dingsir的个人博客分享 http://blog.sciencenet.cn/u/dingsir

博文

利用Python扩展Excel能力的xlwings库使用体会

已有 886 次阅读 2020-3-16 13:54 |个人分类:软件杂谈|系统分类:教学心得| Python, xlwings, Excel, Excel, VBA, 调试

科学网的文章只能在48小时内修改,以后再有更新只能重新发贴,这个不太方便,会导致不少重复文章,顺便吐槽一下.

当前最新0.18版(希望阅读者有基础的VBA开发经验,否则可能有点困难)

★xlwings是个什么东西?

  它是一个支持Excel自动化 使用Python语言的库, 它的基本架构是:

 1)Excel使用VBA语句运行,  VBA调用RunPython等函数来运行Python代码.

 2)Python代码放在外部的Python文件中,可以独立编辑.

 因为RunPython函数等是在xlwings.xlam实现的,需要引用xlwings.xlam文件.


★如何安装xlwings?

网上的介绍比较多.  要求先装python,毕竟代码要在Python环境运行.Excel也是必须的,因为要在Excel中运行Python代码.这两个是必要前提. python安装现在有一种很方便的工具叫pip,建议先安装好并且保证联网可以下载(注意防火墙别挡住它了). 因为下面用到.

1)pip安装:在命令行(不是Python解析器,是Windows的cmd或Powershell下面)输入pip install xlwings, 它自动联网检查并下载安装最新版的xlwings.

2)从源码安装. 解压源码, 在xlwings目录内,在系统的命令行(cmd或powershell)下执行 python setup.py install

系统的cmd或Powershell最好用管理员身份启动.


★安装好之后怎么用?

安装好了之后,如果需要新建一个项目(包括Excel文件和Python源文件),最简单的办法是:

1)在系统的命令行(cmd或Powershell)中,先切换到希望建立项目的目录下,

2) 再执行xlwings quickstart {项目名}.  {项目名}为你指定的项目名称,输入时不用含大括号. xlwings会为你在当前目录下建立一个目录,名称即项目名,下面包括两个文件:一个是{项目名}.xlsm, 另一个是{项目名}.py, Excel文件里放你的电子表格数据,Py文件是Python源码文件.


★如何在Excel里面显示xlwings标签?

打开Excel,按Alt+F11调出VBA编辑环境,在工具菜单下,点击"引用...",再点击对话框里的"浏览", 切换到"xlwings.xlam"文件所在的目录(大致是Python安装目录下面的Lib\site-packages\xlwings\addin目录,

例如C:\Program Files\Python38\Lib\site-packages\xlwings\addin),选中这个文件,点击打开返回即可.


★如何在Excel中启用调试Python代码?

建议你先在Python环境中调试好再从Excel调用, 这样调试会简单一点. 在Python中,调试的函数首行写一句xw.books.active.set_mock_caller(),意思是当前活跃的workbook做为调用者.开始Python调试之前,先把对应的Excel(宿主)打开,以免这一语句失败.  

如果要从Excel开始调用,来调试Python中的代码, 这样做:

  1)首先在Python源码的主程序末尾中写入xw.serve(), 设置好断点,  并启动点击运行(快捷键F5),等待Excel调用.

  2)切换到Excel文件中,勾选xlwings标签里面的Debug UDFs. 再运行Excel里面的宏. 当运行到断点处时,Python环境中就会自动停下.你可以继续步入或逐语句调试了.


★如何将Python中的函数等打包为Excel的自定义函数?

  1)在Python源码中,将需要Excel导入为自定义函数(UDF)的过程,函数名上一行标识为@xw.sub(针对无返回值的) 或 @xw.func(有返回值的).保存!

  2)在调用Excel文件的xlwings标签上,点击Import Functions, 程序会将Python文件中的标识过的@xw.sub 或@xw.func的过程重新导入到xlwings_udfs模块(此模块中先前导入的内容会清除,因此官方提醒不要在此模块内修订内容)


★如果程序运行时出现一个黑色窗口,能否避免?

  可以的,这种情况说明Python是运行中控制台里面的.可以将在Excel的xlwings标签里, Interpreter右边的文本框中填写Pythonw,或是pythonw.exe文件的完整路径,指定用Windows版Python来运行就可以了. 这个对自定义函数服务器也同样有效.


★什么是自定义函数服务器(UDF Server),为什么要重启UDF Server?

  就是Python代码里那些标记了@xw.sub或@xw.func的函数装载到内存的代码. 为了提高效率,可以勾选:RunPython: use UDF server,这样首次运行时代码加载到内存中,后面你再编辑了它的代码,它不会自动更新代码再装载到内存,节省不少时候. 不过如果确实需要更新内存中的这部分代码,你需要在Excel中点击一下Restart UDF Server,

  或者在Python环境停止运行,重新启动调试或运行.


★Python文件中, 一个文件里写的UDF(要导入到Excel的函数)太多,可以分开写到不同文件吗?

  可以的,不过要把其它文件的名字告诉xlwings, 具体就是,在Excel的xlwings标签右边的文本框内写上xlwHello;customUDFs等模块名: 主模块写在前, 拓展的在后,用英文分号分隔.


★如果Python调试服务器没有运行(即xw.server()没有运行), 我在Excel中又勾选了Debug UDFs, 并且调用代码,会怎么样?

1)  会出现运行时错误1000,这时候结束就好,再切换到Python环境中点击一下运行,让xw.server()运行起来,再在Excel中运行代码就行了.

  2)反过来,如果你看到出现这个错误,就检查一下是不是勾上了Debug UDFs,如果不需要调试,取消这个勾就好.

3)如果Python调试服务器运行了,但Excel环境中没有勾选Debug UDFs, 运行会正常进行.但不会触发调试功能.


★Book类下面有一个函数叫set_mock_caller(),是做什么用的?

 原函数里有说明: Sets the Excel file which is used to mock ``xw.Book.caller()`` when the code is called from Python and not from  Excel via ``RunPython``.

 翻译一下就是, 有时代码是从Python环境直接调试或调用的,没有使用Excel里面的RunPython, 这时候没有地方设定代码的调用者是谁,就运行这个函数来假装一下某个workbook就是这些代码的调用者,让Python环境与调用者能够关联起来.

 换句话说,RunPython在运行的时候,是设置了当前的工作薄作为Python代码的调用者(caller)的.

 在Book的caller方法下面还有一句.  To be able to easily invoke such code from Python for debugging, use ``xw.Book.set_mock_caller()``. 为了更容易的从Python调试这些代码,使用xw.Book.set_mock_caller()方法,也就是说,这个mock caller是为了让Python知道谁是调用者,相关的数据写到哪里去.


★xlwings有哪些值得专门讲一下的知识点?

1)Range对象有一个Expand函数,可以扩展当前数据区域,按down向下扩展,right向右扩展,table按表格扩展,扩展到空单元为止.

2)更方便的做法是,将一个range区域赋值给二维数组变量来处理, 可以充分利用数组的切片功能来操作,极为方便.而且不用将列序号转化为列字母(这个比较烦)

3)将一个列表赋给某个单元格,它会自动横向粘贴(也可通过Option设定为Transpose指定为竖向粘贴).一个二维数组也是这样,会按横向/竖向展开.不用你去计算目标单元格要占多少个.

4)可以向代表区域的Range的value属性赋值, 而且这种操作比一个一个赋值快得多.如 range("A1:B10").value=3.2

很多,现在只想到这几点.


★我想看看xlwings.xlam里有些什么代码可以吗?

在{Python安装目录}\Lib\site-packages\xlwings安装目录下有个文件叫 xlwings.bas.


★xlwings有哪些毛病?

目前这个库功能不是最完善,完成度最高的主要是数据的读写/操作/清除/删除等功能, Excel的格式设置等功能比较弱. 如果主要是对Excel数据进行分析和操作, 目前的版本已经够用.还在不断更新.

当然,VBA直接在Excel里面写, 天然的集成在文件格式之内.,相比之下, xlwings复杂了不少,但上手之后一看,其实还是比较简单的.可以方便的利用Python对各种库的支持,扩展的能力是大大增加了.我能接受.


简单演示,如果你的电脑上装好了python和xlwings,也有Excel,可以试试看.

testSelection.rar



http://blog.sciencenet.cn/blog-1213210-1223782.html

上一篇:从两类化合物的密度想到的
下一篇:实测一下不同程序的Pi值计算能力

0

该博文允许注册用户评论 请点击登录 评论 (0 个评论)

数据加载中...

Archiver|手机版|科学网 ( 京ICP备07017567号-12 )

GMT+8, 2020-7-6 15:46

Powered by ScienceNet.cn

Copyright © 2007- 中国科学报社

返回顶部