乐趣区

关于java:100W-数据导出-Excel不卡死不内存溢出这个厉害了

jiezi

一. 简介

导出是后盾管理系统的罕用性能，当数据量特地大的时候会内存溢出和卡顿页面，已经本人封装过一个导出，采纳了分批查问数据来防止内存溢出和应用 SXSSFWorkbook 形式缓存数据到文件上以解决下载大文件 EXCEL 卡死页面的问题。

不过一是存在封装不太敌对应用不不便的问题，二是这些 poi 的操作形式依然存在内存占用过大的问题, 三是存在空循环和整除的时候数据有缺点的问题，以及存在内存溢出的隐患。

无意间查问到阿里开源的 EasyExcel 框架，发现能够将解析的 EXCEL 的内存占用管制在 KB 级别，并且相对不会内存溢出（外部实现待钻研）, 还有就是速度极快，大略 100W 条记录，十几个字段，只须要 70 秒即可实现下载。

遂摈弃本人封装的，转战钻研阿里开源的 EasyExcel. 不过 说实话，过后本人封装的那个还是有些技术含量的，例如：外观模式，模板办法模式，以及委托思维，组合思维，能够看看。

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

二. 案例

2.1 POM 依赖

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

2.2 POJO 对象

package com.authorization.privilege.excel;
 
import java.util.Date;
 
/**
 * @author qjwyss
 * @description
 */
public class User {
 
    private String uid;
    private String name;
    private Integer age;
    private Date birthday;
 
    public User() {}
 
    public User(String uid, String name, Integer age, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }
 
    public String getUid() {return uid;}
 
    public void setUid(String uid) {this.uid = uid;}
 
    public String getName() {return name;}
 
    public void setName(String name) {this.name = name;}
 
    public Integer getAge() {return age;}
 
    public void setAge(Integer age) {this.age = age;}
 
    public Date getBirthday() {return birthday;}
 
    public void setBirthday(Date birthday) {this.birthday = birthday;}
}

2.3 测试环境

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

2.4 生产环境

2.4.0.Excel 常量类
package com.authorization.privilege.constant;
 
/**
 * @author qjwyss
 * @description EXCEL 常量类
 */
public class ExcelConstant {
 
    /**
     * 每个 sheet 存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
 
    /**
     * 每次向 EXCEL 写入的记录数(查问每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
 
}

注：为了书写不便，此处俩个必须要整除，能够省去很多不必要的判断。另外如果本人测试，能够改为 100,20。

2.4.1. 数据量少的(20W 以内吧)：一个 SHEET 一次查问导出
@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

    ServletOutputStream out = null;
    try {out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 设置 EXCEL 名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

        // 设置 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("零碎名称"));
        titles.add(Arrays.asList("零碎标识"));
        titles.add(Arrays.asList("形容"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创立工夫"));
        table.setHead(titles);

        // 查数据写 EXCEL
        List<List<String>> dataList = new ArrayList<>();
        List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
        if (!CollectionUtils.isEmpty(sysSystemVOList)) {
            sysSystemVOList.forEach(eachSysSystemVO -> {
                dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),
                        eachSysSystemVO.getSystemKey(),
                        eachSysSystemVO.getDescription(),
                        eachSysSystemVO.getState().toString(),
                        eachSysSystemVO.getCreateUid(),
                        eachSysSystemVO.getCreateTime().toString()
                ));
            });
        }
        writer.write0(dataList, sheet, table);

        // 下载 EXCEL
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();} finally {if (out != null) {
            try {out.close();
            } catch (Exception e) {e.printStackTrace();
            }
        }
    }

    return ResultVO.getSuccess("导出零碎列表 EXCEL 胜利");
}
2.4.2. 数据量适中（100W 以内）：一个 SHEET 分批查问导出
@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

    ServletOutputStream out = null;
    try {out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 设置 EXCEL 名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

        // 设置 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("零碎名称"));
        titles.add(Arrays.asList("零碎标识"));
        titles.add(Arrays.asList("形容"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创立工夫"));
        table.setHead(titles);

        // 查问总数并【封装相干变量 这块间接拷贝就行 不要改变】Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);

        // 写数据 这个 i 的最大值间接拷贝就行了 不要改
        for (int i = 0; i < writeCount; i++) {List<List<String>> dataList = new ArrayList<>();

            // 此处查问并封装数据即可 currentPage, pageSize 这个变量封装好的 不要改变
            PageHelper.startPage(i + 1, pageSize);
            List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
            if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                sysSystemVOList.forEach(eachSysSystemVO -> {
                    dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),
                            eachSysSystemVO.getSystemKey(),
                            eachSysSystemVO.getDescription(),
                            eachSysSystemVO.getState().toString(),
                            eachSysSystemVO.getCreateUid(),
                            eachSysSystemVO.getCreateTime().toString()
                    ));
                });
            }
            writer.write0(dataList, sheet, table);
        }

        // 下载 EXCEL
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();} finally {if (out != null) {
            try {out.close();
            } catch (Exception e) {e.printStackTrace();
            }
        }
    }

    return ResultVO.getSuccess("导出零碎列表 EXCEL 胜利");
}
2.4.3. 数据里很大（几百万都行）：多个 SHEET 分批查问导出
@Override
public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

    ServletOutputStream out = null;
    try {out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 设置 EXCEL 名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

        // 设置 SHEET 名称
        String sheetName = "零碎列表 sheet";

        // 设置题目
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<List<String>>();
        titles.add(Arrays.asList("零碎名称"));
        titles.add(Arrays.asList("零碎标识"));
        titles.add(Arrays.asList("形容"));
        titles.add(Arrays.asList("状态"));
        titles.add(Arrays.asList("创建人"));
        titles.add(Arrays.asList("创立工夫"));
        table.setHead(titles);

        // 查问总数并封装相干变量(这块间接拷贝就行了不要改)
        Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);
        Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;
        Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
        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);

            // 写数据 这个 j 的最大值判断间接拷贝就行了，不要改变
            for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {List<List<String>> dataList = new ArrayList<>();

                // 此处查问并封装数据即可 currentPage, pageSize 这俩个变量封装好的 不要改变
                PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);
                List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);
                if (!CollectionUtils.isEmpty(sysSystemVOList)) {
                    sysSystemVOList.forEach(eachSysSystemVO -> {
                        dataList.add(Arrays.asList(eachSysSystemVO.getSystemName(),
                                eachSysSystemVO.getSystemKey(),
                                eachSysSystemVO.getDescription(),
                                eachSysSystemVO.getState().toString(),
                                eachSysSystemVO.getCreateUid(),
                                eachSysSystemVO.getCreateTime().toString()
                        ));
                    });
                }
                writer.write0(dataList, sheet, table);
            }
        }

        // 下载 EXCEL
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        writer.finish();
        out.flush();} finally {if (out != null) {
            try {out.close();
            } catch (Exception e) {e.printStackTrace();
            }
        }
    }

    return ResultVO.getSuccess("导出零碎列表 EXCEL 胜利");
}

三、总结

造的假数据，100W 条记录，18 个字段，测试导出是 70s。在实际上产环境应用的时候，具体的还是要看本人写的 sql 的性能。sql 性能快的话，会很快。

有一点举荐一下：在做分页的时候应用单表查问，对于所须要解决的外键对应的冗余字段，在里面一次性查出来放到 map 外面(举荐应用 @MapKey 注解)，而后遍历 list 的时候依据外键从 map 中获取对应的名称。

一个主旨：少发查问 sql, 能力更快的导出。

题外话：如果数据量过大，在应用 count(1)查问总数的时候会很慢，能够通过调整 mysql 的缓冲池参数来放慢查问。

还有就是遇到了一个问题，应用 pagehelper 的时候，数据量大的时候，limit 0,20W, limit 20W,40W, limit 40W,60W, limit 60W,80W 查问有的时候会很快，有的时候会很慢，待钻研。

原文链接：https://blog.csdn.net/qq_3520…

版权申明：本文为 CSDN 博主「请叫我猿叔叔」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2021 最新版)

2. 别在再满屏的 if/ else 了，试试策略模式，真香！！

3. 卧槽！Java 中的 xx ≠ null 是什么新语法？

4.Spring Boot 2.6 正式公布，一大波新个性。。

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

退出移动版