xlwings操作Excel介绍

xlwings详细使用

在我们操作之前可以先了解下，如下内容：

新建：创建一个不存在的工作薄或者工作表
打开：打开一个已经存在的工作薄
引用：就是告诉程序，你要操作哪个对象。比如你打开了A、B、C三个工作薄，现在你想操作A工作薄，就要先引用A
激活：我们可以同时打开多个工作薄，但是一次只能操作一个工作簿，我们正在操作的这个工作薄称为**当前活动工作薄。

在xlwings中

Excel程序用App来表示，多个Excel程序集合用Apps表示；
单个工作簿用Book表示，工作簿集合用Books表示；
单个工作表用Sheet表示，工作表集合用Sheets表示；
区域用Range表示，既可以是一个单元格，也可以是一片单元格区域。

对Excel进行操作主要使用如下三个类：

1 import xlwings as xw
2 xw.App    # 打开一个excel应用
3 xw.Book   # 创建一个工作薄
4 xw.Sheet  # 创建一个工作表

初试：创建一个excel表格并保存

 1 import xlwings as xw
 2  
 3 # 打开excel,参数visible表示处理过程是否可视,add_book表示是否打开新的Excel程序
 4 with xw.App(visible=True,add_book=False) as app:
 5  # 创建一个工作薄
 6  book = app.books.add()
 7  # 工作薄中创建一个sheet表
 8  sht = book.sheets.add()
 9  # 向表格的A1单元格写入“Hello Python”
10  sht.range('A1').value = 'Hello Python'
11  # 保存
12  book.save('d:\\test.xlsx')

理解App

App就是我们打开的一个Excel应用，在我们程序员看来一个App对象就是一个Excel的实例，在此实例下创建工作薄。因此我们要创建工作簿，就必须先创建App实例。一个App实例可以创建多个工作簿Book。使用xlwings可以创建一个或者多个App，而每个App中又可以创建多个工作薄Book，并且多个App之间是相互独立的。

要使用xlwings就需要先引用该库

import xlwings as xw

引入之后，我们可以查看xw下所有的app

1 Apps = xw.apps
2 count = Apps.count
3 print(count) # 打印个数是1，是指当前打开的这个app

创建App

我们可以通过xw.app()创建一个新的app实例

app=xw.App(visible=True,add_book=False)  # 当然也可以通过app.visible = True设置可见性，visible设置是否展示工作簿，True展示，False不展示；add_book 是否添加工作簿，True添加，False不添加

在操作一个app对象的时候要先引用工作薄，但是引用并不代表激活，激活就是当前操作的工作薄。

常用的属性有：

app.screen_updating：打开屏幕更新，我们可以看到xlwings对Excel进行操作的过程，关闭更新可以加速脚本运行。默认是打开的。

app.display_alerts：在使用Excel的过程中，经常会遇到一些提醒信息，比如关闭前的保存提示、数据有效性的警告窗口，若想隐藏这些窗口可以设置成False。如果提醒信息是需要反馈的，Excel会选择默认的方式True

 1 import xlwings as xw
 2  
 3 app = xw.App(visible=False, add_book=False)  # 界面设置
 4 app.display_alerts = False  # 关闭提示信息
 5 app.screen_updating = False  # 关闭显示更新
 6  
 7 wb = app.books.add()  # 创建新的工作簿
 8 sht = wb.sheets['Sheet1']  # 实例化工作表
 9 sht.range('A1').value = 'Hello World!'
10 print(sht.range('A1').value)  # 读取
11 wb.close()
12 app.kill()

其中关闭app有两种方式，通过测试使用kill()函数更快些。

app.kill():通过杀掉进程,强制Excel app退出
或
app.quit():退出excel程序,不保存任何工作簿

工作簿Book与Books

前面介绍了app，并且一个app可以包含多个工作薄，如何在app中创建工作薄呢？

创建Book对象

官方给出的创建工作薄的方式如下：

	xw.Book	xw.books
新建工作簿	`xw.Book()`	`xw.books.add()`
未保存的工作簿	`xw.Book('Book1')`	`xw.books['Book1']`
有全路径的工作簿	`xw.Book(r'C:/path/to/file.xlsx')`	`xw.books.open(r'C:/path/to/file.xlsx')`

两种方式的区别：方式1是创建一个新的App，并在新App中新建一个Book，方式2是在当前App下新建一个Book。

如果是打开一个已经存在的则使用：

wb = app.books.open('绝对或者相对路径的excel文件')
或
wb = xw.Book('绝对或者相对路径的excel文件')

xw.Book('绝对或者相对路径的excel文件')既可以打开工作薄也可以引用工作簿

激活与保存

1 wb.activate()
2 # 如果steal_focus=True, 则把窗口显示到最上层，并且把焦点从Python切换到Excel
3 wb.activate(steal_focus=True)

保存工作薄

wb.save() 
# 或者使用指定路径保存
wb.save('存储路径')

关闭

 1 import xlwings as xw
 2  
 3 app=xw.App(visible=True,add_book=False)
 4 app.display_alerts=False
 5 app.screen_updating=False
 6 # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
 7 filepath=r'test.xlsx'
 8 wb=app.books.open(filepath)
 9  
10 wb.save()
11 wb.close()
12 app.quit()

若想获取当前活动App中的所有books，可以直接通过下列方式

1 import xlwings as xw
2  
3 books = xw.books     # 当前活动App的工作簿集合
4 
5 # books = app.books  # 或者使用app.books获取

工作表Sheet与查看所有Sheets

新建Sheet

sht = wb.sheets.add()
# 或者
sht = wb.sheets.add('test',after='sheet2')

参数1为工作表名称，省略的话为Excel默认名称，参数2为插入位置，可选before或者after，若想引用某一个Sheet，可以通过下面方式

sht = wb.sheets('sheet1') # 指定名称获取sheet工作表

sht = wb.sheets(1) # 根据序号获取

sht = xw.sheets.active #获取当前活动的工作表

 1 import xlwings as xw
 2  
 3 app=xw.App(visible=True,add_book=False)
 4 app.display_alerts=False
 5 app.screen_updating=False
 6 # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
 7 filepath=r'test.xlsx'
 8 wb=app.books.open(filepath)
 9  
10 # add()是在现有的sheets集合列表中追加新的Sheet
11 sht1 = wb.sheets.add()
12 sht2 = wb.sheets.add()
13 print(wb.sheets.count)
14  
15 sht3 = wb.sheets(1)
16 # sht1.activate()
17 sht3.range('A1').value = 'Hello Running'
18  
19 wb.save('test1.xlsx')
20 wb.close()
21 app.quit()

sheet对象可以调用的方法有：

 sheet.activate       sheet.charts         sheet.index
 sheet.api            sheet.clear          sheet.name
 sheet.autofit        sheet.clear_contents sheet.names
 sheet.book           sheet.delete         sheet.pictures
 sheet.cells          sheet.impl           sheet.range
 ......

常用的有：

1 sht.clear()  # 清除工作表所有内容和格式
2 sht.clear_contents() # 清除工作表的所有内容但是保留原有格式
3 sht.delete()      # 删除工作表
4 sht.autofit('c')  # 自动调整列宽
5 sht.autofit('r')  # 自动调整行高
6 sht.autofit()     # 自动调整行高列宽
7 sht.select()      # 在活动工作簿中选择

可以通过属性获取获取工作表的名称、所有单元格的区域对象、当前工作表的索引值

sht.name      sht.cells      sht.index     sht.names

引用区域与单元格操作

在操作区域或者单元格之前，首先就要引用他们，其实就是表明你要操作的区域或者单元格是哪些。可以认为区域是多个单元格。引用区域的方式有很多种，下面列举一下常见的引用方式：

 1 xw.Range('A1:D4')
 2 xw.Range((1,1), (4，4))
 3 xw.Range(xw.Range('A1'),xw.Range('D4'))
 4 xw.Range(xw.Range('A1:E6'),xw.Range('C3:D7'))
 5 xw.Range('NamedRange')
 6 app.range("A1")  # 注意是小写的range
 7 sht.range('A1') 
 8 xw.books['MyBook.xlsx'].sheets[0].range('A1')
 9 sht['A1']
10 sht['A1:D4']
11 sht[0,5]
12 sht[:5,:5]

区域管理可以通过如下方式：

1 range.offset(row_offset=5,column_offset=2)  #表示偏移，row_offset行偏移量（正数表示向下偏移，负数相反），column_offset列偏移量（正数表示向右偏移，负数相反）  注意：是将选区范围进行偏移，内容不进行偏移
2 range.expand(mode='down') # 扩展区域，参数可选取 'down' , 'right' ,'table' ，类似我们使用向下、向右或者下右方的区域扩展操作。
3 range.resize(row_size=4, column_size=2) #表示调整选中区域的大小，参数表示调整后区域的行、列的数量。4 range.current_region #表示全选 类似Ctrl + A

对区域或单元格进行操作：

1）存储数据

1 #储存单个值  ".value“属性
2 sht.range('A1').value=1
3 # 储存列表
4 sht.range('A1').value=[1,2,3] # 将列表[1,2,3]储存在A1：C1中
5 sht.range('A1').options(transpose=True).value=[1,2,3] # 将列表[1,2,3]储存在A1:A3中
6 sht.range('A1').options(expand='table').value=[[1,2],[3,4]] # 将2x2表格，即二维数组，储存在A1:B2中，如第一行1，2，第二行3，4

2）读取数据

1 #读取单个值
2 a=sht.range('A1').value # 将A1的值，读取到a变量中
3 print(a)
4 #将值读取到列表中
5 a=sht.range('A1:A2').value #将A1到A2的值，读取到a列表中
6 print(a)
7 a=sht.range('A1:B2').value # 将第一行和第二行的数据按二维数组的方式读取
8 print(a)

3）清除与删除

1 rng.clear_contents() # 清除range的内容
2 rng.clear() # 清除格式和内容
3 rng.delete(shift=None) # 删除

4）其他设置

 1 rng.number_format # 获取数字格式
 2 rng.number_format = '0.00%' # 设置数字格式
 3 rng.insert(shift=None, copy_origin='format_from_left_or_above')
 4 rng.row# 返回区域第一行的行号
 5 rng.column # 返回区域的第一列的号，注意返回的列号不是ABCD，而是1234
 6  
 7 # 获取行高 或者设置行高
 8 rng.row_height  
 9 rng.row_height = 20
10  
11 # 获取列宽或设置列宽
12 rng.column_width
13 rng.column_width = 20
14  
15 # 自适应行高列宽
16 rng.autofit()
17 rng.columns.autofit()
18 rng.rows.autofit()
19  
20 # 合并单元格
21 rng.merge(across=False)
22 rng.merge_area # 返回合并单元格区域
23 rng.merge_cells # 返回True或者False，测试是否在合并单元格区域
24 rng.unmerge() # 取消单元格合并
25  
26 # 背景色
27 rng.color # 获取指定区域的背景色
28 xw.Range('A1').color = (255,255,255) # 设置背景色
29 xw.Range('A2').color = None # 去除背景色

其他参考

1  range.add_hyperlink  range.clear_contents range.count
2  range.address        range.color          range.current_region
3  range.api            range.column         range.end
4  range.autofit        range.column_width   range.expand
5  range.clear          range.columns        range.formula
6 ...等等

range.add_hyperlink('https://www.baidu.com','百度') # 单元格添加超链接，参数1是url，参数2是单元格显示文字，参数3可省略，默认显示点击提示信息

range.color = (128,128,128) # RGB通道颜色，可获取or设置

range.row/column　　# 获取第几行/列，注意是第几而不是下标

range.formula　　# 可以设置计算表达式，用来进行表内计算

range.current_region　　# 返回当前range所在区域的区域表达，这个比较难描述，好比一个Excel中互相连接的单元格都是连城一片，两个片之间没有任何相邻就是互相独立的。

range.count　　# 返回这个range中共有多少单元格，合并单元格仍然按未合并的算

range.offset(a,b)　　# 获取到当前range向右a格，向下移动b格同样大小的那片区域，ab可以为负值

range.rows/columns　　# 返回行/列的各个range对象

range.expand

参考案例代码：

1）批量写入并读取数据

 1 import xlwings as xw
 2  
 3 wb = xw.Book()
 4 sht = wb.sheets.active
 5 # 向工作表中写入行列值
 6 for i in range(1, 6):
 7     for j in range(1, 6):
 8         sht.range(i, j).value = '({}, {})'.format(i, j)
 9 print(sht.range((1, 1), (5, 5)).expand().value)  # 批量读取
10 print(sht.range(1, 1).expand('right').value)  # 按行读
11 print(sht.range(1, 1).expand('down').value)  # 按列读
12 wb.close()
13

2）提前设置好表格的颜色

1 import xlwings as xw
2 from itertools import product
3  
4 app = xw.App(visible=False)  # 隐藏Excel
5 wb = app.books.open('test.xlsx')  # 打开工作簿
6 sht = wb.sheets['Sheet1']  # 实例化工作表
7 for cell in list(map(''.join, product('ABCDEFGH', '1'))):  # A1 B1 C1 D1 E1 F1 G1 H1
8     print(cell, sht.range(cell).color)  # 填充颜色
9 wb.close()

3）局中插入图片

 1 import os
 2 import xlwings as xw
 3  
 4 wb = xw.Book()
 5 sht = wb.sheets['Sheet1']
 6 rng = sht.range('A1')
 7 fileName = os.path.join(os.getcwd(), 'aa.png')
 8 width, height = 120, 100  # 指定图片大小
 9 left = rng.left + (rng.width - width) / 2  # 居中
10 top = rng.top + (rng.height - height) / 2
11 sht.pictures.add(fileName, left=left, top=top, width=width, height=height)
12 wb.save('test2.xlsx')
13 wb.close()

综合案例：

 1 import xlwings as xw
 2 
 3 wb = xw.Book()
 4 sht = wb.sheets[0]
 5 
 6 info_list = [['110202111111234','帐篷',5],['110202111118891','行李箱','16'],['110202111111004','微波炉','20'],['110202111132741','电冰箱','13'],['110202111109852','乐事薯片','30'],['110202111112030','鲁花花生油','12'],['110202111190391','羽绒服','9'],['110202111122319','防晒霜','18'],]
 7 # 写入表头
 8 titles = [['商品编号','商品名称','数量']]
 9 sht.range('a1').value = titles
10 sht.range('a2').value = info_list  # 写入数据
11 wb.save('goods.xlsx')  # 保存数据

若想更新里面的数据，由于有些商品被卖出，商品数量就会发生变化。另外还有一批货是新引入的。参考代码如下：

 1 import xlwings as xw
 2  
 3 wb = xw.Book()
 4 sht = wb.sheets[0]
 5  
 6  
 7 info_list = [['110202111111234','帐篷','5'],
 8 ['110202111118891','行李箱','16'],
 9 ['110202111111004','微波炉','20'],
10 ['110202111132741','电冰箱','13'],
11 ['110202111109852','乐事薯片','30'],
12 ['110202111112030','鲁花花生油','12'],
13 ['110202111190391','羽绒服','9'],
14 ['110202111122319','防晒霜','18'],
15              ]
16 # 写入表头
17 titles = [['商品编号','商品名称','数量']]
18 sht.range('a1').value = titles
19  
20 # 写入数据
21 sht.range('a2').value = info_list
22  
23 # 保存数据
24 wb.save('goods.xlsx')
25  
26 # 读取数据
27 goods_list = sht.range('a2').expand('table').value
28  
29 for goods in goods_list:
30     goods[0] = str(int(goods[0]))
31     goods[2] = int(goods[2])
32 print(goods_list)
33 new_info = [['110202111111234','帐篷',5],
34 ['110202111118891','行李箱',16],
35 ['110202111111004','微波炉',20],
36 ['110202111132741','电冰箱',10],
37 ['110202111124660','羊毛衫',8],
38 ['110202111109852','乐事薯片',10],
39 ['110202111112030','鲁花花生油',12],
40 ['110202111190391','羽绒服',0],
41 ['110202111122319','防晒霜',9],
42 ['110202111124560','牛仔裤',18],
43 ['110202111134798','老爹鞋',11]]
44 # 去重
45 extra = [i for i in new_info if i not in goods_list]
46 # print(extra)
47  
48 # 读取extra每个商品的包裹号，判断是否存在并更新，然后添加
49 ids = sht.range(2, 1).expand('down').value
50  
51 ids = [str(int(id)) for id in ids]
52 rows = len(sht.range('a2').expand('table').value)
53 # 更新已有数据的库存
54 for goods in extra:
55     if goods[0] in ids:
56         row_number = ids.index(goods[0])
57         print(row_number,goods[1])
58         sht[row_number+1,2].value = goods[2]
59     else:
60         for i in range(3):
61             sht[rows+1,i].value =goods[i]
62         rows+=1
63  
64 wb.save('goods.xlsx')

设置字体样式和字体颜色

 1 # coding: utf-8
 2 
 3 import xlwings as xw
 4 
 5 app=xw.App(visible=False,add_book=False)
 6 filepath = '../data/test.xlsx'
 7 wb=app.books.open(filepath)
 8 sht = wb.sheets('Sheet1')
 9 font_name = sht.range('A1').api.Font.Name    # 获取字体名称
10 font_size = sht.range('A1').api.Font.Size    # 获取字号
11 bold = sht.range('A1').api.Font.Bold        # 获取是否加粗，True--加粗，False--未加粗
12 color = sht.range('A1').api.Font.Color        # 获取字体颜色
13 print(font_name)
14 print(font_size)
15 print(bold)
16 print(color)
17 print('-----设置-----')
18 sht.range('A1').api.Font.Name = 'Times New Roman'    # 设置字体为Times New Roman
19 sht.range('A1').api.Font.Size = 15            # 设置字号为15
20 sht.range('A1').api.Font.Bold = True        # 加粗
21 sht.range('A1').api.Font.Color = (255,0,0)    # 设置为红色RGB(255,0,0)
22 font_name = sht.range('A1').api.Font.Name    # 获取字体名称
23 font_size = sht.range('A1').api.Font.Size    # 获取字体大小
24 bold = sht.range('A1').api.Font.Bold        # 获取是否加粗，True--加粗，False--未加粗
25 color = sht.range('A1').api.Font.Color        # 获取字体颜色
26 print(font_name)
27 print(font_size)
28 print(bold)
29 print(color)
30 wb.save()
31 wb.close()
32 app.quit()

补充资料

获取工作表的总行数和总列数

1 workbook=xw.Book(r'path)
2 workbook.sheets[1].range(1, 1).expand().shape

返回：(25087, 3)

常用方法补充

 1 ###常用函数和方法
 2 #!/usr/bin/env python
 3 # coding:utf-8
 4 import xlwings as xw
 5 ###Book 工作簿常用的api
 6 wb = xw.books['工作簿名称']
 7 wb.activate()  # 激活为当前工作簿
 8 wb.fullname  # 返回工作簿的绝对路径
 9 wb.name  # 返回工作簿的名称
10 wb.save(path = None)  # 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径
11 wb.close()  # 关闭工作簿
12 ##代码例子：
13 wb = xw.books.acitve  # 引用Excel程序中，当前的工作簿
14 x = wb.fullname  # 返回工作簿的绝对路径
15 x = wb.name  # 返回工作簿的名称
16 x = wb.save(path = None)  # 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径
17 x = wb.close()  # 关闭工作簿
18 ###sheet 常用的api
19 sht = x.books['工作簿名称'].sheets['sheet的名称']  # 引用某指定sheet
20 sht.activate()  # 激活sheet为活动工作表
21 sht.clear()  # 清除sheet的内容和格式
22 sht.contents()  # 清除sheet的内容
23 sht.name  # 获取sheet的名称
24 sht.delete  # 删除sheet
25 ###range常用的api
26 rng = x.Range('A1')  # 引用当前活动工作表的单元格
27 rng.add_hyperlink(r'www.baidu.com', '百度', '提示：点击即链接到百度')  # 加入超链接
28 rng.address  # 取得当前range的地址
29 rng.get_address()  # 取得当前range的地址
30 rng.clear_contents()  # 清除range的内容
31 rng.clear()  # 清除格式和内容
32 rng.color  # 取得range的背景色,以元组形式返回RGB值
33 rng.color = (255, 255, 255)  # 设置range的颜色
34 rng.color = None  # 清除range的背景色
35 rng.column  # 获得range的第一列列标
36 rng.count  # 返回range中单元格的数据
37 rng.current_region  # 返回current_region
38 rng.end('down')  # 返回ctrl + 方向
39 rng.formula = '=SUM(B1:B5)'  # 获取公式或者输入公式
40 rng.formula_array  # 数组公式
41 rng.get_address(row_absolute = True, column_absolute = True, include_sheetname = False, external = False)  # 获得单元格的绝对地址
42 rng.column_width  # 获得列宽
43 rng.width  # 返回range的总宽度
44 rng.hyperlink  # 获得range的超链接
45 rng.last_cell  # 获得range中右下角最后一个单元格
46 rng.offset(row_offset = 0, column_offset = 0)  # range平移
47 rng.resize(row_size = None, column_size = None)
48 # range进行resize改变range的大小
49 rng.row  # range的第一行行标
50 rng.row_height  # 行的高度，所有行一样高返回行高，不一样返回None
51 rng.height  # 返回range的总高度
52 rng.shape  # 返回range的行数和列数
53 rng.sheet  # 返回range所在的sheet
54 rng.rows
55 # 返回range的所有行
56 rng.rows[0]  # range的第一行
57 rng.rows.count  # range的总行数
58 rng.columns  # 返回range的所有列
59 rng.columns[0]  # 返回range的第一列
60 rng.columns.count  # 返回range的列数
61 rng.autofit()  # 所有range的大小自适应
62 rng.columns.autofit()  # 所有列宽度自适应
63 rng.rows.autofit()  # 所有行宽度自适应
64 ### books 工作簿集合的api
65 xw.books.add()  # 新建工作簿
66 xw.books.active  # 引用当前活动工作簿
67 ### sheets 工作表的集合
68 xw.sheets.add(name = None, before = None, after = None)  # 新建工作表
69 xw.sheets.active  # 引用当前活动sheet

新建sheet页，适用office和wps

 1 import xlwings as xw
 2 
 3 '''
 4   path: excel文件路径
 5   sheet: 工作簿名称，可以为空，保持默认
 6   before: 添加新工作表前工作表名称可以是整型数字也可以是现有工作表名称
 7   :return:
 8 '''
 9 def creat_sheet(path, sheet = None, before = None):
10   try:
11     wb = xw.Book(path)
12     if isinstance(before, int):
13       before = before + 1
14     sheet_obj = wb.sheets.add(name = sheet, before = before)  #容易出错
15     wb.save()
16     return sheet_obj.name
17   except Exception as e:
18     raise e