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[] files, Integer templateId) { //查询模板 ExcelTemplateEntity ExcelTemplate = excelTemplateService.getById(templateId); if(ObjectUtils.isEmpty(ExcelTemplate)){ return Result.fail("模板不存在"); } try { if (files.length == 0) { return Result.fail("上传的文件为空"); } List fileUploadList = com.google.common.collect.Lists.newArrayList(); for (MultipartFile file : files) { // 检查是否是Excel文件 if (isExcelFile(file.getOriginalFilename())) { File tempFile = convertMultipartFileToFile(file); processSingleExcelFile(tempFile,ExcelTemplate); //文件上传 FileUploadVO fileUploadVO = fileUploadService.uploadFile(file); fileUploadList.add(fileUploadVO); } } return Result.data(fileUploadList); } 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; } // 检查是否是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,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); } // 根据文件类型创建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; } }