excel-report-builder

Turn CSV/JSON data into a polished, formatted .xlsx report from a single declarative config file — no openpyxl code required.

You describe what the report should look like (a title banner, a styled table with number/currency/date/percent formats, conditional formatting, a summary with aggregations, a native chart); the tool writes the workbook. Each recipe mirrors a section of the Excel reporting guide.

Why

Hand-formatting the same spreadsheet every month — bolding headers, re-typing $#,##0.00, re-pointing a chart at shifted rows, recomputing per-region totals — is error-prone busywork. Put the layout in a config once, point it at fresh data, and rebuild the report in one command. The config is data, so it is easy to diff, review, and keep in version control.

Install

Not published to PyPI. Clone the repo and install from the clone:

git clone https://github.com/document-data-automation/excel-report-builder
cd excel-report-builder
pip install .

openpyxl is a required runtime dependency and is pulled in automatically by pip install .. Everything else uses the standard library. YAML configs work only if PyYAML is present — an optional extra:

pip install ".[dev]"    # dev tools (pytest)
pip install ".[yaml]"   # optional: read YAML configs as well as JSON

You can run the tool without installing it, straight from a clone:

python -m excel_report_builder --version

Quickstart

Build one of the bundled recipes end-to-end:

# from a clone
python -m excel_report_builder build \
  --config recipes/monthly_sales.json \
  --out monthly_sales.xlsx

Open monthly_sales.xlsx: a title banner, a frozen bold header, currency and percent columns, a green highlight on large deals, a per-region summary block, and a bar chart.

Start your own report from a documented starter config:

python -m excel_report_builder init > report.json
# edit report.json + point "data" at your CSV/JSON, then:
python -m excel_report_builder build --config report.json --out report.xlsx

Check a config (and its data files) without writing anything:

python -m excel_report_builder build --config report.json --validate

Exit codes: 0 success, 1 config/data error (with a message naming the offending key), 2 usage error.

Config reference

Top level: {"sheets": [ ... ]}. Each sheet:

Key	Meaning
name	Worksheet tab name (required, unique).
data	Path to a .csv/.json file (relative to the config), or an inline list of records (required).
title	Optional banner text merged across the top of the sheet.
table	The styled data table (see below).
summary	Optional aggregation block.
chart	Optional native Excel chart.

table:

Key	Meaning
columns	List of {key, header?, format?}. Omit to use every data column as-is. key must exist in the data.
columns[].format	One of currency, number, integer, percent, date, datetime, text.
header_fill	Header background hex, e.g. "4472C4" (default), or ""/falsey for none.
header_bold	Bold the header row (default true).
freeze_header	Freeze rows above the data (default true).
autofit	Auto-size column widths from content (default true).
conditional_formatting	List of rules (see below).

conditional_formatting[]:

Key	Meaning
column	Which table column the rule applies to (required).
type	greater_than, greater_equal, less_than, less_equal, equal, not_equal, color_scale, or data_bar.
value	Threshold, required for the comparison types.
fill, font_color	Highlight colors for comparison rules.
start_color/mid_color/end_color	Optional overrides for color_scale; color for data_bar.

summary:

Key	Meaning
title	Heading printed above the block (default "Summary").
group_by	Optional column to group by; one output row per group. Omit for an overall Metric/Value block.
aggregations	List of {column, func, header?} where func is sum, avg, min, max, or count.

chart:

Key	Meaning
type	bar, line, or pie.
categories	Table column used for category labels.
values	Table column plotted as the series.
title, width, height	Optional chart cosmetics.

Recipes

Complete, runnable configs in recipes/, with input data in examples/data/:

• monthly_sales.json — grouped-by-region summary + bar chart.
• invoice_aging.json — date formatting + conditional formatting on overdue amounts.
• kpi_dashboard.json — currency + percent KPI dashboard reading from JSON.

As a library

from excel_report_builder import build_report, load_config

config = {
    "sheets": [
        {
            "name": "Sales",
            "title": "Monthly Sales",
            "data": "sales.csv",
            "table": {
                "columns": [
                    {"key": "region", "header": "Region"},
                    {"key": "amount", "header": "Amount", "format": "currency"},
                ],
                "conditional_formatting": [
                    {"column": "amount", "type": "greater_than", "value": 5000,
                     "fill": "C6EFCE"},
                ],
            },
            "summary": {
                "group_by": "region",
                "aggregations": [{"column": "amount", "func": "sum"}],
            },
        }
    ]
}

build_report(config, "sales.xlsx")            # -> Path("sales.xlsx")

# Or load a config file (JSON, or YAML if PyYAML is installed):
build_report(load_config("report.json"), "report.xlsx")

Other helpers: build_workbook(config) returns the in-memory openpyxl.Workbook; validate_config(config) raises ConfigError with a key-specific message; load_data(path) normalises CSV/JSON to a list of dicts.

Development

git clone https://github.com/document-data-automation/excel-report-builder
cd excel-report-builder
pip install -e ".[dev]"
pytest

The tests build real workbooks, load them back with openpyxl, and assert on cell values, header styling, number formats, freeze panes, summary aggregates, chart objects, and conditional-formatting rules. Everything runs offline. CI runs the suite on Python 3.9–3.12.

License

MIT — see LICENSE.

---

Built and maintained by document-data-automation.com, where we write about Python workflows for document and data automation.