EasyExcel实现表格导入导出

导入

pom.xml

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

Controller层

1： MultipartFile flie 是从前端接收的excel文件，通过getInputStream()方法转成流。

2：JdJtkExcel.class 是用来接收EasyExcel从流中读取到的表数据

3：JdJtkDataListener 是监听器，通过构造方法传入我们需要的业务逻辑如 代码中的 service

 private JdJtkService service;

    @Autowired
    public void setService(JdJtkService service) {
        this.service = service;
    }

@ApiOperation(value = "Excel导入")
    @ResponseStatus(HttpStatus.CREATED)
    @RequestMapping(value = "/jdJtks/excelImport", method = RequestMethod.POST)
    public void importEmp(@RequestParam("file") MultipartFile file, @RequestParam("userUuid") String userUuid) throws IOException {

        EasyExcel.read(file.getInputStream(), JdJtkExcel.class, new JdJtkDataListener(service, userUuid)).sheet().doRead();

    }

@ApiOperation
是swagger中的注解，其中各参数的含义：value = “接口说明” ，httpMethod = “接口请求方式” ，response = “接口返回参数类型” ，notes = “接口发布说明”
它的用意是方便生成spring restful风格的接口文档，增加代码的可阅读性提高先后端的对接效率。
定义AnalysisEventListener的继承子类

@ResponseStatus
该注解的作用就是会给前端响应页面显示该值注解的值，常用于自定义异常的抛出，我这里当接口被正常调用时会在前端显示HttpStatus.class中的对应内容：
CREATED(value: 201, resaonPhrase: “Created”)

@RequestMapping
用于指定Web请求和Controller层方法之间的映射关系，各属性的作用：
value = “请求的url” , method = “请求方法” , headers = “请求参数及请求头”
也可以使用String MVC新特性 Restful风格的Mapping注解
@GetMapping，处理get请求
@PostMapping，处理post请求
@PutMapping，处理put请求
@DeleteMapping，处理delete请求
举例：@PostMapping(value = “/user/login”)
等价于@RequestMapping(value = “/user/login”,method = RequestMethod.POST)

表数据封装类

import com.alibaba.excel.annotation.ExcelProperty;
import org.springframework.format.annotation.DateTimeFormat;
import io.swagger.annotations.ApiModel;
import java.util.Date;


@ApiModel(description = "Excel")
public class JdJtkExcel {

    @ExcelProperty(value = "商品编号", index = 0)
    private String proNo;

    @ExcelProperty(value = "订单编号", index = 1)
    private String orderNo;

    @ExcelProperty(value = "下单日期", index = 2)
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date orderCreateDate;

    @ExcelProperty(value = "完成日期", index = 3)
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date finishDate;

    @ExcelProperty(value = "是否有效", index = 4)
    private String status;

    @ExcelProperty(value = "商品数量", index = 5)
    private Double proNum;

    @ExcelProperty(value = "计佣金额", index = 6)
    private Double calculateCommission;

    @ExcelProperty(value = "佣金", index = 7)
    private Double commission;

    @ExcelProperty(value = "总佣金", index = 8)
    private Double totalCommission;

    @ExcelProperty(value = "所属计划/活动", index = 9)
    private String activities;

    @ExcelProperty(value = "推客pin", index = 10)
    private String twitterPin;

    @ExcelProperty(value = "ROI", index = 11)
    private String roi;

    public String getProNo() {
        return proNo;
    }

    public void setProNo(String proNo) {
        this.proNo = proNo;
    }

    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public Date getOrderCreateDate() {
        return orderCreateDate;
    }

    public void setOrderCreateDate(Date orderCreateDate) {
        this.orderCreateDate = orderCreateDate;

    }

    public Date getFinishDate() {
        return finishDate;
    }

    public void setFinishDate(Date finishDate) {
        this.finishDate = finishDate;

    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Double getProNum() {
        return proNum;
    }

    public void setProNum(Double proNum) {
        this.proNum = proNum;
    }

    public Double getCalculateCommission() {
        return calculateCommission;
    }

    public void setCalculateCommission(Double calculateCommission) {
        this.calculateCommission = calculateCommission;
    }

    public Double getCommission() {
        return commission;
    }

    public void setCommission(Double commission) {
        this.commission = commission;
    }

    public Double getTotalCommission() {
        return totalCommission;
    }

    public void setTotalCommission(Double totalCommission) {
        this.totalCommission = totalCommission;
    }

    public String getActivities() {
        return activities;
    }

    public void setActivities(String activities) {
        this.activities = activities;
    }

    public String getTwitterPin() {
        return twitterPin;
    }

    public void setTwitterPin(String twitterPin) {
        this.twitterPin = twitterPin;
    }

    public String getRoi() {
        return roi;
    }

    public void setRoi(String roi) {
        this.roi = roi;
    }
}

@ExcelProperty
EasyExcel会根据这个注解中的名称和编号将对应的表数据自动赋值过来，就很方便吧，不需要你像poi一个个的去get set
还要转化数据类型

@DateTimeFormat 通过该注解 可以用Date数据类型接收表中的字符串类型的时间值，无需你手动转换

继承AnalysisEventListener类的监听器

1：代码中的注释很详细，大家也可以参考官方文档

2：没有注释的部分
2.1：JdJtk 类是数据库中对应表的映射类，和Excel接收类是有一定的差异的，当然如果你的数据库字段和Excel中的完全相同，你也是省略从Excel接收类转成数据库映射类的这个环节，但是个人建议你把两者区分出来。

2.2： DomainUtil.copyNotNullProperties(excel,jdFreightInsurance)
是自定义工具类 ，这里的作用就是将第一个非空对象中的非空成员属性拷贝到第二个对象的对应成员属性

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ione.apm.domain.dto.JdJtkExcel;
import com.ione.apm.domain.entity.JdJtk;
import com.ione.base.domain.DomainUtil;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class JdJtkDataListener extends AnalysisEventListener<JdJtkExcel> {


    private final Logger logger = LoggerFactory.getLogger(JdJtkDataListener.class);

    /**
     * 每隔3000条存储数据库，然后清理list,方便内存回收
     */
    private static final int BATCH_COUNT = 3000;

    /**
     * 表格数据封装类的集合
     */
    List<JdJtkExcel> list = new ArrayList<JdJtkExcel>();

    /**
     * 通过构造方法传入的业务层
     */
    private JdJtkService service;
    private String userUuid;

    public JdJtkDataListener(JdJtkService service, String userUuid) {
        this.service = service;
        this.userUuid = userUuid;
    }

    /**
     * 每一条数据解析都会来调用
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(JdJtkExcel data, AnalysisContext context) {
        try {
            logger.debug("解析到一条数据：{}", new ObjectMapper().writeValueAsString(data));
            list.add(data);
            //达到设定值就去存储一次数据库，防止内存中数据过多，容易OOM
            if (list.size() >= BATCH_COUNT) {
                saveData();
                //存储完成清理list
                list.clear();
            }
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
    }

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

    private void saveData() {

        logger.info("{}条数据，开始存储数据库！", list.size());
        //初始化一个JdJtk集合做数据转化
        List<JdJtk> jdJtks = new ArrayList<>();
        jdJtks = list.stream().map(excel -> {
            JdJtk jdJtk = new JdJtk();
            DomainUtil.copyNotNullProperties(excel,jdJtk);
            jdJtk.setCreateUserUuid(userUuid);
            jdJtk.setCreateDate(new Date());
            return jdJtk;
        }).collect(Collectors.toList());
        //存入数据库
        service.saveBatch(jdJtks);
        //清理集合释放内存
        jdJtks.clear();
        list.clear();
    }
}

导出

Controller层

public void download(@RequestParam HashMap<String, Object> params, HttpServletResponse response) throws IOException {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("特殊事项申请表", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ChanSpecialApplyExcel.class).sheet("模板").doWrite(service.buildChanSpecialApplyExcel(params));
    }

其中：
· ChanSpecialApplyExcel.clsaa是自定义表格数据类，用来封装和表格对应的数据。

· doWrite(方法返回的是需要导出的数据集合)

Service层

public List<ChanSpecialApplyExcel> buildChanSpecialApplyExcel(Map<String, Object> params) throws IOException {

        StringBuilder where = new StringBuilder();
        if (params.containsKey("no")) {
            where.append(" and SPECIAL.NO = :no");
        }
        if (params.containsKey("ocmBaseChanUuid")) {
            where.append(" and SPECIAL.OCM_BASE_CHAN_UUID = :ocmBaseChanUuid");
        }
        if (params.containsKey("dept")) {
            where.append(" and SPECIAL.DEPT = :dept");
        }
        if (params.containsKey("applyFlag")) {
            where.append(" and SPECIAL.APPLY_FLAG = :applyFlag");
        }
        if (params.containsKey("transferFlag")) {
            where.append(" and SPECIAL.TRANSFER_FLAG = :transferFlag");
        }
        try {
            String sql = " SELECT " +
                    " ROWNUM " +
                    ",TO_CHAR(SPECIAL.APPLY_DATE, 'yyyy-mm-dd') AS APPLY_DATE " +
                    ",(CASE " +
                    " WHEN AREA1.GRADE='3' THEN AREA1.NAME " +
                    " WHEN AREA1.GRADE='4'THEN (SELECT NAME FROM CBI_BASE_AREA WHERE UUID=AREA1.PARENT_UUID)END) " +
                    " ||(CASE WHEN AREA1.GRADE='4'THEN AREA1.NAME END) " +
                    " || AREA.NAME AS AREA_NAME " +
                    ",CHAN.NAME AS CHAN_NAME " +
                    ",SPECIAL.CONTENT AS SPECIAL_CONTENT " +
                    ",SPECIAL.REMARK AS SPECIAL_REMARK " +
                    " FROM CBI_CHAN_SPECIAL_APPLY SPECIAL " +
                    " LEFT JOIN OCM_BASE_CHAN CHAN ON SPECIAL.OCM_BASE_CHAN_UUID=CHAN.UUID " +
                    " LEFT JOIN CBI_BASE_AREA AREA ON CHAN.CBI_BASE_AREA_UUID=AREA.UUID " +
                    " LEFT JOIN CBI_BASE_AREA AREA1 ON AREA.PARENT_UUID=AREA1.UUID " +
                    " WHERE 1 = 1 " + where;

            List<ChanSpecialApplyExcel> specialApplyExcels = namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(ChanSpecialApplyExcel.class));
            return specialApplyExcels;
        } catch (DataAccessException e) {
            throw e;
        }
    }

是一个老项目持久层用的JPA框架，所以这里用到了NamedParameterJdbcTemplate来做动态查询

ChanSpecialApplyExcel

@ApiModel(description = "特殊事项excel导出")
public class ChanSpecialApplyExcel {

    @ExcelProperty(value = "序号")
    private BigDecimal rowNum;

    @ExcelProperty(value = "申请时间")
    private String applyDate;

    @ExcelProperty(value = "申请经销商所在地区")
    private String areaName;

    @ExcelProperty(value = "经销商名称")
    private String chanName;

    @ExcelProperty(value = "申请内容")
    private String specialContent;

    @ExcelProperty(value = "备注")
    private String specialRemark;

    public BigDecimal getRowNum() {
        return rowNum;
    }

    public void setRowNum(BigDecimal rowNum) {
        this.rowNum = rowNum;
    }

    public String getApplyDate() {
        return applyDate;
    }

    public void setApplyDate(String applyDate) {
        this.applyDate = applyDate;
    }

    public String getAreaName() {
        return areaName;
    }

    public void setAreaName(String areaName) {
        this.areaName = areaName;
    }

    public String getChanName() {
        return chanName;
    }

    public void setChanName(String chanName) {
        this.chanName = chanName;
    }

    public String getSpecialContent() {
        return specialContent;
    }

    public void setSpecialContent(String specialContent) {
        this.specialContent = specialContent;
    }

    public String getSpecialRemark() {
        return specialRemark;
    }

    public void setSpecialRemark(String specialRemark) {
        this.specialRemark = specialRemark;
    }
}

有一个问题
当我用以下代码时，生成的文件并不是自定义命名的xslx文件，而是一个空的respones.txt文件，目前没弄清原因，猜测是格式化的问题。

String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, ChanSpecialApplyExcel.class).sheet("模板").doWrite(service.buildChanSpecialApplyExcel(params));

导出同对象到多个sheet

 response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("海乐服务结算单", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        if (!nos.isEmpty()) {
            // 这里 指定文件
            ExcelWriter excelWriter = null;
            try {
                excelWriter = EasyExcel.write(response.getOutputStream(), AccountsProviderExcel.class).build();
                for (int i = 0; i < nos.size(); i++) {
                    //获取每一页的数据
                    List<AccountsProviderExcel> excels = statementMapper.getAllProviderExcelNo(nos.get(i));
                    //每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, excels.get(i).getAccountsProviderName()).head(AccountsProviderExcel.class).build();
                    // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                    excelWriter.write(excels, writeSheet);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 千万别忘记finish 会帮忙关闭流
                if (excelWriter != null) {
                    excelWriter.finish();
                }
            }
        } else {
            EasyExcel.write(response.getOutputStream(), AccountsProviderExcel.class).sheet("服务商结算汇总").doWrite(statementMapper.getAllProviderExcelNo(null));
        }

如果对你有帮助，点个赞吧 ^ .^