Java POI 给excel添加级联下拉框

给自己打个广告，最近弄了个Excel的工具，支持下拉框，级联下拉框，隐藏Sheet，多线程大数据量导出，生产者消费者模式读取，代码大部分都有注释，有兴趣的朋友可以看看，觉得不错可以帮我弄个star什么的😄

实现方式

使用的excel的名称管理器(类似Map)存储级联数据的关系，创建隐藏Sheet存储级联的数据，再创建名称管理器，最后设置数据的关联

代码

会涉及到创建下拉框方法(createDropDownListWithHiddenSheet)，可以查看Java Excel POI 添加下拉框

/**
     * 添加级联下拉数据
     *
     * @param workbook            workBook
     * @param displaySheet        用于展示的Sheet
     * @param sheetName          保存级联数据的Sheet名称
     * @param parents            最顶层的父节点数组
     * @param cascadeData        key:父节点名称  value:父节点对应的下一层子节点
     * @param columnRelationships  key:父下拉框列在Sheet的下标(顶级为 -1)  value:子下拉框列在sheet的下标
     * @param maxRowNum          需要关联下拉框数据的行数
     */
    public static void createCascadeDropdownListTemp(Workbook workbook, Sheet displaySheet,
                                                     String sheetName, String[] parents,
                                                     Map<String, List<String>> cascadeData,
                                                     Map<Integer, Integer> columnRelationships, int maxRowNum) {
        // 创建隐藏Sheet
        Sheet hiddenSheet = workbook.createSheet(sheetName);
        // 设置Sheet 隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
        int rowId = 0;
        // 设置第一行，存顶级父节点的信息
        Row firstRow = hiddenSheet.createRow(rowId++);
        firstRow.createCell(0).setCellValue("父节点");
        for (int i = 0; i < parents.length; i++) {
            Cell firstRowCell = firstRow.createCell(i + 1);
            firstRowCell.setCellValue(parents[i]);
        }
        // 创建所有父节点的数组
        Set<String> parentSet = cascadeData.keySet();
        String[] allParents = new String[parentSet.size()];
        parentSet.toArray(allParents);
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (int i = 0; i < allParents.length; i++) {
            String key = allParents[i];
            List<String> son = cascadeData.get(key);
            Row row = hiddenSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int j = 0; j < son.size(); j++) {
                Cell cell = row.createCell(j + 1);
                cell.setCellValue(son.get(j));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = workbook.createName();
            //key不可重复
            name.setNameName(key);
            String formula = sheetName + "!" + range;
            name.setRefersToFormula(formula);
        }

        int firstParentIndex = columnRelationships.get(-1);
        // 创建顶级父节点的下拉框(隐藏Sheet的方式)
        createDropDownListWithHiddenSheet(displaySheet, 1, firstParentIndex, maxRowNum, firstParentIndex, parents, workbook, "hiddenParent" + sheetName);
        for (Map.Entry<Integer, Integer> entry : columnRelationships.entrySet()) {
            // 跳过顶级节点
            if (entry.getKey() == -1) {
                continue;
            }
            int index = entry.getValue();
            int parentIndex = entry.getKey();
            String parentOffset = String.valueOf((char) ((int) 'A' + parentIndex));
            // poi的index从0开始
            setDataValidation(parentOffset, displaySheet, 1, maxRowNum, index);
        }
    }

/**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    private static void setDataValidation(String offset, Sheet sheet, int rowNum, int lastRow, int colNum) {
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(rowNum, lastRow, colNum, colNum);
        DataValidation cacse = null;
        if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            String formulaStringXlsx = "INDIRECT($" + offset + (rowNum + 1) + ")";
            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaStringXlsx);
            cacse = dvHelper.createValidation(dvConstraint, rangeAddressList);
        } else {
            String formulaString = "INDIRECT($" + offset + (rowNum) + ")";
            DVConstraint formula = DVConstraint.createFormulaListConstraint(formulaString);
            cacse = new HSSFDataValidation(rangeAddressList, formula);
        }
        cacse.createErrorBox("error", "请选择正确的选项");
        sheet.addValidationData(cacse);
    }

/**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    private static 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;
        }
    }

注意

经过踩坑，由于名称管理器中是不支持各种特殊符号的(以及纯数字)，比如逗号和分号都不行，有这方面的需求的请谨慎使用