使用EasyExcel实现多种类型多sheet页的Excel导入
一、前言
在工作中，我们或多或少的都会用到数据导入功能。
一般情况下我们都是一个sheet的数据导入，这样我们只需要简单的调用EasyExcel的EasyExcel.read(String pathName, Class head, ReadListener readListener)方法即可
但是，很多情况下，我们都会有比较复杂的导入需求，比如说，我需要导入不同类型的Excel，并且Excel中又有多个sheet页，并且其中的sheet页是有关联的。这种用上面的方法就行不通了。
因此，我们可以用策略模式来获取不同的Excel类型，又因为每种类型的Excel的数据读取方式和存储方式是差不多的，所以我们可以用模板方法模式来实现不同类型不同sheet页的数据的读取和存储。
tips：以下为部分为部分伪代码，具体需要根据自己业务实现
二、准备工作
2.1 准备一个导入的基类DemoImportBaseDTO.java，后面需要导入的类都继承这个基类
如果想需要传递一些数据的话，可以在基类中定义一些属性，加上
@ExcelIgnore注解即可
一些公共的方法也可以定义在基类里面，例如amountCheck()和numberCheck()方法都是来做参数校验的
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DemoImportBaseDTO extends BaseRowModel implements Serializable {
private static final Long serialVersionUID = -1L;
@ExcelIgnore
private RoleEnum role;
@ExcelIgnore
private TypeEnum type;
@ExcelProperty
private String errorMessage;
@ExcelIgnore
private Long userId;
public void inputCheck(StringBuffer errMsg) {
this.setErrorMessage(errMsg.toString());
}
public void amountCheck(StringBuffer errMsg, String amount, String name) {
int index = amount.indexOf(".");
if (index != -1) {
String[] split = amount.split("\\.");
if (split[1].length() > 2) {
errMsg.append(name);
errMsg.append("小数点不要超过2位");
}
}
}
public void numberCheck(StringBuffer errMsg, String amount, String name){
boolean strResult = amount.matches("-?[0-9]+.?[0-9]*");
if (!strResult) {
errMsg.append(name);
errMsg.append("不是数字");
}
}
}
2.2 准备Excel表导入的类，继承DemoImportBaseDTO.java基类
表1有3个sheet页,对应的类为：
Demo1Sheet0ImportDTO.java、Demo1Sheet1ImportDTO.java和Demo1Sheet2ImportDTO.java
表2有2个sheet页，对应的类为：
Demo1Sheet0ImportDTO.java和Demo2Sheet1ImportDTO.java
表3有1个sheet页，对应的类为：
Demo1Sheet0ImportDTO.java
另外：在每个类中都可以写自己的校验规则，如inputCheck()方法
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Demo1Sheet0ImportDTO extends DemoImportBaseDTO {
@ExcelProperty(index = 0, value = "序号")
private Integer index;
@ExcelProperty(index = 1, value = "字段1")
private String field1;
@ExcelProperty(index = 2, value = "字段2")
private String field2;
// .....
public void inputCheck(StringBuffer errMsg) {
if (getIndex() == null) {
errMsg.append("序号不可为空");
}
super.inputCheck(errMsg);
}
}
2.3 准备一个类DemoExcelReadListener.java，用来处理读取出来的Excel数据
@Slf4j
@Data
public class DemoExcelReadListener {
private static final int BATCH_COUNT = 500;
private List<DemoImportBaseDTO> cacheData = new ArrayList<>();
private DemoImportBaseDTO demoImportDTO;
private List<DemoImportBaseDTO> failedData = new ArrayList<>();
private List<Integer> successData = new ArrayList<>();
private Map<Integer, Map<String, String>> relIdMap;
private DemoImportService demoImportService;
// 读取数据
public void invoke(DemoImportBaseDTO baseDTO, AnalysisContext analysisContext) {
if (baseDTO != null) {
BeanUtil.copyProperties(demoImportDTO, baseDTO, CopyOptions.create().setIgnoreNullValue(true).setIgnoreError(true));
cacheData.add(baseDTO);
}
if (cacheData.size() > BATCH_COUNT) {
// 保存数据
demoImportService.save(cacheData, failedData, successData);
cacheData.clear();
}
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
demoImportService.save(cacheData, failedData, successData);
}
}
2.4 准备一个抽象类DemoImportService.java，用来定义一些导入的框架，后面具体导入的实现类都继承这个类
@Slf4j
public abstract class DemoImportService {
public DemoExcelReadListener listener;
public AnalysisEventListener analysisEventListener;
public Class<? extends DemoImportBaseDTO> clazz;
public ApiResult<Map<Integer, Map<String, String>>> demoSheet0Import(String fileName, DemoImportBaseDTO importDTO) {
return read(fileName, importDTO, null, 0, 3);
}
public ApiResult<Map<Integer, Map<String, String>>> debtImport(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
return read(fileName, importDTO, relMap, sheetNo, headNum);
}
private ApiResult<Map<Integer, Map<String, String>>> read(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
this.listener = new DemoExcelReadListener();
listener.setDemoImportDTO(importDTO);
listener.setDemoImportService(this);
listener.setRelIdMap(relMap == null ? new HashMap<>() : relMap);
EasyExcel.read(fileName, clazz, analysisEventListener)
.sheet(sheetNo)
.headRowNumber(headNum)
.doRead();
log.info("未导入数量：{}", listener.getFailedData().size() + "条");
String message = "导入完成，成功" + listener.getSuccessData().size() + "条，失败" + listener.getFailedData().size() + "条";
ApiResult<Map<Integer, Map<String, String>>> resultDto = new ApiResult<>();
resultDto.setMessage(message);
resultDto.setData(listener.getRelIdMap());
return resultDto;
}
public abstract void save(List<DemoImportBaseDTO> cacheData, List<DemoImportBaseDTO> failedData, List<Integer> successData);
}
2.5 准备一个工具类EasyExcelUtil.java，用来将数据以Excel的形式存在指定位置
@Slf4j
public class EasyExcelUtil {
public static void writeAndSaveExcel(String saveFilePath, Map<String, List<? extends BaseRowModel>> dataListMap,
List<String> sheetNames, List<Class<? extends BaseRowModel>> classes) {
if (!CollectionUtils.isEmpty(sheetNames) && !MapUtils.isEmpty(dataListMap)) {
try {
OutputStream out = new FileOutputStream(saveFilePath);
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
int sheetNo = 1;
int headlineMun = 0;
for (int i = 0; i < sheetNames.size(); ++i) {
String sheetName = sheetNames.get(i);
Class<? extends BaseRowModel> clas = classes.get(i);
Sheet sheet = new Sheet(sheetNo, headlineMun, clas);
sheet.setSheetName(sheetName);
sheet.setAutoWidth(true);
writer.write(dataListMap.get(sheetName), sheet);
++sheetNo;
}
writer.finish();
} catch (FileNotFoundException var7) {
log.error(var7.getMessage() == null ? "" : var7.getMessage(), var7);
// throw new Exception(new Object[0].toString());
} catch (Exception var8) {
log.error(var8.getMessage() == null ? "" : var8.getMessage(), var8);
// throw new Exception(new Object[0].toString());
}
}
}
}
2.6 准备一个工具类DemoImportAndExportResponse.java，用来放导入校验失败的数据
@Data
public class DemoImportAndExportResponse implements Serializable {
private static final Long serialVersionUID = -1L;
private List<? extends BaseRowModel> data;
private Class<? extends BaseRowModel> clazz;
}
2.7 准备一个工具类DemoImportRespDto.java，导入返回实体
@Data
public class DemoImportRespDto implements Serializable {
@ApiModelProperty("异常提示信息列表")
String errMessage;
@ApiModelProperty("下载地址")
private String filePath;
}
三、具体代码实现
准备一个类
DemoImportProviderServiceImpl.java，导入的时候调用importDemo()方法即可
3.1 定义一个mapserviceStrategies，并且在Bean初始化的时候赋值，相当于是一个策略模式，减少使用if-else带来的代码臃肿问题
该map的作用是将每个sheet页对应的实现类的名称按
key为sheet页的编号，value为bean的名称存储
在取的时候根据type类型和sheet页的编号，获取对应的实现类的Bean的名称
private Map<TypeEnum, Map<Integer, String>> serviceStrategies = new HashMap<>();
@PostConstruct
private void init() {
if (serviceStrategies.isEmpty()) {
HashMap<Integer, String> Demo1SubMap = new HashMap<>();
Demo1SubMap.put(0, "Demo1Sheet0ImportServiceImpl");
Demo1SubMap.put(1, "Demo1Sheet1ImportServiceImpl");
Demo1SubMap.put(2, "Demo1Sheet2ImportServiceImpl");
serviceStrategies.put(TypeEnum.TYPE1, Demo1SubMap);
HashMap<Integer, String> Demo2Map = new HashMap<>();
Demo2Map.put(0, "Demo1Sheet0ImportServiceImpl");
Demo2Map.put(1, "Demo2Sheet1ServiceImpl");
serviceStrategies.put(TypeEnum.TYPE2, Demo2Map);
HashMap<Integer, String> Demo3Map = new HashMap<>();
Demo3Map.put(0, "Demo1Sheet0ImportServiceImpl");
serviceStrategies.put(TypeEnum.TYPE3, Demo3Map);
}
}
3.2 importDemo()方法
- 通过读取Excel获取sheet页，然后遍历这个sheet页列表，通过sheet页的编号去map中获取对应的
bean的名称- 根据Bean的名称通过
applicationContext.getBean(beanName)方法获取对应的Service类- 根据sheet页的编号调用对应的导入的方法
- 将导入失败的数据通过
exportErrorData()方法写入Excel，保存到指定位置，然后返回错误信息和Excel的下载地址
@Override
public DemoImportRespDto importDemo(String fileName, String importFileName, TypeEnum type, RoleEnum roleEnum, Long userId) {
DemoImportBaseDTO importDTO = new DemoImportBaseDTO();
importDTO.setType(type);
importDTO.setRole(roleEnum);
importDTO.setUserId(userId);
List<ReadSheet> readSheets = EasyExcel.read(fileName).build().excelExecutor().sheetList();
int sheetSize = readSheets.size();
DemoImportService demoImportService;
ApiResult<Map<Integer, Map<String, String>>> resultDto = null;
ArrayList<String> list = new ArrayList<>();
LinkedHashMap<String, DemoImportAndExportResponse> map = new LinkedHashMap<>();
for (ReadSheet sheet : readSheets) {
int sheetNo = sheet.getSheetNo();
String sheetName = sheet.getSheetName();
String beanName = serviceStrategies.get(type).get(sheetNo);
if (null == beanName) {
continue;
}
demoImportService = (DemoImportService) applicationContext.getBean(beanName);
if (sheetNo == 0) {
resultDto = demoImportService.demoSheet0Import(fileName, importDTO);
} else {
resultDto = demoImportService.debtImport(fileName, importDTO, resultDto.getData(), sheetNo, 3);
}
String message = "sheet" + sheetName + resultDto.getMessage();
list.add(message);
if (!demoImportService.listener.getFailedData().isEmpty()) {
DemoImportAndExportResponse response = new DemoImportAndExportResponse();
response.setClazz(demoImportService.clazz);
response.setData(demoImportService.listener.getFailedData());
map.put(sheetName, response);
}
}
return exportErrorData(importFileName, map, list.toString());
}
3.3 DemoImportProviderServiceImpl.java完整代码
@Component
@Slf4j
public class DemoImportProviderServiceImpl implements DemoImportProviderService {
@Resource
private ApplicationContext applicationContext;
private Map<TypeEnum, Map<Integer, String>> serviceStrategies = new HashMap<>();
@PostConstruct
private void init() {
if (serviceStrategies.isEmpty()) {
HashMap<Integer, String> Demo1SubMap = new HashMap<>();
Demo1SubMap.put(0, "Demo1Sheet0ImportServiceImpl");
Demo1SubMap.put(1, "Demo1Sheet1ImportServiceImpl");
Demo1SubMap.put(2, "Demo1Sheet2ImportServiceImpl");
serviceStrategies.put(TypeEnum.TYPE1, Demo1SubMap);
HashMap<Integer, String> Demo2Map = new HashMap<>();
Demo2Map.put(0, "Demo1Sheet0ImportServiceImpl");
Demo2Map.put(1, "Demo2Sheet1ServiceImpl");
serviceStrategies.put(TypeEnum.TYPE2, Demo2Map);
HashMap<Integer, String> Demo3Map = new HashMap<>();
Demo3Map.put(0, "Demo1Sheet0ImportServiceImpl");
serviceStrategies.put(TypeEnum.TYPE3, Demo3Map);
}
}
@Override
public DemoImportRespDto importDemo(String fileName, String importFileName, TypeEnum type, RoleEnum roleEnum, Long userId) {
DemoImportBaseDTO importDTO = new DemoImportBaseDTO();
importDTO.setType(type);
importDTO.setRole(roleEnum);
importDTO.setUserId(userId);
List<ReadSheet> readSheets = EasyExcel.read(fileName).build().excelExecutor().sheetList();
int sheetSize = readSheets.size();
if (type.equals(TypeEnum.TYPE2)) {
if (sheetSize != 2) {
log.error("请导入对应的模板");
}
} else if (type.equals(TypeEnum.TYPE1)) {
if (sheetSize != 4) {
log.error("请导入对应的模板");
}
} else {
if (sheetSize != 1) {
log.error("请导入对应的模板");
}
}
DemoImportService demoImportService;
ApiResult<Map<Integer, Map<String, String>>> resultDto = null;
ArrayList<String> list = new ArrayList<>();
LinkedHashMap<String, DemoImportAndExportResponse> map = new LinkedHashMap<>();
for (ReadSheet sheet : readSheets) {
int sheetNo = sheet.getSheetNo();
String sheetName = sheet.getSheetName();
String beanName = serviceStrategies.get(type).get(sheetNo);
if (null == beanName) {
continue;
}
demoImportService = (DemoImportService) applicationContext.getBean(beanName);
if (sheetNo == 0) {
resultDto = demoImportService.demoSheet0Import(fileName, importDTO);
} else {
resultDto = demoImportService.debtImport(fileName, importDTO, resultDto.getData(), sheetNo, 3);
}
String message = "sheet" + sheetName + resultDto.getMessage();
list.add(message);
if (!demoImportService.listener.getFailedData().isEmpty()) {
DemoImportAndExportResponse response = new DemoImportAndExportResponse();
response.setClazz(demoImportService.clazz);
response.setData(demoImportService.listener.getFailedData());
map.put(sheetName, response);
}
}
return exportErrorData(importFileName, map, list.toString());
}
private DemoImportRespDto exportErrorData(String importFileName, LinkedHashMap<String, DemoImportAndExportResponse> data, String message) {
DemoImportRespDto demoImportRespDto = new DemoImportRespDto();
LinkedHashMap<String, List<? extends BaseRowModel>> map = new LinkedHashMap<>();
List<Class<? extends BaseRowModel>> classes = new ArrayList<>();
List<String> sheetNames = new ArrayList<>();
String tempFilePath = "D:\\doc";
String destFileName = importFileName + "_" + new SimpleDateFormat("yyyyMMdd").format(new Date()) + "导入错误数据" + ".xlsx";
if (!data.isEmpty()) {
data.forEach((k, v) -> {
sheetNames.add(k);
classes.add(v.getClazz());
map.put(k, v.getData());
});
try {
log.info("保存文件失败");
EasyExcelUtil.writeAndSaveExcel(tempFilePath + destFileName, map, sheetNames, classes);
} catch (Exception e) {
log.warn("保存导入失败文件失败");
}
}
demoImportRespDto.setErrMessage(message);
demoImportRespDto.setFilePath(tempFilePath + destFileName);
return demoImportRespDto;
}
}
四、具体实现类
4.1 Demo1Sheet0ImportServiceImpl.java具体代码实现
@Service
@Slf4j
public class Demo1Sheet0ImportServiceImpl extends DemoImportService {
@Resource
private ApplicationContext applicationContext;
@Override
public ApiResult<Map<Integer, Map<String, String>>> demoSheet0Import(String fileName, DemoImportBaseDTO demoImportDTO) {
this.analysisEventListener = new AnalysisEventListener<Demo1Sheet0ImportDTO>() {
@Override
public void invoke(Demo1Sheet0ImportDTO demoImport, AnalysisContext analysisContext) {
log.info("数据校验");
StringBuffer check = new StringBuffer();
demoImport.inputCheck(check);
if (StringUtils.isNotEmpty(check)) {
export4CheckError(demoImport, analysisContext, check);
return;
}
log.info("继续校验");
export4CheckError(demoImport, analysisContext, check);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
listener.doAfterAllAnalysed(analysisContext);
}
};
this.clazz = DemoImportDTO.class;
return super.DemoImport(fileName, demoImportDTO);
}
private void export4CheckError(Demo1Sheet0ImportDTO demoImport, AnalysisContext analysisContext, StringBuffer check) {
demoImport.setErrorMessage(check.toString());
listener.invoke(demoImport, analysisContext);
}
@Override
public void save(List<DemoImportBaseDTO> demoImportDTOS, List<DemoImportBaseDTO> failedData, List<Integer> successData) {
if (CollectionUtils.isEmpty(DemoImportDTOS)) {
return;
}
DemoImportService demoImportService = (DemoImportService) applicationContext.getBean("Demo1Sheet0ImportServiceImpl");
demoImportService.clazz = this.clazz;
demoImportService.listener = this.listener;
DemoImportBaseDTO demoImportBaseDTO = demoImportDTOS.get(0);
RoleEnum role = demoImportBaseDTO.getRole();
Long userId = demoImportBaseDTO.getUserId();
List<Demo1Sheet0ImportDTO> demoDTOS = new ArrayList(demoImportDTOS);
List<Demo1Sheet0ImportDTO> demo1Sheet0ImportDTOS = new ArrayList<>();
for (Demo1Sheet0ImportDTO item : demoDTOS) {
if (StringUtils.isNoneBlank(item.getErrorMessage())) {
failedData.add(item);
continue;
}
Demo1Sheet0ImportDTOS.add(item);
successData.add(item.getIndex());
}
// 执行插入数据库操作...
// 执行插入数据库操作...
// 执行插入数据库操作...
log.info("执行插入数据库操作");
demo1Sheet0ImportDTOS.forEach(demo1Sheet0ImportDTO -> {
HashMap<String, String> param = new HashMap<>();
param.put("relId", demo1Sheet0ImportDTO.getBuyerName());
listener.getRelIdMap().put(demo1Sheet0ImportDTO.getIndex(), param);
});
}
}
4.2 Demo2Sheet1ImportServiceImpl.java具体代码实现
@Service
@Slf4j
public class Demo2Sheet1ImportServiceImpl extends DemoImportService {
@Resource
private ApplicationContext applicationContext;
@Override
public ApiResult<Map<Integer, Map<String, String>>> debtImport(String fileName, DemoImportBaseDTO importDTO, Map<Integer, Map<String, String>> relMap, Integer sheetNo, Integer headNum) {
this.analysisEventListener = new AnalysisEventListener<Demo2Sheet1ImportDTO>() {
@Override
public void invoke(Demo2Sheet1ImportDTO demo2Sheet1ImportDTO, AnalysisContext analysisContext) {
if (listener.getRelIdMap().get(demo2Sheet1ImportDTO.getIndex()) == null){
demo2Sheet1ImportDTO.setErrorMessage("关联数据没有导入成功");
listener.invoke(demo2Sheet1ImportDTO, analysisContext);
}else {
log.info("一些赋值可以在这");
String billNo = listener.getRelIdMap().get(demo2Sheet1ImportDTO.getIndex()).get("no");
demo2Sheet1ImportDTO.setBillNo(billNo);
}
demo2Sheet1ImportDTO.inputCheck(new StringBuffer());
listener.invoke(demo2Sheet1ImportDTO, analysisContext);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
listener.doAfterAllAnalysed(analysisContext);
}
};
this.clazz = demo2Sheet1ImportDTO.class;
return super.debtImport(fileName, importDTO, relMap, sheetNo, 4);
}
@Override
public void save(List<DemoImportBaseDTO> demoImportDTOS, List<DemoImportBaseDTO> failedData, List<Integer> successData) {
if (CollectionUtils.isEmpty(demoImportDTOS)) {
return;
}
DemoImportService demoImportService = (DemoImportService) applicationContext.getBean("Demo2Sheet1ServiceImpl");
demoImportService.clazz = this.clazz;
demoImportService.listener = this.listener;
List<Demo2Sheet1ImportDTO> demo2Sheet1DTOS = new ArrayList(demoImportDTOS);
List<Demo2Sheet1ImportDTO> demo2Sheet1ImportDTOS = new ArrayList<>();
for (Demo2Sheet1ImportDTO item : demo2Sheet1DTOS) {
if (listener.getRelIdMap().get(item.getIndex()) == null) {
item.setErrorMessage("关联数据没有导入成功");
failedData.add(item);
continue;
}
if (StringUtils.isNoneBlank(item.getErrorMessage())) {
failedData.add(item);
continue;
}
log.info("构造对象");
log.info("对象插入数据库");
demo2Sheet1ImportDTOS.add(item);
successData.add(item.getIndex());
}
log.info("执行插入数据库操作");
// xxxservice.saveBath(demo2Sheet1ImportDTOS)
}
}
五、总结
- 通过策略模式来消除if-else带来的代码臃肿问题
- 通过模板方法模式，创建一个抽象类，然后子类去重写一些方法，来实现各自不同的功能
643
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
