记一次JAVA POI excel模板级联菜单生成过程

写程序经常会碰到一些奇葩需求，本次做产品导入模板被坑了好久，因为数据环境的不稳定，人工配置的模板数据变化模板没有及时更新，导入时的数据校验总是被测试提bug，因此才想着去做动态模板，避免以后忘记，还是记录下！话不多说上代码！

/**
     * 创建省市区级联下拉框
     *
     * @param workbook  工作簿
     * @param sheetName sheet名称
     * @param firstRow  開始行號 根据此项目，默认为2(下标0开始)
     * @param firstCol  区域中第一个单元格的列号 (下标0开始)
     * @param lastCol   区域中最后一个单元格的列号
     * @param provinces 省级下拉内容
     * @param cities  城市集合
     * @param areas  区县集合
     */
    public static void selectFormulaList(Workbook workbook, String sheetName, int firstRow, int firstCol,
                                         int lastCol, String[] provinces, Map<String, List<String>> cities, Map<String, List<String>> areas) {
        XSSFSheet sheet = (XSSFSheet) workbook.getSheet(sheetName);
        Sheet hideSheet = workbook.createSheet("area");
        int rowId = 0;
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Cell cell;
        Row row;
        for (String province : cities.keySet()) {
            row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(province);
            List<String> cityList = cities.get(province);
            if (cityList != null && cityList.size() > 0) {
                for (int i = 0; i < cityList.size(); i++) {
                    cell = row.createCell(i + 1);
                    String city = cityList.get(i);
                    cell.setCellValue(city);
                }
                // 添加名称管理器
                String range = getRange(1, rowId, cityList.size());
                Name name = workbook.createName();
                //key不可重复,将父区域名作为key
                name.setNameName(province);
                String formula = "area!" + range;
                name.setRefersToFormula(formula);
            }
        }
        for (String s : provinces) {
            List<String> cityList = cities.get(s);
            if (cityList != null && cityList.size() > 0) {
                for (String city : cityList) {
                    List<String> areaList = areas.get(city);
                    if (areaList != null && areaList.size() > 0) {
                        row = hideSheet.createRow(rowId++);
                        row.createCell(0).setCellValue(city);
                        for (int j = 0; j < areaList.size(); j++) {
                            cell = row.createCell(j + 1);
                            String area = areaList.get(j);
                            cell.setCellValue(area);
                        }
                        // 添加名称管理器
                        String range = getRange(1, rowId, areaList.size());
                        Name name = workbook.createName();
                        //key不可重复,将父区域名作为key
                        name.setNameName(city);
                        String formula = "area!" + range;
                        name.setRefersToFormula(formula);
                    }
                }
            }
        }
        XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);

        // 省规则
        DataValidationConstraint provConstraint = helper.createExplicitListConstraint(provinces);
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, 10000, firstCol, lastCol);
        DataValidation provinceDataValidation = helper.createValidation(provConstraint, provRangeAddressList);
        //验证
        provinceDataValidation.createErrorBox("error", "请选择正确的省份");
        provinceDataValidation.setShowErrorBox(true);
        sheet.addValidationData(provinceDataValidation);

        //设置有效性
        int maxRow = 10000;
        int start = 2;

        for (int i = start; i < maxRow; i++) {
            setDataValidation("K", sheet, i, firstCol + 1);
            setDataValidation("L", sheet, i, firstCol + 2);
        }
    }

计算formula，这段代码主要是计算数据验证的起始行列

/**
     *  计算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(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;
        }
    }

设置有效性，添加数据验证来源，绑定数据

/**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        //  生成下拉列表
        //  只对(x，x)单元格有效
        CellRangeAddressList regions = new CellRangeAddressList(rowNum,
                rowNum, colNum, colNum);
        //计算表格位置，表格坐标从1开始，因此行数+1得到表格坐标
        int tableRow = ++rowNum;
        String formulaString = "INDIRECT($" + offset + (tableRow) + ")";
        //  设置数据验证来源，生成下拉框内容
        DataValidationConstraint constraint = dvHelper.createFormulaListConstraint(formulaString);
        XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(constraint, regions);

        dataValidation.setEmptyCellAllowed(false);
        dataValidation.setShowErrorBox(true);
        // 设置输入信息提示信息
        dataValidation.createPromptBox("提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        sheet.addValidationData(dataValidation);
    }

自动生成模板的代码基本上也就是这个样子了，使用过程中发现在调用时，如果行数太多效率会很低，目前设置10000行的模板生成耗时在5秒左右，我觉得还是不够骚气，以后再优化吧