Excel reporting automation

Cedarline weekly report

Python CLI that turns twelve weekly branch workbooks into one checked, formatted Excel report, built and e-mailed on a schedule. Every Monday a GitHub Actions run generates a fresh week of branch files for the fictional bakery chain Cedarline Bakeries, builds last week's report and publishes it here.

Repository

Latest run

How it works

How build-report works From the input folder through discover, the template reader, the resolver and the long records to the metrics engine, the workbook writer, the reproducible save, the notifier, the manifest and the output folder; below, the GitHub Actions path from weekly-report.yml to the Pages site. The numbered list below the diagram describes the same steps. Generatorgenerate-fixtures Input folder12 workbooksa week Discover*.xlsx by name Template readerper file,findings Resolverweeks, duplicates Long recordsone row permeasure Metrics enginereport.yaml Workbook writerfive sheets,charts Reproducible saveSOURCE_DATE_EPOCH Notifier.eml or SMTP Manifestalways written Output folderxlsx, json, eml IN GITHUB ACTIONS, MONDAYS 06:00 UTC weekly-report.ymlschedule,dispatch build-report runthe steps above web/latest/bot commit pages.ymlLibreOfficerender Pages sitethis page
  1. Each branch saves one workbook per week into the input folder, named like CL-03_2025-W38.xlsx. In the demo the generator writes them, with six typical deviations.
  2. Discover lists the workbooks by name. The template reader opens each one, finds the sheets and columns by name or alias and records findings instead of failing.
  3. The resolver gives every file its week (the cover wins over the file name), keeps the later of two different copies, marks missing branches and keeps the eight weeks of the window.
  4. The returns become one long table: one row per branch, week, category and measure.
  5. The metrics engine evaluates the ten metrics of report.yaml for every branch, product group and the chain, with the week-on-week change and the branch ranking.
  6. The workbook writer builds the five sheets with values, generated formulas in the total rows, three native charts and conditional formatting. The save is re-packed, so identical inputs give identical bytes.
  7. The run writes the workbook and, with --notify, the e-mail as an .eml file or through SMTP. The manifest is written last, in every run, so that it can record the SHA-256 of the workbook and of the .eml. Findings appear in the manifest, on the sheet Run info and in the e-mail.
  8. In GitHub Actions, weekly-report.yml runs these steps every Monday, commits the outputs to web/latest/ and calls pages.yml, which renders the previews with LibreOffice and deploys this page.

Schedule

GitHub Actions

.github/workflows/weekly-report.yml runs every Monday at 06:00 UTC and on demand. GitHub may start scheduled runs late when it is busy.

on:
  schedule: [{cron: "0 6 * * 1"}]
  workflow_dispatch: {inputs: {period: {default: last}}}

Windows Task Scheduler

Mondays at 07:00 local time, as the signed-in user, started late when the computer was off. Register the definition from the repository folder in PowerShell:

Register-ScheduledTask -TaskPath '\Cedarline\' -TaskName 'Weekly report' -Xml (Get-Content -Raw docs\scheduling\build-report-weekly.xml)

cron

On a Linux server whose clock runs on Dublin time, with the SMTP settings in an environment file:

0 7 * * 1  cd /opt/cedarline/build-report && set -a && . /etc/cedarline/build-report.env && set +a && /usr/local/bin/uv run build-report --log-file /var/log/cedarline/build-report.log run --period last --input /srv/cedarline/incoming --out /srv/cedarline/reports --notify smtp

Run it locally

You need Python 3.11 or newer and uv. The repository contains the 96 demo workbooks for 2025-W31 to 2025-W38 with the six deviations of the demo profile.

git clone https://github.com/HARISPAARTAN/excel-reporting-automation.git
cd excel-reporting-automation
uv sync
uv run build-report validate --input data/incoming --period 2025-W38
uv run build-report run --period 2025-W38 --input data/incoming --out reports --notify dry-run
uv run pytest

The run writes Cedarline_Weekly_Report_2025-W38.xlsx, .manifest.json and .eml into reports/ and exits with code 0 and six warnings; with --strict it exits with code 3 and writes no workbook. With SOURCE_DATE_EPOCH=1758520800 every run gives the same bytes.