Bug 63540 - POI writes corrupted .xlsx when adding large number of ValidationData in a sheet
Summary: POI writes corrupted .xlsx when adding large number of ValidationData in a sheet
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC Windows NT
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-07-03 08:52 UTC by Rahul Saxena
Modified: 2019-11-17 12:41 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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?