Bug 53650

Summary: SXSSF can be made to output corrupt XLSX files
Product: POI Reporter: Dave Lambley <apache.org>
Component: SXSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: Spreadsheet which SXSSF cannot process

Description Dave Lambley 2012-08-03 16:30:39 UTC
Created attachment 29162 [details]
Spreadsheet which SXSSF cannot process

I am attempting to use SXSSF to add data to a template XLSX file.  I have a XLSX file produced by Excel for Mac 2011 version 14.2.3, which when processed with the attached minimal Java produces a file which does not open in Excel, but does open in Open Office.  Excel reports the file as broken and offer repair, which removes all the additions placed in by the Java code.

I am using POI 3.8. XSSF is able to process the file correctly.

Minimal Java below,

package sheet2test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author davel
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            final Workbook wb = new SXSSFWorkbook(new XSSFWorkbook(new FileInputStream("template.xlsx")));

            Sheet currentSheet = wb.getSheetAt(1);

            Row currentRow = currentSheet.createRow(1);
            Cell currentCell = currentRow.createCell(1);
            currentCell.setCellType(Cell.CELL_TYPE_STRING);
            currentCell.setCellValue("hello world!");
      
            FileOutputStream out = new FileOutputStream("broken.xlsx");
            wb.write(out);
        }
        catch (IOException e) {
            System.err.println("Could not write output file");
        }
    }

}


Kind regards,
Dave Lambley
Comment 1 Yegor Kozlov 2012-12-07 11:21:23 UTC
You are overwriting an existing row which is now allowed.
SXSSF is streaming forward-only API which means it can only append data to your template.
Your workbook already contains rows, it can easily checked as follows:


        XSSFWorkbook template = new XSSFWorkbook(new FileInputStream("template.xlsx")) ;
        XSSFSheet sheet = template.getSheetAt(1);  
        System.out.println(sheet.getPhysicalNumberOfRows());
        System.out.println(sheet.getFirstRowNum());
        System.out.println(sheet.getLastRowNum());

Your code creates a row with rowIndex=1 which gets appended after the last row in the template and it results in a corrupted workbook for two reasons:
 - there is a duplicate row
 - rows in sheet.xml are not ordered: rowIndex=1 comes after rowIndex=1976


I committed a fix in r1418264. Now SXSSF throws IllegalArgumentException if you are trying to overwrite a row already saved to disk.

Yegor