Java使用EasyExcel实现Excel导入导出功能

1.EasyExcel简介

　　EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。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模式。在上层做了模型转换的封装，让使用者更加简单方便。

2.Java实现EasyExcel操作

2.1 导入依赖

    <dependencies>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
    </dependencies>

2.2 写操作

不存在Excel文件也没关系，EasyExcel会帮我们自动创建

    @Test
    public void test1(){
        List<TestDataEntity> list=new ArrayList<>();
        for (int i=0;i<=10;i++){
            TestDataEntity entity=new TestDataEntity();
            entity.setId(i);
            entity.setName("lucy"+i);
            entity.setAge(30+i);
            list.add(entity);
        }
        String fileName="D:\\upload\\01.xlsx";
        EasyExcel.write(fileName,TestDataEntity.class).sheet("姓名管理").doWrite(list);
    }

2.3 读操作

在实体类加上ExcelProperty注解，用于从Excel中读取的数据进行封装

@Data
@ToString
public class TestDataEntity {
    @ExcelProperty(value = "id",index = 0)
    private int id;
    @ExcelProperty(value = "name",index = 1)
    private String name;
    @ExcelProperty(value = "age",index = 2)
    private Integer age;
}

编写监听器进行读取操作

package com.gh.test;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.Map;

/**
 * @Author Eric
 * @Date 2021/6/6 12:48
 * @Version 1.0
 */
public class ExcelListener extends AnalysisEventListener<TestDataEntity> {
    //一行一行读取Excel数据,从第二行开始读
    @Override
    public void invoke(TestDataEntity testDataEntity, AnalysisContext analysisContext) {
        System.out.println(testDataEntity);
    }
    //读取表头信息
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("===============");
        System.out.println(headMap);
    }
    //读取之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

编写测试方法进行读操作

    @Test
    public void test2(){
        String fileName="D:\\upload\\01.xlsx";
        EasyExcel.read(fileName,TestDataEntity.class,new ExcelListener()).sheet().doRead();
    }

3.EasyExcel整合springboot

3.1 导出到Excel

controller层

    /**
     * 导出数据字典到Excel
     */
    @GetMapping("exportDictToExcel")
    public Result exportDictToExcel(HttpServletResponse response){
        dictService.exportDictToExcel(response);
        return Result.ok();
    }

service层

    /**
     * 导出数据字典
     * @param response
     */
    @Override
    public void exportDictToExcel(HttpServletResponse response) {
        //设置下载信息
        response.setContentType("application/vnd.ms-excel");//application/vnd.ms-excel
        response.setCharacterEncoding("utf-8");
        String filename="dict";
        response.setHeader("Content-disposition", "attachment;filename="+ filename + ".xlsx");
        //查询数据
        List<Dict> dictList = baseMapper.selectList(null);
        List<DictEeVo> list=new ArrayList<>();
        for (Dict dict : dictList) {
            DictEeVo dictEeVo=new DictEeVo();
            BeanUtils.copyProperties(dict,dictEeVo);
            list.add(dictEeVo);
        }
        try {
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet().doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

前端JavaScript：

            exportData() {
                window.location.href = 'http://localhost:8202/admin/cmn/dict/exportDictToExcel'
            },

3.2 从Excel导入

controller层：

    /**
     * 从Excel导入数据到数据库
     */
    @PostMapping("importDictToDatabase")
    public Result importDictToDatabase(MultipartFile file){
        dictService.importDictToDatabase(file);
        return Result.ok();
    }

service层：

    @Override
    public void importDictToDatabase(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

监听器listener：

package com.gh.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.gh.mapper.DictMapper;
import com.gh.model.cmn.Dict;
import com.gh.vo.cmn.DictEeVo;
import org.springframework.beans.BeanUtils;

public class DictListener extends AnalysisEventListener<DictEeVo> {

    private DictMapper dictMapper;
    public DictListener(DictMapper dictMapper) {
        this.dictMapper = dictMapper;
    }

    //一行一行读取
    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
        //调用方法添加数据库
        Dict dict = new Dict();
        BeanUtils.copyProperties(dictEeVo,dict);
        dictMapper.insert(dict);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

表头实体类：

@Data
public class DictEeVo {

    @ExcelProperty(value = "id" ,index = 0)
    private Long id;

    @ExcelProperty(value = "上级id" ,index = 1)
    private Long parentId;

    @ExcelProperty(value = "名称" ,index = 2)
    private String name;

    @ExcelProperty(value = "值" ,index = 3)
    private String value;

    @ExcelProperty(value = "编码" ,index = 4)
    private String dictCode;

}