Bug 63540

Summary: POI writes corrupted .xlsx when adding large number of ValidationData in a sheet
Product: POI Reporter: Rahul Saxena <rascse>
Component: SXSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: normal    
Priority: P2    
Version: 4.0.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows NT   

Description Rahul Saxena 2019-07-03 08:52:16 UTC
I want to write a large .xlsx file(50K rows) using POI in JAVA. I expect each row to contain multiple drop-down cells. Code works fine when number of rows are less than 30K but writes a corrupt file if number of rows exceed 35K.

I have tried SXSSFWorkbook and XSSFWorkbook but none worked for me. Here is the code:
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        SXSSFSheet sheet = workbook.createSheet();
        String[] optionsArray = new String[] {"1000.00","2000.00"};
        int no_of_rows = 35000;
        for(int i=0;i<=no_of_rows;i++) {
            SXSSFRow row1 = sheet.createRow(i);
            SXSSFCell r1c1 = row1.createCell(0);
            DataValidationConstraint  constraint1 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);
            CellRangeAddressList addressList1 = new CellRangeAddressList(i, i, 0, 0);
            DataValidation dataValidation1 = sheet.getDataValidationHelper().createValidation(constraint1, addressList1);
            sheet.addValidationData(dataValidation1);
            r1c1.setCellValue("1000.00");   

            SXSSFCell r1c2 = row1.createCell(1);
            DataValidationConstraint  constraint2 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);
            CellRangeAddressList addressList2 = new CellRangeAddressList(i, i, 1, 1);
            DataValidation dataValidation2 = sheet.getDataValidationHelper().createValidation(constraint2, addressList2);
            sheet.addValidationData(dataValidation2);
            r1c2.setCellValue("2000.00");
        }
        FileOutputStream fos =new FileOutputStream(new File("c:\\data\\testout.xlsx"));
        workbook.write(fos);
        workbook.close();
        fos.close();

I expect POI to work well with 50K rows of such data at least.
Comment 1 Dominik Stadler 2019-11-17 12:41:51 UTC
You may exceed some limitations of Excel itself, did you check Excel can handle such a large number when you create the file elsewhere?