EasyExcel使用–导入导出服务器为例
上传文件读取
@Operation(summary = "物理机导入上传文件")
@PostMapping(value = "/import/machine")
public String importFile(@RequestBody MultipartFile multipartFile) {
return serverUploadService.upload(multipartFile);
}
private static final String SEPARATOR = "/";
private static final Logger logger = LoggerFactory.getLogger(ServerUploadServiceImpl.class);
@Override
public String upload(MultipartFile multipartFile) {
String fileName = Objects.requireNonNull(multipartFile.getOriginalFilename())
.substring(0, multipartFile.getOriginalFilename().lastIndexOf("."));
String fileSuffix = Objects.requireNonNull(multipartFile.getOriginalFilename())
.substring(multipartFile.getOriginalFilename().lastIndexOf("."));
String newFileName = fileName + UUID.randomUUID() + fileSuffix;
String filePath = String.join(SEPARATOR, "D:\\upload", newFileName);
File file = new File(filePath);
try {
multipartFile.transferTo(file);
} catch (Exception e) {
logger.error("文件上传异常", e);
throw new SystemException("文件上传异常");
}
String uploadUid = UUID.randomUUID().toString();
ServerExcelImportBo temp = new ServerExcelImportBo();
temp.setUploadUid(uploadUid);
temp.setStatus(ServerImportStatus.WAIT);
temp.setFilePath(filePath);
metaControllerProcessMap.put(uploadUid, temp);
String tenantId = LoginUserUtil.getTenantId();
String userUid = LoginUserUtil.getUserUid();
CompletableFuture.runAsync(() -> getCheck(uploadUid,tenantId,userUid));
return uploadUid;
}
private final ConcurrentHashMap<String, ServerExcelImportBo> metaControllerProcessMap = new ConcurrentHashMap<String, ServerExcelImportBo>(8){
@Override
public ServerExcelImportBo get(Object key) {
ServerExcelImportBo temp = super.get(key);
if(ServerImportStatus.SUCCESS.equals(temp.getStatus())){
super.remove(key);
}
clearMap();
return temp;
}
private void clearMap(){
if(super.size() > 5){
super.forEach((key, value) -> {
if(ServerImportStatus.SUCCESS.equals(value.getStatus())){
logger.info(String.format("remove key:%s",key));
super.remove(key);
}
});
}
}
@Override
public ServerExcelImportBo put(String key, ServerExcelImportBo value) {
clearMap();
return super.put(key, value);
}
};
@Async
public void getCheck(String uploadUid,String tenantId,String userUid){
ServerExcelImportBo currentImport = metaControllerProcessMap.get(uploadUid);
if(currentImport == null){
logger.warn("未找到任务:"+uploadUid);
return;
}
currentImport.setStatus(ServerImportStatus.RUNNING);
metaControllerProcessMap.put(uploadUid, currentImport);
DataReadListener<MachineBasicInfoSheet> dataReadListener = (DataReadListener<MachineBasicInfoSheet>) machineBasicInfoDataReader;
dataReadListener.setUploadUid(uploadUid);
((MachineBasicInfoDataReader) dataReadListener).setTenantId(tenantId);
((MachineBasicInfoDataReader) dataReadListener).setUserUid(userUid);
ReadSheet readSheet = EasyExcel.readSheet(dataReadListener.sheetName())
.head(dataReadListener.getSheetClass())
.headRowNumber(dataReadListener.getHeadRowNumber())
.registerReadListener(dataReadListener)
.build();
ExcelReader excelReader = EasyExcel.read(currentImport.getFilePath())
.registerConverter(new StringToListConverter())
.registerConverter(new StringToListNameRelValueConverter())
.build();
try {
excelReader.read(readSheet);
} catch (Exception e) {
logger.error("读取 Excel 文件时发生错误：" + e.getMessage());
} finally {
if(excelReader != null){
excelReader.finish();
}
}
boolean error = false;
File errorExcel = new File("D:\\errorExcel.xlsx");
if(!errorExcel.exists()){
try {
errorExcel.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
List<?> errorData = dataReadListener.getErrorData();
currentImport.setErrorNum(errorData.size());
currentImport.setTotal(dataReadListener.getTotal());
if(!ObjectUtils.isEmpty(errorData)){
error = true;
}
if(!ObjectUtils.isEmpty(errorData)){
WriteSheet writeSheet = EasyExcelFactory.writerSheet(0,dataReadListener.getSheetName())
.head(dataReadListener.getSheetClass())
.build();
ExcelWriter excelWritter = EasyExcel.write(errorExcel)
.excelType(ExcelTypeEnum.XLSX)
.registerConverter(new StringToListConverter())
.registerConverter(new StringToListNameRelValueConverter())
.build();
excelWritter.write(errorData,writeSheet);
excelWritter.finish();
}
if(error){
FileInfo errorFile = new FileInfo(errorExcel.getName(), errorExcel.getPath());
errorFile.setFileType(FileType.EXCEL);
errorFile.setFileSize(String.valueOf(currentImport.getErrorNum()));
currentImport.setErrorDataFile(errorFile);
}
currentImport.setStatus(ServerImportStatus.SUCCESS);
metaControllerProcessMap.put(uploadUid, currentImport);
}
监听器
package com.sugon.rest.application.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.util.ObjectUtils;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.TimeUnit;
public abstract class DataReadListener<T> extends AnalysisEventListener<T> {
protected final List<T> errorData = new ArrayList<>();
private final List<T> dataList = new ArrayList<>(100);
private Integer total = 0;
private Integer currentRow = 0;
private String uploadUid;
@Resource
private RedisTemplate<String, String> redisTemplate;
public String getUploadUid() {
return uploadUid;
}
public void setUploadUid(String uploadUid) {
this.uploadUid = uploadUid;
}
public Integer getTotal() {
return total;
}
public List<T> getErrorData() {
return errorData;
}
public String getSheetName() {
return sheetName();
}
public Class<?> getSheetClass() {
return sheetClass();
}
public Integer getHeadRowNumber() {
return headRowNumber();
}
@Override
public void invoke(T data, AnalysisContext context) {
if (total == 0) {
this.total = context.readSheetHolder().getApproximateTotalRowNumber() - getHeadRowNumber();
}
if (dataList.size() < 100) {
dataList.add(data);
} else {
read(dataList);
this.currentRow = this.currentRow + dataList.size();
dataList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!ObjectUtils.isEmpty(dataList)) {
read(dataList);
this.currentRow = this.currentRow + dataList.size();
String finishNum = Optional.ofNullable(redisTemplate.opsForValue().get(getUploadUid())).orElse("0");
int temp = Integer.parseInt(finishNum) + 1;
redisTemplate.opsForValue().set(getUploadUid(), Integer.toString(temp), 30, TimeUnit.MINUTES);
}
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
dataList.clear();
super.onException(exception, context);
}
public abstract void read(List<T> data);
public abstract String sheetName();
public abstract Class<?> sheetClass();
public abstract Integer headRowNumber();
}
package com.sugon.rest.application.listener;
import com.sugon.core.application.enums.LabelGroup;
import com.sugon.core.application.manager.PhysicalMachineManager;
import com.sugon.core.application.manager.ServerClusterManager;
import com.sugon.core.application.pojo.po.PhysicalMachine;
import com.sugon.core.application.pojo.po.ServerCluster;
import com.sugon.core.auth.pojo.po.Label;
import com.sugon.core.auth.manager.LabelManager;
import com.sugon.core.auth.util.LoginUserUtil;
import com.sugon.core.common.util.UuidUtil;
import com.sugon.rest.application.entity.excel.server.MachineBasicInfoSheet;
import com.sugon.rest.application.entity.form.MachineSaveForm;
import com.sugon.rest.application.service.ServerService;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
import java.util.HashSet;
import java