需要实现的功能
参考exchange项目
备注：2024-05-15修改
1、修复成功页和失败页单独记录序号
2、完善业务层代码，入参修改为实体类集合
3、新增mapper批量插入脚本
在excel中录入文件并导入到数据库
要求
1、文件内容去重，包括文件内容去重和数据库去重
2、实时查看文件导入状态
3、生成文件导入结果文件，便于查错
controller层写法
@Override
public void importFile(MultipartFile file, ChargeListReqVO reqVO) throws Exception {
// 判断是否有进行中的任务
TImpNorealFilterTaskCriteria taskCriteria = new TImpNorealFilterTaskCriteria();
taskCriteria.createCriteria()
.andStatusEqualTo(FILE_IMPORT_STATUS.ZERO.getCode());
long count = filterTaskMapper.countByExample(taskCriteria);
if (count > 0) {
throw new ValidateException(CoreConstant.TRANS_STATUS__FAILED, "存在导入中的文件");
}
// 基本信息校验
if (null == file) {
throw new ValidateException(CoreConstant.TRANS_STATUS__FAILED, "请选择文件后再执行导入！");
}
String originalFilename = file.getOriginalFilename();
String extname = originalFilename.substring(originalFilename.lastIndexOf("."));
if (!".xlsx".equalsIgnoreCase(extname)) {
throw new ValidateException(CoreConstant.TRANS_STATUS__FAILED, "仅支持.xlsx格式文件导入，请重新选择文件后再执行导入！");
}
String organCode = reqVO.getOrganCode();
if (StringUtils.isBlank(organCode)) {
throw new ValidateException(CoreConstant.TRANS_STATUS__FAILED, "机构编码不能为空");
}
// 公共变量定义,即主键id生成策略
String impBatchNo = commonMapper.generatePK().toString();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd/");
String formatPath = dateFormat.format(new Date());
String loginName = reqVO.getHead().getLoginName();
// 应该从yaml配置文件中读取
// String originalFilePath="/data/share/static/exchange/charge/";
// 原始文件放置
String filePath = originalFilePath + formatPath;
String fileName = file.getOriginalFilename();
int lastIndexOfDot = originalFilename.lastIndexOf('.');
if (lastIndexOfDot == -1) {
fileName = fileName; // 没有后缀
}
fileName = originalFilename.substring(0, lastIndexOfDot);
fileName = fileName + "_" + commonMapper.generatePK();
String fileFinalPath = filePath + fileName;
Path path = Paths.get(fileFinalPath + extname);
File fileTmp = path.toFile();
File parentDir = fileTmp.getParentFile();
if (!parentDir.exists()) {
parentDir.mkdirs();
}
if (!fileTmp.exists()) {
try {
fileTmp.createNewFile();
} catch (IOException var) {
log.error("结果文件创建失败：{}", var.getMessage());
}
}
try {
file.transferTo(fileTmp);
} catch (Exception e) {
log.error("原始文件放置失败：{}", e.getMessage());
}
TImpNorealFilterTask filterTask = new TImpNorealFilterTask();
filterTask.setImpBatchNo(impBatchNo);
filterTask.setUserName(loginName);
String pathTmp = fileFinalPath + extname;
filterTask.setSourceFile(pathTmp.replace("/data/share/",""));
Date insertTime = new Date();
filterTask.setInsertTime(insertTime);
filterTask.setStartTime(insertTime);
filterTask.setInsertBy(loginName);
filterTask.setOrganCode(organCode);
filterTask.setStatus(FILE_IMPORT_STATUS.ZERO.getCode());
log.info("数据插入数据库");
// 异步导入文件
asyncChargeService.asyncImportFile(file, impBatchNo, loginName, fileFinalPath);
}
业务层写法
/**
* @author wangtuanyuan
* @date: 2023/12/11 011 16:39
* @description:
*/
@Service
@Async
@Slf4j
@RequiredArgsConstructor
public class AsyncChargeServiceImpl implements AsyncChargeService {
/**
* @author wangtuanyuan
* @date 2023/12/11 16:40
* @description
**/
public void asyncImportFile(InputStream inputStream, Long batchId) throws IOException {
List<CustDetailDTO> list = new ArrayList<>();
EasyExcel.read(inputStream, CustDetailDTO.class, new AnalysisEventListener<CustDetailDTO>() {
@SneakyThrows
@Override
public void invoke(CustDetailDTO data, AnalysisContext analysisContext) {
// 读取时,会读取到一个实体对象,但是所有属性都为空,需要排除这个对象
boolean fieldNull = isAllFieldNull(data);
if (!fieldNull) {
list.add(data);
}
}
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
batchImportSave(list, batchId);
}
}).sheet(0).doRead();
}
private void batchImportSave(List<CustDetailDTO> dtoList, Long batchId) {
// 成功结果数据
List<SuccStatusDTO> successList = new ArrayList<>();
// 失败结果数据
List<FileStatusDTO> failList = new ArrayList<>();
// 文件校验后需要入库的数据
List<CustDetailDTO> dbBatchList = new ArrayList<>();
// excel文件去重处理，利用map进行去重
Map<String, String> map = new HashMap<>();
// 总数据量
int total = dtoList.size();
// 本次导入的批次号
final String batchNo = commonMapper.generatePK().toString();
for (int i = 0; i < total; i++) {
// 结果文件中的当前行数
int num = i + 1;
// 需要入库的数据
CustDetailDTO custDetailDTO = new CustDetailDTO();
// 行号,表示当前数据是第几行
int serialNumber = i + 2;
CustDetailDTO detailDTO = dtoList.get(i);
final String custId = detailDTO.getCustId();
if (StringUtils.isBlank(custId)) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("客户号不应为空");
failList.add(statusDTO);
continue;
} else {
if (custId.length() > 40) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("该客户号超过40位");
failList.add(statusDTO);
continue;
} else {
// 查询数据库是否存在
final AppCrsBsDetailCriteria criteria = new AppCrsBsDetailCriteria();
criteria.createCriteria()
.andCustIdEqualTo(custId);
final Long count = appCrsBsDetailMapper.countByExample(criteria);
if (null == count || 0 == count) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("该客户号不存在");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setCustId(custId);
}
}
}
if (!map.isEmpty()) {
String sDate = map.get(custId);
if (StringUtils.isNotBlank(sDate)) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("该客户号本次已导入");
failList.add(statusDTO);
continue;
} else {
// 存入公共Map,进行文件去重
map.put(custId, custId);
}
} else {
// 存入公共Map,进行文件去重
map.put(custId, custId);
}
// 判断其他项都为空
final String selfcertification = detailDTO.getSelfcertification();
final String addressfreeen = detailDTO.getAddressfreeen();
final String rescountrycode = detailDTO.getRescountrycode();
final String tin = detailDTO.getTin();
final String explanation = detailDTO.getExplanation();
if (StringUtils.isBlank(selfcertification)
&& StringUtils.isBlank(addressfreeen)
&& StringUtils.isBlank(rescountrycode)
&& StringUtils.isBlank(tin)
&& StringUtils.isBlank(explanation)) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("除客户号，其他信息项至少录入一项");
failList.add(statusDTO);
continue;
}
if (StringUtils.isNotBlank(selfcertification)) {
if (!"TRUE,FALSE".contains(selfcertification)) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("是否取得自证声明应为“TRUE”或“FALSE”");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setSelfcertification(selfcertification);
}
}
if (StringUtils.isNotBlank(addressfreeen)) {
if (addressfreeen.length() > 100) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("客户地址_英文长度不应超100字符");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setAddressfreeen(addressfreeen);
}
}
if (StringUtils.isNotBlank(rescountrycode)) {
if (rescountrycode.length() > 100) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("税收居民国长度不应超100字符");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setRescountrycode(rescountrycode);
}
}
if (StringUtils.isNotBlank(tin)) {
if (tin.length() > 100) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("税号长度不应超100字符");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setTin(tin);
}
}
if (StringUtils.isNotBlank(explanation)) {
if (explanation.length() > 100) {
FileStatusDTO statusDTO = new FileStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId)
.setMsg("未填写税号的原因长度不应超100字符");
failList.add(statusDTO);
continue;
} else {
custDetailDTO.setExplanation(explanation);
}
}
// 成功结果文件记录
SuccStatusDTO statusDTO = new SuccStatusDTO();
statusDTO.setSerialNumber(serialNumber)
.setNum(num)
.setCustId(custId);
successList.add(statusDTO);
// 需要入库的数据
dbBatchList.add(custDetailDTO);
}
// 清空map GC
map = null;
// 批次切片,把大的集合切片成小的集合
List<List<CustDetailDTO>> splitNList = SplitListUtils.split(dbBatchList, 50);
final Date date = new Date();
for (List<CustDetailDTO> split : splitNList) {
List<TImpCrsCustDetail> detailList = new ArrayList<>();
for (CustDetailDTO fileStatus : split) {
TImpCrsCustDetail custDetail = new TImpCrsCustDetail();
custDetail.setBatchNo(batchNo);
custDetail.setCustId(fileStatus.getCustId());
custDetail.setSelfcertification(fileStatus.getSelfcertification());
custDetail.setAddressfreeen(fileStatus.getAddressfreeen());
custDetail.setRescountrycode(fileStatus.getRescountrycode());
custDetail.setTin(fileStatus.getTin());
custDetail.setExplanation(fileStatus.getExplanation());
detailList.add(custDetail);
}
extImpCrsCustDetailMapper.insertBatch(detailList, date);
}
// 结果文件放到服务器
String resultFile = "/data/share/static/dm-web/crs/result/" + batchNo + "_result.xlsx";
ExcelWriter excelWriter = null;
try {
File dest = new File(resultFile);
if (!dest.getParentFile().exists()) {
dest.getParentFile().mkdirs();
}
// 结果文件，包含成功和失败
excelWriter = EasyExcel.write(resultFile).build();
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
WriteSheet successSheet = EasyExcel.writerSheet(1, "成功页").head(SuccStatusDTO.class).build();
WriteSheet failSheet = EasyExcel.writerSheet(2, "失败页").head(FileStatusDTO.class).build();
// 遍历正确集合，重新修改序号
for (int i = 0; i < successList.size(); i++) {
final SuccStatusDTO statusDTO = successList.get(i);
statusDTO.setNum(i + 1);
}
// 遍历错误集合，重新修改序号
for (int i = 0; i < failList.size(); i++) {
final FileStatusDTO fileStatusDTO = failList.get(i);
fileStatusDTO.setNum(i + 1);
}
// 正确数据和错误数据进行归类
excelWriter.write(successList, successSheet);
excelWriter.write(failList, failSheet);
} catch (Exception e) {
log.error("结果文件写出失败:{}", e.getMessage(), e);
} finally {
excelWriter.finish();
int failSize = failList.size();
Integer successNum = total - failSize;
String impState = CRS_FILE_IMPORT.STATE_ONE.getCode();
if ((successNum == total) && (failSize == 0)) {
// 全部导入成功
impState = CRS_FILE_IMPORT.STATE_ONE.getCode();
} else if ((failSize > 0) && (successNum > 0)) {
// 部分导入成功
impState = CRS_FILE_IMPORT.STATE_THREE.getCode();
} else if ((failSize == total) && (successNum == 0)) {
// 全部导入失败
impState = CRS_FILE_IMPORT.STATE_TWO.getCode();
}
Date now = date;
TImpCrsCustTask custTask = new TImpCrsCustTask();
custTask.setBatchId(new BigDecimal(batchId));
custTask.setState(impState);
custTask.setImpTotal(new BigDecimal(total));
custTask.setImpSuccess(new BigDecimal(successNum));
custTask.setResultPath(resultFile.split("/data/share/")[1]);
custTask.setEndTime(now);
custTask.setUpdateTime(now);
crsCustTaskMapper.updateByPrimaryKeySelective(custTask);
}
}
/**
* 判断该对象是否所有属性为空
* 返回ture表示所有属性为null，返回false表示不是所有属性都是null
*/
public static boolean isAllFieldNull(Object object) {
boolean flag = true;
Class<?> clazz = object.getClass();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
//设置属性是可以访问的(私有的也可以)
field.setAccessible(true);
Object value = null;
try {
value = field.get(object);
// 只要有1个属性不为空,那么就不是所有的属性值都为空
if (value != null) {
flag = false;
break;
}
} catch (Exception e) {
log.error("文件导入存在空对象异常:{}", e.getMessage());
}
}
return flag;
}
}
// ===============================================使用到的类===================================================================
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
class SuccStatusDTO {
@ExcelProperty("序号")
@ColumnWidth(value = 20)
private Integer num;
/**
* 本条数据在excel原始文件中的行数
*/
@ExcelProperty("行号")
@ColumnWidth(value = 20)
private Integer serialNumber;
/**
* 客户号
*/
@ExcelProperty("客户号")
@ColumnWidth(value = 20)
private String custId;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class FileStatusDTO {
@ExcelProperty("序号")
@ColumnWidth(value = 20)
private Integer num;
/**
* 本条数据在excel原始文件中的行数
*/
@ExcelProperty("行号")
@ColumnWidth(value = 20)
private Integer serialNumber;
/**
* 客户号
*/
@ExcelProperty("客户号")
@ColumnWidth(value = 20)
private String custId;
/**
* 错误原因
*/
@ExcelProperty("错误原因")
@ColumnWidth(value = 40)
private String msg;
}
class SplitListUtils {
/**
* 拆分集合
*
* @param <T> 泛型对象
* @param resList 需要拆分的集合
* @param subListLength 每个子集合的元素个数
* @return 返回拆分后的各个集合组成的列表
* 代码里面用到了guava和common的结合工具类
**/
public static <T> List<List<T>> split(List<T> resList, int subListLength) {
if (CollectionUtils.isEmpty(resList) || subListLength <= 0) {
return Lists.newArrayList();
}
List<List<T>> ret = Lists.newArrayList();
int size = resList.size();
if (size <= subListLength) {
// 数据量不足 subListLength 指定的大小
ret.add(resList);
} else {
int pre = size / subListLength;
int last = size % subListLength;
// 前面pre个集合，每个大小都是 subListLength 个元素
for (int i = 0; i < pre; i++) {
List<T> itemList = Lists.newArrayList();
for (int j = 0; j < subListLength; j++) {
itemList.add(resList.get(i * subListLength + j));
}
ret.add(itemList);
}
// last的进行处理
if (last > 0) {
List<T> itemList = Lists.newArrayList();
for (int i = 0; i < last; i++) {
itemList.add(resList.get(pre * subListLength + i));
}
ret.add(itemList);
}
}
return ret;
}
}
补充
excel批量导入的另外一种写法，
@Async
public void importFileAsync(MultipartFile file, PrivacyBatchImportReqVO reqVO, Long batchId) throws IOException {
// 每隔3000条存储数据库,然后清理list,方便内存回收
final int BATCH_COUNT = 3000;
List<PrivacyBatchImportDTO> list = new ArrayList<>();
EasyExcel.read(file.getInputStream(), PrivacyBatchImportDTO.class, new AnalysisEventListener<PrivacyBatchImportDTO>() {
@SneakyThrows
@Override
public void invoke(PrivacyBatchImportDTO data, AnalysisContext analysisContext) {
// 读取时,会读取到一个实体对象,但是所有属性都为空,需要排除这个对象
boolean fieldNull = isAllFieldNull(data);
if (!fieldNull) {
list.add(data);
if (list.size() >= BATCH_COUNT) {
batchImportSave(list, reqVO, batchId);
list.clear();
}
}
}
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
batchImportSave(list, reqVO, batchId);
}
}).sheet(0).doRead();
}
批量插入数据写法
<insert id="insertBatch">
INSERT INTO CIRC_OTH.T_IMP_CRS_CUST_DETAIL (BATCH_NO,CUST_ID,SELFCERTIFICATION,ADDRESSFREEEN,RESCOUNTRYCODE,TIN,EXPLANATION,LOADTIME,IMPDATE)
<foreach collection="detailList" item="item" separator="union all" >
select
#{item.batchNo,jdbcType=VARCHAR},
#{item.custId,jdbcType=VARCHAR},
#{item.selfcertification,jdbcType=CHAR},
#{item.addressfreeen,jdbcType=VARCHAR},
#{item.rescountrycode,jdbcType=VARCHAR},
#{item.tin,jdbcType=VARCHAR},
#{item.explanation,jdbcType=VARCHAR},
#{date,jdbcType=TIMESTAMP},
#{date,jdbcType=TIMESTAMP}
from dual
</foreach>
</insert>
最终效果展示
需要导入的文件
文件导入的结果
805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
