# 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 inrange(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 inrange(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 inrange(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")