Python最好的Excel第三方库

2024-05-08 16:33:49

文章目录

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

简介

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

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

以下公司也在用：

accenture
NOKIA
AQR
Columbia Business

注意！根据xlwings issue，截止2021/2/3，该库只能用在Windows或Mac。Linux下还是得用openpyxl。

虽然好用，但是如果要部署到线上，还是用别的库吧。

安装

pip install xlwings

初试

import xlwings as xw

wb = 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 xw

VISABLE = 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 xw

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

连接工作表（sheet）

import xlwings as xw

wb = 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 xw

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

批量写入

import xlwings as xw

wb = 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 xw

wb = xw.Book()
sht = wb.sheets.active
for 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()

数据兼容

强大的转换器能处理绝大多数数据类型，如字典、Numpy Array 和 Pandas DataFrame

字典

import numpy as np
import xlwings as xw

wb = xw.Book()
sht = wb.sheets['Sheet1']
d = {'a': 1, 'b': 2}
sht.range('A1').value = d
print(sht.range('A1').options(np.array, expand='table').value)
# [['a' '1.0']
#  ['b' '2.0']]

Numpy数组

import numpy as np
import xlwings as xw

wb = 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.]]

Pandas数据表

import pandas as pd
import xlwings as xw

wb = xw.Book()
sht = wb.sheets['Sheet1']
df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])
sht.range('A1').value = df  # 写入DataFrame
print(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 xw
from itertools import product

app = xw.App(visible=False)  # 隐藏Excel
wb = 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)

详细阅读：Excel 文档

插入图片

Matplotlib图像

import xlwings as xw
import matplotlib.pyplot as plt

wb = 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 os
import xlwings as xw

wb = 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 os
import xlwings as xw

wb = 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) / 2
sht.pictures.add(fileName, left=left, top=top, width=width, height=height)

推荐阅读：xlwings插入图片和 xlwings.main.Pictures.add

智能居中插入

效果

设置字体

下划线	值
普通	4或True
双下划线	5
粗双下划线	-4119

import xlwings as xw
from xlwings.utils import rgb_to_int

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

详细阅读：Font 对象 (Excel)

设置列宽

# sht.autofit()  # 自动调整
sht.range("A1").column_width = 0.75  # 列宽
sht.range("B1").column_width = 25.89  # 列宽

设置边框

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 xw
from xlwings.utils import rgb_to_int

if __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 xw

wb = xw.Book()
sht = wb.sheets.active
sht.range('A1:D5').merge()  # 合并单元格
input('Enter to unmerge')
sht.range('A1:D5').unmerge()  # 拆分单元格
input('Enter to quit')
wb.close()

插入公式

import xlwings as xw

wb = xw.Book()
sht = wb.sheets.active
sht.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.sub
def 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进程没被清理，需手动清理。
报错 pywintypes.com_error: (-2147221008, '尚未调用 CoInitialize。', None, None)
先import pythoncom，然后在报错前后调用

pythoncom.CoInitialize()
_app = xw.App(visible=VISABLE, add_book=False)  # 报错的地方
pythoncom.CoInitialize()

脚本

自动遍历某文件夹下的图片，根据文件名一一对应插入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/pywin32
app(pid=2319).workbooks['Workbook1'].worksheets[1]  # Mac/appscript

作者秒回，真牛逼

封装

常用

import xlwings as xw
from xlwings.utils import rgb_to_int

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

已实现功能

设置边框

TODO

批量读写
填充颜色
设置字体
合并拆分单元格
插入公式

import xlwings as xw
from xlwings.utils import rgb_to_int

class 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利用xlwings和pandas达到excel的分类汇总的效果

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

前天本文介绍说实话,这个需求头一次碰到,我相信对于大多数朋友来说,也是头一次碰到."提取excel文本框中的内容",对,你没有听错!我也不知道你碰到过没有,但是这确实是一位朋友 ...
超全整理｜Python 操作 Excel 库 xlwings 常用操作详解！

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

xlwings简要操作说明 xlwings与pandas.numpy模块有较好的交互,语法类似VBA,支持python调用vba,excel中调用python函数,可对excel进行读写操作: 文档链 ...
python xlwings 复制工作表

其实xlwings在早期并没有提供复制工作表的相应函数,要实现复制工作表功能,需要调用excel的vba里面的相应方法worksheet.Copy. 在xlwings0.22版本以后,xlwings开 ...
史上最全Python 操作 Excel库总结！

为了带大家了解各个库的异同,从而在不同场景下可以灵活使用,本文将横向比较7个可以操作 Excel 文件的常用模块,在比较各模块常用操作的同时进行巩固学习! 首先让我们来整体把握下不同库的特点 &quo ...
Pythonic 到底是什么，从 try finally 到 with 的感悟

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

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

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

来自:CSDN,作者:南枝向暖北枝寒MA 链接: https://blog.csdn.net/mall_lucy/article/details/108655317 [导读]:开发项目的时,为了测试 ...
Python自动安装第三方库的小技巧，让你的代码拿来就用

大家好,我是Python之眼. 最近周末也加班了,害...... 有刚接触python的粉丝同学在运行此前<Python爬虫> | 手把手教你扒一扒贝壳网成交房源数据>的完整代码遇到 ...
如何快速一次性卸载所有python包（第三方库）呢？

很多时候我们都会有一个麻烦事,就是打开pycharm或者VScode等的时候,都有可能因为自己电脑上面安装的第三方模块过多,导致电脑很卡,风扇转速不停地增加,这时候我们就会想去卸载一些不太用得着的第三 ...
[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,如图所示: ...
【python】解析Excel中使用xlrd库、xlwt库操作，使用xluils库修改Excel文件详解（三）

之前介绍了读和写excel,前两种都不是修改excel的,但是在实际的工作中,经常会遇到修改已经存在的Excel文件这种需求.xlrd中put_cell可以实现原表格上简单的写入,而xlwt直接生成新 ...