基于EasyExcel实现百万级数据导出

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

基于EasyExcel实现百万级数据导出

在项目开发中往往需要使用到数据的导入和导出导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。

大数据的导入和导出相信大家在日常的开发、面试中都会遇到。

很多问题只要这一次解决了总给复盘记录后期遇到同样的问题就好解决了。好啦废话不多说开始正文

1.传统POI的的版本优缺点比较

其实想到数据的导入导出理所当然的会想到apache的poi技术以及Excel的版本问题。

  • HSSFWorkbook

这个实现类是我们早期使用最多的对象它可以操作Excel2003以前（包含2003的所有Excel版本。在2003以前Excel的版本后缀还是.xls

  • XSSFWorkbook

这个实现类现在在很多公司都可以发现还在使用它是操作的Excel2003–Excel2007之间的版本Excel的扩展名是.xlsx

  • SXSSFWorkbook

这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

HSSFWorkbook

它是POI版本中最常用的方式不过:

  • 它的缺点是 最多只能导出 65535行也就是导出的数据函数超过这个数据就会报错;
  • 它的优点是 不会报内存溢出。（因为数据量还不到7w所以内存一般都够用首先你得明确知道这种方式是将数据先读取到内存中然后再操作

XSSFWorkbook

  • 优点：这种形式的出现是为了突破HSSFWorkbook的65535行局限是为了针对Excel2007版本的1048576行16384列最多可以导出104w条数据；
  • 缺点：伴随的问题来了虽然导出数据行数增加了好多倍但是随之而来的内存溢出问题也成了噩梦。因为你所创建的bookSheetrowcell等在写入到Excel之前都是存放在内存中的（这还没有算Excel的一些样式格式等等可想而知内存不溢出就有点不科学了

SXSSFWorkbook

从POI 3.8版本开始提供了一种基于XSSF的低内存占用的SXSSF方式：

优点：

  • 这种方式不会一般不会出现内存溢出（它使用了硬盘来换取内存空间
  • 也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来而内存中存的都是最新的数据
  • 并且支持大型Excel文件的创建（存储百万条数据绰绰有余。

缺点：

  • 既然一部分数据持久化到了硬盘中且不能被查看和访问那么就会导致
  • 在同一时间点我们只能访问一定数量的数据也就是内存中存储的数据;
  • sheet.clone()方法将不再支持,还是因为持久化的原因;
  • 不再支持对公式的求值还是因为持久化的原因在硬盘中的数据没法读取到内存中进行计算；
  • 在使用模板方式下载数据的时候不能改动表头还是因为持久化的问题写到了硬盘里就不能改变了；

2.使用方式哪种看情况

经过了解也知道了这三种Workbook的优点和缺点那么具体使用哪种方式还是需要看情况的：

我一般会根据这样几种情况做分析选择：

1、当我们经常导入导出的数据不超过7w的情况下可以使用 HSSFWorkbook 或者 XSSFWorkbook都行；

2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式公式格式等操作的情况下推荐使用SXSSFWorkbook;

3、当数据量查过7w并且我们需要操做Excel中的表头样式公式等这时候我们可以使用 XSSFWorkbook 配合进行分批查询分批写入Excel的方式来做；

3.百万数据导入导出

想要解决问题我们首先要明白自己遇到的问题是什么？

1、 我遇到的数据量超级大使用传统的POI方式来完成导入导出很明显会内存溢出并且效率会非常低；

2、 数据量大直接使用select * from tableName肯定不行一下子查出来300w条数据肯定会很慢；

3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中这样效率会非常低；估计打开都得几分钟；

4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行；

5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行；

6、导入时300w数据如果使用Mybatis的批量插入肯定不行因为Mybatis的批量插入其实就是SQL的循环；一样很慢。

解决思路：

  • 针对1 ：

其实问题所在就是内存溢出我们只要使用对上面介绍的POI方式即可主要问题就是原生的POI解决起来相当麻烦。

经过查阅资料翻看到阿里的一款POI封装工具EasyExcel上面问题等到解决；

  • 针对2：

不能一次性查询出全部数据我们可以分批进行查询只不过时多查询几次的问题况且市面上分页插件很多。此问题好解决。

  • 针对3：

可以将300w条数据写到不同的Sheet中每一个Sheet写一百万即可。

  • 针对4：

不能一行一行的写入到Excel上我们可以将分批查询的数据分批写入到Excel中。

  • 针对5：

导入到DB时我们可以将Excel中读取的数据存储到集合中到了一定数量直接批量插入到DB中。

  • 针对6：

不能使用Mybatis的批量插入我们可以使用JDBC的批量插入配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。

4.代码实现

需求：使用EasyExcel完成500w数据的导出。

500w数据的导出解决思路：

  • 首先在查询数据库层面需要分批进行查询（比如每次查询20w
  • 每查询一次结束就使用EasyExcel工具将这些数据写入一次；
  • 当一个Sheet写满了100w条数据开始将查询的数据写入到另一个Sheet中；
  • 如此循环直到数据全部导出到Excel完毕。

ps:我们需要计算Sheet个数以及循环写入次数。特别是最后一个Sheet的写入次数

因为你不知道最后一个Sheet会写入多少数据可能是100w也可能是25w因为我们这里的500w只是模拟数据有可能导出的数据比500w多也可能少

ps:我们需要计算写入次数因为我们使用的分页查询所以需要注意写入的次数。

其实查询数据库多少次就是写入多少次

准备工作

1.基于maven搭建springboot工程引入easyexcel依赖这里我是用的时3.0版本

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.0.5</version>
</dependency>

2.创建海量数据的sql脚本

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数名字 rand_string可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #创建一个存储过程 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整体提交所有sql语句提高效率
   commit;
 end $$

 #添加8000000数据
call insert_emp(100001,8000000)$$

#命令结束符再重新设置为;
delimiter ;

3.实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp implements Serializable {
    @ExcelProperty(value = "员工编号")
    private Integer empno;

    @ExcelProperty(value = "员工名称")
    private String ename;

    @ExcelProperty(value = "工作")
    private String job;

    @ExcelProperty(value = "主管编号")
    private Integer mgr;

    @ExcelProperty(value = "入职日期")
    private Date hiredate;

    @ExcelProperty(value = "薪资")
    private BigDecimal sal;

    @ExcelProperty(value = "奖金")
    private BigDecimal comm;

    @ExcelProperty(value = "所属部门")
    private Integer deptno;

}

4.vo类

@Data
public class EmpVo {

    @ExcelProperty(value = "员工编号")
    private Integer empno;

    @ExcelProperty(value = "员工名称")
    private String ename;

    @ExcelProperty(value = "工作")
    private String job;

    @ExcelProperty(value = "主管编号")
    private Integer mgr;

    @ExcelProperty(value = "入职日期")
    private Date hiredate;

    @ExcelProperty(value = "薪资")
    private BigDecimal sal;

    @ExcelProperty(value = "奖金")
    private BigDecimal comm;

    @ExcelProperty(value = "所属部门")
    private Integer deptno;

}

导出核心代码

public class ExcelConstants {
	//一个sheet装100w数据
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    //每次查询20w数据每次写入20w数据
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

@Override
public void export() throws IOException {
    OutputStream outputStream =null;
    try {
        //记录总数:实际中需要根据查询条件进行统计即可
        //LambdaQueryWrapper<Emp> lambdaQueryWrapper = new QueryWrapper<Emp>().lambda().eq(Emp::getEmpno, 1000001);
        Integer totalCount = empMapper.selectCount(null);
        //每一个Sheet存放100w条数据
        Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
        //每次写入的数据量20w,每页查询20W
        Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
        //计算需要的Sheet数量
        Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
        //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
        Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
        //计算最后一个sheet需要写入的次数
        Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        outputStream = response.getOutputStream();
        //必须放到循环外否则会刷新流
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

        //开始分批查询分次写入
        for (int i = 0; i < sheetNum; i++) {
            //创建Sheet
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetName("测试Sheet1"+i);
            sheet.setSheetNo(i);
            //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
            for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                //分页查询一次20w
                Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);
                List<Emp> empList = page.getRecords();
                List<EmpVo> empVoList = new ArrayList<>();
                for (Emp emp : empList) {
                    EmpVo empVo = new EmpVo();
                    BeanUtils.copyProperties(emp, empVo);
                    empVoList.add(empVo);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "员工信息" + (i + 1)).head(EmpVo.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                //写数据
                excelWriter.write(empVoList, writeSheet);
            }
        }
        // 下载EXCEL
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (BeansException e) {
        e.printStackTrace();
    }finally {
        if (outputStream != null) {
            outputStream.close();
        }
    }
}

这是我电脑测试时内存占用和CPU使用情况当然开了其他一些应用。

image-20230119203725607

导出500w数据共计耗时可以看到差不多400s左右当然还要考虑业务复杂度已经电脑配置我这里只是一个导出的demo并不涉及其他业务逻辑在实际开发中可能时间会比这个更长一些

image-20230119204722601

看下导出效果,我上面的脚本向插入了500w数据100w一个sheet因此正好五个

image-20230119205106593

image-20230119204933287

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
返回列表

上一篇：【机器学习】XGBClassifier的默认参数和调参总结

下一篇：【Pytorch基础（3）】张量的拼接，拆分与统计

“基于EasyExcel实现百万级数据导出” 的相关文章

为什么数组下标是从 0 开始，而不是 1？1年前 (2023-02-02)
可以一学的代码优化小技巧：减少if-else冗余1年前 (2023-02-02)
垃圾回收机制1年前 (2023-02-02)
day06-Spring管理Bean-IOC-041年前 (2023-02-02)
AI修复照片1年前 (2023-02-02)
04HDFS简介1年前 (2023-02-02)
OpenMP 线程同步 Construct 实现原理以及源码分析（下）1年前 (2023-02-02)
portswigger 靶场之 XSS 篇 （下）1年前 (2023-02-02)
【数据结构】LeetCode升级版的环形链表，复制带随机指针的链表1年前 (2023-02-02)
Dubbo 入门系列之快速部署一个微服务应用1年前 (2023-02-02)

阿里云国际版