阿里easyExcel -- excel单元格自定义下拉选择(升级版)

背景

很久很久以前写了一篇类似的文章 阿里easyExcel – excel下载/导出/读取 (单元格自定义下拉选择、不支持图片) ，用了没多久就发现不好用，限制太多（以后遇到你就知道了），然后就有了现在迟到很久的文章，主要懒得写文章。

必看

此篇文章的单元格下拉支持 1级，2级，多级联动下拉等 ，比较复杂，需耐心看一下。
再写之前，先讲几个excel注意的点：

你的excel必须支持创建 名称管理器 ，如下图所示：
excel必须支持 INDIRECT ，CONCATENATE 和 VLOOKUP 函数，要是excel版本太太太太低，可能没有这些函数，检查方式如下，一般=后面加函数名就会有提示：

缺点或限制

当下拉的数据太多时，会导致创建excel的速度变慢
下拉的总数据不能超过 1048576 行，不能超过16384列

先简单看下效果

在这里插入图片描述

下拉的原理，怎么实现的下拉

创建一个sheet，然后把下拉数据放接某一列，如下：
在这里插入图片描述
当然也可以横着放，但是横着最多只能放 16384 列，而竖着可以放 1048576 行。
如果 只有1级下拉 那么不需要创建 名称管理器 ，如果是多级联动下拉，则除了最后1级，其余都要设置 名称管理器 ， 名称管理器 怎么设置下拉自己去百度。下面直接放代码，看不懂慢慢看，不想看直接复制使用，不想讲解了哈哈哈。

另外，由于 名称管理器 对name的设置要求很高，有些字符无法设置，为了实现下拉数据的千奇百怪，所以需要做一层转换。下面展示代码。

代码

ExcelUtils 工具：

java">public class ExcelUtils {/*** 下载** @param writeHandlers 处理器（可自定义，可为null）* @param os            输出流* @param clazz         操作对象字节* @param data          数据* @param sheetName     表名*/public static <T> void downLoad(List<WriteHandler> writeHandlers, OutputStream os, Class<T> clazz, List<T> data, String sheetName) {ExcelWriterSheetBuilder builder = EasyExcelFactory.write(os, clazz).sheet(sheetName);if (!CollectionUtils.isEmpty(writeHandlers)) {writeHandlers.forEach(builder::registerWriteHandler);}builder.doWrite(data);}
}

ExcelLinkageDropdown 多级下拉数据：

java">public class ExcelLinkageDropdown {/*** 是否允许设置其他的值。false：只能是下拉列表的值；true：允许列表之外的值*/private boolean isAllowOtherValue = false;/*** 表头名称（为bean对象时传字段名称，为map时且多个头用json：["头1","头2"]）*/private String fieldName;/*** 第几列，为对象自动计算*/private Integer cellIndex;/*** 下拉内容,<上级，下级列表>，第一级的key为null，只有一级时key也为null*/private Map<String, List<String>> value = new HashMap<>();/*** 提示信息*/private String message = "只能选择列表中的值!!!";//...get set 自己生成
}

DropdownWriteHandler下拉处理器：

java">/*** 下拉处理器：单元格下拉列表格式* 最大行：1048576 / 65536* 最大列：16384 / 256*/
public class DropdownWriteHandler extends AbstractVerticalCellStyleStrategy implements SheetWriteHandler {private final Map<ExcelLinkageDropdown[], String> dropdowns = new HashMap<>(); //所有下拉值private final Class<?> clazz; //操作的类private final int headMax; //表头行数public DropdownWriteHandler(Class<?> clazz) {this.clazz = clazz;Field[] fields = clazz.getDeclaredFields();// 取表头行数this.headMax = Arrays.stream(fields).filter(field -> field.isAnnotationPresent(ExcelProperty.class)).map(field -> field.getAnnotation(ExcelProperty.class).value().length).reduce(Integer::max).orElse(0);}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook book = writeWorkbookHolder.getCachedWorkbook();Sheet sheet = writeSheetHolder.getSheet();// 设置固定区域sheet.createFreezePane(0, headMax, 0, headMax);DataValidationHelper helper = sheet.getDataValidationHelper();// 联动下拉校验if (!CollectionUtils.isEmpty(dropdowns)) {long l = System.currentTimeMillis();for (Map.Entry<ExcelLinkageDropdown[], String> dropdownMap : dropdowns.entrySet()) {ExcelLinkageDropdown[] dropdown = dropdownMap.getKey();String key = StringUtils.isNotBlank(dropdownMap.getValue()) ? dropdownMap.getValue() :"t_" + Arrays.stream(dropdown).map(t -> t.getCellIndex().toString()).collect(Collectors.joining("_"));if (key.length() > 30) {key = "s_" + IdGenerator.getInstance().getId();}//设置下拉及校验数据setDropdownsAndValidationData(book, sheet, helper, dropdown, key);}long l2 = System.currentTimeMillis();System.out.println("下拉耗时" + (l2 - l));}}/*** 设置下拉及校验数据*/private void setDropdownsAndValidationData(Workbook book, Sheet sheet, DataValidationHelper helper, ExcelLinkageDropdown[] dropdown, String key) {// 设置多级下拉if (book.getSheetIndex(key) == -1) {buildDropdownSheet(book, dropdown, key);}//设置一级下拉List<String> val = dropdown[0].getValue().get(null);if (Objects.nonNull(val) && !val.isEmpty()) {String ss = ExcelTools.getRangeByCel(2, 2, val.size()); // A和B被占用，从C开始dropdownValidationData(String.format("='%s'!%s", key, ss), helper, this.headMax, 1000000, dropdown[0], sheet);}//二级及之后的下拉for (int i = 1; i < dropdown.length; i++) {String ci = CellReference.convertNumToColString(dropdown[i - 1].getCellIndex());String format = "INDIRECT(CONCATENATE(\"_\",VLOOKUP($" + ci + (this.headMax + 1) + "," + key + "!A:B,2,0),\"_\",\"" + key + "\"))"; // A:B写死dropdownValidationData(format, helper, this.headMax, 1000001, dropdown[i], sheet);}}/*** 验证下拉数据** @param formula   公式* @param firstRow  第一行* @param lastRow   最后一行* @param dropdown1 下拉数据* @param sheet     表*/private static void dropdownValidationData(String formula, DataValidationHelper helper, int firstRow, int lastRow, ExcelLinkageDropdown dropdown1, Sheet sheet) {DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);DataValidation dataValidation = helper.createValidation(constraint, new CellRangeAddressList(firstRow, lastRow, dropdown1.getCellIndex(), dropdown1.getCellIndex()));dataValidation.setSuppressDropDownArrow(false);if (dataValidation instanceof XSSFDataValidation) {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(!dropdown1.isAllowOtherValue());  // 输入无效值时是否显示错误框dataValidation.setShowPromptBox(!dropdown1.isAllowOtherValue());  // 设置无效值时 是否弹出提示框dataValidation.createPromptBox("温馨提示", dropdown1.getMessage());   // 设置无效值时的提示框内容dataValidation.createErrorBox("温馨提示", dropdown1.getMessage());   // 设置无效值时的提示框内容}sheet.addValidationData(dataValidation);}/*** 设置单级或多级联动下拉，按顺序（1级，2级，3级...），否则将出错* 调用多次将设置多个多级联动* 单级或1级的key为null** @param head clazz为map时需要传头，否则传null*/public void setLinkageDropdown(List<List<String>> head, String excelName, ExcelLinkageDropdown... dropdowns) throws Exception {if (Objects.isNull(dropdowns) || dropdowns.length == 0) {throw new Exception("至少设置一个下拉参数");}boolean isMap = this.clazz.isAssignableFrom(Map.class);if (isMap && CollectionUtils.isEmpty(head)) {throw new Exception("head参数不能为空");}if (StringUtils.isNotBlank(excelName) && excelName.length() > 30) {throw new Exception("excelName长度不能超过30");}if (isMap) {for (ExcelLinkageDropdown dropdown : dropdowns) {if (dropdown.getValue().isEmpty()) {continue;}List<String> heads = head.stream().map(t -> String.join(",", t)).collect(Collectors.toList());int i = heads.indexOf(dropdown.getFieldName());dropdown.setCellIndex(i);}this.dropdowns.put(dropdowns, excelName);return;}List<ExcelLinkageDropdown> ds = new ArrayList<>();for (ExcelLinkageDropdown dropdown : dropdowns) {if (dropdown.getValue().isEmpty()) {continue;}Field field;try {field = this.clazz.getDeclaredField(dropdown.getFieldName());} catch (Exception e) {throw new Exception("填写的字段不存在：" + dropdown.getFieldName() + "，" + e.getMessage());}int index = field.getAnnotation(ExcelProperty.class).index(); // 获取头的位置if (index == -1) {for (Field f : clazz.getDeclaredFields()) {if (!f.isAnnotationPresent(ExcelProperty.class)) {continue;}index++;if (f.getName().equals(dropdown.getFieldName())) {break;}}}dropdown.setCellIndex(index);ds.add(dropdown);}this.dropdowns.put(ds.toArray(new ExcelLinkageDropdown[0]), excelName);}/*** 设置单级或多级联动下拉，按顺序（1级，2级，3级...），否则将出错* 调用多次将设置多个多级联动* 单级或1级的key为null** @param head clazz为map时需要传头，否则传null*/public void setLinkageDropdown(List<List<String>> head, ExcelLinkageDropdown... dropdowns) throws Exception {this.setLinkageDropdown(head, null, dropdowns);}/*** 构建下拉列表sheet页，用于下拉框展示的数据源** @param book 工作簿*/private void buildDropdownSheet(Workbook book, ExcelLinkageDropdown[] dropdowns, String hiddenArea) {//创建新的隐藏表Sheet hideSheet = createNewHideSheet(book, hiddenArea);// 设置map的key格式化, 设置一级下拉this.setKeyFormat(dropdowns, hideSheet);// 第4列开始将具体的数据写入到每一列中int col = 3;int startRow = 1; //开始行CellStyle cellStyle = this.getFixRed(book);for (int i = 1; i < dropdowns.length; i++) {ExcelLinkageDropdown dropdown = dropdowns[i];for (Map.Entry<String, List<String>> entry : dropdown.getValue().entrySet()) {int rows = entry.getValue().size();//起始行如果超出最大行数，则新增一列，起始行重新计算，当前列总行数重新计算；if (startRow >= 1048576 || startRow + rows >= 1048576) {startRow = 1;col++;}//当前列超出最大列数，报错if (col > 16384) {throw new RuntimeException("当前列超出最大列数");}//设置下拉值和名称管理器this.setNameName(book, hideSheet, hiddenArea, col, startRow, cellStyle, entry, rows);//重新计算起始行startRow = startRow + rows + 2;}}}/*** 设置下拉值和名称管理器*/private void setNameName(Workbook book, Sheet hideSheet, String hiddenArea, int col, int startRow, CellStyle cellStyle, Map.Entry<String, List<String>> entry, int rows) {Row row0 = Objects.isNull(hideSheet.getRow(startRow - 1)) ? hideSheet.createRow(startRow - 1) : hideSheet.getRow(startRow - 1);Cell cell = row0.createCell(col);cell.setCellValue(ExcelTools.replaceAscii(entry.getKey()));cell.setCellStyle(cellStyle); //设置样式，区分下拉值for (int j = 0; j < rows; j++) {int r = j + startRow;Row row = Objects.isNull(hideSheet.getRow(r)) ? hideSheet.createRow(r) : hideSheet.getRow(r);row.createCell(col).setCellValue(entry.getValue().get(j));}// 添加名称管理器String range = ExcelTools.getRangeByCel(col, startRow + 1, rows);String nameName = "_" + ExcelTools.replaceAscii(entry.getKey()) + "_" + hiddenArea;Name name = book.createName();name.setNameName(nameName); // key不可重复String formula = hiddenArea + "!" + range;name.setRefersToFormula(formula);}/*** 设置map的key格式化*/private void setKeyFormat(ExcelLinkageDropdown[] dropdowns, Sheet hideSheet) {// 第1-2列设置匹配表int rowId = 0;  // 设置区域的头行for (int i = 1; i < dropdowns.length; i++) {ExcelLinkageDropdown d = dropdowns[i];for (Map.Entry<String, List<String>> kv : d.getValue().entrySet()) {// 原始key-第一列Row row0 = hideSheet.createRow(rowId);row0.createCell(0).setCellValue(kv.getKey()); // 第一列row0.createCell(1).setCellValue(ExcelTools.replaceAscii(kv.getKey())); // 处理后的key-第二列rowId++;}}// 设置第1级List<String> dropdownVal = dropdowns[0].getValue().get(null); // 得到第一级Row r1 = Objects.isNull(hideSheet.getRow(0)) ? hideSheet.createRow(0) : hideSheet.getRow(0);r1.createCell(2).setCellValue(dropdowns[0].getFieldName());for (int i = 0; i < dropdownVal.size(); i++) {Row r1_ = Objects.isNull(hideSheet.getRow(i + 1)) ? hideSheet.createRow(i + 1) : hideSheet.getRow(i + 1);r1_.createCell(2).setCellValue(dropdownVal.get(i));}}/*** 创建新的隐藏表*/private Sheet createNewHideSheet(Workbook book, String hiddenArea) {// 创建一个专门用来存放下拉的隐藏sheet页Sheet hideSheet = book.createSheet(hiddenArea);// 这一行作用是将此sheet隐藏book.setSheetHidden(book.getSheetIndex(hideSheet), true);//如果是SXSSFWorkbook类型，转XSSFWorkbook类型，否则sheet.getRow()可能为空if (book instanceof SXSSFWorkbook) {SXSSFWorkbook sxssfWorkbook = (SXSSFWorkbook) book;hideSheet = sxssfWorkbook.getXSSFWorkbook().getSheetAt(book.getSheetIndex(hideSheet));}return hideSheet;}/*** 红色固定样式*/private CellStyle getFixRed(Workbook book) {CellStyle style = book.createCellStyle();Font font = book.createFont();font.setBold(true);font.setColor(IndexedColors.RED.getIndex());style.setFont(font);return style;}
}

工具：

java">public class ExcelTools {/*** 计算formula：纵向** @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列* @param rowId    第几行开始* @param rowCount 一共多少行* @return 如果给入参 0,2,10. 表示从A2-A11。最终返回 $A$2:$A$11*/public static String getRangeByCel(int offset, int rowId, int rowCount) {String columnLetter1 = CellReference.convertNumToColString(offset);return String.format("$%s$%s:$%s$%s", columnLetter1, rowId, columnLetter1, rowId + rowCount - 1);}/*** 把非（中文、英文、下划线、点）替换为 ascii码，因为excel不支持其他字符设置名称管理器* 如：”审核订单(一级)(1)“ 替换为 ”审核订单.40.一级.41..40.1.41.“*/public static String replaceAscii(String str) {if (StringUtils.isBlank(str)) {return str;}StringBuilder sb = new StringBuilder();for (String s : str.split("")) {if (s.matches("[^\\d\\u4e00-\\u9fa5.a-zA-Z_]")) {s = "." + (int) s.charAt(0) + ".";}sb.append(s);}return sb.toString();}
}

测试

java">@HeadStyle(fillForegroundColor = 1)
public class User implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = {"一级"})private String borth;@ExcelProperty(value = {"二级"})@HeadStyle(fillForegroundColor = 29)private String name;@ExcelProperty(value = {"三级"})private String url;@ExcelProperty(value = {"性别"})private String sex;// 日期校验@ExcelProperty(value = "图片")private WriteCellData<Void> imgs;// 日期校验@ExcelProperty(value = "字节图片")private byte[] byteArray;
}

java">	//测试excel多级联动下拉@GetMapping("/test4")public void test4(HttpServletResponse response, int n) throws Exception {long l = System.currentTimeMillis();Map<String, List<String>> m1 = new HashMap<>();m1.put(null, new ArrayList<>());for (int i = 0; i < n; i++) {m1.get(null).add("A" + (i == 0 ? "" : i));m1.get(null).add("AA" + (i == 0 ? "" : i));m1.get(null).add("AAA" + (i == 0 ? "" : i));m1.get(null).add("AAAA" + (i == 0 ? "" : i));}Map<String, List<String>> m2 = new HashMap<>();for (String s : m1.get(null)) {List<String> a = new ArrayList<>();for (int i = 1; i < 11; i++) {a.add(s + "-" + i + "B");}m2.put(s, a);}List<String> v2 = m2.values().stream().flatMap(List::stream).collect(Collectors.toList());Map<String, List<String>> m3 = new HashMap<>();for (String s : v2) {int t = 50001;List<String> a = new ArrayList<>();for (int i = 1; i < t; i++) {a.add(s + "-" + i + "C");if (i > 1) {t = 1;}}m3.put(s, a);System.out.println(s);}DropdownWriteHandler handler = new DropdownWriteHandler(User.class);ExcelLinkageDropdown dropdown11 = new ExcelLinkageDropdown();dropdown11.setFieldName("borth");dropdown11.setValue(m1);ExcelLinkageDropdown dropdown111 = new ExcelLinkageDropdown();dropdown111.setFieldName("name");dropdown111.setValue(m2);ExcelLinkageDropdown dropdown1111 = new ExcelLinkageDropdown();dropdown1111.setFieldName("url");dropdown1111.setValue(m3);Map<String, List<String>> sexMap = new HashMap<>();sexMap.put(null, m3.values().stream().flatMap(List::stream).collect(Collectors.toList()));ExcelLinkageDropdown sex = new ExcelLinkageDropdown();sex.setFieldName("sex");sex.setValue(sexMap);long l2 = System.currentTimeMillis();System.out.println("设置耗时" + (l2 - l));handler.setLinkageDropdown(null, dropdown11, dropdown111, dropdown1111);handler.setLinkageDropdown(null, sex);long l3 = System.currentTimeMillis();System.out.println("设置耗时" + (l3 - l2));ExcelUtils.downLoad(Lists.newArrayList(handler), ExcelTools.getOutputStream("测试excel多级联动下拉", response), User.class, null, "测试excel多级联动下拉");}