使用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);}@Datapublic 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();}@Overridepublic 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)+")";}@Overridepublic 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、结果