EasyPoi 导入校验使用 |
您所在的位置：网站首页 › excel提示不能为空 › EasyPoi 导入校验使用 |
EasyPoi 导入校验使用
|
EasyPoi 导入校验使用
因工作需要,使用easypoi导入表格,并进行校验,将表格中有问题的地方,给出提示信息,以表格形式返回. 本篇,直接讲述Excel导入校验,基本介绍后续补上. 1 基于Springboot的easypoi导入表格校验 1 pom.xml cn.afterturn easypoi-spring-boot-starter 4.1.2 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 { //保存表格中的手机名称 private static Set 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 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 result = ExcelImportUtil.importExcelMore(file.getInputStream(), PhoneDTO.class, params); //导入成功的数据 List list = result.getList(); //失败结果集 List 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把下载文件上传
要求对导入的表格字段进行校验,并将错误的列,给出提示返回,如没有错误,返回表格内容. 要求: 手机名为小米,则提示重复导入手机名不一致,提示手机名称不一致手机名不能为空商品id,大于等于3,小于等于15,且不为空价格中整数最多3位,小数最多2位,且必须为数字类型 |
【本文地址】
今日新闻 |
推荐新闻 |