easyExcel导入导出excel文件

作者: weisen | 来源:发表于2020-03-27 14:42 被阅读0次

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

一、准备工作

使用mybatis-plus来批量保存一个表中的数据

1、新建表

CREATE TABLE `demo_excel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) DEFAULT NULL,
  `content` varchar(256) DEFAULT NULL,
  `page_num` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2、创建批量插入的方法

在service中添加方法

@Override
    public void saveList(List<DemoExcel> list) {
        this.baseMapper.batchInsert(list);
    }

在mapper中添加方法

/**
     * 批量插入
     * @param users
     */
    void batchInsert(List<DemoExcel> users);

在mapper.xml中添加批量插入

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        INSERT INTO `demo_excel`
        (
        title, content, page_num, create_time
        )
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.title}, #{item.content}, #{item.pageNum}, #{item.createTime}
            )
        </foreach>
    </insert>

3、测试

 @Autowired
    private DemoExcelService demoExcelService;

    @Test
    public void testSaveList() {
        DemoExcel excel = new DemoExcel();
        excel.setTitle("标题1");
        excel.setContent("内容11111");
        excel.setPageNum(3);
        excel.setCreateTime(LocalDateTime.now());
        List<DemoExcel> list = new ArrayList<>();
        list.add(excel);
        demoExcelService.saveList(list);
    }

4、创建一张excel表

标题	内容	页码	创建时间
标题1	的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉	5	2019/4/24 16:00
标题2	的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉	5	2019/4/25 16:00
标题3	的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉	5	2019/4/26 16:00
标题4	的路上风景萨拉丁解放啦电缆附件是，的附加赛决定弗拉	5	2019/4/27 16:00

二、导入

1、pom.xml 中添加依赖

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

2、导入导出的对象

不建议直接在 demoExcel 实体类上加 excel 相关注解，所以我们创建了一个专门用于 excel 的类，属性类型这里全部用 String，防止类型不一致无法转换报异常

@Data
public class DemoEasyExcel  {

    @ExcelProperty("标题")
    private String title;

    @ExcelProperty("内容")
    private String content;

    @ExcelProperty("页码")
    private String pageNum;

    @ExcelProperty("创建时间")
    private String createTime;

}

3、创建监听器

package com.vicente.vicenteboot.easyexcel;

public class DemoExcelListener extends AnalysisEventListener<DemoEasyExcel> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoExcelListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoEasyExcel> list = new ArrayList<DemoEasyExcel>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoExcelService demoExcelService;

    public DemoExcelListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoExcelService = new DemoExcelServiceImpl();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoExcelService
     */
    public DemoExcelListener(DemoExcelService demoExcelService) {
        this.demoExcelService = demoExcelService;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoEasyExcel data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoExcelService.saveExcelList(list);
        LOGGER.info("存储数据库成功！");
    }
}

这里有一个saveExcelList方法，主要就是将从excel中读取到的DemoEasyExcel 转换成数据库的类型DemoExcel

@Override
    public void saveExcelList(List<DemoEasyExcel> list) {
        List<DemoExcel> demoList = new ArrayList<>();
        for (DemoEasyExcel easyExcel : list) {
            DemoExcel demo = new DemoExcel();
            demo.setTitle(easyExcel.getTitle());
            demo.setContent(easyExcel.getContent());
            demo.setPageNum(Integer.parseInt(easyExcel.getPageNum()));
            LocalDateTime time = LocalDateTime.parse(easyExcel.getCreateTime(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            demo.setCreateTime(time);
            demoList.add(demo);
        }
        this.saveList(demoList);
    }

4、测试

测试读取excel，并将内容写入到数据库中

@Autowired
    private DemoExcelService demoExcelService;

    @Test
    public void testReadExcel() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = "D://test_excel.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoEasyExcel.class, new DemoExcelListener(demoExcelService)).sheet().doRead();

    }

查看数据库表，成功将excel中的数据写进去

读取第N个sheet

 // 写法2：
    String fileName = "D://test_excel.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();

读取全部的sheet

// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
    EasyExcel.read(fileName, DemoEasyExcel.class, new DemoExcelListener()).doReadAll();

读取部分的sheet

ExcelReader excelReader = EasyExcel.read(fileName).build();
    // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
    ReadSheet readSheet1 =
        EasyExcel.readSheet(0).head(DemoEasyExcel.class).registerReadListener(new DemoExcelListener()).build();
    ReadSheet readSheet2 =
        EasyExcel.readSheet(1).head(DemoEasyExcel.class).registerReadListener(new DemoExcelListener()).build();
    // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
    excelReader.read(readSheet1, readSheet2);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();

三、写出

1、生成写入数据

从数据库中获取到要写入到excel的数据

@Override
    public List<DompRole> selectRoleForExcel() {
        Wrapper<DompRole> queryWrapper = new QueryWrapper<DompRole>().lt("rid", "140382957");
        List<DompRole> resList = this.baseMapper.selectList(queryWrapper);
        return resList;
    }

2、简单写入excel

@Test
    public void testWriteRoleExcel() {
        List<DompRole> list = dompRoleService.selectRoleForExcel();
        System.out.println(list.size());
        // 写法1
        String fileName =  "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DompRole.class).sheet("模板").doWrite(list);

        // 写法2
        fileName = "D://ssimpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = EasyExcel.write(fileName, DompRole.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        excelWriter.write(list, writeSheet);
        /// 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }

查找测试结果，数据可成功写入到excel文档中，表头数据为字段名称

3、修改表头为中文

修改DompRoleExcel，设置中文标题

@Data
public class DompRoleExcel  {

    @ExcelProperty(value = "角色ID",index = 0)
    private Long roleId;

    @ExcelProperty(value = "角色编号",index = 1)
    private Long rid;

    @ExcelProperty(value = "角色名称",index = 2)
    private String roleName;

    @ExcelProperty(value = "角色状态",index = 3)
    private String status;

    @ExcelProperty(value = "角色描述",index = 4)
    private String roleDes;

    @ExcelProperty(value = "创建时间",index = 5)
    private String createDate;

    @ExcelProperty(value = "修改时间",index = 6)
    private String updateDate;

    @ExcelProperty(value = "修改人",index = 7)
    private String modifyUser;


}

修改方法selectRoleForExcel，使返回的是含有ExcelProperty的实体类，将DompRole转换成DompRoleExcel

 public List<DompRoleExcel> selectRoleForExcel() {
        Wrapper<DompRole> queryWrapper = new QueryWrapper<DompRole>().lt("rid", "140382957");
        List<DompRole> roleList = this.baseMapper.selectList(queryWrapper);
        List<DompRoleExcel> resList = transferDompRoleExcel(roleList);
        return resList;
    }

测试

 List<DompRoleExcel> list = dompRoleService.selectRoleForExcel();
        System.out.println(list.size());
        // 写法1
        String fileName =  "D://simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DompRoleExcel.class).sheet("模板").doWrite(list);

4、复杂表头

修改DompRoleExcel，

@Data
public class DompRoleExcel  {

    @ExcelProperty(value = {"角色", "角色ID"})
    private Long roleId;

    @ExcelProperty(value = {"角色", "角色编号"})
    private Long rid;

    @ExcelProperty(value = {"角色", "角色名称"})
    private String roleName;

    @ExcelProperty(value = {"角色", "角色状态"})
    private String status;

    @ExcelProperty(value = {"角色", "角色描述"})
    private String roleDes;

    @ExcelProperty(value = "创建时间")
    private String createDate;

    @ExcelProperty(value = "修改时间")
    private String updateDate;

    @ExcelProperty(value = "修改人")
    private String modifyUser;

}

四、填充

1、简单填充

填写一个模板表格simple.xlsx，内容如下：

姓名	数字	复杂	忽略
{name}	{number}	{name}今年{number}岁了	{name}忽略，{name}

测试填充

// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String templateFileName = "D://simple.xlsx";
        // 方案2 根据Map填充
        String fileName = "D://simpleFill" + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        Map<String, Object> map = new HashMap<>();
        map.put("name", "张三");
        map.put("number", 5.2);
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);

2、列表填充

填充list 的时候还要注意模板中{.} 多了个点表示list

姓名	数字	复杂	忽略
{.name}	{.number}	{.name}今年{.number}岁了	{.name}忽略，{.name}

测试列表

 // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
       String templateFileName = "D://simple.xlsx";

        // 方案1 一下子全部放到内存里面 并填充
        String fileName = "D://listFill" + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭    
        List<Map<String, Object>> list = new ArrayList();
        for (int i=0;i<10;i++){
            Map<String, Object> map = new HashMap<>();
            map.put("name", "张三"+i);
            map.put("number", 5.2+i);
            list.add(map);
        }
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(list);

五、下载模板

1、下载数据模板

数据库模板类

@Data
public class DemoEasyExcel  {

    @ExcelProperty("标题")
    private String title;

    @ExcelProperty("内容")
    private String content;

    @ExcelProperty("页码")
    private String pageNum;

    @ExcelProperty("创建时间")
    private String createTime;

}

生成模板数据

 private List<DemoEasyExcel> data() {
        List<DemoEasyExcel> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoEasyExcel data = new DemoEasyExcel();
            data.setTitle("标题"+i);
            data.setContent("内容" + i);
            data.setPageNum("1"+i);
            data.setCreateTime(LocalDateTime.now().format( DateTimeFormatter.BASIC_ISO_DATE ));
            list.add(data);
        }
        return list;
    }

controller下载模板

/**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoEasyExcel}
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), DemoEasyExcel.class).sheet("模板").doWrite(data());
    }

    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
    @GetMapping("downloadFailedUsingJson")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), DemoEasyExcel.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                    .doWrite(data());
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

2、异常解决方法

调用下载模板的方法，出现异常信息：getOutputStream() has already been called for this response

最后发现是因为使用了aop来打印日志导致的

 @Pointcut("execution(* com.vicente.vicenteboot.controller..*.*(..))")
    public void log(){
    }

    @Around("log()")
    public Object handlerControllerMethod(ProceedingJoinPoint pjp) {
        long startTime = System.currentTimeMillis();
        log.info("args:"+ JSON.toJSONString(pjp.getArgs()));
        ResultBean<?> result;
        try {
            result = (ResultBean<?>) pjp.proceed();
            log.info(pjp.getSignature() + "use time:" + (System.currentTimeMillis() - startTime));
        } catch (Throwable e) {
            result = handlerException(pjp, e);
        }
        return result;
    }

解决办法：新建一个新的包excelController，将对应的controller放到这个包中，这样aop就不会执行里面的方法。

参考文章

easyexcel官方文档

EasyExcel2.0 实现模板下载、导入和导出功能