How to automate Excel reports with Python (without macros breaking)

A working pattern for turning a folder of spreadsheets into a finished Excel summary and a Word report — plus the four things that break first.

If you produce the same Excel report every week, the actual work is not the analysis — it is the copy-paste. The fix is a script with four stages: read → normalize → merge → render. Here is the pattern that survives contact with real files.

1. Read every file, one at a time

import pandas as pd, os
for fn in sorted(os.listdir(folder)):
    if not fn.lower().endswith((".xlsx", ".xls", ".csv")) or fn.startswith("~$"):
        continue          # ~$ files are Excel lock files — reading them fails
    df = pd.read_excel(os.path.join(folder, fn)) if not fn.endswith(".csv") \
         else pd.read_csv(os.path.join(folder, fn), encoding="utf-8-sig")

2. Normalize each file BEFORE merging

This is the step almost everyone gets wrong. People concatenate first, then try to map columns — and silently lose rows. Two files with headings Revenue and 销售额 are two different DataFrame columns; a groupby on one of them drops the other entirely.

ALIASES = {"amount": ["amount","revenue","sales","total","金额","销售额","营收"],
           "date":   ["date","day","日期","订单日期","时间"]}

def normalize(df):
    out = pd.DataFrame()
    for canon, names in ALIASES.items():
        for c in df.columns:
            if str(c).strip().lower() in names:
                out[canon] = df[c]; break
    return out if "amount" in out else None

Send every file through normalize() first, then pd.concat. You keep all rows, and a column that only exists in one file becomes NaN elsewhere instead of vanishing.

3. Clean the amount column properly

The single most expensive bug in spreadsheet automation: one - or N/A in a revenue column turns the whole column into object dtype, and .sum() starts concatenating strings. Force it:

out["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(r"[^\d.\-]", "", regex=True), errors="coerce")

Then decide explicitly what a NaN means. Silence is what makes reports wrong.

4. Render, don't paste

Write the summary with pandas.ExcelWriter (one sheet per dimension), and the narrative with python-docx. Charts: use openpyxl.chart so the buyer can click into them. Pasted images cannot be edited, and that is the first thing a manager asks for.

The four things that break first

1. Lock files. ~$report.xlsx exists while someone has the file open. Skip them.
2. Merged header rows. pandas reads the top row as headers; two rows of headings gives you Unnamed: 0. Read with header=1 or unmerge upstream.
3. Dates as text. 2026年4月8日 is not ISO; 03/12/2026 is ambiguous (3 December or 12 March?). Full guide on that here.
4. Percentage columns. Excel stores 12% as 0.12, but a CSV export often stores the string "12%". Handle both or your totals are off by 100×.

If you would rather not write it

Excel Report Toolkit is this exact pipeline, packaged: folder in, summary.xlsx + report.docx out, header mapping and cleaning included, tested on mixed English/Chinese exports. 19 USDT, one-time.