Automating Excel Formulas and Charts

Add formulas

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Qty", "Price", "Total"])
ws.append(["Pen", 10, 2.5, "=B2*C2"])
ws.append(["Book", 3, 12.0, "=B3*C3"])
 
wb.save("sales.xlsx")

Create a chart

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Total"])
ws.append(["Pen", 25])
ws.append(["Book", 36])
ws.append(["Ruler", 15])
 
data = Reference(ws, min_col=2, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
 
chart = BarChart()
chart.title = "Totals"
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
 
ws.add_chart(chart, "D2")
wb.save("sales_chart.xlsx")

Notes

• Charts can be limited vs native Excel features
• For heavy reporting, consider generating chart images (matplotlib) and embedding

How formulas work in openpyxl

When you assign a string that starts with =, openpyxl stores it as a formula. It does not calculate anything. Excel (or LibreOffice) calculates the value when someone opens the file. This has one consequence that surprises people: if you read the file back with load_workbook("sales.xlsx", data_only=True), the formula cells return None, because no program has calculated and cached a value yet. Open and save the file in Excel once, or compute the numbers in Python as well when you need them.

Formulas use English function names and commas as separators, whatever language your Excel uses. Cell references are plain text, so you build them with f-strings when you loop:

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.append(["Item", "Qty", "Price", "Total"])
 
for item, qty, price in [("Pen", 10, 2.5), ("Book", 3, 12.0), ("Ruler", 4, 1.5)]:
    ws.append([item, qty, price])
    row = ws.max_row
    ws.cell(row=row, column=4, value=f"=B{row}*C{row}")
 
last = ws.max_row
ws.append(["Grand total", None, None, f"=SUM(D2:D{last})"])
wb.save("sales_formulas.xlsx")

ws.max_row is the number of the row you just appended, so each formula points at its own row. The last line writes a SUM over the range.

How the chart works

A chart needs two things: a data reference and a categories reference.

• Reference(ws, min_col=2, min_row=1, max_row=4) selects column B from row 1 to row 4. Row 1 is the header, and titles_from_data=True uses it as the series name.
• Reference(ws, min_col=1, min_row=2, max_row=4) selects the labels in column A, without the header.
• ws.add_chart(chart, "D2") places the top-left corner of the chart at cell D2.

Other chart classes live in the same module: LineChart, PieChart, ScatterChart. They take the same data and categories calls. Set chart.width and chart.height (in centimetres) if the default size is too small.

Common mistakes

• Off-by-one ranges. If max_row is one too small, the last item is missing from the chart. Use ws.max_row instead of a hard-coded number.
• Including the header in the categories. The header then shows up as a bar label.
• Expecting values from formula cells. See the data_only note above.
• Overwriting a workbook with charts. openpyxl loses some existing charts and images when it loads and saves a file made in Excel. Keep the original and write to a new name.
• Localised function names. Write SUM, not SOMME, in the file.

Adapting it

Build the data from a CSV file, add a totals row, and choose the chart type by report. For anything with several charts or fine styling, make the file in Excel once as a template, then have Python fill in the data.

Check your understanding