Inserting data from arraylist in chunks in excel file using apache poi

Question

I have the arraylist of data in the following format :

ArrayList> listResultData. Now collection contains around 11k+ rows to be inserted in the excel. When i insert these 11490 rows in excel it took 6 hrs to insert the records, that means its very bad performance issue. Is there anyway to insert the data in excel in chunks for 1000 rows at a time (means there should be something like executeBatch() in sql for inserting records). A row contains 4-5 columns also.

Following is the code i have been using :

public boolean setArrayListData(String sheetName, ArrayList<ArrayList<String>> listResultData) {
    try {
        fis = new FileInputStream(path);
        workbook = new XSSFWorkbook(fis);

        int index = workbook.getSheetIndex(sheetName);

        if (index == -1)
            return false;

        sheet = workbook.getSheetAt(index);

        int colNum = 0;
        int rowNum = this.getRowCount(sheetName);
        rowNum++;
        for (ArrayList<String> al : listResultData) {   
            for (String s : al) {
                sheet.autoSizeColumn(colNum);
                row = sheet.getRow(rowNum - 1);
                if (row == null)
                    row = sheet.createRow(rowNum - 1);

                cell = row.getCell(colNum);
                if (cell == null)
                    cell = row.createCell(colNum);

                // cell style
                // CellStyle cs = workbook.createCellStyle();
                // cs.setWrapText(true);
                // cell.setCellStyle(cs);
                cell.setCellValue(s);
                //System.out.print("Cell Value :: "+s);
                colNum++;
            }
            rowNum++;
            colNum = 0;
            //System.out.println("");
        }

        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();
        workbook.close();
        fis.close();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

Please suggest !!

I don't know apache poi, but maybe docx4j has a better performance for you. I create an xslx with about 6000 rows and 20 columns in a short time. I used this sample to adapt it for my solution: github.com/plutext/docx4j/blob/master/src/samples/xlsx4j/org/… Just take care of the R Attribute for each cell: row1colum.setR("A1"); cell.setR("B1"); cell.setR("C1"); ... — froehli
– froehli, Commented Aug 25, 2017 at 10:24

Eritrean · Accepted Answer · 2017-08-25 10:02:40Z

Instead of XSSF you may want to try SXSSF the streaming extension of XSSF. In contrast to xssf where you have access to all rows in the document which can lead to performance or heap space issue sxssf allows you to define a sliding window and limits the access to rows in that window. You can specify the window size at construction time of your workbook using new SXSSFWorkbook(int windowSize) . As you then create your rows and the number of rows exceed the specified window size, the row with the lowest index is flushed and is no longer in memory.

Find further infos at SXSSF (Streaming Usermodel API)

Example:

// keep 100 rows in memory, exceeding rows will be flushed to disk
SXSSFWorkbook wb = new SXSSFWorkbook(100); 
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        //When the row count reaches 101, the row with rownum=0 is flushed to disk and removed from memory, 
        //when rownum reaches 102 then the row with rownum=1 is flushed, etc. 
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }

Collectives™ on Stack Overflow

Inserting data from arraylist in chunks in excel file using apache poi

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related