一 OpenPyXL 和 XlsxWriter
想写入 xlsx 或者 xlsm 文件,就需要在 OpenPyXL 和 XlsxWriter 中做出选择。
OpenPyXL 既可以读也可以写 Excel 文件的包。可以用它来编辑一些简单的Excel 文件。
XlsxWriter 使用的是从 0 开始的单元格索引, 而 OpenPyXL 使用的是从 1 开始的单元格索引,一定要在切换两个包时考虑到这一点。
和 OpenPyXL 相比,XlsxWriter 在写入 xlsm 文件时必须采用一种更复杂的方法。
数据的格式化能达到何种程度取决于你所使用的是哪种包:如果使用的是 pandas 的 to_excel 方法,那么 OpenPyXL 可以为每一个单元格应用一种格式,而 XlsxWriter 只能对行或列应用格式。
1.这两个包有相似的功能,但是各自又有一些对方所没有的特性
pandas%20%E9%BB%98%E8%AE%A4%E4%BD%BF%E7%94%A8%20XlsxWriter" name="2.%E5%A6%82%E6%9E%9C%E5%90%8C%E6%97%B6%E5%AE%89%E8%A3%85%E4%BA%86%20OpenPyXL%20%E5%92%8C%20XlsxWriter%EF%BC%8C%E9%82%A3%E4%B9%88%20pandas%20%E9%BB%98%E8%AE%A4%E4%BD%BF%E7%94%A8%20XlsxWriter">2.如果同时安装了 OpenPyXL 和 XlsxWriter,那么 pandas 默认使用 XlsxWriter
如果你想亲自选择 pandas 所使用的包,则可以在 read_excel 或 to_excel,以及 ExcelFile 或 ExcelWriter 的 engine 参数中指定所选包。engine(引擎) 是小写的包名,因此如果要用 OpenPyXL 而不是 XlsxWriter 来写文件,则需要执行如下代码:
二 OpenPyXL 和 xlrd、xlwt 、xlutils
OpenPyXL 可以为 xlsx 格式提供读、写和编辑的功能。如果将 xlrd、xlwt 和 xlutils 结合起来,它们也可以为旧式 xls 格式的文件提供类似的功能。xlrd 读、xlwt 写和 xluils 编辑 xls 文件。
三 为什么要调整 DataFrame 在 Excel 中的格式
1.提高可读性
通过设置单元格样式(如字体、颜色、边框等)、列宽和行高,以及数据格式(如日期、货币、百分比等),可以使数据更加清晰易读。
2.突出重点信息
在数据报告中,通过设置DataFrame的格式,可以突出显示关键指标或异常数据,使报告更具吸引力和可读性。数据筛选和高亮显示功能也可以帮助用户快速定位到感兴趣的数据。
四 如何使用OpenPyXL
1.使用 OpenPyXL 读取文件
1.导入库
python">import pandas as pd
import openpyxl
import excel
import datetime as dt
2.打开工作簿
python">book = openpyxl.load_workbook("xl/stores.xlsx", data_only=True)
print(book)
要获得单元格的值,需要使用 data_only=True 参数来打开工作簿,其默认值是 False,此时会返回单元格的公式而不是值。
输出结果:
这段输出确认了你已经成功使用 openpyxl.load_workbook
函数加载了一个 Excel 工作簿。
data_only=True
参数表示加载工作簿时,将只读取公式计算后的值,而不是公式本身。在 Excel 中,单元格可以包含公式(如 =SUM(A1:A10)
),这些公式会计算出结果并显示在单元格中。当使用 data_only=True
加载工作簿时,openpyxl
会忽略单元格中的公式,只读取并返回公式计算后的值。即使单元格中包含公式,你访问该单元格时也会得到公式已经计算好的结果,而不是公式本身。
这个 book
对象包含了工作簿中的所有工作表和数据,你可以通过它进一步访问和操作这些数据。
print(book)为什么没有打印出工作簿中的所有工作表和数据?
print(book)
语句打印的是book
对象的内存地址,而不是工作簿中的内容。openpyxl
的Workbook
对象本身并不直接包含工作簿数据的字符串表示形式,而是提供了方法和属性用于访问这些数据。
3.通过名称或索引(从0开始)获取工作表对象
python">sheet = book["2019"]
sheet = book.worksheets[0]
4.获取所有工作表名称的列表
python">print(book.sheetnames)
5.遍历所有工作表对象
2019
、2020
和 2019-2020
。
python">for i in book.worksheets:print(i.title)
'''
2019
2020
2019-2020'''
6.获取维度
python">print(sheet.max_row, sheet.max_column)'''
8 6'''
7.读取单个单元格的值,分别使用的是A1这种字符串表示法,以及单元格索引(从1开始)
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
就是这辆具体的汽车(或工作簿对象),你可以对它进行操作,比如添加工作表、填写数据等。
3.创建第一张工作表并赋予它一个名称
python">sheet = book.active
sheet.title = "Sheet1"
book.active:获取当前工作簿中活动的工作表。这行代码将活动工作表对象赋值给变量 sheet
,之后你就可以通过 sheet
变量来操作这个工作表了。
4.使用A1表示法和单元格索引 (从1开始)写入各个单元格
python">sheet["A1"].value = "Hello 1"
sheet.cell(row=2, column=1, value="Hello 2")
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"
python">sheet["A4"].value = 3.3333
这行代码将单元格 A4
的值设置为浮点数 3.3333
。通过这行代码,单元格 A4
将显示数字 3.3333
。
sheet["A4"]
访问工作表 sheet
中的单元格 A4
。.value
属性用于设置或获取单元格的值。
python">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"
python">sheet["A5"].value = dt.date(2016, 10, 13)
单元格 A5
将存储一个表示 2016 年 10 月 13 日的日期值。
sheet["A5"]
访问工作表 sheet
中的单元格 A5
。.value
属性用于设置或获取单元格的值。
dt.date(2016, 10, 13)
使用 Python 的 datetime
模块创建一个日期对象。
python">sheet["A5"].number_format = "mm/dd/yy"
.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
),但这只是设置图片的初始锚点位置。图片本身仍然可以独立于单元格进行移动和调整。
excel%E6%A8%A1%E5%9D%97%EF%BC%89" name="10.%E4%BA%8C%E7%BB%B4%E5%88%97%E8%A1%A8%EF%BC%88%E4%BD%BF%E7%94%A8excel%E6%A8%A1%E5%9D%97%EF%BC%89">11.二维列表(使用excel模块)
python">data = [[None, "North", "South"],["Last Year", 2, 5],["This Year", 3, 6]]
excel.write(sheet, data, "A10")
data
写入到 Excel 工作表的指定位置。
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 写入大型文件:逐行写入
一定要安装好 lxml 包, lxml 可以让写入过程更迅速。Anaconda 中已经包含了这个包。
最关键的选项是 write_only=True ,它可以让内存消耗保持在较低的水平。这种模式下,openpyxl
不会将整个工作簿加载到内存中,而是逐行写入数据,从而减少了内存消耗。不能像通常那样通过指定行和列索引来写入单个单元格。设置 write_only=True 参数后 book.active 就不可用了。
补充:在最新版本的
openpyxl
中,Workbook
构造函数不再接受write_only
参数。
append
方法是openpyxl
中用于向工作表添加一行数据的方法。当你使用append
方法时,你传递一个列表(或类似列表的结构),其中的每个元素对应一行中的一个单元格。append
方法会自动将这一行添加到工作表的末尾。当 write_only=True
模式被启用时,通常只能使用 append
方法来写入数据,因为其他写入方式(如直接设置单元格值)会因为内存限制而不可行。
例如,你不能使用worksheet['A1'] = 'Hello'
这样的语法来设置单元格的值。相反,你只能使用append
方法(或类似的方法)来逐行添加数据。
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 文件的示例
2..xlsm
格式的文件处理
如何加载和修改一个包含 VBA 宏的 Excel 文件(.xlsm
格式),并在保存时保持其 VBA 宏功能。
使用 openpyxl.load_workbook()
加载一个已经存在的 Excel 文件 xl/macro.xlsm
。
keep_vba=True
参数告诉 openpyxl
在加载文件时要保留其 VBA 宏。因为在默认情况下openpyxl
不会加载或保留 VBA 代码。
.xlsm
文件格式是 Excel 启用宏的工作簿,可以包含 VBA 代码。
openpyxl
只能加载和保存包含 VBA 的文件,但不能创建或修改 VBA 代码。
补充:VBA 宏功能
VBA 宏功能是一种自动化工具,它允许用户通过编写代码来自动执行一系列在 Microsoft Office(如 Excel、Word、Access 等)中的操作。
宏是一个自动化任务的脚本,通过编写VBA代码创建。
5.编写将 pandas 与 OpenPyXL 结合使用的代码
1.读取文件的过程中将 pandas 和 OpenPyXL 搭配使用
这些示例中使用了 OpenPyXL,但是对于其他包来说在概念上也是一样的。
使用 pandas 的 ExcelFile
对象打开文件。
book = xlfile.book
这行代码试图从一个 pd.ExcelFile
对象中获取一个 OpenPyXL 工作簿对象。pd.ExcelFile
是 Pandas 中用于表示 Excel 文件的一个上下文管理器类,但它本身并不直接暴露 OpenPyXL 的工作簿对象。
xlfile
在这个上下文中是一个 pandas.ExcelFile
对象,而不是一个 openpyxl.Workbook
对象。pandas.ExcelFile
对象主要用于与 Pandas 的 read_excel
函数交互,以读取 Excel 文件中的数据。
xlfile.book
是无效的,因为pd.ExcelFile
对象没有book
属性。但是没有报错。不知道为什么。openpyxl库中的Workbook类有book属性,但通常我们不直接这样引用。实际上,在openpyxl中,当我们创建一个新的工作簿或加载一个现有的工作簿时,我们通常直接操作Workbook对象本身,而不是通过某个名为“book”的属性来间接访问它。
第8章152页。
pandas库和openpyxl是pandas包含的关系吗?
Pandas库和OpenPyXL库并不是包含关系,是两个独立的Python库,各自提供不同的功能。
为什么上面代码没有导入 openpyxl 库但是使用了 openpyxl 库的方法?
pandas
库在内部使用了openpyxl
(上面代码指定了engine="openpyxl"
)来读取 Excel 文件。这是pandas
和openpyxl
之间的一个协作点。当调用pd.ExcelFile
并指定engine="openpyxl"
时,pandas
会检查openpyxl
是否已安装,并在内部使用它来解析 Excel 文件。这意味着您不需要在脚本中显式地导入openpyxl
,因为pandas
会为您处理这一点。
sheet = book["2019"]
这行代码的意思是尝试从名为 book
的 openpyxl.Workbook
对象中获取名为 "2019"
的工作表,并将其赋值给变量 sheet
。如果 book
是一个有效的 Workbook
对象,并且包含名为 "2019"
的工作表,那么这行代码将成功执行,并且 sheet
将引用该工作表。
2.在写入工作簿时将 pandas 和 OpenPyXL 搭配使用
python">import pandas as pdwith pd.ExcelWriter("pandas_and_openpyxl.xlsx",engine="openpyxl") as writer:df = pd.DataFrame({"col1": [1, 2, 3, 4], "col2": [5, 6, 7, 8]})# 写入DataFramedf.to_excel(writer, "Sheet1", startrow=4, startcol=2)# 获取OpenPyXL工作簿和工作表对象book = writer.booksheet = writer.sheets["Sheet1"]
# OpenPyXL的代码从这里开始sheet["A1"].value = "This is a Title" # 写入单个单元格的值
从Pandas版本3.0开始,to_excel
函数的参数(除了excel_writer
之外)都将只能以关键字参数(keyword-only arguments)的形式传递,而不能以位置参数(positional arguments)的形式传递。在未来的Pandas 3.0及更高版本中,为了保持代码的兼容性和清晰性,您应该将所有这些参数都明确为关键字参数: