• Java 使用 Easyexcel 导出大量数据

    读Excel | Easy Excel

    1. 1、 我遇到的数据量超级大，使用传统的POI方式来完成导入导出很明显会内存溢出，并且效率会非常低；
    2.  
    3. 2、 数据量大直接使用select * from tableName肯定不行，一下子查出来300w条数据肯定会很慢；
    4.  
    5. 3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中，这样效率会非常低；估计打开都得几分钟；
    6.  
    7. 4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行；
    8.  
    9. 5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行；
    10.  
    11. 6、导入时300w数据如果使用Mybatis的批量插入肯定不行，因为Mybatis的批量插入其实就是SQL的循环；一样很慢。

    准备工作

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

    1.    <!--EasyExcel相关依赖-->
    2.         <dependency>
    3.             <groupId>com.alibaba</groupId>
    4.             <artifactId>easyexcel</artifactId>
    5.             <version>3.0.5</version>
    6.         </dependency>

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

    1. CREATE TABLE dept( /*部门表*/
    2. deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
    3. dname VARCHAR(20)  NOT NULL  DEFAULT "",
    4. loc VARCHAR(13) NOT NULL DEFAULT ""
    5. ) ;
    6.  
    7. #创建表EMP雇员
    8. CREATE TABLE emp
    9. (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
    10. ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
    11. job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
    12. mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
    13. hiredate DATE NOT NULL,/*入职时间*/
    14. sal DECIMAL(7,2)  NOT NULL,/*薪水*/
    15. comm DECIMAL(7,2) NOT NULL,/*红利*/
    16. deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
    17. ) ;
    18.  
    19. #工资级别表
    20. CREATE TABLE salgrade
    21. (
    22. grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    23. losal DECIMAL(17,2)  NOT NULL,
    24. hisal DECIMAL(17,2)  NOT NULL
    25. );
    26.  
    27. #测试数据
    28. INSERT INTO salgrade VALUES (1,700,1200);
    29. INSERT INTO salgrade VALUES (2,1201,1400);
    30. INSERT INTO salgrade VALUES (3,1401,2000);
    31. INSERT INTO salgrade VALUES (4,2001,3000);
    32. INSERT INTO salgrade VALUES (5,3001,9999);
    33.  
    34. delimiter $$
    35.  
    36. #创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
    37. create function rand_string(n INT)
    38. returns varchar(255) #该函数会返回一个字符串
    39. begin
    40. #定义了一个变量 chars_str， 类型  varchar(100)
    41. #默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
    42.  declare chars_str varchar(100) default
    43.    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
    44.  declare return_str varchar(255) default '';
    45.  declare i int default 0; 
    46.  while i < n do
    47.     # concat 函数 : 连接函数mysql函数
    48.    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    49.    set i = i + 1;
    50.    end while;
    51.   return return_str;
    52.   end $$
    53.  
    54.  
    55.  #这里我们又自定了一个函数,返回一个随机的部门号
    56. create function rand_num( )
    57. returns int(5)
    58. begin
    59. declare i int default 0;
    60. set i = floor(10+rand()*500);
    61. return i;
    62. end $$
    63.  
    64.  #创建一个存储过程， 可以添加雇员
    65. create procedure insert_emp(in start int(10),in max_num int(10))
    66. begin
    67. declare i int default 0;
    68. #set autocommit =0 把autocommit设置成0
    69.  #autocommit = 0 含义: 不要自动提交
    70.  set autocommit = 0; #默认不提交sql语句
    71.  repeat
    72.  set i = i + 1;
    73.  #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
    74.  insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    75.   until i = max_num
    76.  end repeat;
    77.  #commit整体提交所有sql语句，提高效率
    78.    commit;
    79.  end $$
    80.  
    81.  #添加8000000数据
    82. call insert_emp(100001,8000000)$$
    83.  
    84. #命令结束符，再重新设置为;
    85. delimiter ;

    3.实体类

    1. @Data
    2. @NoArgsConstructor
    3. @AllArgsConstructor
    4. public class Emp implements Serializable {
    5.     @ExcelIgnore
    6.     private Integer empno;
    7.  
    8.     @ExcelProperty(value = "员工名称")
    9.     private String ename;
    10.  
    11.     @ExcelProperty(value = "工作")
    12.     private String job;
    13.  
    14.     @ExcelProperty(value = "主管编号")
    15.     private Integer mgr;
    16.  
    17.     @ExcelProperty(value = "入职日期")
    18.     private Date hiredate;
    19.  
    20.     @ExcelProperty(value = "薪资")
    21.     private BigDecimal sal;
    22.  
    23.     @ExcelProperty(value = "奖金")
    24.     private BigDecimal comm;
    25.  
    26.     @ExcelProperty(value = "所属部门")
    27.     private Integer deptno;
    28.  
    29. }

    4.vo类
    1. @Data
    2. public class EmpVo {
    3.  
    4.      @ExcelIgnore
    5.     private Integer empno;
    6.  
    7.     @ExcelProperty(value = "员工名称")
    8.     private String ename;
    9.  
    10.     @ExcelProperty(value = "工作")
    11.     private String job;
    12.  
    13.     @ExcelProperty(value = "主管编号")
    14.     private Integer mgr;
    15.  
    16.     @ExcelProperty(value = "入职日期")
    17.     private Date hiredate;
    18.  
    19.     @ExcelProperty(value = "薪资")
    20.     private BigDecimal sal;
    21.  
    22.     @ExcelProperty(value = "奖金")
    23.     private BigDecimal comm;
    24.  
    25.     @ExcelProperty(value = "所属部门")
    26.     private Integer deptno;
    27.  
    28. }

    5、导出核心代码

    1. @Resource
    2. private EmpService empService;
    3. /**
    4.  * 分批次导出
    5.  */
    6. @GetMapping("/export")
    7. public void export() throws IOException {
    8.    
    9.      Long startTime = System.currentTimeMillis();
    10.      empService.export(); //导出
    11.      Long endTime = System.currentTimeMillis();
    12.      Long elapsedTime = (endTime - startTime) / 1000;
    13.      System.out.println("导出_方式耗时：" + elapsedTime + "s");
    14.  
    15. }
    1. public class ExcelConstants {
    2.  //一个sheet装100w数据
    3.     public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    4.     //每次查询20w数据，每次写入20w数据
    5.     public static final Integer PER_WRITE_ROW_COUNT = 200000;
    6. }

    实现类中：
    1. @Override
    2. public void export() throws IOException {
    3.     OutputStream outputStream =null;
    4.     try {
    5.         //记录总数:实际中需要根据查询条件进行统计即可     
    6.         Integer totalCount = empMapper.selectCount(null);
    7.         //每一个Sheet存放100w条数据
    8.         Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
    9.         //每次写入的数据量20w,每页查询20W
    10.         Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
    11.         //计算需要的Sheet数量
    12.         Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
    13.         //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
    14.             Integer oneSheetWriteCount = sheetDataRows % writeDataRows == 0 ? (sheetDataRows / writeDataRows) : (sheetDataRows / writeDataRows + 1);
    15.             //计算最后一个sheet需要写入的次数
    16.             Integer lastCountAll = totalCount - (sheetNum-1)*sheetDataRows;
    17.             Integer lastSheetWriteCount = lastCountAll % writeDataRows == 0 ? (lastCountAll / writeDataRows) : (lastCountAll / writeDataRows + 1);
    18.  
    19.         ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    20.         HttpServletResponse response = requestAttributes.getResponse();
    21.         outputStream = response.getOutputStream();
    22.         //必须放到循环外，否则会刷新流
    23.         ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
    24.  
    25.         //开始分批查询分次写入
    26.         for (int i = 0; i < sheetNum; i++) {
    27.             //创建Sheet
    28.             WriteSheet sheet = new WriteSheet();
    29.             sheet.setSheetName("测试Sheet1"+i);
    30.             sheet.setSheetNo(i);
    31.             //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
    32.             for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
    33.                 //分页查询一次20w
    34.                 Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);
    35.                 List<Emp> empList = page.getRecords();
    36.  
    37. //使用pagehelper的如下
    38. // int  pageNum = j + 1 + oneSheetWriteCount * i;                    //PageHelper.startPage(pageNum,writeDataRows,getOrderBy(pageable.getSort())).setReasonable(true);
    39. //List<DqtbsHiddenDangerQuery> list = mapper.expmortByPage(dto);
    40.  
    41.  
    42. //封装成可以导出实体类
    43.                 List<EmpVo> empVoList = new ArrayList<>();
    44.                 for (Emp emp : empList) {
    45.                     EmpVo empVo = new EmpVo();
    46.                     BeanUtils.copyProperties(emp, empVo);
    47.                     empVoList.add(empVo);
    48.                 }
    49.                 WriteSheet writeSheet = EasyExcel.writerSheet(i, "员工信息" + (i + 1)).head(EmpVo.class)
    50.                         .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
    51.                 //写数据
    52.                 excelWriter.write(empVoList, writeSheet);
    53.             }
    54.         }
    55.         // 下载EXCEL
    56.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    57.         response.setCharacterEncoding("utf-8");
    58.         // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
    59.         String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
    60.         response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    61.         excelWriter.finish();
    62.         outputStream.flush();
    63.     } catch (IOException e) {
    64.         e.printStackTrace();
    65.     } catch (BeansException e) {
    66.         e.printStackTrace();
    67.     }finally {
    68.         if (outputStream != null) {
    69.             outputStream.close();
    70.         }
    71.     }
    72. }

    前端vue的：

    1.   //导出
    2.   exportExcel(values) {
    3.     let this_ = this
    4.     this_.spinningExport = true
    5.     this.$api.export(BASE_URL + 'exportNew', values, `数据查询_${moment(new Date()).format('YYYY-MM-DD')}.xlsx`, {
    6.       success() {
    7.         this_.spinningExport = false
    8.       },
    9.       fail() {
    10.         this_.spinningExport = false
    11.       }
    12.     })
    13.   },

    分批量查询，例如，一个sheet 页 存储 10000条，分页每次查1000条，每个sheet需要查10次。总数据50000条，就分了5个sheet页来显示，大批量数据来说，easyExcel还是很好用的。

  • 相关阅读:
    【Linux基本指令（2)】几十条指令快速入手Linux/深入理解什么是指令
    阿里架构师：对于 Kafka 的消费者客户端详解，你都明白吗？
    内网穿透的应用-本地部署Stackedit Markdown编辑器公网远程访问
    php中进程、线程、协程详细讲解
    【redis】(一)使用docker安装redis、常用五大基本数据类型、Jedis操作Redis、Spring整合Redis
    用Java语言创建的Spring Boot项目中，如何传递List集合呢？
    毕业设计 基于大数据的高校校园学生一卡通数据分析
    VUE3学习 第六章 V3自动引入插件、深入v-model、自定义指令directive、自定义Hooks、编写Vue3插件、
    视频云存储/安防监控/AI分析/视频AI智能分析网关：占道经营算法
    字符串转数字， 数字转字符串
  • 原文地址：https://blog.csdn.net/u011518709/article/details/133746590