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()); } }