几百万数据量的 Excel 导出？

S_L__

已于 2022-12-09 18:17:32 修改

阅读量338

点赞数

分类专栏： java基础文章标签： excel

于 2022-12-05 13:58:40 首次发布

本文链接：https://blog.csdn.net/S_L__/article/details/128185268

版权

java基础专栏收录该内容

22 篇文章 0 订阅

订阅专栏

之前遇到过，导出20万条数据，每条数据200个字段，会造成内存溢出和卡顿，根据这里的刚发解决问题！

摘抄：几百万数据量的 Excel 导出会内存溢出和卡顿？那是你没用对方法！_l_瓶中精灵的博客-CSDN博客

一. 简介

这里使用阿里开源的EasyExcel框架，发现可以将解析的EXCEL的内存占用控制在KB级别，并且绝对不会内存溢出（内部实现待研究）,还有就是速度极快，大概100W条记录，十几个字段，只需要70秒即可完成下载。

EasyExcel的github地址是：https://github.com/alibaba/easyexcel

二. 案例

2.1 POM依赖

<!-- 阿里开源EXCEL -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.1</version>
</dependency>

2.2 代码演示

2.2.1.数据量少的(20W以内吧)：一个SHEET一次查询导出

/**
 * 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中
 * 注意： 一次性查询出来的记录数量不宜过大，不会内存溢出即可。
 *
 * @throws IOException
 */
@Test
public void writeExcelOneSheetOnceWrite() throws IOException {
 
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
 
    // 设置SHEET
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
 
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
 
    // 查询数据导出即可 比如说一次性总共查询出100条数据
    List<List<String>> userList = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
    }
 
    writer.write0(userList, sheet, table);
    writer.finish();
}

2.2.2.数据量适中（100W以内）：一个SHEET分批查询导出

/**
 * 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
 * 注意：
 * 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
 * 数据量不能超过一个SHEET存储的最大数据量105W
 *
 * @throws IOException
 */
@Test
public void writeExcelOneSheetMoreWrite() throws IOException {
 
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
 
    // 设置SHEET
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
 
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
 
    // 模拟分批查询：总记录数50条，每次查询20条，  分三次查询 最后一次查询记录数是10
    Integer totalRowCount = 50;
    Integer pageSize = 20;
    Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
 
    // 注： 此处仅仅为了模拟数据，实用环境不需要将最后一次分开，合成一个即可， 参数为：currentPage = i+1;  pageSize = pageSize
    for (int i = 0; i < writeCount; i++) {
 
        // 前两次查询 每次查20条数据
        if (i < writeCount - 1) {
 
            List<List<String>> userList = new ArrayList<>();
            for (int j = 0; j < pageSize; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
            writer.write0(userList, sheet, table);
 
        } else if (i == writeCount - 1) {
 
            // 最后一次查询 查多余的10条记录
            List<List<String>> userList = new ArrayList<>();
            Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
            for (int j = 0; j < lastWriteRowCount; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
            writer.write0(userList, sheet, table);
        }
    }
 
    writer.finish();
}

2.2.3.数据量很大（几百万都行）：多个SHEET分批查询导出

/**
 * 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中
 * 注意：
 * perSheetRowCount % pageSize要能整除  为了简洁，非整除这块不做处理
 * 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
 *
 * @throws IOException
 */
@Test
public void writeExcelMoreSheetMoreWrite() throws IOException {
 
    // 生成EXCEL并指定输出路径
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
 
    // 设置SHEET名称
    String sheetName = "测试SHEET";
 
    // 设置标题
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<List<String>>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
 
    // 模拟分批查询：总记录数250条，每个SHEET存100条，每次查询20条  则生成3个SHEET，前俩个SHEET查询次数为5， 最后一个SHEET查询次数为3 最后一次写的记录数是10
    // 注：该版本为了较少数据判断的复杂度，暂时perSheetRowCount要能够整除pageSize， 不去做过多处理  合理分配查询数据量大小不会内存溢出即可。
    Integer totalRowCount = 250;
    Integer perSheetRowCount = 100;
    Integer pageSize = 20;
    Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
    Integer previousSheetWriteCount = perSheetRowCount / pageSize;
    Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?
            previousSheetWriteCount :
            (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));
 
    for (int i = 0; i < sheetCount; i++) {
 
        // 创建SHEET
        Sheet sheet = new Sheet(i, 0);
        sheet.setSheetName(sheetName + i);
 
        if (i < sheetCount - 1) {
 
            // 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行  2个SHEET合计200行  实用环境：参数：currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize
            for (int j = 0; j < previousSheetWriteCount; j++) {
                List<List<String>> userList = new ArrayList<>();
                for (int k = 0; k < 20; k++) {
                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                }
                writer.write0(userList, sheet, table);
            }
 
        } else if (i == sheetCount - 1) {
 
            // 最后一个SHEET 实用环境不需要将最后一次分开，合成一个即可， 参数为：currentPage = i+1;  pageSize = pageSize
            for (int j = 0; j < lastSheetWriteCount; j++) {
 
                // 前俩次查询 每次查询20条
                if (j < lastSheetWriteCount - 1) {
 
                    List<List<String>> userList = new ArrayList<>();
                    for (int k = 0; k < 20; k++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
                    }
                    writer.write0(userList, sheet, table);
 
                } else if (j == lastSheetWriteCount - 1) {
 
                    // 最后一次查询 将剩余的10条查询出来
                    List<List<String>> userList = new ArrayList<>();
                    Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
                    for (int k = 0; k < lastWriteRowCount; k++) {
                        userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
                    }
                    writer.write0(userList, sheet, table);
 
                }
            }
        }
    }
 
    writer.finish();
}

三、附一个将对象字段，按顺序转为list的工具

/**
     * excel导出:字段数据映射
     *  为null的返回空串
     * dataList 对象
     * 需要转换的字段集合，顺序也是按照这个
     */
    public static <T> List<List<String>> getObjectData2(List<T> dataList, List<String> fields) {
        if (Objects.isNull(dataList) || Objects.isNull(fields)) {
            return null;
        }
        List<List<String>> result = new ArrayList<>();
        Map<String, String> map;
        for (T entity : dataList) {
            try {
                List<String> data = new ArrayList<>();
                map = BeanUtils.describe(entity);
                String value;
                for (String field : fields) {
                    value = map.get(field);
                    value = StringUtils.isBlank(value) ? "" : value;
                    data.add(value);
                }
                map.clear();
                result.add(data);
            } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                //log.error(e.getMessage(), e);
                e.printStackTrace();
            }
        }
        map = null;
        return result;
    }