Openpyxl bar chart with two different Reference

Question

I have a problem with building complex barchart in openpyxl v3.1.1. All guides have simple example:

chart1 = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)  # Y data
cats = Reference(ws, min_col=1, min_row=2, max_row=7) # X data
chart1.add_data(data, titles_from_data=True)
# or series_2 = Series(data_dec, title=f"Title") when Title not the first row
# chart1.append(series_2)
chart1.set_categories(cats)
ws.add_chart(chart1, "A10")  # draw chart

But Reference contains only one block of "rows", but I need more then one. In MS Excel I can do it with press Ctrl and choose another block of "rows".

Is it possible to do same in openpyxl?

Tiny example illustrating my problem I got table

Data-1	Data-2
2010-Jan-1	0.1
2010-Jan-2	0.2
2010-Jan-1	0.3
2010-Feb-1	0.4
2010-Feb-2	0.5
2010-Feb-3	0.6
2011-Jan-1	0.7
2011-Jan-2	0.8
2011-Jan-3	0.9

I need to build one chart of January (2010-Jan-1, 2010-Jan-2, 2010-Jan-3, 2011-Jan-1, 2011-Jan-2, 2011-Jan-3) and in MS Excel that cells I can pick with Ctrl button and got formula like this: =test_1!$A$2:$A$4;test_1!$A$8:$A$10

But in openpyxl can't find anything to unite two References.

Answer 1

Examples of creating the chart using XLSXWriter and XlWings.

XLSXWRITER
With xlsxwriter you can just enter the non contiguous range as =test_1!$A$2:$A$4;test_1!$A$8:$A$10 although this does trigger a warning message as xlsxwriter cannot extract the sheet name from this range correctly. It indicates the sheet name (as extracted) does not exist in the workbook's list of sheets but otherwise doesn't appear to affect the creation of the workbook and chart.

sheet_name = 'test_1'
workbook = xlsxwriter.Workbook("xlsxwriter_chart.xlsx")
worksheet = workbook.add_worksheet(sheet_name)

### Formatting for data
bold = workbook.add_format({"bold": 1})
# date_format = workbook.add_format({"num_format": "d/mm/yyyy"})
date_format = workbook.add_format({"num_format": "yyyy-mmm-d"})
value_format = workbook.add_format({"num_format": "0.0"})

# Worksheet data that the charts will use
headings = ['Data-1', 'Data-2']
data_list = [
    [40179, 0.1],
    [40180, 0.2],
    [40181, 0.3],
    [40210, 0.4],
    [40211, 0.5],
    [40212, 0.6],
    [40544, 0.7],
    [40545, 0.8],
    [40546, 0.9],
]

### Add data to Sheet with some formatting
worksheet.write_row("A1", headings, bold)
for row, data in enumerate(data_list, 2):
    worksheet.write(f"A{row}", data[0], date_format)
    worksheet.write(f"B{row}", data[1], value_format)

### Create the Bar chart.
chart = workbook.add_chart({"type": "bar"})

### Adjust chart objects to display values better
chart.set_x_axis({
    'major_gridlines': {
        'visible': False,
    },
})
chart.set_y_axis({
    "date_axis": True,
    "min": date(2009, 12, 1),
    "max": date(2011, 2, 1),
})

# Configure the series ranges.
chart.add_series(
    {
        "categories": f"={sheet_name}!$A$2:$A$4,{sheet_name}!$A$8:$A$10",
        "values": f"={sheet_name}!$B$2:$B$4,{sheet_name}!$B$8:$B$10",
        "gridline": False
    }
)

### Insert the chart into the worksheet (with an offset).
worksheet.insert_chart("D2", chart, {"x_offset": 25, "y_offset": 10})

### Widen column A for date values
worksheet.set_column('A:A', 10)

### Save workbook
workbook.close(

XLWINGS
Similarly you can just enter the non contiguous range either with 'set_source_data' or 'FullSeriesCollection' though FullSeriesCollection needs the sheet name included in the range.

import xlwings as xw
from xlwings import constants

with xw.App(visible=False) as app:
    wb = xw.Book()
    ws = wb.sheets[0]
    ws.name = 'test_1'

    # Add the worksheet data to be plotted.
    data = [10, 40, 50, 20, 10, 50]
    data_list = [
        ['Data-1', 'Data-2'],
        [40179, 0.1],
        [40180, 0.2],
        [40181, 0.3],
        [40210, 0.4],
        [40211, 0.5],
        [40212, 0.6],
        [40544, 0.7],
        [40545, 0.8],
        [40546, 0.9],
    ]
    ### Format the cells with data
    ws.range('A1').value = data_list
    ws.range('A1:B1').font.bold = True
    ws.range('A2:A10').number_format = "yyyy-mmm-d"

    ### Create new chart object Chart.
    chart = ws.charts.add(left=200, top=10, width=520, height=380)

    ### Add Chart and set type
    chart.api[1].SeriesCollection().NewSeries()
    chart.api[1].FullSeriesCollection(1).ChartType = constants.ChartType.xlBarClustered

    ### Select non-contiguous range for X axis
    chart.set_source_data(ws.range('B2:B4, B8:B10'))
    ### Select non-contiguous range for Y axis
    chart.api[1].FullSeriesCollection(1).XValues = f"={ws.name}!A2:A4,{ws.name}!A8:A10"

    ### Set date range scale
    chart.api[1].Axes(constants.AxisType.xlCategory).MinimumScale = 40148
    chart.api[1].Axes(constants.AxisType.xlCategory).MaximumScale = 40575
    chart.api[1].Axes(2).MajorGridlines.Delete()

    wb.save('xlwings_chart.xlsx')
    wb.close()

As mentioned the chart may not be very useful though with the values squashed at top and bottom