需求：导出一个带有单选下拉框与多选下拉框的excel模板

思路：1.使用VBA脚本设置带有多选框的模板2.使用EasyExcel给字段填充单选下拉框数据源

一、创建带有多选框宏的excel模板

*.xls：不管有没有宏程序的话都是xls文件 
*.xlsx文件：为无法使用宏的文件类型。
*.xlsm文件：基于XML且启用宏的Excel 2007工作簿。

*.xls是2003版本下的文件 ，不管有没有宏程序的话都是xls文件 。从2007开始做了区分，XLSM文件和XLSX文件都是excel2007及其以后 的文件，但前者是含有宏启用，Excel中默认情况下不自动启用宏，默认是XLSX。VBA中，如果不想保存代码，可以保存为xlsx，即可自动删除其中VBA代码，反之则保存为XLSM文件。

1.先创建一个*.xlsm文件，填入标题字段

我们需要在表格的__性别___（第三列）处设置__单选框__，值为：男/女
在__年级__（第四列）处设置__多选框__，值为数据库查询出的年级数据

2.设置vba脚本
视图 ——》 宏 ——》 查看宏 ——》 创建 —— 》 选中启用宏的sheet对象 ——》 查看代码

此处给出脚本宏：

Sub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,不可重复
Dim rngDV As Range
Dim oldVal As StringDim newVal As String
If Target.Count > 1 Then GoTo exitHandlerOn Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandlerIf rngDV Is Nothing Then GoTo exitHandlerIf Intersect(Target, rngDV) Is Nothing Then
'do nothingElse
Application.EnableEvents = False
newVal = Target.Value
If Target.Column = 3 Or Target.Column = 4 Then  '数字是你想要多选的列是多少，多个用or连接。 【注意：仅写数字是不行的，需要加上Target.Column = 数字】
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else '去除重复的字段If InStr(1, oldVal, newVal) <> 0 ThenIf InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)ElseTarget.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号End IfElse '不是重复选项就视同增加选项
Target.Value = oldVal _
& "," & newVal '可以是任意符号隔开
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

到此，含有启用多选框宏的excel模板就完成了。

二、EasyExcel实现单选下拉框数据源填充

使用自定义注解，标识excel对象类中需要使用下拉数据集的的字段。下拉数据集数据可以为__固定__的，或是程序中__动态获取__的。

1.创建自定义注解 @DropDownSetField

/*** 标记导出excel的下拉数据集*/@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {// 固定下拉内容String[] source() default {};// 动态下拉内容Class[] sourceClass() default {};
}

2.excel对象类中标识需要下拉数据集的字段

@Data
public class StudentExcelExportResult{@ExcelProperty(value = "姓名")private String name;@ExcelProperty(value = "学号")private String code;@ExcelProperty(value = "性别")@DropDownSetField(source = {"男","女"})		// 固定数据源private String gender;@ExcelProperty(value = "年级")@DropDownSetField(sourceClass = DropDownSetImpl.class)		// 动态数据源private String gradeList;@ExcelProperty(value = "科目")private String subject;
}

3.动态下拉数据集接口与实现类

/*** 获得数据源的接口*/public interface DropDownSetInterface {/*** 获得年级下拉框数据源* @return*/String[] getGradeListSource(Integer sectionId);}

在实现类中注入service时，碰到了注入为null的问题。加上@Component标注与@Autowired也没解决。最终查询到的解决方法如下：
（1）静态初始化当前类
（2）在初始化service的方法上加上@PostConstruct注解，使得方法在Bean初始化之后被Spring容器执行
（3）通过类来调用

/*** 获得数据源的实现方法*/@Component
public class DropDownSetImpl implements DropDownSetInterface {// 注入service@Autowiredprivate GradeService gradeService;//静态初始化当前类private static DropDownSetImpl dropDownSet;/*** 获得年级下拉框数据源* @return*/@Overridepublic String[] getGradeListSource() {//调用service时，通过类来调用List<String> gradeList = dropDownSet.gradeService.getGradeList();if (gradeList != null && gradeList .size() > 0) {// list 转 String[]return gradeList .toArray(new String[gradeList .size()]);}return null;}// 在初始化service的方法上加上@PostConstruct注解，使得方法在Bean初始化之后被Spring容器执行@PostConstructpublic void init() {dropDownSet = this;dropDownSet.gradeService = this.gradeService;}
}

4.解析下拉数据集的工具类

/*** 解析下拉数据集工具** 获得数据源*/
public class ResoveDropAnnotationUtil {// 填充年级下拉框public static String[] dynamicListResove(DropDownSetField dropDownSetField, String fieldName) 				{if (!Optional.ofNullable(dropDownSetField).isPresent()) {return null;}// 获取固定下拉信息String[] source = dropDownSetField.source();if (source.length > 0) {return source;}// 获取动态的下拉数据Class<? extends DropDownSetInterface>[] classes = dropDownSetField.sourceClass();if (classes.length > 0) {try {DropDownSetInterface dropDownSetInterface = Arrays.stream(classes).findFirst().get().newInstance();// 获得数据源，可根据filedName，判断并调用对应的方法，填充数据源String[] dynamicSource = null;if ("gradeList".equals(fieldName)) {dynamicSource = dropDownSetInterface.getGradeListSource();} if (null != dynamicSource && dynamicSource.length > 0) {return dynamicSource;}} catch (Exception e) {e.printStackTrace();}}return null;}

6.自定义EasyExcel拦截器处理

/**** easyExcel 自定义拦截器* 对第n列的,第x行至第y行数据新增下拉框**/@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {private Map<Integer, String[]> map = null;public CustomSheetWriteHandler(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();// k：存在下拉数据集的单元格下表；v：下拉数据集map.forEach((k, v) ->{//下拉列表约束数据DataValidationConstraint constraint = helper.createExplicitListConstraint(v);//设置下拉单元格的首行、末行、首列、末列CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);//设置约束DataValidation validation = helper.createValidation(constraint, rangeList);//阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "此值与单元格定义格式不一致");validation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");sheet.addValidationData(validation);});}
}

7.生成带多选下拉框的Excel模板

/*** 下载学生信息模板*/public void getStudentTemplate(HttpServletResponse response, HttpServletRequest request) throws IOException {OutputStream outputStream = response.getOutputStream();// 获得模板输入流InputStream inputStream = new ClassPathResource("template/export_template.xlsm").getInputStream();try {String fileName = "学生信息模板";String fileName3 = URLEncoder.encode(fileName, "utf-8");// 注意导出的文件格式需要是.xlsmresponse.setHeader("Content-disposition", "attachment;filename*=utf-8''"+fileName3+".xlsm");response.setContentType("application/msexcel");// 定义输出类型response.setCharacterEncoding("UTF-8");//获取该类声明的所有字段Field[] fields = StudentExcelExportResult.class.getDeclaredFields();//响应字段对应的下拉集合Map<Integer, String[]> map = new HashMap<>();Field field = null;// 循环判断哪些字段有下拉数据集，并获取for (int i = 0; i < fields.length; i++) {field = fields[i];// 解析注解信息DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);if (null != dropDownSetField) {//调用下拉框数据源的方法String[] source = ResoveDropAnnotationUtil.dynamicListResove(dropDownSetField, field.getName());if (null != source && source.length > 0) {// 记录字段位置，及数据源map.put(i, source);}}}ExcelWriter excelWriter = EasyExcel.write(outputStream).registerWriteHandler(new CustomSheetWriteHandler(map))	//使用拦截器填充数据源.withTemplate(inputStream)	// 使用模板输出.build();WriteSheet sheet = EasyExcel.writerSheet(0,"Sheet1").build();excelWriter.write(null, sheet);excelWriter.finish();outputStream.close();} catch (Exception e) {e.printStackTrace();}
}

以上就完成了含有下拉多选，以及单选的Excel表格生成。

ps：wps如果想启用宏，需要额外下载组件。

参考资料：

EasyExcel导出自定义下拉数据集的Excel模板文件 —— 搬砖盖楼.

Java Excel POI添加多选下拉框 —— Anntly.

EasyExcel · 语雀.

@Autowired注入service为null问题解决 —— 爱米酱.

EasyExcel自定义拦截器demo.