xlwings系列文章我想分为四块:
(一)问题引入、安装、操作Application,Workbooks和worksheets,range的方法
(二)如何与VBA,pandas交互
(三)xlwings实战
(四)pandas,openpyxl,xlwings对比分析
李鑫:python xlwings excel报表自动化 系列文章精讲 (二)zhuanlan.zhihu.com一、问题引入
会Python但是又不想学vba?平时重复性的报表需求有很多?
xlwings优势:
- 能与vba交互,调用vba的api(xlwings最强大的就是调用vba函数);与matplotlib,pandas交互。
- 制作格式模板,涉及各种函数计算,合并单元格,填色,调距离等。在公司里,统计分析重要,美观易读同样重要。
- 支持各种格式。
- 快速
先说下xlwings与pandas的简单区别:pandas是对结构化数据的分析挖掘。要求数据非常规整。比如第一列单元格填什么字段,第二列填什么字段都很清楚。一般为二维数据。pandas读取excel会破坏excel的格式。比如说数据透视表,合并单元格,填色,长宽都无法读出来。excel除了数据储存功能还有报表开发,也就是各种好看易读的模板。pandas就没办法了。
二、python 安装xlwings
pip 安装
pip install xlwings
#comtypes pywin32是使用xlwings需要依赖的包。需要额外安装。
建议使用jupyter notebook 的conda安装。因为conda会自动帮你安装依赖的包。省去一些麻烦。
conda install xlwings
三、xlwings讲解
EXCEL基本结构
看这张不太清晰的图,excel基本结构分为 Application ——> Workbooks ——> Worksheets ——> Range. 即应用程序 ——> 工作簿 ——> 工作表 ——> 单元格。
xlwings属性和方法思维导图这是我做的xlwings对于app,wb,shet,rng的属性和方法的思维导图。根据方法和属性的特点做了一些简单归类。可以保存下来在需要时查阅。
(一)、对APP进行操作
1. 导入xlwings包
import xlwings as xw
Python是面向对象编程。也就是说一切都可以作为对象。对象就有了属性和方法。下面做一下简单介绍。
2. 打开excel:
#visible是否可见。False表示后台运行。 add_book 是否新建一个工作簿
app = xw.App(visible=True,add_book=True)
2.1 对excel操作时屏幕是否更新
app.screen_updating = False # :屏幕更新,就是说代码对于excel的操作你可以看见,关闭实时更新可以加快脚本运行。默认是True。
3. 返回所有book对象集合
app.books #返回一个打开的全部workbook的列表。Python打开的和手动打开的是不互通的
4. 退出excel
app.kill():终止进程,强制退出。
quit():在不保存的情况下,退出excel程序。
(二)对workbook进行操作
1. 打开workbook
1.1 新建wk对象
命令有很多,选择一个常用的就好。我常用wk=app.books.open(file_path)
wb = app.books.add() #创建新的book
wb = app.books.open()
wk = xw.Book()
wk = xw.books.open()
1.2打开某一文件
wb
1.3未储存的excel
wk = xw.Book('Book1')
wk = xw.books['Book1'] #也可以使用索引
截图来自xlwings官方文档
2. Python调用vba脚本。传进的参数为Sub或Function的名称。代码在excel开发工具——>模块中写好。
my_sum = wk.macro('MySum')
my_sum(1, 2)
以下为vba代码:
Function
3.在vba中调用Python代码。具体使用请看系列文章(二)
自我使用感觉就是,xlwings强大在能调用vba的函数。自身函数太少。与openpyxl相反,openpyxl不能调用vba函数,所以得自行开发。caller()函数是python与VBA交互的关键函数。
caller():当vba通过runpython从excel中调用python函数时,引用这个book,还会将其打包成excel中的函数。
注:放置xw.Book.caller()在从Excel调用的函数中,而不是作为全局变量调用。否则,它会阻止Excel在退出时正常关闭,并在您使用时为您留下僵尸进程。[1]
#保存在test.py中
import xlwings as xw
#在A1单元格插入1
def insert_1():
wb = xw.Book.caller()
wb.sheets[0].range('A1').value = 1
在VBA编辑器(Alt-F11)中,将下面的代码写入VBA模块。自动添加带有示例调用的新模块。
Sub HelloWorld()
RunPython ("import test1; test1.insert_1()")
End Sub
注意:将excel和test.py保存在同一目录下。py文件名称为test.py。函数名称为insert_1。
RunPython ("import test1; test1.insert_1()")
'语法:“import py文件名; 文件名.函数名”
4.关闭wk
wk.save() #保存
wk.close() #在没有保存的情况下关闭。
4.查看它的路径+文件名称
wk.fullname:以字符串的形式返回对象的名称跟路径。
5.
xw.Book.open_template()#:创建一个已经包含xlwings VBA模块的Excel文件(必须从交互式python shell中调用此方法)
6.保存
wb.save(path=None)#:保存工作簿,若为指定路径,保存在当前工作目录。
7.set_mock_caller:设置用于调试caller()的excel文件,可以直接在python里运行。
import os
import xlwings as xw
def my_macro():
sht = xw.Book.caller().sheets[0]
sht.range('A1').value = 'Hello xlwings!'
if __name__ == '__main__':
xw.Book('file.xlsm').set_mock_caller()
my_macro()
caller()函数是将python代码在excel中运行;set_mock_caller()函数是可以直接在python中运行。它们结构类似。
(三)对worksheet进行操作
1.sheets返回表示工作簿中所有工作表的工作表集合。
2.active返回正在被操作的sht对象。
sht.activate
3.add(name=None, before=None, after=None)添加sht
wb.sheets.add() #可以指定位置
4.autofit(axis=None) 自动调整行和列
import xlwings as xw
wb = xw.Book()
wb.sheets['Sheet1'].autofit('c') #列 columns h c
wb.sheets['Sheet1'].autofit('r') #行 rows 或 r,
wb.sheets['Sheet1'].autofit()
5.book sht所属的book
sht.book #属性。
6.cells 返回一个Range对象,该对象表示Sheet上的所有单元格(而不仅仅是当前正在使用的单元格)。也不仅仅是有值的单元格
sht.cells
#<Range [xlwing_test.xlsx]Sheet1!$1:$1048576>
sht.cells.shape
#(1048576, 16384)
7.charts:sheet中的所有图表集合。
8.clear():清除整个sheet的内容和格式。
9.clear_contents():清除整个sheet的内容,但是保留格式。
10.delete():删除sheet。
11,pictures:返回所有Pictures对象的集合。
12.index返回工作表的索引(以1为基准,与Excel相同)。
sht.index
#wb中,sht的索引。
13.name获取或设置Sheet的名称。
names返回表示所有工作表特定名称(使用“SheetName!”前缀定义的名称)的名称集合。
(三)对range进行操作
1.add_hyperlink(address, text_to_display=None, screen_tip=None):在单元格上添加超链接。
address:'超链接地址'。text_to_display:在单元格上显示的文本。screen_tip:当鼠标悬停时,出现的提示。
2.clear():删除Range里的内容和格式。
3.clear_contents():删除Range里的内容,保留格式。
4.color:获取或者设置范围里单元额的背景颜色。
设置颜色时,可以使用RGB元组,就像下面这样。
range.color = (125,125,125)
5.column:返回Range对象的第一列编号
6.column_width:获取或设置Range的宽度。
7.columns:返回一个RangeColumns对象,该对象表示指定范围内的列。
8.count返回单元格数量。
9.end(direction):返回一个Range对象。相当于window中的Ctrl+Up,Ctrl+down,Ctrl+left, 或Ctrl+right.
range.end('down') #跳到该列最后一个有值单元格