excel 的macro宏通过vba语言实现excel 的可编程,这是excel 强大的地方。同样,利用python的相关模块,我们可以在此基础上进一步增强 excel 的编程性,通过python 我们可以实现excel 宏的导入导出及通过python实现宏的调用。

一、简单的宏示例

打开excel 软件,在视图里打到宏功能,创建一个名为Test_Marco 的宏,其代码如下:

1Sub Test_Macro()
2 MsgBox "This is a macro in Excel"
3End Sub

上面的代码实现的功能非常简单,即弹了一个宏测试页面。

二、宏调用

1、vbs 调用宏

windows下本身可以通过vbs 脚本很容易的实现宏的调用,代码如下:

1Set oExcel = createobject("Excel.Application")
2oExcel.Visible = false
3Set oWorkbooks = oExcel.Workbooks.Open("d:/test.xlsm")
4oExcel.Run "Test_Macro"
5oWorkbooks.Close
6oExcel.Quit
7Set oWorkbooks= nothing
8Set oExcel= nothing

保存后,右键选择VBS程序打开执行和直接打开excel 后调用的结果是相同的。

2、python调用宏

python 调用宏是利用了win32com模块实现的,具体代码如下:

 1from __future__ import print_function
 2import unittest
 3import os.path
 4import win32com.client
 5class ExcelMacro(unittest.TestCase):
 6    def test_excel_macro(self):
 7        try:
 8            xlApp = win32com.client.DispatchEx('Excel.Application')
 9            xlsPath = os.path.expanduser('d:\\test.xlsm')
10            wb = xlApp.Workbooks.Open(Filename=xlsPath)
11            xlApp.Run('Test_Macro')
12            wb.Save()
13            xlApp.Quit()
14            print("Macro ran successfully!")
15        except:
16            print("Error found while running the excel macro!")
17            xlApp.Quit()
18if __name__ == "__main__":
19    unittest.main()

当然上面的unittest和future模块是非必须的。另外,宏调用还有传参的情况存在,如果出现需要传参的情况时,可以通过如下代码实现传参:

1import win32com.client
2xl=win32com.client.Dispatch("Excel.Application")
3xl.Visible = True
4Path = "C:\\Program Files\\Microsoft Office\\Office14\\XLSTART\\perso.xlsm"
5xl.Workbooks.Open(Filename=Path)
6param1 = "Jeremy"
7param2 = 3
8xl.Application.Run("Proc", param1, param2)

三、excel 中宏的导入导出

1、unzip解包获取宏文件

自excel 2007以后的带宏excel 文件是通过zip工具进行解包的,我这里以linux unzip工具为例,命令如下:

1unzip myfile.xlsm -d myfile

如上面的测试用的test.xlsm 通过unzip 解包到myfile目录时,会在myflie/xl/ 目录下发现vbaProject.bin 文件。

注:不过该方法获取的宏文件中,会仍带有原excel 文件的文件名,在执行调用宏操作时,可能会现告警

2、python 脚本实现导出宏文件

在安装完xlsxwriter模块后,其自带一个vba_extract.py 脚本,如果你找不到该脚本也没关系,因为其代码相当短,你可以在其他目录新建一个py文件,内容如下:

1#!C:\Python27\python.exe
2# EASY-INSTALL-SCRIPT: 'xlsxwriter==0.7.3','vba_extract.py'
3__requires__ = 'xlsxwriter==0.7.3'
4__import__('pkg_resources').run_script('xlsxwriter==0.7.3', 'vba_extract.py')

我这里使用的是0.7.3版本的xlswriter,后续有更新时,后面的版本号可能会不一样,具体以安装后的版本号为准 。以导出上面的test.xlsm 里的测试宏为例:

1$ vba_extract.py test.xlsm
2Extracted: vbaProject.bin

同样,剥离出了二进制的宏文件。这里导出的方法比较干净,不会保留原excel 文件句,所以在其他excel 中导入使用也不会告警,所以推荐使用该方法导出宏。

3、导入excel 宏文件

xlsxwriter 模块带有add_vba_project方法用于导入宏文件,同进也提供了set_vba_name方法用于重命名宏文件。这里使用的还是上面的导出的vbaProject.bin文件,新建一个测试的excel文件如下:

 1import xlsxwriter
 2# Note the file extension should be .xlsm.
 3workbook = xlsxwriter.Workbook('macros.xlsm')
 4worksheet = workbook.add_worksheet()
 5worksheet.set_column('A:A', 30)
 6# Add the VBA project binary.
 7workbook.add_vba_project('./vbaProject.bin')
 8# Show text for the end user.
 9worksheet.write('A3', 'Press the button to say hello.')
10# Add a button tied to a macro in the VBA project.
11worksheet.insert_button('B3', {'macro':   'say_hello',
12                               'caption': 'Press Me',
13                               'width':   80,
14                               'height':  30})
15workbook.close()

上面的示例中,创建了一个excel 文件,并在A3单元格输入了相关的内容,并在excel B3单元格定义了一个调用宏的按键 。点击该按键可以直接调用导入的宏文件。

不过遗憾的是,在xlsxwriter 模块中未找到直接调用宏的方法,如果相在导入后,直接调用,可以考虑和上面提到的win32com模块一起使用。

参考页面: xlsxwriter macros使用页