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

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

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

Python办公自动化 Excel库功能对比 - 疯狂的小黑

关于xlwings

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

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

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

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

xlwings的工作原理

先看图

xlwings工作原理 - 疯狂的小黑
xlwings的工作原理图

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

xwwings的App原理 -疯狂的小黑

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

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

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

xlwings使用入门

xlwings安装和导入

如果你使用的是AnacondaWinPython,那么你可以直接import xlwings库使用

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

pip install xlwings

xlwings导入

import xlwings as xw

xlwings基础操作

建立excel表连接

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

打开方式xw.Bookxw.books
新建工作簿xw.Book()xw.books.add()
绑定已打开的工作簿xw.Book('Book1')xw.books[‘Book1’]
打开指定路径工作簿xw.Book(r'C:/path/to/file.xlsx')xw.books.open(r’C:/path/to/file.xlsx’😉
# 方法一
# 如果没有工作区,会新建一个工作区然后创建一个工作簿,如果有工作区,会在当前工作区创建工作簿
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

如果本站的内容对你有帮助,可以点击这儿,不花一分钱捐赠本站

(1)
疯狂的小黑的头像疯狂的小黑
上一篇 2022年8月22日 下午10:31
下一篇 2022年5月11日 上午10:46

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

微信