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.util.ObjectUtils; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.StandardCopyOption; import java.util.ArrayList; import java.util.List; /** * @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 file, Integer templateId) { //查询模板 ExcelTemplateEntity ExcelTemplate = excelTemplateService.getById(templateId); if(ObjectUtils.isEmpty(ExcelTemplate)){ return Result.fail("模板不存在"); } try { if (file.isEmpty()) { return Result.fail("上传的文件为空"); } // 检查是否是Excel文件 else if (isExcelFile(file.getOriginalFilename())) { File tempFile = convertMultipartFileToFile(file); processSingleExcelFile(tempFile,ExcelTemplate); //文件上传 FileUploadVO fileUploadVO = fileUploadService.uploadFile(file); return Result.data(fileUploadVO); } // 检查是否是目录(通过检查是否没有扩展名) else if (isLikelyDirectory(file.getOriginalFilename())) { // 保存上传的临时文件 Path tempDir = createTempDirectory(); Path uploadedFile = tempDir.resolve(file.getOriginalFilename()); Files.copy(file.getInputStream(), uploadedFile, StandardCopyOption.REPLACE_EXISTING); // 处理目录 List excelFiles = new ArrayList<>(); if (Files.isDirectory(uploadedFile)) { // 如果是目录,遍历其中的文件 Files.walk(uploadedFile) .filter(Files::isRegularFile) .filter(path -> isExcelFile(path.getFileName().toString())) .forEach(path -> excelFiles.add(path.toFile())); } else { // 如果是文件,检查是否是Excel文件 if (isExcelFile(uploadedFile.getFileName().toString())) { excelFiles.add(uploadedFile.toFile()); } } return Result.data(processExcelFiles(excelFiles)); } else { return Result.fail("不支持的文件类型,请上传Excel文件(.xlsx, .xls)或包含Excel文件的ZIP文件夹"); } } catch (Exception e) { return Result.fail("文件处理失败: " + e.getMessage()); } } @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; } private boolean isLikelyDirectory(String filename) { if (filename == null) return false; String lowerCaseFilename = filename.toLowerCase(); for (String extension : EXCEL_EXTENSIONS) { if (lowerCaseFilename.endsWith(extension)) { return false; } } return !lowerCaseFilename.contains("."); } // 检查是否是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; } // 处理多个Excel文件 private String processExcelFiles(List excelFiles) throws IOException { if (excelFiles.isEmpty()) { return "未找到任何Excel文件"; } StringBuilder result = new StringBuilder(); result.append("共找到 ").append(excelFiles.size()).append(" 个Excel文件:\n"); for (File excelFile : excelFiles) { result.append("处理文件: ").append(excelFile.getName()).append("\n"); String fileContent = readExcelFile(excelFile); result.append(fileContent).append("\n\n"); // 删除临时文件 if (!excelFile.delete()) { excelFile.deleteOnExit(); } } return result.toString(); } // 处理单个Excel文件 private void processSingleExcelFile(File excelFile,ExcelTemplateEntity ExcelTemplate) throws IOException { String json = ExcelTemplate.getJson(); List tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); //读取第一个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); } // 读取Excel文件内容 private String readExcelFile(File excelFile) throws IOException { StringBuilder content = new StringBuilder(); try (Workbook workbook = getWorkbook(excelFile)) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); content.append("工作表: ").append(sheet.getSheetName()).append("\n"); for (Row row : sheet) { for (Cell cell : row) { content.append(getCellValueAsString(cell)).append("\t"); } content.append("\n"); } content.append("\n"); } } return content.toString(); } // 根据文件类型创建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(); } } // 创建临时目录 private Path createTempDirectory() throws IOException { return Files.createTempDirectory("excel-upload-"); } // 将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; } }