package com.platform.service.impl; import com.alibaba.fastjson.JSON; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.google.common.collect.Maps; import com.platform.exception.BusinessException; import com.platform.model.entity.ExcelDataEntity; import com.platform.model.entity.ExcelTemplateEntity; 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.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.io.InputStream; import java.text.ParseException; import java.text.SimpleDateFormat; 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; private static final String[] EXCEL_EXTENSIONS = {".xlsx", ".xls"}; @Override @Transactional public Result canvas(List files, int caseId, String folderName) throws IOException { //检查文件 List detail = Lists.newArrayList(); int errorCount = 0; int totalCount = 0; for (MultipartFile file : files) { if (isExcelFile(file.getOriginalFilename())) { File tempFile = convertMultipartFileToFile(file); Workbook workbook = getWorkbook(tempFile); if (ObjectUtils.isEmpty(workbook)) { errorCount++; detail.add(AnalysisVO.builder().fileName(file.getOriginalFilename()).errorReason("文件格式错误").build()); } totalCount++; } else { errorCount++; totalCount++; detail.add(AnalysisVO.builder().fileName(file.getOriginalFilename()).errorReason("文件格式错误").build()); } } if (errorCount > 0) { return Result.data(400, AnalysisVO.builder() .detail(detail) .totalCount(totalCount) .errorCount(errorCount) .build(), "部分文件格式错误"); } //查询模板 ExcelTemplateEntity ExcelTemplate = excelTemplateService.getMaxId(); List tableHeaderList; if (ObjectUtils.isEmpty(ExcelTemplate)) { //默认模板 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()); } else { String json = ExcelTemplate.getJson(); tableHeaderList = JSON.parseArray(json, TableHeaderVO.class); } if (files.isEmpty()) { return Result.fail("上传的文件为空"); } List fileUploadList = Lists.newArrayList(); 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("文件转换错误"); } boolean b = processSingleExcelFile(tempFile, tableHeaderList, folderName, fileUploadVO.getId(), caseId, groupedMap, excelData); if (b) { //文件上传 fileUploadList.add(fileUploadVO); } } } //把tableHeaderList中filter为true索引组成一个数字类型的集合 List filterIndex = Lists.newArrayList(); for (TableHeaderVO tableHeaderVO : tableHeaderList) { if (!ObjectUtils.isEmpty(tableHeaderVO) && !ObjectUtils.isEmpty(tableHeaderVO.getFilter()) && tableHeaderVO.getFilter()) { filterIndex.add(tableHeaderVO.getIndex()); } } for (int i = 0; i < tableHeaderList.size(); i++) { if (!ObjectUtils.isEmpty(tableHeaderList.get(i)) && !ObjectUtils.isEmpty(tableHeaderList.get(i).getFilter()) && tableHeaderList.get(i).getFilter()) { filterIndex.add(i); } } Map excelDataMap = Maps.newHashMap(); List userList = Lists.newArrayList(); //保存excel数据,判断是否使用默认模板 if (!ObjectUtils.isEmpty(ExcelTemplate) && !filterIndex.isEmpty()) { List afterDeduplication = Lists.newArrayList(); List afterDeduplication1 = Lists.newArrayList(); deduplicationData(excelDataMap, excelData, filterIndex); //excelDataMap中的value传给afterDeduplication excelDataMap.forEach( (key, excelDatum) -> afterDeduplication.add(excelDatum) ); for (ExcelDataEntity excelDataEntity : afterDeduplication) { if (StringUtils.isNotEmpty(excelDataEntity.getPayeeName()) && !"-".equals(excelDataEntity.getPayeeName())) { if (excelDataEntity.getPayeeName().contains("(个人)")) { excelDataEntity.setPayeeName(excelDataEntity.getPayeeName().replace("(个人)", "")); afterDeduplication1.add(excelDataEntity); } else if (excelDataEntity.getPayeeName().contains("(商户)")) { excelDataEntity.setPayeeName(excelDataEntity.getPayeeName().replace("(商户)", "")); afterDeduplication1.add(excelDataEntity); } else { afterDeduplication1.add(excelDataEntity); } } } excelDataService.saveBatch(afterDeduplication1); //保存无主用户 //afterDeduplication转换成payeeName的字符串集合并去重 List payeeNameList = afterDeduplication1.stream().map(ExcelDataEntity::getPayeeName).distinct().collect(Collectors.toList()); for (String s : payeeNameList) { userList.add(UserEntity.builder() .userName(s) .caseId(caseId) .type("2") .build()); } if (!userList.isEmpty()) { userService.saveUserBatch(userList); } } else { List excelData1 = Lists.newArrayList(); for (ExcelDataEntity excelDataEntity : excelData) { if (StringUtils.isNotEmpty(excelDataEntity.getPayeeName()) && !"-".equals(excelDataEntity.getPayeeName())) { if (excelDataEntity.getPayeeName().contains("(个人)")) { excelDataEntity.setPayeeName(excelDataEntity.getPayeeName().replace("(个人)", "")); excelData1.add(excelDataEntity); } else if (excelDataEntity.getPayeeName().contains("(商户)")) { excelDataEntity.setPayeeName(excelDataEntity.getPayeeName().replace("(商户)", "")); excelData1.add(excelDataEntity); } else { excelData1.add(excelDataEntity); } } } excelDataService.saveBatch(excelData1); //保存无主用户 //afterDeduplication转换成payeeName的字符串集合并去重 List payeeNameList = excelData1.stream().map(ExcelDataEntity::getPayeeName).distinct().collect(Collectors.toList()); for (String s : payeeNameList) { userList.add(UserEntity.builder() .userName(s) .caseId(caseId) .type("2") .build()); } if (!userList.isEmpty()) { userService.saveUserBatch(userList); } } //保存账户 groupedMap.forEach((paymentAccount, paymentBankName) -> userAccountList.add(UserAccountEntity.builder().userName(trimChineseNameFromFilename(folderName)).account(paymentBankName).caseId(caseId).code(paymentAccount).build())); if (!userAccountList.isEmpty()) { userAccountService.saveUserBatch(userAccountList); } //保存用户 if (StringUtils.isNotEmpty(folderName)) { Map idCardInfo = IdCardUtils.parseIdCardInfo(folderName); //删除旧用户 userService.remove(new LambdaQueryWrapper() .eq(UserEntity::getUserName, trimChineseNameFromFilename(folderName)) .eq(UserEntity::getCaseId, caseId)); if (!ObjectUtils.isEmpty(idCardInfo)) { userService.save(UserEntity.builder() .userName(trimChineseNameFromFilename(folderName)) .age(Integer.parseInt(idCardInfo.get("age"))) .idCard(idCardInfo.get("idCard")) .sex(idCardInfo.get("sex")) .idType("身份证") .type("1") .caseId(caseId).build()); } else { userService.save(UserEntity.builder() .userName(trimChineseNameFromFilename(folderName)) .type("1") .caseId(caseId).build()); } } return Result.data(fileUploadList); } private void deduplicationData(Map excelDataMap, List excelData, List filterIndex) { for (ExcelDataEntity excelDatum : excelData) { StringBuilder key = new StringBuilder(); if (filterIndex.contains(0)) { key.append(excelDatum.getType()); } if (filterIndex.contains(1)) { key.append(excelDatum.getDirection()); } if (filterIndex.contains(2)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if (!ObjectUtils.isEmpty(excelDatum.getTime())) { String formattedDate = sdf.format(excelDatum.getTime()); key.append(formattedDate); } } if (filterIndex.contains(3)) { key.append(excelDatum.getAmount()); } if (filterIndex.contains(4)) { key.append(excelDatum.getSerial()); } if (filterIndex.contains(5)) { key.append(excelDatum.getPayeeBankCard()); } if (filterIndex.contains(6)) { key.append(excelDatum.getPayeeAccount()); } if (filterIndex.contains(7)) { key.append(excelDatum.getPayeeMerchant()); } if (filterIndex.contains(8)) { key.append(excelDatum.getPayeeName()); } if (filterIndex.contains(9)) { key.append(excelDatum.getPaymentBankName()); } if (filterIndex.contains(10)) { key.append(excelDatum.getPaymentBankCard()); } if (filterIndex.contains(11)) { key.append(excelDatum.getPaymentAccount()); } if (filterIndex.contains(12)) { key.append(excelDatum.getIp()); } if (filterIndex.contains(13)) { key.append(excelDatum.getMac()); } if (filterIndex.contains(14)) { key.append(excelDatum.getExternalChannel()); } if (filterIndex.contains(15)) { key.append(excelDatum.getPayAccount()); } if (filterIndex.contains(16)) { key.append(excelDatum.getRemark()); } excelDataMap.put(key.toString(), excelDatum); } } 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 void main(String[] args) { System.out.println(trimChineseNameFromFilename(null)); } public static String trimChineseNameFromFilename(String filename) { if (filename == null) { return ""; } // 正则表达式匹配连续的中文字符 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 boolean processSingleExcelFile(File excelFile, List tableHeaderList, String folderName, String fileId, int caseId, Map groupedMap, List excelData) throws IOException { Workbook workbook = getWorkbook(excelFile); if (ObjectUtils.isEmpty(workbook)) { return false; } //读取第一个sheet Sheet sheet = workbook.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()); } } } } return true; } // // 根据文件类型创建Workbook // private Workbook getWorkbook(File excelFile) { // String fileName = excelFile.getName().toLowerCase(); // // if (fileName.endsWith(".xlsx")) { // try { // return new XSSFWorkbook(new FileInputStream(excelFile)); // } catch (Exception e) { // return null; // } // } else if (fileName.endsWith(".xls")) { // try { // return new HSSFWorkbook(new FileInputStream(excelFile)); // } catch (Exception e) { // return null; // } // } else { // throw new IllegalArgumentException("不支持的Excel文件格式"); // } // } private Workbook getWorkbook(File excelFile) throws IOException { if (excelFile == null || !excelFile.exists()) { throw new IllegalArgumentException("文件不存在或为 null"); } try (InputStream inputStream = new FileInputStream(excelFile)) { byte[] header = new byte[8]; if (inputStream.read(header) < 8) { throw new IOException("文件太小,无法识别格式"); } // 检查文件头(Magic Number)判断真实格式 if (isXLSX(header)) { return new XSSFWorkbook(new FileInputStream(excelFile)); } else if (isXLS(header)) { return new HSSFWorkbook(new FileInputStream(excelFile)); } else if (isET(header)) { // .et 文件(WPS 格式),尝试用 POI 解析(可能需要额外依赖) return new HSSFWorkbook(new FileInputStream(excelFile)); // 假设 .et 兼容旧版 .xls } else { throw new IllegalArgumentException("不支持的文件格式,仅支持 .xls, .xlsx, .et"); } } } // 检查是否是 .xlsx(PK/ZIP 头) private boolean isXLSX(byte[] header) { return header[0] == 0x50 && header[1] == 0x4B && header[2] == 0x03 && header[3] == 0x04; } // 检查是否是 .xls(OLE2 头) private boolean isXLS(byte[] header) { return header[0] == (byte) 0xD0 && header[1] == (byte) 0xCF && header[2] == (byte) 0x11 && header[3] == (byte) 0xE0; } // 检查是否是 .et(WPS 格式,可能和 .xls 类似) private boolean isET(byte[] header) { return header[0] == (byte) 0xD0 && header[1] == (byte) 0xCF && header[2] == (byte) 0x11 && header[3] == (byte) 0xE0; // 注意:.et 文件可能和 .xls 有相同的 OLE2 头,可能需要额外检查 } // 获取单元格值作为字符串 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; } }