EasyPoi 导入校验使用

EasyPoi 导入校验使用

因工作需要,使用easypoi导入表格,并进行校验,将表格中有问题的地方,给出提示信息,以表格形式返回.

本篇,直接讲述Excel导入校验,基本介绍后续补上.

1 基于Springboot的easypoi导入表格校验

1 pom.xml

        <!--easypoi与SpringBoot的整合包-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>

2 实体类DTO

/**
 * @author Chengfei
 * @description 手机类
 * @date 2021/1/30
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PhoneDTO {

    @Max(value = 15,message = "最大不能超过15")
    @Min(value = 3,message = "最小不能小于3")
    @Excel(name = "商品id", width = 15,orderNum = "10")
    @NotNull(message = "商品id不能为空哦!!!")
    private int id;

    @Length(max = 2)
    @Excel(name = "手机名", width = 15,orderNum = "20")
    @NotNull(message = "用户名不能为空哦!!")
    private String phoneName;

    @Excel(name = "价格", width = 15, orderNum = "30")
    @NotNull(message = "价格不能为空!!")
    @Digits(integer = 3,fraction = 2,message = "整数位最多3位,小数位最多2位")
    @DecimalMin(value = "0",message = "成本不能为负数")
    private BigDecimal cost;

    @Excel(name = "时间", exportFormat = "yyyy-MM-dd HH:mm:ss", importFormat = "yyyy-MM-dd HH:mm:ss", width = 25, orderNum = "40")
    private LocalDateTime createTime;

/*
	String类型可以使用正则校验
    @Pattern(regexp = "^[1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*$" ,message = "必须为金额数值")
      private String money;
*/

}

3 工具类

public class ExcelUtils {
    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }
    
        /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }
    
        /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }
    
    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }
    }

}

4 自定义校验类

/**
 * @author Chengfei
 * @description
 * @date 2021/1/30
 */
@Component
public class PhoneExcelverifiyName implements IExcelVerifyHandler<PhoneDTO> {

    //保存表格中的手机名称
    private static  Set<String> stringSet = new HashSet<>();

    @Override
    public ExcelVerifyHandlerResult verifyHandler(PhoneDTO phoneDTO) {

        //设置默认验证为true
        ExcelVerifyHandlerResult excelVerifyHandlerResult = new ExcelVerifyHandlerResult(true);
        if (StringUtils.isNotBlank(phoneDTO.getPhoneName())) {
            stringSet.add(phoneDTO.getPhoneName());
            StringBuilder str = new StringBuilder();

            //校验表格中手机名称是否一致, 不一致,给出错误提示
            if (stringSet.size()>1){
                excelVerifyHandlerResult.setSuccess(false);
                str.append("手机名称不一致 ");
            }

            // 手机名为小米 则为重复录入
            if ("小米".equals(phoneDTO.getPhoneName())) {

                if (excelVerifyHandlerResult.isSuccess()){
                    excelVerifyHandlerResult.setSuccess(false);
                    str.append("手机名称重复");
                }else {
                    str.append(",手机名称重复");
                }
            }

            excelVerifyHandlerResult.setMsg(str.toString());

        }
        return excelVerifyHandlerResult;
    }
}

5 controller控制层

/**
 * @author Chengfei
 * @description
 * @date 2021/1/30
 */
@Controller
@RequestMapping("/phone")
public class PhoneController {

    /**
     * 导出1  使用网上excel导出导入工具类  无需校验的导入导出
     *
     * @param response
     */
    @RequestMapping(value = "/export")
    public void exportExcel(HttpServletResponse response) throws IOException {

        List<PhoneDTO> phoneDTOList = new ArrayList<>();
        phoneDTOList.add(new PhoneDTO(6,"小米",new BigDecimal(33.00),LocalDateTime.now()));
        phoneDTOList.add(new PhoneDTO(2,"小米",new BigDecimal(33.00),LocalDateTime.now()));
        phoneDTOList.add(new PhoneDTO(18,"小米米米米米米",new BigDecimal(33.00),LocalDateTime.now()));
        phoneDTOList.add(new PhoneDTO(6,null,new BigDecimal(-1.02),LocalDateTime.now()));
        phoneDTOList.add(new PhoneDTO(6,"小米",new BigDecimal(3322.00),LocalDateTime.now()));
        phoneDTOList.add(new PhoneDTO(6,"小米",new BigDecimal(33.0067),LocalDateTime.now()));

        ExcelUtils.exportExcel(phoneDTOList, "手机信息表", "手机信息", PhoneDTO.class, "手机信息", response);
    }


    @Autowired
    private PhoneExcelverifiyName phoneExcelverifiyName;
    /**
     * 文件校验导入
     *
     * @return
     */
    @RequestMapping("/import")
    public Object upload(MultipartFile file,HttpServletResponse resp) throws Exception {
        //导入的基本配置
        ImportParams params = new ImportParams();
        //表头一行
        params.setHeadRows(1);
        //标题一行
        params.setTitleRows(1);
        //代表导入这里是需要验证的（根据字段上的注解校验）
        params.setNeedVerify(true);
        //设及一个自定义校验 （自定义校验名字不可重复）
        params.setVerifyHandler(phoneExcelverifiyName);
        //使用框架自身导入工具
        ExcelImportResult<PhoneDTO> result = ExcelImportUtil.importExcelMore(file.getInputStream(), PhoneDTO.class, params);
        //导入成功的数据
        List<PhoneDTO> list = result.getList();
        //失败结果集
        List<PhoneDTO> failList = result.getFailList();
        //拿到导出失败的工作簿
        Workbook failWorkbook = result.getFailWorkbook();



        //验证是否有失败的数据
        if (result.isVerifyFail()) {

            ServletOutputStream fos = resp.getOutputStream();
            //mime类型
            resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            resp.setHeader("Content-disposition", "attachment;filename=error.xlsx");
            result.getFailWorkbook().write(fos);
            fos.close();
        }
        return failList;
    }
    
}

6 结果展示

http://localhost:8080/phone/export

http://localhost:8080/phone/import把下载文件上传

7 说明

要求对导入的表格字段进行校验,并将错误的列,给出提示返回,如没有错误,返回表格内容.

要求:

• 手机名为小米,则提示重复导入
• 手机名不一致,提示手机名称不一致
• 手机名不能为空
• 商品id,大于等于3,小于等于15,且不为空
• 价格中整数最多3位,小数最多2位,且必须为数字类型