package com.platform.service.impl; import com.alibaba.fastjson.JSON; import com.platform.model.entity.ExcelDataEntity; import com.platform.model.entity.ExcelTemplateEntity; import com.platform.model.resp.Result; import com.platform.model.vo.FileUploadVO; import com.platform.model.vo.TableHeaderVO; import com.platform.service.AnalysisService; import com.platform.service.ExcelDataService; import com.platform.service.ExcelTemplateService; import com.platform.service.FileUploadService; import com.platform.utils.DateUtils; import org.apache.commons.compress.utils.Lists; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.ParseException; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.concurrent.CompletableFuture; import java.util.concurrent.CompletionException; import java.util.stream.Collectors; /** * @author : wujipeng * @date : 2025/7/30 11:48 * @Version: 1.0 * @Desc : 描述信息 */ @Service public class AnalysisServiceImpl implements AnalysisService { @Resource private ExcelTemplateService excelTemplateService; @Resource private ExcelDataService excelDataService; @Resource private FileUploadService fileUploadService; private static final String[] EXCEL_EXTENSIONS = {".xlsx", ".xls"}; // @Override // public Result canvas(MultipartFile[] files, Integer templateId) { // //查询模板 // ExcelTemplateEntity ExcelTemplate = excelTemplateService.getById(templateId); // String json = ExcelTemplate.getJson(); // List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); // if (ObjectUtils.isEmpty(ExcelTemplate)) { // return Result.fail("模板不存在"); // } // try { // if (files.length == 0) { // return Result.fail("上传的文件为空"); // } // List fileUploadList = Lists.newArrayList(); // for (MultipartFile file : files) { // // 检查是否是Excel文件 // if (isExcelFile(file.getOriginalFilename())) { // File tempFile = convertMultipartFileToFile(file); // processSingleExcelFile(tempFile, tableHeaderList); // //文件上传 // FileUploadVO fileUploadVO = fileUploadService.uploadFile(file); // fileUploadList.add(fileUploadVO); // } // } // return Result.data(fileUploadList); // } catch (Exception e) { // return Result.fail("文件处理失败: " + e.getMessage()); // } // } @Override public Result canvas(MultipartFile[] files, Integer templateId) { // 1. 预检查 if (files == null || files.length == 0) { return Result.fail("上传的文件为空"); } // 2. 异步处理文件 List> futures = Arrays.stream(files) .filter(file -> !file.isEmpty() && isExcelFile(file.getOriginalFilename())) .map(file -> CompletableFuture.supplyAsync(() -> processFile(file, templateId))) .collect(Collectors.toList()); // 3. 等待所有任务完成并收集结果 try { List fileUploadList = futures.stream() .map(CompletableFuture::join) .collect(Collectors.toList()); return Result.data(fileUploadList); } catch (CompletionException e) { return Result.fail("文件处理失败: " + e.getCause().getMessage()); } } private FileUploadVO processFile(MultipartFile file, Integer templateId) { try { // 1. 获取模板 ExcelTemplateEntity excelTemplate = excelTemplateService.getById(templateId); if (excelTemplate == null) { throw new RuntimeException("模板不存在"); } List tableHeaderList = JSON.parseArray(excelTemplate.getJson(), TableHeaderVO.class); // 2. 使用内存处理替代临时文件 Workbook workbook = getWorkbook(file); Sheet sheet = workbook.getSheetAt(0); // 3. 批量处理数据 List excelData = new ArrayList<>(sheet.getLastRowNum()); for (int i = 1; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row != null) { ExcelDataEntity entity = handleExcelData(tableHeaderList, row); if (entity != null) { excelData.add(entity); } } } // 4. 批量保存 if (!excelData.isEmpty()) { excelDataService.saveBatch(excelData); } // 5. 上传文件 return fileUploadService.uploadFile(file); } catch (Exception e) { throw new RuntimeException("处理文件 " + file.getOriginalFilename() + " 失败: " + e.getMessage(), e); } } // 直接从MultipartFile创建Workbook private Workbook getWorkbook(MultipartFile file) throws IOException { String fileName = file.getOriginalFilename().toLowerCase(); InputStream inputStream = file.getInputStream(); if (fileName.endsWith(".xlsx")) { return new XSSFWorkbook(inputStream); } else if (fileName.endsWith(".xls")) { return new HSSFWorkbook(inputStream); } else { throw new IllegalArgumentException("不支持的Excel文件格式"); } } // 重构handleExcelData返回单个实体 private ExcelDataEntity handleExcelData(List tableHeaderList, Row row) { ExcelDataEntity excelDataEntity = new ExcelDataEntity(); trySetValue(() -> excelDataEntity.setType(getCellValueAsString(row.getCell(tableHeaderList.get(0).getIndex())))); trySetValue(() -> excelDataEntity.setDirection(getCellValueAsString(row.getCell(tableHeaderList.get(1).getIndex())))); trySetValue(() -> { try { excelDataEntity.setTime(DateUtils.parseCompactDateTime(getCellValueAsString(row.getCell(tableHeaderList.get(2).getIndex())))); } catch (ParseException ignored) { } }); trySetValue(() -> excelDataEntity.setAmount(Double.parseDouble(getCellValueAsString(row.getCell(tableHeaderList.get(3).getIndex()))))); trySetValue(() -> excelDataEntity.setSerial(getCellValueAsString(row.getCell(tableHeaderList.get(4).getIndex())))); trySetValue(() -> excelDataEntity.setPayeeBankCard(getCellValueAsString(row.getCell(tableHeaderList.get(5).getIndex())))); trySetValue(() -> excelDataEntity.setPayeeAccount(getCellValueAsString(row.getCell(tableHeaderList.get(6).getIndex())))); trySetValue(() -> excelDataEntity.setPayeeMerchant(getCellValueAsString(row.getCell(tableHeaderList.get(7).getIndex())))); trySetValue(() -> excelDataEntity.setPayeeName(getCellValueAsString(row.getCell(tableHeaderList.get(8).getIndex())))); trySetValue(() -> excelDataEntity.setPaymentBankName(getCellValueAsString(row.getCell(tableHeaderList.get(9).getIndex())))); trySetValue(() -> excelDataEntity.setPaymentBankCard(getCellValueAsString(row.getCell(tableHeaderList.get(10).getIndex())))); trySetValue(() -> excelDataEntity.setPaymentAccount(getCellValueAsString(row.getCell(tableHeaderList.get(11).getIndex())))); trySetValue(() -> excelDataEntity.setIp(getCellValueAsString(row.getCell(tableHeaderList.get(12).getIndex())))); trySetValue(() -> excelDataEntity.setMac(getCellValueAsString(row.getCell(tableHeaderList.get(13).getIndex())))); trySetValue(() -> excelDataEntity.setExternalChannel(getCellValueAsString(row.getCell(tableHeaderList.get(14).getIndex())))); trySetValue(() -> excelDataEntity.setPayAccount(getCellValueAsString(row.getCell(tableHeaderList.get(15).getIndex())))); trySetValue(() -> excelDataEntity.setRemark(getCellValueAsString(row.getCell(tableHeaderList.get(16).getIndex())))); return excelDataEntity; } // 辅助方法:简化异常处理 private void trySetValue(Runnable setter) { try { setter.run(); } catch (Exception ignored) { // 忽略单个字段设置失败 } } @Override public Result> template(MultipartFile file) { try { if (file.isEmpty()) { return Result.fail("上传失败"); } else if (isExcelFile(file.getOriginalFilename())) { File tempFile = convertMultipartFileToFile(file); return Result.data(parseTheHeader(tempFile)); } else { return Result.fail("上传失败"); } } catch (Exception e) { return Result.fail("上传失败"); } } //返回Excel文件表头数组 private List parseTheHeader(File tempFile) throws IOException { //读取第一个sheet Sheet sheet = getWorkbook(tempFile).getSheetAt(0); //获取文件表头 Row headerRow = sheet.getRow(0); List headers = Lists.newArrayList(); int index = 0; for (Cell cell : headerRow) { headers.add(TableHeaderVO.builder().name(getCellValueAsString(cell)).index(index).build()); index++; } return headers; } // 检查是否是Excel文件 private boolean isExcelFile(String filename) { if (filename == null) return false; String lowerCaseFilename = filename.toLowerCase(); for (String extension : EXCEL_EXTENSIONS) { if (lowerCaseFilename.endsWith(extension)) { return true; } } return false; } private void processSingleExcelFile(File excelFile, List tableHeaderList) throws IOException { //读取第一个sheet Sheet sheet = getWorkbook(excelFile).getSheetAt(0); //获取sheet长度 int sheetLength = sheet.getLastRowNum(); List excelData = Lists.newArrayList(); for (int i = 1; i < sheetLength; i++) { Row row = sheet.getRow(i); //处理excel数据 handleExcelData(tableHeaderList, row, excelData); } //保存excel数据 excelDataService.saveBatch(excelData); } private void handleExcelData(List tableHeaderList, Row row, List excelData) { ExcelDataEntity excelDataEntity = new ExcelDataEntity(); try { excelDataEntity.setType(getCellValueAsString(row.getCell(tableHeaderList.get(0).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setDirection(getCellValueAsString(row.getCell(tableHeaderList.get(1).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setTime(DateUtils.parseCompactDateTime(getCellValueAsString(row.getCell(tableHeaderList.get(2).getIndex())))); } catch (Exception ignored) { } try { excelDataEntity.setAmount(Double.parseDouble(getCellValueAsString(row.getCell(tableHeaderList.get(3).getIndex())))); } catch (Exception ignored) { } try { excelDataEntity.setSerial(getCellValueAsString(row.getCell(tableHeaderList.get(4).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPayeeBankCard(getCellValueAsString(row.getCell(tableHeaderList.get(5).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPayeeAccount(getCellValueAsString(row.getCell(tableHeaderList.get(6).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPayeeMerchant(getCellValueAsString(row.getCell(tableHeaderList.get(7).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPayeeName(getCellValueAsString(row.getCell(tableHeaderList.get(8).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPaymentBankName(getCellValueAsString(row.getCell(tableHeaderList.get(9).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPaymentBankCard(getCellValueAsString(row.getCell(tableHeaderList.get(10).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPaymentAccount(getCellValueAsString(row.getCell(tableHeaderList.get(11).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setIp(getCellValueAsString(row.getCell(tableHeaderList.get(12).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setMac(getCellValueAsString(row.getCell(tableHeaderList.get(13).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setExternalChannel(getCellValueAsString(row.getCell(tableHeaderList.get(14).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setPayAccount(getCellValueAsString(row.getCell(tableHeaderList.get(15).getIndex()))); } catch (Exception ignored) { } try { excelDataEntity.setRemark(getCellValueAsString(row.getCell(tableHeaderList.get(16).getIndex()))); } catch (Exception ignored) { } excelData.add(excelDataEntity); } // 根据文件类型创建Workbook private Workbook getWorkbook(File excelFile) throws IOException { String fileName = excelFile.getName().toLowerCase(); if (fileName.endsWith(".xlsx")) { return new XSSFWorkbook(new FileInputStream(excelFile)); } else if (fileName.endsWith(".xls")) { return new HSSFWorkbook(new FileInputStream(excelFile)); } else { throw new IllegalArgumentException("不支持的Excel文件格式"); } } // 获取单元格值作为字符串 private String getCellValueAsString(Cell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case STRING: return cell.getStringCellValue(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getLocalDateTimeCellValue().toString(); } else { return String.valueOf(cell.getNumericCellValue()); } case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); case BLANK: return ""; default: return cell.toString(); } } // 将MultipartFile转换为File private File convertMultipartFileToFile(MultipartFile file) throws IOException { File convFile = new File(System.getProperty("java.io.tmpdir") + "/" + file.getOriginalFilename()); file.transferTo(convFile); return convFile; } }