3.通过名称或索引（从0开始）获取工作表对象

python">sheet = book["2019"]
sheet = book.worksheets[0]

4.获取所有工作表名称的列表

python">print(book.sheetnames)

python">for i in book.worksheets:print(i.title)
'''
2019
2020
2019-2020'''

6.获取维度

python">print(sheet.max_row, sheet.max_column)'''
8 6'''

python">print(sheet["B6"].value)
print(sheet.cell(row=6, column=2).value)'''
Boston
Boston'''

excel%C2%A0%E6%A8%A1%E5%9D%97%E6%9D%A5%E8%AF%BB%E5%8F%96%E4%B8%80%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E5%8C%BA%E5%9F%9F%E7%9A%84%E5%80%BC" name="8.%E4%BD%BF%E7%94%A8%C2%A0excel%C2%A0%E6%A8%A1%E5%9D%97%E6%9D%A5%E8%AF%BB%E5%8F%96%E4%B8%80%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E5%8C%BA%E5%9F%9F%E7%9A%84%E5%80%BC">8.使用 excel 模块来读取一个单元格区域的值

excel 模块是自定义模块。

python">data = excel.read(book["2019"], (2, 2), (8, 6))
print(data)

python">data = excel.read(book["2019"], (2, 2), (8, 6))
print(data[:2])'''
[['Store', 'Employees', 'Manager', 'Since', 'Flagship'], ['New York', 10, 'Sarah', datetime.datetime(2018, 7, 20, 0, 0), False]]'''

9.使用 OpenPyXL 读取大型 Excel 文件

使用 read_only=True 参数来加载工作簿。指定文件应该以只读模式打开。在只读模式下，openpyxl不会尝试加载或修改文件的样式、图表等可能不需要的数据，这可以显著减少内存使用并加快加载速度。即使设置了read_only=True，你仍然可以对加载到内存中的数据进行修改，只是这些修改不会被写回到原始文件中。

由于 OpenPyXL 并不支持 with 语句，因此需要确保在工作完成时关闭文件。

如果你的Excel文件包含了对其他Excel文件的链接（例如，通过公式或数据连接），openpyxl在加载文件时会尝试解析这些链接。通过设置keep_links=False参数，你可以告诉openpyxl在加载文件时忽略这些链接。这可以加快读取速度，特别是当链接的外部工作簿很大或不可用时。需要注意的是，设置keep_links=False可能会导致与链接相关的数据或公式在加载后的工作簿中无法正确显示或计算。如果你只是需要读取某个工作簿中的值，并且不关心与链接相关的数据或公式，将keep_links设置为False通常是一个更好的选择。如果你确实需要保留对外部工作簿的引用（例如，你需要解析或计算与链接相关的公式），你可以将keep_links设置为True。

python"> book = openpyxl.load_workbook("xl/big.xlsx",data_only=True, read_only=True,keep_links=False)# 在这里执行所需读取操作book.close() # 需设置参数read_only=True

2.使用 OpenPyXL 写入文件

假设你正在使用 Word 编写一份报告。你在软件中输入文字、调整格式、插入图片等，所有这些操作都是在软件的内存中完成的，而不是直接在你的硬盘上修改文件。当你完成报告并决定保存时，软件会将内存中构建好的报告数据写入到你的硬盘上，形成一个新的或更新后的文件。这个过程与 OpenPyXL 操作 Excel 文件非常相似。在内存中构建文件的好处是，你可以随时撤销、重做或尝试不同的操作，而不必担心会立即影响到硬盘上的文件。当你满意你的操作结果后，再将其保存到硬盘上即可。

1.导入库

python">import openpyxl
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, colors
from openpyxl.styles.borders import Border, Side
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.fills import PatternFill
import excel

2.实例化工作簿：创建一个新的工作簿

python">book = openpyxl.load_workbook("xl/stores.xlsx", data_only=True)book = openpyxl.Workbook()

补充：编程中的实例化

类是一个模板，用于定义对象的属性和行为。而实例化是指根据这个类创建一个具体的对象。这个对象继承了类的所有属性和方法，并且可以被独立操作和使用。

Workbook 是 OpenPyXL 库中定义的一个类，book = openpyxl.Workbook() 这行代码就是实例化操作。

假设你有一个“汽车设计图纸”（编程中的类）。这个设计图纸定义了汽车的所有特征和功能，比如发动机、轮子、座位、方向盘等。

设计图纸（类）：就像 Workbook 类一样，它是一个模板，描述了汽车（或工作簿）应该如何构建。

制造汽车（实例化）：根据这个设计图纸，工厂会制造出一辆具体的汽车。这辆汽车拥有设计图纸中定义的所有特征和功能，但它是一个独立的实体，可以独立运行和使用。

book = openpyxl.Workbook() 就相当于工厂根据设计图纸制造了一辆汽车。book 就是这辆具体的汽车（或工作簿对象），你可以对它进行操作，比如添加工作表、填写数据等。

5.格式化：填充颜色、对齐、边框和字体

python">font_format = Font(color="FF0000", bold=True)
thin = Side(border_style="thin", color="FF0000")
sheet["A3"].value = "Hello 3"
sheet["A3"].font = font_format
sheet["A3"].border = Border(top=thin, left=thin,right=thin, bottom=thin)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A3"].fill = PatternFill(fgColor="FFFF00", fill_type="solid")

使用 OpenPyXL 库来格式化 Excel 工作表中的单元格 A3。

python">font_format = Font(color="FF0000", bold=True)

定义单元格文本的字体格式。

color="FF0000"：设置字体颜色为红色（使用 RGB 十六进制代码）。

bold=True：将字体设置为粗体。

python">thin = Side(border_style="thin", color="FF0000")

定义单元格边框的样式。

border_style="thin"：设置边框样式为细线。

color="FF0000"：设置边框颜色为红色。

python">sheet["A3"].value = "Hello 3"
sheet["A3"].font = font_format

设置单元格 A3 的值为字符串 "Hello 3"。

将单元格 A3 的字体格式设置为之前定义的 font_format 对象。

python">sheet["A3"].border = Border(top=thin, left=thin, right=thin, bottom=thin)

.border：是单元格对象的一个属性，用于设置或获取单元格的边框样式。通过给这个属性赋值，你可以改变单元格的边框样式。

Border 是OpenPyXL 中的一个类，用于定义单元格的边框样式。top=thin, left=thin, right=thin, bottom=thin：这些参数分别设置单元格的顶部、左侧、右侧和底部边框。thin 是一个 Side 对象，之前通过 Side(border_style="thin", color="FF0000") 创建。它定义了边框的样式为细线，并且颜色为红色。

python">sheet["A3"].alignment = Alignment(horizontal="center")

设置单元格 A3 的文本水平居中对齐。

python">sheet["A3"].fill = PatternFill(fgColor="FFFF00", fill_type="solid")

设置单元格 A3 的填充样式。

fgColor="FFFF00"：设置前景颜色为黄色（使用 RGB 十六进制代码）。

fill_type="solid"：将填充类型设置为实心，这样整个单元格会被黄色填充。

6.数字格式化（使用Excel的格式化字符串）

python">sheet["A4"].value = 3.3333
sheet["A4"].number_format = "0.00"

这行代码设置单元格 A4 的数值格式为 "0.00"。

.number_format 属性用于设置或获取单元格的显示格式。

"0.00" 是一个格式代码，指示 Excel 以两位小数的形式显示该数值。这意味着，尽管单元格中的实际值是 3.3333，它将在 Excel 中显示为 3.33，因为格式代码限制了小数位数为两位。

示例代码：

如果没有for循环的输出结果：

df.to_excel(writer) ：将DataFrame写入ExcelWriter指定的文件，默认从第一行第一列开始写入。

获取工作簿对象和工作表对象：
book = writer.book：通过ExcelWriter对象获取工作簿（Workbook）对象。
sheet = writer.sheets["Sheet1"] ： 通过 ExcelWriter 对象获取名为"Sheet1"的工作表对象。

nrows, ncols = df.shape：获取DataFrame的行数和列数。df.shape返回一个元组，其中第一个元素是行数，第二个元素是列数。print(df.shape)：(2, 2)

for row in range(nrows):  # 遍历DataFrame的每一行。该循环将遍历从0到nrows-1的整数序列。
for col in range(ncols):  # 遍历DataFrame的每一列。

注意：这里的 row + 2 和 col + 2 意味着数据从 Excel 的第3行第3列开始写入，即C3单元格开始。
cell = sheet.cell(row=row + 2, column=col + 2)  ：获取工作表中指定行和列的单元格对象。
cell.number_format = "0.000"  ： 设置单元格的数字格式为带有三位小数的数字。
cell.alignment = Alignment(horizontal="center")  ：设置单元格的水平对齐方式为居中。

row + 2 和 col + 2 意味着数据从 Excel 的第3行第3列开始写入，即C3单元格开始。但是为什么输出的文件中，B2,C2,B3,C3数据都格式化了？

补充：利用 pandas：通过 df.style 属性。

python">import pandas as pddf = pd.DataFrame({"col1": [1, -2], "col2": [-3, 4]},index=["row1", "row2"])
df.index.name = "ix"df.style.applymap(lambda x: "number-format: 0.000;""text-align: center").to_excel("styled.xlsx")

7.日期格式化（使用Excel的格式化字符串）

python">sheet["A5"].value = dt.date(2016, 10, 13)
sheet["A5"].number_format = "mm/dd/yy"

单元格 A5 将存储一个表示 2016 年 10 月 13 日的日期值。

sheet["A5"] 访问工作表 sheet 中的单元格 A5。.value 属性用于设置或获取单元格的值。

dt.date(2016, 10, 13) 使用 Python 的 datetime 模块创建一个日期对象。

.number_format 属性用于设置或获取单元格的显示格式。

"mm/dd/yy" 是一个格式代码，指示 Excel 以“月/日/年”的形式显示日期。

单元格 A5 中的日期将显示为 10/13/16，对应的格式是月/日/两位数的年份。

补充：利用 pandas 。

8.索引和标题格式化

亲自编写相应的代码。

代码逐步解释：

python">with pd.ExcelWriter("formatting_openpyxl.xlsx",engine="openpyxl") as writer:# 将整个df以默认格式从A1处写入df.to_excel(writer, startrow=0, startcol=0)

python">with pd.ExcelWriter("formatting_openpyxl.xlsx",engine="openpyxl") as writer:# 将整个df以默认格式从A1处写入df.to_excel(writer, startrow=0, startcol=0)# # 将整个df以默认格式从A6处写入startrow, startcol = 0, 5# 1.写入DataFrame的数据部分df.to_excel(writer, header=False,index=False,startrow=startrow + 1, startcol=startcol + 1)

python">sheet = writer.sheets["Sheet1"]
style = PatternFill(fgColor="D9D9D9", fill_type="solid")

sheet = writer.sheets["Sheet1"]：这行代码从 pd.ExcelWriter 上下文管理器中获取了名为 "Sheet1" 的工作表对象，并将其赋值给变量 sheet。在 pd.ExcelWriter 中，当您写入第一个 DataFrame 时，它会默认创建一个名为 "Sheet1" 的工作表（除非您通过 sheet_name 参数指定了不同的名称）。

style = PatternFill(fgColor="D9D9D9", fill_type="solid")：这行代码创建了一个 PatternFill 对象，并将其赋值给变量 style。PatternFill 是 openpyxl 库中用于设置单元格填充样式的类。fill_type="solid" 指定了填充类型为实色填充。

python">for i, col in enumerate(df.columns):sheet.cell(row=startrow + 1, column=i + startcol + 2,value=col).fill = style

enumerate(df.columns)：enumerate() 是一个内置函数，它用于将一个可遍历的数据对象（如列表、元组或字符串）组合为一个索引序列，同时列出数据和数据下标。print(df.columns)：Index(['col1', 'col2'], dtype='object')。enumerate(...)：这个函数接收 df.columns 作为输入，并返回一个迭代器。每次迭代都会返回一个元组，包含两个元素：当前列名的索引（从0开始）和列名本身。

sheet.cell(row=startrow + 1, column=i + startcol + 2, value=col).fill = style：

sheet.cell(...)：获取工作表 sheet 中的特定单元格。.fill = style：应用填充样式到单元格。

value=col：将列名设置为单元格的值。如果没有value=col就是下面这个效果。

python">index = [df.index.name if df.index.name else None] + list(df.index)
for i, row in enumerate(index):sheet.cell(row=i + startrow + 1, column=startcol + 1,value=row).fill = style

将DataFrame的索引名写入Excel工作表，并为这些单元格应用一个特定的样式。

9.在 Excel 工作表的单元格中设置公式：必须使用以逗号分隔的英文公式名称

python">sheet["A6"].value = "=SUM(A4, 2)"

=SUM(A4, 2) 是一个 Excel 公式，被赋值给单元格 A6。

在这个公式中，SUM(A4, 2) 的作用是计算单元格 A4 的值加上数字 2。

10.在 Excel 工作表中的特定单元格插入一张图片

python">sheet.add_image(Image("images/python.png"), "C1")

.add_image() 是一个方法，用于在工作表中插入图片。

Image("images/python.png") 创建一个图片对象，这个对象表示磁盘上的一张图片文件。

"C1" 是 Excel 工作表中的目标单元格，图片将被插入到这个单元格的起始位置。

为什么插入的图片不在C1这个单元格，而是自由移动的状态？

在 Excel 中，插入的图片、形状和其他对象是作为“浮动”对象处理的。它们不依赖于单元格进行定位，而是可以在工作表上自由移动和调整。虽然可以指定一个起始单元格（如 C1），但这只是设置图片的初始锚点位置。图片本身仍然可以独立于单元格进行移动和调整。

data 是一个包含数据的二维列表：

第一行：[None, "North", "South"]，其中 None 表示该单元格可能为空。

第二行：["Last Year", 2, 5]

第三行：["This Year", 3, 6]

excel.write(sheet, data, "A10") 是将 data 列表中的内容写入到工作表 sheet 中，起始位置为单元格 A10。

12.图表

python">chart = BarChart()
chart.type = "col"
chart.title = "Sales Per Region"
chart.x_axis.title = "Regions"
chart.y_axis.title = "Sales"
chart_data = Reference(sheet, min_row=11, min_col=1,max_row=12, max_col=3)
chart_categories = Reference(sheet, min_row=10, min_col=2,max_row=10, max_col=3)
chart.add_data(chart_data, titles_from_data=True, from_rows=True)
chart.set_categories(chart_categories)
sheet.add_chart(chart, "A15")

python">chart = BarChart()
chart.type = "col"
chart.title = "Sales Per Region"
chart.x_axis.title = "Regions"
chart.y_axis.title = "Sales"

chart = BarChart()：创建一个 BarChart 对象实例。默认创建一个垂直柱状图。

chart.type = "col"：设置图表类型为柱状图。在创建 BarChart 时默认就是柱状图，但这一行明确指定了图表类型。

chart.title = "Sales Per Region"：设置图表的标题为 "Sales Per Region"。这将显示在图表顶部。

chart.x_axis.title = "Regions"：设置 X 轴的标题为 "Regions"。这表示数据水平轴的分类标签。

chart.y_axis.title = "Sales"：设置 Y 轴的标题为 "Sales"。这表示数据垂直轴的数值。

python">chart_data = Reference(sheet, min_row=11, min_col=1,max_row=12, max_col=3)

chart_data 引用了 Excel 表格中 A11:C12 这个区域的数据。

Reference：openpyxl.chart.reference.Reference 是用来定义 Excel 图表的数据范围的类。

sheet：表示引用的数据所在的工作表对象。

min_row=11, min_col=1：表示引用范围的起始单元格，从第 11 行的第 1 列开始（即 A11 单元格）。

max_row=12, max_col=3：表示引用范围的结束单元格，到第 12 行的第 3 列结束（即 C12 单元格）。

chart_data：保存了指定的单元格范围，最终这个范围会用作图表的数据源。

python">chart_categories = Reference(sheet, min_row=10, min_col=2,max_row=10, max_col=3)

因此，chart_categories 引用了 Excel 表格中 B10:C10 这个区域的数据。

min_row=10, min_col=2：表示引用范围的起始单元格，从第 10 行的第 2 列开始（即 B10 单元格）。

max_row=10, max_col=3：表示引用范围的结束单元格，到第 10 行的第 3 列结束（即 C10 单元格）。

chart_categories：保存了指定的单元格范围，通常用作图表的分类轴（如 X 轴的标签）。

python">chart.add_data(chart_data, titles_from_data=True, from_rows=True)
chart.set_categories(chart_categories)
sheet.add_chart(chart, "A15")

chart.add_data(chart_data, titles_from_data=True, from_rows=True)：将数据添加到图表中。

补充：titles_from_data 和 from_rows 参数

假设数据如下：

titles_from_data=True 表示从数据中提取标题（通常是第一行），用作系列的名称。from_rows=True 表示数据是按行组织的。

titles_from_data=True, from_rows=True：

图表会从第 11 行提取标题，即 "X", "10", "20"，并将它们用作系列名称或分类。每个系列对应一行数据，例如，一个系列名为 X，其值为 10, 20。

titles_from_data=True, from_rows=False：

图表会从第 1 列提取标题，即 "X", "Y"，并将它们用作分类轴标签。每个系列对应一列数据，例如，一个系列名为 B，其值为 10, 30。

chart.set_categories(chart_categories)：设置 X 轴标签。chart_categories 包含类别的数据。

sheet.add_chart(chart, "A15")：将图表添加到工作表中，并将其放置在单元格 A15。这指定了图表在工作表中的起始位置。

13.保存工作簿在磁盘上创建文件

python">book.save("openpyxl.xlsx")

13.使用 OpenPyXL 写入大型文件：逐行写入

python">book = openpyxl.Workbook(write_only=True)sheet = book.create_sheet()
# 生成一张包含1000x200个单元格的工作表
for row in range(1000):sheet.append(list(range(200)))
book.save("openpyxl_optimized.xlsx")

book = openpyxl.Workbook(write_only=True)：创建一个新的工作簿对象book。

sheet = book.create_sheet()：在工作簿book中创建一个新的工作表，并将其赋值给变量sheet。默认情况下，新创建的工作表会被命名为"Sheet"。

for row in range(1000)：这个循环将运行1000次，每次循环代表工作表中的一行。

sheet.append(list(range(200)))：在循环内部，使用 append 方法向工作表 sheet添加一行数据。list(range(200))生成一个包含0到199的整数列表，这个列表被作为一行数据添加到工作表中。因此，每行都将包含200个单元格，单元格的值从0递增到199。

3.使用 openpyxl 库创建一个 Excel 模板文件（.xltx 格式）

.xltx 是 Excel 模板文件的扩展名。保存为这种格式的文件可以在 Excel 中作为模板使用，用户可以基于此模板创建新的工作簿。

4.使用 OpenPyXL 编辑文件

OpenPyXL 首先会读取所有它可以理解的数据，然后再将文件数据从头到尾写回去，其间你做出的所有更改也会包含其中。

如果你的工作表中包含图表或者其他高级内容，那么 OpenPyXL 的功能就显得非常有限了，这些内容要么会被修改，要么会被直接丢弃。例如，在 OpenPyXLv3.0.5 版本中，图表会被重命名且标题会被丢弃。

1.编辑 Excel 文件的示例