导出 Excel 模板自动生成规则，避免用户来回修改

一句话总结

Excel 导出、导入时，根据注解自动添加单元格验证规则，避免用户因填写错误的枚举字段而反复修改 Excel

需求背景

对于 Java Web 项目，总是不可避免的出现 Excel 导入、导出的需求，而 Excel 导入导出时，枚举字段和枚举值的映射是非常常见的一种情况

例如：下面这张示例中的性别列

数据库表结构：

Excel 中用户需要输入：男，女，未知

常见的 Excel 框架都已经覆盖了枚举映射的功能，例如：EasyPOI

但是这种操作方式对于用户来说，并不是很方便，试想一下：假如用户在性别列输入了：男性，最终的结果一般就是程序抛出异常，用户得到提示：性别输入有误，贴心的开发者可能会加上：请输入 男 女 未知，做的更好一些的 可能在列头添加标签提示：该列仅能输入 男 女 未知，但是这种弱限制也无法从根本上解决问题

更好一点的解决方案是：利用 Excel 的数据验证功能，把单元格加上规则校验，让用户只能输入正确的枚举值，避免因一次输入错误而反复返工，浪费用户的时间和好心情

当用户输入了非枚举值之后，Excel 会提示用户输入不合规，禁止用户保存

这样的交互就能从源头保证用户输入正确的值

那这么友好的设计，在 Java 中如何能方便且可扩展性更强的实现呢？

需求实现

我这边的实现是基于 EasyPOI + 注解(EasyPOI 转换映射关系注解复用) + 反射 实现的，解决了以上需求痛点的同时，可以满足代码一处修改，多个功能都生效的目的

代码仓库

GayHub

实体类

@Excel 注解中的 replace 属性，该属性是 EasyPOI 用来做字段映射的，我这里复用他做 Excel 验证的可选项，另外一个就是 orderNum 属性，用该值来自动获取某个字段在 Excel 中的列的位置

@Data
public class Human extends BaseEntity { private Long id; @Excel(name = "姓名", orderNum = "1", width = 15)
private String name; @Excel(name = "年龄", orderNum = "2", width = 15)
private Integer age; @Excel(name = "性别", replace = {"男_1", "女_2", "未知_3"}, orderNum = "3", width = 15)
private Integer gender;
}


获取列名和列位置的映射

该类在初始化时，需要指定当前导出 Excel 对应的实体类的类类型，然后通过遍历类中字段的注解，生成字段和列排序(位置)的映射关系

public class FieldOrderMappingHelper<T> {
/**
* 支持的最大字段数
*/
private final static int MAX_LIST_SIZE = 26; public FieldOrderMappingHelper(Class<T> pojo) {
this.pojo = pojo;
initMap();
} /**
* 解析注释的 pojo 对象
*/
private Class<T> pojo; /**
* 字段和序号的映射关系
*/
private HashMap<String, Integer> fieldAndOrderMap; /**
* 功能：初始化类的字段内容，建立字段和序号以及字段和 excel 列名的映射关系
*
* @author kangshuai@gridsum.com
* @date 2021/4/9 12:06
*/
private void initMap() {
HashMap<String, Integer> fieldAndOrderMap = new HashMap<>(16);
HashSet<Integer> existOrderNumSet = new HashSet<>(16); List<FiledAndOrder> list = new ArrayList<>();
list = initList(list, pojo);
if (list.size() > MAX_LIST_SIZE) {
throw new RuntimeException(pojo.getName() + "目前最大支持 26 个字段，26+ 需要改代码");
} // 排序
list.sort(Comparator.comparing(FiledAndOrder::getOrder)); for (int i = 0; i < list.size(); i++) {
if (existOrderNumSet.contains(list.get(i).getOrder())) {
throw new RuntimeException(pojo.getName() + "类内部或与父类字段中存在重复的 excel 排序，请修改");
}
existOrderNumSet.add(list.get(i).getOrder());
fieldAndOrderMap.put(list.get(i).getFiledName(), i);
}
this.fieldAndOrderMap = fieldAndOrderMap;
} /**
* 功能：初始化类的字段信息，转换成 ArrayList
*
* @return java.util.List<com.gridsum.ad.ooh.project.entity.FiledAndOrder>
* @author kangshuai@gridsum.com
* @date 2021/4/9 12:09
*/
private List<FiledAndOrder> initList(List<FiledAndOrder> list, Class<?> pojoClass) {
if (Object.class.equals(pojoClass)) {
return list;
}
Field[] fields = pojoClass.getDeclaredFields();
for (Field f : fields) {
// 找到所有加了 Excel 注解的字段
Excel annotation = f.getAnnotation(Excel.class);
if (annotation == null) {
continue;
}
// 过滤隐藏行
if (annotation.isColumnHidden()) {
continue;
}
FiledAndOrder filedAndOrder = new FiledAndOrder(f.getName(), Integer.parseInt(annotation.orderNum()));
list.add(filedAndOrder);
}
// 递归查找父类
Class<?> superclass = pojoClass.getSuperclass();
return initList(list, superclass);
} public HashMap<String, Integer> getFieldAndOrderMap() {
return fieldAndOrderMap;
}
}


设置验证规则

setValidation 方法有两个参数，第一个是导出 Excel 对应的实体类的类类型，第二个是 FieldOrderMappingHelper.getFieldAndOrderMap() 获取到的列名和排序映射，该类通过反射字段上的注解，自动为生成的 workbook 添加验证规则

public class ExcelStyleHelper { /**
* 添加列值验证的最小行
*/
public static final int EXCEL_VALID_ROW_MIN = 1;
/**
* 添加列值验证的最大行
*/
public static final int EXCEL_VALID_ROW_MAX = (2 << 15) - 1;
/**
* Excel 对象
*/
private Workbook workbook;
/**
* Sheet 页，默认取第一个 sheet 页
*/
private Sheet sheet; public ExcelStyleHelper(Workbook workbook) {
this.workbook = workbook;
this.sheet = workbook.getSheetAt(0);
} /**
* 功能：单元格添加下拉框，仅支持 xls
*
* @author kangshuai@gridsum.com
* @date 2021/4/8 18:55
*/
public void setValidation(Class<?> pojoClass, HashMap<String, Integer> map) {
// 递归到 Object 就停下
if (Object.class.equals(pojoClass)) {
return;
}
// 获取所有的字段
Field[] fields = pojoClass.getDeclaredFields();
for (Field field : fields) {
Excel annotation = field.getAnnotation(Excel.class);
if (annotation == null) {
continue;
}
String[] replace = annotation.replace();
if (replace.length == 0) {
continue;
}
String[] textList = new String[replace.length];
for (int i = 0; i < replace.length; i++) {
textList[i] = replace[i].split("_")[0];
}
// 根据字段名获取他在 excel 中的列数（结合 excel 注解中的排序）
Integer col = map.get(field.getName());
setValid(textList, col, col);
}
// 递归父类的注解
Class<?> superclass = pojoClass.getSuperclass();
setValidation(superclass, map);
} /**
* 功能：设置验证区间
*
* @author kangshuai@gridsum.com
* @date 2021/4/9 15:11
*/
private void setValid(String[] textList, int firstCol, int endCol) {
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(EXCEL_VALID_ROW_MIN, EXCEL_VALID_ROW_MAX, firstCol, endCol);
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textList);
// 数据有效性对象
HSSFDataValidation dataList = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataList);
}
}


示例导出代码

控制层代码如下

@RestController
public class ExcelExportController { @GetMapping("excel")
public void excelExport(HttpServletResponse response) throws Exception {
List<Human> humanList = new ArrayList<>(); doWriteListToResponse(humanList, Human.class, response, "测试 Sheet", "测试 Excel.xls");
} /**
* 功能：将结果写入输出流
*
* @author kangshuai@gridsum.com
* @date 2021/4/14 14:46
*/
public <T> void doWriteListToResponse(List<T> list, Class<T> exportType, HttpServletResponse response, String sheetName, String excelName) throws IOException {
ExportParams ex = new ExportParams(null, sheetName, ExcelType.HSSF);
// 创建导出对象
Workbook workbook = ExcelExportUtil.exportExcel(ex, exportType, list);
// 初始化工具类
HashMap<String, Integer> map = new FieldOrderMappingHelper<>(exportType).getFieldAndOrderMap();
ExcelStyleHelper styleHelper = new ExcelStyleHelper(workbook);
// 添加规则
styleHelper.setValidation(exportType, map);
// 写入输出流，忽略此处硬编码
response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
workbook.write(response.getOutputStream());
}
}