EasyExcel导出样式带下拉框(多级级联)

直接上代码

实现SheetWriteHandler 接口

@Data //lombok的注解
@AllArgsConstructor  //lombok的注解
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * Called after the sheet is created
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        selectedMap.forEach((k, v) -> {
            // 判断是否是多级
            SelectSource selectSource = v.getSelectSource();
            if (selectSource.isOneLevel()) {
                // 设置下拉列表的行： 首行，末行，首列，末列
                CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                // 设置下拉列表的值
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSelectSource().getSource());
                // 设置约束
                setValidation(sheet, helper, constraint, rangeList);
            } else {
                Map<String, List<String>> sourceMap = selectSource.getSourceMap();
                // 父类
                Sheet hideSheet = workbook.createSheet("site");
                workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
                // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
                int rowId = 0;
                Row proviRow = hideSheet.createRow(rowId++);
                proviRow.createCell(0).setCellValue("大类列表");
                int rowIndex = 0;
                for (String key : sourceMap.keySet()) {
                    Cell proviCell = proviRow.createCell(rowIndex + 1);
                    proviCell.setCellValue(key);
                    rowIndex++;
                }
                // 子类
                for (String key : sourceMap.keySet()) {
                    List<String> son = sourceMap.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 = workbook.createName();
                    name.setNameName(key);
                    String formula = "site!" + range;
                    name.setRefersToFormula(formula);
                }
                // 开始设置（大类小类）下拉框
                DataValidationHelper dvHelper = sheet.getDataValidationHelper();
                // 大类规则
                DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(sourceMap.keySet().toArray(new String[sourceMap.size()]));
                CellRangeAddressList expRangeAddressList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                setValidation(sheet, dvHelper, expConstraint, expRangeAddressList);
                // 子类
                // 小类规则(各单元格按个设置)
                // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市
                // 为了让每个单元格的公式能动态适应，使用循环挨个给公式。
                // 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联
                String letter = getIndexLetter(k);
                for (int i = 2; i < v.getLastRow(); i++) {
                    CellRangeAddressList rangeAddressList = new CellRangeAddressList(i - 1, i - 1, k + 1, k + 1);
                    DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($" + letter + "$" + i + ")");
                    setValidation(sheet, dvHelper, formula, rangeAddressList);
                }
            }
        });
    }

    /**
     * 获取索引对应的字母
     *
     * @param index
     * @return
     */
    private String getIndexLetter(int index) {
        String[] letter = {"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"};
        if (index >= letter.length) {
            int a = index / 26;
            int b = index % 26;
            return letter[a - 1] + letter[b];
        }
        return letter[index];
    }

    /**
     * 设置验证规则
     *
     * @param sheet       sheet对象
     * @param helper      验证助手
     * @param constraint  createExplicitListConstraint
     * @param addressList 验证位置对象
     */
    private void setValidation(Sheet sheet,
                               DataValidationHelper helper,
                               DataValidationConstraint constraint,
                               CellRangeAddressList addressList) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.createErrorBox("错误提示", "您输入的内容，不符合限制条件");
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * @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;
            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;
        }
    }

}

ExcelSelectedResolve类

@Data
public class ExcelSelectedResolve {

    /**
     * 下拉内容
     */
    private SelectSource selectSource;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;
}

SelectSource类 //下拉框内容

@Data
public class SelectSource {

    /**
     * 是否一级下拉框
     */
    private boolean oneLevel = false;

    /**
     * 一级下拉框数据
     */
    private String[] source;

    /**
     * 多级，目前到二级
     */
    private boolean multistage = false;

    /**
     * 二级下拉框数据
     */
    private LinkedHashMap<String, List<String>> sourceMap;

    public SelectSource(LinkedHashMap<String, List<String>> sourceMap) {
        this.sourceMap = sourceMap;
        this.multistage = true;
        this.oneLevel = false;
    }

    public SelectSource(String[] source) {
        this.source = source;
        this.multistage = false;
        this.oneLevel = true;
    }

    public SelectSource(LinkedHashMap<String, List<String>> sourceMap, boolean oneLevel, boolean multistage) {
        this.sourceMap = sourceMap;
        this.oneLevel = oneLevel;
        this.multistage = multistage;
    }
}

使用 

return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                .build();