write huge number of records to xlsx using POI

Question

I want to write thousands of records to excel. My code =

public static void updateMetadataExcel(List<Metadata> metaDataList, String excelPath, int maxRiskArea) {


    FileOutputStream fileOut = null;
    FileInputStream file = null;
    Workbook workbook = null;
    int cellCount = 0 ;
        try {

            for(Metadata metaDataIterator : metaDataList){
                cellCount = 0;
                file = new FileInputStream(new File(excelPath));
                workbook = WorkbookFactory.create(file);

                int rowCount = 0;
                Sheet sheet = workbook.getSheetAt(0);
                rowCount = sheet.getPhysicalNumberOfRows();
            //  System.out.println("last =" + rowCount);

                rowCount = sheet.getPhysicalNumberOfRows();
                Row row = sheet.createRow(rowCount);
                Cell cell = row.createCell(cellCount);
                createCell(cell, row, metaDataIterator.getCourseContentName(), cellCount);
                createCell(cell, row, metaDataIterator.getLang(), ++cellCount);
                createCell(cell, row, metaDataIterator.getPalette(), ++cellCount);
                createCell(cell, row, metaDataIterator.getWordToGLE(), ++cellCount);
                createCell(cell, row, metaDataIterator.getCourseTitleAttr(), ++cellCount);
                createCell(cell, row, metaDataIterator.getAudience(), ++cellCount);
                createCell(cell, row, metaDataIterator.getClientCode(), ++cellCount);
                createCell(cell, row, metaDataIterator.getContentRegion(), ++cellCount);
                createCell(cell, row, metaDataIterator.getCourseStatus(), ++cellCount);
                createCell(cell, row, metaDataIterator.getCourseTitleTag(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDateCreated(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDateReleased(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDateRetired(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDeprecatedId(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDeprecatedProductCode(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDivision(), ++cellCount);
                createCell(cell, row, metaDataIterator.getDuration(), ++cellCount);
                createCell(cell, row, metaDataIterator.getEditorialReview(), ++cellCount);
                createCell(cell, row, metaDataIterator.getFormatChangeDate(), ++cellCount);
                createCell(cell, row, metaDataIterator.getGleCode(), ++cellCount);
                createCell(cell, row, metaDataIterator.getGuid(), ++cellCount);
                createCell(cell, row, metaDataIterator.getIndustry(), ++cellCount);
                createCell(cell, row, metaDataIterator.getInternalCourseNotes(), ++cellCount);
                createCell(cell, row, metaDataIterator.getKeyLearningPoints(), ++cellCount);
                createCell(cell, row, metaDataIterator.getKeywords(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLanguage(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLastModified(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLawsCovered(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLearningFormat(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLearningObjectives(), ++cellCount);
                createCell(cell, row, metaDataIterator.getLegalReview(), ++cellCount);
                createCell(cell, row, metaDataIterator.getOriginalLanguage(), ++cellCount);
                createCell(cell, row, metaDataIterator.getProductDescription(), ++cellCount);
                createCell(cell, row, metaDataIterator.getProductType(), ++cellCount);
                createCell(cell, row, metaDataIterator.getProjectType(), ++cellCount);
                createCell(cell, row, "", ++cellCount);

                for(int i = 0 ; i < maxRiskArea ;  ++i){
                    if(metaDataIterator.getRiskAreas().size() >= maxRiskArea)
                        createCell(cell, row, metaDataIterator.getRiskAreas().get(i), ++cellCount);
                    else
                        createCell(cell, row, "", ++cellCount);
                }

                createCell(cell, row, "", ++cellCount);
                createCell(cell, row, metaDataIterator.getRole(), ++cellCount);
                createCell(cell, row, metaDataIterator.getSalesforceId(), ++cellCount);
                createCell(cell, row, metaDataIterator.getScriptFilename(), ++cellCount);
                createCell(cell, row, metaDataIterator.getScriptPath(), ++cellCount);
                createCell(cell, row, metaDataIterator.getSetting(), ++cellCount);
                createCell(cell, row, metaDataIterator.getSmsCode(), ++cellCount);
                createCell(cell, row, metaDataIterator.getSponsoringRegion(), ++cellCount);
                createCell(cell, row, metaDataIterator.getTargetAudience(), ++cellCount);
                createCell(cell, row, metaDataIterator.getTopic(), ++cellCount);
                createCell(cell, row, metaDataIterator.getTranslationFormat(), ++cellCount);
                createCell(cell, row, metaDataIterator.getVersion(), ++cellCount);
                createCell(cell, row, metaDataIterator.getVisualFormat(), ++cellCount);

                for(int colNum = 0; colNum<row.getLastCellNum();colNum++)   
                    workbook.getSheetAt(0).autoSizeColumn(colNum);
                fileOut = new FileOutputStream(excelPath);
                workbook.write(fileOut);
            }

            System.out.println("\nUpdated Excel For Metadata.");
        }
        catch (Exception e) {
            errorLog.error("Excel Read/Write Error =" + e.getMessage());
            throw new GLEException(e);
    }
}

private static void createCell(Cell cell , Row row, String name, int cellCount ){
    cell = row.createCell(cellCount);
    cell.setCellValue(name);
}

But its taking so much of TIME, memory and RAM!

2k records each with 52 columns took around an hour

what can be the issue ?

Java uses enough resources as is. Check your JVM memory allocation. — Mr. Polywhirl, Mar 11 '14 at 10:19
I would suggest to check the memory consumption with JVisualVM, maybe the GC is consuming most of the CPU time because the heap is nearly full. — SWoeste, Mar 11 '14 at 10:22
You should also avoid fileOut = new FileOutputStream(excelPath); inside the loop, for every iteration you are opening a new connection to the file. Which you are not closing this is probably causing the underlying OS major issues. Ditto on input stream. — Caleryn, Mar 11 '14 at 10:25
2000 rows, 52 columns each, HSSF, takes under 1 second on my laptop. I don't know what you're doing wrong, but the problem isn't with Apache POI it's with you... — Gagravarr, Mar 11 '14 at 12:00
am using SXSSF, its taking less than a second for each record — gursahib.singh.sahni, Mar 11 '14 at 12:21
Thanks @Caleryn for pointing out such obvious mistake. Works like a charm. — gursahib.singh.sahni, Mar 11 '14 at 13:51

score 0 · Answer 1 · edited May 23 '17 at 12:05

From the documentation from apache POI site it is stated that since version 3.8.beta3 there is a new api available called SXSSF. It has a much smaller footprint and should be used to produce very large excel files. Please check following link: http://poi.apache.org/spreadsheet/

Also, your question is a possible duplicate from Socket Exception while genrating bulk excel file using Apache Poi

write huge number of records to xlsx using POI

1 Answers1