Springboot整合EasyExcel，实现Excel文件上传
一、概念
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。 它能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。EasyExcel是在尽可能节约内存的情况下支持读写百M的Excel。
二、Excel的上传(读Excel)
1.Excel读取的实现方案
实现Springboot结合EasyExcel实现对Excel中数据的读取，并且将读取的数据通过Mybatis-plus保存到Mysql 数据库 。
2. maven依赖，pom文件
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.8</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.76</version> </dependency> </dependencies>
3. pom文件，加载xml文件
由于用到MyBatisplus，所以一定不要忘记加下面的这段代码，否则你的mapper是编译不到你的classpath中的。
<build> <!-- 由于用到MyBatis，所以一定不要忘记加下面的这段代码，否则你的mapper是编译不到你的classpath中的。--> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> </resources> </build>
4. application.yml文件配置mybatis-plus
server: port: 80 spring: datasource: druid: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/redhorse?serverTimezone=UTC username: root password: root mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
5. 表格和表对应的实体类
既然要读取Excel，同时存入数据库，那么就必然需要对应的表，以及表对应的实体类，而Excel也需要对应的实体类。因为Excel表格会增加一些不必要的字段，而这些字段并不需要存入数据库中，同理数据库实体类同样存在一些字段不是从表格中获取。
5.1 表格对应的实体类
package com.atorientsec.entities;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import lombok.Data;
@Data
public class ExcelAttdnOver {
@ExcelProperty(index =2)
private String department;
@ExcelProperty(index = 0)
private String name;
* 这里用String去接日期，才能格式化，接收年月日的格式
@ExcelProperty(index = 3)
@DateTimeFormat(value = "yyyy-MM-dd")
private String overDate;
@ExcelProperty(index = 4)
private Double overHours;
*接收百分比的数字
@ExcelProperty(index = 7)
@NumberFormat("#.##%")
private String rate;
}5.2 数据库对应的实体类
package com.atorientsec.entities;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date;
@Data
public class AttdnOver {
@TableId(type = IdType.AUTO)
private Integer id;
private String department;
private String name;
private String attdnMonth;
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date overDate;
private Double overHours;
private Double rate;
}注释：数据库实体类的overDate是Date类型，而Excel对应的类中overDate是String类型，只有String去接日期才能格式化。
6. 默认一行行的读取excel，所以需要创建excel一行一行的回调监听器
package com.atorientsec.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.atorientsec.entities.AttdnOver;
import com.atorientsec.entities.ExcelAttdnOver;
import com.atorientsec.service.AttdnOverService;
import lombok.extern.slf4j.Slf4j;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
public class AttdnDataListener implements ReadListener<ExcelAttdnOver> {
* Excel模板的读取类
* 有个很重要的点，AttdnDataListener不能被Spring管理
* 要每次读取excel都要new，然后里面用到spring可以构造方法传进去
* @param excelAttdnOver
* @param analysisContext
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
private static final int BATCH_COUNT = 100;
private int count = 0;
* 缓存的数据,List<AttdnOver>
private List<AttdnOver> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private AttdnOverService attdnOverService;
private String month;
public AttdnDataListener(){
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
public AttdnDataListener(AttdnOverService attdnOverService,String month){
this.attdnOverService = attdnOverService;
this.month = month;
* 这个每一条数据解析都会来调用
@Override
public void invoke(ExcelAttdnOver excelAttdnOver, AnalysisContext analysisContext) {
log.info("解析到第 {} 条数据:{}", (++count), JSON.toJSONString(excelAttdnOver));
try {
//把表格对应的实体类对象转化成数据库表对应的对象
AttdnOver attdnOver = new AttdnOver();
attdnOver.setDepartment(excelAttdnOver.getDepartment());
attdnOver.setName(excelAttdnOver.getName());
attdnOver.setAttdnMonth(this.month);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse(excelAttdnOver.getOverDate());
attdnOver.setOverDate(date);
attdnOver.setOverHours(excelAttdnOver.getOverHours());
attdnOver.setRate(Double.parseDouble(excelAttdnOver.getRate().replace("%", "")));
cachedDataList.add(attdnOver);
if(cachedDataList.size()>=BATCH_COUNT){
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}catch (Exception e){
log.error(e.getMessage());
* 接收表头信息
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
Map<Integer,String> StringMap = ConverterUtils.convertToStringMap(headMap,context);
Set<Integer> keySet = StringMap.keySet();
System.out.println("该Excel表头信息是：");
for(int i=0;i<keySet.size();i++){
System.out.println("第 "+(i+1)+" 列 = "+StringMap.get(i));
ReadListener.super.invokeHead(headMap, context);
* 所有数据解析完成了 都会来调用
* @param analysisContext
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("表格中的所有数据解析完成！！！");
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
* 存储数据库
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
attdnOverService.batchSave(cachedDataList);
log.info("存储数据库成功！");
}7. mapper-继承Mybatis-plus的baseMapper
package com.atorientsec.mapper;
import com.atorientsec.entities.AttdnOver;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface AttdnOverMapper extends BaseMapper<AttdnOver> {
}8. service：Mybatis-plus实现批量插入，并开启事务
package com.atorientsec.service;
import com.atorientsec.entities.AttdnOver;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
public interface AttdnOverService extends IService<AttdnOver> {
@Transactional
boolean batchSave(ArrayList<AttdnOver> attdnOverArrayList);
}package com.atorientsec.service.impl;
import com.atorientsec.entities.AttdnOver;
import com.atorientsec.mapper.AttdnOverMapper;
import com.atorientsec.service.AttdnOverService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
@Service
public class AttdnOverServiceImpl extends ServiceImpl<AttdnOverMapper, AttdnOver> implements AttdnOverService {
public boolean batchSave(ArrayList<AttdnOver> attdnOverArrayList){
//saveBatch是mybatisplus的批量插入方法
boolean status = saveBatch(attdnOverArrayList);
return status;
}9. Controller：MultipartFile上传文件
EasyExcel读文件
package com.atorientsec.controller;
import com.alibaba.excel.EasyExcel;
import com.atorientsec.entities.ExcelAttdnOver;
import com.atorientsec.listener.AttdnDataListener;
import com.atorientsec.service.AttdnOverService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
@RestController
@RequestMapping("/excel")
public class AttdnOverController {
@Autowired
private AttdnOverService attdnOverService;
@PostMapping("/upload/{month}")
public String upload(MultipartFile file, @PathVariable String month) throws IOException {
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭