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")