easyexcel2.2.6版本导出excel模板时，标题带下拉框及其下拉值过多不显示问题

需求背景：有一个需求要做下拉框的值有100多条，同时这个excel是一个多sheet的导入模板 直接用easyexcel 导出，会出现下拉框的值过多，导致生成出来的excel模板无法正常展示下拉功能  

使用的easyexcel版本： <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> 自定义处理器 package com.manager.utils; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import java.util.Map; /** * 解决使用 easyExcel导出模板，下拉框数据超长，导出模板后，下拉框数据不展示问题 * @author yjj * @date 2025/02/18 10:16 **/ public class EasyExcelCellWriteHandler implements SheetWriteHandler { public static final String SHEET_NAME = "下拉框隐藏表hidden"; /** * 设置阈值，避免生成的导入模板下拉值获取不到 */ private static final Integer LIMIT_NUMBER = 50; private Map<Integer, String[]> map = null; public EasyExcelCellWriteHandler(Map<Integer, String[]> map) { this.map = map; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { // 这里可以对cell进行任何操作 Sheet sheet = writeSheetHolder.getSheet(); DataValidationHelper helper = sheet.getDataValidationHelper(); Class<?> headClass = writeSheetHolder.getClazz(); // k 为存在下拉数据集的单元格下表 v为下拉数据集 map.forEach((k, v) -> { System.out.println("Key = " + k + ", Value = " + v); // 设置下拉单元格的首行 末行 首列 末列 CellRangeAddressList rangeList = new CellRangeAddressList(EasyExcelUtils.headRowNumber(headClass), 65536,k,k); // 如果下拉值总数大于50，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到 if (v.length > LIMIT_NUMBER) { //定义sheet的名称 //1.创建一个隐藏的sheet 名称为 hidden + k String sheetName = SHEET_NAME +sheet.getSheetName() + k; Workbook workbook = writeWorkbookHolder.getWorkbook(); Sheet hiddenSheet = workbook.createSheet(sheetName); for (int i = 0, length = v.length; i < length; i++) { // 开始的行数i，列数k hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]); } Name category1Name = workbook.createName(); category1Name.setNameName(sheetName); String excelLine = getExcelLine(k); // =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组 String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1); // 将刚才设置的sheet引用到你的下拉列表中 DataValidationConstraint constraint = helper.createFormulaListConstraint(refers); DataValidation dataValidation = helper.createValidation(constraint, rangeList); writeSheetHolder.getSheet().addValidationData(dataValidation); // 设置存储下拉列值得sheet为隐藏 int hiddenIndex = workbook.getSheetIndex(sheetName); if (!workbook.isSheetHidden(hiddenIndex)) { workbook.setSheetHidden(hiddenIndex, true); } } // 下拉列表约束数据 DataValidationConstraint constraint = helper.createExplicitListConstraint(v); // 设置约束 DataValidation validation = helper.createValidation(constraint, rangeList); // 阻止输入非下拉选项的值 validation.setErrorStyle(DataValidation.ErrorStyle.STOP); validation.setShowErrorBox(true); validation.setSuppressDropDownArrow(true); validation.createErrorBox("提示", "此值与单元格定义格式不一致"); // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓"); sheet.addValidationData(validation); }); } /** * 返回excel列标A-Z-AA-ZZ * * @param num 列数 * @return java.lang.String */ private String getExcelLine(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; } } 导出工具类 /** * 支持超长下拉框展示 * 下载导入模板 - 支持多sheet */ public static void writeTemplateBoxTooLong(HttpServletResponse response, ExcelModel excelModel) { ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(outputStream(excelModel.getFileName(), response)) .registerConverter(new DateConverter()) .useDefaultStyle(false) .build(); List<ExcelModel.Sheet<?>> sheets = excelModel.getSheets(); for (int i = 0; i < sheets.size(); i++) { ExcelModel.Sheet<?> sheet = sheets.get(i); WriteSheet writeSheet; if (ExtraOption.class.isAssignableFrom(sheet.getHeadClass())) { writeSheet = EasyExcel.writerSheet(i, sheet.getSheetName()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(HorizontalCellStyleStrategyFactory.optStyleStrategy()) .head(sheet.getHeadClass()) .sheetName(sheet.getSheetName()) .build(); } else { Map<Integer, String[]> map = buildExcelDropDownSetField(sheet.getHeadClass()); writeSheet = EasyExcel.writerSheet(i, sheet.getSheetName()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new EasyExcelCellWriteHandler(map)) .registerWriteHandler(new ImportTempleRowWriteHandler()) .registerWriteHandler(new ImportTempleCellWriteHandler()) .head(sheet.getHeadClass()) .sheetName(sheet.getSheetName()) .relativeHeadRowIndex(RELATIVE_HEAD_ROW_INDEX) .includeColumnFiledNames(sheet.getIncludeFiledNames()) .build(); } excelWriter.write(sheet.getData(), writeSheet); } } catch (Exception e) { Throwable cause = Throwables.getRootCause(e); log.error("下载模板失败：{}", cause.getMessage(), cause); throw new ServiceException("下载模板失败：{0}", cause.getMessage()); } finally { if (excelWriter != null) { excelWriter.finish(); } } } /** * 批量导入 - 支持多sheet */ @SuppressWarnings("rawtypes") public static Map<Class<?>, List<?>> readMultiSheet(MultipartFile file, Class<?>... classes) { Map<Class<?>, List<?>> resultMap = Maps.newHashMapWithExpectedSize(classes.length + 1); try { checkExcelFile(file); ByteArrayInputStream inputStream = deleteHiddenSheets(file); ExcelReader excelReader = EasyExcel.read(inputStream).build(); for (int i = 0; i < classes.length; i++) { Class clazz = classes[i]; SimpleAnalysisEventListener listener = SimpleAnalysisEventListener.factory(true); ReadSheet readSheet = EasyExcel.readSheet(i) .head(clazz) .registerReadListener(listener) .headRowNumber(headRowNumber(clazz)) .build(); excelReader.read(readSheet); resultMap.put(clazz, listener.getResults()); } if (resultMap.values().stream().allMatch(CollectionUtils::isEmpty)) { throw new ServiceException("请至少录入一条数据"); } } catch (Exception e) { Throwable cause = Throwables.getRootCause(e); log.error("解析异常：{}", cause.getMessage(), cause); throw new ServiceException("解析异常：{0}", cause.getMessage()); } return resultMap; } //删除导出模板时生成的隐藏sheet,避免导入时读取带隐藏sheet报错 public static ByteArrayInputStream deleteHiddenSheets(MultipartFile file){ try (InputStream inputStream = file.getInputStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); ){ Workbook workbook = new XSSFWorkbook(inputStream); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().contains(EasyExcelCellWriteHandler.SHEET_NAME)) { workbook.removeSheetAt(i); i--; // 因为删除了一个sheet，索引需要调整 } } workbook.write(outputStream); workbook.close(); return new ByteArrayInputStream(outputStream.toByteArray()); } catch (IOException e) { log.error("解析excel失败！",e); throw new ServiceException("解析失败！"); } }

原生poi参考这位大佬：解决POI的SXSSFSheet 创建excel下拉框，下拉框内容过多时不显示的问题_java poi 下拉框数据7万行,隐藏sheet方法也看不不全-CSDN博客