创建简单的电子表格和条形图#
在本例中，将从头创建，并添加一些数据，然后绘制它。还将探讨一些有限的单元格样式和格式。
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
treeData = [["Type", "Leaf Color", "Height"], ["Maple", "Red", 549], ["Oak", "Green", 783], ["Pine", "Green", 1204]]
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[1], line 1
----> 1 from openpyxl import Workbook
3 wb = Workbook()
4 ws = wb.active
ModuleNotFoundError: No module named 'openpyxl'
接下来，使用 Worksheet.append 函数把这些数据输入工作表。
for row in treeData:
ws.append(row)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[2], line 1
----> 1 for row in treeData:
2 ws.append(row)
NameError: name 'treeData' is not defined
应该将标题设置为粗体，使其更加突出，为此需要创建 styles.Font，并将其应用到标题行的所有单元格。
from openpyxl.styles import Font
ft = Font(bold=True)
for row in ws["A1:C1"]:
for cell in row:
cell.font = ft
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[3], line 1
----> 1 from openpyxl.styles import Font
3 ft = Font(bold=True)
5 for row in ws["A1:C1"]:
ModuleNotFoundError: No module named 'openpyxl'
是时候做一些图表了：
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
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[4], line 1
----> 1 from openpyxl.chart import BarChart, Series, Reference
3 chart = BarChart()
4 chart.type = "col"
ModuleNotFoundError: No module named 'openpyxl'
这就创建了柱状图的框架。现在需要添加对数据所在位置的引用，并将其传递给图表对象
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)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[5], line 1
----> 1 data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3)
2 categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1)
NameError: name 'Reference' is not defined
chart.add_data(data)
chart.set_categories(categories)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[6], line 1
----> 1 chart.add_data(data)
2 chart.set_categories(categories)
NameError: name 'chart' is not defined
最后可以把它添加到表格中。
ws.add_chart(chart, "E1")
wb.save("../build/TreeData.xlsx")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[7], line 1
----> 1 ws.add_chart(chart, "E1")
2 wb.save("../build/TreeData.xlsx")
NameError: name 'ws' is not defined
添加图片#
from openpyxl import Workbook
from openpyxl.drawing.image import Image
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[8], line 1
----> 1 from openpyxl import Workbook
3 from openpyxl.drawing.image import Image
ModuleNotFoundError: No module named 'openpyxl'
wb = Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[9], line 1
----> 1 wb = Workbook()
2 ws = wb.active
4 ws['A1'] = 'You should see three logos below'
NameError: name 'Workbook' is not defined
# create an image
img = Image('../../../logo.jpg')
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[10], line 2
1 # create an image
----> 2 img = Image('../../../logo.jpg')
NameError: name 'Image' is not defined
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('../build/logo.xlsx')
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[11], line 2
1 # add to worksheet and anchor next to cells
----> 2 ws.add_image(img, 'A1')
3 wb.save('../build/logo.xlsx')
NameError: name 'ws' is not defined
修改图片尺寸：
_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)
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[12], line 1
----> 1 from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
2 font = Font(name='Calibri',
3 size=11,
4 bold=False,
(...)
8 strike=False,
9 color='FF000000')
10 fill = PatternFill(fill_type=None,
11 start_color='FFFFFFFF',
12 end_color='FF000000')
ModuleNotFoundError: No module named 'openpyxl'
单元格样式和命名样式#
有两种类型的样式：单元格样式和命名样式，也称为样式模板。
单元格样式#
单元格样式在对象之间共享，一旦它们被分配，就不能更改。这可以避免不必要的副作用，比如当只有一个单元格发生变化时，许多单元格的样式都会发生变化。
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
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[13], line 1
----> 1 from openpyxl.styles import colors
2 from openpyxl.styles import Font, Color
3 from openpyxl import Workbook
ModuleNotFoundError: No module named 'openpyxl'
样式副本#
样式可以有副本：
from openpyxl.styles import Font
from copy import copy
ft1 = Font(name='Arial', size=14)
ft2 = copy(ft1)
ft2.name = "Tahoma"
ft1.name
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[14], line 1
----> 1 from openpyxl.styles import Font
2 from copy import copy
3 ft1 = Font(name='Arial', size=14)
ModuleNotFoundError: No module named 'openpyxl'
ft2.name
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[15], line 1
----> 1 ft2.name
NameError: name 'ft2' is not defined
ft2.size # copied from the
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[16], line 1
----> 1 ft2.size # copied from the
NameError: name 'ft2' is not defined
颜色#
字体、背景、边框等的颜色可以通过三种方式设置：索引、aRGB或主题。索引颜色是遗留实现，颜色本身取决于工作簿或应用程序默认提供的索引。主题颜色对于颜色的互补阴影是有用的，但也取决于工作簿中出现的主题。因此，建议使用 aRGB 颜色。
aRGB 颜色#
RGB 颜色是用红、绿、蓝的十六进制值设置的。
from openpyxl.styles import Font
font = Font(color="FF0000")
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[17], line 1
----> 1 from openpyxl.styles import Font
2 font = Font(color="FF0000")
ModuleNotFoundError: No module named 'openpyxl'
alpha 值在理论上是指颜色的透明度，但这与单元格样式无关。默认值 00 将前置任何简单的 RGB 值：
from openpyxl.styles import Font
font = Font(color="00FF00")
font.color.rgb
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[18], line 1
----> 1 from openpyxl.styles import Font
2 font = Font(color="00FF00")
3 font.color.rgb
ModuleNotFoundError: No module named 'openpyxl'
它还支持遗留的索引颜色以及主题和色调。
from openpyxl.styles.colors import Color
c = Color(indexed=32)
c = Color(theme=6, tint=0.5)
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[19], line 1
----> 1 from openpyxl.styles.colors import Color
3 c = Color(indexed=32)
4 c = Color(theme=6, tint=0.5)
ModuleNotFoundError: No module named 'openpyxl'
c
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[20], line 1
----> 1 c
NameError: name 'c' is not defined
应用样式#
样式直接应用于单元格
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
wb = Workbook()
ws = wb.active
c = ws['A1']
c.font = Font(size=12)
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[21], line 1
----> 1 from openpyxl.workbook import Workbook
3 from openpyxl.styles import Font, Fill
4 wb = Workbook()
ModuleNotFoundError: No module named 'openpyxl'
样式也可以应用于列和行，但请注意，这只适用于文件关闭后创建的单元格(在 Excel 中)。如果你想对整个行和列应用样式，那么你必须自己对每个单元格应用样式。这是文件格式的限制：
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[22], line 1
----> 1 col = ws.column_dimensions['A']
2 col.font = Font(bold=True)
3 row = ws.row_dimensions[1]
NameError: name 'ws' is not defined
样式化合并单元格#
合并的单元格的行为与其他单元格对象类似。它的值和格式在其左上角的单元格中定义。要更改整个合并单元格的边界，请更改其左上角单元格的边界。格式是为了写作而生成的。
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")
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[23], line 1
----> 1 from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
2 from openpyxl import Workbook
3 wb = Workbook()
ModuleNotFoundError: No module named 'openpyxl'
使用数字格式#
您可以为单元格指定数字格式，或者对于某些实例(如 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
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[24], line 2
1 import datetime
----> 2 from openpyxl import Workbook
3 wb = Workbook()
4 ws = wb.active
ModuleNotFoundError: No module named 'openpyxl'
ws["A2"] = 0.123456
ws["A2"].number_format = "0.00" # Display to 2dp
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[25], line 1
----> 1 ws["A2"] = 0.123456
2 ws["A2"].number_format = "0.00" # Display to 2dp
NameError: name 'ws' is not defined
编辑页面设置#
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
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[26], line 1
----> 1 from openpyxl.workbook import Workbook
2 wb = Workbook()
3 ws = wb.active
ModuleNotFoundError: No module named 'openpyxl'
命名样式#
与单元格样式相反，命名样式是可变的。当您想要同时对许多不同的单元格应用格式时，它们是有意义的。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)
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[27], line 1
----> 1 from openpyxl.styles import NamedStyle, Font, Border, Side
2 highlight = NamedStyle(name="highlight")
3 highlight.font = Font(bold=True, size=20)
ModuleNotFoundError: No module named 'openpyxl'
一旦创建了命名样式，就可以将它注册到工作簿中:
wb.add_named_style(highlight)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[28], line 1
----> 1 wb.add_named_style(highlight)
NameError: name 'wb' is not defined
但是命名样式也会在第一次分配给单元格时自动注册：
ws['A1'].style = highlight
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[29], line 1
----> 1 ws['A1'].style = highlight
NameError: name 'highlight' is not defined
注册后，仅需使用名称分配样式：
ws['D5'].style = 'highlight'
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[30], line 1
----> 1 ws['D5'].style = 'highlight'
NameError: name 'ws' is not defined