Poi（二）- excel添加多级菜单级联候选框

最新推荐文章于 2023-10-31 09:00:14 发布

香蜜沉沉烬如霜

最新推荐文章于 2023-10-31 09:00:14 发布

阅读量676

点赞数

分类专栏： poi 文章标签： java excel poi

本文链接：https://blog.csdn.net/yiyuanyu/article/details/113545661

版权

poi 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1 级联（多级菜单）excel是怎么添加的

1 先说一下数据

第一行 parent 所有的第一层父级菜单（辽，吉，黑）
剩下的每一行，都是一个父级数据对应它所包含的子集（比如：辽{大连，沈阳}）
另外，第一列为所有有子集的父级（辽，吉，黑，大连，沈阳）
2 然后给创建名称管理器，选中公式->名称管理器，新建，名称就是每行首列的值，引用位置就选则当前行除了首列的其余列
3 新建另一个sheet页，选中某一列（比如说A ）的单元格（一级菜单），选择数据->数据验证，条件选择序列，来源输入=parent
4 选中下一列（B）的单元格（二级菜单）选择数据->数据验证，条件选择序列，来源输入=INDIRECT（A1）
5 如果多级菜单以此类推，具体可以参考如下的动图

2 java代码怎么插入级联（多级菜单）

核心代码

public static void cascade(final Workbook workbook, final List<String> mainParent, final List<String> allParent,
            final Map<String, List<String>> children,
            final ArrayList<Integer> columns, Sheet sheet, final int startRow, final int endRow) {
    
        String hideSheetName = "area";
        while (workbook.getSheet(hideSheetName) != null) {
            hideSheetName = hideSheetName + new Random().nextInt(100);
        }
        final Sheet hideSheet = workbook.createSheet(hideSheetName);
        //这一行作用是将此sheet隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存省的信息
        final String parentNameKey = "parent";
        final Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue(parentNameKey);
        for (int i = 0; i < mainParent.size(); i++) {
            final Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(mainParent.get(i));
        }
        // 添加父级别的名称管理器
        final String parentRange = EasyPoiCascadeUtil.getRange(1, rowId, mainParent.size());
        final Name parentName = workbook.createName();
        //key不可重复
        parentName.setNameName(parentNameKey);
        final String parentFormula = hideSheetName + "!" + parentRange;
        parentName.setRefersToFormula(parentFormula);

        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (final String key : allParent) {
            final String[] son = children.get(key).toArray(new String[1]);
            final Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int j = 0; j < son.length; j++) {
                final Cell cell = row.createCell(j + 1);
                cell.setCellValue(son[j]);
            }

            // 添加名称管理器
            final String range = EasyPoiCascadeUtil.getRange(1, rowId, son.length);
            final Name name = workbook.createName();
            //key不可重复
            name.setNameName(key);
            final String formula = hideSheetName + "!" + range;
            System.out.println(formula);
            name.setRefersToFormula(formula);
        }

        if (sheet.getSheetName().equals(hideSheetName)) {
            return;
        }

        final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);

        //最外层的规则 加载数据,将名称为hidden的sheet中的数据转换为List形式
        final XSSFDataValidationConstraint dvConstraint =
                (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(parentNameKey);
        // 四个参数分别是：起始行、终止行、起始列、终止列
        final CellRangeAddressList provRangeAddressList =
                new CellRangeAddressList(startRow, endRow, columns.get(0), columns.get(0));
        final XSSFDataValidation provinceDataValidation =
                (XSSFDataValidation) dvHelper.createValidation(dvConstraint, provRangeAddressList);

        //验证
        provinceDataValidation.createErrorBox("error", "请选择正确的数据");
        provinceDataValidation.setShowErrorBox(true);
        provinceDataValidation.setSuppressDropDownArrow(true);
        sheet.addValidationData(provinceDataValidation);

        for (int j = 0; j < (columns.size() - 1); j++) {
            final String column = EasyPoiCascadeUtil.getColumn(columns.get(j));
            EasyPoiCascadeUtil.setDataValidation(column, (XSSFSheet) sheet, startRow, endRow,
                    columns.get(j + 1));
        }
    }

    /**
     * 加载下拉列表内容
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(
            final String formulaString, final int firstRow, final int lastRow, final int naturalColumnIndex,
            final XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        //如果A2是江苏省，那么此处就是江苏省下的市信息。
        final XSSFDataValidationConstraint dvConstraint =
                (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        final CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, naturalColumnIndex, naturalColumnIndex);
        // 数据有效性对象
        // 绑定
        final XSSFDataValidation data_validation_list =
                (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof XSSFDataValidation) {
            data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        return data_validation_list;
    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(final String offset, final XSSFSheet sheet, final int startRow,
            final int endRow,
            final int colNum) {
        final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = EasyPoiCascadeUtil.getDataValidationByFormula(
                "INDIRECT($" + offset + (2) + ")", startRow, endRow, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    /**
     * 计算formula
     *
     * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId 第几行
     * @param colCount 一共多少列
     * @return 如果给入1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(final int offset, final int rowId, final int colCount) {
        return "$" + EasyPoiCascadeUtil.getColumn(offset) + "$" + rowId + ":$"
                + EasyPoiCascadeUtil.getColumn(((offset + colCount) - 1)) + "$" + rowId;
    }

//根据输入的数字，转换成excel中相应的列，0->A, 26->AA
    public static String getColumn(int col) {
        if (col < 0) {
            return null;
        }
        String columnStr = "";
        do {
            if (columnStr.length() > 0) {
                col--;
            }
            columnStr = ((char) ((col % 26) + 'A')) + columnStr;
            col = col / 26;
        } while (col > 0);
        return columnStr;
    }