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.
You may exceed some limitations of Excel itself, did you check Excel can handle such a large number when you create the file elsewhere?