Python办公自动化 – 使用xlwings库操作Excel

Python有很多第三方库可以操控Excel，主流的有9个，功能上各有千秋，前辈们已经做了功能测试，我就直接选择一个最功能比较全面的库xlwings来使用。

关于xlwings

官网：https://www.xlwings.org/

xlwings是开源且免费的，预装了Anaconda和WinPython，可在 Windows 和 macOS 上运行，能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改。

xlwings还可以和matplotlib、numpy以及pandas无缝连接，支持读写numpy、pandas数据类型，将matplotlib可视化图表导入到excel中。

最重要的是xlwings可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。

xlwings的工作原理

先看图

App是什么？App就是实例化的Excel程序，你可以同时打开多个Excle程序，可以理解成微信多开的效果。为了方便理解，我把一个App成为一个工作区。

工作原理：工作区–>工作簿–>工作表–>单元格

理解了xlwings的工作原理，所有的操作都会变得一目了然。

首先用xwwings创建一个应用实例app，然后在app中创建一个工作簿，选择工作簿中的工作表，再指定单元格或者范围进行操作。这个操作模型跟我们操作excle基本上是一模一样的。

xlwings使用入门

xlwings安装和导入

如果你使用的是Anaconda和WinPython，那么你可以直接import xlwings库使用

其他情况可以先使用pip安装

pip install xlwings

xlwings导入

import xlwings as xw

xlwings基础操作

建立excel表连接

xlwings连接工作簿的两种操作方法，xw.Book是全局操作，xw.books是在对应的实例中操作

# 方法一
# 如果没有工作区，会新建一个工作区然后创建一个工作簿，如果有工作区，会在当前工作区创建工作簿
wb = xw.Book() ## 新建工作簿
wb = xw.Book('Book1')  ## 绑定已经打开的工作簿
wb = xw.Book("example.xlsx")  ## 打开指定的工作簿

# 方法二
# 先新建一个工作区App，然后在指定的App中新建或者绑定工作簿
app = xw.App()
wb = app.books.add()  # 新建工作簿
wb = app.books['Book1']  # 绑定已经打开的工作簿
wb = app.books.open["example.xlsx"]  # 打开指定的工作簿

实例化工作表对象

sht = wb.sheets["sheet1"]

# 下面两种方式是等价的
sht = wb.sheets[0]  # Python视角，下标0，代表第一个工作表
sht = wb.sheets(1)  # Excel视角，数字1，代表Sheet1，即第一个工作表

设置活动对象

设置活动对象可以理解成激活窗口，我们要操作那个工作表，就激活那个工作表窗口，和我们平时自己操作excle的逻辑是一样的

# 活动应用程序
app = xw.apps.active

# 活动工作簿
wb = xw.books.active  # 全局视角
wb = app.books.active  # 工作区视角

# 活动工作表
sht = xw.sheets.active  # 全局视角
sht = wb.sheets.active  # 工作簿视角

# 活动工作表的Range
xw.Range('A1')   # 全局视角
sheet.range('a1')  # 工作表视角

写入内容

# 写入单元格
sht.range('a1').value = 'I'

# 横向写入--> A1:C1
sht.range('a1').value = ['I', 'Love', 'You']

# 纵向写入--> A1:A3
sht.range('a1').options(transpose=True).value = ['I', 'Love', 'You']

# 写入区域--> A1:B2
sht.range('a1').value = [['I', 'Love', 'You'], ['I', 'hate', 'You']]

读取内容

# 读取单元格
sht.range('a1').value

# 读取行
sht.range('a1:c1').value

# 读取列
sht.range('a1:a2').value

# 读取区域
sht.range('a1:c2').value

# 读取批量数据
sht.range('a1').expand().value

获取数据范围

shape = sht.used_range.shape

修改行高和列宽

# 修改
sht.range('a1').row_height = 15
sht.range('a1').column_width = 10
sht.range('a1').rows.autofit()  # 行高自适应
sht.range('a1').columns.autofit()  # 列宽自适应

# 读取
sht.range('a1').row_height
sht.range('a1').column_width

获取及设置公式

# 写入公式
sht.range('c2').formula='=SUM(A1,A2)'

# 读取公式
sht.range('c2').formula

获取、设置及清除颜色格式

# 设置颜色
sht.range('c1').color = (255, 0, 120)

# 获取颜色
sht.range('c1').color

# 清除颜色
sht.range('c1').color = None

# 清除内容和格式
sht.range('c1').clear()

保存和关闭

wb.save()  # 保存工作簿
wb.close()  # 关闭工作簿
app.quit()  # 关闭工作区

xlwings与numpy、pandas、matplotlib互动

支持写入numpy array数据类型

import numpy as np
np_data = np.array((1,2,3))
sht.range('F1').value = np_data

支持将pandas DataFrame数据类型写入excel

import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
sht.range('A5').value = df

将数据读取，输出类型为DataFrame

sht.range('A5').options(pd.DataFrame,expand='table').value

将matplotlib图表写入到excel表格里

import matplotlib.pyplot as plt
%matplotlib inline
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
sht.pictures.add(fig, name='MyPlot', update=True)

xlwings与VBA互相调用

xlwings与VBA的配合非常完美，你可以在python中调用VBA，也可以在VBA中使用python编程，这些通过xlwings都可以巧妙实现。这里不对该内容做详细讲解，感兴趣的童鞋可以去xlwings官网学习。

参考文章

https://www.kancloud.cn/gnefnuy/xlwings-docs/1127454（中文文档）

https://docs.xlwings.org/en/stable/quickstart.html（官方文档）

https://zhuanlan.zhihu.com/p/82783751

https://juejin.cn/post/6876704014050787341

https://zhuanlan.zhihu.com/p/120415076

https://cloud.tencent.com/developer/article/1785671