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) ch1 = ScatterChart() 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) ch1.series.append(series) ch1.title = "Default layout" ch1.style = 13 ch1.x_axis.title = 'Size' ch1.y_axis.title = 'Percentage' ch1.legend.position = 'r' ws.add_chart(ch1, "B10") from copy import deepcopy
chart4.title = "Both Log Scale" chart4.x_axis.title = 'x (log10)' chart4.y_axis.title = 'y (log10)' chart4.legend = None chart4.x_axis.scaling.logBase = 10 chart4.y_axis.scaling.logBase = 10 chart5 = ScatterChart() chart5.title = "Log Scale Base e" chart5.x_axis.title = 'x (ln)' chart5.y_axis.title = 'y (ln)' chart5.legend = None chart5.x_axis.scaling.logBase = math.e chart5.y_axis.scaling.logBase = math.e x = Reference(ws, min_col=1, min_row=2, max_row=22) y = Reference(ws, min_col=2, min_row=2, max_row=22) s = Series(y, xvalues=x) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) chart5.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "I1") ws.add_chart(chart3, "C15") ws.add_chart(chart4, "I15") ws.add_chart(chart5, "F30") wb.save("log.xlsx")
(1,), (2,), (3,), (2,), (3,), (3,), (1,), (2,), ] for r in rows: ws.append(r) c = BarChart() data = Reference(ws, min_col=1, min_row=1, max_row=8) c.add_data(data, titles_from_data=True) c.title = "Chart with patterns" # set a pattern for the whole series series = c.series[0] fill = PatternFillProperties(prst="pct5") fill.foreground = ColorChoice(prstClr="red") fill.background = ColorChoice(prstClr="blue") series.graphicalProperties.pattFill = fill # set a pattern for a 6th data point (0-indexed) pt = DataPoint(idx=5) pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz") series.dPt.append(pt)
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")
from openpyxl25 import Workbook from openpyxl25.chart import PieChart, Reference, Series wb = Workbook() ws = wb.active cs = wb.create_chartsheet() rows = [ ["Bob", 3], ["Harry", 2], ["James", 4], ] for row in rows: ws.append(row) chart = PieChart() labels = Reference(ws, min_col=1, min_row=1, max_row=3) data = Reference(ws, min_col=2, min_row=1, max_row=3) chart.series = (Series(data), ) chart.title = "PieChart" cs.add_chart(chart) wb.save("demo.xlsx")
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")
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' # Create a second chart 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"
chart3 = ScatterChart() chart3.title = "Flip Y" chart3.x_axis.title = 'x' chart3.y_axis.title = 'y' chart3.legend = None chart3.x_axis.scaling.orientation = "minMax" chart3.y_axis.scaling.orientation = "maxMin" chart4 = ScatterChart() chart4.title = "Flip Both" chart4.x_axis.title = 'x' chart4.y_axis.title = 'y' chart4.legend = None chart4.x_axis.scaling.orientation = "maxMin" chart4.y_axis.scaling.orientation = "maxMin" x = Reference(ws, min_col=2, min_row=2, max_row=102) y = Reference(ws, min_col=3, min_row=2, max_row=102) s = Series(y, xvalues=x) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) ws.add_chart(chart1, "D1") ws.add_chart(chart2, "J1") ws.add_chart(chart3, "D15") ws.add_chart(chart4, "J15") wb.save("orientation.xlsx")
[100], ] # based on http://www.excel-easy.com/examples/gauge-chart.html wb = Workbook() ws = wb.active for row in data: ws.append(row) # First chart is a doughnut chart c1 = DoughnutChart(firstSliceAng=270, holeSize=50) c1.title = "Code coverage" c1.legend = None ref = Reference(ws, min_col=1, min_row=2, max_row=5) s1 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(4)] slices[0].graphicalProperties.solidFill = "FF3300" # red slices[1].graphicalProperties.solidFill = "FCF305" # yellow slices[2].graphicalProperties.solidFill = "1FB714" # green slices[3].graphicalProperties.noFill = True # invisible s1.data_points = slices c1.series = [s1] # Second chart is a pie chart c2 = PieChart(firstSliceAng=270) c2.legend = None
[ 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")
[ '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 openpyxl25.chart.data_source import NumData, NumVal pts = [NumVal(idx=i) for i in range(len(data) - 1)]
from openpyxl25 import Workbook from openpyxl25.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")
(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") wb.save("bubble.xlsx")