Can't create chart with range containing Excel Formula

Issue #103 resolved
Anonymous created an issue

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.

Comments (11)

  1. Anonymous

    I have found a solution to this problem. The code needs to write a dummy value into the c value in the xml. The value must also be referenced as a numref in the xml document. When the sheet is opened and all the calculations are updated the dummy values will be overwritten with the correct values. The changes to the code are minor. I will create a test and check the code in once I completely tested it on my local cases.

  2. CharlieC

    @Camilla Karud yes, I'm hoping to have this in 2.2. The current 2.2 branch no longer bothers to write out any values so it's no longer essential for the series to have them. Unfortunately, however, there's still quite a bit of code that assumes that there are cell values.

  3. CharlieC

    As we no longer write cached values for formula we don't need to enforce logic that depends upon it. More work needed to avoid trying to calclulate any values for the chart based on series. This should be entirely the responsibility of client code. Resolves #103

    → <<cset 9075e110ea0e>>

  4. CharlieC

    This is fixed in 2.2. FWIW the chart API has changed slightly since the issue was filed. The following code now works.

    import random
    
    from openpyxl import Workbook
    from openpyxl.charts import ScatterChart
    from openpyxl.charts import Reference, Series
    
    wb = Workbook()
    ws = wb.active
    
    ws.cell(row=1, column=2).value = 'Scatter 1'
    ws.cell(row=1, column=3).value = 'Scatter 2'
    for i in range(2, 12):
        ws.cell(row=i, column=1).value = (i-1) / 10.
        ws.cell(row=i, column=2).value = random.random()
        ws.cell(row=i, column=3).value = '=A%u+B%u' % (i,i)
    
    xyserie1 = Series(Reference(ws, (2,2), (11,2)),
                      xvalues=Reference(ws, (1,2), (11,2)),
                      title="Scatter 1")
    
    xyserie2 = Series(Reference(ws, (2,3), (11,3)),
                      xvalues=Reference(ws, (1,2), (11,2)),
                      title="Scatter 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('Issues/bug103.xlsx')
    
  5. Log in to comment