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

简介

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()，公式如下：

$$\text{Hex}=\text{R}+\text{G}\times 256+\text{B}\times 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()

效果

推荐阅读：

1. Python实现RGB和HEX颜色互转（Microsoft的RGB函数）
2. 颜色常量

合并拆分单元格

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

其他

1. 全局单元格

_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

遇到的坑

1. 报错 pywintypes.com_error: (-2147023266, '这个类型的数据不受支持。', None, None)
   解决方案：打开一个Excel
2. Excel 行高列宽单位不一致
   
   Excel 行高 1单位 = 1像素
   Excel 列宽 1单位 = 1个字符宽度（默认为6.107像素）
3. Excel 点击 Import Functions 报错 could not creat python process
   确保 Error in parsing PYTHONPATH · Issue #574 中的步骤正确
4. 用久了资源管理器有很多Excel进程没被清理，需手动清理。
5. 报错 pywintypes.com_error: (-2147221008, '尚未调用 CoInitialize。', None, None)
   先import pythoncom，然后在报错前后调用

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

脚本

1. 自动遍历某文件夹下的图片，根据文件名一一对应插入Excel，每N个换行
   图片大小可不一，N可指定
   

遇到问题怎么办？

根据官方文档Missing Features，找不到对应的方法或者遇到问题，这样解决：

1. 在Github上开issue问问题
2. 实际上，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()

参考文献

1. xlwings 文档
2. Excel VBA 文档
3. xlwings 中文文档
4. xlwings 官网
5. xlwings GitHub
6. Python读写Excel文件第三方库汇总，你想要的都在这儿！
7. Excel 行高列宽与图片像素的关系
8. What is the unit of Excel column width?
9. xlwings：Python for Excel
10. Error in parsing PYTHONPATH · Issue #574
11. python中实现26个英文字母与其对应的ascii码之间的转换
12. xlwings最全操作
13. How to set the color of a font in a cell？
14. How to set the color of individual letters?