Automated Excel reports with Python and openpyxl

Many office reports come about the same way: you export data from a management system, paste it into an Excel file, add totals, format the header, adjust the column widths and send the file. Every week. With the openpyxl library a Python script can produce the finished file, already formatted and ready to send, without opening Excel and even on a computer where Excel isn't installed.

What openpyxl does

openpyxl reads and writes .xlsx files by working directly on their internal structure. It doesn't drive Excel the way a macro would: it creates the file as Excel itself would. This has an important consequence: formulas are written but not calculated. The result appears when the file is opened in Excel or another spreadsheet program, which recalculates it.

pip install openpyxl

Creating the file and writing data

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"

ws.append(["Date", "Customer", "Product", "Quantity", "Price", "Total"])
sales = [
    ("2025-03-03", "Rossi Ltd", "Licence", 2, 120.0),
    ("2025-03-04", "Bianchi plc", "Support", 5, 60.0),
]
for row, (day, customer, product, qty, price) in enumerate(sales, start=2):
    ws.append([day, customer, product, qty, price, f"=D{row}*E{row}"])

wb.save("weekly_sales.xlsx")

append adds a row at the bottom of the sheet. The Total column contains a real formula, not a number calculated in Python: whoever receives the file can see where the value comes from and can change it. Formulas must be written with English function names and a comma as the argument separator, for example =SUM(F2:F50), even if the file will be opened in a localised Excel, which will display them in its own language.

Real dates, not text

In the example the dates are strings: Excel will see them as text and won't be able to sort them or filter them by month. Just pass Python date objects and set the display format:

from datetime import date

ws["A2"] = date(2025, 3, 3)
ws["A2"].number_format = "DD/MM/YYYY"

The same goes for amounts: the number stays a number, and the format decides how it looks.

for cell in ws["E"][1:] + ws["F"][1:]:
    cell.number_format = "#,##0.00 €"

The format code uses the English convention (comma for thousands, dot for decimals); Excel will display it according to the locale settings of whoever opens the file.

Formatting the header

A readable report has a clear header. openpyxl provides objects for font, fill and alignment:

from openpyxl.styles import Font, PatternFill, Alignment

for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="305496")
    cell.alignment = Alignment(horizontal="center")

Widths, filters and frozen panes

Three touches that make the file look carefully prepared:

widths = {"A": 12, "B": 24, "C": 18, "D": 10, "E": 12, "F": 14}
for column, width in widths.items():
    ws.column_dimensions[column].width = width

ws.auto_filter.ref = ws.dimensions   # filter on the whole table
ws.freeze_panes = "A2"               # header always visible

openpyxl has no automatic column fit like Excel's double-click, because it doesn't know the real width of the characters. You can approximate it by computing the length of the longest text in each column, but a table of fixed widths is often simpler and more predictable.

The totals row

last = ws.max_row
ws.cell(row=last + 1, column=5, value="Total").font = Font(bold=True)
ws.cell(row=last + 1, column=6, value=f"=SUM(F2:F{last})").font = Font(bold=True)

max_row returns the last used row: the total adapts by itself to the number of rows each week.

Adding a chart

openpyxl can insert native Excel charts that the recipient can edit:

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Total per sale"
data = Reference(ws, min_col=6, min_row=1, max_row=last)
categories = Reference(ws, min_col=2, min_row=2, max_row=last)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "H2")

Organising the script

In my work I always split these scripts into three distinct parts: reading the data, calculations, writing the file. If tomorrow the management system changes its export format, only the first part changes; if the recipient wants a different colour, only the last. It is also worth putting the date in the file name, for example sales_2025-03-10.xlsx, so versions don't overwrite one another.

In short

• openpyxl creates complete Excel files without needing Excel installed.
• Formulas are written in English and calculated when the file is opened.
• Use real dates and numbers with number_format, not text.
• A formatted header, filters and frozen panes make the report readable.
• Keep reading, calculations and writing separate for a script that is easy to maintain.