Python&Excel 02 Python xlwings处理Excel（一文汇总）_xlwings常用函数

一、Python处理Excel库分析

Python处理Excel方面常用的有xlrd、xlwt等7个库，根据对官方网站及网络的查询，在我比较关注的操作系统、python3支持、新旧Excel文件处理，读写改几个方面进行了筛选。

对我而言，由于使用的windows系统，日常处理需要兼顾新旧Excel文件处理，读写改肯定是都能支持最好，因此毫不犹豫选择了xlwings作为Python处理Excel的入口。

二、xlwings简介

xlwings是一个可以实现从Excel调用Python，也可在python中调用Excel的库。开源免费，一直在更新。
特点：

• 编写脚本：使用接近VBA的语法从Python自动执行Excel/与Excel交互。
• 宏：使用干净而强大的Python代码替换VBA宏。
• 自定义项：用Python编写用户定义函数（UDF）（仅限Windows）。

xlvings的宗旨就是让Excel飞起来！

xlwings官方文档：https://docs.xlwings.org/en/stable/index.html

三、xlwings安装

（一）安装先决条件

1. xlwings需要Excel的安装因此只适用于Windows（窗口）和macOS操作系统。请注意，macOS当前不支持UDF。
2. xlwings至少需要Python 3.8。

（二）安装

使用pip进行安装：

pip install xlwings
1

或conda：

conda install xlwings
1

Anaconda已经预安装了xlwings

但可能版本比较落后，我们可以通过：
pip：

pip install --upgrade xlwings
1

或conda：

conda install -c conda-forge xlwings
1

更新到最新版本。

四、xlwings_helloworld

接下来我们用一个示例运行xlwings。

具体代码如下：

import xlwings as xw

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

执行代码，在指定目录创建helloworld.xlsx文件

并按照要求添加文字Hello Python

五、xlwings的主要用例及常用函数

在我们操作之前可以先了解下xlwings的基础规范：

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

在xlwings中：

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

对Excel进行操作的主要用例及常用函数：

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

# 创建app
app=xw.App(visible=True,add_book=False)  # visible设置是否展示工作簿，True展示，False不展示；add_book 是否添加工作簿，True添加，False不添加
app.display_alerts = False  # 关闭提示信息
app.screen_updating = False  # 关闭显示更新

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

# 创建新的工作簿
wb = app.books.add() 

# 打开已经存在的工作簿
wb = app.books.open('绝对或者相对路径的excel文件')
wb = xw.Book('绝对或者相对路径的excel文件')

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

# 关闭工作簿
wb.close()

# 保存工作簿
wb.save() 
wb.save('存储路径') 

# 新建工作表
sht = wb.sheets.add()
sht = wb.sheets.add('test',after='sheet2') #参数1为工作表名称，省略的话为Excel默认名称，参数2为插入位置，可选before或者after

# 引用某一个工作表
sht = wb.sheets('sheet1') # 指定名称获取sheet工作表
sht = wb.sheets(1) # 根据序号获取
sht = xw.sheets.active #获取当前活动的工作表

# sheet对象常用的调用方法
sht.clear()  # 清除工作表所有内容和格式
sht.clear_contents() # 清除工作表的所有内容但是保留原有格式
sht.delete()      # 删除工作表
sht.autofit('c')  # 自动调整列宽
sht.autofit('r')  # 自动调整行高
sht.autofit()     # 自动调整行高列宽
sht.select()      # 在活动工作簿中选择

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

# 引用区域与单元格操作
xw.Range('A1:D4')
xw.Range((1,1), (4，4))
xw.Range(xw.Range('A1'),xw.Range('D4'))
xw.Range(xw.Range('A1:E6'),xw.Range('C3:D7'))
xw.Range('NamedRange')
app.range("A1")  # 注意是小写的range
sht.range('A1')
xw.books['MyBook.xlsx'].sheets[0].range('A1')
sht['A1']
sht['A1:D4']
sht[0,5]
sht[:5,:5]

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

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

# 区域或单元格读取数据
a=sht.range('A1').value # 将A1的值，读取到a变量中
print(a)
a=sht.range('A1:A2').value #将A1到A2的值，读取到a列表中
print(a)
a=sht.range('A1:B2').value # 将第一行和第二行的数据按二维数组的方式读取
print(a)

# 区域或单元格清除与删除
rng.clear_contents() # 清除range的内容
rng.clear() # 清除格式和内容
rng.delete(shift=None) # 删除

# 区域或单元格其他设置
rng.number_format # 获取数字格式
rng.number_format = '0.00%' # 设置数字格式
rng.insert(shift=None, copy_origin='format_from_left_or_above')
rng.row# 返回区域第一行的行号
rng.column # 返回区域的第一列的号，注意返回的列号不是ABCD，而是1234
# 获取行高 或者设置行高
rng.row_height
rng.row_height = 20
# 获取列宽或设置列宽
rng.column_width
rng.column_width = 20
# 自适应行高列宽
rng.autofit()
rng.columns.autofit()
rng.rows.autofit()
# 合并单元格
rng.merge(across=False)
rng.merge_area # 返回合并单元格区域
rng.merge_cells # 返回True或者False，测试是否在合并单元格区域
rng.unmerge() # 取消单元格合并
# 背景色
rng.color # 获取指定区域的背景色
xw.Range('A1').color = (255,255,255) # 设置背景色
xw.Range('A2').color = None # 去除背景色
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

六、综合案例

案例：某单位经费有一级项目2个（公用经费、运行维保），每个一级项目下各有5个二级项目，经系统导出今年1月、2月经费执行情况，当月未产生费用的项目不在excel中出现，费用按照开支时间排序，顺序不固定，需要统计1至2月的一级项目及各二级项目总的资金执行率。

总预算表test1.xlsx：

一月执行情况test1_1.xlsx:

二月执行情况test1_2.xlsx:

代码及结果如下：