需求背景：有一個需求要做下拉框的值有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;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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 + kString 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，列數khiddenSheet.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博客