简单的 Excel VBA 开发工作流

引言

在大多数语言中，生成包含表格数据的 CSV/TSV 文件是一项简单的任务。然而，以编程方式生成带有格式的 Excel 文档一直很棘手。此外，我还没有找到一个清晰的 VBA for Excel 开发流程。本文档详细介绍了一种我在实现一个涉及非平凡宏开发解决方案时所采用的方法。我尝试使用源代码控制和构建系统，以便在运行时创建 Excel 文档，而不是将脚本嵌入到工作簿中。

背景

我最近的一位客户需要批量生成包含数据表、数据透视表和图表 Excel 表格。 此外，还需要一个区域供用户填写数据，这些数据与验证函数和公式相关联。 此外，内容/演示文稿需要根据目标群体进行调整。

开发目标

• 公式和 VBA 需要进行版本控制（因此可供审查）
• Excel 文档应使用以下方式构建：
  • 声明式方法，即一组简单的配置
  • 构建代理

解决方案

概述

构建脚本使用来自源代码控制的配置和 VBA，并将其与数据结合以生成 Excel 文档。

所用技术

• PowerShell（配置和构建脚本）
  • ImportExcel - PowerShell 模块，用于导入/导出 Excel 电子表格，无需 Excel

采用此流程的第一步是将所有 VBA 脚本提取并存储在源代码控制中，作为单独的文件（或者如果您选择，则作为一个整体）。 可以进一步（就像我做的那样）创建一个配置文件，其中包含要添加到工作簿的公式、颜色和其他资源。

以下是构建脚本的一个片段，它使用提供的数据创建一个 Excel 文档，并在保存之前嵌入一个 VBA 模块。

# create an Excel package
$excelPackage = $data | Export-Excel -PassThru
# create a VB project
$excelPackage.Workbook.CreateVBAProject()
# embed macros
$module = $excelPackage.Workbook.VbaProject.Modules.AddModule($moduleName)
$module.Code = (Get-Content <VBA file path>) -join "`n"
# save the Excel file
$excelPackage.SaveAs(<output file path>)

源代码：https://github.com/shristi-dev/excel-macro-demo

关注点

虽然这种方法对于任何宏都适用，但我仍然需要找到一个类似的方法来处理表单设计。 此外，将测试添加到工作流程中是理想的，而我还没有涉及这方面的内容。

ImportExcel 是一个优秀的 PowerShell 模块，并且文档完善。 我发现它易于使用，并建议访问该项目以了解有关格式和公式用法的更多详细信息。

历史

• 2020 年 6 月 29 日：初始版本