<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
监听器
package com.finshine.finaps.web.listener;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.finshine.finaps.web.dto.equipment.EquipmentCreateDTO;
import com.finshine.finaps.web.enums.ProcessTypeEnum;
import com.finshine.finaps.web.service.EquipmentService;
import com.finshine.finaps.web.vo.EquipmentExcelVO;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class EasyExcelListener extends AnalysisEventListener<EquipmentExcelVO> {
private final EquipmentService equipmentService;
public EasyExcelListener(EquipmentService equipmentService) {
this.equipmentService = equipmentService;
}
/**
* 每隔100条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
List<EquipmentExcelVO> list = new ArrayList<EquipmentExcelVO>();
@Override
public void invoke(EquipmentExcelVO data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSONObject.toJSON(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
for (EquipmentExcelVO equipmentExcelVO : list) {
//存放转换后的工序数字
List<String> process = new ArrayList<>();
String status = equipmentExcelVO.getStatus();
if (StringUtils.equals("正常", status)) {
// 0正常 1-故障 2-维修
equipmentExcelVO.setStatus("0");
} else if (StringUtils.equals("故障", status)) {
equipmentExcelVO.setStatus("1");
} else if (StringUtils.equals("维修", status)) {
equipmentExcelVO.setStatus("2");
}
String processType = StrUtil.removeAll(equipmentExcelVO.getProcessType(), " ");
//逗号-不区分中英文逗号
String regex = ",|,|\\s+";
//存放工序列表
List<String> processTypeList = List.of(processType.split(regex));
processTypeList.forEach(p -> {
Integer type = getType(p);
process.add(String.valueOf(type));
});
EquipmentCreateDTO equipmentEntityDto = BeanUtil.copyProperties(equipmentExcelVO, EquipmentCreateDTO.class);
equipmentEntityDto.setProcessType(process);
equipmentService.create(equipmentEntityDto);
//每次插入后清空工序数字
process.clear();
}
log.info("存储数据库成功!");
}
/**
* 根据工序中文 得到对应数字
*
* @param process 工序
* @return {@code Integer}
*/
private Integer getType(String process) {
if (StringUtils.equals(ProcessTypeEnum.TURNING.getProcessName(), process)) {
return ProcessTypeEnum.TURNING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.MILLING.getProcessName(), process)) {
return ProcessTypeEnum.MILLING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.DRILLING.getProcessName(), process)) {
return ProcessTypeEnum.DRILLING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.GRINDING.getProcessName(), process)) {
return ProcessTypeEnum.GRINDING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.FORGING.getProcessName(), process)) {
return ProcessTypeEnum.FORGING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.STAMPING.getProcessName(), process)) {
return ProcessTypeEnum.STAMPING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.WELDING.getProcessName(), process)) {
return ProcessTypeEnum.WELDING.getProcessCode();
} else if (StringUtils.equals(ProcessTypeEnum.ASSEMBLY.getProcessName(), process)) {
return ProcessTypeEnum.ASSEMBLY.getProcessCode();
} else {
return 0;
}
}
}
实体类
package com.finshine.finaps.web.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @description
* @since 2023-01-10 16:50
*/
@Data
public class EquipmentExcelVO {
@ApiModelProperty(value = "设备编号")
@ExcelProperty(value = "设备编号", index = 1)
private String equipmentNum;
@ApiModelProperty("设备类型")
@ExcelProperty(value = "设备类型", index = 2)
private String equipmentType;
@ApiModelProperty("设备型号")
@ExcelProperty(value = "设备型号", index = 3)
private String spec;
@ApiModelProperty(value = "工序类型")
@ExcelProperty(value = "可用工序类型", index = 4)
private String processType;
@ExcelProperty(value = "设备状态 0正常 1-故障 2-维修", index = 5)
private String status;
}
controller
@ApiOperation("导入设备excel")
@PostMapping("/importDeviceExcel")
public RespWrapper testImport(@RequestParam("file") MultipartFile multipartFile) {
try {
EasyExcel.read(multipartFile.getInputStream(), EquipmentExcelVO.class, new EasyExcelListener(equipmentService)).sheet().doRead();
} catch (Exception e) {
throw new BusinessException("数据导入失败:" + e);
}
return RespWrapper.buildSuccessResp("导入成功");
}