xlwings简要操作说明

xlwings与pandas、numpy模块有较好的交互，语法类似VBA，支持python调用vba，excel中调用python函数，可对excel进行读写操作；

xlwings简要操作说明
- 1. 对象结构：
- 2. App常用语法
- 3. Book常用语法
- - 3.1 新建工作簿
  - 3.2 打开工作簿
  - 3.3 工作簿保存
  - 3.4 其他：获取名称、激活、关闭
- 4. Sheet常用语法
- - 4.1 工作表引用
  - 4.2 新建|删除工作表
  - 4.3 其他：激活，删除、工作表名称、行列删除
  - 4.4 自动匹配工作表列、行宽度
- 5. Range常用语法
- - 5.1 单元格引用
  - 5.2 数据的读取
  - 5.3 单元格值默认读取格式
  - 5.4 单元格数据写入
  - 5.5 expand:动态选择Range维度
  - 5.6 其他方法
- 6. 转化器
- - 6.1 字典转化
  - 6.2 numpy转化
  - 6.3 Pandas Series与DataFrame转化器
- 7. python调用执行VBA代码
- 8. VBA调用python

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 xwapp = xw.App(visible=True,add_book=False)

3.1 新建工作簿

wb = app.books.add()# 方法1wb = 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 xwwb = 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='新工作表')

4.3 其他：激活，删除、工作表名称、行列删除

# 激活为活动工作簿sheet.activate()# 清除工作表的内容和格式sheet.clear()# 清除工作表内容，不清除样式sheet.clear_contents()# 工作表名称sheet_name = sheet.name# 删除工作表sheet.delete()# 工作表计算sheet.calculate()# 工作表的使用范围，等价与vba的usedrangesheeet.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).insertsheet.api.columns('c").insert# 工作表隐藏，bool类型,True or Falsesheet.visible = True

4.4 自动匹配工作表列、行宽度

sheet.autofit(轴=无)

'''若要自动调整行，请使用以下内容之一：rows或r若要自动装配列，请使用以下内容之一：columns或c若要自动调整行和列，请不提供参数。'''import xlwings as xwwb = xw.Book()wb.sheets['Sheet1'].autofit('c')wb.sheets['Sheet1'].autofit('r')wb.sheets['Sheet1'].autofit()

5. Range常用语法

5.1 单元格引用

import xlwings as xwrng = 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')# a1rng = 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，xltorightrng = sheet[0,0].end('down')

5.2 数据的读取

# 获取单元格的值,单元格的value属性val = sheet.range('a1').valuels = sheet.range("a1:a2").value# 一维列表ls = sheet.range("a1:b2").value  # 二维列表

5.3 单元格值默认读取格式

默认情况下，带有数字的单元格被读取为float，带有日期单元格被读取为datetime.datetime,空单元格转化为None；数据读取可以通过option操作指定格式读取

import datetimesheet[1,1].value = 1sheet[1,1].value # 输出是1.0sheet[1,1].options(numbers=int).value# 输出是1# 指定日期格式为datetime.datesheet[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.addressrng.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 <= 255rng.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)# 行的高度，所有行一样高返回行高，不一样返回Nonerng.row_height# 返回range的总高度rng.height# 返回range的行数和列数rng.shape# 返回range所在的sheetrng.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 npsheet = 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，第一列不为索引，读取结果为DataFromdf = sheet.range('a1').options(pd.Series,expand='table',index=0).value# index=1，第一列设置为索引，输出为Seriess = sheet.range('a1').options(pd.Series,expand='table',index=1).value# 写入，不需要索引，index设置为False，保留表头，header=Truesheet.range('d1').options(pd.DataFrame,index=False,header=True).value = df# 读取为DataFramedf = 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 + bEnd Function

第二步：访问调用vba代码

app = xw.App()app.books.open('test.xlsm')sum = app.macro('sum')sum(10,20)# return: 30

8. VBA调用python

转载：vba调用python转载链接

(5条消息) python