def test_series(self, BarChart): from openpyxl25.chart import Series s1 = Series(values="Sheet1!$A$1:$A$10") s2 = Series(values="Sheet1!$B$1:$B$10") bc = BarChart(ser=[s1, s2]) xml = tostring(bc.to_tree()) expected = """ <barChart> <barDir val="col"></barDir> <grouping val="clustered"></grouping> <ser> <idx val="0"></idx> <order val="0"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$A$1:$A$10</f> </numRef> </val> </ser> <ser> <idx val="1"></idx> <order val="1"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$B$1:$B$10</f> </numRef> </val> </ser> <gapWidth val="150" /> <axId val="10" /> <axId val="100" /> </barChart> """ diff = compare_xml(xml, expected) assert diff is None, diff
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")
[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 # Half-size chart, bottom right ch2 = deepcopy(ch1) ch2.title = "Manual chart layout"
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")
] # 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 ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
def test_write(self, AreaChart): s1 = Series(values="Sheet1!$A$1:$A$12") s2 = Series(values="Sheet1!$B$1:$B$12") chart = AreaChart(ser=[s1, s2]) xml = tostring(chart._write()) expected = """ <chartSpace xmlns="http://schemas.openxmlformats.org/drawingml/2006/chart"> <chart> <plotArea> <areaChart> <grouping val="standard"></grouping> <ser> <idx val="0"></idx> <order val="0"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$A$1:$A$12</f> </numRef> </val> </ser> <ser> <idx val="1"></idx> <order val="1"></order> <spPr> <a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:prstDash val="solid" /> </a:ln> </spPr> <val> <numRef> <f>Sheet1!$B$1:$B$12</f> </numRef> </val> </ser> <axId val="10"></axId> <axId val="100"></axId> </areaChart> <catAx> <axId val="10"></axId> <scaling> <orientation val="minMax"></orientation> </scaling> <axPos val="l" /> <majorTickMark val="none" /> <minorTickMark val="none" /> <crossAx val="100"></crossAx> <lblOffset val="100"></lblOffset> </catAx> <valAx> <axId val="100"></axId> <scaling> <orientation val="minMax"></orientation> </scaling> <axPos val="l" /> <majorGridlines/> <majorTickMark val="none" /> <minorTickMark val="none" /> <crossAx val="10"></crossAx> </valAx> </plotArea> <legend> <legendPos val="r"></legendPos> </legend> <plotVisOnly val="1" /> <dispBlanksAs val="gap"></dispBlanksAs> </chart> </chartSpace> """ diff = compare_xml(xml, expected) assert diff is None, diff
(), (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")
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")