Automate Excel Reports with Python and openpyxl

2026-11-21来源 dev.toEnglish

openpyxl is a Python library that reads and writes Excel files (.xlsx) without Excel installed. It matters because the sales report is still an Excel file in most workplaces, and rebuilding the same report by hand every month wastes hours. In this tutorial we build a monthly sales report from code, with live formulas, a summary sheet and a chart.

Key takeaways

  • openpyxl writes real .xlsx files with formulas, styles, filters and charts, and Excel is not needed to create them.
  • A string that starts with = is stored as a live Excel formula, and Excel calculates it when the file opens.
  • openpyxl never calculates formulas, so data_only=True returns None until Excel or LibreOffice has saved the file once.
  • pandas is the better tool for the arithmetic, while openpyxl is the better tool for the layout and formatting.
  • A totals row is a label row, not data, so drop it before a pandas groupby or it becomes a fake region.

What we build

We build a monthly sales report workbook called sales_report.xlsx. It has a Sales sheet with eight rows of sales data, a Revenue column that uses a live formula in every row, a totals row, frozen header panes and an auto filter. It also has a Summary sheet with one total per region, conditional formatting that highlights big totals, and a bar chart. The finished workbook holds 201 units sold and a grand total of $29,647.98, and it opens in Excel, LibreOffice and Google Sheets.

The numbers are small on purpose, so every total can be checked by hand: North $11,890.50, South $8,991.70, East $5,205.00 and West $3,560.78, for a grand total of $29,647.98.

Prerequisites

We need Python 3.12 and pip. We tested every step on Python 3.12.3 with openpyxl 3.1.5, and the last step also uses pandas 3.1.1. No Excel install is needed anywhere in this tutorial, because openpyxl writes the .xlsx file directly. Create a folder for the project and move into it:

mkdir excel-reports cd excel-reports pip install openpyxl python -c "import openpyxl; print(openpyxl.__version__)" 

Our run printed this:

3.1.5 

Step 1: Write the sales data and the headers

We start with a plain Python list of tuples, the kind of data that comes from a database query, an API or a CSV file. Each tuple is one sale: region, product, units sold and unit price. Then we create a workbook, take the active sheet, name it Sales, and append the header row and the eight data rows. The openpyxl documentation shows this same pattern in its first example, with Workbook, the active sheet, append and save, so this is the standard way to start a file.

import openpyxl SALES = [ ("North", "Laptop", 12, 899.00), ("North", "Mouse", 45, 24.50), ("South", "Laptop", 8, 899.00), ("South", "Keyboard", 30, 59.99), ("East", "Monitor", 15, 249.00), ("East", "Mouse", 60, 24.50), ("West", "Keyboard", 22, 59.99), ("West", "Monitor", 9, 249.00), ] HEADERS = ["Region", "Product", "Units", "Unit price", "Revenue"] wb = openpyxl.Workbook() ws = wb.active ws.title = "Sales" ws.append(HEADERS) for region, product, units, price in SALES: ws.append([region, product, units, price]) 

Nothing is saved yet. The workbook only exists in memory until we call wb.save, which is handy because it means a mistake halfway through costs nothing. The sheet now has nine rows: one header row and eight data rows.

Step 2: Add live formulas for revenue and totals

This step is the whole point of using openpyxl instead of writing plain values. Any string that starts with = is stored as an Excel formula, not as text. We put =C2*D2 in the Revenue cell of each row, so Excel multiplies units by unit price when the file opens. Then we add a totals row with =SUM formulas for the units column and the revenue column.

for row in range(2, 2 + len(SALES)): ws.cell(row=row, column=5, value=f"=C{row}*D{row}") total_row = 2 + len(SALES) ws.cell(row=total_row, column=1, value="Total").font = openpyxl.styles.Font(bold=True) ws.cell(row=total_row, column=3, value=f"=SUM(C2:C{total_row - 1})").font = openpyxl.styles.Font(bold=True) ws.cell(row=total_row, column=5, value=f"=SUM(E2:E{total_row - 1})").font = openpyxl.styles.Font(bold=True) 

The totals row lands on row 10, so the formulas read =SUM(C2:C9) and =SUM(E2:E9). Because total_row is computed from the length of SALES, the range stays right when the data grows.

Step 3: Style the sheet so it looks like a real report

A report that nobody wants to open is a report nobody reads. We give the header row a dark blue fill with white bold text, format the price and revenue columns as money with two decimals, set sensible column widths, freeze the header row and add an auto filter. Freezing the header means it stays visible while someone scrolls through hundreds of rows, and the filter lets them narrow the sheet to one region without touching the data.

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side header_font = Font(bold=True, color="FFFFFF", size=11) header_fill = PatternFill("solid", fgColor="1F3864") money_fmt = '#,##0.00' thin = Side(style="thin", color="BFBFBF") border = Border(bottom=thin) for cell in ws[1]: cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center") for row in ws.iter_rows(min_row=2, max_row=total_row, min_col=4, max_col=5): for cell in row: cell.number_format = money_fmt cell.border = border for col, width in zip("ABCDE", [12, 12, 10, 14, 14]): ws.column_dimensions[col].width = width ws.freeze_panes = "A2" ws.auto_filter.ref = f"A1:E{total_row - 1}" 

Two details matter here. First, number formats are display settings, not values: the cell still holds 899.0, and Excel shows it as 899.00. Second, column widths are set in character units, so a width of 14 fits a money value like 11,890.50 with room to spare. A column that is too narrow shows ### in Excel, which is the classic sign of a report built without widths set.

What does openpyxl do that pandas cannot?

pandas is the better tool for the arithmetic, and openpyxl is the better tool for the workbook. pandas can write a dataframe to .xlsx, but it writes values, not a finished report: no live formulas, no conditional formatting, no charts, no frozen panes and only basic styling. The pandas documentation states that pandas uses openpyxl as its engine for .xlsx files, so the two libraries are partners, not rivals. Our rule is simple: pandas computes the numbers, and openpyxl places them in a workbook that looks deliberate. Step 6 shows that split in action.

Step 4: Add a summary sheet, conditional formatting and a chart

The Sales sheet is the detail. The Summary sheet is the answer. We create a second sheet, write one row per region, and use a SUMIF formula in each row so the totals stay live: =SUMIF(Sales!A2:A9, A2, Sales!E2:E9) adds up every revenue cell whose region matches the label in column A. A grand total row uses =SUM(B2:B5). Then we add conditional formatting that fills any regional total above 8,000 in light green, and a column chart of revenue by region.

from openpyxl.formatting.rule import CellIsRule from openpyxl.chart import BarChart, Reference summary = wb.create_sheet("Summary") summary.append(["Region", "Total revenue"]) for cell in summary[1]: cell.font = header_font cell.fill = header_fill regions = ["North", "South", "East", "West"] for i, region in enumerate(regions, start=2): summary.cell(row=i, column=1, value=region) summary.cell(row=i, column=2, value=f'=SUMIF(Sales!A2:A{total_row - 1}, A{i}, Sales!E2:E{total_row - 1})') summary.cell(row=i, column=2).number_format = money_fmt summary.cell(row=6, column=1, value="Grand total").font = Font(bold=True) summary.cell(row=6, column=2, value="=SUM(B2:B5)").font = Font(bold=True) summary.cell(row=6, column=2).number_format = money_fmt summary.column_dimensions["A"].width = 14 summary.column_dimensions["B"].width = 16 green = PatternFill("solid", fgColor="C6EFCE") summary.conditional_formatting.add( "B2:B5", CellIsRule(operator="greaterThan", formula=["8000"], fill=green)) chart = BarChart() chart.type = "col" chart.title = "Revenue by region" chart.y_axis.title = "Revenue" chart.x_axis.title = "Region" data = Reference(summary, min_col=2, min_row=1, max_row=5) cats = Reference(summary, min_col=1, min_row=2, max_row=5) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) chart.shape = 4 summary.add_chart(chart, "D2") wb.save("sales_report.xlsx") print("saved sales_report.xlsx") 

Our run printed saved sales_report.xlsx, and the file is 7,979 bytes. The North and South totals clear 8,000, so those two cells get the green fill when the file opens. The chart is a native Excel chart, so it updates when the numbers change, which a pasted image never does.

Step 5: Read the file back and check the gotcha

Before trusting the file, we read it back with openpyxl and print what is actually stored. This step caught the single most important fact in this tutorial, so do not skip it.

from openpyxl import load_workbook wb = load_workbook("sales_report.xlsx") ws = wb["Sales"] print("E2:", repr(ws["E2"].value)) print("E10 (totals row):", repr(ws["E10"].value)) print("C10 (units total):", repr(ws["C10"].value)) wb2 = load_workbook("sales_report.xlsx", data_only=True) ws2 = wb2["Sales"] print("E2 with data_only:", repr(ws2["E2"].value)) print("E10 with data_only:", repr(ws2["E10"].value)) 

Our run printed this:

E2: '=C2*D2' E10 (totals row): '=SUM(E2:E9)' C10 (units total): '=SUM(C2:C9)' E2 with data_only: None E10 with data_only: None 

Why do formulas read back as None?

openpyxl writes formulas but never calculates them, because it has no calculation engine. A normal load returns the formula text, which is why E2 reads back as =C2*D2. Loading with data_only=True asks for the cached result instead, and our file has never been opened in Excel, so no cached result exists and every formula cell returns None. Nothing is broken: the number simply has never been computed by anything yet. Open the file in Excel or LibreOffice once and save it, and data_only=True returns real numbers from then on. Until then, compute the values in Python when code needs them. Our check did exactly that and confirmed the numbers: a grand total of 29,647.98 and 201 units, which match the hand calculation from the start of this tutorial.

The same trap waits inside pandas. pandas reads .xlsx files through openpyxl, so a formula column arrives as empty values. We read our file with pandas 3.1.1 and the whole Revenue column came back as NaN, all nine rows of it. The fix is the same: compute the column in pandas with df["Revenue"] = df["Units"] * df["Unit price"], and treat the workbook as layout, not as a source of calculated values.

Step 6: Let pandas compute and openpyxl present

The last step puts the split from the earlier question into code. pandas groups the sales by region and sums the revenue, and those numbers should match the SUMIF formulas in the workbook exactly. There is one trap, and our first run walked straight into it: the totals row is a label row, not data, so groupby counted it as a fifth region called Total with 0 revenue. Filtering it out before the groupby fixes the result.

import pandas as pd df = pd.read_excel("sales_report.xlsx", sheet_name="Sales") print("Revenue column read by pandas:", df["Revenue"].tolist()) df = df[df["Region"] != "Total"] df["Revenue"] = df["Units"] * df["Unit price"] totals = df.groupby("Region")["Revenue"].sum().round(2) print(totals.to_string()) print("grand total:", round(df["Revenue"].sum(), 2)) print("units total:", int(df["Units"].sum())) 

Our run printed this:

Revenue column read by pandas: [nan, nan, nan, nan, nan, nan, nan, nan, nan] Region East 5205.00 North 11890.50 South 8991.70 West 3560.78 grand total: 29647.98 units total: 201 

The pandas totals match the SUMIF formulas exactly: North 11,890.50, South 8,991.70, East 5,205.00 and West 3,560.78. Two independent calculations agreeing is a real check, not a feeling, and it is the check we would run before sending any report to a client.

What to try next

First, read the sales rows from a real CSV file instead of a list, so next month means swapping one file, not editing code. Second, add one sheet per region with its own chart, using the same loop that built the Summary sheet. Third, put the month in the filename, like sales_report_2026_11.xlsx, and schedule the script to run on the first of each month, so the report builds itself while the team is still having coffee.

Originally published on LogicStack. Run the code examples right in your browser with our free interactive Python and SQL playgrounds — no setup, no sign-up.

原文:https://www.a8q9.cn/en/article/automate-excel-reports-with-python-and-openpyxl-9ebfd1.html

相关文章