一种基于 Python 的 Excel 复杂处理方法：在 Python 中调用 VBA

Programming
, , ,

某些情况下，我们希望利用Python在Excel中批量且自动地调整格式。这种时候，比起查阅复杂的 win32api，直接在 Python 中向 Excel 的 VBA 宏发起调用显然是一个更加简单且直观的方法。

下文会简单介绍一下我常用的一种方法，我对于它的使用多半基于经验，而非严谨的理论，因此它可能并不是最好的一种方法，甚至都算不上一种好方法，并且在我没有试验过的硬件环境下存在出错的可能。请务必以批判的眼光阅读，如有问题可随时与我探讨。

环境

  • Windows （因为需要用到win32api）
  • Python3 （我用的是3.12，但是版本应当并不重要，只要能够安装下面的依赖）
  • Microsoft Office （在Python中调用VBA需要创建一个Excel进程，因此你的机器上一定要安装Office）

准备工作

安装 pywin32 库

pip install pypiwin32

确保在Excel中启用并信任宏

打开 Excel，在文件-选项-信任中心-信任中心设置-宏设置中，勾选信任对 VBA 工程对象模型的访问。

录制并编辑你的宏模板

如果在此之前你有VBA和宏的使用经验，那么这一部分对你来说应当小菜一碟。

首先要明白，录制宏是为了帮助我们简化宏和VBA的编写，让你省去大量查询VBA对象文档的时间，它不一定是万能的，但相当有效。

录制宏的时机也很有讲究，比如，

我需要利用程序向Sheet中填入n行，而对于这n行中的任意行N，我都希望把第N行A列的单元格格式设置为加粗居中，同时把第1行从B到E列的所有单元格的格式全部刷到第N行从B到E列的单元格上。

那么我们需要做的便是，

在Excel顶上的视图-宏的下拉框中选择录制宏，宏名随意，快捷键不设，然后点击确定开始录制。回到Sheet，选中A2单元格（或者是A3、A4、A2000，这个不重要），在开始选项卡中依次点击加粗和居中；然后选中B1到E1的单元格，在开始选项卡中点击格式刷，然后点击B2单元格将格式刷上去。最后回到视图-宏，在下拉框中停止录制。

💡 发现了吗？在录制宏的整个流程中，我们所扮演的是“指导者”的角色，我们并不需要告诉机器每一步应当具体怎么做（实际上我们也做不到），我们只需要给机器演示其中一步，之后再通过编程的手段让机器“举一反三”即可。

好，到此为止，我们已经录制了我们的第一个宏。让我们在视图-宏的下拉框中找到查看宏，选中我们的宏名，点击编辑。在这里，我们可以看到刚刚录制的宏的 VB 代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub 宏1()

'
' 宏1 宏
'

'
  Range("A2").Select
  Selection.Font.Bold = True
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  Range("B1:E1").Select
  Selection.Copy
  Range("B2").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
End Sub

对于这段代码，我们大可不必弄懂它每一步说的是啥，干了什么。我们唯一需要做的，就是把那些可变的量标记出来。

还记得我们刚刚做了什么吗？我们希望为任意的AN单元格设置加粗和居中，但在录制的时候我们仅仅为A2一个单元格设置了加粗和居中。因此，我们要把A2替换成变量AN，并在之后的编程中进一步替换。

1
2
3
4
5
6
7
8
9
10
11
12
Range("AN").Select  Selection.Font.Bold = True
With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
End With

以此类推，我们希望把B1:E1的单元格刷到任意BN:EN单元格中，那么我们应当把代码中的B2:E2标记为变量……吗？你可能已经发现了，代码中并没有出现B2:E2的字眼，只有B2出现过一次。不过不用担心，这是格式刷的特性，在这里我们也作特殊处理，只要把B2替换为变量BN即可。

1
2
3
4
5
6
Range("B1:E1").Select
Selection.Copy
Range("BN").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

最后，让我们把宏的名字从宏1改成我的宏，然后删除多余的注释行，一个 VBA 模板的雏形就此诞生！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub 我的宏()
  Range("AN").Select
  Selection.Font.Bold = True
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  Range("B1:E1").Select
  Selection.Copy
  Range("BN").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
End Sub

Python 代码部分

首先引入pywin32：

1
from win32com.client import DispatchEx

还记得我们在刚刚录制宏的最后产生了一个 VB 代码模板吗？将它复制过来，同时利用 Python 的字符串 Formatter 功能实现变量替换。

让我们创建一个根据模板生成 VB 代码的函数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def gen_script(script_id: int, row_index: int) -> str:
    return f'''
    Sub 我的宏{str(script_id)}()
        Range("A{str(row_index)}").Select
        Selection.Font.Bold = True
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("B1:E1").Select
        Selection.Copy
        Range("B{str(row_index)}").Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End Sub
    '''

我们这个函数读取了一个行索引row_index作为参数，并用其替换我们刚刚的变量AN和BN，最后以字符串的形式返回生成的VBA代码。

我想你可能注意到了，我们还有一个形参script_id，这个参数改变了我们的宏名，如果script_id取1，那么生成的宏名则为我的宏1；如果取2000，那么宏名则为我的宏2000……

理由很简单，这么做是为了保证在一个 Excel 文件中执行多个宏时不会出现冲突。就像同一命名空间下 Python 的函数名不能重复（类里面对于成员方法的重写除外）一样，同一文件下的 VBA 宏名也不能重复。

好了，最复杂的部分搞定了，让我们“八股文”式地编写 Python 代码来调用这个宏吧！

首先创建 Excel 进程，将其设置为“无头”模式：

1
2
3
client = DispatchEx('Excel.Application')
client.Visible = False
client.DisplayAlerts = False

初始化script_id，我们准备让它从1开始迭代，每次的增量为1：

1
script_id = 1

然后打开工作表、打开 Sheet、打开……

1
2
workbook = client.Workbooks.Open(os.path.abspath('MyWorkbook.xlsx'), ReadOnly=False)
worksheet = workbook.Worksheets('Sheet1')

注意：在Open时请务必使用绝对路径，使用相对路径一定会报错！

接下来我用一个简单的 for 循环，对 Sheet 的 3 到 23 行生成并执行宏：

1
2
3
4
5
for row_index in range(3, 24):
    script_module = workbook.VBProject.VBComponents.Add(1)
    script_module.CodeModule.AddFromString(gen_script(script_id, row_index))
    client.Application.Run(f'我的宏{script_id}')
    script_id += 1

最后，让我们保存并退出：

1
2
3
workbook.SaveAs(os.path.abspath('MyWorkbookNEW.xlsx'))
workbook.Close()
client.Quit()

同样，SaveAs 也要使用绝对路径。

结尾

内容到此告一段落。

我在 GitHub 上有一个应用实例，包含了本文所述的基于 Python 的 VBA 调用方法，代码可随时查看，不过使用的数据，由于涉及隐私不便公开。

一个基于 pandas 和 pywin32 的 Excel 处理实例

本作品 [页面标题] 由 创作，采用 CC BY-NC-SA 4.0