Excel line charts - "Show empty cells as" functionality

Issue #877 resolved
Aaron Black created an issue

I'm using openpyxl 2.4.8 to generate a set of data points and create a chart from them. There is a substantial amount of open space in the data set, and the resulting graph shows these gaps as the default behavior. Within Excel there is an option to change the display behavior for these empty cells, but I have been unable to find that option within openpyxl.

I've attached screenshots to show the data set and resulting graph with the empty cells displaying as gaps and alternately as connected (the third option as zeroes is not included).

Comments (4)

  1. CharlieC

    Thanks for the report. There is a dispBlanksAs property of the ChartContainer but this currently isn't accessible to client code.

  2. James james

    I tried to use the display_blank attribute, but it does not work. The graph has always a blank, regardless the value put to display_blank (span, gap, zero).

    Below is an example and the result in Excel. I am missing something or there is still a problem with this ?

    I am using openpyxl 3.0.2.

    from openpyxl import Workbook
    from openpyxl.chart import ScatterChart, Series, Reference
    
    wb = Workbook()
    ws = wb.active
    
    ws['A2'] = 1
    ws['A3'] = 2
    ws['A4'] = 3 
    ws['A5'] = 4
    ws['A6'] = 5
    
    ws['B2'] = 2
    ws['B3'] = 4
    ws['B5'] = 16
    ws['B6'] = 32
    
    chart = ScatterChart()
    chart.display_blank = 'gap'
    value = Reference(ws, min_col=2, min_row=2, max_row=6, max_col=2)
    axis = Reference(ws, min_col=1, min_row=2, max_row=6, max_col=1)
    series = Series(value, xvalues=axis)
    series.marker.symbol = 'circle'
    chart.append(series)
    ws.add_chart(chart, 'B8')
    
    wb.save('mwe.xlsx')
    wb.close()
    

  3. Log in to comment