Python最好的Excel第三方库——xlwings快速上手
文章目录
- 简介
- 安装
- 初试
- 隐藏Excel
- 工作簿和工作表
- 读写数据
- 数据兼容
- 填充颜色
- 插入图片
- 设置字体
- 设置边框
- 合并拆分单元格
- 插入公式
- 其他
- 宏
- UDFs: 用户定义函数
- 用户设置
- 遇到的坑
- 脚本
- 遇到问题怎么办?
- 封装
- 参考文献
简介
xlwings 是一款操作 Excel 的开源库,其宗旨——让Excel飞起来!
用 Python 控制 Excel,就像 VBA 的脚本、宏、函数一样。
以下公司也在用:
- accenture
- NOKIA
- AQR
- Columbia Business
安装
pip install xlwings
- 1
- 1
初试
import xlwings as xwwb = xw.Book() # 创建新的工作簿sht = wb.sheets['Sheet1'] # 实例化工作表sht.range('A1').value = 'Hello World!' # 写入print(sht.range('A1').value) # 读取wb.save('test.xlsx') # 保存wb.close()
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
隐藏Excel
调用
xw.App(visible=False)
import xlwings as xwVISABLE = False # 是否可视化app = xw.App(visible=VISABLE, add_book=False) # 界面设置app.display_alerts = VISABLE # 关闭提示信息app.screen_updating = VISABLE # 关闭显示更新wb = app.books.add() # 创建新的工作簿sht = wb.sheets['Sheet1'] # 实例化工作表sht.range('A1').value = 'Hello World!'print(sht.range('A1').value) # 读取wb.close()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
工作簿和工作表
连接工作簿(workbook)
import xlwings as xwapp = xw.App(visible=True, add_book=False) # 隐藏界面# app = xw.App(visible=False, add_book=False) # 隐藏界面app.display_alerts = False # 关闭提示信息app.screen_updating = False # 关闭显示更新wb = app.books.add() # 创建新的工作簿# wb = app.books.open('test.xlsx') # 连接当前路径下的工作簿# wb = app.books.active # 获取当前活动的工作簿# wb = xw.Book() # 创建新的工作簿# wb = xw.Book('test.xlsx') # 连接当前路径下的工作簿# wb = xw.Book(r'C:\test.xlsx') # Windows下绝对路径连接input('Enter to quit')wb.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
连接工作表(sheet)
import xlwings as xwwb = xw.Book()sht = wb.sheets.active # 连接工作表# sht = wb.sheets[0] # 连接工作表# sht = wb.sheets['Sheet1'] # 连接工作表# sht1 = wb.sheets.add() # 连接工作表sht1 = wb.sheets.add('新表', after=sht) # 连接工作表input('Enter to quit')wb.close()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
读写数据
指定位置是数据起始点,一行一行读写
写入单元格
import xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1'] # 连接工作表sht.range('A1').value = 'Foo 1'print(sht.range('A1').value)# Foo 1
1
2
3
4
5
6
7
1
2
3
4
5
6
7
批量写入
import xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]] # 批量写入print(sht.range('A1').expand().value) # 批量读取wb.save('test.xlsx') # 保存wb.close()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
按行列号读写(向下、向右数),如图
import xlwings as xwwb = xw.Book()sht = wb.sheets.activefor i in range(1, 6): for j in range(1, 6): sht.range(i, j).value = '({}, {})'.format(i, j)print(sht.range((1, 1), (5, 5)).expand().value) # 批量读取print(sht.range(1, 1).expand('right').value) # 按行读print(sht.range(1, 1).expand('down').value) # 按列读input('Enter to quit')wb.close()
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
数据兼容
强大的转换器能处理绝大多数数据类型,如 字典、Numpy Array 和 Pandas DataFrame
字典
import numpy as npimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']d = {'a': 1, 'b': 2}sht.range('A1').value = dprint(sht.range('A1').options(np.array, expand='table').value)# [['a' '1.0']# ['b' '2.0']]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Numpy数组
import numpy as npimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']sht.range('A1').value = np.eye(3) # 对角矩阵print(sht.range('A1').options(np.array, expand='table').value)# [[1. 0. 0.]# [0. 1. 0.]# [0. 0. 1.]]
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
Pandas数据表
import pandas as pdimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])sht.range('A1').value = df # 写入DataFrameprint(sht.range('A1').options(pd.DataFrame, expand='table').value)# a b# 0.0 1.0 2.0# 1.0 3.0 4.0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
详细阅读:转换器及选项
填充颜色
调用Range.color
import xlwings as xwfrom itertools import productapp = xw.App(visible=False) # 隐藏Excelwb = app.books.open('test.xlsx') # 打开工作簿sht = wb.sheets['Sheet1'] # 实例化工作表for cell in list(map(''.join, product('ABCDEFGH', '1'))): # A1 B1 C1 D1 E1 F1 G1 H1 print(cell, sht.range(cell).color) # 填充颜色wb.close()# A1 None# B1 (255, 0, 0)# C1 (255, 165, 0)# D1 (255, 255, 0)# E1 (0, 128, 0)# F1 (0, 255, 255)# G1 (0, 0, 255)# H1 (128, 0, 128)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
详细阅读:Excel 文档
插入图片
Matplotlib图像
import xlwings as xwimport matplotlib.pyplot as pltwb = xw.Book()sht = wb.sheets['Sheet1']fig = plt.figure()plt.plot([1, 2, 3, 4, 5])sht.pictures.add(fig, name='MyPlot', update=True)wb.save('test.xlsx')wb.close()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
效果
指定图片
import osimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']# sht.pictures.add('1.jpg') # 使用相对路径可能报错sht.pictures.add(os.path.join(os.getcwd(), '1.jpg'))wb.save('test.xlsx')wb.close()
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
居中插入
import osimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']rng = sht.range('A1')fileName = os.path.join(os.getcwd(), '1.jpg')width, height = 10, 10 # 指定图片大小left = rng.left + (rng.width - width) / 2 # 居中top = rng.top + (rng.height - height) / 2sht.pictures.add(fileName, left=left, top=top, width=width, height=height)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
推荐阅读:xlwings插入图片 和 xlwings.main.Pictures.add
智能居中插入
效果
设置字体
下划线 | 值 |
---|---|
普通 | 4或True |
双下划线 | 5 |
粗双下划线 | -4119 |
import xlwings as xwfrom xlwings.utils import rgb_to_intwb = xw.Book()sht = wb.sheets['Sheet1']sht.range('A1', 'XFD1048576').api.Font.Name = '微软雅黑' # 全局字体sht.range('A1').value = 'ABCDE'sht.range('A1').api.Font.Size = 12 # 字号sht.range('A1').api.Font.Bold = True # 加粗start_index = 3 # 下标从1开始length_string = 1 # 修改长度sht.range('A1').api.GetCharacters(start_index, length_string).Font.Color = rgb_to_int((255, 0, 0)) # 设为红色sht.range('B1').value = 'ABCDE'sht.range('B1').api.Font.Italic = True # 斜体sht.range('B1').api.Font.Strikethrough = True # 删除线sht.range('C1').value = 'ABCDE'sht.range('C1').api.Font.Underline = True # 下划线 4普通 5双下划线 -4119粗双下划线sht.range('A2').value = 'a2'sht.range('A2').api.GetCharacters(2, 1).Font.Superscript = True # 上标sht.range('B2').value = 'H2O'sht.range('B2').api.GetCharacters(2, 1).Font.Subscript = True # 下标sht.autofit() # 自动调整input('Enter to quit')wb.close()
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
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
详细阅读:Font 对象 (Excel)
设置边框
位置 | 值 |
---|---|
左上对角线 | 5 |
左下对角线 | 6 |
左 | 7 |
上 | 8 |
下 | 9 |
右 | 10 |
内部垂直线 | 11 |
内部水平线 | 12 |
线型 | 值 |
---|---|
实线 | 1 |
虚线 | -4115 |
点划相间线 | 4 |
划线后跟两个点 | 5 |
点式线 | -4118 |
双线 | -4119 |
无线 | -4142 |
倾斜的划线 | 13 |
粗细 | 值 |
---|---|
细 | 1 |
细长 | 2 |
粗 | 4 |
中 | -4138 |
Color边框颜色和填充颜色不同,若指定RGB需要转换为HEX,调用
rgb_to_int()
,公式如下:
Hex = R + G × 256 + B × 65536 \text{Hex}=\text{R}+\text{G}\times 256+\text{B}\times 65536 Hex=R+G×256+B×65536
颜色 | 值 |
---|---|
黑色 | 0x0 |
红色 | 0xFF |
绿色 | 0xFF00 |
黄色 | 0xFFFF |
蓝色 | 0xFF0000 |
洋红 | 0xFF00FF |
蓝绿 | 0xFFFF00 |
白色 | 0xFFFFFF |
import xlwings as xwfrom xlwings.utils import rgb_to_intif __name__ == '__main__': wb = xw.Book() sht = wb.sheets['Sheet1'] '''5左上角对角线''' sht.range('A2', 'B3').api.Borders(5).LineStyle = 1 # 实线 sht.range('A2', 'B3').api.Borders(5).Weight = 1 # 细 sht.range('A2', 'B3').api.Borders(5).Color = 0x0 # 黑色 '''6左下角对角线''' sht.range('C2', 'D3').api.Borders(6).LineStyle = -4119 # 双线 sht.range('C2', 'D3').api.Borders(6).Weight = 2 # 细长 sht.range('C2', 'D3').api.Borders(6).Color = 0xFF # 红色 '''7 8 9 10 左上下右''' for i in [7, 8, 9, 10]: sht.range('E2', 'F3').api.Borders(i).LineStyle = 4 # 点划相间线 sht.range('E2', 'F3').api.Borders(i).Weight = 4 # 粗 sht.range('E2', 'F3').api.Borders(i).Color = 0xFF00 # 绿色 '''11内部垂直线''' sht.range('G2', 'H3').api.Borders(11).LineStyle = 5 # 划线后跟两个点 sht.range('G2', 'H3').api.Borders(11).Weight = -4138 # 中 sht.range('G2', 'H3').api.Borders(11).Color = rgb_to_int((0, 128, 128)) # 紫色 '''12内部水平线''' sht.range('I2', 'J3').api.Borders(12).LineStyle = -4115 # 虚线 sht.range('I2', 'J3').api.Borders(12).Weight = 4 # 粗 sht.range('I2', 'J3').api.Borders(12).Color = rgb_to_int((0, 0, 255)) # 蓝色 input('任意输入保存') wb.save('result.xlsx') wb.close()
- 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
- 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
效果
推荐阅读:
合并拆分单元格
import xlwings as xwwb = xw.Book()sht = wb.sheets.activesht.range('A1:D5').merge() # 合并单元格input('Enter to unmerge')sht.range('A1:D5').unmerge() # 拆分单元格input('Enter to quit')wb.close()
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
插入公式
import xlwings as xwwb = xw.Book()sht = wb.sheets.activesht.range('A1').value = [['语文', '数学', '总分'], [100, 100, None]]sht.range('C2').formula = '=SUM(A2:B2)'input('Enter to quit')wb.close()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
其他
- 全局单元格
_range = sht.range('A1', sht.used_range.last_cell) # 用过的最后一个单元格_range = sht.range('A1', 'XFD1048576') # 全局单元格
1
2
1
2
宏
1. 设置 Excel 信任对 VBA 工程对象模型的访问:
文件 → 选项 → 信任中心 → 信任中心设置 → 信任对 VBA 工程对象模型的访问
2. 安装加载项
xlwings addin install
- 1
- 1
3. 命令行客户端
快速构建项目
xlwings quickstart project
1
1
4. 打开project.xlsm
启用内容
5. 打开Visual Basic编辑器
快捷键: Alt+F11
Excel 的自定义功能区勾选上开发工具
点击 Visual Basic
出现 xlwings 则说明加载项安装成功
设置引用:工具 → 引用 → 勾选 xlwings
6. 运行宏
此宏运行的逻辑与同名.py的一致
import xlwings as xw@xw.subdef main(): wb = xw.Book.caller() sheet = wb.sheets[0] if sheet['A1'].value == 'Hello xlwings!': sheet['A1'].value = 'Bye xlwings!' else: sheet['A1'].value = 'Hello xlwings!'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
7. 运行宏的其他两种方式
通过 Visual Basic
通过表单控件
UDFs: 用户定义函数
仅支持Windows
1. 命令行客户端
快速构建项目
xlwings quickstart hello
1
1
2. 打开hello.xlsm
启用内容
3. 打开Visual Basic编辑器
快捷键: Alt+F11
设置引用:工具 → 引用 → 勾选 xlwings
4. 运行
用户设置
Windows 用户的配置文件位于:
%USERPROFILE%/.xlwings
的 xlwings.conf
详细阅读:Add-in & Settings
遇到的坑
- 报错
pywintypes.com_error: (-2147023266, '这个类型的数据不受支持。', None, None)
解决方案:打开一个Excel - Excel 行高列宽单位不一致
Excel 行高 1单位 = 1像素
Excel 列宽 1单位 = 1个字符宽度(默认为6.107像素) - Excel 点击 Import Functions 报错
could not creat python process
确保 Error in parsing PYTHONPATH · Issue #574 中的步骤正确 - 用久了资源管理器有很多Excel进程没被清理,需手动清理
脚本
- 自动遍历某文件夹下的图片,根据文件名一一对应插入Excel,每N个换行
图片大小可不一,N可指定
遇到问题怎么办?
根据官方文档Missing Features,找不到对应的方法或者遇到问题,这样解决:
- 在Github上开issue问问题
- 实际上,
xlwings
是对Windows的pywin32或对Mac的appscript的封装,可以通过调用api属性来访问底层对象,使用pywin32/appscript感觉很像VBA的语法,底层对象提供VBA可以做的几乎所有事情
>>> sheet = xw.Book().sheets[0]>>> sheet.api<COMObject <unknown>> # Windows/pywin32app(pid=2319).workbooks['Workbook1'].worksheets[1] # Mac/appscript
- 1
- 2
- 3
- 4
- 1
- 2
- 3
- 4
作者秒回,真牛逼
封装
常用
import xlwings as xwfrom xlwings.utils import rgb_to_intVISIBLE = True # 界面是否可见app = xw.App(visible=VISIBLE, add_book=False) # 界面设置app.display_alerts = VISIBLE # 提示信息app.screen_updating = VISIBLE # 显示更新wb = app.books.add() # 新建工作簿sht = wb.sheets.active # 实例化工作表'''初始化样式'''sht.range('A1', 'XFD1048576').api.Font.Name = '微软雅黑' # 字体sht.range('A1', 'XFD1048576').api.Font.Size = 12 # 字号# sht.range('A1', 'XFD1048576').row_height = 10 # 行高=1像素# sht.range('A1', 'XFD1048576').column_width = 10 # 列宽=6.107像素for i in [7, 8, 9, 10, 11, 12]: sht.range('A1', 'XFD1048576').api.Borders(i).LineStyle = 1 # 边框线型为实线 sht.range('A1', 'XFD1048576').api.Borders(i).Weight = 2 # 边框粗细为细长 sht.range('A1', 'XFD1048576').api.Borders(i).Color = rgb_to_int((255, 255, 255)) # 边框颜色为白色'''此处编写逻辑代码''''''此处编写逻辑代码'''sht.autofit() # 自动调整input('Enter to quit')wb.save('result.xlsx')wb.close()
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
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
已实现功能
设置边框
TODO
批量读写
填充颜色
设置字体
合并拆分单元格
插入公式
import xlwings as xwfrom xlwings.utils import rgb_to_intclass ExcelHelper(object): def __init__(self, fullname='test.xlsx', sheet='Sheet1', visible=True): app = xw.App(visible=visible, add_book=False) # 界面设置 app.display_alerts = visible # 提示信息 app.screen_updating = visible # 显示更新 self.wb = app.books.open(fullname) self.sht = self.wb.sheets[sheet] # 实例化工作表 def save(self, fullname='result.xlsx'): self.sht.save(fullname) def close(self): self.wb.close() def set_border(self, cell1=None, cell2=None, linestyle=1, weight=2, color=(0, 0, 0), edge=[7, 8, 9, 10, 11, 12]): '''设置边框 :param cell1: 设置范围左上角单元格。默认为全局 :param cell2: 设置范围右下角单元格。默认为全局 :param linestyle: 边框线型。1直线 2虚线 4点划线 5双点划线 :param weight: 边框粗细。默认为2 :param color: 边框颜色。默认为黑色 :param edge: 边框位置。默认为内部线,5左上角对角线 6左下角对角线 7左 8上 9下 10右 11内部垂直线 12内部水平线 ''' if all([cell1, cell2]): _range = self.sht.range(cell1, cell2) else: # _range = self.sht.range('A1', self.sht.used_range.last_cell) # 用过的最后一个单元格 _range = self.sht.range('A1', 'XFD1048576') # 全局单元格 for i in edge: _range.api.Borders(i).LineStyle = linestyle _range.api.Borders(i).Weight = weight _range.api.Borders(i).Color = rgb_to_int(color)if __name__ == '__main__': excelHelper = ExcelHelper() excelHelper.set_border(color=(0, 0, 0)) input() excelHelper.close()
- 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
- 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
参考文献
- xlwings 文档
- Excel VBA 文档
- xlwings 中文文档
- xlwings 官网
- xlwings GitHub
- Python读写Excel文件第三方库汇总,你想要的都在这儿!
- Excel 行高列宽与图片像素的关系
- What is the unit of Excel column width?
- xlwings:Python for Excel
- Error in parsing PYTHONPATH · Issue #574
- python中实现26个英文字母与其对应的ascii码之间的转换
- xlwings最全操作
- How to set the color of a font in a cell?
- How to set the color of individual letters?