← All samples

Excel Report Automation

Turns a raw sales CSV into a formatted Excel workbook: styled data sheet, live-formula summary by region, and a chart. Fictional data.

Run it

cd samples/excel-automation
python build_workbook.py   # needs openpyxl

Files: sales_report.xlsx · sales.csv · build_workbook.py

Input (first rows)

Summary sheet, recalculated in LibreOffice

The summary uses real spreadsheet formulas (COUNTIF/SUMIF), so it updates when the data changes. The script cross-checks the totals in plain Python; the recalculated values above match.

Code

"""Turns a raw sales CSV into a formatted Excel workbook: data sheet, live-formula summary by region, and a chart.
Sample data is fictional. Run: python build_workbook.py   (needs openpyxl)"""
import csv, random, datetime
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter

def make_sample(path="sales.csv"):
    random.seed(7); regions=["Ontario","Quebec","BC","Alberta"]; items=[("Tote bag",24.0),("Mug",14.5),("Notebook",9.0),("Hoodie",55.0)]
    with open(path,"w",newline="") as f:
        w=csv.writer(f); w.writerow(["date","region","item","qty","unit_price"])
        d=datetime.date(2026,9,1)
        for i in range(60):
            it=random.choice(items); w.writerow([(d+datetime.timedelta(days=i//2)).isoformat(),random.choice(regions),it[0],random.randint(1,12),it[1]])

def build(src="sales.csv", out="sales_report.xlsx"):
    rows=list(csv.DictReader(open(src)))
    wb=Workbook(); ws=wb.active; ws.title="Data"
    hdr=["Date","Region","Item","Qty","Unit price","Total"]; ws.append(hdr)
    for i,r in enumerate(rows, start=2):
        ws.append([datetime.date.fromisoformat(r["date"]),r["region"],r["item"],int(r["qty"]),float(r["unit_price"]),f"=D{i}*E{i}"])
        ws.cell(i,1).number_format="yyyy-mm-dd"; ws.cell(i,5).number_format=ws.cell(i,6).number_format='"$"#,##0.00'
    head=PatternFill("solid",fgColor="2F6F5E")
    for c in ws[1]: c.font=Font(bold=True,color="FFFFFF"); c.fill=head; c.alignment=Alignment(horizontal="center")
    ws.freeze_panes="A2"; ws.auto_filter.ref=ws.dimensions
    for i,wd in enumerate([12,10,12,6,11,12],1): ws.column_dimensions[get_column_letter(i)].width=wd
    n=len(rows)+1; s=wb.create_sheet("Summary"); s.append(["Region","Orders","Units","Revenue"])
    regions=sorted({r["region"] for r in rows})
    for j,reg in enumerate(regions, start=2):
        s.append([reg,f'=COUNTIF(Data!B2:B{n},A{j})',f'=SUMIF(Data!B2:B{n},A{j},Data!D2:D{n})',f'=SUMIF(Data!B2:B{n},A{j},Data!F2:F{n})'])
        s.cell(j,4).number_format='"$"#,##0.00'
    t=len(regions)+2; s.append(["Total",f"=SUM(B2:B{t-1})",f"=SUM(C2:C{t-1})",f"=SUM(D2:D{t-1})"]); s.cell(t,4).number_format='"$"#,##0.00'
    for c in s[1]+s[t]: c.font=Font(bold=True)
    for c in s[1]: c.fill=head; c.font=Font(bold=True,color="FFFFFF")
    for col,wd in zip("ABCD",[12,9,9,13]): s.column_dimensions[col].width=wd
    ch=BarChart(); ch.title="Revenue by region"; ch.add_data(Reference(s,min_col=4,min_row=1,max_row=t-1),titles_from_data=True)
    ch.set_categories(Reference(s,min_col=1,min_row=2,max_row=t-1)); s.add_chart(ch,"F2")
    wb.save(out)
    # plain-Python cross-check of what the formulas should compute
    exp={reg:round(sum(int(r["qty"])*float(r["unit_price"]) for r in rows if r["region"]==reg),2) for reg in regions}
    print(f"wrote {out}: {len(rows)} rows, {len(regions)} regions; expected revenue {exp}, total {round(sum(exp.values()),2)}")
    return exp

if __name__=="__main__":
    make_sample(); build()