SpringBoot实现文件上传（Excel表）

需求：
SpringBoot搭建项目实现文件的上传，对文件字段进行校验，将校验后的数据用对象封装，调用dao接口进行批量update操作，结果保存到MySQL数据库。对有格式有错误的数据进行统计并反馈到前端页面。

关于实现批量update操作，见(https://blog.csdn.net/m0_52850461/article/details/121504680?spm=1001.2014.3001.5501)

思路：
后端使用MultipartFile 接收文件，
使用EasyExcel组件完成Excel文件的处理，
在service目录创建一个监听器来监控字段，并进行校验。

实现：
准备表格：

pom文件：

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

实体类：

@Data
@AllArgsConstructor
public class CustomNetworkBaseSupplyInfo {
    @ExcelProperty("DNN")
    private String dnnName;
    @ExcelProperty("用户名称")
    private String cusName;
    @ExcelProperty("基站IP(多个以英文逗号相隔)")
    private String basestationIp;
}

Controller层：

/**
     * 批量导入信息
     *
     * @param file
     * @throws IOException
     */
    @PostMapping("ImportInfo")
    @ApiOperation(value = "批量导入信息", httpMethod = "POST")
    public ReturnJson importCustomNetworkInfo(@ApiParam(name = "file", value = "要导入的文件") MultipartFile file,) throws IOException {
        ExcelImportResult result = customNetworkSupplyInfoService.importCustomNetworkInfo(file);
        return new ReturnJson(result);
    }

service层：

/**
     * 通过Excel批量导入信息
     *
     * @param file
     * @return
     * @throws IOException
     */
    @Override
    public ExcelImportResult importCustomNetworkInfo(MultipartFile file) throws IOException {

        //保存导出来的信息
        List<CustomNetworkBaseSupplyInfo> lists = new ArrayList<>();
        //记录错误的信息
        StringBuffer errorMessage = new StringBuffer();
        //记录操作的结果，初始化为false
        String result = "false";
        //创建监听器
        CustomNetworkSupplyListener listener = new CustomNetworkSupplyListener(lists, errorMessage);
        EasyExcel.read(file.getInputStream(), CustomNetworkBaseSupplyInfo.class, listener).sheet().doRead();
        //判断Excel信息是否有错误
        if (!StringUtils.isEmpty(errorMessage.toString())) {
            result = "success";
            //调用接口存到数据库
            supplyInfoMapper.importCustomNetworkInfo(lists, updateName);
        }
        return new ExcelImportResult(result, errorMessage.toString());
    }

监听器：

/**
 * 信息监听器
 *
 * @author libo
 * @data 2021/11/19 - 15:37
 */
public class CustomNetworkSupplyListener extends AnalysisEventListener<CustomNetworkBaseSupplyInfo> {

    //保存导出来的信息
    private List<CustomNetworkBaseSupplyInfo> lists;
    //记录错误的信息
    private StringBuffer errorMessage;

    public CustomNetworkSupplyListener(List<CustomNetworkBaseSupplyInfo> lists, StringBuffer errorMessage) {
        this.lists = lists;
        this.errorMessage = errorMessage;
    }

    @Override
    public void invoke(CustomNetworkBaseSupplyInfo customNetworkBaseSupplyInfo, AnalysisContext analysisContext) {
        //格式检查
        StringBuffer errorInfo = validInfo(customNetworkBaseSupplyInfo);
        //获取当前Excel行号
        ReadRowHolder readRowHolder = analysisContext.readRowHolder();
        Integer index = readRowHolder.getRowIndex() + 1;
        //判断当前行号的数据是否有数据错误
        if (StringUtils.isNotEmpty(errorInfo)) {
            errorMessage.append("第" + index + "行填写有误:" + errorInfo + "\n");
        } else {
            lists.add(customNetworkBaseSupplyInfo);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    public StringBuffer validInfo(CustomNetworkBaseSupplyInfo supplyInfo) {

        //记录单个error
        StringBuffer errorInfo = new StringBuffer();
        //dnn非空
        String dnnName = supplyInfo.getDnnName();
        if (StringUtils.isEmpty(dnnName)) {
            errorInfo.append("DNN的值不能为空;");
        }
        //cusName非空
        String cusName = supplyInfo.getCusName();
        if (StringUtils.isEmpty(cusName)) {
            errorInfo.append("用户名称的值不能为空;");
        }
        //多个以英文逗号相隔,格式为：设备名称/设备IP
        String equipmentB = supplyInfo.getEquipmentB();
        if (!checkIp(equipmentB)) {
            errorInfo.append("B设备格式有误");
        }
    /**
     * ip的格式判断,格式为：设备名称1/设备IP1,设备名称2/设备IP2,...
     *
     * @param baseIp
     * @return
     */
    public boolean checkIp(String baseIp) {
        if (!StringUtils.isEmpty(baseIp)) {
            String[] baseIps = baseIp.split(",");
            //大于1
            if (baseIps.length >= 1) {
                for (String ip : baseIps) {
                    String[] lists = ip.split("/");
                    if (lists.length >= 2 && !"NULL".equals(lists[1])) {  //ip为有效值
                        if (Validator.isIpv4(lists[1]) || Validator.isIpv6(lists[1])) {
                            continue;
                        } else {  //不符合Ipv4或者Ipv6即为错误,其他为正确值
                            return false;
                        }
                    }
                }
            }
        }
        return true;
    }
}

Mapper层：

 Integer importCustomNetworkInfo(@Param("supplyInfos")List<CustomNetworkBaseSupplyInfo> customNetworkBaseSupplyInfo);

Mapper.xml文件：

<update id="importCustomNetworkInfo">
        update cus_network_info set
        <foreach collection="supplyInfos" item="supplyInfo" open="cus_name = case dnn_name" close="end,">
            when #{supplyInfo.dnnName} then #{supplyInfo.cusName}
        </foreach>
        <foreach collection="supplyInfos" item="supplyInfo" open="basestation_ip = case dnn_name" close="end">
            when #{supplyInfo.dnnName} then #{supplyInfo.basestationIp}
        </foreach>
        where dnn_name in
        <foreach collection="supplyInfos" item="supplyInfo" open="(" close=")" separator=",">
            #{supplyInfo.dnnName}
        </foreach>
    </update>