package com.platform.service.impl; import com.google.common.collect.Maps; import com.platform.exception.BusinessException; import com.platform.model.entity.ExcelDataEntity; import com.platform.model.entity.UserAccountEntity; import com.platform.model.entity.UserEntity; 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.AnnotationUtils; 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.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; 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 CasesService caseService; private static final String[] EXCEL_EXTENSIONS = {".xlsx", ".xls"}; @Override @Transactional public Result canvas(List files, int 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("模板不存在"); // } boolean flag = false; //默认模板 List tableHeaderList = Lists.newArrayList(); tableHeaderList.add(TableHeaderVO.builder().name("交易类型").index(2).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("交易主体的出入账标识").index(4).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("交易时间").index(5).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("交易金额").index(7).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("交易流水号").index(8).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("收款方银行卡所属银行卡号").index(12).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("收款方的支付帐号").index(13).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("收款方的商户号").index(15).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("收款方的商户名称").index(16).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("付款方银行卡所属银行名称").index(18).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("付款方银行卡所属银行卡号").index(19).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("付款方的支付帐号").index(20).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("交易支付设备ip").index(22).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("mac地址").index(23).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("银行外部渠道交易流水号").index(27).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("支付帐号").index(29).filter(false).build()); tableHeaderList.add(TableHeaderVO.builder().name("备注").index(30).filter(false).build()); if (files.isEmpty()) { return Result.fail("上传的文件为空"); } List fileUploadList = Lists.newArrayList(); //错误数量 List detail = Lists.newArrayList(); int errorCount = 0; int totalCount = 0; Map groupedMap = Maps.newHashMap(); List userAccountList = Lists.newArrayList(); List excelData = Lists.newArrayList(); for (MultipartFile file : files) { // 检查是否是Excel文件 if (isExcelFile(file.getOriginalFilename())) { FileUploadVO fileUploadVO = fileUploadService.uploadFile(file); File tempFile; try { tempFile = convertMultipartFileToFile(file); } catch (IOException e) { throw new BusinessException("文件转换错误"); } try { processSingleExcelFile(tempFile, tableHeaderList, folderName, fileUploadVO.getId(), caseId, groupedMap, excelData); } catch (IOException e) { throw new BusinessException("文件解析错误"); } //文件上传 fileUploadList.add(fileUploadVO); totalCount++; } else { errorCount++; totalCount++; detail.add(AnalysisVO.builder().folderName(folderName).fileName(file.getOriginalFilename()).errorReason("文件格式错误").build()); } } //把tableHeaderList中filter为true索引组成一个数字类型的集合 List filterIndex = tableHeaderList.stream().filter(TableHeaderVO::getFilter).map(TableHeaderVO::getIndex).collect(Collectors.toList()); Map excelDataMap = Maps.newHashMap(); //保存excel数据,判断是否使用默认模板 if (flag) { List afterDeduplication = Lists.newArrayList(); for (ExcelDataEntity excelDatum : excelData) { StringBuilder key = new StringBuilder(); for (Integer index : filterIndex) { Field field = AnnotationUtils.findFieldByNumericValue(excelDatum, index); if (field != null) { // 获取getter方法 Method getter = AnnotationUtils.findGetterMethod(field, excelDatum.getClass()); if (getter != null) { // 调用getter方法获取值 try { key.append((String) getter.invoke(excelDatum)); } catch (IllegalAccessException | InvocationTargetException ignored) { } } } } excelDataMap.put(key.toString(), excelDatum); } //excelDataMap中的value传给afterDeduplication excelDataMap.forEach( (key, excelDatum) -> afterDeduplication.add(excelDatum) ); excelDataService.saveBatch(afterDeduplication); } else { excelDataService.saveBatch(excelData); } //保存账户 String userName = trimChineseNameFromFilename(folderName); groupedMap.forEach((paymentAccount, paymentBankName) -> userAccountList.add(UserAccountEntity.builder().userName(userName).account(paymentBankName).caseId(caseId).code(paymentAccount).build())); userAccountService.saveUserBatch(userAccountList); if (StringUtils.isNotEmpty(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 (errorCount == 0) { return Result.data(fileUploadList); } else { return Result.data(AnalysisVO.builder().folderCount(1).totalCount(totalCount).errorCount(errorCount).detail(detail).build()); } } private ExcelDataEntity handleExcelData(List tableHeaderList, Row row, String folderName, String fileId, int 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, int caseId, Map groupedMap, List excelData) throws IOException { //读取第一个sheet Sheet sheet = getWorkbook(excelFile).getSheetAt(0); //获取sheet长度 int sheetLength = sheet.getLastRowNum(); 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)) { if (!excelData.isEmpty()) { //excelData转化成以paymentAccount为键paymentBankName为值的map for (ExcelDataEntity excelDatum : excelData) { if ("出账".equals(excelDatum.getDirection())) { groupedMap.put(excelDatum.getPaymentAccount(), excelDatum.getPaymentBankName()); } else if ("入账".equals(excelDatum.getDirection())) { groupedMap.put(excelDatum.getPayeeAccount(), excelDatum.getPayeeBankCard()); } } } } } // 根据文件类型创建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; } }