Apache POI生成Excel工作表花费很长时间

ljsrvy3e  于 2023-01-03  发布在  Apache
关注(0)|答案(2)|浏览(232)
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;
        }

}

这段代码是用来生成excel报表的。有15列,如果记录的数量在1000左右,生成excel表需要将近10-15分钟。上面的代码有什么问题吗?如何提高性能(减少excel报表生成的时间)?

js81xvg6

js81xvg61#

sheet.autoSizeColumn()方法可能需要一些时间--并且在向工作表中添加每一个数据之后都要调用它。尝试删除所有调用,并在代码的最后执行自动调整--每列只执行一次。

iqih9akk

iqih9akk2#

如果我们尝试使用XSSFWorkbook类生成Excel,我们可能会遇到极其缓慢的情况,因为显然 * 添加到工作簿单元格的所有数据都保存在内存中 ,直到保存Excel。
你可以很容易地找到很多关于这个问题的线程(例如this),解决方案是使用
SXSSFWorkbook
类。
SXSSFWorkbook是实现“BigGridDemo”策略的XSSFWorkbook的版本。这
允许在不耗尽内存的情况下写入非常大的文件*,因为在任何时候只有行的可配置部分保留在内存中。
当***示例化***SXSSFWorkbook类时,您提供了一个窗口大小参数,该参数表示将保留在内存中的行数。所有超出的行将被刷新到磁盘。

相关问题