一、概念
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 文件流会自动关闭
-
EasyExcel.read(file.getInputStream(), ExcelAttdnOver.class,
new
AttdnDataListener(attdnOverService,month))
-
.sheet().headRowNumber(
1).doRead();
-
return
"success";
-
}
-
}
三、postman测试文件上传
1. postman设置Header的key=Content-Type，Value=multipart/form-data
2. postman设置Body的key=file，并选择为File，value选择目录
注释：key=file，此处的file变量与Java代码的Controller中的MultipartFile file变量名保持一样，否则不起作用，读不到文件。
@PostMapping("/upload/{month}") public String upload(MultipartFile file, @PathVariable String month) {}
转载：https://blog.csdn.net/weixin_51725434/article/details/128306711
查看评论