Apache POI taking long time to generate excel sheet

Question

package efms.bizobj;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import efms.db.bean.PsatMetricsDrillDown2ActionList;
import efms.db.bean.PsatMetricsDrillDown2Bean;
import efms.log.EfmsLogger;

public class DrillDown2ExcelReport {

    public  void generateHeader1(HSSFWorkbook wb, HSSFSheet sheet, String name,List<PsatMetricsDrillDown2Bean> records) { 
        EfmsLogger.debug("Inside generateHeader1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;  

        int count = 14;
        row = sheet.createRow(0);   


        cell = row.createCell(0);
        cell.setCellValue("USO Number");
        cell = row.createCell(1);
        cell.setCellValue("CLCI");
        cell = row.createCell(2);
        cell.setCellValue("CLO");
        cell = row.createCell(3);
        cell.setCellValue("OCO CLLI");
        cell = row.createCell(4);
        cell.setCellValue("Service Type");
        cell = row.createCell(5);
        cell.setCellValue("Order Action");
        cell = row.createCell(6);
        cell.setCellValue("Order Status");
        cell = row.createCell(7);
        cell.setCellValue("Test Section");
        cell = row.createCell(8);
        cell.setCellValue("Test Status");
        cell = row.createCell(9);
        cell.setCellValue("Condition Code and Desc");
        cell = row.createCell(10);
        cell.setCellValue("Details on Failure Reason");
        cell = row.createCell(11);
        cell.setCellValue("CKL Number");
        cell = row.createCell(12);
        cell.setCellValue("Test Triggered Date/Time");
        cell = row.createCell(13);
        cell.setCellValue("Trigger");
        cell = row.createCell(14);
        cell.setCellValue("Trigger Name");

        for(int i =0 ;i <= count ;i ++)
            sheet.autoSizeColumn(i);
    }

    public  void insertReportData(HSSFWorkbook wb, HSSFSheet sheet, String name,List<PsatMetricsDrillDown2Bean> records,String failType) {
        HSSFRow row ;
        HSSFCell cell;

        String clci ;
        String clo;
        int orderType;
        String usoNumber ;
        int svcOrderId ;
        String serviceType;
        String ocoClli ;
        String orderAction;
        String orderStatus;
        String testStatus ;
        String conditionCode ;
        String conditionCodeDesc ;
        String cklNum  ;
        String triggerDateTime ;
        String trigger ;
        String triggerName ;


        for(int i=0 ; i < records.size() ;i ++){

            clci =" ";
            clo =" ";
            orderType = -1;
            usoNumber = " ";
            svcOrderId = -1;
            serviceType = " ";
            ocoClli = " ";
            orderAction = " ";
            orderStatus = " ";
            testStatus = " ";
            conditionCode = " ";
            conditionCodeDesc = " ";
            cklNum = " " ;
            triggerDateTime = " ";
            trigger = " ";
            triggerName = " ";


            if(records.get(i).getClci() != null)
                clci = records.get(i).getClci();    

            if(records.get(i).getClo() != null)
                clo = records.get(i).getClo();

            if(records.get(i).getOrderType() != -1)
                orderType = records.get(i).getOrderType();

            if(records.get(i).getUsoNumber() != null)
                usoNumber = records.get(i).getUsoNumber();

            if(records.get(i).getSvcOrderId() != -1)
                svcOrderId = records.get(i).getSvcOrderId();

            if(records.get(i).getServiceType() != null)
                serviceType = records.get(i).getServiceType();

            if(records.get(i).getOcoClli() != null)
                ocoClli = records.get(i).getOcoClli();

            if(records.get(i).getOrderAction() != null)
                orderAction = records.get(i).getOrderAction();

            if(records.get(i).getOrderStatus() != null)
                orderStatus = records.get(i).getOrderStatus();

            if(records.get(i).getTestStatus() != null)
                testStatus = records.get(i).getTestStatus();

            if(records.get(i).getConditionCode() != null)
                conditionCode = "("+ records.get(i).getConditionCode()+")";

            if(records.get(i).getConditionCodeDescription() != null)
                conditionCodeDesc = records.get(i).getConditionCodeDescription();

            if(records.get(i).getCklNumber() != null)
                cklNum = records.get(i).getCklNumber();

            if(records.get(i).getTrigDateTime() != null)
                triggerDateTime = records.get(i).getTrigDateTime();

            if(records.get(i).getTrigger() != null)
                trigger = records.get(i).getTrigger();

            if(records.get(i).getTriggerName() != null)
                triggerName = records.get(i).getTriggerName();

            row = sheet.createRow(i+1);

            cell = row.createCell(0);
            cell.setCellValue(usoNumber);
            sheet.autoSizeColumn(0);

            cell = row.createCell(1);
            cell.setCellValue(clci);
            sheet.autoSizeColumn(1);

            cell = row.createCell(2);
            cell.setCellValue(clo);
            sheet.autoSizeColumn(2);

            cell = row.createCell(3);
            cell.setCellValue(ocoClli);
            sheet.autoSizeColumn(3);            


            cell = row.createCell(4);
            cell.setCellValue(serviceType);
            sheet.autoSizeColumn(4);


            cell = row.createCell(5);
            cell.setCellValue(orderAction);
            sheet.autoSizeColumn(5);

            cell = row.createCell(6);
            cell.setCellValue(orderStatus);
            sheet.autoSizeColumn(6);

            cell = row.createCell(7);
            cell.setCellValue(failType);
            sheet.autoSizeColumn(7);

            cell = row.createCell(8);
            cell.setCellValue(testStatus);
            sheet.autoSizeColumn(8);


            cell = row.createCell(9);
            cell.setCellValue(conditionCode + conditionCodeDesc);
            sheet.autoSizeColumn(9);

            if(records.get(i).getActionList()!=null){
                for(PsatMetricsDrillDown2ActionList p: records.get(i).getActionList()) {
                cell = row.createCell(10);
                cell.setCellValue(p.getActionName() + " - " + p.getActionReason());
                sheet.autoSizeColumn(10);
                }
        }else{
            cell = row.createCell(10);
            cell.setCellValue(" ");
            sheet.autoSizeColumn(10);
        }

            cell = row.createCell(11);
            cell.setCellValue(cklNum);
            sheet.autoSizeColumn(11);

            cell = row.createCell(12);
            cell.setCellValue(triggerDateTime);
            sheet.autoSizeColumn(12);

            cell = row.createCell(13);
            cell.setCellValue(trigger);
            sheet.autoSizeColumn(13);

            cell = row.createCell(14);
            cell.setCellValue(triggerName);
            sheet.autoSizeColumn(14);


    }
    }


        public   String generateDD2ExcelReport(List<PsatMetricsDrillDown2Bean> records,String failType){
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet dd2ExcelSheet = wb.createSheet("DrillDown2 Report");
            generateHeader1(wb,dd2ExcelSheet,"DrillDown2 Report",records);
            insertReportData(wb,dd2ExcelSheet,"DrillDown2 Report",records,failType);

            FileOutputStream fileOut;
            Random rand = new Random();
            String fileName = "";           
            String fileLocation = "" ;
            boolean fileExists = true;

            try {


            do{
                fileName = "DrillDown2" + rand.nextInt(10000000)+"Report.xls";
                fileLocation = "/opt/www/data/psat_report" + File.separator + fileName;
                fileExists = new File(fileLocation).exists();

            }while(fileExists);


            fileOut = new FileOutputStream(fileLocation);
            String path = new File(fileLocation).getAbsolutePath();

            wb.write(fileOut);
            fileOut.close();
        } catch (IOException e) {

            e.printStackTrace();
        }
            return fileName;
        }


}

This code is for generating excel report . There are 15 columns , if the number of record is around 1000 , it takes nearly 10-15 min to generate excel sheet. Is there any issue in the above code ? How performance(time reduction in excel report generation) can be improved ?

possible duplicate of [A POI related code block running dead slow](http://stackoverflow.com/questions/18984785/a-poi-related-code-block-running-dead-slow) — rgettman, Oct 29 '13 at 17:42

score 21 · Accepted Answer · answered Oct 29 '13 at 08:32

21

The sheet.autoSizeColumn() method may take some time - and you call it after every single piece of data you add to the sheet. Try removing all the calls and do the autosize at the very end of you code - just once for each column.

answered Oct 29 '13 at 08:32

Jack

2,937
5
34
44

thanks jack, it'll help me lot, thanks again with +1. – Yogesh Prajapati Sep 12 '14 at 04:51
**autoSizeColumn()** was the culprit in my case too. Thank for a wonderful soultion. +1 for you – arun_kk Apr 15 '15 at 07:05
I had to wait a few hours to generate a report with two thousand rows. I removed autoSizeColumn from a cycle. Now it takes 15-20 seconds. Thanks. +1 – Valeriy K. Nov 12 '18 at 08:35
Adding to this answer, as I just had the problem myself. Whether you use the "streaming" API or the regular one, the *autoSizeColumn* will trigger extremely costly operations in POI. In most cases you're better off computing sizes on your own and then call *[sheet].setColumnWidth* at the end of your loop. In my case it brought a 120k rows sheet down from about 8 minutes to just above 20 seconds, (which are inclusive of all the other stuff my code is doing). The documentation for setColumnWidth will give you the formula to use. – Filippo Possenti Oct 30 '19 at 16:37

score 0 · Answer 2 · answered Jan 02 '23 at 02:58

If we try to generate an Excel using the XSSFWorkbook class we may experience extreme slowness, because apparently all the data added to the workbook cells are kept in-memory until the Excel is saved.

You can easily find a lot of threads (e.g this) about this problem and the solution is using the SXSSFWorkbook class instead. Both implementations implement the Workbook interface.

SXSSFWorkbook is a Streaming version of XSSFWorkbook implementing the “BigGridDemo” strategy. This allows to write very large files without running out of memory as only a configurable portion of the rows are kept in-memory at any one time.

When instantiating the SXSSFWorkbook class, you provide a window size parameter which represents the amount of rows which will be kept in-memory. All the exceeding rows will be flushed to disk.

Apache POI taking long time to generate excel sheet

2 Answers2