前文：
Python-openpyxl教程1 - openpyxl简介
Python-openpyxl教程2 - 简单使用
Python-openpyxl教程3 - 读写性能
Python-openpyxl教程4 - 优化模式
Python-openpyxl教程5 - 与Pandas交互
Python-openpyxl教程6 - 图表之面积图和条形图
Python-openpyxl教程7 - 图表之散点图，饼图和环形图
雷达图
可以在雷达图中绘制工作表中按列或行排列的数据。雷达图比较多个数据系列的合计值。它实际上是面积图在圆形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 Center Sales'
chart.y_axis.delete = True
# from copy import deepcopy
# chart2 = deepcopy(chart)
# chart2.type = 'standard' # 图表类型为标准
# ws.add_chart(chart2,'A33')
ws.add_chart(chart, 'A17')
wb.save('SampleRadar.xlsx')
雷达图
股价图
在工作表上按特定顺序排列在列或行中的数据可以在股价图表中绘制。顾名思义，最常使用股价图表来说明股票价格波动。但是，此图标也可以用于科学数据。例如，您可以使用股价图来指示每日或每年的温度波动。您必须按照正确的顺序组织数据才能创建股价图。
工作表中的组织结构图数据的组织方式非常重要。例如，要创建一个简单的高-低-收盘价股价图，您应该按此顺序排列数据，分别以高，低和收盘价作为栏标题输入。
尽管股价图是一种独特的类型，但是各种类型只是特定格式选项的快捷方式：
- 高-低-收盘价基本是没有线的折线图，并且标记设置XYZ。还将hiLowLines设置为True
- 开-高-低-收盘价与高-低-收盘价图表相同，每个数据点的标记设置为XYZ和upDownLines。
可以通过将股价图和条形图相结合来添加体积。
from copy import deepcopy
from openpyxl import Workbook
from openpyxl.chart import BarChart, StockChart, Reference
from openpyxl.chart.axis import ChartLines
from openpyxl.chart.data_source import NumData, NumVal
from openpyxl.chart.updown_bars import UpDownBars
wb = Workbook()
ws = wb.active
rows = [
['Date', 'Volume', 'Open', 'High', 'Low', 'Close'],
['2015-01-01', 20000, 26.20, 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.90, 23.65, 19.50, 21.51]
]
for row in rows:
ws.append(row)
# High->Low->Close
chart1 = StockChart() # 创建一个股价图对象
labels = Reference(ws, min_col=1, min_row=2, max_row=6) # 引用数据[A2:A6] -> labels
data = Reference(ws, min_col=4, max_col=6, min_row=1, max_row=6) # 引用数据[D1:F6] -> data
chart1.add_data(data, titles_from_data=True) # chart1添加数据，并且从引用数据data中获取标题 [High, Low, Close]
chart1.set_categories(labels) # 引用数据labels设置类别 [2015-01-01, 2015-01-02, 2015-01-03, 2015-01-04, 2015-01-05]
"""
series: 系列.
使用add_data的时候里面添加了几个系列. 则series里面有几个Series对象。
"""
for s in chart1.series:
# graphicalProperties 图形属性
s.graphicalProperties.line.noFill = True
# marker for close
s.marker.symbol = 'dot' # 标记close的值，等效于 chart1.series[-1].marker.symbol = 'dot'
s.marker.size = 5 # 标记的大小，等效于 chart1.series[-1].marker.size = 5
chart1.title = 'High-low-close' # chart1 的标题
chart1.hiLowLines = ChartLines() # 定义一个高低线对象，但是此时并不会显示
# Excel is broken and needs a cache of values in order to display hiLowLines.
# Excel 已经损坏，需要一个缓存值才能显示高低线 (NumVal和NumData暂未找到相关的解释点)
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
chart1.series[-1].val.numRef.numCache = cache
ws.add_chart(chart1, 'A10')
# Open-high-low-close
chart2 = StockChart()
data = Reference(ws, min_col=3, max_col=6, min_row=1, max_row=6)
chart2.add_data(data, titles_from_data=True)
chart2.set_categories(labels)
for s in chart2.series:
s.graphicalProperties.line.noFill = True
chart2.hiLowLines = ChartLines()
chart2.upDownBars = UpDownBars()
chart2.title = 'Open-high-low-close'
# add dummy cache
chart2.series[-1].val.numRef.numCache = cache
ws.add_chart(chart2, 'I10')
# Create bar chart for volume
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)
# Volume-high-low-close
bar1 = deepcopy(bar)
chart3 = deepcopy(chart1)
chart3.y_axis.majorGridlines = None
chart3.y_axis.title = 'Price'
bar1.y_axis.axId = 20
bar1.z_axis = chart3.y_axis
bar1.y_axis.crosses = 'max'
bar1 += chart3
chart3.title = 'High low close volume'
ws.add_chart(bar1, 'A27')
# Volume-open-high-low-close
bar2 = deepcopy(bar)
chart4 = deepcopy(chart2)
# majorGridlines : 主要网格线
chart4.y_axis.majorGridlines = None
chart4.y_axis.title = 'Price'
bar2.y_axis.axId = 20
bar2.z_axis = chart4.y_axis
bar2.y_axis.crosses = 'max'
bar2 += chart4
ws.add_chart(bar2, 'I27')
wb.save('SampleStock.xlsx')
股价图
曲面图
可以在曲面图中绘制工作表中按列或行排列的数据。当您想在两组数据之间找到最佳组合时，曲面图很有用。与地图地形一样，颜色和图案表示在相同值范围内的区域。
默认情况下所有曲面图都是三维的。通过设置I旋转和透视图，可以创建二维线框和轮廓图。
from copy import deepcopy
from openpyxl import Workbook
from openpyxl.chart import SurfaceChart, SurfaceChart3D, Reference
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)
chart1 = 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)
chart1.add_data(ref, titles_from_data=True)
chart1.set_categories(labels)
chart1.title = 'Contour'
ws.add_chart(chart1, 'A12')
# wireframe :线框
chart2 = deepcopy(chart1)
chart2.wireframe = True
chart2.title = '2D Wireframe'
ws.add_chart(chart2, 'I12')
# 3D Surface
chart3 = SurfaceChart3D()
chart3.add_data(ref, titles_from_data=True)
chart3.set_categories(labels)
chart3.title = 'Surface'
ws.add_chart(chart3, 'A29')
chart4 = deepcopy(chart3)
chart4.wireframe = True
chart4.title = '3D Wireframe'
ws.add_chart(chart4, 'I29')
wb.save('SampleSurface.xlsx')
曲面图
来源：https://openpyxl.readthedocs.io/en/stable/charts/introduction.html#chart-types
网友评论