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();
    }
}

（如果你觉得文章对你有帮助，欢迎捐赠，^_^，谢谢！） 

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

©Copyright 蕃薯耀 2020-01-07

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