如何对导入的excel文件内容进行校验后落库？

Question

请问使用poi怎么校验呢？该怎么修改我的接口呢？

//实体类
public class TaskBranchIssue extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 主键 */
    private Long id;

    /** 机构号 */
    @Excel(name = "支行机构号")
    private String orgNo;

    /** 机构名称 */
    @Excel(name = "支行名称")
    private String orgName;

    /** 存款总金额 */
    @Excel(name = "存款任务数")
    private String depositAmount;

    /** 存款总金额 */
    @Excel(name = "贷款任务数")
    private String loanAmount;

    /** 批次号 */
    @Excel(name = "批次号")
    private String batchNum;

    /** 预留字段1 */
//    @Excel(name = "预留字段1")
    private String remark1;

    /** 预留字段2 */
//    @Excel(name = "预留字段2")
    private String remark2;

    /** 预留字段3 */
//    @Excel(name = "预留字段3")
    private String remark3;

    /** 预留字段4 */
//    @Excel(name = "预留字段4")
    private String remark4;

    /** 预留字段5 */
//    @Excel(name = "预留字段5")
    private String remark5;

    public void setId(Long id) 
    {
        this.id = id;
    }

    public Long getId() 
    {
        return id;
    }
    public void setOrgNo(String orgNo) 
    {
        this.orgNo = orgNo;
    }

    public String getOrgNo() 
    {
        return orgNo;
    }
    public void setOrgName(String orgName) 
    {
        this.orgName = orgName;
    }

    public String getOrgName() 
    {
        return orgName;
    }
    public void setDepositAmount(String depositAmount) 
    {
        this.depositAmount = depositAmount;
    }

    public String getDepositAmount() 
    {
        return depositAmount;
    }
    public void setLoanAmount(String loanAmount) 
    {
        this.loanAmount = loanAmount;
    }

    public String getLoanAmount() 
    {
        return loanAmount;
    }
    public void setBatchNum(String batchNum) 
    {
        this.batchNum = batchNum;
    }

    public String getBatchNum() 
    {
        return batchNum;
    }
    public void setRemark1(String remark1) 
    {
        this.remark1 = remark1;
    }

    public String getRemark1() 
    {
        return remark1;
    }
    public void setRemark2(String remark2) 
    {
        this.remark2 = remark2;
    }

    public String getRemark2() 
    {
        return remark2;
    }
    public void setRemark3(String remark3) 
    {
        this.remark3 = remark3;
    }

    public String getRemark3() 
    {
        return remark3;
    }
    public void setRemark4(String remark4) 
    {
        this.remark4 = remark4;
    }

    public String getRemark4() 
    {
        return remark4;
    }
    public void setRemark5(String remark5) 
    {
        this.remark5 = remark5;
    }

    public String getRemark5() 
    {
        return remark5;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
            .append("id", getId())
            .append("orgNo", getOrgNo())
            .append("orgName", getOrgName())
            .append("depositAmount", getDepositAmount())
            .append("loanAmount", getLoanAmount())
            .append("batchNum", getBatchNum())
            .append("createTime", getCreateTime())
            .append("createBy", getCreateBy())
            .append("updateTime", getUpdateTime())
            .append("updateBy", getUpdateBy())
            .append("remark1", getRemark1())
            .append("remark2", getRemark2())
            .append("remark3", getRemark3())
            .append("remark4", getRemark4())
            .append("remark5", getRemark5())
            .toString();
    }
}

//导入接口
@Log(title = "支行任务下发", businessType = BusinessType.IMPORT)
    @RequiresPermissions("largescreen:taskBranchIssue:import")
    @PostMapping("/importData")
    public AjaxResult importData(MultipartFile file, boolean isUpdateSupport) throws Exception {
Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheetAt0 = workbook.getSheetAt(0);
        //校验单元格 是否符合指定条件
        // 若不符合 定位到不符合的单元格 显示单元格位置给用户
        int lastRowNum = sheetAt0.getLastRowNum();
        for(int index=3;index<=lastRowNum;index++){
            Row row = sheetAt0.getRow(index);
            short lastCellNum = row.getLastCellNum();
            for(int indexCell=0;indexCell<lastCellNum;indexCell++){
                Cell cell = row.getCell(indexCell);
                cell.setCellType(CellType.STRING);
                //
            }
        }
}

Answer 1

你不是在用两个循环遍历吗？一个个判断不行吗？
题外话，你在提问时记得把真实的数据，替换成测试数据。我看你提供的数据都是真实的数据。

Answer 2

抛开你的实际问题。
我想的思路是，在生成Excel模板时，制定一套相匹配的验证规则。

如

{
    title: xxx模板验证规则,
    version: 1.0.0,
    rules: [
        {
            name: 支行名称,
            rule: [
                {
                    required: true,
                },
                {
                    maxLength: 20,
                }
                {
                    minLength: 20,
                }
            ]
        }
    ]
}

然后进行校验

---

本文参与了SegmentFault 思否面试闯关挑战赛，欢迎正在阅读的你也加入。