//依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
1.编写excel监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
//用户监听excel表格的数据,获取数据
public class ExcelListener extends AnalysisEventListener<LinkedHashMap> {
private List<LinkedHashMap> datas = new ArrayList<>();
@Override
public void invoke(LinkedHashMap linkedHashMap, AnalysisContext analysisContext) {
datas.add(linkedHashMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List<LinkedHashMap> getDatas(){
return this.datas;
}
public void clearDatas(){
datas = new ArrayList<>();
}
}
2.编写实体类
import com.alibaba.fastjson.annotation.JSONField;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.web.multipart.MultipartFile;
@Data
public class ImportExcelDataReq {
//接受传入的excel文件
@ApiModelProperty(value = "excel文件")
private MultipartFile file;
}
3.编写接口
@ApiOperation(value = "批量上传数据 ",
notes = "")
@PostMapping("importBlindBox")
@Transactional(rollbackFor = Exception.class)
public Result importBlindBox( ImportExcelDataReq req) throws IOException, ResultInfo {
InputStream inputStream = req.getFile().getInputStream();
//excel监听器
ExcelListener listener = new ExcelListener();
//使用EasyExcel读取数据流和监听器
ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(inputStream, listener);
//创建EXCEL读取对象
ExcelReader reader = excelReaderBuilder.build();
//获取EXCEL的表的所有页面
List<ReadSheet> sheetList = reader.excelExecutor().sheetList();
// 上传
for (ReadSheet sheet : sheetList) {
//从0开始读取页面,0-excel的第一页开始
reader.read(sheet);
List<LinkedHashMap> datas = listener.getDatas();
// 数据导入,sheet.getSheetName()取每页的页名
String blindBoxName = sheet.getSheetName().split("-")[1];
productV1Service.importProduct(blindBoxName, datas);
//执行完一页就清除一次数据
listener.clearDatas();
}
reader.finish();
return new Result<>().success();
}
@Override
public void importAuth(List<LinkedHashMap> datas) {
for (LinkedHashMap linkedHashMap : datas) {
//获取第一列的数据
String year = (String) linkedHashMap.get(0);
//获取第二列的数据
String name = (String) linkedHashMap.get(1);
//获取第三列的数据
String type = (String) linkedHashMap.get(2);
//获取第四列的数据
String title = (String) linkedHashMap.get(3);
//获取第五列的数据
String item = (String) linkedHashMap.get(4);
}