CODEWITHSUNDEEP
pythonexcelchartsopenpyxl
Apollinaris
Apollinaris

Reputation: 21

Openpyxl charts - data series from random unconnected cells possible?

I'm currently experimenting with the python module openpyxl, trying to automate some tasks at work and generate spreadsheets automatically. For one of the required sheets I need to generate a scatter chart from tabulated data. However, the scatter chart should consist from multiple lines connecting two points each, so each of the individual x/y series in the scatter chart should connect two points only.

Generally I found from the openpyxl documentation that scatter charts are generated like in this small example:

from openpyxl import Workbook
from openpyxl.chart import (
    ScatterChart,
    Reference,
    Series,
)

wb = Workbook()
ws = wb.active

rows = [
    ['Size', 'Batch 1', 'Batch 2'],
    [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)

chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'

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)
    chart.series.append(series)

ws.add_chart(chart, "A10")

wb.save("scatter.xlsx")

However, the x (and y) coordinates of the two points I would like to connect in the scatter points are not located in adjacent cells. So when I import the data series manually in excel by holding 'ctrl' and select two cells I get something like this:

'Sheet!$A$4;Sheet!$A$6'

instead of

'Sheet!$A$4:$A$6'

when dragging the cursor to select a range of cells.

For only two individual not-adjacent cells this means that I do not have a clear min_row/min_col/max_row etc.. but only a list of cell pairs (for both x and y). Is there a way create a data series in openpyxl as a list of cells instead of a connected/adjacent range?

Help would be much appreciated! :)

Upvotes: 2

Views: 4582

Answers (2)

ryry1985
ryry1985

Reputation: 336

Are you sure this doesn't work? I've modified the example for a situation like yours and it seems to work for me:

from openpyxl import Workbook
from openpyxl.chart import (
    ScatterChart,
    Reference,
    Series,
)

wb = Workbook()
ws = wb.active

rows = [
    ['Size'],
    [2, 'Batch 1', 'Batch 2'],
    [3, 40, 30],
    [4, 40, 25],
    [5, 50, 30],
    [6, 30, 25],
    [7, 25, 35],
    [None, 20, 40],
]

for row in rows:
    ws.append(row)

chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'

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=2, max_row=8)
    series = Series(values, xvalues, title_from_data=True)
    chart.series.append(series)

ws.add_chart(chart, "A10")

wb.save("scatter.xlsx")

Result:

Screenshot of output spreadsheet

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19537

There are currently no plans to support non-contiguous cell ranges in chart series. I would suggest you try and arrange your data or create references to it that will allow you to work with contiguous ranges.

Upvotes: 0

Related Questions