新建SpinnerLongHandler类 将内容拷贝过去直接调用即可
.registerWriteHandler(new SpinnerLongHandler(spinnerLongMap,excelDtoList.size()))
如：
//调用方式 Integer为所在列数 ， string[]下拉列表的string数组EasyExcel.write(response.getOutputStream(), Students.class).registerWriteHandler(new SpinnerLongHandler(spinnerLongMap,excelDtoList.size())).sheet("管理").doWrite(excelDtoList);
新建SpinnerLongHandler 继承SheetWriteHandler
public class SpinnerLongHandler implements SheetWriteHandler {/*** 下拉框内容map Integer数据所在列数，string[]下拉数据列表*/private Map<Integer, String[]> spinnerMap;/*** 导出数据的list大小*/private int dataSize;public SpinnerLongHandler(Map<Integer, String[]> spinnerMap, int dataSize) {this.spinnerMap = spinnerMap;this.dataSize = dataSize;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//获取一个workbookSheet sheet = writeSheetHolder.getSheet();//设置下拉框DataValidationHelper helper = sheet.getDataValidationHelper();//定义sheet的名称String hiddenName = "hidden";//1.创建一个隐藏的sheet 名称为 hiddenWorkbook workbook = writeWorkbookHolder.getWorkbook();Sheet hidden = workbook.createSheet(hiddenName);Name category1Name = workbook.createName();category1Name.setNameName(hiddenName);for (Map.Entry<Integer, String[]> entry : spinnerMap.entrySet()) {//下拉框的起始行,结束行,起始列,结束列CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize+500, entry.getKey(), entry.getKey());//获取excel列名String excelLine = getExcelLine(entry.getKey());//2.循环赋值String[] values = entry.getValue();for (int i = 0, length = values.length; i < length; i++) {// 3:表示你开始的行数 3表示 你开始的列数Row row = hidden.getRow(i);if (row == null) {row = hidden.createRow(i);}row.createCell(entry.getKey()).setCellValue(values[i]);}//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组String refers = "="+hiddenName + "!$"+excelLine+"$1:$"+excelLine +"$"+ (values.length);//5 将刚才设置的sheet引用到你的下拉列表中DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);DataValidation dataValidation = helper.createValidation(constraint, addressList);writeSheetHolder.getSheet().addValidationData(dataValidation);}//设置列为隐藏int hiddenIndex = workbook.getSheetIndex("hidden");if (!workbook.isSheetHidden(hiddenIndex)) {workbook.setSheetHidden(hiddenIndex, true);}}/*** @Description 返回excel列标A-Z-AA-ZZ* @Author chou* @Date /9/8* @param num 列数* @return java.lang.String*/public static 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;}}
原理: 以wps为例:
参考资料：Excel突破下拉框序列字符的限制问题这不还是之前遇到的那个Excel的限制吗，下拉框的文本长度不能超过2550604更新:修复多个下拉框出现的一些问题 (解决了评论区sheet名称重复问题)
