java导入activiti xml文件

转载

GhostLover 2024-12-31 12:29:35

文章标签 java 开发语言后端 mysql 字段 文章分类 Java 后端开发

ImportParams 参数介绍下

属性	类型	默认值	功能
titleRows	int	0	表格标题行数,默认0
headRows	int	1	表头行数,默认1
startRows	int	0	字段真正值和列标题之间的距离默认0
keyIndex	int	0	主键设置,如果这个cell没有值,就跳过或者认为这个是list的下面的值, 这一列必须有值,不然认为这列为无效数据
startSheetIndex	int	0	开始读取的sheet位置,默认为0
sheetNum	int	1	上传表格需要读取的sheet 数量,默认为1
needSave	boolean	false	是否需要保存上传的Excel
needVerfiy	boolean	false	是否需要校验上传的Excel
saveUrl	String	upload/excelUpload	保存上传的Excel目录,默认是如 TestEntity这个类保存路径就是 upload/excelUpload/Test/yyyyMMddHHmss，保存名称上传时间*五位随机数
verifyHanlder	IExcelVerifyHandler	null	校验处理接口,自定义校验
lastOfInvalidRow	int	0	最后的无效行数,不读的行数
readRows	int	0	手动控制读取的行数
importFields	String[]	null	导入时校验数据模板,是不是正确的Excel
keyMark	String	“:”	Key-Value 读取标记,以这个为Key,后面一个Cell 为Value,多个改为ArrayList
readSingleCell	boolean	false	按照Key-Value 规则读取全局扫描Excel,但是跳过List读取范围提升性能仅仅支持titleRows + headRows + startRows 以及 lastOfInvalidRow
dataHanlder	IExcelDataHandler	null	数据处理接口,以此为主,replace,format都在这后面

添加pom依赖

<!--easypoi-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.0.0</version>
</dependency>
<!--工具类-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.23</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.21</version>
</dependency>
<!--validation注解-->
<dependency>
    <groupId>javax.validation</groupId>
    <artifactId>validation-api</artifactId>
    <version>2.0.1.Final</version>
</dependency>
<!-- JSR 303 规范验证包 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.2.4.Final</version>
</dependency>

第一种：简单的导入（无校验）

实体类

package com.example.mybatismysql8demo.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

@Data
public class GoodsImportExcel implements Serializable {

    @Excel(name = "商品名称")
    private String goodsName;

    @Excel(name = "商品价格")
    private BigDecimal price;

    @Excel(name = "商品数量")
    private Integer num;
}

导入格式

java导入activiti xml文件_字段

执行方法

package com.example.mybatismysql8demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

import cn.hutool.core.bean.BeanUtil;
import com.example.mybatismysql8demo.excel.GoodsImportExcel;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

@RestController
public class EasypoiController {

    @RequestMapping(value = "easypoiImport",method = RequestMethod.POST)
    public void easypoiImport(@RequestPart(value = "file") MultipartFile file) {
        //如果出现一个数据库表数据存在多个导入模板，可以在每个模板添加一行隐藏字段命名值，对应导入对象的属性命名值
        try {
            if (!file.isEmpty()) {
                //文件名称
                int begin = Objects.requireNonNull(file.getOriginalFilename()).indexOf(".");
                //文件名称长度
                int last = file.getOriginalFilename().length();
                //判断文件格式是否正确
                String fileName = file.getOriginalFilename().substring(begin, last);
                if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                    System.out.println("上传文件格式不正确,只支持xls、xlsx文件");
                }
            } else {
                throw new IllegalArgumentException("excel格式错误");
            }
            //设置导入参数配置
            ImportParams importParams = new ImportParams();
            //标题(设置忽略的行,字段列前几行)
            importParams.setTitleRows(1);
            //表头(列字段占几行)
            importParams.setHeadRows(1);
            // 校验Excel文件，去掉空行
            importParams.setNeedVerify(true);
            //设置读取行数(默认从0开始)
            importParams.setReadRows(299);
            //流
            InputStream inputStream = file.getInputStream();
            //校验excel字段名
            List<GoodsImportExcel> batchGoodsImportModels = ExcelImportUtil.importExcel(inputStream,GoodsImportExcel.class,importParams);
            //对数据进行处理(去除空数据)，工具文件有方法
            batchGoodsImportModels = batchGoodsImportModels.stream().filter(b-> !BeanUtil.isEmpty(b)).collect(Collectors.toList());
            System.out.println("导入数据:"+batchGoodsImportModels);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

结果打印
导入数据:[GoodsImportExcel(goodsName=苹果, price=10, num=11), GoodsImportExcel(goodsName=香蕉, price=8, num=12)]

第二种：添加必填字段校验（存在错误数据）

实体类

package com.example.mybatismysql8demo.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.DecimalMax;
import javax.validation.constraints.Max;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.math.BigDecimal;

@Data
public class GoodsImportExcel implements IExcelModel, IExcelDataModel {

    @Excel(name = "商品名称")
    @Pattern(regexp = "[\\u4E00-\\u9FA5]{2,5}", message = "数据格式错误")//字符包含2-5个
    @Length(max = 50,message = "长度不可超出50字符")
    private String goodsName;

    @Excel(name = "商品价格")
    @DecimalMax(value = "9999.99", message = "最大可输入4位")
    private BigDecimal price;

    @Excel(name = "商品数量")
    @NotNull(message = "商品数量不能为空")
    @Max(value = 99999, message = "最大可输入5位")
    private Integer num;

    @Excel(name="是否进口", replace = {"否_false", "是_true"})
    private Boolean inward;

    /**错误数据*/
    private String errorMsg;

    /**错误数据行*/
    private Integer rowNum;

    /**错误数据列*/
    private Integer cellNum;

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

    @Override
    public int getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}

导入文件

java导入activiti xml文件_字段_02

执行方法

package com.example.mybatismysql8demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.bean.BeanUtil;
import com.example.mybatismysql8demo.excel.GoodsImportExcel;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

@RestController
public class EasypoiController {

    @RequestMapping(value = "easypoiImport",method = RequestMethod.POST)
    public void employee(@RequestPart(value = "file") MultipartFile file) {
        try {
            if (!file.isEmpty()) {
                //文件名称
                int begin = Objects.requireNonNull(file.getOriginalFilename()).indexOf(".");
                //文件名称长度
                int last = file.getOriginalFilename().length();
                //判断文件格式是否正确
                String fileName = file.getOriginalFilename().substring(begin, last);
                if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                    System.out.println("上传文件格式不正确,只支持xls、xlsx文件");
                }
            } else {
                System.out.println("excel格式错误");
            }
            //设置导入参数配置
            ImportParams params = new ImportParams();
            //标题(设置忽略的行,字段列前几行)
            params.setTitleRows(1);
            //表头(列字段占几行)
            params.setHeadRows(1);
            //设置验证支持
            params.setNeedVerify(true);
            //二.获取excel中的数据，封装成了一个结果对象(有很多东西)
            ExcelImportResult<GoodsImportExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), GoodsImportExcel.class, params);
            //三.获到正确的数据，并把它们保存到数据库
            List<GoodsImportExcel> list = result.getList();
            System.out.println("正确数据:"+list);
            //判断是否有错误
            if(result.isVerfiyFail()){
                System.out.println("错误数据:"+result.getFailList().stream().filter(b-> !BeanUtil.isEmpty(b,"errorMsg")).collect(Collectors.toList()));;
            }
        } catch (Exception e) {
            throw new IllegalArgumentException(e.getMessage());
        }
    }

}

结果打印
正确数据:[GoodsImportExcel(goodsName=香蕉, price=8, num=12, inward=true, errorMsg=null, rowNum=3, cellNum=null)]
错误数据:[GoodsImportExcel(goodsName=苹果, price=10, num=110000, inward=false, errorMsg=最大可输入5位, rowNum=2, cellNum=null)]

第三种：必填校验加自定义校验

实体类

package com.example.mybatismysql8demo.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.*;
import java.math.BigDecimal;

@Data
public class GoodsImportExcel implements IExcelModel, IExcelDataModel {

    @Excel(name = "商品名称",orderNum = "1")
    @Pattern(regexp = "[\\u4E00-\\u9FA5]{2,5}", message = "数据格式错误")//字符包含2-5个
    @Length(max = 50,message = "长度不可超出50字符")
    private String goodsName;

    @Excel(name = "商品价格",orderNum = "2")
    @DecimalMax(value = "9999.99", message = "最大可输入4位")
    private BigDecimal price;

    @Excel(name = "商品数量",orderNum = "3")
    @NotNull(message = "商品数量不能为空")
    @Max(value = 99999, message = "最大可输入5位")
    private Integer num;

    @Excel(name="是否进口", replace = {"否_false", "是_true"},orderNum = "4")
    @NotNull(message = "是否进口不可为空")
    public Boolean inward;

    @Excel(name="进口国家",orderNum = "5")
    private String country;

    @Excel(name="种植地",orderNum = "6")
    private String area;

    /**错误数据*/
    public String errorMsg;

    /**错误数据行*/
    private Integer rowNum;

    /**错误数据列*/
    public Integer cellNum;

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

    @Override
    public int getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}

导入文件

java导入activiti xml文件_java_03

自定义校验方法

package com.example.mybatismysql8demo.config;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import cn.hutool.core.bean.BeanUtil;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;



/***
 * 自定义验证
 * @author Administrator
 */
@Component
public class ParamExcelHandler<T> implements IExcelVerifyHandler<T> {

	private static final String ERROR_MSG = "errorMsg";

	private static final String INWARD = "inward";

	private static final String CELL_NUM = "cellNum";

	/***
	 * 自定义验证实现
	 */
	@Override
	public ExcelVerifyHandlerResult verifyHandler(T object) {
		 //默认设置验证通过为true
		 ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
		try {
			//去除错误字段值后所有数据不为为空
			if (!BeanUtil.isEmpty(object,ERROR_MSG)){
				//获取商品名称
				Field inwardField = object.getClass().getField(INWARD);
				if (inwardField.get(object) != null){
					//当是否进口值不一样时，存在必填值不一样
					Boolean value = (Boolean) inwardField.get(object);
					//获取对应需要校验的属性值
					Map<String,String> map = getVerifyParam(value);
					//获取对象属性值
					Field[] fields = object.getClass().getDeclaredFields();
					//遍历
					for (Field field : fields) {
						// 设置字段可访问
						field.setAccessible(true);
						//校验字段属性是否添加注解
						Excel excelColumnImport = field.getAnnotation(Excel.class);
						if (excelColumnImport != null && map.get(field.getName()) != null && field.get(object) == null){
							//校验是否为空
							result.setSuccess(false);
							//错误信息(需要设置字段为public)
							Field errorMsg = object.getClass().getField(ERROR_MSG);
							if (errorMsg.get(object) == null){
								errorMsg.set(object,map.get(field.getName()));
							}else {
								errorMsg.set(object,errorMsg.get(object) + "," + map.get(field.getName()));
							}
							//设置错误数据列数(需要设置字段为public)
							Field cellNum = object.getClass().getField(CELL_NUM);
							cellNum.set(object,Integer.valueOf(excelColumnImport.orderNum()));
						}
					}
				}
			}
		} catch (NoSuchFieldException | IllegalAccessException e) {
			e.printStackTrace();
		}
		return result;

	}

	private Map<String,String> getVerifyParam(Boolean value) {
		Map<String,String> map = new HashMap<>();
		if (value){
			map.put("country","进口国家不能为空");
		}else {
			map.put("area","种植地不能为空");
		}
		return map;
	}
    
}

执行方法

package com.example.mybatismysql8demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.bean.BeanUtil;
import com.example.mybatismysql8demo.config.ParamExcelHandler;
import com.example.mybatismysql8demo.excel.GoodsImportExcel;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

@RestController
public class EasypoiController {

    @Autowired
    private ParamExcelHandler paramExcelHandler;

    @RequestMapping(value = "easypoiImport",method = RequestMethod.POST)
    public void employee(@RequestPart(value = "file") MultipartFile file) {
        try {
            if (!file.isEmpty()) {
                //文件名称
                int begin = Objects.requireNonNull(file.getOriginalFilename()).indexOf(".");
                //文件名称长度
                int last = file.getOriginalFilename().length();
                //判断文件格式是否正确
                String fileName = file.getOriginalFilename().substring(begin, last);
                if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                    System.out.println("上传文件格式不正确,只支持xls、xlsx文件");
                }
            } else {
                System.out.println("excel格式错误");
            }
            //设置导入参数配置
            ImportParams params = new ImportParams();
            //标题(设置忽略的行,字段列前几行)
            params.setTitleRows(1);
            //表头(列字段占几行)
            params.setHeadRows(1);
            //设置验证支持
            params.setNeedVerify(true);
            //设置一个验证处理器(额外的校验，例如判断数据库是否存在相同数据)
            params.setVerifyHandler(paramExcelHandler);
            //二.获取excel中的数据，封装成了一个结果对象(有很多东西)
            ExcelImportResult<GoodsImportExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), GoodsImportExcel.class, params);
            //三.获到正确的数据，并把它们保存到数据库
            List<GoodsImportExcel> list = result.getList();
            System.out.println("正确数据:"+list);
            //判断是否有错误
            if(result.isVerfiyFail()){
                System.out.println("错误数据:"+result.getFailList().stream().filter(b-> !BeanUtil.isEmpty(b,"errorMsg")).collect(Collectors.toList()));;
            }
        } catch (Exception e) {
            throw new IllegalArgumentException(e.getMessage());
        }
    }

}

结果打印
正确数据:[]
错误数据:[GoodsImportExcel(goodsName=苹果, price=10, num=1100, inward=true, country=null, area=null, errorMsg=进口国家不能为空,null, rowNum=2, cellNum=5), GoodsImportExcel(goodsName=香蕉, price=8, num=12, inward=false, country=null, area=null, errorMsg=种植地不能为空,null, rowNum=3, cellNum=6)]

第四种：存在合并数据

package com.example.mybatismysql8demo.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;

import javax.validation.constraints.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

@Data
public class GoodsImportExcel implements IExcelModel, IExcelDataModel {

    /**needMerge = true的属性,就可以完成单元格的合并*/
    @Excel(name = "商品分类",needMerge = true)
    @NotNull(message = "是否进口不可为空")
    public String goodsCategory;

    @Excel(name = "型号",needMerge = true)
    @NotNull(message = "型号不可为空")
    public String model;

    /**ExcelCollection一对多信息*/
    @ExcelCollection(name = "商品信息")
    public List<GoodsNeedInfo> goodsInfo;

    @Data
    public static class GoodsNeedInfo implements Serializable {

        @Excel(name = "商品名称",orderNum = "1")
        public String goodsName;

        @Excel(name = "商品价格",orderNum = "2")
        public BigDecimal price;

        @Excel(name = "商品数量",orderNum = "3")
        public Integer num;

        @Excel(name="是否进口", replace = {"否_false", "是_true"},orderNum = "4")
        public Boolean inward;

        @Excel(name="进口国家",orderNum = "5")
        public String country;

        @Excel(name="生产地",orderNum = "6")
        public String area;

        /**错误数据*/
        public String errorMsg;

        /**错误数据行*/
        public Integer rowNum;

        /**错误数据列*/
        public Integer cellNum;
    }

    /**错误数据*/
    public String errorMsg;

    /**错误数据行*/
    public Integer rowNum;

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

    @Override
    public int getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}

导入文件

java导入activiti xml文件_字段_04

自定义必填校验

package com.example.mybatismysql8demo.config;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import cn.hutool.core.bean.BeanUtil;
import com.example.mybatismysql8demo.excel.GoodsImportExcel;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



/***
 * 自定义验证
 * @author Administrator
 */
@Component
public class ParamExcelHandler implements IExcelVerifyHandler<GoodsImportExcel> {

	private static final String ERROR_MSG = "errorMsg";

	/***
	 * 自定义验证实现
	 */
	@Override
	public ExcelVerifyHandlerResult verifyHandler(GoodsImportExcel goodsImportExcel) {
		//默认设置验证通过为true
		ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
		try {
			//去除错误字段值后所有数据不为为空(为了防止空格行)
			if (!BeanUtil.isEmpty(goodsImportExcel,ERROR_MSG)){
				//获取商品集合
				List<GoodsImportExcel.GoodsNeedInfo> goodsNeedInfos = goodsImportExcel.getGoodsInfo();
				//数据处理
				for (GoodsImportExcel.GoodsNeedInfo goodsNeedInfo : goodsNeedInfos) {
					//获取字段的列数
					Field[] fields = goodsNeedInfo.getClass().getDeclaredFields();
					//获取必填校验属性值
					Map<String, String> verifyParam = getVerifyParam(goodsNeedInfo.getInward());
					for (Field field : fields) {
						//设置属性可访问
						field.setAccessible(true);
						//校验字段属性是否添加注解
						Excel excelColumnImport = field.getAnnotation(Excel.class);
						if (excelColumnImport != null && verifyParam.get(field.getName()) != null && field.get(goodsNeedInfo) == null){
							//错误信息
							if (goodsNeedInfo.getErrorMsg() == null){
								goodsNeedInfo.setErrorMsg(verifyParam.get(field.getName()));
							}else {
								goodsNeedInfo.setErrorMsg(goodsNeedInfo.getErrorMsg() + "," + verifyParam.get(field.getName()));
							}
						}
					}
				}
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return result;

	}

	private Map<String,String> getVerifyParam(Boolean inward) {
		Map<String,String> map = new HashMap<>();
		map.put("goodsName","商品名称不能为空");
		map.put("price","商品加个不能为空");
		map.put("num","商品数量不能为空");
		map.put("inward","是否进口不能为空");
		if (inward){
			map.put("country","进口国家不能为空");
		}else {
			map.put("area","种植地不能为空");
		}
		return map;
	}
    
}

执行方法

package com.example.mybatismysql8demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;

import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSONObject;
import com.example.mybatismysql8demo.config.ParamExcelHandler;
import com.example.mybatismysql8demo.excel.GoodsImportExcel;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;
import java.util.stream.Collectors;

@RestController
public class EasypoiController {

    @Autowired
    private ParamExcelHandler paramExcelHandler;

    @RequestMapping(value = "easypoiImport",method = RequestMethod.POST)
    public void employee(@RequestPart(value = "file") MultipartFile file) {
        try {
            if (!file.isEmpty()) {
                //文件名称
                int begin = Objects.requireNonNull(file.getOriginalFilename()).indexOf(".");
                //文件名称长度
                int last = file.getOriginalFilename().length();
                //判断文件格式是否正确
                String fileName = file.getOriginalFilename().substring(begin, last);
                if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
                    System.out.println("上传文件格式不正确,只支持xls、xlsx文件");
                }
            } else {
                System.out.println("excel格式错误");
            }
            //设置导入参数配置
            ImportParams params = new ImportParams();
            //标题(设置忽略的行,字段列前几行)
            params.setTitleRows(1);
            //表头(列字段占几行)
            params.setHeadRows(2);
            //设置验证支持
            params.setNeedVerify(true);
            //设置一个验证处理器(额外的校验，例如判断数据库是否存在相同数据)
            params.setVerifyHandler(paramExcelHandler);
            //二.获取excel中的数据，封装成了一个结果对象(有很多东西)
            ExcelImportResult<GoodsImportExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), GoodsImportExcel.class, params);
            //三.获到正确的数据，并把它们保存到数据库
            List<GoodsImportExcel> list = new ArrayList<>();
            //错误数据
            List<GoodsImportExcel> failList = new ArrayList<>();
            //正确数据处理，集合中包含的错误数据
            for (GoodsImportExcel goodsImportExcel : result.getList()) {
                //数据正确、错误分组
                Map<Boolean, List<GoodsImportExcel.GoodsNeedInfo>> groupedByErrorMsg = goodsImportExcel.getGoodsInfo().stream().collect(Collectors.groupingBy(info -> StringUtils.isEmpty(info.getErrorMsg())));
                //正确数据
                if (groupedByErrorMsg.get(Boolean.TRUE) != null){
                    GoodsImportExcel goodsImport = new GoodsImportExcel();
                    BeanUtil.copyProperties(goodsImportExcel,goodsImport);
                    goodsImport.setGoodsInfo(groupedByErrorMsg.get(Boolean.TRUE));
                    list.add(goodsImport);
                }
                //错误数据
                if (groupedByErrorMsg.get(Boolean.FALSE) != null){
                    GoodsImportExcel goodsImport = new GoodsImportExcel();
                    BeanUtil.copyProperties(goodsImportExcel,goodsImport);
                    goodsImport.setGoodsInfo(groupedByErrorMsg.get(Boolean.FALSE));
                    failList.add(goodsImport);
                }
            }
            //错误数据
            System.out.println("正确数据:"+ JSONObject.toJSONString(list));
            //判断是否有错误
            if(result.isVerfiyFail()){
                List<GoodsImportExcel> errorList = result.getFailList().stream().filter(b -> !BeanUtil.isEmpty(b, "errorMsg")).collect(Collectors.toList());
                failList.addAll(errorList);
                System.out.println("错误数据:"+JSONObject.toJSONString(failList));;
            }
        } catch (Exception e) {
            throw new IllegalArgumentException(e.getMessage());
        }
    }


}

结果打印
正确数据:[{"goodsCategory":"水果","goodsInfo":[{"goodsName":"香蕉","inward":false,"num":12,"price":8}],"model":"m0001","rowNum":3}]
错误数据:[{"goodsCategory":"水果","goodsInfo":[{"errorMsg":"进口国家不能为空","goodsName":"苹果","inward":true,"num":110,"price":10}],"model":"m0001","rowNum":3},{"errorMsg":"型号不可为空","goodsCategory":"零食","goodsInfo":[{"errorMsg":"商品数量不能为空,种植地不能为空","goodsName":"辣条","inward":false,"price":5}],"rowNum":5}]

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。