xlwings简要操作说明
xlwings与pandas、numpy模块有较好的交互,语法类似VBA,支持python调用vba,excel中调用python函数,可对excel进行读写操作;
局限,不同于openpyxl模块,xlwings模块对excel应用有依赖,这意味着如果在linux中,该模块无法使用。
OpenPyXL 可以读、写和编辑Excel文件,而XlsxWriter 只能读。
OpenPyXL 处理包含 VBA 宏的Excel文件时更加方便。
XlsxWriter的文档更优秀。
XlsxWriter 通常比OpenPyXL 更快,不过具体速度取决于你要写入的工作簿的大小,有时候差异并不明显。
文档链接:xlwings中文文档链接
目录
1. 对象结构:
xlwings — apps(App) — books(Book)— sheets(Sheet)— Range
模块 — 应用 — 工作簿 — 工作表 — 单元格
2. App常用语法
import xlwings as xw
# 创建应用app:
# 参数:visible:应用是否可见(True|False),add_book:是否创建新工作簿(True|False)
app = xw.App(visible=True,add_book=True)
wb = app.books.active # get新创建的工作簿(刚创建的工作簿为活动工作簿,使用active获取)
# 警告提示(True|False)
app.display_alerts = False
# 屏幕刷新(True|False)
app.screen_updating = False
# 工作表自动计算{'manual':'手动计算','automatic':'自动计算','semiautomatic':'半自动'}
app.calculation = 'manual'
# 应用计算,calculate方法同样适用于工作簿,工作表
app.calculate()
# 退出应用
app.quit()
3. Book常用语法
import xlwings as xw
app = xw.App(visible=True,add_book=False)
3.1 新建工作簿
wb = app.books.add() # 方法1
wb = xw.Book() # 方法2,不填写参数新建工作簿
wb = xw.books.add()
3.2 打开工作簿
# file_path:工作簿文件路径
wb = app.books.open(file_path)
wb = xw.Book(file_path)
xlwings.Book(fullname = None,update_links = None,read_only = None,format = None,password = None,write_res_password = None,ignore_read_only_recommended = None,origin = None,delimiter = None,editable = None,notify = None,converter = None,add_to_mru = None,local = None,destroy_load = None,impl = None )
参数:
- fullname(str 或类似路径的对象,默认为None)–现有工作簿的完整路径或名称(包括xlsx,xlsm等)或未保存工作簿的名称。如果没有完整路径,它将在当前工作目录中查找文件。
- update_links(bool ,默认为None)–如果省略此参数,则提示用户指定如何更新链接
- read_only(bool ,默认为False)– True以只读模式打开工作簿
- format(str)–如果打开文本文件,则指定分隔符
- password (str)–打开受保护的工作簿的密码
- write_res_password(str)–写入写保留工作簿的密码
- ignore_read_only_recommended(bool ,默认为False)–设置为True使只读推荐消息静音
- origin(int)–仅适用于文本文件。指定它的起源。使用XlPlatform常数。
- delimiter (str)–如果format参数为6,则指定分隔符。
- editable(bool ,默认为False)–此选项仅适用于旧版Microsoft Excel 4.0加载项。
- notify(bool ,默认为False)–如果文件无法以读写模式打开,则在文件可用时通知用户。
- converter(int)–打开文件时尝试的第一个文件转换器的索引。
- add_to_mru(bool ,默认为False)–将此工作簿添加到最近添加的工作簿列表中。
- local(bool ,默认为False)–如果为True,则使用Excel语言保存文件,否则使用VBA语言保存文件。在macOS上不支持。
- destroy_load(int ,默认xlNormalLoad)–可以是xlNormalLoad,xlRepairFile或xlExtractData之一。在macOS上不支持。
3.3 工作簿保存
wb.save()
wb.save(path=None) # 或者指定path参数保存到其他路径,如果没保存为脚本所在路径
3.4 其他:获取名称、激活、关闭
# get指定名称的工作簿
wb = xw.books['工作簿名称']
# 激活为当前工作簿
wb.activate()
# 返回工作簿的绝对路径
wb.fullname
# 工作簿名称
wb.name
# 关闭工作簿
wb.close()
4. Sheet常用语法
4.1 工作表引用
引用工作表的前提:工作簿被打开
import xlwings as xw
wb = xw.books['工作簿名字']
sheet = wb.sheets['工作表名字']
sheet = wb.sheets[0] # 也可以使用数字索引,从0开始,类似于vba的worksheets(1)
sheet = wb.sheets('工作表名字') # 也可以使用熟悉的vba圆括号引用
# 从左往右,第二张sheet,圆括号序列从1开始,方括号从0开始
sheet = wb.sheets(2)
sheet = xw.sheets.active # 当前活动工作表,sheets是工作表集合
sheet = wb.sheets.active
4.2 新建|删除工作表
# 新建工作表表
# 参数:name:新建工作表名称;before创建的工作表位置在哪个工作表前面;after:创建位置在哪个工作表后面;
# before和after参数可以传入数字,也可以传入已有的工作表名称,传入数字n表示从左往右第n个sheet位置
# before和after参数不传,创建sheet默认在当前活动工作表左侧
sheet = xw.sheets.add(name=None,before=None,after=None)
wb.sheets.add(name='新工作表4',before='新工作表')
# 删除工作表
wb.sheets("新工作表4").delete()
4.3 其他:激活,删除、工作表名称、行列删除
# 激活为活动工作簿
sheet.activate()
# 清除工作表的内容和格式
sheet.clear()
# 清除工作表内容,不清除样式
sheet.clear_contents()
# 工作表名称
sheet_name = sheet.name
# 删除工作表
sheet.delete()
# 工作表计算
sheet.calculate()
# 工作表的使用范围,等价与vba的usedrange
sheeet.used_range
# 删除第一行
sheet.api.rows(1).delete
# 第一行插入一行
sheet.api.rows(1).insert
# 删除第一列,a列
sheet.api.columns(1).delete
# 删除b-e列
sheet.api.columns('b:e').delete
# 第三列c列,插入一列
sheet.api.columns(3).insert
sheet.api.columns('c").insert
# 工作表隐藏,bool类型,True or False
sheet.visible = True
4.4 自动匹配工作表列、行宽度
sheet.autofit(轴=无)
'''
若要自动调整行,请使用以下内容之一:rows或r
若要自动装配列,请使用以下内容之一:columns或c
若要自动调整行和列,请不提供参数。
'''
import xlwings as xw
wb = xw.Book()
wb.sheets['Sheet1'].autofit('c')
wb.sheets['Sheet1'].autofit('r')
wb.sheets['Sheet1'].autofit()
5. Range常用语法
5.1 单元格引用
import xlwings as xw
rng = xw.books['工作簿名称'].sheets['工作表名称'].range('a1')
# 第一个应用第一个工作簿第一张sheet的第一个单元格
xw.apps[0].books[0].sheets[0].range('a1')
xw.apps[0].books[0].sheets[0].range(1,1) # 使用row+column定位,坐标从1开始
# 引用活动sheet的单元格,直接接xw,Range首字母大写
rng = xw.Range('a1') # a1
rng = xw.Range(1,1) # a1,行列用tuple进行引用,圆括号从1开始
rng = xw.Range((1,1),(3,3)) # a1:a3
# 也可以工作表对象接方括号引用单元格
sheet = xw.books['工作簿'].sheets['工作表名称']
rng = sheet['a1'] # a1单元格
rng = sheet['a1:b5'] # a1:b5单元格
rng = sheet[0,1] # b1单元格,也可以根据行列索引,从0开始为
rng = sheet[:10,:10] # a1:j10
# 单元格邻近范围
rng = sheet[0,0].current_region #a1单元格邻近区域=vba:currentregion
# 返回excel:ctrl键+方向键跳转单元格对象:上:up,下:down,左:left,右:right
# 等同于vba:end语法:xlup,xldown,xltoleft,xltoright
rng = sheet[0,0].end('down')
# 清理单元格格式
Range.ClearFormats
5.2 数据的读取
# 获取单元格的值,单元格的value属性
val = sheet.range('a1').value
ls = sheet.range("a1:a2").value # 一维列表
ls = sheet.range("a1:b2").value # 二维列表
5.3 单元格值默认读取格式
默认情况下,带有数字的单元格被读取为float,带有日期单元格被读取为datetime.datetime,空单元格转化为None;数据读取可以通过option操作指定格式读取
import datetime
sheet[1,1].value = 1
sheet[1,1].value
# 输出是1.0
sheet[1,1].options(numbers=int).value
# 输出是1
# 指定日期格式为datetime.date
sheet[2,1].options(dates=datetime.date).value
# 指定空单元格为'NA'
sheet[2,1].options(empty='NA').value
5.4 单元格数据写入
# 单个值
sheet.range('a1').value = 1
# 写入一维列表
sheet.range("a1:c1").value = [1,2,3]
#option:设置transpose参数转置下
sheet.range("a1:a3").options(transpose=True).value = [1,2,3]
sheet.range("a1:a3").value = [1,2,3]
# 写入二维列表
sheet.range('A1').options(expand='table').value=[[1,2],[3,4]]
sheet.range('A1').value=[[1,2],[3,4]] # 也可以直接这样写
'''
尽量减少与excel交互次数有助于提升写入速度
sheet.range('A1').value = [[1,2],[3,4]]
比sheet.range('A1').value = [1, 2]
和sheet.range('A2').value = [3, 4]会更快
'''
5.5 expand:动态选择Range维度
可以通过单元格的expand或者options的expand属性动态获取excel中单元格维度;两者再使用区别是,使用expand方法,只有在访问范围的值才会计算;options方法会随着单元格值范围扩增而相应的范围增大,区别示例如下:
expand参数值除了’table’,还可以使用‘right’:向右延伸,‘down’:向下延伸;
sheet = xw.sheets.add(name='工作表名称')
sheet.range('a1').value = [[1,2],[3,4]]
# 使用options方法
rng1 = sheet.range('a1').options(expand='table')
# 使用expand方法
rng2 = sheet.range('a1').expand('table') # 默认是table,‘table’参数也可以不填
# 现在新增一行数据
sheet.range('a3').value = [5,6]
print(rng1.value)
# [[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]
print(rng2.value)
# [[1.0, 2.0], [3.0, 4.0]] 使用的expand方法,范围没有扩散
print(sheet.range('a1').options(expand='table').value)
# [[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]],再次expand方法访问,值范围扩散
5.6 其他方法
# 引用当前活动工作表的单元格
rng=xw.Range('A1')
# 加入超链接
rng.add_hyperlink(r'www.baidu.com','百度',‘提示:点击即链接到百度')
# 取得当前range的地址
rng.address
rng.get_address()
# 清除range的内容
rng.clear_contents()
# 清除格式和内容
rng.clear()
# 取得range的背景色,以元组形式返回RGB值
rng.color
# 设置range的颜色
rng.color=(255,255,255)
# 清除range的背景色
rng.color=None
# 获得range的第一列列标
rng.column
# range的第一行行标
rng.row
# 返回range中单元格的数据
rng.count
# 获取公式或者输入公式
rng.formula='=SUM(B1:B5)'
# 数组公式
rng.formula_array
# 获得单元格的绝对地址
rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)
# 获得列宽,column_width必须在以下范围内:0 <= column_width <= 255
rng.column_width
# 返回range的总宽度
rng.width
# 获得range的超链接
rng.hyperlink
# 获得range中右下角最后一个单元格
rng.last_cell
# range平移
rng.offset(row_offset=0,column_offset=0)
# range进行resize改变range的大小
rng.resize(row_size=None,column_size=None)
# 行的高度,所有行一样高返回行高,不一样返回None
rng.row_height
# 返回range的总高度
rng.height
# 返回range的行数和列数
rng.shape
# 返回range所在的sheet
rng.sheet
# 返回range的所有行
rng.rows
# range的第一行
rng.rows[0]
# range的总行数
rng.rows.count
# 返回range的所有列
rng.columns
# 返回range的第一列
rng.columns[0]
# 返回range的列数
rng.columns.count
# 所有range的大小自适应
rng.autofit()
# 所有列宽度自适应
rng.columns.autofit()
# 所有行宽度自适应
rng.rows.autofit()
# 从指定的Range对象创建一个合并的单元格。
# cross(bool ,默认为False)– True,将指定范围的每一行中的单元格合并为单独的合并单元格。
rng.api.merge(cross = False)
# 返回一个Range对象,该对象表示包含指定单元格的合并Range。如果指定的单元格不在合并范围内,则此属性返回指定的单元格。
# 合并单元格拆分
rng.api.unmerge()
# 单元格的格式
rng.number_format = '0.00%'
6. 转化器
6.1 字典转化
字典转化可以将excel两列数据读取为字典,如果是两行数据,使用transpose转置下;
sheet.range('a1').value = [['a',1],['b',2]]
sheet.range('a1:b2').options(dict).value
# {'a': 1.0, 'b': 2.0}
sheet.range('a4').value = [['a','b'],[1,2]]
sheet.range('a4:b5').options(dict,transpose=True).value
# {'a': 1.0, 'b': 2.0}
excel工作表值如下:
6.2 numpy转化
相关参数:
ndim=None(维度,:1维也可以设置为2转化成二维array),dtype=None(可指定数据类型)
import numpy as np
sheet = xw.Book().sheets[0]
sheet.range('A1').options(transpose=True).value = np.array([1, 2, 3])
sheet.range('A1:A3').options(np.array, ndim=2).value # 返回二维数组
6.3 Pandas Series与DataFrame转化器
相关参数:
ndim=None,index=1(多列,是否使用第一列为索引),header=True(表头),dtype=None;
DataFrame的表头可以设置为1,2,1等价于True,2表示二维表头;index:0等价与False,1等价于True,第一列设置为索引
# 写入两列数据
sheet.range('a1').values = [['name','age'],['张三',18],['李四',20],['王五',35]]
# index=0,第一列不为索引,读取结果为DataFrom
df = sheet.range('a1').options(pd.Series,expand='table',index=0).value
# index=1,第一列设置为索引,输出为Series
s = sheet.range('a1').options(pd.Series,expand='table',index=1).value
# 写入,不需要索引,index设置为False,保留表头,header=True
sheet.range('d1').options(pd.DataFrame,index=False,header=True).value = df
# 读取为DataFrame
df = sheet.range('a1').options(pd.DataFrame,expand='table',index=0).value
7. python调用执行VBA代码
第一步:excel文件‘test.xlsm’:vbe窗口创建一个函数,也可以是模块,另见Book.macro;
部分python不好操作的,我们也可以事先在excel文件里植入vba模块代码,结合xlwings一起使用;
Function sum(a,b)
sum = a + b
End Function
第二步:访问调用vba代码
app = xw.App()
app.books.open('test.xlsm')
sum = app.macro('sum')
sum(10,20)
# return: 30
8. VBA调用python
xlwings在Windows中提供了访问底层 pywin32 对象的接口,在macOS中提供了访问appscript 对象的接口。
在Windows中,sheet[“A1”].api会返回一个pywin32 对象;在macOS中返回的是appscript 对象。
不同对象,同样一个方法函数名可能不同
在Windows中,大部分时候可以直接在api对象上使用VBA 方法或属性。如果要使用方法,那么一定要在Python 代码中加上圆括号:sheet["A1"].api.ClearFormats()
。如果在macOS中操作,因为appscript的语法难以捉摸,处理起来会复杂写。清除单元格格式sheet["A1"].api.clear_formats()
。
可以通过内核判断是在macos还是win系统环境下
import sys
if sys.platform.startswith("darwin"):
sheet["A10"].api.clear_formats()
elif sys.platform.startswith("win"):
sheet["A10"].api.ClearFormats()