Python最好的Excel第三方库——xlwings快速上手

2024-06-25 07:39:55

文章目录

简介
安装
初试
隐藏Excel
工作簿和工作表
读写数据
数据兼容
填充颜色
插入图片
设置字体
设置边框
合并拆分单元格
插入公式
其他
宏
UDFs: 用户定义函数
用户设置
遇到的坑
脚本
遇到问题怎么办？
封装
参考文献

简介

xlwings 是一款操作 Excel 的开源库，其宗旨——让Excel飞起来！

用 Python 控制 Excel，就像 VBA 的脚本、宏、函数一样。

以下公司也在用：

accenture
NOKIA
AQR
Columbia Business

安装

pip install xlwings

初试

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()

`隐藏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()

工作簿和工作表

连接工作簿（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()

读写数据

指定位置是数据起始点，一行一行读写

写入单元格

import xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']  # 连接工作表sht.range('A1').value = 'Foo 1'print(sht.range('A1').value)# Foo 1

批量写入

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()

按行列号读写（向下、向右数），如图

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']]

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

详细阅读：转换器及选项

填充颜色

调用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()

效果

指定图片

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()

居中插入

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)

推荐阅读：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)

`设置边框`

Borders边框位置常量

位置	值
左上对角线	5
左下对角线	6
左	7
上	8
下	9
右	10
内部垂直线	11
内部水平线	12

LineStyle边框线型

线型	值
实线	1
虚线	-4115
点划相间线	4
划线后跟两个点	5
点式线	-4118
双线	-4119
无线	-4142
倾斜的划线	13

Weight边框粗细

粗细	值
细	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()

效果

推荐阅读：

Python实现RGB和HEX颜色互转（Microsoft的RGB函数）

颜色常量

合并拆分单元格

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()

`插入公式`

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()

其他

全局单元格

_range = sht.range('A1', sht.used_range.last_cell)  # 用过的最后一个单元格_range = sht.range('A1', 'XFD1048576')  # 全局单元格

`宏`

1. 设置 Excel 信任对 VBA 工程对象模型的访问：

文件 → 选项 → 信任中心 → 信任中心设置 → 信任对 VBA 工程对象模型的访问

2. 安装加载项

xlwings addin install

3. 命令行客户端

快速构建项目

xlwings quickstart project

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!'

7. 运行宏的其他两种方式

通过 Visual Basic

通过表单控件

UDFs: 用户定义函数

仅支持Windows

1. 命令行客户端

快速构建项目

xlwings quickstart hello

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

作者秒回，真牛逼

封装

常用

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()

参考文献

史上最全Python 操作 Excel库总结！

为了带大家了解各个库的异同,从而在不同场景下可以灵活使用,本文将横向比较7个可以操作 Excel 文件的常用模块,在比较各模块常用操作的同时进行巩固学习! 首先让我们来整体把握下不同库的特点 &quo ...
超全整理｜Python 操作 Excel 库 xlwings 常用操作详解！

人人都可以简单入门Python.爬虫.数据分析简说Python推荐文 | 陈熹.刘早起来源:早起Python 在之前的文章中我们曾详细的讲解了如何使用openpyxl 操作Excel,其实在 ...
python xlwings 复制工作表

其实xlwings在早期并没有提供复制工作表的相应函数,要实现复制工作表功能,需要调用excel的vba里面的相应方法worksheet.Copy. 在xlwings0.22版本以后,xlwings开 ...
python利用xlwings和pandas达到excel的分类汇总的效果

excel中的分类汇总,以及数据透视表,能够根据某几个"标签",对某个"数量"进行分类汇总,但是我总觉得,这2个用起来都不那么好看,那么,如何用python实现 ...
Python提取 “Excel文本框” 内容，这个需求头一次见，1000个表，10行代码！

前天本文介绍说实话,这个需求头一次碰到,我相信对于大多数朋友来说,也是头一次碰到."提取excel文本框中的内容",对,你没有听错!我也不知道你碰到过没有,但是这确实是一位朋友 ...
Pythonic 到底是什么，从 try finally 到 with 的感悟

不要费心编写 Python 已经完成的东西,因为您无法编写更好的东西. 学习 Python 的第一节课不仅仅是 "Hello World",相信也有"Python 之禅& ...
运营学Python｜Python&Excel联动(上)

来源:简说Python 作者:老表一.写在前面目前这个系列具体安排还没想好((fang)搁(ge)置(zi)了好久),主要是围绕Python自动化办公展开,至少包括Python对Excel.Wor ...
Python最好的Excel第三方库

文章目录简介安装初试隐藏Excel 工作簿和工作表读写数据数据兼容填充颜色插入图片设置字体设置列宽设置边框合并拆分单元格插入公式其他宏 UDFs: 用户定义函数用户设置 ...
《Python中神奇的第三方库：Faker》

来自:CSDN,作者:南枝向暖北枝寒MA 链接: https://blog.csdn.net/mall_lucy/article/details/108655317 [导读]:开发项目的时,为了测试 ...
如何快速一次性卸载所有python包（第三方库）呢？

很多时候我们都会有一个麻烦事,就是打开pycharm或者VScode等的时候,都有可能因为自己电脑上面安装的第三方模块过多,导致电脑很卡,风扇转速不停地增加,这时候我们就会想去卸载一些不太用得着的第三 ...
Python自动安装第三方库的小技巧，让你的代码拿来就用

大家好,我是Python之眼. 最近周末也加班了,害...... 有刚接触python的粉丝同学在运行此前<Python爬虫> | 手把手教你扒一扒贝壳网成交房源数据>的完整代码遇到 ...
[js] 第99天不用第三方库，说说纯js怎么实现读取和导出excel？

今日试题: 不用第三方库,说说纯js怎么实现读取和导出excel? 此开源项目四大宗旨:勤思考,多动手,善总结,能坚持 <论语>,曾子曰:"吾日三省吾身"(我每天多次反 ...
Python第三方库资源大全，1000+工具包

5天前 awesome-python 是 vinta 发起维护的 Python 资源列表,内容包括:Web 框架.网络爬虫.网络内容提取.模板引擎.数据库.数据可视化.图片处理.文本处理.自然语言处理 ...
【python】解析Excel中使用xlrd库、xlwt库操作，写入excel进阶完整脚本实例（五）

写excel的难点可能不在构造一个workbook的本身,而是填充的数据,不过这不在范围内.在写excel的操作中也有棘手的问题,比如写入合并的单元格就是比较麻烦的,另外写入还有不同的样式. 本次拟构 ...
【python】解析Excel中使用xlrd库、xlwt库操作，读取excel进阶完整脚本实例（四）

本次主要通过脚本展现使用xlrd库.xlwt库的完整操作,对象是比较常见的表格,通过这两个脚本的学习,实现对Excel的进阶操作. 首先,我们定义一个表格example_excel.xls,如图所示: ...