大连网站的优化,杭州网站建设推荐q479185700上墙,萨龙 wordpress,重庆网站建站模板公司场景#xff1a;普普通通模板下载#xff0c;加数据导入#xff0c;分全量和增量#xff0c;预计20w数据#xff0c;每一条数据校验#xff0c;前后端代码贴上#xff08;代码有删改#xff0c;关键代码都有#xff0c;好朋友们自己取舍#xff0c;代码一股脑贴上了普普通通模板下载加数据导入分全量和增量预计20w数据每一条数据校验前后端代码贴上代码有删改关键代码都有好朋友们自己取舍代码一股脑贴上了 前端代码示例 div styledisplay: flex; align-items: center; margin-left: autoel-button stylewidth: 160px;height: 25px;font-size: 11px;display: flex;justify-content: end;div styledisplay: flex; align-items: centerspan styleoverflow: hidden;text-overflow: ellipsis;white-space: nowrap;width: 122px;{{ fileName }}/spanspan classel-icon-delete stylemargin-left: 5px clickdeleteFile/span/div/el-buttonel-upload refupload idupload action :on-previewhandlePreview :on-removehandleRemove:before-removebeforeRemove :on-exceedhandleExceed :file-listfileList :limit1:show-file-listfalse :namefileName :on-changehandleChange accept.xls, .xlsx:auto-uploadfalse :http-requestuploadFileel-button iconel-icon-more styleheight: 25px;width: 35px;display: flex;align-items: center;justify-content: center;margin-left: -2px;/el-button/el-uploadel-button styleheight: 25px; margin-left: 5px; padding-top: 7px clickimportData开始导入数据/el-buttondiv clickdownloadTemplate stylefont-size: 13; color: #56b7ecimg src../../../../assets/download.png classicon altDownload Icon /模板下载/divdiv stylemargin-left: 10pxel-input placeholder v-modelsearchParams.called_numberstyleheight: 25px; line-height: 32px; width: 180px keyup.enter.nativesearchData(1)template slotsuffixi classel-icon-search clicksearchData(1) styleline-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 ResponseBodyRequestMapping(value /downloadDataTemplate)ApiOperation(value downloadDataTemplate)public void downloadDataTemplate(HttpServletResponse response, RequestBody MapString, String params) {resourceService.downloadDataTemplate(response, params.getOrDefault(fileName, ), params.getOrDefault(dataType, ));}RequestMapping(value /upload)ResponseBodyApiOperation(value upload)public MapString, Object upload(RequestParam(file) MultipartFile file,RequestParam(dataType) String dataType) {MapString, 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)ResponseBodyApiOperation(value deleteFile)public MapString, Object deleteFile(RequestBody MapString, String param) {MapString, 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)ResponseBodyApiOperation(value vaildData)public MapString, Object vaildData(RequestBody MapString, Object params, HttpServletResponse response) {MapString, 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)ResponseBodyApiOperation(value batchImport)public MapString, Object batchImport(RequestBody MapString, Object params) {MapString, 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)ResponseBodyApiOperation(value exportErorData)public void exportErorData(HttpServletResponse response, RequestBody MapString, 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) ;MapString, Object upload(MultipartFile multipartFile, String dataType);MapString, Object deleteFile(String fileName,String dataType);MapString, Object batchImport(MapString, Object params);MapString, Object vaildData(MapString, Object params,HttpServletResponse response);void exportErrorMsg(HttpServletResponse response,String fileName,String type);
impl部分代码做了屏蔽基本的使用代码都是包含的另外使用了truncate清表大家也可以酌情使用事务控制 private CellDataListener cellDataListener new CellDataListener();private ListExcelErrorMessage errorList new ArrayList();ListCellTemplate saiList new ArrayList();ListCellTemplate laiList new ArrayList();ListCellTemplate taiList new ArrayList();ListCellTemplate laiData new ArrayList();ListCellTemplate taiData new ArrayList();private String carrierId ;MapString, ListCellTemplate validationData new HashMap();ListSpecialNumber 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;filenametemplate.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 MapString, 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);MapString, 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 MapString, 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);MapString, Object result new HashMap();File file new File(filePath);if (file.delete()) {result.put(isSuccess, true);} else {result.put(isSuccess, false);}return result;}Overridepublic MapString, Object batchImport(MapString, Object params) {MapString, Object result new HashMap();MapString, 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 MapString, Object vaildData(MapString, 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 ;MapString, 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;}MapString, ListCellTemplate sheetDataMap readExcelBySheetName(filePath);//校验模板数据生成错误信息MapString, 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 (MapString, ListCellTemplate) 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;}ListSpecialNumber numberList readExcelBySheetNo(filePath);MapString, 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 (ListSpecialNumber) 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 MapString, Object processData(ListSpecialNumber listData, String type, String userGroupId) {MapString, 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 MapString, Object saveActiveCommand(ListSpecialNumber listData, String type) {MapString, Object resultMap new HashMap();for (SpecialNumber specialNumber : listData) {MapString, 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 MapString, Object processCellData(boolean isFullLoad, MapString, ListCellTemplate listData) {MapString, Object resultMap new HashMap();Integer insertLaiTaiCount 0;Integer insertCount 0;//laitailistData.remove(CARRIER_KEY);ListCellTemplate 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);//cellDataListCellTemplate allCellTemplates listData.values().stream().flatMap(List::stream).collect(Collectors.toList());for (ListCellTemplate batch : batch(laiTaiList, BATCH_INSERT_SIZE)) {insertLaiTaiCount ppqMapper.saveLaiTai(batch, carrierId);}for (ListCellTemplate batch : batch(allCellTemplates, BATCH_INSERT_SIZE)) {insertCount ppqMapper.saveCell(batch, carrierId);}resultMap.put(insertCount, insertCount insertLaiTaiCount);resultMap.put(insert, insertCount);return resultMap;}private T ListListT batch(ListT list, int batchSize) {ListListT 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;}MapString, ListCellTemplate 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);}MapString, ListCellTemplate sheetDataMap cellListener.getDataList();log.info(execelData---------:{}, sheetDataMap);return sheetDataMap;}ListSpecialNumber 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);}ListSpecialNumber sheetDataList numberDataListener.getDataList();log.info(resultList:{}, sheetDataList);return sheetDataList;}private MapString, Object validateData(String filePath, ListSpecialNumber list, String dataType) {ListString messageInfo new ArrayList();MapString, 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)) {ListMapString, String queryParams new ArrayList();if (null ! list.get(i).getCarrier() !.equals(list.get(i))) {MapString, String param new HashMap();param.put(key, CARRIER);param.put(value, list.get(i).getCarrier());queryParams.add(param);ListPMCCarrier 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 numbers 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 MapString, Object validateAndProcessData(String filePath, MapString, ListCellTemplate sheetDataMap, Boolean isFullLoad) {ListString messageInfo new ArrayList();MapString, String validationErrors new HashMap();MapString, ListCellTemplate resultMap new HashMap();MapString, Object returnMap new HashMap();ListCellTemplate cgiList new ArrayList();ListCellTemplate ecgiList new ArrayList();AtomicReferenceListMapString, Object cellList new AtomicReference(new ArrayList());ListFuture? futures new ArrayList();ListCellTemplate carrierData sheetDataMap.get(Carrier);ListString laiAndTaiSeen new ArrayList();ListString 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();ListMapString, String queryParams new ArrayList();MapString, String param new HashMap();param.put(key, CARRIER);param.put(value, carrierData.get(0).getCarrierName());queryParams.add(param);ListPMCCarrier 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) {ListMapString, 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());
// ListCellTemplate 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;
// }}// CGIListCellTemplate 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());
// ListCellTemplate 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());
// ListCellTemplate 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;
// }}// SAIListCellTemplate 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());
// ListCellTemplate 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;
// }}// ECGIListCellTemplate 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());
// ListCellTemplate 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, ListString messageInfo, Integer sheetNo) {ListExcelErrorMessage 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 AnalysisEventListenerCellTemplate {private MapString, ListCellTemplate sheetDataMap new HashMapString, ListCellTemplate();private ListCellTemplate currentSheetData new ArrayList();private final static String ERROR_COLUMN_NAME Error message;//错误信息标志boolean flag true;private ListString 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();ListCellTemplate sheetData new ArrayList();sheetData.addAll(currentSheetData);sheetDataMap.put(sheetName, sheetData);currentSheetData.clear();}public Boolean isVaild(){return this.flag;}public void initFlag(){this.flagtrue;}public void updateFlag(){this.flagfalse;}public MapString, ListCellTemplate 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,ListExcelErrorMessage errorList) {MapInteger, 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.flagfalse;}}}}} 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 ListString 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 idcreateTempTableCREATE TABLE ${newTable} AS SELECT * FROM ${oldTable}/updateupdate idtruncateTable parameterTypejava.lang.StringTRUNCATE TABLE ${tableName}/updateupdate idrecoveryContent parameterTypejava.lang.StringINSERT INTO ${oldTable} SELECT * FROM ${tempTable}/updateupdate idremoveTableDROP TABLE ${tableName}/update
文章转载自： http://www.morning.mkhwx.cn.gov.cn.mkhwx.cn http://www.morning.lbbyx.cn.gov.cn.lbbyx.cn http://www.morning.wrlxt.cn.gov.cn.wrlxt.cn http://www.morning.mczjq.cn.gov.cn.mczjq.cn http://www.morning.bpmdq.cn.gov.cn.bpmdq.cn http://www.morning.gltmz.cn.gov.cn.gltmz.cn http://www.morning.ggnrt.cn.gov.cn.ggnrt.cn http://www.morning.tsycr.cn.gov.cn.tsycr.cn http://www.morning.zhengdaotang.cn.gov.cn.zhengdaotang.cn http://www.morning.rqfkh.cn.gov.cn.rqfkh.cn http://www.morning.bksbx.cn.gov.cn.bksbx.cn http://www.morning.zlzpz.cn.gov.cn.zlzpz.cn http://www.morning.rrxnz.cn.gov.cn.rrxnz.cn http://www.morning.wkqrp.cn.gov.cn.wkqrp.cn http://www.morning.kgslc.cn.gov.cn.kgslc.cn http://www.morning.chzqy.cn.gov.cn.chzqy.cn http://www.morning.xhqwm.cn.gov.cn.xhqwm.cn http://www.morning.krhkn.cn.gov.cn.krhkn.cn http://www.morning.jydhl.cn.gov.cn.jydhl.cn http://www.morning.kbynw.cn.gov.cn.kbynw.cn http://www.morning.mkhwx.cn.gov.cn.mkhwx.cn http://www.morning.pngdc.cn.gov.cn.pngdc.cn http://www.morning.rdqzl.cn.gov.cn.rdqzl.cn http://www.morning.kphyl.cn.gov.cn.kphyl.cn http://www.morning.dkgtr.cn.gov.cn.dkgtr.cn http://www.morning.bfycr.cn.gov.cn.bfycr.cn http://www.morning.bpmnh.cn.gov.cn.bpmnh.cn http://www.morning.nktxr.cn.gov.cn.nktxr.cn http://www.morning.ho-use.cn.gov.cn.ho-use.cn http://www.morning.qphcq.cn.gov.cn.qphcq.cn http://www.morning.zqbrw.cn.gov.cn.zqbrw.cn http://www.morning.kwdfn.cn.gov.cn.kwdfn.cn http://www.morning.tqbw.cn.gov.cn.tqbw.cn http://www.morning.cgtfl.cn.gov.cn.cgtfl.cn http://www.morning.cgbgc.cn.gov.cn.cgbgc.cn http://www.morning.ummpdl.cn.gov.cn.ummpdl.cn http://www.morning.ggcjf.cn.gov.cn.ggcjf.cn http://www.morning.hzqjgas.com.gov.cn.hzqjgas.com http://www.morning.jpkhn.cn.gov.cn.jpkhn.cn http://www.morning.xhhzn.cn.gov.cn.xhhzn.cn http://www.morning.pmmrb.cn.gov.cn.pmmrb.cn http://www.morning.qtkfp.cn.gov.cn.qtkfp.cn http://www.morning.zlbjx.cn.gov.cn.zlbjx.cn http://www.morning.dywgl.cn.gov.cn.dywgl.cn http://www.morning.pjzcp.cn.gov.cn.pjzcp.cn http://www.morning.lrflh.cn.gov.cn.lrflh.cn http://www.morning.pkmw.cn.gov.cn.pkmw.cn http://www.morning.fllfz.cn.gov.cn.fllfz.cn http://www.morning.swzpx.cn.gov.cn.swzpx.cn http://www.morning.brhxd.cn.gov.cn.brhxd.cn http://www.morning.nktxr.cn.gov.cn.nktxr.cn http://www.morning.lwyqd.cn.gov.cn.lwyqd.cn http://www.morning.kgkph.cn.gov.cn.kgkph.cn http://www.morning.ysfj.cn.gov.cn.ysfj.cn http://www.morning.xglgm.cn.gov.cn.xglgm.cn http://www.morning.nuobeiergw.cn.gov.cn.nuobeiergw.cn http://www.morning.fhcwm.cn.gov.cn.fhcwm.cn http://www.morning.gbrps.cn.gov.cn.gbrps.cn http://www.morning.rwcw.cn.gov.cn.rwcw.cn http://www.morning.llxyf.cn.gov.cn.llxyf.cn http://www.morning.xdpjs.cn.gov.cn.xdpjs.cn http://www.morning.gtmdq.cn.gov.cn.gtmdq.cn http://www.morning.pxdgy.cn.gov.cn.pxdgy.cn http://www.morning.nzfjm.cn.gov.cn.nzfjm.cn http://www.morning.bpwdc.cn.gov.cn.bpwdc.cn http://www.morning.bntgy.cn.gov.cn.bntgy.cn http://www.morning.dpgdj.cn.gov.cn.dpgdj.cn http://www.morning.rlzxr.cn.gov.cn.rlzxr.cn http://www.morning.qtxwb.cn.gov.cn.qtxwb.cn http://www.morning.ogzjf.cn.gov.cn.ogzjf.cn http://www.morning.zmnyj.cn.gov.cn.zmnyj.cn http://www.morning.ynstj.cn.gov.cn.ynstj.cn http://www.morning.mnclk.cn.gov.cn.mnclk.cn http://www.morning.nbpqx.cn.gov.cn.nbpqx.cn http://www.morning.slpcl.cn.gov.cn.slpcl.cn http://www.morning.ldhbs.cn.gov.cn.ldhbs.cn http://www.morning.bzlfw.cn.gov.cn.bzlfw.cn http://www.morning.kyjpg.cn.gov.cn.kyjpg.cn http://www.morning.frsxt.cn.gov.cn.frsxt.cn http://www.morning.dtnyl.cn.gov.cn.dtnyl.cn
