Python openpyxl甜甜圈实例
原创甜甜圈
# Doughnut charts are similar to pie charts except that they use a ring instead of a circle.
# They can also plot several series of data as concentric rings.
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
# Cut the first slice out of the doughnut
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")
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")
雷达图
# Data that is arranged in columns or rows on a worksheet can be plotted in a radar chart.
# Radar charts compare the aggregate values of multiple data series.
# It is effectively a projection of an area chart on a circular x-axis.
#
# There are two types of radar chart: standard, where the area is marked with a line;
# and filled, where the whole area is filled. The additional type “marker” has no effect.
# If markers are desired these can be set for the relevant series.
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")
散布图
# Scatter, or xy, charts are similar to some line charts.
# The main difference is that one series of values is plotted against another.
# This is useful where values are unordered.
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")
Stock Chart
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
# marker for close
s.marker.symbol = "dot"
s.marker.size = 5
c1.title = "High-low-close"
c1.hiLowLines = ChartLines()
# Excel is broken and needs a cache of values in order to display hiLoLines :-/
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"
# add dummy cache
c2.series[-1].val.numRef.numCache = cache
ws.add_chart(c2, "G10")
# 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)
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")
表面图
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
# wireframe
c2 = deepcopy(c1)
c2.wireframe = True
c2.title = "2D Wireframe"
ws.add_chart(c2, "G12")
# 3D Surface
c3 = SurfaceChart3D()
c3.add_data(ref, titles_from_data=True)
c3.set_categories(labels)
c3.title = "Surface"
ws.add_chart(c3, "A29")
c4 = deepcopy(c3)
c4.wireframe = True
c4.title = "3D Wireframe"
ws.add_chart(c4, "G29")
wb.save("surface.xlsx") 版权声明
所有资源都来源于爬虫采集,如有侵权请联系我们,我们将立即删除
itfan123




