1.教程
这里整了一部分。。。里面内容太多了 不过能够真正实用的也就操作工作薄部分。
1.1操作内存中的工作簿
使用openpyxl时，不需要在文件系统上创建文件。只需导入工作簿类开始使用它。
>>> from openpyxl import Workbook
>>> wb = Workbook()
始终至少使用一个工作表创建工作簿。你可以通过使用openpyxl.workbook.Workbook.active()属性:
>>> ws = wb.active
注意:这个函数使用_active_sheet_index属性，默认设置为0。除非你修改它的值，否则你会使用此方法始终获取第一个工作表。
您还可以使用openpyxsl .workbook. workbook. create_sheet()创建新的工作表方法:
>>> ws1 = wb.create_sheet() # 插入到结尾 (默认)
# or
>>> ws2 = wb.create_sheet(0) # 插入到启始位置
在创建工作表时，会自动给它们一个名称。它们按顺序编号(表格，表格1，表格2，……)。你可以在任何时候用title属性改变这个名字:
>>> ws.title = "New Title"
默认情况下，保持这个标题的标签的背景颜色是白色。您可以通过提供RRGGBB来更改它的颜色代码到sheet_properties.tabColor属性:
>>> ws.sheet_properties.tabColor = "1072BA"
为工作表指定名称后，可以将其作为工作簿的键或使用openpyxl.workbook.Workbook.get_sheet_by_name()方法：
>>> ws3 = wb["New Title"]
>>> ws4 = wb.get_sheet_by_name("New Title")
>>> ws is ws3 is ws4
True
您可以使用openpyxsl .workbook. workbook. get_sheet_names()方法查看工作簿中所有工作表的名称：
>>> print(wb.get_sheet_names())
['Sheet2', 'New Title', 'Sheet1']
可以循环遍历工作表：
>>> for sheet in wb:
... print(sheet.title)
1.2 表数据处理
1.2.1 访问一个单元格
现在我们知道了如何访问工作表，我们可以开始修改单元格内容。
单元格可以作为工作表的键直接访问：
>>> c = ws['A4']
这将返回A4位置的单元格，如果单元格还不存在，则创建一个单元格。值可以直接赋值:
ws['A4'] = 4
还有openpyxsl .worksheet. worksheet. cell()方法:
>>> c = ws.cell('A4')
你也可以使用行和列表示法访问单元格:
d = ws.cell(row = 4, column = 2)
注意:在内存中创建工作表时，它不包含单元格。它们是在第一次访问时创建的。这种方式我们不会创建永远不会被访问的对象，从而减少内存占用。
警告:由于这个特性，滚动单元格而不是直接访问它们将会在内存中创建它们，即使你没有给它们赋值。
>>> for i in range(1,101):
... for j in range(1,101):
... ws.cell(row = i, column = j)
将在内存中创建100x100个单元格，都为空。
但是，有一种方法可以清除所有这些不需要的单元格，我们将在后面看到。
1.2.2 访问多个单元格
单元格的范围可以使用切片来访问
>>> cell_range = ws['A1':'C2']
你也可以使用openpyxsl .worksheet. worksheet. iter_rows()方法:
>>> tuple(ws.iter_rows('A1:C2'))
((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),
(<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))
>>> for row in ws.iter_rows('A1:C2'):
... for cell in row:
... print cell
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
如果你需要遍历一个文件的所有行或列，你可以使用openpyxsl .worksheet. worksheet. rows()属性:
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> ws.rows
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
或者openpyxsl .worksheet. worksheet. columns()属性:
>>> ws.columns
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))
1.2.3 数据存储
一旦我们有了openpyxl.cell。单元格，我们可以给它赋值:
>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'
>>> d.value = 3.14
>>> print(d.value)
3.14
您还可以启用类型和格式推断:
>>> wb = Workbook(guess_types=True)
>>> c.value = '12%'
>>> print(c.value)
0.12
>>> import datetime
>>> d.value = datetime.datetime.now()
>>> print d.value
datetime.datetime(2010, 9, 10, 22, 25, 18)
>>> c.value = '31.50'
>>> print(c.value)
31.5
1.2.4 保存到文件
保存工作簿最简单、最安全的方法是使用openpyxsl .workbook. workbook. save()方法的openpyxsl .workbook.Workbook 对象:
>>> wb = Workbook()
>>> wb.save('balances.xlsx')
警告:此操作将覆盖现有文件而不发出警告。
注意:扩展不强制为xlsx或xlsm，尽管你可能会有一些麻烦直接打开它。如果不使用官方扩展，则使用另一个应用程序。
由于OOXML文件基本上是ZIP文件，您也可以用. ZIP文件结束文件名，然后用您最喜欢的ZIP归档管理器打开它。
您可以指定属性as_template=True，以将文档保存为模板
>>> wb = load_workbook('document.xlsx')
>>> wb.save('document_template.xltx', as_template=True)
或者指定属性as_template=False(默认情况下)，将文档模板(或文档)保存为文档。
>>> wb = load_workbook('document_template.xltx')
>>> wb.save('document.xlsx', as_template=False)
>>> wb = load_workbook('document.xlsx')
>>> wb.save('new_document.xlsx', as_template=False)
警告:您应该监视在文档模板中保存文档的数据属性和文档扩展名，反之亦然，否则结果表引擎无法打开文档。
注意:以下操作将失败:
>>> wb = load_workbook('document.xlsx') >>> # 需要保存扩展名*.xlsx >>> wb.save('new_document.xlsm') >>> # MS Excel无法打开文档 >>> >>> # or >>> >>> # 需要指定属性 keep_vba=True >>> wb = load_workbook('document.xlsm') >>> wb.save('new_document.xlsm') >>> # MS Excel无法打开文档 >>> >>> # or >>> >>> wb = load_workbook('document.xltm', keep_vba=True) >>> # 如果我们需要模板文档，那么我们需要指定扩展名为*.xltm。 >>> # 如果我们需要document，那么我们需要指定属性as_template=False。 >>> wb.save('new_document.xlsm', as_template=True) >>> # MS Excel无法打开文档
1.2.5 从文件加载
与编写方法相同，你可以导入openpyxl.load_workbook()来打开一个现有的工作簿:
>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']
本教程到此结束，您可以继续学习简单用法一节
2 菜单
2.1 简单的使用
2.1.1 写一个工作簿
>>> from openpyxl import Workbook
>>> from openpyxl.compat import range
>>> from openpyxl.cell import get_column_letter
>>>
>>> wb = Workbook()
>>>
>>> dest_filename = 'empty_book.xlsx'
>>>
>>> ws1 = wb.active
>>> ws1.title = "range names"
>>>
>>> for row in range(1, 40):
... ws1.append(range(600))
>>>
>>> ws2 = wb.create_sheet(title="Pi")
>>>
>>> ws2['F5'] = 3.14
>>>
>>> ws3 = wb.create_sheet(title="Data")
>>> for row in range(10, 20):
... for col in range(27, 54):
... _ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
>>> print(ws3['AA10'].value)
AA
>>> wb.save(filename = dest_filename)
2.1.2 写一个从*.xltx到*.xlsx的workbook
>>> from openpyxl import load_workbook
>>>
>>>
>>> wb = load_workbook('sample_book.xltx')
>>> ws = wb.active
>>> ws['D2'] = 42
>>>
>>> wb.save('sample_book.xlsx')
>>>
>>> # or you can overwrite the current document template
>>> # wb.save('sample_book.xltx')
2.1.3 写一个从*.xltm到*.xlsm的workbook
>>> from openpyxl import load_workbook
>>>
>>>
>>> wb = load_workbook('sample_book.xltm', keep_vba=True)
>>> ws = wb.active
>>> ws['D2'] = 42
>>>
>>> wb.save('sample_book.xlsm')
>>>
>>> # 或者您可以覆盖当前文档模板
>>> # wb.save('sample_book.xltm')
2.1.4 阅读现有的工作簿
>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename = 'empty_book.xlsx')
>>> sheet_ranges = wb['range names']
>>> print(sheet_ranges['D18'].value)
3
注意： 有几个标志可以在load_workbook中使用。
- 在读取单元格时，guess_types将启用或禁用(默认)类型推断。
- data_only控制带有公式的单元格是否具有公式(默认)或上次存储的值
Excel阅读表格。 - keep_vba控制是否保留任何Visual Basic元素(默认)。如果它们被保存了下来
仍然不可编辑。
警告:openpyxl目前不能读取Excel文件中的所有可能项目，因此，如果以相同的名称打开并保存现有文件中的图像和图表，它们将丢失。
2.1.5 使用数字格式
>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # 使用Python datetime设置date
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'
>>> # 您可以根据具体情况启用类型推断
>>> wb.guess_types = True
>>> # 使用后跟百分比符号的字符串设置百分比
>>> ws['B1'] = '3.14%'
>>> wb.guess_types = False
>>> ws['B1'].value
0.031400000000000004
>>>
>>> ws['B1'].number_format
'0%'
2.1.6 使用公式
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")
警告:注意，函数必须使用英文名称，函数参数之间必须用逗号分隔，而不是其他的标点符号，如分号。
openpyxl从不评估公式，但可以检查公式的名称:
>>> from openpyxl.utils import FORMULAE
>>> "HEX2DEC" in FORMULAE
True
如果你试图使用一个未知的公式，这可能是因为你使用的公式没有包含在最初的规范中。这些公式必须加上xlfn前缀。去工作。
2.1.7 合并/不合并单元格
>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A1:B1')
>>> ws.unmerge_cells('A1:B1')
>>>
>>> # or
>>> ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
>>> ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
2.1.8 插入一个图片
>>> from openpyxl import Workbook
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = 'You should see three logos below'
>>> # 创建一个图片
>>> img = Image('logo.png')
>>> # 添加到工作表并锚定在单元格旁边
>>> ws.add_image(img, 'A1')
>>> wb.save('logo.xlsx')
2.1.9 褶皱列(大纲)
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> ws = wb.create_sheet()
>>> ws.column_dimensions.group('A','D', hidden=True)
>>> wb.save('group.xlsx')
3. 图表
3.1 表格
警告:Openpyxl目前只支持在工作表内创建图表。现有工作簿中的图表将丢失。
3.1.1 2D区域表
面积图与折线图类似，只是在绘制的线下面填充了区域。通过将分组设置为“标准”、“堆叠”或“百分比堆叠”，可以获得不同的变体;“standard”是默认值。
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 10],
[6, 25, 5],
[7, 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
wb.save("area.xlsx")
3.1.2 3D区域表
你也可以创建3D区域表：
from openpyxl import Workbook
from openpyxl.chart import (
AreaChart3D,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 30, 40],
[3, 25, 40],
[4 ,30, 50],
[5 ,10, 30],
[6, 5, 25],
[7 ,10, 50],
]
for row in rows:
ws.append(row)
chart = AreaChart3D()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
chart.legend = None
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
wb.save("area3D.xlsx")
3.1.3 柱状和柱状图
在条形图中，值以水平条或垂直列的形式绘制。
垂直、水平和堆叠条形图
注:以下设置会影响不同的图表类型。
通过将类型分别设置为col或bar，在垂直条形图和水平条形图之间切换。
当使用堆叠图表时，重叠需要设置为100。
如果条形图是水平的，则x轴和y轴是反转的。
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")
wb.save("bar.xlsx")
这将产生四个图表来说明各种可能性。
3.1.4 三维柱形图
您还可以创建3D柱状图:
from openpyxl import Workbook
from openpyxl.chart import (
Reference,
Series,
BarChart3D,
)
wb = Workbook()
ws = wb.active
rows = [
(None, 2013, 2014),
("Apples", 5, 4),
("Oranges", 6, 2),
("Pears", 8, 3)
]
for row in rows:
ws.append(row)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")
3.1.5 泡沫图表
气泡图与散点图类似，但使用三维来确定气泡的大小。图表可以包括多个系列。
"""
样本气泡图
"""
from openpyxl import Workbook
from openpyxl.chart import Series, Reference, BubbleChart
wb = Workbook()
ws = wb.active
rows = [
("Number of Products", "Sales in USD", "Market share"),
(14, 12200, 15),
(20, 60000, 33),
(18, 24400, 10),
(22, 32000, 42),
(),
(12, 8200, 18),
(15, 50000, 30),
(19, 22400, 15),
(25, 25000, 50),
]
for row in rows:
ws.append(row)
chart = BubbleChart()
chart.style = 18 # 使用预设样式
# 添加第一批数据
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
chart.series.append(series)
# 添加第二批
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
chart.series.append(series)
# 将图表从单元格E1开始放置
ws.add_chart(chart, "E1")
wb.save("bubble.xlsx")
3.1.6 折线图表
折线图允许数据按固定轴绘制。它们类似于散点图，主要区别在于，用折线图绘制的每个数据系列都是根据相同的值绘制的。次级轴可以使用不同种类的轴。
与条形图类似，折线图也有三种:标准折线图、堆叠折线图和百分比折线图。
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
Reference,
)
from openpyxl.chart.axis import DateAxis
wb = Workbook()
ws = wb.active
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
[date(2015,9, 1), 40, 30, 25],
[date(2015,9, 2), 40, 25, 30],
[date(2015,9, 3), 50, 30, 45],
[date(2015,9, 4), 30, 25, 40],
[date(2015,9, 5), 25, 35, 30],
[date(2015,9, 6), 20, 40, 35],
]
for row in rows:
ws.append(row)
c1 = LineChart()
c1.title = "Line Chart"
c1.style = 13
c1.y_axis.title = 'Size'
c1.x_axis.title = 'Test Number'
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)
# 线类型
s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # 标记填充
s1.marker.graphicalProperties.line.solidFill = "FF0000" # 标志的轮廓
s1.graphicalProperties.line.noFill = True
s2 = c1.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # 宽度在EMUs
s2 = c1.series[2]
s2.smooth = True # 使线条平滑
ws.add_chart(c1, "A10")
from copy import deepcopy
stacked = deepcopy(c1)
stacked.grouping = "stacked"
stacked.title = "Stacked Line Chart"
ws.add_chart(stacked, "A27")
percent_stacked = deepcopy(c1)
percent_stacked.grouping = "percentStacked"
percent_stacked.title = "Percent Stacked Line Chart"
ws.add_chart(percent_stacked, "A44")
# 带日期轴的图表
c2 = LineChart()
c2.title = "Date Axis"
c2.style = 12
c2.y_axis.title = "Size"
c2.y_axis.crossAx = 500
c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_format = 'd-mmm'
c2.x_axis.majorTimeUnit = "days"
c2.x_axis.title = "Date"
c2.add_data(data, titles_from_data=True)
dates = Reference(ws, min_col=1, min_row=2, max_row=7)
c2.set_categories(dates)
ws.add_chart(c2, "A61")
wb.save("line.xlsx")
3.1.7 3D折线图表
在3D线形图中，第三个轴与系列图例相同。
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
LineChart3D,
Reference,
)
from openpyxl.chart.axis import DateAxis
wb = Workbook()
ws = wb.active
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
[date(2015,9, 1), 40, 30, 25],
[date(2015,9, 2), 40, 25, 30],
[date(2015,9, 3), 50, 30, 45],
[date(2015,9, 4), 30, 25, 40],
[date(2015,9, 5), 25, 35, 30],
[date(2015,9, 6), 20, 40, 35],
]
for row in rows:
ws.append(row)
c1 = LineChart3D()
c1.title = "3D Line Chart"
c1.legend = None
c1.style = 15
c1.y_axis.title = 'Size'
c1.x_axis.title = 'Test Number'
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)
ws.add_chart(c1, "A10")
wb.save("line3D.xlsx")
3.1.8 散点图
散点图(xy)类似于一些折线图。主要的不同之处在于，一组值被绘制成另一组值。这在值是无序的情况下很有用
from openpyxl import Workbook
from openpyxl.chart import (
ScatterChart,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "A10")
wb.save("scatter.xlsx")
注意:规范规定有以下几种散点图:“line”、“lineMarker”、“marker”、“smooth”、“smoothMarker”。然而，至少在Microsoft Excel中，这只是其他设置的快捷方式，否则没有效果。为了与折线图保持一致，应该手动设置每个系列的样式。
3.1.9 饼图
饼图将数据绘制成圆形的切片，每个切片代表整体的百分比。切片以顺时针方向绘制，0°位于圆的顶部。饼状图只能收集单一系列的数据。图表的标题默认为系列的标题。
from openpyxl import Workbook
from openpyxl.chart import (
PieChart,
ProjectedPieChart,
Reference
)
from openpyxl.chart.series import DataPoint
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# 从饼图上切下第一片
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
ws = wb.create_sheet(title="Projection")
data = [
['Page', 'Views'],
['Search', 95],
['Products', 4],
['Offers', 0.5],
['Sales', 0.5],
]
for row in data:
ws.append(row)
projected_pie = ProjectedPieChart()
projected_pie.type = "pie"
projected_pie.splitType = "val" # 分裂的价值
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)
ws.add_chart(projected_pie, "A10")
from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar"
projected_bar.splitType = 'pos' # 分裂的位置
ws.add_chart(projected_bar, "A27")
wb.save("pie.xlsx")
投影饼图从饼图中提取一些切片，并将它们投影到第二个饼图或柱状图中。当数据系列中有几个较小的项时，这很有用。图表可以根据百分比，val(ue)或pos(ition)进行分割。如果没有设置，则应用程序决定使用哪一个。此外，还可以定义自定义分割。
3.1.10 3D 饼图
饼图也可以用3D效果创建。
from openpyxl import Workbook
from openpyxl.chart import (
PieChart3D,
Reference
)
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart3D()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
ws.add_chart(pie, "D1")
wb.save("pie3D.xlsx")
3.1.11 油炸圈饼图
甜甜圈图和饼状图很相似，不同的是它们用的是圆环而不是圆。他们还可以将一系列数据绘制成同心圆。
from openpyxl import Workbook
from openpyxl.chart import (
DoughnutChart,
Reference,
Series,
)
from openpyxl.chart.series import DataPoint
data = [
['Pie', 2014, 2015],
['Plain', 40, 50],
['Jam', 2, 10],
['Lime', 20, 30],
['Chocolate', 30, 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
chart = DoughnutChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = "Doughnuts sold by category"
chart.style = 26
# 把甜甜圈的第一片切下来
slices = [DataPoint(idx=i) for i in range(4)]
plain, jam, lime, chocolate = slices
chart.series[0].data_points = slices
plain.graphicalProperties.solidFill = "FAE1D0"
jam.graphicalProperties.solidFill = "BB2244"
lime.graphicalProperties.solidFill = "22DD22"
chocolate.graphicalProperties.solidFill = "61210B"
chocolate.explosion = 10
ws.add_chart(chart, "E1")
from copy import deepcopy
chart2 = deepcopy(chart)
chart2.title = None
data = Reference(ws, min_col=3, min_row=1, max_row=5)
series2 = Series(data, title_from_data=True)
series2.data_points = slices
chart2.series.append(series2)
ws.add_chart(chart2, "E17")
wb.save("doughnut.xlsx")
3.1.12 雷达图表
在工作表上按列或行排列的数据可以绘制在雷达图上。雷达图比较多个数据序列的累计值。它实际上是面积图在圆形x轴上的投影。
雷达图分为标准雷达图和标准雷达图两种:标准雷达图用直线标记区域;填满了整个区域。附加类型“marker”没有效果。如果需要标记，可以为相关系列设置标记。
from openpyxl import Workbook
from openpyxl.chart import (
RadarChart,
Reference,
)
wb = Workbook()
ws = wb.active
rows = [
['Month', "Bulbs", "Seeds", "Flowers", "Trees & shrubs"],
['Jan', 0, 2500, 500, 0,],
['Feb', 0, 5500, 750, 1500],
['Mar', 0, 9000, 1500, 2500],
['Apr', 0, 6500, 2000, 4000],
['May', 0, 3500, 5500, 3500],
['Jun', 0, 0, 7500, 1500],
['Jul', 0, 0, 8500, 800],
['Aug', 1500, 0, 7000, 550],
['Sep', 5000, 0, 3500, 2500],
['Oct', 8500, 0, 2500, 6000],
['Nov', 3500, 0, 500, 5500],
['Dec', 500, 0, 100, 3000 ],
]
for row in rows:
ws.append(row)
chart = RadarChart()
chart.type = "filled"
labels = Reference(ws, min_col=1, min_row=2, max_row=13)
data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True
ws.add_chart(chart, "A17")
wb.save("radar.xlsx")
3.1.13 股票图表
在工作表上按特定顺序排列的列或行数据可以绘制在股票图表中。顾名思义，股票图表最常用来说明股票价格的波动。然而，这个图表也可以用于科学数据。例如，您可以使用股票图表来指示日或年温度的波动。你必须以正确的顺序组织你的数据来创建股票图表。
股票图表数据在工作表中的组织方式是非常重要的。例如，要创建一个简单的高-低股票图表，你应该将你的数据以高、低、低作为列标题，按这个顺序排列。
虽然股票图表是一个独特的类型，各种类型只是特定格式选项的快捷方式:
- high-low-close本质上是一个没有线的折线图，并且标记为XYZ。它还将hiLoLines设置为True
- 开-高-低-收盘价是一个高-低-收盘价图表，每个数据点设置为XZZ和上下线。
成交量可以通过结合股票图表和成交量柱状图来增加。
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
BarChart,
StockChart,
Reference,
Series,
)
from openpyxl.chart.axis import DateAxis, ChartLines
from openpyxl.chart.updown_bars import UpDownBars
wb = Workbook()
ws = wb.active
rows = [
['Date', 'Volume','Open', 'High', 'Low', 'Close'],
['2015-01-01', 20000, 26.2, 27.20, 23.49, 25.45, ],
['2015-01-02', 10000, 25.45, 25.03, 19.55, 23.05, ],
['2015-01-03', 15000, 23.05, 24.46, 20.03, 22.42, ],
['2015-01-04', 2000, 22.42, 23.97, 20.07, 21.90, ],
['2015-01-05', 12000, 21.9, 23.65, 19.50, 21.51, ],
]
for row in rows:
ws.append(row)
# High-low-close
c1 = StockChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=6)
data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6)
c1.add_data(data, titles_from_data=True)
c1.set_categories(labels)
for s in c1.series:
s.graphicalProperties.line.noFill = True
# 标记关闭
s.marker.symbol = "dot"
s.marker.size = 5
c1.title = "High-low-close"
c1.hiLowLines = ChartLines()
# Excel被破坏了，需要一个值缓存来显示hilline :-/
from openpyxl.chart.data_source import NumData, NumVal
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
c1.series[-1].val.numRef.numCache = cache
ws.add_chart(c1, "A10")
# Open-high-low-close
c2 = StockChart()
data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
c2.add_data(data, titles_from_data=True)
c2.set_categories(labels)
for s in c2.series:
s.graphicalProperties.line.noFill = True
c2.hiLowLines = ChartLines()
c2.upDownBars = UpDownBars()
c2.title = "Open-high-low-close"
# 添加虚拟缓存
c2.series[-1].val.numRef.numCache = cache
ws.add_chart(c2, "G10")
# 为量创建条形图
bar = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=6)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
from copy import deepcopy
# Volume-high-low-close
b1 = deepcopy(bar)
c3 = deepcopy(c1)
c3.y_axis.majorGridlines = None
c3.y_axis.title = "Price"
b1.y_axis.axId = 20
b1.z_axis = c3.y_axis
b1.y_axis.crosses = "max"
b1 += c3
c3.title = "High low close volume"
ws.add_chart(b1, "A27")
## Volume-open-high-low-close
b2 = deepcopy(bar)
c4 = deepcopy(c2)
c4.y_axis.majorGridlines = None
c4.y_axis.title = "Price"
b2.y_axis.axId = 20
b2.z_axis = c4.y_axis
b2.y_axis.crosses = "max"
b2 += c4
ws.add_chart(b2, "G27")
wb.save("stock.xlsx")
警告:由于Excel中的一个错误，只有当至少一个数据系列有一些虚拟值时，才会显示高低行。这可以通过以下方法实现:
from openpyxl.chart.data_source import NumData, NumVal pts = [NumVal(idx=i) for i in range(len(data) - 1)] cache = NumData(pt=pts) c1.series[-1].val.numRef.numCache = cache
3.1.14 面图
在工作表上按列或行排列的数据可以绘制在表面图表中。当你想要找到两组数据之间的最佳组合时，曲面图是很有用的。在地形图中，颜色和图案表示在同一数值范围内的区域。
默认情况下，所有表面图表都是3D的。通过设置旋转和透视可以创建2D线框和轮廓图。
from openpyxl import Workbook
from openpyxl.chart import (
SurfaceChart,
SurfaceChart3D,
Reference,
Series,
)
from openpyxl.chart.axis import SeriesAxis
wb = Workbook()
ws = wb.active
data = [
[None, 10, 20, 30, 40, 50,],
[0.1, 15, 65, 105, 65, 15,],
[0.2, 35, 105, 170, 105, 35,],
[0.3, 55, 135, 215, 135, 55,],
[0.4, 75, 155, 240, 155, 75,],
[0.5, 80, 190, 245, 190, 80,],
[0.6, 75, 155, 240, 155, 75,],
[0.7, 55, 135, 215, 135, 55,],
[0.8, 35, 105, 170, 105, 35,],
[0.9, 15, 65, 105, 65, 15],
]
for row in data:
ws.append(row)
c1 = SurfaceChart()
ref = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=10)
labels = Reference(ws, min_col=1, min_row=2, max_row=10)
c1.add_data(ref, titles_from_data=True)
c1.set_categories(labels)
c1.title = "Contour"
ws.add_chart(c1, "A12")
from copy import deepcopy
c2 = deepcopy(c1)
c2.wireframe = True
c2.title = "2D Wireframe"
ws.add_chart(c3, "A29")
c4 = deepcopy(c3)
c4.wireframe = True
c4.title = "3D Wireframe"
ws.add_chart(c4, "G29")
wb.save("surface.xlsx")
3.1.15 创建一个图表
图表至少由一个或多个数据点组成的一个系列。系列本身由对单元格范围的引用组成。
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> for i in range(10):
... ws.append([i])
>>>
>>> from openpyxl.chart import BarChart, Reference, Series
>>> values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
>>> chart = BarChart()
>>> chart.add_data(values)
>>> ws.add_chart(chart)
>>> wb.save("SampleChart.xlsx")
3.1.16 使用轴
添加第二个轴实际上涉及创建第二个图表，该图表与第一个图表共享一个共同的x轴，但有一个单独的y轴。
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
BarChart,
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 10, 40, 50, 20, 10, 50],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# 创建第二个表
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Humans"
# 在右侧显示第二个图表的y轴，将其设置为最大限度地与x轴交叉
c1.y_axis.crosses = "max"
c1 += c2
ws.add_chart(c1, "D4")
wb.save("secondary.xlsx")