esayexcel进行模板下载，数据导入，验证不通过，错误信息标注在excel上进行返回下载

场景：普普通通模板下载，加数据导入，分全量和增量，预计20w数据，每一条数据校验，前后端代码贴上（代码有删改，关键代码都有，好朋友们自己取舍，代码一股脑贴上了）

前端代码示例：

  <div style="display: flex; align-items: center; margin-left: auto"><el-button style="width: 160px;height: 25px;font-size: 11px;display: flex;justify-content: end;"><div style="display: flex; align-items: center"><span style="overflow: hidden;text-overflow: ellipsis;white-space: nowrap;width: 122px;">{{ fileName }}</span><span class="el-icon-delete" style="margin-left: 5px" @click="deleteFile"></span></div></el-button><el-upload ref="upload" id="upload" action="" :on-preview="handlePreview" :on-remove="handleRemove":before-remove="beforeRemove" :on-exceed="handleExceed" :file-list="fileList" :limit="1":show-file-list="false" :name="fileName" :on-change="handleChange" accept=".xls, .xlsx":auto-upload="false" :http-request="uploadFile"><el-button icon="el-icon-more" style="height: 25px;width: 35px;display: flex;align-items: center;justify-content: center;margin-left: -2px;"></el-button></el-upload><el-button style="height: 25px; margin-left: 5px; padding-top: 7px" @click="importData">开始导入数据</el-button><div @click="downloadTemplate" style="font-size: 13; color: #56b7ec"><img src="../../../../assets/download.png" class="icon" alt="Download Icon" />模板下载</div><div style="margin-left: 10px"><el-input placeholder="" v-model="searchParams.called_number"style="height: 25px; line-height: 32px; width: 180px" @keyup.enter.native="searchData(1)"><template slot="suffix"><i class="el-icon-search" @click="searchData(1)" style="line-height: 25px,color: #00a9ff"></i></template></el-input></div></div>

function：

  data() {return {fileList: [],fileName: "",carrierList: [],searchParams: {calledNumber: "",},tableData: [],userParams: {show: false,userData: {},operation: "add",editable: true,title: "Add Called Number",},selections: null,pageInfo: {total: 0,pageIndex: 1,pageSize: 10,},cmdLogParams: {visible: false,title: "Command Log",groupId: "",},};},import ToyCore from "toy-core";const request = ToyCore.axios;

deleteFile() {// alert(this.fileName)if (this.fileName == null || this.fileName == "") {return false;}const params = {"dataType": "called","fileName": this.fileName}ResourceApi.deleteFile(params, {headers: {"Content-Type": "text/plain",},}).then((res) => {if (res.isSuccess) {this.$message.success("Delete successful");this.fileName = "";this.fileList = [];} else {this.$message.error(res.data.message);}});}, uploadFile(data) {const file = data.file;const allowedTypes = ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];if (!allowedTypes.includes(file.type)) {this.$message.error("File type must be xls or xlsx");this.fileName = "";this.fileList = [];return false;}const maxSize = 15 * 1024 * 1024;if (file.size > maxSize) {this.$message.error("File size exceeds the limit of 15MB");return;}const formData = new FormData();formData.append("file", file);formData.append("dataType", "called");ResourceApi.upload(formData, {headers: {"Content-Type": "multipart/form-data",},}).then((res) => {if (res.isSuccess) {this.$message.success("Upload successful");} else {this.$message.error(res.data.message);}});}, handleChange(file, fileList) {this.fileName = file.name;this.fileList = fileList;console.log(file);console.log(document.getElementsByClassName("el-upload__input")[0].value);this.$refs.upload.submit();}, handleExceed(files, fileList) {if (this.fileList.length >= 1) {this.$message.error("Only one file can be uploaded");}}, beforeRemove(file, fileList) {return this.$confirm(`确定移除 ${file.name}？`);}, handleRemove(file, fileList) { },handlePreview(file) {alert(file);console.log(file);},async importData() {if (this.fileName == null || this.fileName == "") {this.$message.warning("Upload the file you want to import first！");return false;}const params = {fileName: this.fileName,fullLoad: this.append,dataType: "called"};ResourceApi.vaildData(params).then((res) => {if (res.isSuccess) {params.cellTempTable = res.cellTempTable;params.laiTaiTempTable = res.laiTaiTempTable;params.isFullLoad = res.isFullLoad;console.log(params)ResourceApi.batchImport(params).then((res) => {if (res.isSuccess) {this.$message.success("Import successful");} else {this.$message.error("Import failed");}});} else {this.$message.error(res.message);this.exportErrorData(params);}});const param = {queryParams: [],pageSize: 10,};this.init(param);},async downloadTemplate() {const timestamp = new Date().getTime();try {const params = {"fileName": "","dataType": "called"};const data = await request.postUrl("/nrms-cmconfig/plugins/cmconfig/manageResource/downloadDataTemplate",params,{responseType: "arraybuffer",});console.log(new Uint8Array(data));const blob = new Blob([data], {type: "application/vnd.ms-excel",});const url = window.URL.createObjectURL(blob);const a = document.createElement("a");a.style.display = "none";a.href = url;a.download = "called_template" + timestamp + ".xls";document.body.appendChild(a);a.click();document.body.removeChild(a);window.URL.revokeObjectURL(url);} catch (error) {console.error("下载模板失败:", error);}}, async exportErrorData(params) {try {const data = await request.postUrl("/nrms-cmconfig/plugins/cmconfig/manageResource/exportErorData",params,{responseType: "arraybuffer",});console.log(new Uint8Array(data));const blob = new Blob([data], {type: "application/vnd.ms-excel",});const url = window.URL.createObjectURL(blob);const a = document.createElement("a");a.style.display = "none";a.href = url;a.download = "called_template_error_data.xls";document.body.appendChild(a);a.click();document.body.removeChild(a);window.URL.revokeObjectURL(url);} catch (error) {console.error("下载模板失败:", error);}},},

这里有个问题就是下载模板的时候使用request.postUrl，我们项目都是自己封装了一个api请求，用封装的请求总是下载不好，使用xxl的时候下载的模板没有样式，于是哟用了最原始的方案可以看到下载模板和数据导入请求方式的不一致；数据导入一开始后端放在一个方法里了，但是前端请求总是超时，异步处理的话和要求不搭，甲方要求校验数据不通过接着返回错误模板数据，并标注信息，现在是后端分为校验和数据导入两部分；目前方案如此，后期在优化，给出后端代码：

controller：

  @ResponseBody@RequestMapping(value = "/downloadDataTemplate")@ApiOperation(value = "downloadDataTemplate")public void downloadDataTemplate(HttpServletResponse response, @RequestBody Map<String, String> params) {resourceService.downloadDataTemplate(response, params.getOrDefault("fileName", ""), params.getOrDefault("dataType", ""));}@RequestMapping(value = "/upload")@ResponseBody@ApiOperation(value = "upload")public Map<String, Object> upload(@RequestParam("file") MultipartFile file,@RequestParam("dataType") String dataType) {Map<String, Object> result = new HashMap<>();log.info("upload file start -----");if (file != null) {result = resourceService.upload(file, dataType);}log.info("upload file end -----result={}", result);return result;}@RequestMapping(value = "/deleteFile")@ResponseBody@ApiOperation(value = "deleteFile")public Map<String, Object> deleteFile(@RequestBody Map<String, String> param) {Map<String, Object> result = new HashMap<>();log.info("delete file start -----{}", param);try {result = resourceService.deleteFile(param.get("fileName"), param.get("dataType"));result.put("isSuccess", true);result.put("message", "Delete successfully");} catch (Exception e) {result.put("isSuccess", false);result.put("message", "Delete Failed");log.info("Delete error----------------Exception:", e);}log.info("delete file end -----result={}", result);return result;}@RequestMapping(value = "/vaildData")@ResponseBody@ApiOperation(value = "vaildData")public Map<String, Object> vaildData(@RequestBody Map<String, Object> params, HttpServletResponse response) {Map<String, Object> result = new HashMap<>();log.info("Verify data format start -----");result = resourceService.vaildData(params, response);log.info("Verify data format start -----result={}", result);return result;}@RequestMapping(value = "/batchImport")@ResponseBody@ApiOperation(value = "batchImport")public Map<String, Object> batchImport(@RequestBody Map<String, Object> params) {Map<String, Object> result = new HashMap<>();log.info("Import file start -----");result = resourceService.batchImport(params);log.info("upload file end -----result={}", result);return result;}@RequestMapping(value = "/exportErorData")@ResponseBody@ApiOperation(value = "exportErorData")public void exportErorData(HttpServletResponse response, @RequestBody Map<String, Object> params) {log.info("Export errorDataFile start -----");resourceService.exportErrorMsg(response, String.valueOf(params.get("fileName")), params.get("dataType") + "");}

service：

    void downloadDataTemplate(HttpServletResponse response,String filePath,String dataType) ;Map<String, Object> upload(MultipartFile multipartFile, String dataType);Map<String, Object> deleteFile(String fileName,String dataType);Map<String, Object> batchImport(Map<String, Object> params);Map<String, Object> vaildData(Map<String, Object> params,HttpServletResponse response);void exportErrorMsg(HttpServletResponse response,String fileName,String type);

impl：部分代码做了屏蔽，基本的使用代码都是包含的，另外使用了truncate清表，大家也可以酌情使用事务控制

   private CellDataListener cellDataListener = new CellDataListener();private List<ExcelErrorMessage> errorList = new ArrayList<>();List<CellTemplate> saiList = new ArrayList<>();List<CellTemplate> laiList = new ArrayList<>();List<CellTemplate> taiList = new ArrayList<>();List<CellTemplate> laiData = new ArrayList<>();List<CellTemplate> taiData = new ArrayList<>();private String carrierId = "";Map<String, List<CellTemplate>> validationData = new HashMap<>();List<SpecialNumber> specialNumberList = new ArrayList<>();String relatedGroup = "";

@Overridepublic void downloadDataTemplate(HttpServletResponse response, String filePath, String dataType) {//yaml文件配置的路径filePath = dataDownloadPath;exportExcelFile(response, filePath);}public void exportExcelFile(HttpServletResponse response, String filePath) {File file = new File(filePath);if (!file.exists()) {log.error("文件未找到: " + file.getAbsolutePath());throw new RuntimeException("文件未找到: " + file.getAbsolutePath());}try {FileInputStream fileInputStream = new FileInputStream(file);response.setHeader("Content-Type", "application/vnd.ms-excel");response.addHeader("Content-Disposition", "attachment;filename=template.xls");OutputStream outputStream = response.getOutputStream();byte[] buffer = new byte[2048];int bytesRead = -1;while ((bytesRead = fileInputStream.read(buffer)) != -1) {outputStream.write(buffer, 0, bytesRead);}outputStream.flush();fileInputStream.close();outputStream.close();} catch (Exception e) {e.printStackTrace();}}@Overridepublic Map<String, Object> upload(MultipartFile multipartFile, String dataType) {String uploadPath = "";if ("type2".equals(dataType)) {uploadPath = whiteUploadPath;} else if ("type1".equals(dataType)) {uploadPath = blackUploadPath;} else if ("type3".equals(dataType)) {uploadPath = smsUploadPath;} else if ("fzData".equals(dataType)) {uploadPath = cellUploadPath;} else if ("type4".equals(dataType)) {uploadPath = vipUploadPath;} else if ("type5".equals(dataType)) {uploadPath = calledUploadPath;}log.info("Start uploadExcel--------------------------multipartFile:{}", multipartFile);Map<String, Object> result = new HashMap<>();try {String fileName = multipartFile.getOriginalFilename();if (fileName == null || fileName.isEmpty()) {result.put("isSuccess", false);result.put("message", "File name is empty");return result;}File saveFile = new File(uploadPath + fileName);saveFile.setReadable(true, false);saveFile.setWritable(true, false);multipartFile.transferTo(saveFile);result.put("isSuccess", true);result.put("message", "File saved successfully");result.put("data", saveFile.getAbsolutePath());} catch (IOException e) {result.put("isSuccess", false);result.put("message", "Failed to save file");log.error("Failed to save file: ", e);}return result;}@Overridepublic Map<String, Object> deleteFile(String fileName, String dataType) {String filePath = "";if ("fzData".equals(dataType)) {filePath = cellUploadPath + fileName;} else if ("type2".equals(dataType)) {filePath = whiteUploadPath + fileName;} else if ("type1".equals(dataType)) {filePath = blackUploadPath + fileName;} else if ("type3".equals(dataType)) {filePath = smsUploadPath + fileName;} else if ("type4".equals(dataType)) {filePath = vipUploadPath;} else if ("type5".equals(dataType)) {filePath = calledUploadPath;}log.info("Start deleteLocalFile--------------------------filePath:{}", filePath);Map<String, Object> result = new HashMap<>();File file = new File(filePath);if (file.delete()) {result.put("isSuccess", true);} else {result.put("isSuccess", false);}return result;}@Overridepublic Map<String, Object> batchImport(Map<String, Object> params) {Map<String, Object> result = new HashMap<>();Map<String, Object> resultMap = new HashMap<>();Integer insertCount = 0;String cellTempTable = (String) params.get("cellTempTable");String laiTaiTempTable = (String) params.get("laiTaiTempTable");boolean isFullLoad = (boolean) params.get("fullLoad");String dataType = (String) params.get("dataType");try {if ("fzData".equalsIgnoreCase(dataType)) {resultMap = processCellData(isFullLoad, validationData);} else {resultMap = processData(specialNumberList, dataType, relatedGroup);}insertCount = Integer.parseInt(resultMap.get("insert") + "");if (insertCount > 0) {result.put("isSuccess", true);result.put("message", insertCount + " pieces of data are successfully imported");} else {result.put("isSuccess", false);result.put("message", "Data import failure");}} catch (Exception e) {if (!isFullLoad) {ppqMapper.recoveryContent(cellTempTable, TABLE_CELL);ppqMapper.recoveryContent(laiTaiTempTable, TABLE_LAITAI);}result.put("isSuccess", false);result.put("message", "Failed to process file: " + e.getMessage());log.error("Failed to process file: ", e);}if (!isFullLoad) {ppqMapper.removeTable(cellTempTable);ppqMapper.removeTable(laiTaiTempTable);}return result;}@Overridepublic Map<String, Object> vaildData(Map<String, Object> params, HttpServletResponse response) {validationData = new HashMap<>();relatedGroup = "";specialNumberList = new ArrayList<>();String fileName = (String) params.get("fileName");String dataType = (String) params.get("dataType");String filePath = "";Map<String, Object> result = new HashMap<>();String cellTempTable = "";String laiTaiTempTable = "";result.put("isSuccess", true);boolean isFullLoad = (boolean) params.get("fullLoad");try {if ("fzData".equals(dataType)) {//cell类型数据filePath = cellUploadPath + fileName;if (!isFullLoad) {//创建临时表cellTempTable = "TEMP_TABLE1_DATA" + System.currentTimeMillis();laiTaiTempTable = "TEMP_TABLE2_DATA" + System.currentTimeMillis();ppqMapper.createTempTable(TABLE_CELL, cellTempTable);ppqMapper.createTempTable(TABLE_LAITAI, laiTaiTempTable);ppqMapper.truncateTable(TABLE_CELL);ppqMapper.truncateTable(TABLE_LAITAI);}cellDataListener.initFlag();Path path = Paths.get(filePath);if (!Files.exists(path)) {result.put("isSuccess", false);result.put("message", "File does not exist");if (!isFullLoad) {ppqMapper.recoveryContent(cellTempTable, TABLE_CELL);ppqMapper.recoveryContent(laiTaiTempTable, TABLE_LAITAI);}return result;}Map<String, List<CellTemplate>> sheetDataMap = readExcelBySheetName(filePath);//校验模板数据，生成错误信息Map<String, Object> validationResultMap = validateAndProcessData(filePath, sheetDataMap, isFullLoad);if (!cellDataListener.isVaild()) {result.put("isSuccess", false);result.put("message", "Template data error");if (!isFullLoad) {ppqMapper.recoveryContent(cellTempTable, TABLE_CELL);ppqMapper.recoveryContent(laiTaiTempTable, TABLE_LAITAI);}return result;}validationData = (Map<String, List<CellTemplate>>) validationResultMap.get("data");} else {if ("type2".equals(dataType)) {filePath = whiteUploadPath + fileName;} else if ("type1".equals(dataType)) {filePath = blackUploadPath + fileName;} else if ("type3".equals(dataType)) {filePath = smsUploadPath + fileName;} else if ("type4".equals(dataType)) {filePath = vipUploadPath + fileName;} else if ("type5".equals(dataType)) {filePath = calledUploadPath + fileName;}Path path = Paths.get(filePath);if (!Files.exists(path)) {result.put("isSuccess", false);result.put("message", "File does not exist");return result;}List<SpecialNumber> numberList = readExcelBySheetNo(filePath);Map<String, Object> validationResultMap = validateData(filePath, numberList, dataType);if (!Boolean.parseBoolean(validationResultMap.get("isSuccess") + "")) {result.put("isSuccess", false);result.put("message", "Template data error");return result;}specialNumberList = (List<SpecialNumber>) validationResultMap.get("data");relatedGroup = params.getOrDefault("related_group", "") + "";}} catch (Exception e) {if (!isFullLoad) {ppqMapper.recoveryContent(cellTempTable, TABLE_CELL);ppqMapper.recoveryContent(laiTaiTempTable, TABLE_LAITAI);}result.put("isSuccess", false);result.put("message", "Failed to process file: " + e.getMessage());log.error("Failed to process file: ", e);}result.put("isFullLoad", isFullLoad);result.put("cellTempTable", cellTempTable);result.put("laiTaiTempTable", laiTaiTempTable);return result;}@Overridepublic void exportErrorMsg(HttpServletResponse response, String fileName, String type) {String filePath = "";if ("fzData".equals(type)) {filePath = cellUploadPath + fileName;} else if ("type2".equals(type)) {filePath = whiteUploadPath + fileName;} else if ("type1".equals(type)) {filePath = blackUploadPath + fileName;} else if ("type3".equals(type)) {filePath = smsUploadPath + fileName;} else if ("type4".equals(type)) {filePath = vipUploadPath + fileName;} else if ("type5".equals(type)) {filePath = calledUploadPath + fileName;}exportExcelFile(response, filePath);}public Map<String, Object> processData(List<SpecialNumber> listData, String type, String userGroupId) {Map<String, Object> returnMap = new HashMap<>();Integer insertCount = 0;if ("type2".equals(type)) {listData = listData.stream().map(number -> {number.setUuid(UUID.randomUUID().toString().replace("-", ""));number.setRelatedGroup(userGroupId);return number;}).collect(Collectors.toList());insertCount = ppqMapper.saveWhiteNumber(listData);} else if ("type1".equals(type)) {listData = listData.stream().peek(number -> number.setUuid( UUID.randomUUID().toString().replace("-", ""))).collect(Collectors.toList());insertCount = ppqMapper.saveBlackNumber(listData);//保存指令saveActiveCommand(listData, "type1");} else if ("type3".equals(type)) {listData = listData.stream().map(number -> {number.setUuid(UUID.randomUUID().toString().replace("-", ""));number.setRelatedGroup(userGroupId);return number;}).collect(Collectors.toList());insertCount = ppqMapper.saveSmsNumber(listData);} else if ("type4".equals(type)) {listData = listData.stream().peek(number -> number.setUuid(UUID.randomUUID().toString().replace("-", ""))).collect(Collectors.toList());insertCount = ppqMapper.saveVipNumber(listData);//保存指令saveActiveCommand(listData, "type4");} else if ("type5".equals(type)) {listData = listData.stream().peek(number -> number.setUuid( UUID.randomUUID().toString().replace("-", ""))).collect(Collectors.toList());insertCount = ppqMapper.saveCalledNumber(listData);//保存指令saveActiveCommand(listData, "SP");}returnMap.put("count", insertCount);return returnMap;}public Map<String, Object> saveActiveCommand(List<SpecialNumber> listData, String type) {Map<String, Object> resultMap = new HashMap<>();for (SpecialNumber specialNumber : listData) {Map<String, Object> saveActiveResult = pmcService.saveActiveCommand("type4".equalsIgnoreCase(type) ? specialNumber.getMsisdn() : specialNumber.getNumber(), specialNumber.getCarrier(), type, specialNumber.getUuid());boolean saveFlag = (boolean) saveActiveResult.get("success");//判断是否保存成功if (!saveFlag) {//执行失败resultMap.put("isSuccess", false);resultMap.put("message", "save Command Failed!");return resultMap;}}resultMap.put("isSuccess", true);resultMap.put("message", "Operation successful.");return resultMap;}//cell数据入库public Map<String, Object> processCellData(boolean isFullLoad, Map<String, List<CellTemplate>> listData) {Map<String, Object> resultMap = new HashMap<>();Integer insertLaiTaiCount = 0;Integer insertCount = 0;//lai&tailistData.remove(CARRIER_KEY);List<CellTemplate> laiTaiList = new ArrayList<>();laiTaiList.addAll(listData.getOrDefault(LAI_KEY, Collections.emptyList()));laiTaiList.addAll(listData.getOrDefault(TAI_KEY, Collections.emptyList()));listData.remove(LAI_KEY);listData.remove(TAI_KEY);//cellDataList<CellTemplate> allCellTemplates = listData.values().stream().flatMap(List::stream).collect(Collectors.toList());for (List<CellTemplate> batch : batch(laiTaiList, BATCH_INSERT_SIZE)) {insertLaiTaiCount = ppqMapper.saveLaiTai(batch, carrierId);}for (List<CellTemplate> batch : batch(allCellTemplates, BATCH_INSERT_SIZE)) {insertCount = ppqMapper.saveCell(batch, carrierId);}resultMap.put("insertCount", insertCount + insertLaiTaiCount);resultMap.put("insert", insertCount);return resultMap;}private <T> List<List<T>> batch(List<T> list, int batchSize) {List<List<T>> batches = new ArrayList<>();for (int i = 0; i < list.size(); i += batchSize) {batches.add(list.subList(i, Math.min(i + batchSize, list.size())));}return batches;}Map<String, List<CellTemplate>> readExcelBySheetName(String filePath) {CellDataListener cellListener = new CellDataListener();try (ExcelReader excelReader = EasyExcel.read(filePath).build()) {ReadSheet readCarrierSheet = EasyExcel.readSheet("Carrier").head(CellTemplate.class).registerReadListener(cellListener).build();ReadSheet readCgiSheet = EasyExcel.readSheet("CGI").head(CellTemplate.class).registerReadListener(cellListener).build();ReadSheet readSaiSheet = EasyExcel.readSheet("SAI").head(CellTemplate.class).registerReadListener(cellListener).build();ReadSheet readLaiSheet = EasyExcel.readSheet("LAI").head(CellTemplate.class).registerReadListener(cellListener).build();ReadSheet readEcgiSheet = EasyExcel.readSheet("ECGI").head(CellTemplate.class).registerReadListener(cellListener).build();ReadSheet readTaiSheet = EasyExcel.readSheet("TAI").head(CellTemplate.class).registerReadListener(cellListener).build();excelReader.read(readCarrierSheet, readCgiSheet, readCgiSheet, readLaiSheet, readEcgiSheet, readTaiSheet, readSaiSheet);}Map<String, List<CellTemplate>> sheetDataMap = cellListener.getDataList();log.info("execelData---------:{}", sheetDataMap);return sheetDataMap;}List<SpecialNumber> readExcelBySheetNo(String filePath) {NumberDataListener numberDataListener = new NumberDataListener();try (ExcelReader excelReader = EasyExcel.read(filePath).build()) {ReadSheet sheet = EasyExcel.readSheet(0).head(SpecialNumber.class).registerReadListener(numberDataListener).build();excelReader.read(sheet);}List<SpecialNumber> sheetDataList = numberDataListener.getDataList();log.info("resultList:{}", sheetDataList);return sheetDataList;}private Map<String, Object> validateData(String filePath, List<SpecialNumber> list, String dataType) {List<String> messageInfo = new ArrayList();Map<String, Object> returnMap = new HashMap<>();if (list.size() > 0) {errorList.clear();messageInfo.clear();for (int i = 0; i < list.size(); i++) {if (null != list.get(i)) {StringBuilder strBuilder = validateNumber("type4".equals(dataType) ? list.get(i).getMsisdn() : list.get(i).getNumber(), list.get(i).getDescription(), dataType);if (strBuilder.length() > 0) {messageInfo.add("[" + (i + 1) + "]" + strBuilder);}}//vip含有imsi字段，须遵循规则if ("type4".equals(dataType)) {StringBuilder strBuilder = validateImsi(list.get(i).getImsi());if (StringUtils.isNotEmpty(strBuilder)) {messageInfo.add("[" + (i + 1) + "]" + strBuilder);}}//校验是否唯一 vip号码表格字段是msisdn，其他均为numberInteger count = ppqMapper.isUniqueNumber("type4".equals(dataType) ? list.get(i).getMsisdn() : list.get(i).getNumber());if (count > 0) {messageInfo.add("[" + (i + 1) + "]" + " The number is already exist in table; ");}//校验carrierif (!"type5".equals(dataType)) {List<Map<String, String>> queryParams = new ArrayList<>();if (null != list.get(i).getCarrier() && !"".equals(list.get(i))) {Map<String, String> param = new HashMap<>();param.put("key", "CARRIER");param.put("value", list.get(i).getCarrier());queryParams.add(param);List<PMCCarrier> queryCarrier = ppqMapper.queryCarrier(queryParams, null, null);if (queryCarrier.size() < 1) {carrierId = null;messageInfo.add("[" + (i + 1) + "]" + " The Carrier name does not exist in table; ");} else {carrierId = queryCarrier.get(0).getIntId();list.get(i).setCarrier(carrierId);}} else {messageInfo.add("[" + (i + 1) + "]" + " The required  field Carrier is empty; ");}}}}if (messageInfo.size() > 0) {returnMap.put("isSuccess", false);returnMap.put("data", list);processingErrorMsg(filePath, messageInfo, 0);} else {returnMap.put("data", list);returnMap.put("isSuccess", true);}log.info("Error message: {}", messageInfo.toString());return returnMap;}public static StringBuilder validateImsi(String imsi) {StringBuilder resultMsg = new StringBuilder();if (StringUtils.isNotBlank(imsi)) {String regex = "^\\d{1,15}$";boolean regexMatches = imsi.matches(regex);if (!regexMatches) {resultMsg.append("the data format is incorrect; ");}}return resultMsg;}public static StringBuilder validateNumber(String number, String description, String dataType) {StringBuilder resultMsg = new StringBuilder();if (description != null && description.length() > 200) {resultMsg.append("The maximum length of the number's description is 200 characters. ");}if (StringUtils.isEmpty(number)) {resultMsg.append("the required field " + (dataType.equalsIgnoreCase("type4") ? "MSISDN" : "NUMBER") + " is empty; ");return resultMsg;}if (dataType.equalsIgnoreCase("type4") ? number.length() > 16 : number.length() > 32) {resultMsg.append("The maximum length of  " + (dataType.equalsIgnoreCase("type4") ? "MSISDN" : "NUMBER") + " is 32 characters; ");}if (!number.matches("^\\d+$")) {resultMsg.append("The  number consists of digits; ");}if (number.startsWith("00")) {resultMsg.append("The " + (dataType.equalsIgnoreCase("type4") ? "MSISDN" : "NUMBER") + " must be in the format of Country code(without 00)+Telephone number; ");}return resultMsg;}private Map<String, Object> validateAndProcessData(String filePath, Map<String, List<CellTemplate>> sheetDataMap, Boolean isFullLoad) {List<String> messageInfo = new ArrayList<>();Map<String, String> validationErrors = new HashMap<>();Map<String, List<CellTemplate>> resultMap = new HashMap<>();Map<String, Object> returnMap = new HashMap<>();List<CellTemplate> cgiList = new ArrayList<>();List<CellTemplate> ecgiList = new ArrayList<>();AtomicReference<List<Map<String, Object>>> cellList = new AtomicReference<>(new ArrayList<>());List<Future<?>> futures = new ArrayList<>();List<CellTemplate> carrierData = sheetDataMap.get("Carrier");List<String> laiAndTaiSeen = new ArrayList<>();List<String> seen = new ArrayList<>();final ExecutorService executorService = Executors.newFixedThreadPool(20);saiList.clear();laiList.clear();taiList.clear();laiData.clear();taiData.clear();// Carrierif (carrierData != null && carrierData.size() == 1) {errorList.clear();messageInfo.clear();List<Map<String, String>> queryParams = new ArrayList<>();Map<String, String> param = new HashMap<>();param.put("key", "CARRIER");param.put("value", carrierData.get(0).getCarrierName());queryParams.add(param);List<PMCCarrier> queryCarrier = ppqMapper.queryCarrier(queryParams, null, null);if (queryCarrier.size() < 1) {carrierId = null;messageInfo.add("[1]" + " The Carrier name does not exist in table; ");} else {carrierId = queryCarrier.get(0).getIntId();}} else {messageInfo.add("[1]" + " Only one carrier is allowed in the carrier sheet; ");}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 0);}//excel重复数据if (isFullLoad) {List<Map<String, Object>> cellListResult = new ArrayList<>();cellListResult.addAll(ppqMapper.queryCell(null, null, null, null));cellListResult.addAll(ppqMapper.queryLaiTai(null, null, null));cellList.set(cellListResult);}// LAIlaiData = sheetDataMap.get("LAI");if (null != laiData) {int batchNo = 0;
//            while (batchNo * BATCH_SIZE <= laiData.size()) {
//                int startNo = batchNo * BATCH_SIZE;
//                int end = Math.min(startNo + BATCH_SIZE, laiData.size());
//                List<CellTemplate> batch = laiData.subList(startNo, end);Future<?> laiFuture = executorService.submit(() -> {synchronized (this) {errorList.clear();messageInfo.clear();laiList.clear();boolean laiFound = false;for (int j = 0; j < laiData.size(); j++) {CellTemplate laiItem = laiData.get(j);
//                            int finalJ = startNo + j;int finalJ = j;if (isFullLoad) {laiFound = cellList.get().stream().anyMatch(fzData -> fzData.containsKey("CELLID") && fzData.get("CELLID").equals(laiData.get(finalJ).getId()));}if (laiFound) {messageInfo.add("[" + (finalJ + 1) + "]" + " The data already exists in the table; ");}if (!laiAndTaiSeen.contains(laiItem.getId())) {laiAndTaiSeen.add(laiItem.getId());if (isValidLai(laiItem).length() < 1 && !laiFound) {laiList.add(laiItem);resultMap.put("LAI", laiList);} else {messageInfo.add("[" + (j + 1) + "]" + isValidLai(laiItem));}} else {messageInfo.add("[" + (finalJ + 1) + "]" + " The same data exists in the imported template; ");}}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 3);}}});futures.add(laiFuture);batchNo++;
//            }}// CGIList<CellTemplate> cgiData = sheetDataMap.get("CGI");if (null != cgiData) {int batchNo = 0;
//            while (batchNo * BATCH_SIZE <= cgiData.size()) {
//                int startNo = batchNo * BATCH_SIZE;
//                int end = Math.min(startNo + BATCH_SIZE, cgiData.size());
//                List<CellTemplate> batch = cgiData.subList(startNo, end);Future<?> cgiFuture = executorService.submit(() -> {synchronized (this) {errorList.clear();messageInfo.clear();boolean cgiFound = false;for (int j = 0; j < cgiData.size(); j++) {int finalI = j;if (isFullLoad) {cgiFound = cellList.get().stream().anyMatch(fzData -> fzData.containsKey("CELLID") && fzData.get("CELLID").equals(cgiData.get(finalI).getId()));}if (cgiFound) {messageInfo.add("[" + (j + 1) + "]" + " The data already exists in the table; ");}if (!laiAndTaiSeen.contains(cgiData.get(j).getId())) {laiAndTaiSeen.add(cgiData.get(j).getId());if (isValidCgi(cgiData.get(j)).length() < 1 && !cgiFound) {cgiData.get(j).setLatitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(cgiData.get(j).getLatitude()) + "");cgiData.get(j).setLongitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(cgiData.get(j).getLongitude()) + "");cgiList.add(cgiData.get(j));resultMap.put("CGI", cgiList);} else {messageInfo.add("[" + (finalI + 1) + "]" + isValidCgi(cgiData.get(j)));}} else {messageInfo.add("[" + (finalI + 1) + "]" + " The same data exists in the imported template; ");}}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 1);}}});futures.add(cgiFuture);batchNo++;
//            }}// TAItaiData = sheetDataMap.get("TAI");if (null != taiData) {
//            int batchNo = 0;
//            while (batchNo * BATCH_SIZE <= taiData.size()) {
//                int startNo = batchNo * BATCH_SIZE;
//                int end = Math.min(startNo + BATCH_SIZE, taiData.size());
//                List<CellTemplate> batch = taiData.subList(startNo, end);Future<?> taiFuture = executorService.submit(() -> {synchronized (this) {messageInfo.clear();errorList.clear();boolean taiFound = false;for (int j = 0; j < taiData.size(); j++) {int finalI = j;if (isFullLoad) {taiFound = cellList.get().stream().anyMatch(fzData -> fzData.containsKey("CELLID") && fzData.get("CELLID").equals(taiData.get(finalI).getId()));}if (taiFound) {messageInfo.add("[" + (finalI + 1) + "]" + " The data already exists in the table; ");}if (!seen.contains(taiData.get(j).getId())) {seen.add(taiData.get(j).getId());if (isValidTai(taiData.get(finalI)).length() < 1 && !taiFound) {taiList.add(taiData.get(finalI));resultMap.put("TAI", taiList);} else {messageInfo.add("[" + (finalI + 1) + "]" + isValidTai(taiData.get(j)));}} else {messageInfo.add("[" + (finalI + 1) + "]" + " The same data exists in the imported template; ");}}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 5);messageInfo.clear();}}});futures.add(taiFuture);
//                batchNo++;
//            }}// SAIList<CellTemplate> saiData = sheetDataMap.get("SAI");if (null != saiData) {int batchNo = 0;
//            while (batchNo * BATCH_SIZE <= saiData.size()) {
//                int startNo = batchNo * BATCH_SIZE;
//                int end = Math.min(startNo + BATCH_SIZE, saiData.size());
//                List<CellTemplate> batch = saiData.subList(startNo, end);Future<?> saiFuture = executorService.submit(() -> {synchronized (this) {messageInfo.clear();errorList.clear();boolean saiFound = false;for (int j = 0; j < saiData.size(); j++) {int finalI = j;if (isFullLoad) {saiFound = cellList.get().stream().anyMatch(fzData -> fzData.containsKey("CELLID") && fzData.get("CELLID").equals(saiData.get(finalI).getId()));}if (saiFound) {messageInfo.add("[" + (finalI + 1) + "]" + " The data already exists in the table; ");}if (!seen.contains(saiData.get(finalI).getId())) {seen.add(saiData.get(j).getId());if (isValidSai(saiData.get(finalI)).length() < 1) {saiData.get(finalI).setLatitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(saiData.get(finalI).getLatitude()) + "");saiData.get(finalI).setLongitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(saiData.get(finalI).getLongitude()) + "");saiList.add(saiData.get(finalI));resultMap.put("SAI", saiList);} else {messageInfo.add("[" + (finalI + 1) + "]" + isValidSai(saiData.get(finalI)));}} else {messageInfo.add("[" + (finalI + 1) + "]" + " The same data exists in the imported template; ");}}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 2);}}});futures.add(saiFuture);batchNo++;
//            }}// ECGIList<CellTemplate> ecgiData = sheetDataMap.get("ECGI");if (null != ecgiData) {int batchNo = 0;
//            while (batchNo * BATCH_SIZE <= ecgiData.size()) {
//                int startNo = batchNo * BATCH_SIZE;
//                int end = Math.min(startNo + BATCH_SIZE, ecgiData.size());
//                List<CellTemplate> batch = ecgiData.subList(startNo, end);Future<?> ecgiFuture = executorService.submit(() -> {synchronized (this) {messageInfo.clear();errorList.clear();boolean ecgiFound = false;for (int j = 0; j < ecgiData.size(); j++) {int finalI = j;if (isFullLoad) {ecgiFound = cellList.get().stream().anyMatch(fzData -> fzData.containsKey("CELLID") && fzData.get("CELLID").equals(ecgiData.get(finalI).getId()));}if (ecgiFound) {messageInfo.add("[" + (finalI + 1) + "]" + " The data already exists in the table; ");}if (!seen.contains(ecgiData.get(finalI).getId())) {seen.add(ecgiData.get(j).getId());if (isValidEcgi(ecgiData.get(finalI)).length() < 1) {ecgiData.get(finalI).setLatitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(ecgiData.get(finalI).getLatitude()) + "");ecgiData.get(finalI).setLongitude(LongitudeAndLatitudeUtils.convertLatitudeAndLongitude(ecgiData.get(finalI).getLongitude()) + "");ecgiList.add(ecgiData.get(finalI));resultMap.put("ECGI", ecgiList);} else {messageInfo.add("[" + (finalI + 1) + "]" + isValidEcgi(ecgiData.get(finalI)));}} else {messageInfo.add("[" + (finalI + 1) + "]" + " The same data exists in the imported template; ");}}if (messageInfo.size() > 0) {processingErrorMsg(filePath, messageInfo, 4);messageInfo.clear();}}});futures.add(ecgiFuture);batchNo++;
//            }}for (Future<?> future : futures) {try {future.get();} catch (InterruptedException | ExecutionException e) {e.printStackTrace();} finally {executorService.shutdown();}}seen.clear();laiAndTaiSeen.clear();returnMap.put("validationErrors", validationErrors);returnMap.put("data", resultMap);log.info("Error message: {}", validationErrors.toString());return returnMap;}public StringBuilder isValidCgi(CellTemplate cellTemplate) {StringBuilder resultMsg = new StringBuilder();if (null == cellTemplate.getId()) {resultMsg.append("the required field ID is empty; ");} else {boolean exists = laiData.stream().anyMatch(fzData -> null != cellTemplate && null != fzData.getId() && cellTemplate.getId().contains(fzData.getId()));if (!exists) {resultMsg.append("the ID not exist in LAI sheet; ");} else {if (!validIdLength(cellTemplate.getId(), 13, 14)) {resultMsg.append("the length of ID is not correct; ");} else {if (!isValidSaiAndCgiHex(cellTemplate.getId())) {resultMsg.append("the format of ID is not correct; ");}}}}if (null == cellTemplate.getLongitude()) {resultMsg.append("the required field longitude is empty; ");} else {resultMsg.append(isValidLongitude(cellTemplate.getLongitude()));}if (null == cellTemplate.getLatitude()) {resultMsg.append("the required field latitude is empty; ");} else {resultMsg.append(isValidLatitude(cellTemplate.getLatitude()));}resultMsg.append(isValidName(cellTemplate.getName(), 200));return resultMsg;}public StringBuilder isValidLai(CellTemplate cellTemplate) {StringBuilder resultMsg = new StringBuilder();if (null == cellTemplate.getId()) {return resultMsg.append("the required field ID is empty; ");} else {if (!validIdLength(cellTemplate.getId(), 9, 10)) {resultMsg.append("the length of ID is not correct; ");} else {if (!isValidLaiHex(cellTemplate.getId())) {resultMsg.append("the format of ID is not correct; ");}}}return resultMsg.append(isValidName(cellTemplate.getName(), 200));}public StringBuilder isValidSai(CellTemplate cellTemplate) {StringBuilder resultMsg = new StringBuilder();if (null == cellTemplate.getId()) {resultMsg.append("the required field ID is empty; ");} else {if (!validIdLength(cellTemplate.getId(), 13, 14)) {resultMsg.append("the length of ID is not correct; ");} else {if (!isValidSaiAndCgiHex(cellTemplate.getId())) {resultMsg.append("the format of ID is not correct; ");}}}if (null == cellTemplate.getLongitude()) {resultMsg.append("the required field longitude is empty ;");} else {resultMsg.append(isValidLongitude(cellTemplate.getLongitude()));}if (null == cellTemplate.getLatitude()) {resultMsg.append("the required field latitude is empty; ");} else {resultMsg.append(isValidLatitude(cellTemplate.getLatitude()));}resultMsg.append(isValidName(cellTemplate.getName(), 200));return resultMsg;}public StringBuilder isValidEcgi(CellTemplate cellTemplate) {StringBuilder resultMsg = new StringBuilder();if (null == cellTemplate.getId()) {resultMsg.append("the required field ID is empty; ");} else {if (!validIdLength(cellTemplate.getId(), 12, 13)) {resultMsg.append("the length of ID is not correct; ");} else {if (!isValidEcgiHex(cellTemplate.getId())) {resultMsg.append("the format of ID is not correct; ");}}}if (null == cellTemplate.getLongitude()) {resultMsg.append("the required field longitude is empty; ");} else {resultMsg.append(isValidLongitude(cellTemplate.getLongitude()));}if (null == cellTemplate.getLatitude()) {resultMsg.append("the required field latitude is empty; ");} else {resultMsg.append(isValidLatitude(cellTemplate.getLatitude()));}if (null != cellTemplate.getOwnerTai()) {boolean exists = taiData.stream().anyMatch(fzData -> cellTemplate != null && null != fzData.getId() && cellTemplate.getOwnerTai().equals(fzData.getId()));if (!exists) {resultMsg.append("the ownerTai not exist in TAI sheet; ");}}return resultMsg.append(isValidName(cellTemplate.getName(), 200));}public StringBuilder isValidTai(CellTemplate cellTemplate) {StringBuilder resultMsg = new StringBuilder();if (null == cellTemplate.getId()) {resultMsg.append("the required field ID is empty; ");} else {if (!validIdLength(cellTemplate.getId(), 9, 10)) {resultMsg.append("the length of ID is not correct; ");} else {if (!isValidTaiHex(cellTemplate.getId())) {resultMsg.append("the format of ID is not correct; ");}}}return resultMsg.append(isValidName(cellTemplate.getName(), 200));}private boolean validIdLength(String id, int minLen, int maxLen) {return id != null && id.length() >= minLen && id.length() <= maxLen;}private String isValidName(String name, int maxLength) {if (null != name && name.length() > 200) {return "the name length does not meet the specification; ";}return "";}//纬度private StringBuilder isValidLatitude(String latitude) {StringBuilder sb = new StringBuilder();try {String regex = "^([1-8]?\\d(?:\\.\\d+)?|90)(?:°(\\d{1,2})'([0-5]?\\d)\"([NS]))?$";Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(latitude);if (!matcher.matches()) {return sb.append("the latitude data format error;");}if (null != matcher.group(2)) {int degrees = Integer.parseInt(matcher.group(1));int minutes = Integer.parseInt(matcher.group(2));int seconds = Integer.parseInt(matcher.group(3));String direction = matcher.group(4);double decimalDegrees = degrees + minutes / 60.0 + seconds / 3600.0;if (direction.contains("S")) {decimalDegrees = -decimalDegrees;}return decimalDegrees >= -90.0 && decimalDegrees <= 90.0 ? sb.append("") : sb.append("the latitude data value range error; ");} else {double decimalDegrees = Double.parseDouble(latitude);return decimalDegrees >= -90.0 && decimalDegrees <= 90.0 ? sb.append("") : sb.append("the latitude data value range error; ");}} catch (NumberFormatException e) {log.error("Error parsing latitude", e);return sb.append("the latitude data format error; ");}}//经度private StringBuilder isValidLongitude(String longitude) {StringBuilder sb = new StringBuilder();String regex = "^([1-8]?\\d(?:\\.\\d+)?|180)(?:°(\\d{1,2})'([0-5]?\\d)\"([EW]))?$";Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(longitude);if (!matcher.matches()) {return sb.append("the longitude data format error; ");}if (null != matcher.group(2)) {int degrees = Integer.parseInt(matcher.group(1));int minutes = Integer.parseInt(matcher.group(2));int seconds = Integer.parseInt(matcher.group(3));String direction = matcher.group(4);double decimalDegrees = degrees + minutes / 60.0 + seconds / 3600.0;if (direction.contains("W")) {decimalDegrees = -decimalDegrees;}return decimalDegrees >= -180.0 && decimalDegrees <= 180.0 ? sb.append("") : sb.append("the longitude data value range error; ");} else {double decimalDegrees = Double.parseDouble(longitude);return decimalDegrees >= -180.0 && decimalDegrees <= 180.0 ? sb.append("") : sb.append("the longitude data value range error; ");}}private boolean isValidSaiAndCgiHex(String value) {String decimalPart = value;String hexPart = value;if (value.length() == 13) {decimalPart = value.substring(0, 8);hexPart = value.substring(8).toLowerCase();} else if (value.length() == 14) {decimalPart = value.substring(0, 8);hexPart = value.substring(8, 14).toLowerCase();}Pattern decimalPattern = Pattern.compile("[0-9]+");Pattern hexPattern = Pattern.compile("[0-9a-fA-F]+");return decimalPattern.matcher(decimalPart).matches() && hexPattern.matcher(hexPart).matches();}private boolean isValidEcgiHex(String value) {String decimalPart = value;String hexPart = value;if (value.length() == 13) {decimalPart = value.substring(0, 6);hexPart = value.substring(6).toLowerCase();} else if (value.length() == 12) {decimalPart = value.substring(0, 5);hexPart = value.substring(5).toLowerCase();}Pattern decimalPattern = Pattern.compile("[0-9]+");Pattern hexPattern = Pattern.compile("[0-9a-fA-F]+");return decimalPattern.matcher(decimalPart).matches() && hexPattern.matcher(hexPart).matches();}private boolean isValidTaiHex(String value) {String decimalPart = value;String hexPart = value;if (value.length() == 10) {decimalPart = value.substring(0, 6);hexPart = value.substring(6).toUpperCase();} else if (value.length() == 9) {decimalPart = value.substring(0, 5);hexPart = value.substring(5).toUpperCase();}Pattern decimalPattern = Pattern.compile("[0-9]+");Pattern hexPattern = Pattern.compile("[0-9a-fA-F]+");return decimalPattern.matcher(decimalPart).matches() && hexPattern.matcher(hexPart).matches();}private boolean isValidLaiHex(String data) {String decimalPart = "";String hexPart = "";if (data.length() == 9) {decimalPart = data.substring(0, 5);hexPart = data.substring(5).toUpperCase();} else if (data.length() == 10) {decimalPart = data.substring(0, 6);hexPart = data.substring(6).toUpperCase();}Pattern decimalPattern = Pattern.compile("[0-9]+");Pattern hexPattern = Pattern.compile("[0-9a-fA-F]+");return decimalPattern.matcher(decimalPart).matches() && hexPattern.matcher(hexPart).matches() && !hexPart.equalsIgnoreCase("0000") && !hexPart.equalsIgnoreCase("FFFF");}public void processingErrorMsg(String filePath, List<String> messageInfo, Integer sheetNo) {List<ExcelErrorMessage> errorList = new ArrayList<>();messageInfo.stream().map(message -> message.split("\\[|\\]")).filter(parts -> parts.length == 3).forEach(parts -> {int number = Integer.parseInt(parts[1].trim()) + 1;String message = parts[2].trim().replaceAll(",", "");errorList.add(new ExcelErrorMessage().setRowNum(number).setMessage(message));cellDataListener.updateFlag();});cellDataListener.generateErrorSheet(filePath, sheetNo, errorList);}

实体类：

package com.inspur.softwaregroup.communication.nrms.cmconfig.model.pmc;import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;/*** @Author: pangyq* @CreateTime: 2024-10-12  14:26* @Description: TODO* @Version: 1.0*/
@Data
@Builder
@TableName("IM_PMC_CELL")
@NoArgsConstructor
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class CellTemplate  implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty("ID(M)")private String id;@ExcelProperty("Carrier Name(M)")private  String carrierName;@ExcelProperty("name(O)")private String name;@ExcelProperty("latitude(M)")private String latitude;@ExcelProperty("longitude(M)")private String longitude;@ExcelProperty("owner TAI(O)")private String ownerTai;private String lai;private String tai;private String cgiSai;private String ecgi;private String cellType;
}

esayexecel：

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.read.metadata.holder.ReadWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;/*** @Author: pangyq* @CreateTime: 2024-10-12  09:09* @Description: TODO* @Version: 1.0*/
@Slf4j
public class CellDataListener extends AnalysisEventListener<CellTemplate> {private Map<String, List<CellTemplate>> sheetDataMap = new HashMap<String, List<CellTemplate>>();private List<CellTemplate> currentSheetData = new ArrayList<>();private final static String ERROR_COLUMN_NAME = "Error message";//错误信息标志boolean flag =true;private List<String> sheetNames = new ArrayList<>();private int sheetIndex = 0;@Overridepublic void invoke(CellTemplate data, AnalysisContext context) {currentSheetData.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {String sheetName = context.readSheetHolder().getSheetName();List<CellTemplate> sheetData = new ArrayList<>();sheetData.addAll(currentSheetData);sheetDataMap.put(sheetName, sheetData);currentSheetData.clear();}public  Boolean isVaild(){return this.flag;}public  void initFlag(){this.flag=true;}public  void updateFlag(){this.flag=false;}public Map<String, List<CellTemplate>> getDataList() {sheetDataMap.entrySet().stream().forEach(e -> {if ("CGI".equalsIgnoreCase(e.getKey()) ) {e.getValue().forEach(k -> {k.setCellType("cgi");});} else if ("LAI".equalsIgnoreCase(e.getKey())) {e.getValue().forEach(k -> {k.setCellType("lai");});} else if ("ECGI".equalsIgnoreCase(e.getKey())) {e.getValue().forEach(k -> {k.setCellType("ecgi");});} else if ("TAI".equalsIgnoreCase(e.getKey())) {e.getValue().forEach(k -> {k.setCellType("tai");});}else if ("SAI".contentEquals(e.getKey())) {e.getValue().forEach(k -> {k.setCellType("cgi");});}});return sheetDataMap;}public Boolean generateErrorSheet(String filePath,Integer sheetNo,List<ExcelErrorMessage> errorList) {Map<Integer, String> errorMap = errorList.stream().collect(Collectors.groupingBy(ExcelErrorMessage::getRowNum,Collectors.mapping(ExcelErrorMessage::getMessage, Collectors.joining(";"))));HSSFWorkbook workbook = null;try (FileInputStream inputStream = new FileInputStream(filePath)) {workbook = new HSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(sheetNo);CellStyle style = workbook.createCellStyle();Row headerRow = sheet.getRow(0);short lastCellNum = headerRow.getLastCellNum();Cell lastValidCell = headerRow.getCell(lastCellNum - 1);if (lastValidCell != null) {if (!ERROR_COLUMN_NAME.equals(lastValidCell.getStringCellValue())) {Cell errorHeaderCell = headerRow.createCell(lastCellNum);errorHeaderCell.setCellValue(ERROR_COLUMN_NAME);errorMap.forEach((rowNum, msg) -> {Row row = sheet.getRow(rowNum - 1);if (row != null) {Cell errorCell = row.createCell(lastCellNum);errorCell.setCellValue(msg);errorCell.setCellStyle(style);}});} else {int lastRowNum = sheet.getLastRowNum();for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {Row row = sheet.getRow(rowNum);String setErrorMsg = errorMap.get(rowNum + 1);Cell errorCell = row.getCell(lastCellNum - 1);if (setErrorMsg == null) {style.setFillBackgroundColor(IndexedColors.WHITE.getIndex());style.setFillPattern(FillPatternType.NO_FILL);if (errorCell != null) {errorCell.setBlank();errorCell.setCellStyle(style);}} else {style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());if (errorCell == null) {errorCell = row.createCell(lastCellNum - 1);}errorCell.setCellStyle(style);errorCell.setCellValue(setErrorMsg);this.flag=false;}}}}} catch (IOException e) {log.error("Failed to generate an error message. Procedure,", e);throw new RuntimeException("Failed to generate an error message. Procedure");}try (FileOutputStream outputStream = new FileOutputStream(filePath)) {workbook.write(outputStream);workbook.close();} catch (IOException e) {log.error("Failed to generate an error message. Procedure,", e);throw new RuntimeException("Failed to generate an error message. Procedure");}return true;}public List<String> getSheetNames() {return sheetNames;}public int getSheetIndex() {return sheetIndex;}public void setSheetIndex(int sheetIndex) {this.sheetIndex = sheetIndex;}}

经纬度转换工具类：

import lombok.extern.slf4j.Slf4j;import java.text.DecimalFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;/*** @Author: pangyq* @CreateTime: 2024-10-16  08:38* @Description: TODO* @Version: 1.0*/
@Slf4j
public class LongitudeAndLatitudeUtils {private StringBuilder isValidLatitude(String latitude) {StringBuilder sb = new StringBuilder();try {String regex = "^([1-8]?\\d(?:\\.\\d+)?|90)(?:°(\\d{1,2})'([0-5]?\\d)\"([NS]))?$";Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(latitude);if (!matcher.matches()) {return sb.append("the latitude data format error;");}if (null != matcher.group(2)) {int degrees = Integer.parseInt(matcher.group(1));int minutes = Integer.parseInt(matcher.group(2));int seconds = Integer.parseInt(matcher.group(3));String direction = matcher.group(4);double decimalDegrees = degrees + minutes / 60.0 + seconds / 3600.0;if (direction.contains("S")) {decimalDegrees = -decimalDegrees;}return decimalDegrees >= -90.0 && decimalDegrees <= 90.0 ? sb.append("") : sb.append("the latitude data value range error; ");} else {double decimalDegrees = Double.parseDouble(latitude);return decimalDegrees >= -90.0 && decimalDegrees <= 90.0 ? sb.append("") : sb.append("the latitude data value range error; ");}} catch (NumberFormatException e) {log.error("Error parsing latitude", e);return sb.append("the latitude data format error; ");}}private StringBuilder isValidLongitude(String longitude) {StringBuilder sb = new StringBuilder();String regex = "^([1-8]?\\d(?:\\.\\d+)?|180)(?:°(\\d{1,2})'([0-5]?\\d)\"([EW]))?$";Pattern pattern = Pattern.compile(regex);Matcher matcher = pattern.matcher(longitude);if (!matcher.matches()) {return sb.append("the longitude data format error; ");}if (null != matcher.group(2)) {int degrees = Integer.parseInt(matcher.group(1));int minutes = Integer.parseInt(matcher.group(2));int seconds = Integer.parseInt(matcher.group(3));String direction = matcher.group(4);double decimalDegrees = degrees + minutes / 60.0 + seconds / 3600.0;if (direction.contains("W")) {decimalDegrees = -decimalDegrees;}return decimalDegrees >= -180.0 && decimalDegrees <= 180.0 ? sb.append("") : sb.append("the longitude data value range error; ");} else {double decimalDegrees = Double.parseDouble(longitude);return decimalDegrees >= -180.0 && decimalDegrees <= 180.0 ? sb.append("") : sb.append("the longitude data value range error; ");}}public static double convertLatitudeAndLongitude(String inputCoordinate) {final String DECIMAL_FORMAT = "0.0000000";if (inputCoordinate == null || !inputCoordinate.matches("\\d+°\\d+'\\d+\"[NSWE]")) {throw new IllegalArgumentException("Invalid DMS format: " + inputCoordinate);}try {String[] parts = inputCoordinate.split("[°'\"]");int du = Integer.parseInt(parts[0]);double min = Double.parseDouble(parts[1]);double sec = Double.parseDouble(parts[2]);double decimalDegree = du + (min / 60) + (sec / 3600);char direction = inputCoordinate.charAt(inputCoordinate.length() - 2);if (direction == 'W' || direction == 'S') {decimalDegree *= -1;}DecimalFormat df = new DecimalFormat(DECIMAL_FORMAT);return Double.parseDouble(df.format(decimalDegree));} catch (NumberFormatException | ArrayIndexOutOfBoundsException e) {throw new IllegalArgumentException("Error parsing DMS string: " + inputCoordinate, e);}}public static String convertToDMS(double decimalDegree, boolean isLatitude) {if (Double.isNaN(decimalDegree) || Double.isInfinite(decimalDegree)) {throw new IllegalArgumentException("Invalid input: decimalDegree must be a finite number.");}try {boolean isNegative = decimalDegree < 0;decimalDegree = Math.abs(decimalDegree);int degrees = (int) decimalDegree;double minutes = (decimalDegree - degrees) * 60;int mins = (int) minutes;double seconds = (minutes - mins) * 60;//            DecimalFormat df = new DecimalFormat("0.0");DecimalFormat df = new DecimalFormat("0");String formattedMins = df.format(mins);String formattedSecs = df.format(seconds);String direction;if (isLatitude) {direction = isNegative ? "S" : "N";} else {direction = isNegative ? "W" : "E";}return degrees + "°" + formattedMins + "'" + formattedSecs + "\"" + direction;} catch (Exception e) {throw new RuntimeException("An error occurred while converting to DMS format: " + e.getMessage(), e);}}}

代码可以贴在记事本上，用到哪里去截取一下，一开始只做了某一种类型的导入导出，后来添加了类型，于是，搞了这么一堆，数据校验也挺烦人的，大部分都是校验规则，另外truncate和建立临时表，是某位老师提供的思路，二话不说直接听取，mapper也贴个大概吧：

    <update id="createTempTable">CREATE TABLE ${newTable} AS  SELECT * FROM ${oldTable}</update><update id="truncateTable" parameterType="java.lang.String">TRUNCATE TABLE ${tableName}</update><update id="recoveryContent" parameterType="java.lang.String">INSERT INTO ${oldTable} SELECT * FROM ${tempTable}</update><update id="removeTable">DROP TABLE ${tableName}</update>