Bug 58077

Summary: groupRow doesn't work with SXSSF
Product: POI Reporter: xiaozhulolo
Component: SXSSFAssignee: POI Developers List <dev>
Status: REOPENED ---    
Severity: critical    
Priority: P2    
Version: 3.12-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Attached is the generated results

Description xiaozhulolo 2015-06-26 08:53:42 UTC
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}
Comment 1 Dominik Stadler 2015-06-26 11:50:47 UTC
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.
Comment 2 Dominik Stadler 2015-07-07 20:17:26 UTC
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.
Comment 3 albfan 2017-02-27 17:59:33 UTC
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);
	}
Comment 5 albfan 2017-02-27 18:13:31 UTC
	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));
	}
Comment 6 albfan 2017-02-27 18:15:17 UTC
that code resolves it as

groupRow(1,n)

groupRow(n+1, m)

joins two groups and generate same as

groupRow(1,m)