Sending Charts From a Query to an Excel Sheet by Script

A table of fourteen months of revenue by region is accurate and slow to read. A line chart on the same sheet shows in a second that the north region has been falling since March. Generating that chart in the same job that writes the numbers removes the manual step where someone selects the range, picks a type and drags it into place, and it makes the chart reproducible. This article shows how to do it with XlsxWriter and openpyxl, how to choose the chart, and how to keep the range right when the data changes size. It extends building Excel reports from SQL queries people trust, where the table is already on the sheet.

Write the data first

A native chart does not hold data. It holds references such as =Data!$B$2:$B$15, and Excel draws whatever is in those cells. So write the result to a sheet, and compute the row numbers from the size of the result.

import pandas as pd

df = pd.DataFrame({
    "month": pd.to_datetime(["2026-07-01", "2026-08-01", "2026-09-01"]),
    "north": [120_400, 118_900, 109_300],
    "south": [98_200, 101_500, 104_800],
})

with pd.ExcelWriter("revenue.xlsx", engine="xlsxwriter") as xw:
    df.to_excel(xw, sheet_name="Data", index=False)
    wb, ws = xw.book, xw.sheets["Data"]
    n = len(df)

n is the number of data rows. Everything that follows uses it, so a month with more rows draws a longer chart without a code change. The header is in row 1 (index 0) and data runs from row 2 to row n + 1.

Add a line chart with XlsxWriter

chart = wb.add_chart({"type": "line"})

for col, name in ((1, "North"), (2, "South")):
    chart.add_series({
        "name":       ["Data", 0, col],
        "categories": ["Data", 1, 0, n, 0],
        "values":     ["Data", 1, col, n, col],
        "marker":     {"type": "circle", "size": 5},
    })

chart.set_title({"name": "Net revenue by region"})
chart.set_x_axis({"name": "Month", "date_axis": True, "num_format": "mmm yyyy"})
chart.set_y_axis({"name": "Net revenue (USD)", "num_format": "#,##0", "major_gridlines": {"visible": True}})
chart.set_legend({"position": "bottom"})
chart.set_size({"width": 720, "height": 360})

ws.insert_chart("E2", chart)

Each series takes a name, categories and values as [sheet, first_row, first_col, last_row, last_col] lists with zero-based indexes. The name list points at the header cell so a renamed header renames the series. date_axis makes Excel treat the x values as dates and space them in time, which matters when months are missing. The chart goes at E2, to the right of the data, where it does not cover the numbers. The calls are documented in the XlsxWriter chart reference.

For a comparison between categories, change the type: {"type": "column"} for vertical bars or {"type": "bar"} for horizontal bars, and add "subtype": "stacked" when the parts sum to a whole.

The same chart with openpyxl

openpyxl is the right choice when you add a chart to a workbook you are already building with it.

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws.append(["month", "north", "south"])
for row in df.itertuples(index=False):
    ws.append([row.month.to_pydatetime(), row.north, row.south])

chart = LineChart()
chart.title = "Net revenue by region"
chart.y_axis.title = "Net revenue (USD)"
chart.x_axis.title = "Month"
chart.x_axis.number_format = "mmm yyyy"

data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=len(df) + 1)
cats = Reference(ws, min_col=1, min_row=2, max_row=len(df) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height, chart.width = 9, 18

ws.add_chart(chart, "E2")
wb.save("revenue_openpyxl.xlsx")

titles_from_data=True takes series names from the first row of the reference. Sizes are in centimeters. The openpyxl charts documentation covers bar, pie, scatter and area charts, and secondary axes. A known quirk: recent Excel versions can hide axes in an openpyxl chart unless chart.x_axis.delete = False and chart.y_axis.delete = False are set, so set them if axes vanish (adding charts to an Excel sheet from Python has the details).

Choose the chart for the question

The chart type should answer a question the reader has.

Sort bar charts by value, not alphabetically, unless the order of the categories carries meaning. Avoid 3D effects: the depth distorts comparison and adds nothing. Avoid pie charts beyond four slices, because angles are hard to compare. A dual axis chart, with two y axes in different units, invites the reader to see a relationship that the scaling creates (avoiding misleading axes in Excel charts covers the traps).

Make it readable without you

A chart goes to people who were not in the room. Give it:

• A title that says what it shows, with the unit and period if they are not in the axes.

• Axis titles with units.

• Number formats on the axes that match the data: thousands separators, percentages, dates.

• A legend only when there is more than one series, placed where it does not crowd the plot.

• Consistent colors across charts in one report, so "north" is the same color everywhere.

• A size that fits the sheet. About 700 by 350 pixels reads well on a laptop.

Skip decorations: gridlines in light gray at most, no background images, no data labels on every point of a long series. Where precise values matter, the table sits beside the chart.

Keep the range tied to the result

The most common chart bug in generated reports is a fixed range. The chart was built for twelve rows, next month returns thirteen and the thirteenth is missing, or a month returns eleven and the chart shows an empty gap. Computing the last row from len(df) avoids both. For a chart that must update when someone adds rows by hand to the sheet afterward, base it on an Excel table or a named range that grows (refreshing charts from a changing data range goes through the options).

Handle an empty result explicitly. A query that returns no rows should produce a sheet that says "no data for this period" and no chart, not a chart with a blank plot area.

Several charts on one sheet

A report page with two or three charts needs a layout. Place charts on a grid by cell anchors: E2, E22, P2. Give each the same width and height so they align. Put the most important chart top left, where the eye starts. When charts share an x axis, such as months, keep the same date range in each. If you need many charts, one per region for example, generate them in a loop from the data, and cap the count so a new region does not produce a sheet of 80 charts.

Check the file

Open the output in Excel or LibreOffice once with the first run and look. Does every series have the right name? Do the axes show the format you set? Is the chart over the data? Then add a cheap automated check: reopen the saved workbook with openpyxl and assert that the sheet has one chart (len(ws._charts) == 1) and that the data range sizes match the result. Charts are the part of a workbook that is hardest to test by reading values, so a manual look at the first build and a structural check on every run is a fair balance.

When a native Excel chart is the wrong output

A native chart is the wrong output when the chart will be pasted into a slide or a PDF and nobody needs to edit it. A rendered image from matplotlib or a charting library looks the same everywhere and does not depend on the reader's Excel version (exporting Excel charts as images from a script covers the options). It is also wrong when the data has thousands of points, where a native chart becomes slow and cluttered, and when the audience reads on a phone, where a small image is more reliable than a workbook. For a chart type Excel lacks, such as a heatmap on a map, use a dedicated tool and link to it.