Java Poi实现二级级联菜单

依赖

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

        <!-- POI导入导出 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

实现类

package com.cy.store.controller;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.util.Map;
import java.util.Set;

public class Excel implements SheetWriteHandler {

    /**
     * 设置二级级联下拉框数据
     * @param wb              表格对象
     * @param typeName        要渲染的sheet名称
     * @param hiddenSheetName 数据字典sheet名称
     * @param values          级联下拉数据
     * @param fatherCol       父级下拉区域
     * @param sonCol          子级下拉区域
     */
    public   String typeName;
    public   String hiddenSheetName;
    public   Map<String, String[]> values;
    public   Integer fatherCol;
    public   Integer sonCol;



    public Excel(String typeName, String hiddenSheetName, Map<String, String[]> values, Integer fatherCol, Integer sonCol) {
       this.typeName=typeName;
       this.hiddenSheetName=hiddenSheetName;
       this.values=values;
       this.fatherCol=fatherCol;
       this.sonCol=sonCol;
    }


    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder wb, WriteSheetHolder writeSheetHolder) {

        //处理下拉数据
        if (values != null && values.size() != 0) {
            Workbook workbook = wb.getWorkbook();
            //新建一个sheet页
            Sheet areaSheet = workbook.createSheet(hiddenSheetName);
            // 获取数据起始行,从第0行开始,startRowNum=1
            int startRowNum = areaSheet.getLastRowNum() + 1;
            int endRowNum = startRowNum;
            Set<String> keySet = values.keySet();
            for (String key : keySet) {
//                -----定义一级校验-----
                Row fRow = areaSheet.createRow(endRowNum++);
                //实际A2,A3,A4...A20
                fRow.createCell(0).setCellValue(key);
//                -----定义二级校验-----
                String[] sons = values.get(key);
                for (int i = 1; i <= sons.length; i++) {
                    //实际B2...C2...K2...
                    fRow.createCell(i).setCellValue(sons[i - 1]);
                }
                // 添加名称管理器,B2~K2,以便后续INDIRECT($B2)，通过B2定位到整行数据
                String range = getRange(1, endRowNum, sons.length);
                Name name =workbook.createName();
                //key不可重复
                name.setNameName(key);
                String formula = hiddenSheetName + "!" + range;
                //添加公式
                name.setRefersToFormula(formula);
            }
//            ------二级校验结束-----
            //将数据字典sheet页隐藏掉
            workbook.setSheetHidden(workbook.getSheetIndex(areaSheet), true);

            // 设置父级下拉
            //获取新sheet页内容,实际A2~A20
            String mainFormula = hiddenSheetName + "!$A$" + ++startRowNum + ":$A$" + endRowNum;
            Sheet mainSheet = writeSheetHolder.getSheet();
            // 设置下拉列表值绑定到主sheet页具体哪个单元格起作用
            mainSheet.addValidationData(SetDataValidation(workbook, mainFormula, 1, 65535, fatherCol, fatherCol));

            // 设置子级下拉
            // 当前列为子级下拉框的内容受父级哪一列的影响  =INDIRECT($B2)
            String indirectFormula = "INDIRECT($" + decimalToTwentyHex(fatherCol + 1) + "2)";
            mainSheet.addValidationData(SetDataValidation(workbook, indirectFormula, 1, 65535, sonCol, sonCol));
        }
    }


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

    /**
     * 返回类型 DataValidation
     * @param wb         表格对象
     * @param strFormula formula
     * @param firstRow   起始行
     * @param endRow     终止行
     * @param firstCol   起始列
     * @param endCol     终止列
     * @return 返回类型 DataValidation
     */
    public static DataValidation SetDataValidation(Workbook wb, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) wb.getSheet("typelist"));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        return dvHelper.createValidation(formulaListConstraint, regions);
    }
    /**
     * 十进制转二十六进制
     */
    public static String decimalToTwentyHex(int decimalNum) {
        StringBuilder result = new StringBuilder();
        while(decimalNum > 0) {
            int remainder = decimalNum % 26;
            result.append((char)(remainder + 64));//大写A的ASCII码值为65
            decimalNum = decimalNum/26;
        }
        return result.reverse().toString();
    }

}

模板内容

实体类

@Data
@ToString(callSuper = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Postsss {
    @ExcelProperty(index = 0)
    private String postCode;
    @ExcelProperty(index = 1)
    private String postName;
}

测试

    @RequestMapping("/excel")
    public void ehs(HttpServletResponse response){
        Map<String, String[]> values=new HashMap<>();
        String[] a=new String[]{"1","2","3"};
        String[] b=new String[]{"4","5","6"};
        values.put("北京",a);
        values.put("河南",b);
        InputStream inputStream = null;
        try {
            inputStream = new ClassPathResource("excel- 
            templates/wl.xlsx").getInputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("模板导出", "UTF-8").replaceAll("\\+", 
            "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + 
            fileName + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
            List<Postsss> dataList=new ArrayList<>();
            //模板文件输入流 //样式调整  //默认第一个
            EasyExcel.write(response.getOutputStream(), Postsss.class)
                    .withTemplate(inputStream)
                    .registerWriteHandler(new Excel("Sheet","hiddenSheet",values,0,1))
                    .sheet(0)
                    .doFill(dataList);

        }catch (Exception e ){

        }
    }