Java poi Excel導出檔案，Java poi 分批次導出大批量資料

================================

©Copyright 蕃薯耀 2020-01-07

https://www.cnblogs.com/fanshuyao/

一、問題描述：

1、當使用WorkbookFactory.create 建立Excel時，其實建立的文檔為Excel2003版的，最大容納65536行資料，超出這個行數就是發生溢出。

WorkbookFactory.create(...);      

2、當資料量大時，直接從資料庫查詢出來，記憶體不夠也會溢出。

二、解決方案：

 1、解決Excel2003行數溢出，使用XSSFWorkbook對象建立2007版的Excel，能容納104萬行資料。

具體如下：

Excel2003版最大行數是65536，Excel2007最大行數是1048576

Excel2003版最大列數是256，Excel2007最大列數是16384

XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模闆檔案      

2、直接從資料庫查詢大批量資料導緻記憶體溢出，可以使用分批次的方式查詢資料，避免記憶體一下子暴掉。如要導出資料庫100萬條資料，每次取1萬條，這樣分100次取，每次取的數量自己可以定義，保證記憶體不溢出。

具體例子和代碼：

/**
     * 導出excel檔案
     * @param request
     * @param response
     * @throws Exception
     */
    public void exportExcelFile(HttpServletRequest request,HttpServletResponse response){
        Map<String, Object> map = new HashMap<String, Object>();;
        FileOutputStream fileOutputStream = null;
        try {
            Row row = parseRequestParametersToRow(request);
            
            String projectPath = getRealPath(request);
            String excelTemplatePath = projectPath + File.separator 
                    + "template" + File.separator + "name" + File.separator 
                    + "exportPlaceNameQueryExcelFile.xlsx";
            File excelTemplateFile = new File(excelTemplatePath);//模闆檔案
            
            File destFile = this.createDestExcelFile(request, excelTemplateFile);//目标檔案
            
            fileOutputStream = new FileOutputStream(destFile);
            
            map.put("absoluteFilePath", destFile.getAbsolutePath());
            map.put("fileName", destFile.getName());
            int count = getPlaceNameQueryDelegate().getPnPlaceNameCount(row);//擷取Excel導出的總資料量，根據數量判斷是否進行分批次導出
            int pageSize = 1000 * 5;//每次查詢的資料量
            int startRow = 2;//模闆前2行是模闆标題，excel行數是從0開始的，除去2行模闆标題，此處設定成2
            XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模闆檔案
            //Excel2003版最大行數是65536，Excel2007最大行數是1048576
            //Excel2003版最大列數是256，Excel2007最大列數是16384
            SXSSFWorkbook workbook = new SXSSFWorkbook(xSSFWorkbook, 100);//使用SXSSFWorkbook避免Excel資料超出65536行時報錯
            //Workbook b = WorkbookFactory.create(destFile);//超出65536行時報錯
            Sheet sheet = workbook.getSheetAt(0);
            List<Row> rows = null;
            if(count > pageSize){//當數量大時，采用分批次擷取資料
                int page = (count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1);
                for(int i=1; i<=page; i++){
                    row.addColumn("pageNumber", i);
                    row.addColumn("pageSize", pageSize);
                    rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);
                    startRow = this.setSheet(sheet, startRow, rows);//此處要注意回寫startRow供下次循環使用
                }
            }else{
                rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);
                startRow = this.setSheet(sheet, startRow, rows);
            }
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                if(fileOutputStream != null){
                    fileOutputStream.close();
                }
                writeJson(response, JsonUtils.toJson(map));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }      

private File createDestExcelFile(HttpServletRequest request, File excelTemplateFile){
        try {
            String destFileName = UUID.randomUUID().toString();
            Properties  prop = PropertiesUtils.read("placeName.properties");
            String destDir = prop.getProperty("destDirForPlaceNameQuery");
            
            File destFileDir = new File(destDir); 
            if(!destFileDir.exists()){
                destFileDir.mkdirs();
            }
            String absoluteFilePath =  excelTemplateFile.getAbsolutePath();
            String suffix = absoluteFilePath.substring(absoluteFilePath.lastIndexOf("."));
            File destFile = new File(destDir + destFileName + suffix);
            FileUtils.copyFile(excelTemplateFile, destFile);
            return destFile;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }      

private void writeToSheet(Sheet sheet, int startRow, int startCell, String value){
        org.apache.poi.ss.usermodel.Row poiRow = sheet.getRow(startRow);
        if(poiRow == null){
            poiRow = sheet.createRow(startRow);
        }
        Cell cell = poiRow.getCell(startCell);
        if(cell == null){
            cell = poiRow.createCell(startCell);
        }        
        cell.setCellValue(value);
    }      

/**
     * 根據Excel對象插入資料後，傳回可以插入資料下一行的行數，startRow
     * @param workbook
     * @param startRow
     * @param rows
     * @return int
     */
    private int setSheet(Sheet sheet, int startRow, List<Row> rows){
        if(rows != null && rows.size() > 0){
            
            if(startRow < 0){
                startRow = sheet.getLastRowNum();
            }
            for (Row row : rows) {
                int startCell = 0;
                this.writeToSheet(sheet, startRow, startCell++, row.getString("pn_code"));
                this.writeToSheet(sheet, startRow, startCell++, row.getString("standard_name"));//地名（standard_name）
                this.writeToSheet(sheet, startRow, startCell++, row.getString("pinyin"));//拼音（pinyin）
                this.writeToSheet(sheet, startRow, startCell++, row.getString("tree_path"));//地名類别（tree_path）
                String statusText = row.getString("status");
                if(statusText.equals("1")){
                    statusText = "有效";
                }else if(statusText.equals("0")){
                    statusText = "無效";
                }else if(statusText.equals("2")){
                    statusText = "審批中";
                }
                this.writeToSheet(sheet, startRow, startCell++, statusText);//狀态（status）
                
                //額外屬性
                String type = row.getString("type");
                if(!StringUtils.isBlank(type)){
                    type = type.trim();
                    if("建築物".equals(type)){
                        //緊接着基礎資訊，startCell不用加
                        this.setBuildingProperties(sheet, startRow, startCell, row);//建築物
                        
                    }else if("道路".equals(type)){
                        startCell += 10;//除去建築物的10個屬性
                        this.setRoadProperties(sheet, startRow, startCell, row);
                        
                    }else if("橋梁".equals(type)){
                        startCell += 18;//除去建築物、道路的18個屬性
                        this.setBridgeProperties(sheet, startRow, startCell, row);
                        
                    }else if("公園".equals(type)){
                        startCell += 28;//除去建築物、道路、橋梁的28個屬性
                        this.setPartProperties(sheet, startRow, startCell, row);
                        
                    }else if("廣場".equals(type)){
                        startCell += 37;//除去建築物、道路、橋梁、公園的37個屬性
                        this.setSquareProperties(sheet, startRow, startCell, row);
                        
                    }else if("隧道".equals(type)){
                        startCell += 46;//除去建築物、道路、橋梁、公園、廣場的46個屬性
                        this.setTunnelProperties(sheet, startRow, startCell, row);
                    }
                }
                
                startRow ++;//最後行号加1，必須有，且在最後
            }
        }
        return startRow;
    }      

private void setBuildingProperties(Sheet sheet, int startRow, int startCell, Row row){
        try {
            Row dataRow = getPlaceNameQueryDelegate().getBuilding(row);
            if(dataRow != null){
                this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("building_func"));
                this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("start_date"));
                                 ……
                                 ……
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }      

private void setRoadProperties(Sheet sheet, int startRow, int startCell, Row row){
    try {
        Row dataRow = getPlaceNameQueryDelegate().getRoad(row);
            if(dataRow != null){
            this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("starting_point"));
            ……
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}      

（如果你覺得文章對你有幫助，歡迎捐贈，^_^，謝謝！） 

今天越懶，明天要做的事越多。