设为首页收藏本站
开启辅助访问

关于EasyExcel 优化，实现格式自定义，数据字典自动转化。

 您所在的位置：网站首页 › 数据字典转换mysql › 关于EasyExcel 优化，实现格式自定义，数据字典自动转化。

关于EasyExcel 优化，实现格式自定义，数据字典自动转化。

2023-08-17 20:35| 来源: 网络整理| 查看: 265

最终要达到的结果： 1.下载模板，添加模板说明sheet 2.导入数据，对数据进行校验，对模板进行检验，并将校验结果展示给用户。 3.导出数据，可选导出字段 4.字典数据自动转化，导入/导出

操作方式：添加一个注解即可。@ExcelVaild

下载模板优化 优化效果:如下(添加模板说明)

在这里插入图片描述实现方式：添加自定义注解@ExcelVaild 具体代码：

注解：

@Documented @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelVaild { ExcelValidEnum value() default ExcelValidEnum.NULL; String dictType() default ""; //配置字典数据 }

枚举

@AllArgsConstructor public enum ExcelValidEnum { NULL("", "可以为空"),//不校验 NOTNULL("\\S", "不为空"), BIRTHDAY("[1-9]{4}([-./])\\d{1,2}\\1\\d{1,2}", "生日"), BIRTHDAY_NOTNULL("[1-9]{4}([-./])\\d{1,2}\\1\\d{1,2}", "生日"), EMAIL("\\w+@\\w+\\.[a-z]+(\\.[a-z]+)?", "邮件"), EMAIL_NOTNULL("\\w+@\\w+\\.[a-z]+(\\.[a-z]+)?", "邮件"), IDCARD("[1-9]\\d{13,16}[a-zA-Z0-9]{1}", "身份证"), IDCARD_NOTNULL("[1-9]\\d{13,16}[a-zA-Z0-9]{1}", "身份证"), DIGIT("\\-?[1-9]\\d+", "整数"), DIGIT_NOTNULL("\\-?[1-9]\\d+", "整数"), DECIMAL("[-+]?[0-9]*\\.?[0-9]+", "浮点数"), DECIMAL_NOTNULL("[-+]?[0-9]*\\.?[0-9]+", "浮点数"), POSTCODE("[1-9]\\d{5}", "邮编"), POSTCODE_NOTNULL("[1-9]\\d{5}", "邮编"), CHINESE("[1-9]{4}([-./])\\d{1,2}\\1\\d{1,2}", "中文"), CHINESE_NOTNULL("[1-9]{4}([-./])\\d{1,2}\\1\\d{1,2}", "中文"), PHONE("^(13[0-9]|14[579]|15[0-3,5-9]|16[6]|17[0135678]|18[0-9]|19[89])\\d{8}$", "手机号码"), PHONE_NOTNULL("^(13[0-9]|14[579]|15[0-3,5-9]|16[6]|17[0135678]|18[0-9]|19[89])\\d{8}$", "手机号码"), CP("^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领A-Z]{1}[A-Z]{1}[A-Z0-9]{3,4}[A-Z0-9挂学警港澳]{1}$", "车牌号"), CP_NOTNULLL("^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领A-Z]{1}[A-Z]{1}[A-Z0-9]{3,4}[A-Z0-9挂学警港澳]{1}$", "车牌号"); private String regex; private String msg; /** * 正则校验 * @param enums * @param name * @param val * @return */ public static String regexCheck(ExcelValidEnum enums, String name, Object val) { String msgs = null; if (enums.name().equals(ExcelValidEnum.NULL.name())) { return msgs; } if (enums.name().equals(ExcelValidEnum.NOTNULL.name())) { boolean notEmpty = StringUtils.isNotEmpty(val); if (notEmpty) { return msgs; } else { msgs = ("【" + name + "】不符合" + enums.getMsg() + "的规则"); return msgs; } } if (StringUtils.isNotEmpty(enums.regex)&& StringUtils.isNotEmpty(val)) { boolean flag = Pattern.matches(enums.getRegex(), String.valueOf(val)); if (!flag) { msgs = ("【" + name + "】不符合" + enums.getMsg() + "的规则"); return msgs; } } if (StringUtils.isNotEmpty(enums.regex)&& enums.name().endsWith("NOTNULL") ){ if (StringUtils.isEmpty(val)){ msgs = ("【" + name + "】不能为空"); return msgs; } boolean flag = Pattern.matches(enums.getRegex(), String.valueOf(val)); if (!flag) { msgs = ("【" + name + "】不符合" + enums.getMsg() + "的规则"); return msgs; } } return msgs; } }

vo类

public class DzdaQbztkBcDxsVO extends SysVO { private static final long serialVersionUID = 1L; /** * 姓名 */ @ApiModelProperty(value = "姓名") //@NotBlank(message = "姓名 不能为空") @ExcelProperty(value = "姓名") private String xm; /** * 身份证号码 */ @ApiModelProperty(value = "身份证号码") //@NotBlank(message = "身份证号码 不能为空") @ExcelProperty("身份证号码") @ExcelVaild(value =ExcelValidEnum.IDCARD_NOTNULL) private String sfzh; /** * 性别 */ @ApiModelProperty(value = "性别") //@NotBlank(message = "性别 不能为空") @ExcelProperty("性别") @ExcelVaild(dictType = "gender") private String xb; /** * 民族 */ @ApiModelProperty(value = "民族") //@NotBlank(message = "民族 不能为空") @ExcelProperty("民族") @ExcelVaild(dictType = "cqfk_common_mz") private String mz; /** * 学校 */ @ApiModelProperty(value = "学校") //@NotBlank(message = "学校 不能为空") @ExcelProperty("学校") private String xx; /** * 专业名称 */ @ApiModelProperty(value = "专业名称") //@NotBlank(message = "专业名称 不能为空") @ExcelProperty("专业名称") private String zymc; /** * 层次 */ @ApiModelProperty(value = "层次") //@NotBlank(message = "层次 不能为空") @ExcelProperty("层次") @ExcelVaild(dictType = "dzda_ztk_cc") private String cc; /** * 政治面貌 */ @ApiModelProperty(value = "政治面貌") //@NotBlank(message = "政治面貌 不能为空") @ExcelProperty("政治面貌") @ExcelVaild(dictType = "dzda_ztk_zzmm") private String zzmm; /** * 入学年份 */ @ApiModelProperty(value = "入学年份") //@NotBlank(message = "入学年份 不能为空") @ExcelProperty("入学年份") private String rxnf; /** * 所属区县area_code */ @ApiModelProperty(value = "所属区县area_code") //@NotBlank(message = "所属区县area_code 不能为空") @ExcelProperty("所属区县area_code") private String ssqxAreacode; /** * 所属区县area_code */ @ApiModelProperty(value = "所属区县名") //@NotBlank(message = "所属区县area_code 不能为空") @ExcelProperty("所属区县名") private String ssqxmc; /** * 所属派出所dept_id */ @ApiModelProperty(value = "所属派出所dept_id") //@NotBlank(message = "所属派出所dept_id 不能为空") @ExcelProperty("所属派出所dept_id") private String sspcsId; /** * 所属派出所dept_id */ @ApiModelProperty(value = "所属派出所名称") //@NotBlank(message = "所属派出所dept_id 不能为空") @ExcelProperty("所属派出所名称") private String sspcsmc; /** * 是否在渝 */ @ApiModelProperty(value = "是否在渝") //@NotBlank(message = "是否在渝 不能为空") @ExcelProperty("是否在渝") @ExcelVaild(dictType = "is_no") private String sfzy; /** * 是否有问题 */ @ApiModelProperty(value = "是否有问题") //@NotBlank(message = "是否有问题 不能为空") @ExcelProperty("是否有问题") @ExcelVaild(dictType = "is_no") private String sfywt;

controller

/** * 导出模板 * @param response * @throws IOException */ @GetMapping("/export") public void downLoad( HttpServletResponse response) { ExportEasyExcelUtil.exportemplete(response,DzdaQbztkBcDxsVO.class); }

ExportEasyExcelUtil

* @author ssp * @className ExportEasyExcelUtil * @description TODO excel导出工具类 * @create 2022/8/11 16:18 **/ public class ExportEasyExcelUtil { private static RemoteDicService getRemoteDicService(){ return SpringContextHolder.getBean(RemoteDicService.class); } /** * 导出自定义列(导出数据) * @param response * @param fileName 导出名称 * @param includeColumnFiledNames 导出字段属性集合 * @param objectClass 导出对象的class对象 * @param datas 导出数据集合 * 若调用此方法，浏览器可直接访问接口即可下载文件 */ public static void export(HttpServletResponse response, String fileName,List includeColumnFiledNames, Class objectClass, List datas) { try { List list = converDict(objectClass, datas); response.addHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); if (ListUtils.isEmpty(includeColumnFiledNames)){ EasyExcel.write(response.getOutputStream(),objectClass) .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)) // 简单的列宽策略，列宽20 .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)) .sheet("Sheet1"). doWrite(list); }else { EasyExcel.write(response.getOutputStream(),objectClass).includeColumnFiledNames(includeColumnFiledNames).sheet("Sheet1"). doWrite(list); } } catch (IOException e) { e.printStackTrace(); } } /** * 下载模板 * @param response * @param objectClass */ public static void exportemplete(HttpServletResponse response,Class objectClass){ ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(response.getOutputStream()).build(); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); //可以防止中文乱码,和easyexcel没有关系 String fileName = DateUtils.getYyyyMMdd(); fileName = URLEncoder.encode(fileName,"utf-8"); response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xlsx"); WriteSheet writeSheet = EasyExcel.writerSheet(0,"数据模板").head(objectClass) .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)) // 简单的列宽策略，列宽20 .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)) .build(); excelWriter.write(new ArrayList(),writeSheet); //sheet2 writeSheet = EasyExcel.writerSheet(1,"模板说明《必看》").head(ExcelVO.class) .registerWriteHandler(new CellStyleStrategy()) .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) // 简单的列宽策略，列宽20 .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)) .build(); ArrayList arrayList = new ArrayList(); Field[] fields = objectClass.getDeclaredFields(); for (Field field : fields) { ExcelProperty annotation = field.getAnnotation(ExcelProperty.class); if (null!=annotation){ ExcelVO excelVO = new ExcelVO(); excelVO.setZdsx(field.getName()); String[] value = annotation.value(); StringBuilder builder = new StringBuilder(); for (String s : value) { builder.append(s); } excelVO.setZdmc(builder.toString()); String typeName = field.getGenericType().getTypeName(); String[] split = typeName.split("\\."); excelVO.setDateType(dataStringToChinese(split[split.length-1])); ExcelVaild vaild = field.getAnnotation(ExcelVaild.class); if (null!=vaild){ if (StringUtils.isNotEmpty(vaild.dictType())){ RemoteDicService remoteDicService = getRemoteDicService(); excelVO.setIsDict("是"); excelVO.setDictType(vaild.dictType()); List data = remoteDicService.getDictByType(vaild.dictType(), SecurityConstants.FROM_IN).getData(); if (ListUtils.isNotEmpty(data)){ StringBuffer buffer = new StringBuffer(); data.stream().forEach(e->{ buffer.append(e.getLabel() + ";"); }); excelVO.setRemark(buffer.toString()); } } if (vaild.value().name().endsWith("NOTNULL")){ excelVO.setIsNotNull("是"); } } arrayList.add(excelVO); } } excelWriter.write(arrayList, writeSheet); } catch (IOException e) { e.printStackTrace(); }finally { excelWriter.finish(); } } /** * 数据字段转换 * @param objectClass * @param datas * @return */ private static List converDict(Class objectClass, List datas){ HashMap map = new HashMap(); Field[] fields =objectClass.getDeclaredFields(); Arrays.stream(fields).forEach(e->{ ExcelVaild annotation = e.getAnnotation(ExcelVaild.class); if (null!=annotation){ String s = annotation.dictType(); if (StringUtils.isNotEmpty(s)){ map.put(s, e); } } }); datas.stream().forEach(e->{ try { for (String key : map.keySet()) { Field field = map.get(key); field.setAccessible(true); String value = field.get(e).toString(); if (StringUtils.isNotEmpty(value)){ String labelByCache = DictUtil.getLabelByCache(key, value); map.get(key).set(e,labelByCache); } } }catch (Exception E){ E.getMessage(); } }); return datas; } public static String dataStringToChinese(String data) { String s = data; switch (s) { case "String": s = "String(字符串)"; break; case "Integer": s="Integer(数字)"; break; case "Boolean": s="Boolean(判断 true/false)"; break; case "LocalDateTime": s = "LocalDateTime(日期 yyyy-MM-dd HH:mm:ss)"; break; case "LocalDate": s = "LocalDate(日期 yyyy-MM-dd)"; break; case "Date": s = "Date(日期 yyyy-MM-dd HH:mm:ss)"; break; } return s; } } a

sheet2（模板说明：格式自定义拦截器）

public class CellStyleStrategy extends AbstractVerticalCellStyleStrategy { /** * 表头样式设置 * @param head * @return */ @Override protected WriteCellStyle headCellStyle(Head head) { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 12); headWriteCellStyle.setWriteFont(headWriteFont); return headWriteCellStyle; } /** * 内容样式设置 * @param head * @return */ @Override protected WriteCellStyle contentCellStyle(Head head) { // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 字体策略 WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short) 12); contentWriteCellStyle.setWriteFont(contentWriteFont); contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 自动换行 contentWriteCellStyle.setWrapped(true); //设置 垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); Integer columnIndex = head.getColumnIndex(); if(2 == columnIndex || 4== columnIndex){ WriteFont writeFont = contentWriteCellStyle.getWriteFont(); writeFont.setColor(IndexedColors.RED1.getIndex()); writeFont.setBold(true); return contentWriteCellStyle; } return contentWriteCellStyle; } } 导入优化

实现界面提示输入错误项，字典自动转化。

@PostMapping("/import") public R importFile(@RequestPart("file") MultipartFile file){ try { EasyExcelListener listener = new EasyExcelListener(DzdaQbztkBcDxsVO.class); List list = EasyExcel.read(file.getInputStream(), DzdaQbztkBcDxsVO.class, listener).headRowNumber(1).sheet().doReadSync(); if (ListUtils.isNotEmpty(listener.getErrors())){ return R.failed(listener.getErrors(),"导入excel校验失败"); } return R.ok(dzdaQbztkBcDxsService.saveBatch(dzdaQbztkBcDxsUtil.convertVOList2Entiy(list))); } catch (IOException e) { e.printStackTrace(); } return R.failed(); }

EasyExcelListener 监听器，实现模板校验，数据校验。

public class EasyExcelListener extends AnalysisEventListener { private static final Logger log = LoggerFactory.getLogger(DefaultAnalysisEventListener.class); private final List list = new ArrayList(); private final List errorMessageList = new ArrayList(); private Long lineNum = 1L; @Setter private Class clazz; public EasyExcelListener(Class clazz) { this.clazz = clazz; } @Override public void invokeHeadMap(Map headMap, AnalysisContext context) { /* count 记录模板表头有几个，用以判断用户导入的表格是否和模板完全一致 如果用户导入表格较模板的表头多，但其余符合模板，这样不影响则不需要 */ int count = 0; int i = -1; // 获取数据实体的字段列表 Field[] fields =clazz.getDeclaredFields(); // 遍历字段进行判断 for (Field field : fields) { // 获取当前字段上的ExcelProperty注解信息 ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class); // 判断当前字段上是否存在ExcelProperty注解 if (fieldAnnotation != null) { ++count; i++; // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名 String headName = headMap.get(i); // 判断表头是否为空或是否和当前字段设置的表头名不相同 String s = fieldAnnotation.value()[0]; if (StringUtils.isEmpty(headName) || !headName.equals(s)) { errorMessageList.add(new ErrorMessage(lineNum, Collections.singleton("导入模板错误"))); } } } // 判断用户导入表格的标题头是否完全符合模板 if (count != headMap.size()) { errorMessageList.add(new ErrorMessage(lineNum, Collections.singleton("导入模板错误"))); } } @Override public void invoke(T t, AnalysisContext analysisContext) { lineNum++; Set violations = Validators.validate(t); if (!violations.isEmpty()) { Set messageSet = (Set)violations.stream().map(ConstraintViolation::getMessage).collect(Collectors.toSet()); this.errorMessageList.add(new ErrorMessage(this.lineNum, messageSet)); } else { this.list.add(t); } ErrorMessage vaild = ExcelValidUtil.vaild(t); if (null!=vaild){ vaild.setLineNum(this.lineNum); this.errorMessageList.add(vaild); } } public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.debug("Excel read analysed"); } public List getList() { return this.list; } public List getErrors() { return this.errorMessageList; } } 导出优化

实现前台可选导出字段，字典自动转化。

/** * 导出数据 * * @param * @return */ @GetMapping("/export") public void export(@RequestBody(required = false) DzdaQbztkBcDxsVO dzdaQbztkBcDxsVO , HttpServletResponse httpServletResponse) { List list = dzdaQbztkBcDxsUtil.convertEntityList2VO(dzdaQbztkBcDxsService.list(WrapperUtil.getQueryWrapper(dzdaQbztkBcDxsVO, DzdaQbztkBcDxs.class))); ExportEasyExcelUtil.export(httpServletResponse,"Export",null ,DzdaQbztkBcDxsVO.class,list); }

null==list集合。可传导出字段属性的list集合，这里没有传，是全部导出，ExportEasyExcelUtil里面做了判断。



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3