21.Excel自动化：如何使用 xlwings 进行编程

一 将Excel用作数据查看器

1.导包

import datetime as dt
import xlwings as xw
import pandas as pd
import numpy as np

2.view 函数

创建一个基于伪随机数的DataFrame，它有足够多的行，使得只有首尾几行会被显示。

df = pd.DataFrame(data=np.random.randn(100, 5),columns=[f"Trial {i}" for i in range(1, 6)])
print(df)

xw.view(df)

view 函数可以接受所有常见的 Python 对象，包括数字、字符串、列表、字典、元组、NumPy 数组和 pandas DataFrame。

在默认情况下，它会打开一个新的工作簿，然后将对象粘贴到第一张工作表的 A1 单元格。

不必每次都打开一个新的工作簿，你也可以通过为 view 函数提供一个 xlwings sheet 对象作为第二个参数来重复利用同一个工作簿文件：xw.view(df, mysheet)。

自动打开，未展示完全。 

二 xlwings 的使用方法

1.xw.Book() 的不同用法

1.xw.Book()：创建新工作簿

wb = xw.Book() 和 wb = app.books.add() 都是新建一个工作簿，有什么区别？

在 xlwings 中，xw.Book() 和 app.books.add() 都可以用于创建新工作簿，但它们在底层实现和使用场景上有以下区别。

xw.Book()：如果没有活动的Excel实例，它会启动一个新的，然后新建一个工作簿。如果有活动的实例，比如用户已经手动打开了Excel，那么它会在这个实例中新建工作簿。无需显式创建 App 对象，适合快速操作。

app.books.add()：需要先有一个app对象，通过xw.App()创建。在指定的 app 实例中新建工作簿。适合需要同时操作多个 Excel 实例或工作簿的场景。

xw.Book()更自动化，适合简单场景；app.books.add()更明确，适合需要精细管理的情况。

补充：如果你在两个 Excel 实例中打开了同一个工作簿，或者想要指定某个 Excel 实例打开某个工作簿，就不能再使用 xw.book 了。此时需要使用 books 集合。

.books方法

xw.apps[key].books表示 Excel 实例中所有打开的工作簿集合。

访问工作簿：

通过索引：app.books[0] 获取第一个工作簿。

通过名称：app.books['Book1.xlsx'] 按名称获取。

myapp 代表一个 xlwings app 对象。（myapp=xw.App()）如果将 myapp.books 替换成 xw.books，则 xlwings 会使用活动的 app。

2.xw.Book("Book1")：引用未保存的未命名工作簿

 xw.Book() 和 xw.Book("Book1") 的区别？

xw.Book()：创建一个全新的 Excel 工作簿。

如果当前没有打开的 Excel 实例，xlwings 会自动启动一个。新工作簿默认命名为 Book1（如果未指定其他名称）。该工作簿不会自动保存，需手动调用 .save() 方法。

xw.Book("Book1")：尝试打开已存在的名为 "Book1" 的工作簿。

如果名为 "Book1" 的工作簿已打开，直接返回该工作簿的引用。如果名为 "Book1" 的工作簿未打开但存在（即文件存在于磁盘），xlwings 会打开它。如果文件不存在，会抛出 FileNotFoundError。

3.xw.Book("Book1.xlsx")：引用已保存的文件 

路径也可以是绝对路径。

 4.xw.books.active：获取活动工作簿

返回当前 Excel 实例中活动的工作簿对象，直接获取用户当前正在操作的 Excel 工作簿对象。

2.xw.sheets[ ]：访问工作表集合

返回工作簿中所有工作表的集合（类似列表）。

1.通过名称或索引访问特定工作表

2.遍历所有工作表

3.添加工作表

4.删除工作表

5.检查工作表是否存在

3.xw.range（）的不同用法

.range方法是用来操作Excel单元格区域的。

1.对单个单元格

1.通过单元格名字操作单元格

补充：.value方法。

.value 是 Range 对象的属性。

读取：

cell_value = sheet.range("A1").value
print(cell_value)

写入：

sheet.range("A1").value = "New Value"

sheet1.range("A1").value = [[1, 2], [3, 4]] 这行代码是什么意思，A1这一个单元格里面的数据是[1, 2], [3, 4]吗？

自动扩展写入范围：虽然你指定了 A1 这个单个单元格，但当你给一个单元格赋值一个二维列表（如 [[1,2], [3,4]]）时，xlwings 会自动将数据扩展到相邻的单元格区域。数据会被写入到 A1:B2 的区域。这个A1相当于是指定从A1单元格开始写入数据而不是写入A1单元格。赋予二维列表（如 [[1,2], [3,4]]），xlwings 会将其视为多行多列的数据，并自动扩展写入到连续的单元格区域。

2.通过行列索引操作

2.对多个单元格（范围）

1.命名范围操作

2.批量操作整行/整列

补充：索引和切片。

3.公式操作

4.多区域操作

3.range 对象的 options 方法

对应：5.Pandas ：DataFrame 的使用——数据操作→9.将 DataFrame 写入 Excel

range.options() 方法，是用于动态控制 Excel 数据读写时的值处理方式的。只有在调用 value 属性时，options 的设置才会生效。

options() 本身不会立即处理数据，它只是定义了一组规则。当调用 .value 时，xlwings 会根据 options 的规则，对 range 区域的数据进行处理，返回结果。

options() 用于定义读取或写入数据时，值的转换规则。

常见的选项包括：

convert：控制数据类型转换（如将 Excel 中的数字文本转为 Python 的整数）。

dates：处理日期格式（如将 Excel 的日期序列转为 Python 的 datetime 对象）。

numbers：控制数字的解析方式（如科学计数法、千分位分隔符）。

补充：convert 参数可以接受的值。

4.对 sheet 对象进行索引和切片来获得 range 对象

补充：对 sheet 对象进行索引 / 切片和使用range 对象之间的区别。

sheet 对象在 xlwings 中代表 Excel 的工作表。用户可能通过sheet[行, 列]或者sheet[:行, :列]这样的索引或切片方式来操作单元格。而range对象则是通过sheet.range("A1")或者sheet.range("A1:B2")。

5.从 0 开始的索引和从 1 开始的索引的说明

作为一个 Python 包，只要你通过 Python 的索引或切片语法（通过方括号）访问元素，xlwings 就始终使用从 0 开始的索引。但是 xlwings 的 range 对象使用的是从 1 开始的行列索引。那么可以使用 sheet[row_selection, column_selection] 语法。

6.xw.name（）和 xw.fullname（）

xw.name：返回 Excel 应用程序的简短名称（通常是 "Microsoft Excel"）。

示例输出："Microsoft Excel"

xw.fullname：返回 Excel 应用程序的完整名称（包含版本信息，如 "Microsoft Excel 365"）。

示例输出："Microsoft Excel 365"

7.xw.app（）的不同用法

.app是指Excel应用程序实例的接口。通过xw.App()来启动Excel实例，或者使用现有的实例。

.app 用于控制 Excel 应用程序级别的操作（如可见性、屏幕更新、计算模式等）。

1.启动/关闭 Excel 实例

1.启动新实例

默认可见。

2.连接已打开的 Excel 实例

3.从打开的工作簿中获取app对象 

2.控制 Excel 的行为

3.打开/操作/保存并关闭工作簿

1.打开工作簿

2.操作工作簿

1.列出各实例中打开的工作簿名称

通过列表推导式。

 2.写入值

3.保存并关闭工作簿

4.获取 app 的键

App 对象代表一个 Excel 应用程序实例，可以获取其唯一标识符（即“键”）。

1.app.pid

返回 Excel 进程的唯一数字标识符（Process ID），由操作系统分配。

2.app.hwnd 

返回窗口的唯一标识符（仅 Windows 有效）。

补充：xw.apps.keys()

app.pid 和 app.hwnd 是App对象的属性。

xw.apps.keys()会返回所有正在运行的Excel实例的进程ID列表。

xw.apps是一个类似字典的对象，其中每个键对应一个Excel实例的标识符，而值则是App对象本身。所以xw.apps.keys()应该返回所有当前运行的Excel实例的键（它们的标识符）。 

5.退出 Excel 实例

6..caller() 

如果你通过 Excel 中的 VBA 宏、按钮或其他方式运行 Python 代码，.caller() 会返回调用代码时正在运行的 Excel 应用程序实例。

返回一个 xlwings 的 App 对象，使你可以在 Python 中直接操作触发代码的 Excel 应用程序。

8.xw.save（）和 xw.close（）

三 xlwings 的高级用法

1.批量操作工作表

2.创建 Excel 图表

charts 集合的 add 方法。

向 Excel 工作表插入一个新的图表。返回一个 Chart 对象，代表新添加的图表，可以通过该对象进一步配置图表（如设置类型、数据源等）。

top=sheet1["A19"].top：图表的顶部位置与单元格 A19 的顶部对齐。

left=sheet1["A19"].left：图表的左侧位置与单元格 A19 的左侧对齐。

图表会被放置在单元格 A19 的位置，覆盖该单元格。

chart.chart_type = "column_clustered"：

将图表的类型设置为簇状柱形图（一种常见的柱状图类型）。

chart.set_source_data(sheet1["A15"].expand())：设置图表数据源

sheet1["A15"].expand()：

从单元格 A15 开始，自动扩展选择连续的、非空的数据区域。

set_source_data：

将上述扩展后的数据区域设置为图表的数据源，图表会根据这些数据生成对应的柱状图。

补充：其他例子

3.结合 Matplotlib

将 Matplotlib 图像放入 Excel 中。

在处理图片时，一定要确保安装了 Pillow，它是 Python 中常用的图片处理库。Pillow 能够保证图片在 Excel 中有正确的尺寸和比例。Anaconda 中包含了 Pillow。

当使用 pandas 的默认绘图后端时，创建的是一张 Matplotlib 的图像。

要将这样的图 像放进 Excel 中，首先要获取它的 figure 对象，然后将其作为参数传递给pictures.add， pictures.add 会将 Matplotlib 图像转换为图片然后发送至 Excel。pictures.add 方法是 xlwings 库中的一个功能，用于将图片添加到 Excel 工作表中。

pictures.add ：将图片（如生成的图表）插入到 Excel 工作表中。返回一个 Picture 对象，代表插入的图片，可以通过该对象进一步配置图片（如调整位置、大小等）。

除了接受 Matplotlib 图像， pictures.add 也可以接受磁盘上的图片路径。

image：要插入的图片对象（如 matplotlib 的 Figure 对象）。

name（可选）：图片的名称（字符串）。

update（可选）：如果为 True，更新现有同名图片；否则添加新图片（默认 False）。

left（可选）：图片左侧距离工作表左侧的位置（单位：点）。

top（可选）：图片顶部距离工作表顶部的位置（单位：点）。

width（可选）：图片的宽度（单位：点）。

height（可选）：图片的高度（单位：点）。

代码举例：

%matplotlib inline：这是一个魔术命令，用于在 Jupyter Notebook 中直接显示 matplotlib 生成的图表。图表会内嵌在 Notebook 的单元格下方，而不是弹出一个独立的窗口。

plt.style.use("seaborn")：设置 matplotlib 的图表风格为 "seaborn"。seaborn 是一种现代、美观的统计图表风格，由同名库 seaborn 提供。使用此风格后，图表的网格、颜色、字体等视觉元素会自动调整为 seaborn 的默认样式。

ax = df.T.plot.bar()：生成一个柱状图，并返回 Axes 对象。

df.T：对 DataFrame df 进行转置（行和列交换）。转置后，原来的行索引（Last Year 和 This Year）会变成列名，原来的列名（North 和 South）会变成行索引。转置是为了调整图表的方向，使柱状图的分组更直观。

.plot.bar()：调用 pandas 的绘图方法生成柱状图。

返回的 ax 是一个 Axes 对象，代表图表的坐标轴和绘图区域。

补充：Figure 对象和 Axes 对象

在 matplotlib 中 Figure 对象代表整个图表（包括所有子图、标题、图例等元素）。Axes 对象代表图表中的一个子图或坐标轴区域。

一个 Figure 对象可以包含多个 Axes 对象（例如，通过 plt.subplots() 创建多个子图）。

fig = ax.get_figure()：从 Axes 对象中获取关联的 Figure 对象。

.get_figure() ：从 Axes 对象中获取其所属的 Figure 对象。

保存图表：通过 Figure 对象可以保存整个图表为文件（如 fig.savefig("chart.png")）。

调整图表布局：通过 Figure 对象可以调整图表的全局布局（如 fig.tight_layout()）。

获取图表属性：通过 Figure 对象可以访问图表的尺寸、分辨率等属性。

补充：.get_figure() 方法是 matplotlib 中用于从 Axes 对象获取其所属 Figure 对象的关键方法。通过 Figure 对象，用户可以执行保存图表、调整布局等高级操作。

补充：想使用新的图像来更新图片

只需调用 update 方法并传递另一个 figure 对象即可。虽然这样做会替换 Excel 中的图片，但会保留位置、尺寸、名称等属性。

(df + 1)：对 DataFrame df 中的每个元素加 1。

转置后：

.plot.bar()：调用 pandas 的绘图方法生成柱状图。返回的 ax 是一个 Axes 对象，代表图表的坐标轴和绘图区域。

plot = plot.update(ax.get_figure())：用新的 Figure 对象更新已有的图表对象。

4.已定义名称集合

1.什么是已定义名称

在 Excel 中，我们通过为区域、公式和常量赋予名称来创建已定义名称。为一个区域命名是常见情况，这种区域被称作具名区域。利用具名区域，你可以在公式和代码中使用描述性名称而不是抽象地址（A1:B2）来引用一个 Excel 区域。

补充：

具名区域：给某个单元格或单元格区域赋予一个名称（如 SalesData），方便在公式或图表中引用。

全局作用域：名称在整个工作簿中唯一，任何工作表都能引用。

工作表作用域：名称仅在当前工作表中唯一，复制到其他工作表时会自动重命名。

利用具名区域读写值可以在不调整 Python 代码的情况下重新组织工作簿。比如，即使插入新行导致了单元格的移动，但对应的名称仍然引用的是原来的单元格。自定义名称可以在全局工作簿作用域或局部工作表作用域中设置。

工作表作用域的优势是在复制工作表时不用担心重复的具名区域发生冲突。

什么是工作表作用域的优势是在复制工作表时不用担心重复的具名区域发生冲突？

假设你有一个工作表 Sheet1，其中定义了一个全局作用域的具名区域 SalesData。当你复制 Sheet1 到 Sheet2 时：Sheet2 中也会出现一个名为 SalesData 的具名区域。如果此时你尝试在公式或图表中引用 SalesData，软件会不知道引用的是 Sheet1 还是 Sheet2 中的 SalesData，导致冲突。

在 Excel 中，你可以在“公式 > 定义名称”菜单项中添加自定义名称。也可以选择一个区域，然后将想要的名称写到名称框（公式栏左边的文本框）中，你可以在这里看到默认的单元格地址。

2.使用 xlwings 创建自定义名称的方法

默认作用域是工作簿作用域。

sheet1["A1:B2"].name = "matrix1"

4.名称有多种方法和属性

1.获取对应的range对象

book.names["matrix1"].refers_to_range

获取工作簿中名为 matrix1 的具名区域所引用的具体单元格范围。

book 是工作簿对象（如 Excel 文件）。book.names 是工作簿中所有具名区域的集合，包含工作簿作用域和工作表作用域的具名区域。通过名称 matrix1 从 book.names 集合中获取特定的具名区域对象。

.refers_to_range：是具名区域对象的一个属性，返回该具名区域所引用的具体单元格范围（Range 对象）。例如，如果 matrix1 引用的是 Sheet1!A1:B2，那么 .refers_to_range 会返回这个范围。

2.为常量或公式取名

使用add方法。

book.names.add("EURUSD", "=1.1151")

"EURUSD"：是新具名区域的名称，遵循命名规则（如不能以数字开头，不能包含空格等）。

=1.1151"：如果直接赋值（如 =1.1151），表示该具名区域的值是 1.1151。如果引用单元格（如 =Sheet1!A1），表示该具名区域的值是 Sheet1!A1 单元格的值。

5.通过 Python 来运行 VBA 宏

如果你手上有一些包含大量 VBA 代码的旧式 Excel 项目，那么要将所有东西都迁移到 Python 并非易事。在这种情况下，可以通过 Python 来运行 VBA 宏。

步骤2：绑定 VBA 函数

vba_book.macro( ) 将 VBA 函数 MySum 绑定到 Python 变量 mysum。此时 mysum 像一个 Python 函数，可直接调用。

调用：

传递参数 5 和 4 给 VBA 函数 MySum ，VBA 执行 x + y 计算，返回结果 9.0。

步骤3：绑定 VBA 子程序

同理，将 VBA 子程序 ShowMsgBox 绑定到 show_msgbox。子程序无返回值，但会执行操作（如弹窗）。

调用：

传递字符串参数 "Hello xlwings!"。VBA 执行 MsgBox 弹出消息框，需手动关闭后才能继续。

步骤4：关闭工作簿

关闭book对象（一定要先关闭对话框）。若子程序弹出对话框未关闭，直接关闭工作簿会报错。