使用EasyExcel实现导出excel文件时生成多级下拉选

前言

公司有个需求本来只涉及到两个下拉选项，后面就想能不能实现多个下拉选，当然我这里说的多个下拉选是联动的，比如省、地市、区县这种。

实现步骤

1、添加EasyExcel的Maven依赖

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
 </dependency>

2、一个具有多级关联的数据项

/**
 * excel下拉框数据项
 * @author lcy
 */
@Data
public class SelectItem {

    public SelectItem(Integer columnIndex) {
        this.columnIndex = columnIndex;
    }

    /**
     * 下拉框所在列的索引，从0开始
     */
    private Integer columnIndex;

    /**
     * 下拉框的值列表
     */
    private List<DataItem> dataItems;


    /**
     * 子级对应的下拉框数据
     */
    private SelectItem subSelect;


    public  void addDataItem(String mappingKey,List<String> values){
        if (this.dataItems == null){
            this.dataItems = new ArrayList<>();
        }
        this.dataItems.add(new DataItem(mappingKey,values));
    }
    public  void addDataItem(List<String> values){
        this.addDataItem("_"+UUID.randomUUID().toString().replaceAll("-",""),values);
    }


    @Data
    public static class DataItem{

        /**
         * 关联上级的key
         */
        private String mappingKey;

        /**
         * 当前下拉框的值
         */
        private List<String> values;

        /**
         * 当前下拉框的引用，隐藏页单元格地址
         */
        private String hiddenFormulaRef;

        public DataItem(String mappingKey, List<String> values) {
            Assert.notBlank(mappingKey,"mappingKey is not blank");
            Assert.notEmpty(values,"values is not empty");
            this.mappingKey = mappingKey;
            this.values = values;
        }
    }

3、定义一个SheetWriteHandler，这是EasyExcel提供的一个组件，允许我们在sheet页生成前后做一些干预动作。

/**
 * @author lcy
 */
public class SelectWriteHandler implements SheetWriteHandler , CellWriteHandler {

    private static final int ROW_SIZE = 10000;

    private final  WriteFont redFont;

    private final  List<SelectItem> selectItems;

    private final String HIDDEN_SHEET_NAME = "hidden_sheet";

    private final Set<Integer> selectColumns = new HashSet<>();

    private boolean isLoadSelectColumns = false;

    private int rowIndex = 0;

    public SelectWriteHandler(List<SelectItem> selectItems) {
        Assert.notEmpty(selectItems, "selectItems can not be empty");
        this.selectItems = selectItems;
        redFont = getRedFont();
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet hiddenSheet = workbook.getSheet(HIDDEN_SHEET_NAME);
        if (hiddenSheet != null){
            return ;
        }
        hiddenSheet = workbook.createSheet(HIDDEN_SHEET_NAME);
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
        Sheet sheet = writeSheetHolder.getSheet();
        for (SelectItem selectItem : selectItems) {
            buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,selectItem,null);
        }
        if (!isLoadSelectColumns){
            isLoadSelectColumns = true;
        }
    }

    private void buildHiddenSheetSelectRef(Workbook workbook,Sheet sheet,Sheet hiddenSheet, SelectItem selectItem,String formulaRef ) {
        if (!isLoadSelectColumns){
            selectColumns.add(selectItem.getColumnIndex());
        }
        List<SelectItem.DataItem> dataItems = selectItem.getDataItems();
        for (SelectItem.DataItem dataItem : dataItems) {
            setDataAndName(workbook, hiddenSheet, dataItem);
        }
        // 单元格地址引用
        if (formulaRef == null || formulaRef.isEmpty()){
            formulaRef = dataItems.get(0).getHiddenFormulaRef();
        }
        // 创建检验器
        DataValidation dataValidation = getDataValidation(sheet, selectItem, formulaRef);
        sheet.addValidationData(dataValidation);
        SelectItem subSelect = selectItem.getSubSelect();
        if (subSelect != null){
            buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,subSelect,getInDirectFormulaRef(selectItem.getColumnIndex()));
        }
    }

    private  DataValidation getDataValidation(Sheet sheet, SelectItem selectItem, String formulaRef) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(formulaRef);
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,ROW_SIZE, selectItem.getColumnIndex(), selectItem.getColumnIndex());
        DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);
        dataValidation.setShowErrorBox(true);
        return dataValidation;
    }

    private void setDataAndName(Workbook workbook, Sheet hiddenSheet, SelectItem.DataItem dataItem) {
        // 构建隐藏数据
        Row row = hiddenSheet.createRow(rowIndex);
        List<String> values = dataItem.getValues();
        for (int i = 0; i < values.size(); i++) {
            row.createCell(i).setCellValue(values.get(i));
        }
        // 创建名称命名器
        Name name = workbook.createName();
        name.setNameName(dataItem.getMappingKey());
        name.setRefersToFormula(getFormulaRef(row));
        dataItem.setHiddenFormulaRef(name.getRefersToFormula());
        rowIndex++;
    }

    private String getInDirectFormulaRef(Integer columnIndex){
        CellReference slectCellReference = new CellReference(1, columnIndex);
        return  "INDIRECT("+joinFormulaRef(slectCellReference, false)+")";
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (!context.getHead()){
            Integer columnIndex = context.getColumnIndex();
            if (selectColumns.contains(columnIndex)){
                // 设置红色字体
                context.getFirstCellData().getOrCreateStyle().setWriteFont(redFont);
            }
        }
        CellWriteHandler.super.afterCellDispose(context);
    }

    private   String getFormulaRef(Row prvRow) {
        Cell startCell = prvRow.getCell(prvRow.getFirstCellNum());
        Cell endCell = prvRow.getCell(prvRow.getLastCellNum() - 1);
        return HIDDEN_SHEET_NAME + "!" + joinFormulaRef(new CellReference(startCell),true) + ":" + joinFormulaRef(new CellReference(endCell),true);
    }

    public  String joinFormulaRef(CellReference cellReference,boolean isAbsolute){
        StringBuilder sb = new StringBuilder();
        String[] refs = cellReference.getCellRefParts();
        for (int i = refs.length -1 ; i >= 1; i--) {
            if (isAbsolute){
                sb.append("$");
            }
            sb.append(refs[i]);
        }
        return sb.toString();
    }

    /**
     * 返回一个红色字体
     * @return
     */
    private WriteFont getRedFont() {
        WriteFont redFont =  new WriteFont();
        redFont.setColor(IndexedColors.RED.getIndex());
        return redFont;
    }
}

4、准备数据

       // 准备数据
        SelectItem selectItem = new SelectItem(0);
        selectItem.addDataItem(List.of("浙江省","河南省"));

        SelectItem subSelectItem = new SelectItem(1);
        subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
        subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
        selectItem.setSubSelect(subSelectItem);

        SelectItem selectItem3 = new SelectItem(2);
        selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
        selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
        selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
        selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
        selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
        subSelectItem.setSubSelect(selectItem3);

5、测试

EasyExcel.write("d:\\5555.xlsx")
                .registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
                .sheet()
                .doWrite(Collections.emptyList());

完整的测试代码

public class SelectExcelTest {


    public static void main(String[] args) {

        // 准备数据
        SelectItem selectItem = new SelectItem(0);
        selectItem.addDataItem(List.of("浙江省","河南省"));

        SelectItem subSelectItem = new SelectItem(1);
        subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
        subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
        selectItem.setSubSelect(subSelectItem);

        SelectItem selectItem3 = new SelectItem(2);
        selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
        selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
        selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
        selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
        selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
        subSelectItem.setSubSelect(selectItem3);



        EasyExcel.write("d:\\5555.xlsx")
                .registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
                .sheet()
                .doWrite(Collections.emptyList());
    }

}

6、结果