POI大批量读取、写入Excel

最新推荐文章于 2025-05-03 23:47:39 发布

weixin_43751710

最新推荐文章于 2025-05-03 23:47:39 发布

阅读量2.6k

点赞数

分类专栏： java基础

原文链接：www.baidu.com

版权

java基础专栏收录该内容

101 篇文章

订阅专栏

本文介绍使用Apache POI处理大数据集的策略，避免内存溢出。通过SXXFWorkbook类，可设置每次处理的行数，将数据定期刷新到磁盘。同时，提供创建表头、单元格和写入数据的实例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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

写入大量数据的方法：

使用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文件