项目场景：

EasyExcel官网文档地址：

https://easyexcel./docs/current/

项目场景：实际开发中，需要用到很多下拉框去供使用者选择特定得值，但是easyExcel官方文档中提供得示例很简单，只有1个列有下拉框。在此分享一下多列下拉框得心得体会。

代码

直接上代码，许多地方 我都会有注释

import com.alibaba.excel.write.handler.SheetWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.usermodel.XSSFDataValidation;import java.util.*;@Slf4jpublic class SheetUtil implements SheetWriteHandler {/*** 设置下拉框的起始行，默认为第二行，可通过后续传参改变*/private final static int firstRow = 1;/*** 设置下拉框得结束行行，默认为最后一行*/private final static int lastRow = 0x10000;/*** Integer 是需要设置下拉框得列* List<String> 是该列下拉框得值*/private LinkedHashMap<Integer, List<String>> fieldValues;/*** 完成赋值，上面得起始列，结束列也可以如此实现*/public SheetUtil(LinkedHashMap<Integer, List<String>> fieldValues) {this.fieldValues = fieldValues;}/*** 创建sheet 操作*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//获取sheet页Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();fieldValues.forEach((k, v) -> {//设置下拉框得起始行，结束行，起始列，结束列CellRangeAddressList list = new CellRangeAddressList(firstRow, lastRow, k, k);//将数字转化为 A-Z 格式String excelLine = getExcelLinke(k);//重新定义一个隐藏得sheet名称为 xxx(自己爱取啥名就取啥名）+ kString sheetName = "xxx" + k;//创建sheet，突破下拉框255得限制Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet workbookSheet = workbook.createSheet(sheetName);for (int i = 0; i < v.size(); i++) {//row 表示开始得行数，cell表示开始得列数workbookSheet.createRow(i).createCell(k).setCellValue(v.get(i));}Name name = workbook.createName();name.setNameName(sheetName);//下拉框设置String refers = sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.size() + 1);name.setRefersToFormula(refers);//设置为隐藏int index = workbook.getSheetIndex(sheetName);if (!workbook.isSheetHidden(index)) {workbook.setSheetHidden(index, true);}DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);DataValidation dataValidation = helper.createValidation(constraint, list);//适配 office ，配置很多得话，office会把初始话表格if (dataValidation instanceof XSSFDataValidation) {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);} else {dataValidation.setSuppressDropDownArrow(false);}sheet.addValidationData(dataValidation);});log.info("sheet写入完成！");}/*** 返回excel列标A-Z-AA-ZZ*/private String getExcelLinke(int num) {String line = "";int first = num / 26;int second = num % 26;if (first > 0) {line = (char) ('A' + first - 1) + "";}line += (char) ('A' + second) + "";return line;}}

文中也参考了比较多其他博主得帖子，项目做完也找不到记录了（主要是懒）