how to use openpyxl to create chart in excel
关翰
I collected all the supported chart in openpyxl in one script, please check it.
But the chart which openpyxl created seems not beautiful as manual created in excel.
from openpyxl import Workbook, load_workbook
from openpyxl.comments import Comment
from openpyxl.utils import units
from openpyxl.chart.series import DataPoint
from openpyxl.chart.axis import DateAxis, ChartLines
from openpyxl.chart.updown_bars import UpDownBars
import openpyxl
import random
from datetime import date
from openpyxl.chart import (
BarChart,
AreaChart,
LineChart,
BubbleChart,
PieChart,
ProjectedPieChart,
ScatterChart,
DoughnutChart,
RadarChart,
Reference,
StockChart,
Series,
)
def create_new_file(file_name):
wb = Workbook()
wb.save(file_name)
def create_chart(file_name):
wb = Workbook()
create_bar_chart(wb)
create_bar2_chart(wb)
create_area_chart(wb)
create_line_chart(wb)
create_line2_chart(wb)
create_bubble_chart(wb)
create_pie_chart(wb)
create_scatter_chart(wb)
create_doughnut_chart(wb)
create_radar_chart(wb)
create_stock_chart(wb)
wb.save(file_name)
def create_stock_chart(wb):
ws = wb.create_sheet("stock")
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, "K10")
# 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, "K27")
def create_radar_chart(wb):
ws = wb.create_sheet("radar")
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")
def create_doughnut_chart(wb):
ws = wb.create_sheet("doughnut")
data = [
['Pie', 2014, 2015],
['Plain', 40, 50],
['Jam', 2, 10],
['Lime', 20, 30],
['Chocolate', 30, 40],
]
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")
def create_scatter_chart(wb):
ws = wb.create_sheet("scatter")
rows = [
['Size', 'Batch 1', 'Batch 2', 'Batch 3', 'Batch 4', 'Batch 5', 'Batch 6', 'Batch 7'],
[2, 70, 40, 20, 30, 10, 10, 30],
[3, 20, 30, 10, 20, 48, 20, 25],
[4, 30, 40, 56, 10, 58, 40, 30],
[5, 30, 60, 32, 80, 33, 20, 25],
[6, 35, 75, 15, 15, 20, 35, 35],
[7, 40, 90, 20, 10, 25, 40, 40],
]
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 1
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, 9):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
# {'triangle', 'dash', 'x', 'auto', 'diamond', 'circle', 'star', 'picture', 'square', 'dot', 'plus'}
series.marker = openpyxl.chart.marker.Marker('circle')
series.graphicalProperties.line.noFill = True
# print(dir(series.graphicalProperties))
chart.series.append(series)
ws.add_chart(chart, "A10")
from copy import deepcopy
for i in range(2): # max is 48
if i > 1:
chart1 = deepcopy(chart)
chart1.style = i
ws.add_chart(chart1, "A" + str(i*15))
def create_pie_chart(wb):
ws = wb.create_sheet("pie")
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_col=1, 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"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
ws = wb.create_sheet(title="pie_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" # split by value
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' # split by position
ws.add_chart(projected_bar, "A27")
def create_bubble_chart(wb):
ws = wb.create_sheet("bubble")
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 # use a preset style
# add the first series of data
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)
# add the second
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)
# place the chart starting in cell E1
ws.add_chart(chart, "E1")
def create_bar2_chart(wb):
ws = wb.create_sheet("bar2")
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, "K10")
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, "K27")
def create_bar_chart(wb):
ws = wb.create_sheet("bar")
for i in range(10):
ws.append([i])
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, "E15")
def create_area_chart(wb):
ws = wb.create_sheet("area")
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=2, max_row=7) # attention for the category range
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")
def create_line2_chart(wb):
ws = wb.create_sheet("line2")
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)
# Style the lines
s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True
s2 = c1.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs
s2 = c1.series[2]
s2.smooth = True # Make the line smooth
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")
# Chart with date axis
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")
def create_line_chart(wb):
ws = wb.create_sheet("line")
# Let's create some sample sales data
rows = [
["", "January", "February", "March", "April",
"May", "June", "July", "August", "September",
"October", "November", "December"],
[1, ],
[2, ],
[3, ],
]
for row in rows:
ws.append(row)
for row in ws.iter_rows(min_row=2, max_row=4, min_col=2, max_col=13):
for cell in row:
cell.value = random.randrange(5, 100)
chart = LineChart()
data = Reference(worksheet=ws,
min_row=2,
max_row=4,
min_col=1, # because the chart now expects the first column to have the titles.
max_col=13)
# from_rows=True --> This argument makes the chart plot row by row instead of column by column.
chart.add_data(data, from_rows=True, titles_from_data=True)
cats = Reference(worksheet=ws,
min_row=1,
max_row=1,
min_col=2,
max_col=13)
chart.set_categories(cats)
chart.x_axis.title = "Months"
chart.y_axis.title = "Sales (per unit)"
# You can play with this by choosing any number between 1 and 48
chart.style = 24
ws.add_chart(chart, "A10")
chart2 = LineChart()
data2 = Reference(worksheet=ws,
min_row=2,
max_row=4,
min_col=1,
max_col=13)
chart2.add_data(data2)
chart2.height = chart2.height + 8
ws.add_chart(chart2, "A30")
def main():
create_chart("SampleChart.xlsx")
if __name__ == '__main__':
main()
类似资料：