|
python是当下非常流行的编程语言，即使不是专业的程序猿，也可以上手学上一点，在自动化办公方面，有不少可以发挥的地方，今天介绍一个python工具包xlwings，它是非常好的自动化处理Excel的工具。 要使用python，我们首先要有一个python环境。关于python环境的安装，这方面的教程很多，不在本篇范围，有兴趣的朋友可以自行百度。 掌握xwlings的使用，需要具备的知识包括： 1、python语言基础，掌握一点pandas 2、熟悉Excel对象模型，了解VBA 一、安装xlwings 在本地电脑安装过python环境以后，我们就可以打开cmd，输入命令行安装xlwings工具包了。
pip install xlwings
此时会同时安装pywin32依赖项： 
如果只是想通过xlwings操作Excel，可以直接使用如vscode、pycharm，甚至是记事本编辑好需要的代码后，像执行一般py文件一样直接运行。
如果想要在Excel中调用并执行python代码，可选择补充安装xlwings addin。
二、在Excel中调用python代码# 在cmd中使用命令行安装addin
xlwings addin install
安装后addin后，会出现三个主要特征： 重新打开excel文件，会出现xlwings选项卡。
在VBE环境里，会出现xlwings工程加载项，加载项有加密，默认密码为：xlwings
在VBE的添加引用里，会出现xlwings。
[ 注：从技术上讲，当执行"xlwings addin install"时，插件会从Python的安装目录复制到Excel的XLSTART文件夹中。] 
第1种方式，通过Run main按钮调用python代码：| 选项卡功能 | 用法 |
|---|
| Interpreter | python解释器路径 | | PYTHONPATH | 目标py文件的路径，可以是文件全路径或是文件所在目录 (为空则在当前工作簿所在目录寻找py文件) | | Run main | 运行同名py文件中的main函数，py路径由PYTHONPATH指明 |
示例： 在xlwings文件夹下有工作簿名称为：test.xlsx 和 test.py（两个文件名字相同）。在test.py中我们写入代码： import xlwings as xw
# 代码功能是将活动工作簿的第1个工作表中的单元格A1，赋值"Hello world"
def main():
wb = xw.Book.caller()
#print(wb.name)
wb.sheets[0].range("A1").value = "Hello world"
然后点击"Rnu main"，效果如下： 
第2种方式，在VBA代码中使用"RunPython"调用python代码，并通过VBE的运行宏按钮运行：优点：可以调用python，且不限制py文件的名称和py函数的名称。因此适合使用VBA调用多个不同python方法，从而根据不同情景，可执行多个不同任务。 缺点：因为是通过VBA代码调用python，因此需要启用宏。而vba代码要保存，也需要Excel是可保存代码的宏文件，如 *.xlsm。 缺点：因为要使用RunPython，因此要添加xlwings引用。
解释: RunPython为xlwings.xlsm中的函数名，内容可见xlwings.xlsm的main模块。
示例 xlwings文件夹下有工作簿名称为：test.xlsm 和 hello.py。hello.py中写入代码： # hello.py
import numpy as np
import xlwings as xw
def world():
wb = xw.Book.caller()
wb.sheets[0].range("a1").value="在VBA中调用Python"
vba 模块中写入代码： Sub HelloWorld()
RunPython "import hello;hello.world()"
End Sub
与第1种方式不同，py文件和工作簿的名称不相同，py文件内的函数名称也不再是"main"。最后通过VBE运行HelloWorld宏： 
三、快速创建项目的方法使用命令行：xlwings quickstart myproject
上述命令，自动创建一个名为“myproject”的文件夹，且在文件夹下自动创收两个文件: myproject.py 和 myproject.xlsm。 如果需要在指定目录创建工程，可先在CMD中先进入对应目录，然后执行命令行： D:\> xlwings quickstart myproject
# 在D盘下创建myproject文件夹，并自动创建两个文件。
其中xlsm文件会自动添加xlwings引用，py文件中会有几行示例代码。 
四、使用自定义函数(User Defined Functions (UDFs))自定义函数只能在windows系统中使用 使用自定义函数的工作簿必须为可运行宏的工作簿，如xlsm 需在宏设置中开启“信任对VBA工程对象模型的访问”
自定义有参数和返回值的普通函数
为了考虑适用性，将含有自定义函数的py文件和工作簿置放在不同目录下，且py文件的名称也和工作簿名称不同。（py文件和工作簿放在同一目录下且名称相同也可以省去配置环节） 第一步：配置准备| 配置 | 说明 |
|---|
| PYTHONPATH | 如果py文件和工作簿不在同一目录，则需在此处指明py文件所在目录 | | UDF MODULES | 如果py文件和工作簿名称不同，则需指明py文件名称(可以没有后缀名) |
第二步：在py文件中写入代码：# hello.py
import xlwings as xw
@xw.func
def double_sum(x, y):
""" 返回两个数字和值的2倍 """
return 2 * (x + y)
# 此时用到装饰器 @xw.func
# 三个引号中的内容为函数的描述
第三步：点击Import Functions按钮
第四步：在工作表中以普通工作表函数的方式使用
第五步：如果在py文件中，修改了函数名，或者参数，或者增加了新的自定义函数，则需要再运行一次Import Functions，以获得更新。# hello.py
import xlwings as xw
@xw.func
def double_sum(x, y):
"""返回两个数字和值的2倍"""
return 2 * (x + y)
@xw.func
def three_sum(x, y):
"""返回两个数字和值的3倍"""
return 2 * (x + y)
# 添加了新函数，此时需再导入一次，以获得更新。
自定义有参数和返回值的数组函数
示例1、最简单的定义方式：@xw.func
def add_one(data):
""" 使用源单元格的值，并+1后返回 """
return [[cell + 1 for cell in row] for row in data]
定义的定法和普通函数一样使用装饰器即可，使用方式也和一般数组函数一样遵守三键原则：ctrl+shfit+enter。 示例2、限制数组函数必须是二维的 在示例1中，add_one的参数必须接收一个二维的数据，如果输入的参数只有1个单元格，就会抛出异常：
此时可对函数的参数做修改，强制Excel总是给出一个二维的数组： @xw.func
@xw.arg('data', ndim=2)
def add_one(data):
return [[cell + 1 for cell in row] for row in data]
此时再使用函数，且只输入一个单元格，也可以正常运行：
示例3、让数组函数使用numpy做计算：# hello.py
import xlwings as xw
import numpy as np
@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
return x @ y
示例4、让数组函数使用DataFrame做计算，且在需要返回值时取消它的索引：# hello.py
import xlwings as xw
import numpy as np
import pandas as pd
@xw.func
@xw.arg('x',pd.DataFrame,index=False,header=False)
@xw.arg('y',pd.DataFrame,index=False,header=False)
@xw.ret(index=False,header=False)
def poweradd(x,y):
return x + y
| 装饰器 | 用法 |
|---|
| @xw.arg | 应用于参数，可以有转换的作用。 | | @xw.ret | 应用于返回值，可以有转换的作用。 |
示例4、动态数组公式(Dynamic Array Formulas) 本地动态数组于2018年9月底在Office 365 Insider Fast中推出。仅特定Excel版本支持，略。 import numpy as np
@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
return np.random.randn(int(r), int(c))
五、添加函数的参数、返回值的说明： 注：仅支持显示在函数向导中
|
