easyExcel单一下拉框和级联下拉框

文章目录：

• 单一下拉框
• 级联下拉框

具体实现：

• 单一下拉框

public class BoolWriteHandler implements SheetWriteHandler {private List<String> dropDown;private List<Integer> indexList;public BoolWriteHandler(List<Integer> indexList,List<String> dropDown) {this.indexList = indexList;this.dropDown =dropDown;}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 定义一个map key是需要添加下拉框的列的index value是下拉框数据Map<Integer, String[]> mapDropDown = new HashMap<>(2);//下拉选项String[] downArray = dropDown.toArray(new String[dropDown.size()]);//下拉选在Excel中对应的列for (Integer index : indexList) {mapDropDown.put(index, downArray);}// 获取Sheet表Sheet st = writeSheetHolder.getSheet();//设置下拉框DataValidationHelper helper = st.getDataValidationHelper();for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {// 起始行、终止行、起始列、终止列  起始行为1即表示表头不设置CellRangeAddressList addressList = new CellRangeAddressList(1, 999, entry.getKey(), entry.getKey());// 设置下拉框数据 (设置长度为0的数组会报错，所以这里需要判断)if (entry.getValue().length > 0) {//创建显式列表约束DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());// 指定行列约束以及错误信息setValidation(st,helper,constraint,addressList,"提示","请选择下拉框中的值");}}}}

	/*** 字段校验*/private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {DataValidation dataValidation = helper.createValidation(constraint, addressList);dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);dataValidation.setShowErrorBox(true);dataValidation.setSuppressDropDownArrow(true);dataValidation.createErrorBox(msgHead, msgContext);sheet.addValidationData(dataValidation);

• 级联下拉框

• 2级级联

public class CascadeWriteHandler implements SheetWriteHandler {private static char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L','M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};private List<String> dropDown;private List<String> cityList;Map<String, List<String>> cityCountyMap;private Integer provinceIndex;private Integer cityIndex;private Integer countyIndex;public CascadeWriteHandler(List<String> dropDown, List<String> cityList, Map<String, List<String>> cityCountyMap,Integer provinceIndex,Integer cityIndex,Integer countyIndex) {this.dropDown =dropDown;this.cityList = cityList;this.cityCountyMap = cityCountyMap;this.provinceIndex = provinceIndex;this.cityIndex = cityIndex;this.countyIndex = countyIndex;}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 省份固定// 定义一个map key是需要添加下拉框的列的index value是下拉框数据Map<Integer, String[]> mapDropDown = new HashMap<>(2);//下拉选项String[] downArray = dropDown.toArray(new String[dropDown.size()]);//下拉选在Excel中对应的列mapDropDown.put(provinceIndex, downArray);// 获取Sheet表Sheet st = writeSheetHolder.getSheet();//设置下拉框DataValidationHelper helper = st.getDataValidationHelper();for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {// 起始行、终止行、起始列、终止列  起始行为1即表示表头不设置CellRangeAddressList addressList = new CellRangeAddressList(1, 999, entry.getKey(), entry.getKey());// 设置下拉框数据 (设置长度为0的数组会报错，所以这里需要判断)if (entry.getValue().length > 0) {//创建显式列表约束DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());// 指定行列约束以及错误信息setValidation(st,helper,constraint,addressList,"提示","请选择下拉框中的值",true);}}//市区与县的级联关系Sheet sheet = writeSheetHolder.getSheet();Workbook book = writeWorkbookHolder.getWorkbook();//创建隐藏的sheetSheet hideSheet = book.createSheet("site");book.setSheetHidden(book.getSheetIndex(hideSheet), true);int rowId = 0;Row cityRow = hideSheet.createRow(rowId++);cityRow.createCell(0).setCellValue("市区行");//放置市数据for (int i = 0; i < cityList.size(); i++) {Cell proviCell = cityRow.createCell(i + 1);proviCell.setCellValue(cityList.get(i));}//放置区数据Iterator<String> keyIterator = this.cityCountyMap.keySet().iterator();while (keyIterator.hasNext()) {String key = keyIterator.next();List<String> son = cityCountyMap.get(key);Row row = hideSheet.createRow(rowId++);row.createCell(0).setCellValue(key);for (int i = 0; i < son.size(); i++) {Cell cell = row.createCell(i + 1);cell.setCellValue(son.get(i));}//级联数据String range = getRange(1, rowId, son.size());Name name = book.createName();name.setNameName("_" + key);String formula = "site!" + range;name.setRefersToFormula(formula);}///开始设置下拉框DataValidationHelper dvHelper = sheet.getDataValidationHelper();// 市下拉框DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(cityList.toArray(new String[]{}));CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 999, cityIndex, cityIndex);setValidation(sheet, dvHelper, expConstraint, expRangeAddressList, "提示", "请选择下拉框中的值",true);// 区规则// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市// 为了让每个单元格的公式能动态适应，使用循环挨个给公式。// 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联for (int i = 2; i < 1000; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, countyIndex, countyIndex);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT(\"_\"&$"+getExcelColumn(countyIndex-1)+"$"+i+")");setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "请选择下拉框中的值",false);}}

代码过程说明：
1.创建一个隐藏的级联site表
想看隐藏sheet，只需true改为false

    Sheet hideSheet = book.createSheet("site");book.setSheetHidden(book.getSheetIndex(hideSheet), true);

2.创建名称管理器

• 以市为key，即为名称管理器的名称
  踩坑： 如果key为数字，则会提示名称管理器的名称不能以数字开头； 解决方案：数字前加下划线“_”
  踩坑:
  如果key为数字，则会提示名称管理器的名称不能以数字开头;
  解决方案：数字前加下划线“_”，此时"INDIRECT"函数中必须加前缀: “下划线”&，而且下划线必须用双引号！！

    String range = getRange(1, rowId, son.size());Name name = book.createName();name.setNameName("_" + key);String formula = "site!" + range;name.setRefersToFormula(formula);

3.填充级联数据

• 1000代表1000行生效级联关系，可自行修改。

    for (int i = 2; i < 1000; i++) {CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, countyIndex, countyIndex);DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT(\"_\"&$"+getExcelColumn(countyIndex-1)+"$"+i+")");setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "请选择下拉框中的值",false);}

• 工具类：
  计算区域方法：

    /*** @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列* @param rowId    第几行* @param colCount 一共多少列* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1*/public String getRange(int offset, int rowId, int colCount) {char start = (char) ('A' + offset);if (colCount <= 25) {char end = (char) (start + colCount - 1);return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;} else {char endPrefix = 'A';char endSuffix = 'A';if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）if ((colCount - 25) % 26 == 0) {// 边界值endSuffix = (char) ('A' + 25);} else {endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);}} else {// 51以上if ((colCount - 25) % 26 == 0) {endSuffix = (char) ('A' + 25);endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);} else {endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);endPrefix = (char) (endPrefix + (colCount - 25) / 26);}}return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;}}

      计算列编码：

    private static String getExcelColumn(int num) {String column = "";int len = alphabet.length - 1;int first = num / len;int second = num % len;if (num <= len) {column = alphabet[num] + "";} else {column = alphabet[first - 1] + "";if (second == 0) {column = column + alphabet[len] + "";} else {column = column + alphabet[second - 1] + "";}}return column;}

• 3级级联
  引用：https://blog.csdn.net/weixin_50087960/article/details/135975892