1.所需架包
<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.4</version>
</dependency>
2、实体类
package com.yunhe.zzedu.file;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import lombok.Data;
/**
* @description:
* @author: bgq
* @create: 2021/8/25 11:45
*/
@Data
@ContentRowHeight(10)
public class Apply {
@ColumnWidth(20)
@ExcelProperty(value = "姓名",index = 1)
private String xm;
@ColumnWidth(20)
@ExcelProperty(value = "身份证号",index = 2)
private String sfzh;
@ColumnWidth(20)
@ExcelProperty(value = "性别",index = 3)
private String xb;
@ColumnWidth(20)
@ExcelProperty(value = "出生日期",index = 4)
private String csrq;
}
3、监听
package com.yunhe.zzedu.web.controller.common;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.yunhe.zzedu.common.tool.IDTool;
import com.yunhe.zzedu.file.Apply;
import com.yunhe.zzedu.service.base.ApplyService;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.util.*;
/**
* @description:
* @author: bgq
* @create: 2021/8/25 11:11
*/
@Component
@Scope("prototype")
public class ApplyExcelListener extends AnalysisEventListener<Apply> {
private ApplyService applyService;
List<Apply> datas=new ArrayList<>();
String batchId = null;
private static final int BATCH_COUNT = 3000;
@Override
public void invoke(Apply chapter, AnalysisContext analysisContext) {
//数据存储到datas,供批量处理,或后续自己业务逻辑处理。
datas.add(chapter);
//达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if(datas.size() >= BATCH_COUNT){
saveData();
// 存储完成清理datas
datas.clear();
}
}
public void setBatchId(String batchId){
this.batchId = batchId;
}
public ApplyExcelListener(ApplyService applyService){
this.applyService = applyService;
}
//数据处理
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
}
private void saveData() {
List<Map<String,Object>> mapList =new ArrayList<>();
if (datas.size() >= 1) {
for (int i = 0; i < datas.size(); i++) {
Map<String,Object> map = new HashMap<>();
Apply apply = datas.get(i);
map.put("id", IDTool.getUUID32());
map.put("xm",apply.getXm());
map.put("sfzh",apply.getSfzh());
map.put("xb",apply.getXb());
map.put("csrq",apply.getCsrq());
mapList.add(map);
}
applyService.saveBatch(mapList);
}
}
}
4、接口
@RequestMapping(value = "/importExcel",method = RequestMethod.POST)
@ResponseBody
public Result importExcel(MultipartRequest request){
//获得工作簿
ExcelReaderBuilder read = null;
try(InputStream inputStream = request.getFile("file").getInputStream()) {
String batchId = ((StandardMultipartHttpServletRequest) request).getParameterMap().get("batchId")[0];
//实例化实现了AnalysisEventListener接口的类
ApplyExcelListener listener = new ApplyExcelListener(applyService);
listener.setBatchId(batchId);
//获得工作簿
read = EasyExcel.read(inputStream, Apply.class, listener);
//获得工作表 又两种形形式可以通过下标也可以通过名字2003Excel不支持名字
ExcelReaderSheetBuilder sheet = read.sheet();
//进行读操作
sheet.doRead();
} catch (IOException e) {
e.printStackTrace();
}
return ResultUtil.success();
}