背景:导入功能
图示:
excel模板:
返回数据:
功能简述:
根据文件地址,获取文件,解析校验表单(标题行,数据行各种校验),存入数据,返回成功条数,失败条数,失败原因(错误位置,错误原因)
本文重点(表单的校验)
1.标题行
1.1 非空,字符串校验
1.2 名称校验(如;票据号码)
1.3 顺序校验(防止客户误操作移动列,导致数据插入进db的非对应字段)
2. 数据行各种校验
2.1 非空,数据格式校验(字符串,数字,日期等)
2.2 长度校验(恒等的长度,以及最大的长度)
2.3 数据合理性校验(金额范围,出票日期<=当前日期<到票日期)
3. 代码链
package com.business.service.draft;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.business.domain.draft.AcceptanceBill;
import com.business.mapper.draft.AcceptanceBillMapper;
import com.business.param.draft.request.AcceptanceBillListVO;
import com.business.param.draft.request.AcceptanceBillVO;
import com.business.param.draft.request.AcceptanceImportVO;
import com.business.param.draft.response.AcceptanceImportResponseItem;
import com.business.param.draft.response.AcceptanceResponseItem;
import com.general.dto.PageResponse;
import com.general.dto.Result;
import com.general.util.OrderByCheckUtil;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @Description 承兑汇票Service
* @Author Luffy
* @Date 2020-09-10 10:36
*/
@Service
@Slf4j
public class AcceptanceService {
@Autowired
private AcceptanceBillMapper acceptanceBillMapper;
/**
* @Description
* excel中的字段: 汇票号码(30位数字) 出票单位 汇票金额 出票银行 出票日期 到票日期
* 此功能不添加事务,允许有不可导入的数据出现(如,票号重复)
* @Author Luffy
* @Date 2020-09-10 14:47
*/
public Result<AcceptanceImportResponseItem> importAcceptDraft(AcceptanceImportVO requestVO) {
AcceptanceImportResponseItem resp = new AcceptanceImportResponseItem();
//1.获取文件
File file = new File(requestVO.getAccessUrl());
if(!file.exists()){
return Result.buildFail("获取文件失败,未在对应地址匹配到文件!");
}
FileInputStream fis = null;
Workbook workBook = null;
try {
fis = new FileInputStream(file);
//判断文件类型
if (requestVO.getAccessUrl().toLowerCase().endsWith(".xls")) {
// 使用 HSSFWorkbook 解析xls
workBook = new HSSFWorkbook(fis, true);
} else if (requestVO.getAccessUrl().toLowerCase().endsWith(".xlsx")) {
// 使用 XSSFWorkbook 解析xlsx
workBook = new XSSFWorkbook(fis);
} else {
return Result.buildFail("文件格式错误");
}
//2.获取excel数据存入map;
List<Map<Integer, List<String>>> formatList = checkExcelFormat(workBook);
//总的有效数据条数totalRows(不含首行中文列名),总的字段列数totalCols
int totalRows = Integer.valueOf(formatList.get(1).get(0).get(1));
int totalCols = Integer.valueOf(formatList.get(1).get(0).get(2));
List<String> formatFailReasonList = formatList.get(1).get(1);
//3.解析,根据业务处理数据文件
String userLogin = requestVO.getUserPartyVO().userLoginId();
List<Map<Integer, List<String>>> dealList = dealImportedAccept(formatList,userLogin,totalCols);
int dealSuccNum = Integer.valueOf(dealList.get(0).get(0).get(0));
List<String> dealFailReasonList = dealList.get(0).get(1);
//反馈信息合并(成功,失败条数)
resp.setSuccNum(dealSuccNum);
resp.setFailNum(totalRows-dealSuccNum);
//反馈信息合并(反馈信息list)
formatFailReasonList.addAll(dealFailReasonList);
resp.setFailReasonList(formatFailReasonList);
} catch (Exception e) {
e.printStackTrace();
} finally{
//关流
if(fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();