package com.tslc.car.fileDataUpload.service.impl;
import com.tslc.car.common.core.entity.CommonCurrentUserEntity;
import com.tslc.car.fileDataUpload.constant.ImportConstant;
import com.tslc.car.fileDataUpload.entity.ImportSheetMapping;
import com.tslc.car.fileDataUpload.repository.ImportSheetMappingMapper;
import com.tslc.car.fileDataUpload.repository.ImportTemplateMapper;
import com.tslc.car.fileDataUpload.service.IUploadFileDataService;
import com.tslc.car.fileDataUpload.service.ImportTemplateFailRecordService;
import com.tslc.car.fileDataUpload.service.ImportTemplateRecordService;
import com.tslc.car.fileDataUpload.service.ImportTemplateService;
import com.tslc.car.fileDataUpload.vo.*;
import com.tslc.common.entity.ResultMessage;
import com.tslc.common.util.DataUtil;
import com.tslc.common.util.IDAssignUtil;
import com.tslc.core.base.constants.StatusEnum;
import com.tslc.core.base.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
/**
* @BelongsProject: project-h
* @BelongsPackage: com.tslc.car.controldimension.service.impl
* @Author: LC20677
* @CreateTime: 2025-09-9 13:23
* @Description: 通用模板导入
* @Version: 1.0
*/
@Service
@Slf4j
public class UploadFileDataServiceImpl implements IUploadFileDataService {
@Autowired
private ImportTemplateMapper importTemplateMapper;
@Autowired
private ImportSheetMappingMapper importSheetMappingMapper;
@Autowired
private ImportTemplateRecordService importTemplateRecordService;
@Autowired
private ImportTemplateFailRecordService importTemplateFailRecordService;
// 线程池
private ExecutorService executor = Executors.newFixedThreadPool(10);
/*
* @MethodName importData
* @Description 通用导入模板
* @Param [employeeCode]
* @Return
* @USER LC20677
* @Date 2025/8/27
**/
@Override
@Transactional(rollbackFor = ServiceException.class)
public ResultMessage uploadFileData(UploadDataVo uploadDataVo, MultipartFile file) throws ServiceException {
try {
ResultMessage resultMessage = ResultMessage.success("导入成功");
if (null == file) {
resultMessage = ResultMessage.failed("数据插入失败,文件流为NULL。");
// TODO 插入模板导入日志
// TODO 增加失败日志
// 插入模板导入日志
insertImportTemplateLog(uploadDataVo.getTemplateCode(),uploadDataVo.getTemplateCode(), 0, 0, 0, "2", "文件流为NULL");
return resultMessage;
}
String fileName = file.getOriginalFilename();
// 获取传入文件的格式
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
List<ImportTemplateVo> templateList = importTemplateMapper.queryImportTemplate(uploadDataVo.getTemplateCode());
if (null != templateList && templateList.size() > 0) {
for (ImportTemplateVo importTemplateVo : templateList) {
if (ImportConstant.FILE_TYPE_EXCEL.equalsIgnoreCase(importTemplateVo.getFileType())) {
if (!ImportConstant.FILE_TYPE_XLSX.equalsIgnoreCase(fileType) && ImportConstant.FILE_TYPE_XLS.equalsIgnoreCase(fileType)) {
resultMessage = ResultMessage.failed("数据插入失败,传入文件格式跟模板配置所需格式不一致。");
// TODO 插入模板导入日志
// TODO 增加失败日志
// 插入模板导入日志
insertImportTemplateLog(uploadDataVo.getTemplateCode(),uploadDataVo.getTemplateCode(), 0, 0, 0, "2", "文件格式与模板配置不一致");
return resultMessage;
}
} else {
if (!ImportConstant.FILE_TYPE_CSV.equalsIgnoreCase(fileType)) {
resultMessage = ResultMessage.failed("数据插入失败,传入文件格式跟模板配置所需格式不一致。");
// TODO 插入模板导入日志
// TODO 增加失败日志
// 插入模板导入日志
insertImportTemplateLog(uploadDataVo.getTemplateCode(),uploadDataVo.getTemplateCode(), 0, 0, 0, "2", "文件格式与模板配置不一致");
return resultMessage;
}
}
}
} else {
resultMessage = ResultMessage.failed("数据插入失败,请确认模板配置sheet是否正确。");
// TODO 插入模板导入日志
// TODO 增加失败日志
// 插入模板导入日志
insertImportTemplateLog(uploadDataVo.getTemplateCode(),uploadDataVo.getTemplateCode(), 0, 0, 0, "2", "模板配置sheet不正确");
return resultMessage;
}
XSSFWorkbook wb = null;
if (ImportConstant.FILE_TYPE_CSV.equalsIgnoreCase(fileType)) {
// CSV TODO
wb = new XSSFWorkbook();
this.writeDataToWorkbook(wb, this.readCsv(file.getInputStream()));
} else {
wb = new XSSFWorkbook(file.getInputStream());
}
// 根据配置处理文件导入的数据
for (ImportTemplateVo template : templateList) {
// 线程异步处理
this.fileImportHandler(uploadDataVo, wb, template, fileType);
}
return resultMessage;
} catch (Exception e) {
log.error(this.getClass().getName() + ":", e);
throw new ServiceException(e.getMessage(), e);
}
}
/**
* 文件数据上传相关处理(异步处理)
*
* @param uploadDataVo
* @param wb
* @param template
*/
private void fileImportHandler(UploadDataVo uploadDataVo, XSSFWorkbook wb, ImportTemplateVo template, String fileType) {
executor.submit(new Runnable() {
@Override
public void run() {
String templateRecordId = insertImportTemplateLog(
template.getTemplateName(), template.getTemplateCode(),// 传入实际的模板名称
0, 0, 0, "0", "导入进行中"
);
XSSFSheet sheet = null;
if (ImportConstant.FILE_TYPE_CSV.equalsIgnoreCase(fileType)) {
// csv 只能读取第一页sheet
sheet = wb.getSheetAt(0);
} else {
// excel根据sheet名获取数据
sheet = wb.getSheet(template.getSheetName());
}
Integer totalCount = 0;
Integer successCount = 0;
Integer failCount = 0;
for (int i = template.getStartRow(); i <= sheet.getLastRowNum(); i++) {
totalCount = totalCount + 1;
XSSFRow eachRow = sheet.getRow(i);
List<ImportSheetMapping> sheetMappingList = importSheetMappingMapper.queryImportSheetMapping(template.getTemplateSheetId());
ImportDataVo importDataVo = new ImportDataVo();
importDataVo.setTableName(template.getTableName());
if (null != sheetMappingList && sheetMappingList.size() > 0) {
List<CloumData> cloumDataList = new ArrayList<>();
try {
// 获取每列中的值
for (ImportSheetMapping sheetMapping : sheetMappingList) {
CloumData cloumData = new CloumData();
cloumData.setCloumName(sheetMapping.getFieldName());
if (ImportConstant.FILE_TYPE_CSV.equalsIgnoreCase(fileType)) {
this.csvGetCellValue(eachRow, sheetMapping, cloumData);
} else {
this.excelGetCellValue(eachRow, sheetMapping, cloumData);
}
cloumDataList.add(cloumData);
}
if (null != cloumDataList && cloumDataList.size() > 0) {
cloumDataList.addAll(setCreateCommonData());
importDataVo.setCloumData(cloumDataList);
// 插入失败catch。后面继续
try {
importTemplateMapper.importDate(importDataVo);
successCount = successCount + 1;
} catch (Exception e) {
failCount = failCount + 1;
log.error(e.getMessage(), e);
// TODO 插入失败日志
// 插入失败日志
insertImportFailLog(templateRecordId, i, e.getMessage());
}
}
} catch (Exception e) {
// 插入失败catch。后面继续
failCount = failCount + 1;
log.error(e.getMessage(), e);
// TODO 插入失败日志
// 插入失败日志
insertImportFailLog(templateRecordId, i, e.getMessage());
}
}
}
// TODO 插入模板导入日志
// 插入模板导入日志
updateImportTemplateLog(template.getTemplateName(),templateRecordId,template.getTemplateCode(), totalCount, successCount, failCount, getImportStatus(successCount, failCount), "导入完成");
}
/**
* excel设置cellValue值
* @param eachRow
* @param sheetMapping
* @param cloumData
*/
private void excelGetCellValue(XSSFRow eachRow, ImportSheetMapping sheetMapping, CloumData cloumData) {
if(DataUtil.intUtil(sheetMapping.getUseDefault()) == 1){
cloumData.setCloumValue(sheetMapping.getDefaultValue());
}else{
if (ImportConstant.CLOUM_TYPE_STRING.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(eachRow.getCell(sheetMapping.getCloumIndex()).getStringCellValue());
} else if (ImportConstant.CLOUM_TYPE_DOUBLE.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(eachRow.getCell(sheetMapping.getCloumIndex()).getNumericCellValue());
} else if (ImportConstant.CLOUM_TYPE_DATE.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(eachRow.getCell(sheetMapping.getCloumIndex()).getDateCellValue());
} else {
cloumData.setCloumValue(eachRow.getCell(sheetMapping.getCloumIndex()).getStringCellValue());
}
}
}
/**
* csv设置cellValue值
* @param eachRow
* @param sheetMapping
* @param cloumData
*/
private void csvGetCellValue(XSSFRow eachRow, ImportSheetMapping sheetMapping, CloumData cloumData) {
String cellValue = eachRow.getCell(sheetMapping.getCloumIndex()).getStringCellValue();
if(sheetMapping.getUseDefault() == 1){
cloumData.setCloumValue(sheetMapping.getDefaultValue());
}else{
if (ImportConstant.CLOUM_TYPE_STRING.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(cellValue);
} else if (ImportConstant.CLOUM_TYPE_DOUBLE.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(DataUtil.doubleUtil(cellValue));
} else if (ImportConstant.CLOUM_TYPE_DATE.equals(sheetMapping.getFieldType())) {
cloumData.setCloumValue(cellValue);
} else {
cloumData.setCloumValue(cellValue);
}
}
}
});
}
/**
* 设置基础固定栏位
*
* @return
*/
private List<CloumData> setCreateCommonData() {
List<CloumData> cloumDataList = new ArrayList<>();
CommonCurrentUserEntity entity = new CommonCurrentUserEntity();
CloumData bid = new CloumData();
bid.setCloumName(ImportConstant.CLOUM_NAME_BID);
bid.setCloumValue(IDAssignUtil.uuid());
cloumDataList.add(bid);
CloumData delete = new CloumData();
CloumData creator = new CloumData();
creator.setCloumName(ImportConstant.CLOUM_NAME_CREATOR);
creator.setCloumValue(entity.getCurrentUserCode());
cloumDataList.add(creator);
CloumData createDate = new CloumData();
createDate.setCloumName(ImportConstant.CLOUM_NAME_CREATE_DATE);
createDate.setCloumValue(new Date());
cloumDataList.add(createDate);
delete.setCloumName(ImportConstant.CLOUM_NAME_IS_DELETE);
delete.setCloumValue(StatusEnum.RUNING.getValue());
cloumDataList.add(delete);
CloumData version = new CloumData();
version.setCloumName(ImportConstant.CLOUM_NAME_VERSION);
version.setCloumValue(1);
cloumDataList.add(version);
return cloumDataList;
}
/**
* 读取csv数据流
*
* @param inputStream
* @return
* @throws IOException
*/
public String[][] readCsv(InputStream inputStream) throws IOException {
BufferedReader br = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"));
String line;
StringBuilder sb = new StringBuilder();
while ((line = br.readLine()) != null) {
sb.append(line).append("\n");
}
br.close();
// 按行分割CSV数据
String[] rows = sb.toString().split("\n");
String[][] data = new String[rows.length][];
for (int i = 0; i < rows.length; i++) {
data[i] = rows[i].split(","); // 按逗号分割列
}
return data; // 返回CSV数据
}
/**
* 写入数据到workbook
*
* @param workbook
* @param csvData
*/
public void writeDataToWorkbook(XSSFWorkbook workbook, String[][] csvData) {
Sheet sheet = workbook.createSheet("Sheet1"); // 创建新的工作表
for (int i = 0; i < csvData.length; i++) {
Row row = sheet.createRow(i); // 创建行
for (int j = 0; j < csvData[i].length; j++) {
row.createCell(j).setCellValue(csvData[i][j]); // 写入每个单元格数据
}
}
}
/**
* 保存到excel文件 暂时不需要用
*
* @param workbook
* @param filePath
* @throws IOException
*/
public void saveWorkbook(XSSFWorkbook workbook, String filePath) throws IOException {
FileOutputStream outputStream = new FileOutputStream(filePath);
workbook.write(outputStream); // 写入数据
outputStream.close();
workbook.close(); // 关闭 Workbook
}
/**
* 插入导入模板日志
*/
/**
* 插入导入模板日志并返回导入记录ID
*/
private String insertImportTemplateLog(String tenokateName,String templateCode, int totalCount, int successCount, int failCount, String status, String remark) {
try {
ImportTemplateRecordVo templateVo = new ImportTemplateRecordVo();
templateVo.setTemplateCode(templateCode);
templateVo.setTemplateName(tenokateName);
templateVo.setTotalCount(String.valueOf(totalCount));
templateVo.setSuccessCount(successCount);
templateVo.setFailCount(failCount);
templateVo.setImportStatus(status);
importTemplateRecordService.saveOrUpdate(templateVo);
return templateVo.getBid(); // 返回生成的ID
} catch (Exception e) {
log.error("插入导入模板日志失败: ", e);
return null;
}
}
/**
* 更新导入模板日志
*/
private void updateImportTemplateLog(String tenokateName,String bid,String templateCode, int totalCount, int successCount, int failCount, String status, String remark) {
try {
ImportTemplateRecordVo templateVo = new ImportTemplateRecordVo();
templateVo.setBid(bid);
templateVo.setTemplateCode(templateCode);
templateVo.setTemplateName(tenokateName);
templateVo.setTotalCount(String.valueOf(totalCount));
templateVo.setSuccessCount(successCount);
templateVo.setFailCount(failCount);
templateVo.setImportStatus(status);
importTemplateRecordService.saveOrUpdate(templateVo);
} catch (Exception e) {
log.error("插入导入模板日志失败: ", e);
}
}
/**
* 插入导入失败日志
*/
private void insertImportFailLog(String templateRecordId, int rowIndex, String errorMsg) {
try {
ImportTemplateFailRecordVo failRecordVo = new ImportTemplateFailRecordVo();
failRecordVo.setTemplateRecordId(templateRecordId);
failRecordVo.setFailIndex(rowIndex);
failRecordVo.setRemark(errorMsg != null && errorMsg.length() > 500 ? errorMsg.substring(0, 500) : errorMsg);
importTemplateFailRecordService.saveImportTemplateFailRecord(failRecordVo);
} catch (Exception e) {
log.error("插入导入失败日志失败: ", e);
}
}
/**
* 根据成功数和失败数确定导入状态
*/
private String getImportStatus(int successCount, int failCount) {
if (failCount == 0) {
return ImportConstant.ALL_SUCCESS; // 全部成功
} else if (successCount == 0) {
return ImportConstant.ALL_FAIL; // 全部失败
} else {
return ImportConstant.PART_SUCCESS; // 部分成功
}
}
}
我重申一下我的需求,这个东西是一个公共的导入接口,现在需要改造的点是需要增加数据方面的校验,也就是需要查询出来数据之后不要直接存入数据库,而是要放在map集合里面,然后调用一下,调用方提供的校验接口,这个的难点就在于不同的调用方提供不同的校验方法在不同的serviceimpl,并且一个模板不止一个校验方法,所以重点就在于在如何去动态的调用校验方法