用POI做excel导入功能,各种校验控制

背景:导入功能

图示:

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();