Can't create chart with range containing Excel Formula
Created originally on Bitbucket by Anonymous
I am trying to chart a range that contains Excel Calculations. When I add_serie to the chart the series fails with the following exception: {{{ #!python from openpyxl.workbook import Workbook from openpyxl.chart import ScatterChart, Serie, Reference import random
def test_scatterchart_formula(): """This code demonstrates a bug in the chart code when one of the ranges contains an Excel Formula""" wb = Workbook() ws = wb.get_active_sheet() ws.title = u'scatterdata'
ws.cell(row=0, column=1).value = 'Scatter 1'
ws.cell(row=0, column=2).value = 'Scatter 2'
for i in range(1,12):
ws.cell(row=i, column=0).value = (i-1) / 10.
ws.cell(row=i, column=1).value = random.random()
ws.cell(row=i, column=2).value = '=A%u+B%u' % (i+1,i+1)
xyserie1 = Serie( Reference(ws, (1,1), (11,1)),
xvalues=Reference(ws, (1,0), (11,0)),
legend=Reference(ws, (0,1)))
xyserie2 = Serie( Reference(ws, (1,2), (11,2)),
xvalues=Reference(ws, (1,0), (11,0)),
legend=Reference(ws, (0,2)))
scatterchart = ScatterChart()
scatterchart.add_serie(xyserie1)
scatterchart.add_serie(xyserie2)
scatterchart.x_axis.min = 0
scatterchart.x_axis.max = 1
scatterchart.x_axis.unit = 0.2
scatterchart.y_axis.min = 0
scatterchart.y_axis.max = 1
scatterchart.y_axis.unit = 0.2
# chart container dimensions in pixels
scatterchart.drawing.left = 200
scatterchart.drawing.top = 20
scatterchart.drawing.height = 200
scatterchart.drawing.width = 500
# chart area in percentage of the container
scatterchart.width = .7
scatterchart.height = .7
scatterchart.margin_top = .2
ws.add_chart(scatterchart)
wb.save(r'chart.xlsx')
Traceback (most recent call last): File "./chart_bug.py", line 57, in ? test_scatterchart_formula() File "./chart_bug.py", line 30, in test_scatterchart_formula scatterchart.add_serie(xyserie2) File "//openpyxl/openpyxl/chart.py", line 217, in add_serie self._compute_min_max() File "/openpyxl/openpyxl/chart.py", line 259, in _compute_min_max maxi = math.ceil(maxi * 1.1) TypeError: can't multiply sequence by non-int }}}
The issues is that the cell does not have an actual value until it is opened in Excel (or other spreadsheet). I am not sure if the max/min calculation is vital when the sheet is written or not.