package com.platform.service.impl; import com.alibaba.fastjson.JSON; import com.platform.exception.BusinessException; import com.platform.model.entity.*; import com.platform.model.resp.Result; import com.platform.model.vo.AnalysisVO; import com.platform.model.vo.FileUploadVO; import com.platform.model.vo.TableHeaderVO; import com.platform.service.*; import com.platform.utils.DateUtils; import com.platform.utils.IdCardUtils; import com.platform.utils.StringUtils; 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.transaction.annotation.Transactional; 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.text.ParseException; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; 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; @Resource private UserService userService; @Resource private UserAccountService userAccountService; @Resource private CaseService caseService; private static final String[] EXCEL_EXTENSIONS = {".xlsx", ".xls"}; @Override @Transactional public Result canvas(MultipartFile[] files, String caseId, String folderName) { //查询模板 CasesEntity caseEntity = caseService.getById(caseId); String templateId = caseEntity.getTemplateId(); if(StringUtils.isEmpty(templateId)){ throw new BusinessException("案件未绑定模板"); } 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(); //错误数量 List detail = Lists.newArrayList(); int errorCount = 0; int totalCount = 0; for (MultipartFile file : files) { // 检查是否是Excel文件 if (isExcelFile(file.getOriginalFilename())) { FileUploadVO fileUploadVO = fileUploadService.uploadFile(file); File tempFile = convertMultipartFileToFile(file); processSingleExcelFile(tempFile, tableHeaderList, folderName, fileUploadVO.getId(), caseId); //文件上传 fileUploadList.add(fileUploadVO); totalCount++; }else { errorCount++; totalCount++; detail.add(AnalysisVO.builder() .folderName(folderName) .fileName(file.getOriginalFilename()) .errorReason("文件格式错误") .build()); } } if (errorCount == 0){ return Result.data(fileUploadList); }else { return Result.data(AnalysisVO.builder() .folderCount(1) .totalCount(totalCount) .errorCount(errorCount) .detail(detail) .build()); } } catch (Exception e) { throw new BusinessException(e.getMessage()); } } private ExcelDataEntity handleExcelData(List tableHeaderList, Row row, String folderName, String fileId, String caseId) { 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())))); if (StringUtils.isNotEmpty(folderName)) { //切割文件名前面是中文的人名,使用正则表达式 trySetValue(() -> excelDataEntity.setPayUserName(trimChineseNameFromFilename(folderName))); } excelDataEntity.setFileId(fileId); excelDataEntity.setUseFlag(true); excelDataEntity.setCaseId(caseId); return excelDataEntity; } public static String trimChineseNameFromFilename(String filename) { // 正则表达式匹配连续的中文字符 Pattern pattern = Pattern.compile("[\u4e00-\u9fa5]+"); Matcher matcher = pattern.matcher(filename); // 只获取第一组匹配的中文 if (matcher.find()) { return matcher.group(); } else { return ""; } } // 辅助方法:简化异常处理 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, String folderName, String fileId, String caseId) 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数据 ExcelDataEntity excelDataEntity = handleExcelData(tableHeaderList, row, folderName, fileId, caseId); excelData.add(excelDataEntity); } if (StringUtils.isNotEmpty(folderName)) { String userName = trimChineseNameFromFilename(folderName); Map idCardInfo = IdCardUtils.parseIdCardInfo(folderName); if (!ObjectUtils.isEmpty(idCardInfo)) { userService.save(UserEntity.builder().userName(userName) .age(Integer.parseInt(idCardInfo.get("age"))) .idCard(idCardInfo.get("idCard")) .sex(idCardInfo.get("sex")) .idType("身份证") .caseId(caseId) .build()); } if (!excelData.isEmpty()) { List paymentBankNames = excelData.stream().map(ExcelDataEntity::getPaymentBankName).distinct().collect(Collectors.toList()); List userAccountList = Lists.newArrayList(); for (String paymentBankName : paymentBankNames) { userAccountList.add(UserAccountEntity.builder() .userName(userName) .account(paymentBankName) .caseId(caseId) .build()); } userAccountService.saveUserBatch(userAccountList); } } //保存excel数据 excelDataService.saveBatch(excelData); } // 根据文件类型创建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; } }