SpringBoot整合EasyExcel实现表格导入操作
1.导入Maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
2.创建一个与需要导入Excel文件头字段对应的实体类(index是字段所在列数)
@Data
public class ExcelData{
@ExcelProperty(value="水库名",index=0)
private String name;
@ExcelProperty(value="水库分类名",index=1)
private String categoryName;
@ExcelProperty(value="面积",index=2)
private Float area;
@ExcelProperty(value="水库容积",index=3)
private Float volume;
@ExcelProperty(value="流域",index=4)
private Float flows;
@ExcelProperty(value="地理位置",index=5)
private String location;
@ExcelProperty(value="文件地址",index=6)
private String file;
}
3.业务类
//======================业务导入excel=====================
@Override // 自己定义的
public Result saveByExcel(MultipartFile file,ReserService reservice) {
try{
//需要将上传文件转成Stream流
InputStream in = file.getInputStream();
EasyExcel.read(in,ExcelData.class,new ExcelLister(reservice)).sheet().doRead();;
}catch(Exception e){
e.printStackTrace();
}
return Result.success("导入数据成功");
}
4.因为需要通过操作数据库插入数据,业务类不能通过注入的方式加入到自己定义的ExcelLister类中,这里使用构造方法注入
@Slf4j
public class ExcelLister extends AnalysisEventListener<ExcelData>{
//因为SubjectExcelLister不能交给Spring进行管理,需要自己通过构造方法 进行注入
public ReserService reservice;
public ExcelLister() {}
public ExcelLister (ReserService reservice){
this.reservice = reservice;
}
//读取excel的内容 一行行进行读取 头部那行自动舍去
@Override
public void invoke(ExcelData data, AnalysisContext context) {
if(data == null){
throw new RuntimeException("文件数据异常");
}
Reser existOne = reservice.getExistOne(data.getName());
if(existOne == null){ // 如果 数据库没有重复字段 继续添加
Reser reserExcel = new Reser();
reserExcel.setName(data.getName());
reserExcel.setArea(data.getArea());
reserExcel.setLocation(data.getLocation());
reserExcel.setVolume(data.getVolume());
reserExcel.setFlows(data.getFlows());
//插入数据
reservice.add(reserExcel);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO Auto-generated method stub
}
}
5.通过上传文件完成,导入操作!!