EXCEL和Python擦出爱的火花
1、背景
最近接手与前端硬件设备进行交互的工作,主要是通过Soceket TCP长连接的方式进行数据交互。然后通过wireshark抓包工具对数据进行解析。还涉及AES加解密处理等。
EXCEL的自带函数有限,尤其是AES加解密更是不容易操作,网上通过搜索关于VBA方式的AES加解密方案也是不是这不通就是那不通。于是想到了Python的对于数学处理的计算强大性。于是找打了xlwings这个开源方式。
如果Excel是牛郎的话,Python就是织女,xlwings就是鹊桥。就是给他俩牵线搭桥的。、
当然,现在最新的office 365直接就支持python的函数调用的,又兴趣的可以去看看。
话不多说,直接进入主题。
2、Python
2.1安装Python环境
首先得安装python开发环境,我的是安装的是Python3.10版本
2.2 安装xlwings包
安装xlwings的包版本是0.33.9版本
安装命令:pip install xlwings
3、xlwings
3.1快速生成项目
利用Xlwings模块的quickstart快速创建带宏的工作蒲和python代码文件,具体操作如下。
WIN+R输入cmd打开命令行,用cd命令切换至你想要的路径,在路径下输入命令“xlwings quickstart ProjectName”
执行完命令后会生成一对文件。
3.2文件内容
python文件内容:
import xlwings as xw
def main():
wb = xw.Book.caller()
sheet = wb.sheets[0]
if sheet["A1"].value == "Hello xlwings!":
sheet["A1"].value = "Bye xlwings!"
else:
sheet["A1"].value = "Hello xlwings!"
@xw.func
def hello(name):
return f"Hello {name}!"
if __name__ == "__main__":
xw.Book("ProjectName.xlsm").set_mock_caller()
main()
VBA文件内容:
Sub SampleCall()
mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
RunPython "import " & mymodule & ";" & mymodule & ".main()"
End Sub
4、EXCEL
4.1在excel中加载xlwings模块
关于wlxings的使用,是python自动化办公|批量处理Excel文件的利器-xlwings,这里不再赘述
首先将xlwings模块作为excel功能区的选项卡
依次点击“文件-选项”,打开如下对话框:
点击“信任中心-信任中心设置=宏设置”,勾选“信任对VBA工程对象模型的访问”:
点击功能区“开发工具-加载宏”,勾选Xlwings:
如果没有Xlwings,可以点击“浏览”,选择xwlings安装目录下的xlwings.xlam文件,加载进去就OK了
点击添加完成之后,会在Excel顶部出现一个关于xwings模块的配置。
4.2 xlwings解释器和python文件路径配置
Interpreter(Python解析器):
D:\Program Files\Python\Python311\python.exe
PYTHONPAT(要执行的python文件):
C:\Users\ZhuanZ\Desktop\xlwingsProject\sds\ProjectName
4.3 VBA中工具–引用–xlwings导入
5.EXCEL与Python互调
5.1Excel通过宏调用Python函数
指定宏
执行宏
执行完毕后,就将python里的代码结果显示到Excel表格里了。
5.2Excel直接调用Python函数
点击导入函数
然后进行测试
5.3Python调用Excel函数
python文件新增函数:
# Python调用VBA脚本
def pyDiaoyongVba():
app = xw.App(visible=True, add_book=False)
# 设置测试文件的路径
wb = app.books.open(r'C:\Users\ZhuanZ\Desktop\xlwingsProject\ProjectName\ProjectName.xlsm')
# 调用VBA脚本
test1901 = wb.macro('test1901')
test1901(4, 5)
return print(test1901(4, 5))
Excel vab新增函数:
Function test1901(a, b)
Debug.Print "1901"
test1901 = a - b
End Function
执行完python函数后,得到运行的结果为20: