JAVA POI给EXCEL添加宏 java调用excel vba宏

需求：导出一个带有单选下拉框与多选下拉框的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 String

Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing

Else
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 Then
          If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复
            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
          Else
            Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号
          End If
        Else '不是重复选项就视同增加选项
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
    @Autowired
    private GradeService gradeService;
    
    //静态初始化当前类
    private static DropDownSetImpl dropDownSet;

    /**
     * 获得年级下拉框数据源
     * @return
     */
    @Override
    public 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容器执行
    @PostConstruct
    public 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;
    }

    @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();

        // 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");
       // 注意导出的文件格式需要是.xlsm
       response.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.