创建简单的电子表格和条形图#
在本例中，将从头创建，并添加一些数据，然后绘制它。还将探讨一些有限的单元格样式和格式。
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
treeData = [["Type", "Leaf Color", "Height"], ["Maple", "Red", 549], ["Oak", "Green", 783], ["Pine", "Green", 1204]]
接下来，使用 Worksheet.append 函数把这些数据输入工作表。
for row in treeData:
ws.append(row)
应该将标题设置为粗体，使其更加突出，为此需要创建 styles.Font，并将其应用到标题行的所有单元格。
from openpyxl.styles import Font
ft = Font(bold=True)
for row in ws["A1:C1"]:
for cell in row:
cell.font = ft
是时候做一些图表了：
from openpyxl.chart import BarChart, Series, Reference
chart = BarChart()
chart.type = "col"
chart.title = "Tree Height"
chart.y_axis.title = 'Height (cm)'
chart.x_axis.title = 'Tree Type'
chart.legend = None
这就创建了柱状图的框架。现在需要添加对数据所在位置的引用，并将其传递给图表对象
data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1)
chart.add_data(data)
chart.set_categories(categories)
最后可以把它添加到表格中。
ws.add_chart(chart, "E1")
wb.save("../build/TreeData.xlsx")
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[7], line 2
1 ws.add_chart(chart, "E1")
----> 2 wb.save("../build/TreeData.xlsx")
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/workbook/workbook.py:386, in Workbook.save(self, filename)
384 if self.write_only and not self.worksheets:
385 self.create_sheet()
--> 386 save_workbook(self, filename)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/writer/excel.py:291, in save_workbook(workbook, filename)
279 def save_workbook(workbook, filename):
280 """Save the given workbook on the filesystem under the name filename.
281
282 :param workbook: the workbook to save
(...)
289
290 """
--> 291 archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
292 workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
293 writer = ExcelWriter(workbook, archive)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
1329 while True:
1330 try:
-> 1331 self.fp = io.open(file, filemode)
1332 except OSError:
1333 if filemode in modeDict:
FileNotFoundError: [Errno 2] No such file or directory: '../build/TreeData.xlsx'
添加图片#
from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
# create an image
img = Image('../../../logo.jpg')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('../build/logo.xlsx')
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[11], line 3
1 # add to worksheet and anchor next to cells
2 ws.add_image(img, 'A1')
----> 3 wb.save('../build/logo.xlsx')
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/workbook/workbook.py:386, in Workbook.save(self, filename)
384 if self.write_only and not self.worksheets:
385 self.create_sheet()
--> 386 save_workbook(self, filename)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/writer/excel.py:291, in save_workbook(workbook, filename)
279 def save_workbook(workbook, filename):
280 """Save the given workbook on the filesystem under the name filename.
281
282 :param workbook: the workbook to save
(...)
289
290 """
--> 291 archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
292 workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
293 writer = ExcelWriter(workbook, archive)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
1329 while True:
1330 try:
-> 1331 self.fp = io.open(file, filemode)
1332 except OSError:
1333 if filemode in modeDict:
FileNotFoundError: [Errno 2] No such file or directory: '../build/logo.xlsx'
修改图片尺寸：
_from = AnchorMarker(0, 50000, 1, 50000)
to = AnchorMarker(15, -50000, 40, -50000)
anchor = TwoCellAnchor('twoCell', _from, to)
ws.add_image(img, anchor)
样式#
样式用于更改显示在屏幕上的数据的外观。它们还用于确定数字的格式。
样式可以应用于以下方面：
设置字体大小、颜色、下划线等
填充以设置图案或颜色渐变
在单元格上设置边框
单元格对齐
保护
以下为默认值：
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
border = Border(left=Side(border_style=None, color='FF000000'),
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
diagonal=Side(border_style=None, color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000'))
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True, hidden=False)
单元格样式和命名样式#
有两种类型的样式：单元格样式和命名样式，也称为样式模板。
单元格样式#
单元格样式在对象之间共享，一旦它们被分配，就不能更改。这可以避免不必要的副作用，比如当只有一个单元格发生变化时，许多单元格的样式都会发生变化。
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
ft = Font(color="FF0000")
a1.font = ft
d4.font = ft
# a1.font.italic = True # is not allowed # doctest: +SKIP
# If you want to change the color of a Font, you need to reassign it::
a1.font = Font(color="FF0000", italic=True) # the change only affects A1
样式副本#
样式可以有副本：
from openpyxl.styles import Font
from copy import copy
ft1 = Font(name='Arial', size=14)
ft2 = copy(ft1)
ft2.name = "Tahoma"
ft1.name
'Arial'
ft2.name
'Tahoma'
ft2.size # copied from the
14.0
颜色#
字体、背景、边框等的颜色可以通过三种方式设置：索引、aRGB或主题。索引颜色是遗留实现，颜色本身取决于工作簿或应用程序默认提供的索引。主题颜色对于颜色的互补阴影是有用的，但也取决于工作簿中出现的主题。因此，建议使用 aRGB 颜色。
aRGB 颜色#
RGB 颜色是用红、绿、蓝的十六进制值设置的。
from openpyxl.styles import Font
font = Font(color="FF0000")
alpha 值在理论上是指颜色的透明度，但这与单元格样式无关。默认值 00 将前置任何简单的 RGB 值：
from openpyxl.styles import Font
font = Font(color="00FF00")
font.color.rgb
'0000FF00'
它还支持遗留的索引颜色以及主题和色调。
from openpyxl.styles.colors import Color
c = Color(indexed=32)
c = Color(theme=6, tint=0.5)
c
<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=6, tint=0.5, type='theme'
应用样式#
样式直接应用于单元格
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
wb = Workbook()
ws = wb.active
c = ws['A1']
c.font = Font(size=12)
样式也可以应用于列和行，但请注意，这只适用于文件关闭后创建的单元格(在 Excel 中)。如果你想对整个行和列应用样式，那么你必须自己对每个单元格应用样式。这是文件格式的限制：
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")
样式化合并单元格#
合并的单元格的行为与其他单元格对象类似。它的值和格式在其左上角的单元格中定义。要更改整个合并单元格的边界，请更改其左上角单元格的边界。格式是为了写作而生成的。
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.merge_cells('B2:F4')
top_left_cell = ws['B2']
top_left_cell.value = "My Cell"
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.fill = fill = GradientFill(stop=("000000", "FFFFFF"))
top_left_cell.font = Font(b=True, color="FF0000")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
wb.save("../build/styled.xlsx")
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[23], line 15
13 top_left_cell.font = Font(b=True, color="FF0000")
14 top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
---> 15 wb.save("../build/styled.xlsx")
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/workbook/workbook.py:386, in Workbook.save(self, filename)
384 if self.write_only and not self.worksheets:
385 self.create_sheet()
--> 386 save_workbook(self, filename)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/writer/excel.py:291, in save_workbook(workbook, filename)
279 def save_workbook(workbook, filename):
280 """Save the given workbook on the filesystem under the name filename.
281
282 :param workbook: the workbook to save
(...)
289
290 """
--> 291 archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
292 workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
293 writer = ExcelWriter(workbook, archive)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
1329 while True:
1330 try:
-> 1331 self.fp = io.open(file, filemode)
1332 except OSError:
1333 if filemode in modeDict:
FileNotFoundError: [Errno 2] No such file or directory: '../build/styled.xlsx'
使用数字格式#
您可以为单元格指定数字格式，或者对于某些实例(如 datetime)，它将自动格式化。
import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'
ws["A2"] = 0.123456
ws["A2"].number_format = "0.00" # Display to 2dp
编辑页面设置#
from openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
命名样式#
与单元格样式相反，命名样式是可变的。当您想要同时对许多不同的单元格应用格式时，它们是有意义的。NB。将命名样式分配给单元格后，对样式的其他更改将不会影响单元格。
一旦已命名的样式被注册到工作簿中，就可以简单地通过名称引用它。
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
一旦创建了命名样式，就可以将它注册到工作簿中:
wb.add_named_style(highlight)
但是命名样式也会在第一次分配给单元格时自动注册：
ws['A1'].style = highlight
注册后，仅需使用名称分配样式：
ws['D5'].style = 'highlight'