esayexcel进行模板下载，数据导入，验证不通过，错误信息标注在excel上进行
场景：普平凡通模板下载，加数据导入，分全量和增量，预计20w数据，每一条数据校验，前后端代码贴上（代码有编削，关键代码都有，好朋侪们本身弃取，代码一股脑贴上了）https://i-blog.csdnimg.cn/direct/8275594a1d934f269ea94d9d4567a0f7.png前端代码示例：
<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").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(, {
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(, {
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 = "";
@Override
public 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;
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();
}
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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&tai
listData.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);
//cellData
List<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，其他均为number
Integer 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; ");
}
//校验carrier
if (!"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 requiredfield 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("Thenumber 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();
// Carrier
if (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("" + " The Carrier name does not exist in table; ");
} else {
carrierId = queryCarrier.get(0).getIntId();
}
} else {
messageInfo.add("" + " 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);
}
// LAI
laiData = 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++;
// }
}
// CGI
List<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++;
// }
}
// TAI
taiData = 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++;
// }
}
// SAI
List<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++;
// }
}
// ECGI
List<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 = "^(?\\d(?:\\.\\d+)?|90)(?:°(\\d{1,2})'(?\\d)\"())?$";
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 = "^(?\\d(?:\\.\\d+)?|180)(?:°(\\d{1,2})'(?\\d)\"())?$";
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("+");
Pattern hexPattern = Pattern.compile("+");
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("+");
Pattern hexPattern = Pattern.compile("+");
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("+");
Pattern hexPattern = Pattern.compile("+");
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("+");
Pattern hexPattern = Pattern.compile("+");
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.trim()) + 1;
String message = parts.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-1214:26
* @Description: TODO
* @Version: 1.0
*/
@Data
@Builder
@TableName("IM_PMC_CELL")
@NoArgsConstructor
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class CellTemplateimplements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty("ID(M)")
private String id;
@ExcelProperty("Carrier Name(M)")
privateString 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-1209: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;
@Override
public void invoke(CellTemplate data, AnalysisContext context) {
currentSheetData.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
String sheetName = context.readSheetHolder().getSheetName();
List<CellTemplate> sheetData = new ArrayList<>();
sheetData.addAll(currentSheetData);
sheetDataMap.put(sheetName, sheetData);
currentSheetData.clear();
}
publicBoolean isVaild(){
return this.flag;
}
publicvoid initFlag(){
this.flag=true;
}
publicvoid 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-1608:38
* @Description: TODO
* @Version: 1.0
*/
@Slf4j
public class LongitudeAndLatitudeUtils {
private StringBuilder isValidLatitude(String latitude) {
StringBuilder sb = new StringBuilder();
try {
String regex = "^(?\\d(?:\\.\\d+)?|90)(?:°(\\d{1,2})'(?\\d)\"())?$";
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 = "^(?\\d(?:\\.\\d+)?|180)(?:°(\\d{1,2})'(?\\d)\"())?$";
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+\"")) {
throw new IllegalArgumentException("Invalid DMS format: " + inputCoordinate);
}
try {
String[] parts = inputCoordinate.split("[°'\"]");
int du = Integer.parseInt(parts);
double min = Double.parseDouble(parts);
double sec = Double.parseDouble(parts);
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} ASSELECT * 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>
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]