easyExcel的简单使用方法

为了实现springboot项目对数据库导入导出Excel表格的功能，引入了Alibaba提供的对excel表格操作的工具easyExcel。并且使用该方法写了一个对excel操作的例子，因为使用不熟练，还没有用到读写的进阶操作如合并单元格、设置底色等功能。

首先对pom文件引入相关依赖：

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

与数据库表对应的实体类：

package com.bankcard.project.easyExcel.domain;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelTestTable implements Serializable {

    @ExcelProperty(index = 1,value = "ID")
    private Integer id;
    @ExcelProperty(index = 2,value = "身份证号")
    private String custr;
    @ExcelProperty(index = 3,value = "银行编号")
    private Integer nbr;
    @ExcelProperty(index = 4,value = "收入")
    private Double income;

    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(index = 5,value = "日期")
    private Date dateTime;
}

每个数据表都需要一个对应的实体类来定义excel表的格式。@ExcelProperty(index = 1,value = "ID")，是绑定excel表列的注解，上面第一个参数index指定列的序号，value是显示的列名。

核心类Listener代码:

package com.bankcard.project.easyExcel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.bankcard.project.easyExcel.mapper.ExcelBaseMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class Listener<T, M extends ExcelBaseMapper> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(Listener.class);

    private static final int BATCH_COUNT = 3000;
    List<T> list = new ArrayList<T>();

    private M mapper;

    public Listener(M mapper) {
        this.mapper = mapper;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        System.out.println(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());
        if (list.size()>0){
            mapper.save(list);
        }

        LOGGER.info("存储数据库成功！");
    }

    /**
     * 
     * @param exception
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context){

    }

}

该核心类和官网给出的示例不同，我做出了一部分改动，因为按照官网给的例子，每针对一个表操作都需要创建一个全新的Listener，我在该类中定义了两个泛型，一个是往数据库里添加时的List<T>，另一个是传入的mapper对象M（所有使用的mapper必须继承基础接口），使用这两个泛型后，即使对不同的数据库表操作导入/导出excel表格只需要使用这个公共的Listener即可。

Mapper基础接口：

package com.bankcard.project.easyExcel.mapper;

import com.baomidou.mybatisplus.core.mapper.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ExcelBaseMapper extends Mapper {

    void save(List<?> list);

}

该接口中定义的方法save()的入参list没有指定存储类型，

后续所有对数据库导入/导出操作的接口都需要继承此接口，如我例子中的

ExcelTestTableMapper接口：

package com.bankcard.project.easyExcel.mapper;

import com.bankcard.project.easyExcel.domain.ExcelTestTable;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public interface ExcelTestTableMapper extends ExcelBaseMapper{
    List<ExcelTestTable> queryForPage(Map<String,Integer> map);

    Integer queryCount();
}

该接口的XML文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bankcard.project.easyExcel.mapper.ExcelTestTableMapper">
    <insert id="save" parameterType="java.util.List">
        INSERT INTO EXCEL_TEST_TABLE
        (ID,CUSTR, NBR, INCOME,DATETIME)
        select EXCEL_TEST_TABLE_SEQ.nextval,m.* from (
        <foreach collection="list" item="ExcelTestTable" separator=" union all
                         " open="(" close=")">select
            #{ExcelTestTable.custr}, #{ExcelTestTable.nbr}, #{ExcelTestTable.income},#{ExcelTestTable.dateTime}
            from dual
        </foreach >
        ) m
    </insert>

    <resultMap id="excelTestTableMap" type="com.bankcard.project.easyExcel.domain.ExcelTestTable">
        <result property="id" column="ID"/>
        <result property="custr" column="CUSTR"/>
        <result property="nbr" column="NBR"/>
        <result property="income" column="INCOME"/>
        <result property="dateTime" column="DATETIME"/>
    </resultMap>
    <select id="queryAll" resultMap="excelTestTableMap">
        select * from EXCEL_TEST_TABLE
    </select>
    <select id="queryForPage" resultMap="excelTestTableMap" parameterType="java.util.Map">
        select ID,CUSTR,NBR,INCOME,DATETIME from (select rownum r,e. * from EXCEL_TEST_TABLE e where rownum &lt;= #{endIndex}) t where t.r &gt; #{startIndex}
     </select>

    <select id="queryCount" resultType="Integer">
        select count(1) from EXCEL_TEST_TABLE
    </select>
</mapper>

Service层代码：

package com.bankcard.project.easyExcel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.bankcard.project.easyExcel.domain.ExcelTestTable;
import com.bankcard.project.easyExcel.listener.Listener;
import com.bankcard.project.easyExcel.mapper.ExcelBaseMapper;
import com.bankcard.project.easyExcel.mapper.ExcelTestTableMapper;
import com.bankcard.project.easyExcel.service.ExcelOperateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelOperateServiceImpl implements ExcelOperateService {
    @Autowired
    ExcelTestTableMapper excelTestTableMapper;
    @Override
    public boolean excelRead(String path) {
        String fileName = path + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, ExcelTestTable.class, new Listener(excelTestTableMapper)).sheet().doRead();

        return true;
    }

    @Override
    public boolean excelWrite(String path) {
        Map<String,Integer> map = new HashMap<>();
        List<ExcelTestTable> totalList = new ArrayList<>();
        //起始页
        int currentPage=1;
        //每页数据条数
        int pageSize=10000;
        Integer count = excelTestTableMapper.queryCount();
        for (int i = 1; (i-1)*pageSize <= count; i++) {
            map.put("startIndex",(currentPage-1)*pageSize);
            map.put("endIndex",currentPage*pageSize);
            List<ExcelTestTable> list = excelTestTableMapper.queryForPage(map);
            totalList.addAll(list);
            currentPage=currentPage+1;
            System.out.println(list.size());
        }
        //拿到路径，拼接上文件名
        String fileName= path + "demo.xlsx";
        EasyExcel.write(fileName, ExcelTestTable.class).sheet("模板").doWrite(totalList);
        return true;
    }
}

在该类中定义了两个方法：

1. 传入路径，使用已有的Excel文件，且拥有对应的实体类时，向数据库中导入Excel表中的数据，在该方法中调用了EasyExcel的read()方法，将mapper传入，Listener会调用mapper中的save()方法，按照设定的数量，进行批次插入数据。
2. 传入路径，定义存储数据的list列表，定义每次读取的条数，使用mapper中的查询方法进行简单的分页查询，将一次次的查询结果集中存放到定义的list中，然后使用EasyExcel根据实体类模板导出Excel表格。

Controller类代码：

package com.bankcard.project.easyExcel.controller;

import com.bankcard.project.easyExcel.service.ExcelOperateService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Api(tags = "excel表格操作")
@RestController
@RequestMapping("/excel")
public class ExcelOperateController {
    @Autowired
    ExcelOperateService excelOperateService;

    //C:\\Users\\sinosoft\\Desktop\\
    @ApiOperation("导入excel")
    @GetMapping("/excelInput")
    public int excelInput(String path){
        excelOperateService.excelRead(path);
        return 0;
    }


    @ApiOperation("导出excel")
    @GetMapping("/excelOutput")
    public int excelOutput(String path){

        excelOperateService.excelWrite(path);
        return 0;
    }
}

----------------------------------后续测试

使用swagger接口文档，分别提供一个接口用于测试该例子。

 导出Excel：

        输入导出路径后，控制台打印每一万条一次的记录，如图

直到控制台结束输出，代表该任务执行结束，这时候在之前传入的路径下会看到一个Excel文件已经创建。

导入Excel：

        和导出功能类似，使用该功能需要给出完整文件路径以及文件名，在执行任务时，控制台输出如下：

程序每拿到三千条就去往数据库里进行一次插入，直到控制台提示“所有数据解析完成”，该任务执行完毕，我们查询一下数据库，导入成功。