当前位置： 首页 > news >正文

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

news 2025/8/7 9:41:53

前言

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

实现步骤

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、结果


 

http://www.lryc.cn/news/478273.html

相关文章：

  • 微信小程序 高校教材征订系统
  • 从0开始的STM32 定时器（I）：聊一聊基本定时器
  • vue常见题型(1-10)
  • 【SpringBoot】使用注解进行XSS防御
  • 华为海思招聘-芯片与器件设计工程师-模拟芯片方向- 机试题-真题套题题目——共8套（每套四十题）
  • vscode 下载慢的解决方法
  • STM32ZET6-USART使用
  • es自动补全（仅供自己参考）
  • 13-综合排序：Function Score Query 优化算分
  • 鸿蒙应用App测试-专项测试（DevEco Testing）
  • RabbitMQ设置消息过期时间
  • 大数据-209 数据挖掘 机器学习理论 - 梯度下降 梯度下降算法调优
  • 粒子群优化双向深度学习！PSO-BiTCN-BiGRU-Attention多输入单输出回归预测
  • 排序算法简介
  • （没有跳过联网激活）导致使用微软账号激活电脑---修改为本地账户和英文名字
  • [论文粗读][REALM: Retrieval-Augmented Language Model Pre-Training
  • flink 内存配置（五）：网络缓存调优
  • set和map的使用
  • LCL三相并网逆变器simulink仿真+说明文档
  • 从0开始深度学习（24）——填充和步幅
  • CPU Study - Instructions Fetch
  • GJ Round (2024.9) Round 1~7
  • 【CMCL】多模态情感识别的跨模态对比学习
  • 输入/输出系统
  • asp.net+uniapp养老助餐管理系统 微信小程序
  • 部署istio应用未能产生Envoy sidecar代理
  • 使用YOLO 模型进行线程安全推理
  • ABAP 增强
  • vue使用方法创建组件
  • HTML 基础标签——链接标签 ＜a＞ 和 ＜iframe＞