POI大批量读取、写入Excel

刺骨的言语ヽ痛彻心扉 2023-06-14 06:29 46阅读 0赞

数据量变大的时候，普通的处理方法都会出现问题，POI也不例外。

一次性读出或写入的数据太多的话，可能会导致内存溢出，因为POI是把数据放在内存里的。

不过POI提供了相应的解决办法：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]

## 写入大量数据的方法： ##

使用SXXFWorkbook，而不是03或07版Excel对应的类，SXXFWorkbook是专门为大批量写入数据准备的。

通过设置SXXFWorkbook的构造参数，可以设置每次在内存中保持的行数，当达到这个值的时候，那么会把这些数据flush到磁盘上，这样就不会出现内存不够的情况

## 读出大量数据的方法： ##

一次性读出的数据太多的话，也会有相应的情况出现。这就需要把Excel转换为csv来解决（当然也可以自己循环，设置每次读出3000行这样来读）

## 写入大批量数据的实例 ##

**创建表头的样式，以及创建表头：**

private CellStyle createTitleStyle(SXSSFWorkbook workbook) { 
    
        Font boldFont = workbook.createFont();
        boldFont.setFontHeight((short) 200);
        CellStyle style = workbook.createCellStyle();
        style.setFont(boldFont);
        return style;
    
    }

public SXSSFSheet createSheet1(SXSSFWorkbook workbook, CellStyle style,int index) { 
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("品牌馆信息" + index);
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 8000);
        sheet.setColumnWidth(3, 8000);
        sheet.setColumnWidth(4, 8000);
        sheet.setColumnWidth(5, 8000);
        sheet.setColumnWidth(6, 8000);
        sheet.setColumnWidth(7, 8000);
        SXSSFRow rowHead = (SXSSFRow) sheet.createRow((short) 0);
        createCell(rowHead, 0, style, HSSFCell.CELL_TYPE_STRING, "brand_uid");
        createCell(rowHead, 1, style, HSSFCell.CELL_TYPE_STRING, "brand_name");
        createCell(rowHead, 2, style, HSSFCell.CELL_TYPE_STRING, "已发布");
        createCell(rowHead, 3, style, HSSFCell.CELL_TYPE_STRING, "待发布");
        createCell(rowHead, 4, style, HSSFCell.CELL_TYPE_STRING, "是否配置了");
        createCell(rowHead, 5, style, HSSFCell.CELL_TYPE_STRING, "是否有生效中");
        createCell(rowHead, 6, style, HSSFCell.CELL_TYPE_STRING, "是否配置");
        createCell(rowHead, 7, style, HSSFCell.CELL_TYPE_STRING, "是否有生效中");
        return sheet ;
    }

public SXSSFSheet createSheet2(SXSSFWorkbook workbook, CellStyle style,int index) { 
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("品牌馆选人信息" + index);
        sheet.setColumnWidth(0, 8000);
        sheet.setColumnWidth(1, 8000);
        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 8000);
        sheet.setColumnWidth(4, 8000);
        SXSSFRow rowHead0 = (SXSSFRow) sheet.createRow((short) 0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));// 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4));// 合并单元格
        createCell(rowHead0, 0, style, HSSFCell.CELL_TYPE_STRING, "有生效中");
        createCell(rowHead0, 3, style, HSSFCell.CELL_TYPE_STRING, "使用过");
        SXSSFRow rowHead = (SXSSFRow) sheet.createRow((short) 1);
        createCell(rowHead, 0, style, HSSFCell.CELL_TYPE_STRING, "brand_uid");
        createCell(rowHead, 1, style, HSSFCell.CELL_TYPE_STRING, "brand_name");
        createCell(rowHead, 2, style, HSSFCell.CELL_TYPE_STRING, "间隔");
        createCell(rowHead, 3, style, HSSFCell.CELL_TYPE_STRING, "brand_uid");
        createCell(rowHead, 4, style, HSSFCell.CELL_TYPE_STRING, "brand_name");
        return sheet ;
    }

public SXSSFSheet createSheet3(SXSSFWorkbook workbook, CellStyle style,int index) { 
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("品牌馆一周内新增及活动信息" + index);
        sheet.setColumnWidth(0, 8000);
        sheet.setColumnWidth(1, 8000);
        sheet.setColumnWidth(2, 8000);
        sheet.setColumnWidth(3, 8000);
        SXSSFRow rowHead = (SXSSFRow) sheet.createRow((short) 0);
        createCell(rowHead, 0, style, HSSFCell.CELL_TYPE_STRING, "一周内新增数量");
        createCell(rowHead, 1, style, HSSFCell.CELL_TYPE_STRING, "一周内参活动的人次");
        createCell(rowHead, 2, style, HSSFCell.CELL_TYPE_STRING, "一周内参与活动的人数");
        createCell(rowHead, 3, style, HSSFCell.CELL_TYPE_STRING, "进行中的活动数量");
        return sheet ;
    }

**创建单元格的方法：**

private void createCell(Row row, int column, CellStyle cellStyle, int cellType, Object value) { 
        Cell cell = row.createCell(column);
        if (cellStyle != null) { 
            cell.setCellStyle(cellStyle);
        }
        switch (cellType) { 
            case HSSFCell.CELL_TYPE_NUMERIC: { 
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.parseDouble(value.toString()));}
            break;
            case HSSFCell.CELL_TYPE_STRING: { 
                cell.setCellValue(value.toString());
            }
            break;
            case HSSFCell.CELL_TYPE_BLANK: { 
    
            }
            break;
            default:
                break;
        }
    }

**最终实现写数据，并输出文件**

//生成excel文件
    SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
    //样式
    CellStyle style = createTitleStyle(workbook);
    //建表，并填写表头
    SXSSFSheet sheet1 = createSheet1(workbook,style,1);
    SXSSFSheet sheet2 = createSheet2(workbook,style,2);
    SXSSFSheet sheet3 = createSheet3(workbook,style,3);
    if (CollectionUtils.isNotEmpty(brandInfoListHundred))
    { 
        //填写表内容
        int rowNum = 0;
        for (int i = 0; i < brandInfoListHundred.size(); i++) { 
            if (i < sheetMaxRow) { 
                rowNum = i + 1;
            } else { 
                if (i % sheetMaxRow == 0) { 
                    sheet1 = createSheet1(workbook, style, i / sheetMaxRow + 1);
                }
                rowNum = i % sheetMaxRow + 1;
            }
            BrandIdAndNameExtend brandIdAndName = brandInfoListHundred.get(i);
            Row row = sheet1.createRow((short) (rowNum));
            createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandUid().toString()) ? brandIdAndName.getBrandUid().toString() : "");
            createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandName()) ? brandIdAndName.getBrandName() : "");
            createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getPublishedConfig()) ? brandIdAndName.getPublishedConfig() : "");
            createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getUnPublishedConfig()) ? brandIdAndName.getUnPublishedConfig() : "");
            createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getStoreyDataConfig()) ? brandIdAndName.getStoreyDataConfig() : "");
            createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getValidStoreyData()) ? brandIdAndName.getValidStoreyData() : "");
            createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getjShopLinkData()) ? brandIdAndName.getjShopLinkData() : "");
            createCell(row, 7, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getValidJShopData()) ? brandIdAndName.getValidJShopData() : "");
        }
    }
    
    if(CollectionUtils.isNotEmpty(brandInfoListValid))
    { 
        //填写表内容
        int rowNum = 0;
        for (int i = 0; i < brandInfoListValid.size(); i++) { 
            if (i < sheetMaxRow) { 
                rowNum = i + 2;
            } else { 
                if (i % sheetMaxRow == 0) { 
                    sheet2 = createSheet2(workbook, style, i / sheetMaxRow + 1);
                }
                rowNum = i % sheetMaxRow + 2;
            }
            BrandIdAndName brandIdAndName = brandInfoListValid.get(i);
            Row row = sheet2.createRow((short) (rowNum));
            createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandUid().toString()) ? brandIdAndName.getBrandUid().toString() : "");
            createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandName()) ? brandIdAndName.getBrandName() : "");
            }
    }
    
    if(CollectionUtils.isNotEmpty(brandInfoListPeople))
    { 
        //填写表内容
        int rowNum = 0;
        for (int i = 0; i < brandInfoListPeople.size(); i++) { 
            if (i < sheetMaxRow) { 
                rowNum = i + 2;
            } else { 
                if (i % sheetMaxRow == 0) { 
                    sheet2 = createSheet2(workbook, style, i / sheetMaxRow + 1);
                }
                rowNum = i % sheetMaxRow + 2;
            }
            BrandIdAndName brandIdAndName = brandInfoListPeople.get(i);
            Row row = sheet2.createRow((short) (rowNum));
            createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandUid().toString()) ? brandIdAndName.getBrandUid().toString() : "");
            createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING, com.jd.common.util.StringUtils.isNotBlank(brandIdAndName.getBrandName()) ? brandIdAndName.getBrandName() : "");
        }
    }
    int rowNum3 = 1;
    Row row3 = sheet3.createRow((short) (rowNum3));
    createCell(row3, 0, style, HSSFCell.CELL_TYPE_STRING, newBrandNum);
    if(!joinActivityPeopleNum.isEmpty())
    { 
        createCell(row3, 1, style, HSSFCell.CELL_TYPE_STRING, joinActivityPeopleNum.get("num"));
        createCell(row3, 2, style, HSSFCell.CELL_TYPE_STRING, joinActivityPeopleNum.get("disnum"));
    }
    createCell(row3, 3, style, HSSFCell.CELL_TYPE_STRING, activeConcernNum);
    
    file = File.createTempFile("poi-sxssf-template", ".xlsx");
    fos = new FileOutputStream(file);
    workbook.write(fos);
    workbook.dispose(); //删除临时xml文件

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191118224121550.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzc1MTcxMA==,size_16,color_FFFFFF,t_70