EasyExcel


前言

导出是后台管理系统的常用功能,当数据量特别大的时候会内存溢出和卡顿页面,曾经自己封装过一个导出,POI百万级大数据量EXCEL导出 采用了分批查询数据来避免内存溢出和使用SXSSFWorkbook方式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。不过一是存在封装不太友好使用不方便的问题,二是这些poi的操作方式仍然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺陷的问题,以及存在内存溢出的隐患。阿里开源的EasyExcel框架,可以将解析的EXCEL的内存占用控制在KB级别,并且绝对不会内存溢出(内部实现待研究),还有就是速度极快, 大概100W条记录,十几个字段, 只需要70秒即可完成下载。遂抛弃自己封装的,转战研究阿里开源的EasyExcel

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

今天博主将为大家分享7 行代码优雅地实现 Excel 文件导出功能?(阿里出品的 EasyExcel,安利一波),不喜勿喷,如有异议欢迎讨论!


入题

关于导出 Excel 文件,可以说是大多数服务中都需要集成的功能。那么,要如何去实现这个功能呢?

你可能第一想法是:这还不简单?用 Apache 开源框架 poi, 或者 jxl 都可以实现啊。面向百度编程,把代码模板 copy 下来,根据自己的业务再改改,能有多难?

嗯… 的确不难,但是你的代码可能是下面这个熊样子的:

easyExcel大数据量导出resp_List

上面这段代码看上去是不是又臭又长呢?今天,教您如何使用 7 行代码搞定 Excel 文件生成功能!


Apache poi、jxl 的缺陷

在说如何实现之前,我们先来讨论一下传统 Excel 框架的不足!除了上面说的,Apache poi、jxl 都存在生成 excel 文件不够简单优雅快速外,它们都还存在一个严重的问题,那就是非常耗内存,严重时会导致内存溢出。

POI 虽然目前来说,是 excel 解析框架中被使用最广泛的,但这个框架并不完美。

为什么这么说呢?

开发者们大部分使用 POI,都是使用其 userModel 模式。而 userModel 的好处是上手容易使用简单,随便拷贝个代码跑一下,剩下就是写业务转换了,虽然转换也要写上百行代码,但是还是可控的。

然而 userModel 模式最大的问题是在于,对内存消耗非常大,一个几兆的文件解析甚至要用掉上百兆的内存。现实情况是,很多应用现在都在采用这种模式,之所以还正常在跑是因为并发不大,并发上来后,一定会OOM或者频繁的 full gc。


阿里出品的 EasyExcel,安利一波

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便


POM依赖
<!-- 阿里开源EXCEL -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>

七行代码搞定 Excel 生成

easyExcel大数据量导出resp_Excel文件导出_02

***上面这段示例代码中,有两个点很重要,已经重点标注标:***

  • ①:WriteModel 这个对象就是要写入 Excel 的数据模型对象,等等,你这好像不行吧?表头 head,以及每个单元格内的数据顺序都没指定,能达到想要的效果么?别急,后面会讨论这块!
  • ②:创建需要写入的数据集,当然了,正常业务中,这块都是从数据库中查询出来的。

如果说写入的数据量很大,需要做分片查询再写入的处理,否则可能会 OOM(Out of Memory).

回过头来,我们来看看 WriteModel 这个对象内部到底有什么幺蛾子!

easyExcel大数据量导出resp_easyExcel大数据量导出resp_03


ExayExcel 提供注解的方式, 来方便的定义 Excel 需要的数据模型:

  • ①:首先,定义的写入模型必须要继承自 BaseRowModel.java;
  • ②:通过 @ExcelProperty 注解来指定每个字段的列名称,以及下标位置;

同时,上面定义的 createModelList() 方法也很简单,通过循环,创建一个写入模型的 List 集合:

easyExcel大数据量导出resp_easyExcel大数据量导出resp_04


特殊场景支持
动态生成 Excel 内容

上面的例子是基于注解的,也就是说表头 head, 以及内容都是写死的,换句话说,我定义好了一个数据模型,那么,生成的 Excel 文件也就是只能遵循这种模型来了,但是,实际业务中可能会存在动态变化的需求,要怎么做呢?

easyExcel大数据量导出resp_easyExcel大数据量导出resp_05

  • ①:无注解模式,动态添加表头,也可自由组合复杂表头,代码如下:

easyExcel大数据量导出resp_List_06

  • ②:创建动态数据,注意这里的数据类型是 Object:

easyExcel大数据量导出resp_List_07

自定义表头以及内容样式

easyExcel大数据量导出resp_easyExcel大数据量导出resp_08


我们复用了上面的示例代码,并额外添加了设置自定义表格样式的代码, createTableStytle()具体内容如下:

easyExcel大数据量导出resp_easyExcel大数据量导出resp_09


我们可以通过 TableStyle 这个类来设置表头、表格主题的样式。

合并单元格

我们可以通过 merge() 方法来合并单元格:

easyExcel大数据量导出resp_阿里出品_10

自定义处理

对于更复杂的处理,EasyExcel 预留了 WriterHandler 接口来,允许你自定义处理代码:

easyExcel大数据量导出resp_easyExcel大数据量导出resp_11


接口中定义了三个方法:

  • sheet(): 在创建每个 sheet 后自定义业务逻辑处理;
  • row(): 在创建每个 row 后自定义业务逻辑处理;
  • cell(): 在创建每个 cell 后自定义业务逻辑处理;

我们实现了该接口后,编写自定义逻辑处理代码,然后调用 getWriterWithTempAndHandler()静态方法获取 ExcelWriter 对象时,传入 WriterHandler 的实现类即可。

easyExcel大数据量导出resp_阿里出品_12

ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true, new MyWriterHandler());

Web 下载示例代码
public class Down {

		@GetMapping("/a.htm")

		public void cooperation(HttpServletRequest request, HttpServletResponse response) {

			ServletOutputStream out = response.getOutputStream();

			ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);

			String fileName = new String(("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), "UTF-8");

			Sheet sheet1 = new Sheet(1, 0);
			sheet1.setSheetName("第一个sheet");
			writer.write0(getListString(), sheet1);
			writer.finish();
			response.setContentType("multipart/form-data");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
			out.flush();
		}
	}

}

POJO对象(来一波栗子)
@Data
public class User {
 
    private String uid;
    private String name;
    private Integer age;
    private Date birthday;
 
}

生产环境

Excel常量类

package com.authorization.privilege.constant;
 
/**
 * @author ChenYongJia
 * @date 2019/5/15
 * @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。

数据量少的(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=" + fileName + ".xlsx");
            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以内): 一个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=" + fileName + ".xlsx");
            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成功");
    }

数据里很大(几百万都行): 多个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=" + fileName + ".xlsx");
            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性能快的话,会很快

数据量过大,在使用count(1)查询总数的时候会很慢,可以通过调整mysql的缓冲池参数来加快查询,,数据量大的时候,limit 0,20W; limit 20W,40W, limit 40W,60W, limit 60W,80W 查询会很快。


到这里7 行代码优雅地实现 Excel 文件导出功能?(阿里出品的 EasyExcel,安利一波),分享完毕了,快去试试吧!


最后

  • 阿里的就是牛逼,阿里的就是牛逼,阿里的就是牛逼,重要的事说三遍!
  • 更多参考精彩博文请看这里:《陈永佳的博客》
  • 喜欢博主的小伙伴可以加个关注、点个赞哦,持续更新嘿嘿!