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 ?

Answer 1

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.