Created attachment 32858 [details] Attached is the generated results Run below code can generate xlsx with SXSSF and XSSF for comparison. The one generated by XSSF is expected, the one generated by SXSSF won't group rows correctly. {code} import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class TestSXSSF { public static void main(String args[]) throws FileNotFoundException, IOException{ File outputFile = new File("E:/testFile-actual.xlsx"); SXSSFWorkbook sworkbook = new SXSSFWorkbook(); creatXLSX(outputFile, sworkbook); File expectedoutputFile = new File("E:/testFile-expected.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); creatXLSX(expectedoutputFile, workbook); } private static void creatXLSX(File outputFile, Workbook workbook) throws IOException, FileNotFoundException { Sheet sheet = workbook.createSheet(); for(int i = 0; i < 200; i++) sheet.createRow(i); for(int i = 1; i < 200; i++){ sheet.groupRow(i, i+10); i = i+11; } workbook.write(new FileOutputStream(outputFile)); workbook.close(); } } {code}
As the Javadoc states " * Please note the rows being grouped <em>must</em> be in the current window, * if the rows are already flushed then groupRow has no effect. The default window size is 100, please try with a larger one when you construct the XSSFWorkbook and report here if it works then.
No response for some time, so I assume it was fixed by my previous comment, please reopen this bug if there is still a problem with grouping.
If the window size has bigger size than expected write files, where is the benefit in use SXSSFWorkbook? The usage of SXSSFWorkbook is that you don't know how many files you will get, and you want to flush its contents to disk. But if you need to group rows, if you reach a module of window_size and you didn't create a group row it will be write withtout grouping, and you have no access to that rows again. Say you want to group rows 25 to 455 with a window size of 200 you will get first 200 files ungrouped I tried to define a group every window size and almost work, but this add too ramdomly a group on window_size +1 to window_size + 2 I try to define group from begin to window_size module everytime and almost too but some inner groups appear from time to time. I guess best shot is to left this uninmplemented same as setRowGroupCollased() which through an UnImplementException Here is some test code I made: public static final int ROWS=1000; public static final int WINDOW_SIZE=100; //public static final int WINDOW_SIZE=1000; public static void main(String args[]) throws FileNotFoundException, IOException{ File outputFile = new File("testFile-actual.xlsx"); SXSSFWorkbook sworkbook = new SXSSFWorkbook(WINDOW_SIZE); creatXLSX(outputFile, sworkbook); File expectedoutputFile = new File("testFile-expected.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); creatXLSX(expectedoutputFile, workbook); } private static void creatXLSX(File outputFile, Workbook workbook) throws IOException, FileNotFoundException { Sheet sheet = workbook.createSheet(); int posFirstRow = 1; boolean addgroup = true; for(int i = 0; i < ROWS; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue(i); if (addgroup && i % WINDOW_SIZE == 0) { createGroup(sheet, posFirstRow, i); //posFirstRow = i; } if (i == 556) { createGroup(sheet, posFirstRow, 553); //posFirstRow = 556; addgroup = false; } } workbook.write(new FileOutputStream(outputFile)); } private static void createGroup(Sheet sheet, int posFirstRow, int i) { sheet.groupRow(posFirstRow, i); }
Here says it clearly https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFSheet.html#groupRow(int,%20int)
public static final int ROWS=1000; public static final int WINDOW_SIZE=100; //public static final int WINDOW_SIZE=1000; public static void main(String args[]) throws FileNotFoundException, IOException{ File outputFile = new File("testFile-actual.xlsx"); SXSSFWorkbook sworkbook = new SXSSFWorkbook(WINDOW_SIZE); creatXLSX(outputFile, sworkbook); File expectedoutputFile = new File("testFile-expected.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(); creatXLSX(expectedoutputFile, workbook); } private static void creatXLSX(File outputFile, Workbook workbook) throws IOException, FileNotFoundException { Sheet sheet = workbook.createSheet(); int posFirstRow = 1; boolean addgroup = true; for(int i = 0; i < ROWS; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue(i); if (addgroup && i % WINDOW_SIZE == 0) { sheet.groupRow(posFirstRow, i); posFirstRow = i+1; } if (i == 556) { sheet.groupRow(posFirstRow, 553); posFirstRow = 556; addgroup = false; } } workbook.write(new FileOutputStream(outputFile)); }
that code resolves it as groupRow(1,n) groupRow(n+1, m) joins two groups and generate same as groupRow(1,m)