report-engine-kit

English | 中文文档

---

English

report-engine-kit is a framework-agnostic Python report & pivot-table engine with an optional Django integration layer. Extracted from a production drawing/change-management system, it ships three reporting capabilities:

1. Fixed-statistic chart data sources — register a Python function or class to produce standard ReportData (Chart.js-compatible JSON), exportable to xlsx / csv / pdf / json / html / md.
2. An in-memory pivot engine (Excel-pivot-like) — feed it a "headers + string rows" table (CSV, JSON, list[dict], pandas DataFrame or an ORM queryset), and users drive a full pivot table with one JSON config: multi-table comparison, column-dimension cross-tabs, sum/average/min/max/distinct-count, multi-level subtotals, per-metric sorting & Top N, percent/running-total derived metrics, multi-level headers, custom rows/columns, Excel-style formulas, cross-report references, manual overrides, blank rows/columns, row/column moving, row heights & column widths …
3. An optional Django app ([django] extra) — generic HTTP views, a saved-report model with migrations, an ORM drag-and-drop pivot engine (single-SQL conditional aggregation), a cross-report snapshot refresher, plus front-end pages and JS assets.

The core has zero third-party dependencies. openpyxl, reportlab, pandas, Django and PyYAML are all opt-in extras; the core runs on the Python standard library alone.

Table of contents

• 1. What it is / is not
• 2. Installation
• 3. Five-minute quickstart
• 4. Tutorial A — Fixed-statistic data sources
• 5. Tutorial B — In-memory pivot (core tutorial, full JSON config)
• 6. Tutorial C — Declarative chart analytics (no-code charts)
• 7. Tutorial D — Command line (CLI)
• 8. Tutorial E — Saving reports without a database (JSON file store)
• 9. Tutorial F — Django integration
• 10. Counting semantics (read first)
• 11. Caching & configuration
• 12. Security model
• 13. Capability map vs an Excel pivot table
• 14. Project layout
• 15. Development, testing & release
• 16. FAQ
• License

---

1. What it is / is not

Good fits

• Your data is fundamentally a ledger table (headers + rows, cells mostly text/dates/amounts) and end users want to drag out statistics in the browser like an Excel pivot table.
• You already have a bunch of statistic functions and want one registry, one cache, one export pipeline and one Chart.js contract for all of them.
• You want report configs (filters, dimensions, measures, styles, formulas) to be fully JSON-driven — persistable and recomputed per-viewer permissions across processes.
• You work in Django / Flask / FastAPI / plain scripts / pandas but don't want to be tied to one framework.

Not a good fit

• Billion-row OLAP (the in-memory engine targets ledger-sized data and walks representative rows in memory; for very large data use the ORM conditional-aggregation engine in the [django] extra, or aggregate in SQL inside your data source).
• A full BI suite (dashboard authoring, row/column permission matrices, data-source management UI). This package ships the engine and a generic builder page; menus/navigation stay in the host system.

---

2. Installation

pip install report-engine-kit                  # core only: csv/json/html/md + all pivot math
pip install "report-engine-kit[xlsx,pdf]"      # Excel/PDF exporters
pip install "report-engine-kit[pandas]"        # DataFrame adapter
pip install "report-engine-kit[yaml]"          # YAML configs for the CLI
pip install "report-engine-kit[django]"        # Django app + ORM pivot engine
pip install "report-engine-kit[all]"           # everything
pip install "report-engine-kit[dev]"           # development/testing

Python 3.9–3.13. Verify:

report-engine --help
python -c "import report_engine_kit as r; print(r.__version__)"

Naming conventions:

Name	Form	Example
Distribution (pip install)	hyphenated	report-engine-kit
Python import	underscored	import report_engine_kit
CLI command	hyphenated	report-engine domains ...

---

3. Five-minute quickstart

Given an orders CSV, build a pivot "orders & revenue by region" and export Excel in ~20 lines:

from report_engine_kit.tabular_pivot import (
    register_domain, PivotService, SimpleUser, table_from_csv)

# 1) Register the CSV as a "domain". Column 0 is the order code (dedup key).
register_domain(
    "orders", "Orders",
    load_tables=lambda force=False: [
        table_from_csv("orders.csv", key="orders", label="Orders", code_col=0),
    ],
    can_access=lambda user: True,
    can_manage=lambda user: True,
)

# 2) Execute a config through the service facade
svc = PivotService("orders", user=SimpleUser(id=1))
config = {
    "sheets": ["orders"],
    "row_dimensions": ["Region"],
    "metrics": [
        {"key": "cnt", "label": "Orders"},                    # default = count
        {"key": "rev", "label": "Revenue", "agg": "sum",
         "value_field": "Amount"},                            # summation
    ],
    "global_filters": {},
}
result = svc.execute(config)
for row in result["rows"]:
    print(row["kind"], row["dim_values"], row["values"])

# 3) Export (xlsx / csv / json; xlsx needs pip install "report-engine-kit[xlsx]")
open("orders.xlsx", "wb").write(svc.export_xlsx(config).getvalue())
open("orders.csv", "wb").write(svc.export_csv(config))

A runnable version lives in examples/02_tabular_pivot.py; every config field is explained in Tutorial B.

Result shape (excerpt):

{
  "domain": "orders",
  "dim_labels": ["Region"],
  "metric_columns": [{"key": "cnt", "label": "Orders"}, "..."],
  "rows": [
    {"kind": "data", "dim_values": ["East"], "values": {"cnt": 12, "rev": 3500}},
    {"kind": "totals", "dim_values": ["Total"], "values": {"cnt": 20, "rev": 9800}}
  ],
  "totals": {"cnt": 20, "rev": 9800},
  "layout": ["..."]
}

Runnable examples in examples/:

python examples/01_data_source.py               # fixed-statistic chart
python examples/02_tabular_pivot.py             # CSV -> pivot -> xlsx
python examples/03_saved_reports_file_store.py  # saving without a DB
python examples/04_analytics_chart.py           # declarative analytics

---

4. Tutorial A — Fixed-statistic data sources

For charts with fixed metrics and parameterized queries (status distribution, monthly trends, KPI cards).

4.1 Data contract

Class	Field	Meaning
ReportParams	start_date / end_date	datetime.date, inclusive
	profession_filter	business-scope string (app-defined meaning)
	time_dimension	day/week/month/quarter/year
	field_key	field key for field statistics
	extra	any JSON-serializable extension params
ReportData	chart_type	bar/line/pie/doughnut/polarArea/radar/table
	title / labels / summary	title, x-axis labels, summary dict
	datasets	[Dataset(label, data, backgroundColor?, borderColor?, fill?, extra?)]

to_dict() is exactly the JSON Chart.js consumes; from_dict() parses it back.

4.2 Option 1 — Subclassing (recommended: drilldown, fields, cache)

from datetime import date
from report_engine_kit import (
    ReportDataSource, ReportData, Dataset, ReportParams,
    DataSourceRegistry, export_report_data)

@DataSourceRegistry.register
class OrdersSource(ReportDataSource):
    name = "orders_by_status"      # required, unique within app_label
    label = "Orders by status"
    chart_type = "bar"
    cache_ttl = 60                 # seconds; 0 = no engine cache
    app_label = "shop"             # scope isolation; "" = visible to all apps

    def fetch(self, params: ReportParams) -> ReportData:
        rows = my_db.query_orders(params.start_date, params.end_date)  # your query
        return ReportData(
            title=f"Orders ({params.start_date} ~ {params.end_date})",
            labels=[r["status"] for r in rows],
            datasets=[Dataset(label="Orders", data=[r["n"] for r in rows])],
            summary={"total": sum(r["n"] for r in rows)},
        )

    # optional cell drilldown
    def drilldown(self, params, group_key: str) -> "DrilldownData":
        from report_engine_kit import DrilldownData
        items = my_db.query_detail(group_key)
        return DrilldownData(
            headers=["Order", "Amount"],
            rows=[[x["code"], x["amount"]] for x in items],
            total=len(items))

    # optional dynamic field metadata (for UI field pickers)
    def list_fields(self, params):
        from report_engine_kit import FieldMeta
        return [FieldMeta("status", "Status"), FieldMeta("dept", "Dept")]

# fetch with app_label isolation
data = DataSourceRegistry.fetch(
    "orders_by_status",
    ReportParams(start_date=date(2026, 1, 1), end_date=date(2026, 9, 30)),
    app_label="shop")

open("orders.xlsx", "wb").write(export_report_data(data, "xlsx"))

4.3 Option 2 — Register an existing function (zero changes)

The adapter introspects the signature and passes ISO date strings:

from report_engine_kit import register_legacy_function

def get_sales(start_date=None, end_date=None, profession_filter=None):
    return {"chart_type": "line", "title": "Sales trend",
            "labels": ["Jun", "Jul", "Aug"],
            "datasets": [{"label": "Revenue", "data": [10, 20, 30]}]}

register_legacy_function(
    "sales", "Sales trend", get_sales,
    chart_type="line",
    supports_drilldown=False,
    drilldown_fn=None,          # or (params, group_key) -> DrilldownData
    app_label="shop",
    cache_ttl=None)             # None = global LEGACY_SOURCE_CACHE_TTL

4.4 Six export formats

from report_engine_kit import export_report_data
for fmt in ("csv", "json", "md", "html", "xlsx", "pdf"):
    try:
        with open(f"out.{fmt}", "wb") as f:
            f.write(export_report_data(data, fmt))
    except Exception as e:
        print(fmt, "needs its extra:", e)

Format	Extra	Notes
csv	none	utf-8-sig BOM (Excel-friendly); cells starting with = + - @ get a tab prefix (formula-injection guard)
json	none	full payload; adds params when provided
md	none	Markdown pipe table, escapes |/newlines
html	none	standalone styled HTML with optional embedded Chart.js; chart_cdn_url="" for full offline
xlsx	xlsx (openpyxl)	styled headers, CJK-aware auto width, frozen header, compact JSON scalars, injection guard
pdf	pdf (reportlab)	A4 table; auto CJK font discovery (simhei/msyh/simsun, wqy/noto, REPORT_ENGINE_FONT_PATH)

Custom exporter:

from report_engine_kit.export import BaseExporter, ExporterRegistry

@ExporterRegistry.register
class ParquetExporter(BaseExporter):
    format_name = "parquet"
    content_type = "application/vnd.apache.parquet"
    file_extension = "parquet"
    def export(self, data, params=None) -> bytes: ...

---

5. Tutorial B — In-memory pivot (core tutorial, full JSON config)

5.1 The data contract

DataTable(
    key="cr",                 # unique table key inside the domain
    label="CR",               # display name / column-dimension value
    headers=["Code", "Status", "Date", "Amount"],
    rows=[TableRow(code="CR-001", cells={"0": "CR-001", "1": "Closed",
                                         "2": "2026-02-10", "3": "200"})],
    code_col=0,               # dedup code column
    status_col=1,             # __status__ semantic dimension maps here
    date_cols=[2],            # date columns (headers containing date/time auto-detected)
    version="20260930_v1",    # version bump drives the representative-row L1 cache key
)

Table loader: load_tables(force) -> list[DataTable]; force=True bypasses business-side caches.

5.2 Register a domain (all permission hooks live here)

register_domain(
    code="change", label="Change management",
    load_tables=load_change_tables,
    can_access=lambda u: u.is_authenticated,             # read
    can_manage=lambda u: u.is_staff,                     # create/save-as/publish
    restricted_cols=lambda u, t: {3} if not u.is_staff else set(),  # hidden columns
    blocked_header=lambda name, t: name == "Internal note",
    default_tables=["cr", "fcr"],
    drill_resolver=my_drill_builder,                     # numeric-cell drilldown
)

Hook	Signature	Purpose
can_access(user)	→ bool	enforced in service + HTTP layers
can_manage(user)	→ bool	create/edit/publish; defaults to can_access
restricted_cols(user, table)	→ set[int]	columns hidden from this user (cache key includes the permission fingerprint)
blocked_header(name, table)	→ bool	extra blocked columns; by default flow headers containing 分发/回收/接收/归还 are also blocked
drill_resolver(config, row_key, metric_key, restricted)	→ list[dict]	drilldown → [{code,label,count,url}]

5.3 Build a config and execute

config = {
    # tables (__type__ dimension available with multiple tables)
    "sheets": ["cr", "fcr"],

    # row dimensions (up to 4); __type__=table, __status__=semantic status,
    # anything else is a literal header name
    "row_dimensions": ["__type__", "__status__"],
    # dimensions can also filter values: {"key": "Dept", "values": ["EM4","EM5"]}

    # column dimension (cross-tab, 1 level): each measure fans out per value
    "column_dimension": "Dept",

    # measures (up to 30)
    "metrics": [
        {
            "key": "total", "label": "Total", "group": "Phase 1",  # same group -> multi-level header
            "agg": "count",                                       # count/sum/avg/min/max/distinct
            "col_filters": [                                      # per-measure filters
                {"name": "Close date", "values": [], "mode": "include",
                 "keyword": "",
                 "date_rule": {"col": "Close date", "mode": "range",
                               "start": "2026-01-01", "end": "2026-03-31"}}],
            "filter_logic": "and",                                # and / or
            "date_rule": {"col": "Import date", "mode": "year"},  # own date window
            "number_format": {"decimals": 0, "thousands": True}
        },
        {"key": "rev", "label": "Amount", "agg": "sum",
         "value_field": "Amount",
         "number_format": {"decimals": 2, "thousands": True, "prefix": "$"}},
        {"key": "share", "label": "Share", "calc": "pct_of_total",
         "calc_source": "rev",
         "number_format": {"percent": True, "decimals": 1}},
        {"key": "cum", "label": "Cumulative", "calc": "running_total",
         "calc_source": "rev"}
    ],

    # global filters (common row set; each measure then adds its own)
    "global_filters": {
        "date_col": "", "start": "", "end": "",                  # legacy fields
        "date_rule": {"col": "Import date", "mode": "month"},   # new rule takes precedence
        "col_filters": [{"name": "Dept", "values": ["EM4"], "mode": "exclude"}],
        "filter_logic": "and"
    },

    # row/column control
    "show_totals": True,            # grand-total row
    "show_column_totals": True,     # per-measure "Total" column under a column dimension
    "hide_empty_rows": False,       # hide all-zero rows
    "subtotals": True,              # insert subtotals for multi-level row dimensions
    "sort": {"by": "rev", "dir": "desc"},   # sort by a measure
    "row_limit": 10,                # Top N (data rows only; totals unaffected)

    # manual overrides (applied outside cache; totals recomputed)
    "manual_overrides": {'["Closed"]': {"total": 99}},

    # custom rows / columns (formulas in 5.6)
    "custom_rows": [{"id": "r1", "label": "Monthly target",
                     "values": {"total": 100}, "formulas": {}}],
    "custom_columns": [
        {"id": "cc1", "label": "Double", "template": "=B1*2",
         "cells": {"1": {"t": "n", "v": 3}}}
    ],

    # layout editing (see 5.7)
    "spacer_rows": [{"id": "sr1", "after": -1, "height": 30}],
    "spacer_cols": [{"id": "sc1", "after": "dim:__status__", "width": 24}],
    "row_heights": {"data": 26, "totals": 32},
    "col_widths": {"metric:rev": 160},
    "layout_overrides": {"row_order": [], "col_order": [], "hidden_rows": []},

    # text banners, manual merges, footer
    "custom_texts": [], "custom_merges": [],
    "footer_text": "Source: change ledger"
}

result = svc.execute(config)

5.4 Value aggregations (Excel "value field settings")

agg	Meaning	Needs value_field	Notes
count	row count	no	representative rows (blank codes excluded)
sum	summation	yes	non-numeric cells ignored
avg	average	yes	averages numeric cells only (Excel semantics)
min / max	min / max	yes
distinct	distinct count	yes	distinct numeric values

Numeric parsing is lenient: "1,234.5", "$200", "~300" all parse.

Derived metrics:

calc	Meaning	Requirement
pct_of_total	fraction of the column grand total (stored 0–1)	calc_source
running_total	cumulative sum across rows in order	calc_source

number_format:

{"decimals": 2, "thousands": true, "percent": true,
 "prefix": "$", "suffix": "", "multiplier": 1}

xlsx export writes real Excel cell formats; CSV/HTML render formatted text.

5.5 Date rules — 7 modes

mode	Meaning	Extra fields
today	today	—
month	first of month ~ today	—
year	Jan 1 ~ today	—
recent	last N days	days (0–3650)
range	fixed interval	start, end (YYYY-MM-DD)
offset	relative offset (overdue etc.)	base(today/fixed), baseDate, sign(plus/minus), unit(day/month/year), amount, cmp(gt/lt)
wheel	rolling window (quarter-to-date etc.)	s/e as {y,m,d,mode,cur} parts following current year/month/day

wheel example (first day of quarter ~ today; endpoints normalized so start>end never silently empties the table):

{"col": "Close date", "mode": "wheel",
 "s": {"y": {"mode": "cur"}, "m": {"mode": "fix", "v": 10}, "d": {"mode": "fix", "v": 1}},
 "e": {"y": {"mode": "cur"}, "m": {"mode": "cur"}, "d": {"mode": "cur"}}}

Column filters support multi-value include/exclude, keyword contains/not-contains, per-group AND/OR; blank sentinels (blank) / (non-blank).

5.6 Formula DSL (Excel-flavored; custom parser, never eval)

Category	Contents
Operators	+ - * /, parentheses, unary signs, comparisons = == <> != < <= > >= (return 1/0)
Cells	A1, ranges B1:B10 (physical coordinates, 1-based)
Cross-report	[12]C3, range [12]A1:A10
Functions	SUM / AVG / MIN / MAX / COUNT / ROUND / ABS / SQRT / POWER / MOD / IF / AND / OR / NOT
Custom columns	template for relative column fill (shifts per row); per-cell {t:"n",v} or {t:"f",v:"=..."}
Error codes	#VALUE! #REF! #DIV/0! #CYCLE! #NAME? #NUM!
Guards	formula length ≤200, depth ≤100, cross-report chain ≤20, cycle detection

=SUM(B1:B5)+ROUND(AVG(C1:C5)*0.1, 2)
=IF(B1>=100, C1*0.9, C1)
=[12]C3 + [12]D3

5.7 Layout editing (added in 0.3.0, applied server-side)

{
  "col_widths": {"dim:Region": 200, "metric:rev": 160,
                 "ccol:cc1": 120, "spacer:sc1": 24},
  "row_heights": {"title": 36, "header": 28, "data": 24,
                  "subtotal": 26, "totals": 30, "spacer": 12},
  "spacer_rows": [{"id": "sr1", "after": -1, "height": 30},
                  {"id": "sr2", "after": 4, "height": 16}],
  "spacer_cols": [{"id": "sc1", "after": "dim:Region", "width": 24},
                  {"id": "sc2", "after": "metric:rev"}],
  "layout_overrides": {
    "row_order": ["[\"West\"]", "[\"East\"]"],
    "col_order": ["metric:rev", "metric:cnt", "ccol:cc1"],
    "hidden_rows": ["[\"Void\"]"]
  }
}

• Column widths: token → px (8–800); stay correct after column moves, fan-out and inserted gaps.
• Row heights: per row kind or per element id (spacer:<id>, customrow:<id>); xlsx converts px → points (×0.75).
• Blank rows: after anchors to a data-row index (-1 = top); they break dimension auto-merges like a real inserted Excel row and shift formula/merge coordinates consistently; excluded from flat CSV.
• Blank columns: anchors __first__ / head / dim:<key> / metric:<key> / ccol:<id>; borderless gaps.
• Move rows: row_order holds JSON row keys — listed rows move to the top in order, unlisted rows keep natural order after them; hidden_rows removes rows and recomputes subtotals/totals.
• Move columns: metric blocks and custom columns are movable (a fan-out metric moves as one block); head/dim columns stay fixed.

All moves/inserts are driven by one canonical column plan (result.column_plan = {blocks, width, metric_phys, ccol_phys, spacer_phys}), shared by formula coordinates, merge validation, headers and xlsx — no coordinate drift.

5.8 Export

buf = svc.export_xlsx(config, report_name="Quarterly")   # BytesIO with all styles/formats/subtotals/spacers
open("r.xlsx", "wb").write(buf.getvalue())

csv_bytes = svc.export_csv(config, formatted=True)        # flat CSV with subtotal/total row tags
json_bytes = svc.export_json_bytes(config)                # raw result JSON

The first CSV column tags the row kind: data / subtotal / total / custom.

5.9 Theming (light/dark) & white-label

The shipped builder is fully tokenized — every color is a CSS variable defined once in the static theme.css (:root for light, [data-theme="dark"] for dark).

• The toolbar moon/sun button toggles the theme; the choice persists in localStorage (tpv-theme).
• White-label without forking: override the --tpv-* variables in your own stylesheet (brand color, surfaces, borders, radius…) — the component markup never changes.
• The builder now also exposes the entire engine surface in the UI: per-measure aggregation (count/sum/avg/min/max/distinct), derived metrics and number formats, plus a collapsible "高级布局" card for the column dimension, multi-level subtotals, sort/Top N, blank rows/columns and row heights. All choices round-trip through save/reload.

---

6. Tutorial C — Declarative chart analytics (no-code charts)

Instead of writing fetch() for every chart, hand list[dict] rows to aggregate_rows with a declarative spec:

from report_engine_kit import Dimension, Measure, aggregate_rows

data = aggregate_rows(
    rows,                                   # any list[dict]
    dimensions=[
        Dimension("month", "Month", date_trunc="month"),
        Dimension("status", "Status"),      # 2nd dimension automatically becomes the series
    ],
    measures=[
        Measure("orders", "Orders"),                                     # count
        Measure("revenue", "Revenue", agg="sum", field_name="amount"),
        Measure("aov", "AOV", agg="avg", field_name="amount"),
        Measure("buyers", "Buyers", agg="distinct_count",
                field_name="customer_id"),
    ],
    filters=[
        {"key": "status", "op": "in", "values": ["paid"]},
        {"key": "amount", "op": "gte", "values": ["0"]},
        {"key": "dept", "op": "contains", "value": "EM"},
    ],
    series_dimension=None,    # defaults to the second dimension
    sort="-orders::paid",     # label / -label / measure key / -measure key
    top_n=20,
    chart_type="bar",
)

• Filter ops: in / not_in / contains / not_contains / eq / ne / gt / gte / lt / lte / between / blank / nonblank
• Date buckets: day / week / month / year; blanks go to (blank) and always sort last.
• The same spec can drive a data source over HTTP, so end users add new charts without server-side code:

from report_engine_kit import analytics_source_factory, ReportParams, DataSourceRegistry

analytics_source_factory(
    "shop_dyn", "Shop dynamic",
    rows_loader=lambda params: my_db.fetch_all(),   # -> list[dict]
    app_label="shop")

params = ReportParams(extra={
    "dimensions": [{"key": "dept"}],
    "measures": [{"key": "n", "label": "Count"}],
    "filters": [{"key": "status", "op": "in", "values": ["paid"]}],
})
data = DataSourceRegistry.fetch("shop_dyn", params, app_label="shop")

Runnable example: examples/04_analytics_chart.py.

---

7. Tutorial D — Command line (CLI)

Any Python module that calls register_domain(...) on import can be the "domain module" (see examples/shop_domain.py).

# list registered domains
report-engine --domain-module shop_domain domains

# available row dimensions / date columns
report-engine --domain-module shop_domain dimensions --domain shop_orders

# field options (with counts, pagination, keyword)
report-engine --domain-module shop_domain field-options --domain shop_orders \
    --field Status --limit 20

# run an ad-hoc config (JSON; YAML with the [yaml] extra)
report-engine --domain-module shop_domain execute --domain shop_orders \
    --config pivot.json -o result.json

# export (xlsx / csv / json)
report-engine --domain-module shop_domain export --domain shop_orders \
    --config pivot.json --format xlsx --out r.xlsx --name Quarterly

# save/list/run/publish/delete reports without a database (JSON file store)
report-engine --domain-module shop_domain reports save \
    --domain shop_orders --store file:reports.json --config pivot.json
report-engine --domain-module shop_domain reports list \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports run 1 \
    --domain shop_orders --store file:reports.json --format xlsx --out r1.xlsx
report-engine --domain-module shop_domain reports public 1 \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports delete 1 \
    --domain shop_orders --store file:reports.json

Global options: --user-id (defaults to env REPORT_ENGINE_USER_ID), --admin, --store memory|file:PATH.

---

8. Tutorial E — Saving reports without a database (JSON file store)

from report_engine_kit.tabular_pivot import (
    PivotService, JsonFileReportStore, SimpleUser, register_domain)

register_domain("orders", "Orders",
                load_tables=..., can_access=lambda u: True,
                can_manage=lambda u: True)

store = JsonFileReportStore("data/reports.json")   # atomic writes (temp + os.replace)
svc = PivotService("orders", user=SimpleUser(id=1, is_superuser=True), store=store)

record = svc.save_report({"name": "Sep board", **pivot_config})
print(svc.list_reports(scope="available"))
print(svc.list_reports(scope="public"))
result = svc.run_report(record["id"], force=False)
svc.set_public(record["id"], True)

Permission semantics are identical across the in-memory, JSON-file and Django ORM stores:

• content edits are owner-only (built-in templates superuser-only); everyone else uses "save as";
• creating/publishing needs can_manage or superuser;
• reads allow: owner / public / built-in template / domain manager (who may reference others' private reports);
• deletes: owner or superuser.

Implement the store protocol (list_reports/get_config/save_fields/delete/ set_public) to back MongoDB/Redis/etc.

---

9. Tutorial F — Django integration

9.1 Install & routes

# settings.py
INSTALLED_APPS = [..., "report_engine_kit.contrib.django"]

# urls.py
from django.urls import include, path
urlpatterns += [
    path("report-engine/tabular/<str:domain_code>/",
         include("report_engine_kit.contrib.django.tabular_urls")),
]

python manage.py migrate report_engine_kit   # app_label = report_engine_kit

The app wires report caching to Django's cache framework in ready(). Register a domain in your own app's ready():

from report_engine_kit.tabular_pivot import register_domain
from report_engine_kit.contrib.django.orm_adapters import table_from_queryset
from myapp.models import Order

register_domain(
    "orders", "Orders",
    load_tables=lambda force=False: [table_from_queryset(
        Order.objects.all(), key="orders", label="Orders",
        fields=["code", "status", "created_at", "dept__name"],
        code_field="code", status_field="status")],
    can_access=lambda u: u.is_authenticated,
    can_manage=lambda u: u.is_staff,
)

Open /report-engine/tabular/orders/ for the self-contained drag-and-drop pivot builder.

9.2 Generic endpoints

GET  dimensions/        field-options/      config/list/
GET  config/load/<id>/  config/execute/<id>/
POST execute/  export/  drill/  config/save/
POST config/delete/<id>/  config/visibility/<id>/

For fixed-statistic charts use report_engine_kit.contrib.django.http.ReportApiMixin (list_sources/get_data/get_drilldown/export/list_fields) and add your own auth decorators.

9.3 ORM pivot engine (SQL aggregation instead of in-memory counting)

When measures must aggregate directly in the database, implement a PivotContext (dimension metadata, base queryset, scope filter, cross-source joins, predicate Q builders, fast-path hooks, metric versions …):

from report_engine_kit.contrib.django.pivot import PivotContext, PivotReportEngine

class OrderPivotContext(PivotContext):
    app_label = "orders"
    config_model = MyReportConfig
    metric_model = None
    def list_dimensions(self, scope_code=""): ...
    def get_base_queryset(self, data_source): ...
    def apply_scope_filter(self, qs, ds, codes): ...

engine = PivotReportEngine(OrderPivotContext())

Features: single-SQL Count(Q(...)) conditional aggregation with automatic per-metric fallback, predicate AND/OR trees, HAVING, chart data, cell formulas, chunked_robust_count (survives MySQL 2013 disconnects).

Cross-report snapshot refresher (daemon thread, cross-process cache lock, close_old_connections() for MySQL wait_timeout):

from report_engine_kit.contrib.django.pivot.snapshot import (
    register_pivot_domain, start_snapshot_scheduler)
register_pivot_domain("orders", OrderPivotContext, MyReportConfig)
start_snapshot_scheduler("22:30")

See docs/django.md and docs/api.md.

---

10. Counting semantics (read first)

The most important business contracts — they determine whether the numbers are correct:

1. Dedup by code column: rows sharing code_col are one entity; rows with a blank code never count.
2. Same code = merged cell: each column inherits the first non-empty value in row order (representative-row inheritance).
3. Per-measure filters: global filters (incl. global date) fix the common row set; each measure adds its own column filters and date window.
4. Date filtering: when a date range is active, rows with blank/unparsable dates are excluded; parse_date_lenient accepts 2026/2/9, 2026.2.9, 2026年2月9日.
5. Multi-table: __type__ groups by table label; a shared dimension name uses common columns across tables.
6. Restricted columns: columns hidden via restricted_cols enter neither dimensions nor measure values for that user; the cache key contains the exact hidden-column fingerprint.
7. Totals: the totals row is produced by merging accumulators (avg = total sum / total numeric count, not an average of averages; distinct = set union), so sorting/Top N can never corrupt it.

---

11. Caching & configuration

from report_engine_kit.cache import (
    MemoryCacheBackend, ReportCacheManager, configure_cache)
from report_engine_kit import configure

# switch to the Django cache (the [django] extra does this automatically in AppConfig.ready())
configure_cache(DjangoCacheBackend(alias="default"))

# global settings
configure(DEFAULT_CACHE_TTL=300,
          LEGACY_SOURCE_CACHE_TTL=120,
          MEMORY_CACHE_MAX_ENTRIES=2048,
          DRILL_PAGE_SIZE_DEFAULT=50,
          DRILL_PAGE_SIZE_MAX=200)

• The default MemoryCacheBackend is thread-safe with TTL, bounded eviction, atomic add and glob deletion; env overrides REPORT_ENGINE_DEFAULT_TTL / REPORT_ENGINE_MEMORY_CACHE_MAX_ENTRIES.
• Pivot results cache for 30 s, representative-row L1 for 1 h (max 64 keys); cache key = config + permission fingerprint + app id — views never leak across users.
• A custom backend only needs get/set/delete (optional add/delete_pattern).

---

12. Security model

• Domain access/manage/restricted-column hooks are enforced server-side; client input is untrusted.
• All pivot input is whitelist-sanitized with size caps (≤4 row dimensions, ≤30 metrics, ≤200 filter values, ≤5000 custom cells, formula length ≤200, cross-report chain ≤20 …).
• Cross-report refs [id] recompute as the viewer; only owner/public/ domain-manager-visible reports are reachable.
• CSV/XLSX exports neutralize cells starting with = + - @; download filenames are RFC 5987 encoded.
• Formulas use a purpose-built tokenizer/parser (AST whitelist), never eval; manual overrides accept numbers only.

---

13. Capability map vs an Excel pivot table

Capability	report-engine-kit
Row fields (multi-level)	✅ up to 4 levels, auto-merge equal values
Column fields	✅ 1-level cross-tab, measures fan out per value, optional per-measure total
Value aggregation	✅ count/sum/avg/min/max/distinct count
Value/report filters	✅ per-measure + global, AND/OR, keyword, 13 operators
Date grouping	✅ day/week/month/year + 7 dynamic date rules (incl. wheel)
Subtotals	✅ auto multi-level subtotals, exact merge for every aggregation
Grand totals	✅ row totals + column totals (toggleable)
Sort / Top N	✅ by any measure, keep first N
Percent / running total	✅ pct_of_total, running_total
Number formats	✅ decimals/thousands/percent/prefix/suffix/multiplier, real xlsx formats
Calculated fields/formulas	✅ 14 functions + comparisons + ranges + cross-report refs + cycle guard
Manual overrides	✅ cell override, red font, totals recomputed
Custom rows/columns	✅ incl. formula columns and templates
Insert blank rows/columns	✅ server-side, unified coordinate system
Move rows/columns, hide rows	✅ server-side (engine + xlsx + CSV consistent)
Row heights/column widths	✅ per kind/element, correct after moves
Manual cell merges	✅ owner-matrix validation, stale/illegal items dropped
Multi-level headers/banners	✅ layout engine as single source of truth
Drilldown	✅ domain hook, code set → detail links
Export	✅ xlsx / csv / pdf / json / html / md
DB-free deployment	✅ CLI + atomic JSON file store
Web integration	✅ optional Django app + ORM aggregation engine
Charts	✅ data-source registry + declarative analytics (no-code charts)

---

14. Project layout

report-engine-kit/
├── pyproject.toml / MANIFEST.in / LICENSE / CHANGELOG.md
├── README.md
├── docs/                  # api.md / django.md / release.md
├── examples/              # 4 runnable tutorial scripts + sample data
├── scripts/release.ps1    # Windows release script
├── .github/workflows/publish.yml
├── tests/                 # 110 unit tests
└── src/report_engine_kit/
    ├── chart core: contracts/base/registry/cache/export/snapshot/analytics/date_utils/cli
    ├── tabular_pivot/     # in-memory pivot engine (stores/loaders/adapters/numbers)
    └── contrib/
        ├── pandas_support.py   # [pandas]
        └── django/             # [django]: app/model/migrations/HTTP/ORM pivot/assets

---

15. Development, testing & release

git clone <repo> && cd report-engine-kit
python -m venv .venv
.\.venv\Scripts\Activate.ps1     # Windows
source .venv/bin/activate         # macOS/Linux

pip install -e ".[dev,all]"
pytest                                   # 110 tests
python examples/02_tabular_pivot.py
python -m build
python -m twine check dist/*

Release (see docs/release.md):

$env:TWINE_USERNAME = "__token__"
$env:TWINE_PASSWORD = "<token>"
.\scripts\release.ps1 -Version 0.3.0 -Target testpypi
.\scripts\release.ps1 -Version 0.3.0 -Target pypi

Tags v*rc* publish to TestPyPI and v* to PyPI via GitHub Actions (OIDC trusted publishing preconfigured).

---

16. FAQ

My count doesn't match Excel COUNTIF? Check: ① the code column (blank codes excluded, same code → one representative row); ② whether the measure's own col_filters/date_rule filtered further; ③ restricted columns. See section 10.

What does sum/avg do with text cells? Non-numeric cells are ignored (while still counted by count); avg's denominator counts numeric cells only. Thousands separators/currency symbols parse leniently.

The UI shows hidden rows/sorting but Excel export doesn't? Since 0.3.0 row_order/col_order/hidden_rows apply server-side; make sure you're on 0.3.0+ and the values are saved under layout_overrides.

=[12]C3 returns #REF!? Report 12 doesn't exist, the viewer lacks access (not owner/public/domain-manager), or C3 isn't numeric in the target report. Cycles return #CYCLE!.

Can I use it without Django/openpyxl? Yes — the zero-dependency core covers csv/json/html/md, all pivot math, the CLI and JSON file storage.

Garbled Chinese in PDF? Set REPORT_ENGINE_FONT_PATH to a font file; on Linux install fonts-wqy-zenhei or noto-cjk.

Very large data? The in-memory engine suits ledger-sized data (tens of thousands of representative rows). Aggregate in SQL inside load_tables, or use the ORM PivotReportEngine in the [django] extra.

Flask/FastAPI? Instantiate PivotService and pass the request JSON as config; permission hooks accept your own user object (duck-typed .id / .is_superuser).

License

MIT © bzsystem team

---

中文文档

回到语言切换：English | 中文文档

report-engine-kit（报表引擎工具包） 是一个框架无关的 Python 报表与数据透视引擎，并提供可选的 Django 集成层。它从一个生产级图纸/变更管理系统中抽取，包含三大报表能力：

1. 固定统计图数据源——注册 Python 函数或类即可产出标准 ReportData，输出 Chart.js 兼容 JSON，可导出 xlsx / csv / pdf / json / html / md。
2. 内存表透视引擎（类 Excel 数据透视表）——喂给引擎"表头 + 字符串行"（CSV、JSON、dict 列表、pandas DataFrame、ORM QuerySet 都行），用户通过一份 JSON 配置即可得到完整的透视表：多表对比、列维度交叉、求和/平均/最大/最小/去重计数、多级小计、按指标排序/Top N、占比/累计等衍生指标、多级表头、自定义行列、Excel 风格公式、跨报表引用、手动改数、插空行空列、拖拽行列、行高列宽……
3. 可选 Django App（[django] extra）——通用 HTTP 视图、已存报表模型与迁移、ORM 版拖拽透视引擎（单条 SQL 条件聚合）、跨报表快照定时刷新、前端页面与 JS 资源。

核心零第三方依赖：openpyxl、reportlab、pandas、Django、PyYAML 全部是按需安装的 extras，纯核心只用 Python 标准库。

中文目录

• 一、这是什么 / 不是什么
• 二、安装
• 三、五分钟快速开始
• 四、教程 A：固定统计图数据源
• 五、教程 B：内存表透视（核心教程，含完整 JSON 配置）
• 六、教程 C：声明式图表分析（用户零代码自定义图表）
• 七、教程 D：命令行 CLI
• 八、教程 E：不用数据库保存报表（JSON 文件存储）
• 九、教程 F：Django 集成
• 十、数据口径（计数规则，务必先读）
• 十一、缓存机制与配置
• 十二、安全模型
• 十三、能力总览（与 Excel 数据透视表对照）
• 十四、项目结构
• 十五、开发、测试与发布
• 十六、常见问题 FAQ
• 许可证

---

一、这是什么 / 不是什么

适合的场景

• 你的数据本质是"台账表"（表头 + 若干行，单元格多为文本/日期/金额），用户希望在前端像 Excel 透视表一样拖拽出各种统计报表；
• 你有一堆已经写好的统计函数，想统一注册、统一缓存、统一导出、统一走 Chart.js；
• 你希望报表配置（筛选、维度、指标、样式、公式）完全由 JSON 驱动，可以落库、可以跨进程按查看者权限重算；
• 你在 Django / Flask / FastAPI / 纯脚本 / pandas 环境中使用，但不想被某一个框架绑定。

不适合的场景

• 亿级数据的 OLAP 分析（内存表引擎是为台账量级设计的，默认在内存里遍历代表行；超大数据请用 [django] extra 里的 ORM 条件聚合引擎，或自己在数据源层做 SQL 聚合）；
• 需要完整 BI 平台（仪表盘编排、行列权限矩阵、数据源管理 UI）——本包提供的是引擎和通用设计器页面，业务菜单/导航仍由宿主系统负责。

---

二、安装

pip install report-engine-kit                  # 仅核心：csv/json/html/md + 全部透视计算
pip install "report-engine-kit[xlsx,pdf]"      # Excel/PDF 导出
pip install "report-engine-kit[pandas]"        # DataFrame 适配器
pip install "report-engine-kit[yaml]"          # CLI 读取 YAML 配置
pip install "report-engine-kit[django]"        # Django App + ORM 透视引擎
pip install "report-engine-kit[all]"           # 一次性装全
pip install "report-engine-kit[dev]"           # 开发/测试

支持 Python 3.9、3.10、3.11、3.12、3.13。验证安装：

report-engine --help
python -c "import report_engine_kit as r; print(r.__version__)"

安装包的命名约定（避免歧义）：

名称	形式	示例
分发包名（pip install）	连字符	report-engine-kit
Python 导入名	下划线	import report_engine_kit
CLI 命令名	连字符	report-engine domains ...

---

三、五分钟快速开始

场景：你有一个订单 CSV，想用 20 行代码生成"按区域统计订单数和金额"的透视表并导出 Excel。

from report_engine_kit.tabular_pivot import (
    register_domain, PivotService, SimpleUser, table_from_csv)

# 1) 把 CSV 注册成一个"业务域"（domain）。code=0 的列是订单编号（去重列）。
register_domain(
    "orders", "订单",
    load_tables=lambda force=False: [
        table_from_csv("orders.csv", key="orders", label="订单", code_col=0),
    ],
    can_access=lambda user: True,
    can_manage=lambda user: True,
)

# 2) 用服务门面执行一份配置
svc = PivotService("orders", user=SimpleUser(id=1))
config = {
    "sheets": ["orders"],
    "row_dimensions": ["区域"],
    "metrics": [
        {"key": "cnt", "label": "订单数"},                       # 默认 count
        {"key": "rev", "label": "金额", "agg": "sum",
         "value_field": "金额"},                                 # 求和
    ],
    "global_filters": {},
}
result = svc.execute(config)
for row in result["rows"]:
    print(row["kind"], row["dim_values"], row["values"])

# 3) 导出（xlsx / csv / json；需要 pip install "report-engine-kit[xlsx]"）
open("订单统计.xlsx", "wb").write(svc.export_xlsx(config).getvalue())
open("订单统计.csv", "wb").write(svc.export_csv(config))

示例 CSV 的真实表头是中文，且"状态"列被识别为语义状态列；完整可运行版本见 examples/02_tabular_pivot.py，配置字段逐项解释见 教程 B。

返回结果结构（节选）：

{
  "domain": "orders",
  "dim_labels": ["区域"],
  "metric_columns": [{"key": "cnt", "label": "订单数"}, "..."],
  "rows": [
    {"kind": "data", "dim_values": ["华东"], "values": {"cnt": 12, "rev": 3500}},
    {"kind": "totals", "dim_values": ["合计"], "values": {"cnt": 20, "rev": 9800}}
  ],
  "totals": {"cnt": 20, "rev": 9800},
  "layout": ["..."]
}

可直接运行的例子在 examples/ 目录：

python examples/01_data_source.py            # 固定统计图
python examples/02_tabular_pivot.py          # CSV → 透视 → xlsx
python examples/03_saved_reports_file_store.py  # 免数据库保存
python examples/04_analytics_chart.py        # 声明式图表分析

---

四、教程 A：固定统计图数据源

适用于"指标固定、参数化查询"的图表（如任务状态分布、月度趋势、KPI 卡片）。

4.1 数据契约

类	字段	说明
ReportParams	start_date / end_date	datetime.date，闭区间
	profession_filter	业务域过滤字符串（含义由你的 app 定义）
	time_dimension	day/week/month/quarter/year
	field_key	字段统计用的列键
	extra	任意 JSON 可序列化扩展参数
ReportData	chart_type	bar/line/pie/doughnut/polarArea/radar/table
	title / labels / summary	标题、X 轴标签、摘要 dict
	datasets	[Dataset(label, data, backgroundColor?, borderColor?, fill?, extra?)]

to_dict() 输出的就是 Chart.js 能直接吃的 JSON；from_dict() 可反向解析。

4.2 方式一：子类化（推荐，支持钻取/字段列表/缓存）

from datetime import date
from report_engine_kit import (
    ReportDataSource, ReportData, Dataset, ReportParams,
    DataSourceRegistry, export_report_data)

@DataSourceRegistry.register
class OrdersSource(ReportDataSource):
    name = "orders_by_status"      # 必填，同一 app_label 内唯一
    label = "订单状态分布"
    chart_type = "bar"
    cache_ttl = 60                 # 秒；0 = 关闭引擎层缓存
    app_label = "shop"             # 业务隔离；"" 表示所有 app 可见

    def fetch(self, params: ReportParams) -> ReportData:
        # 这里替换成你的真实查询（SQL/ORM/HTTP 都行）
        rows = my_db.query_orders(params.start_date, params.end_date)
        return ReportData(
            title=f"订单状态（{params.start_date} ~ {params.end_date}）",
            labels=[r["status"] for r in rows],
            datasets=[Dataset(label="订单数", data=[r["n"] for r in rows])],
            summary={"total": sum(r["n"] for r in rows)},
        )

    # 可选：单元格下钻
    def drilldown(self, params, group_key: str) -> "DrilldownData":
        from report_engine_kit import DrilldownData
        items = my_db.query_detail(group_key)
        return DrilldownData(
            headers=["订单号", "金额"],
            rows=[[x["code"], x["amount"]] for x in items],
            total=len(items))

    # 可选：动态字段元信息（前端字段下拉用）
    def list_fields(self, params):
        from report_engine_kit import FieldMeta
        return [FieldMeta("status", "状态"), FieldMeta("dept", "部门")]

# 调用（带 app_label 做隔离校验）
data = DataSourceRegistry.fetch(
    "orders_by_status",
    ReportParams(start_date=date(2026, 1, 1), end_date=date(2026, 9, 30)),
    app_label="shop")

open("orders.xlsx", "wb").write(export_report_data(data, "xlsx"))

4.3 方式二：注册已有函数（零改造接入）

引擎会通过函数签名自动识别它需要哪些参数，日期按旧约定传 ISO 字符串：

from report_engine_kit import register_legacy_function

def get_sales(start_date=None, end_date=None, profession_filter=None):
    # 你已有的函数，完全不用改
    return {"chart_type": "line", "title": "销售趋势",
            "labels": ["6月", "7月", "8月"],
            "datasets": [{"label": "收入", "data": [10, 20, 30]}]}

register_legacy_function(
    "sales", "销售趋势", get_sales,
    chart_type="line",
    supports_drilldown=False,
    drilldown_fn=None,          # 也可注入 (params, group_key) -> DrilldownData
    app_label="shop",
    cache_ttl=None)             # None = 用全局默认 LEGACY_SOURCE_CACHE_TTL

4.4 六种导出格式

from report_engine_kit import export_report_data
for fmt in ("csv", "json", "md", "html", "xlsx", "pdf"):
    try:
        with open(f"out.{fmt}", "wb") as f:
            f.write(export_report_data(data, fmt))
    except Exception as e:
        print(fmt, "需要安装对应 extra：", e)

格式	extra	特点
csv	无	utf-8-sig BOM（Excel 直接打开不乱码）；= + - @ 开头自动加 Tab 防公式注入
json	无	完整 payload；传了 params 时附 params 字段
md	无	Markdown 管道表格，|/换行自动转义
html	无	独立样式 HTML，默认内嵌 Chart.js（CDN）；chart_cdn_url="" 可完全离线
xlsx	xlsx（openpyxl）	样式表头、CJK 感知自动列宽、冻结表头、非标量紧凑 JSON 化、公式注入防护
pdf	pdf（reportlab）	A4 表格；自动探测中文字体（Windows simhei/msyh/simsun、Linux wqy/noto、REPORT_ENGINE_FONT_PATH）

自定义导出器：

from report_engine_kit.export import BaseExporter, ExporterRegistry

@ExporterRegistry.register
class ParquetExporter(BaseExporter):
    format_name = "parquet"
    content_type = "application/vnd.apache.parquet"
    file_extension = "parquet"
    def export(self, data, params=None) -> bytes: ...

---

五、教程 B：内存表透视（核心教程，含完整 JSON 配置）

5.1 第一步：理解数据契约

DataTable(
    key="cr",                 # 域内唯一表标识
    label="CR",               # 显示名/列维度取值
    headers=["编号", "状态", "日期", "金额"],
    rows=[TableRow(code="CR-001", cells={"0": "CR-001", "1": "关闭",
                                         "2": "2026-02-10", "3": "200"})],
    code_col=0,               # 去重编码列
    status_col=1,             # __status__ 语义维度映射到此列
    date_cols=[2],            # 日期列（表头名含日期/时间也会自动识别）
    version="20260930_v1",    # 版本变化会驱动代表行 L1 缓存换 key
)

数据加载器：load_tables(force) -> list[DataTable]，force=True 表示穿透业务侧缓存重读。

5.2 第二步：注册业务域（权限钩子全部在这里）

register_domain(
    code="change", label="变更管理",
    load_tables=load_change_tables,
    can_access=lambda u: u.is_authenticated,            # 读权限
    can_manage=lambda u: u.is_staff,                    # 新建/另存/公开权限
    restricted_cols=lambda u, t: {3} if not u.is_staff else set(),  # 列级隐藏
    blocked_header=lambda name, t: name == "内部备注",  # 额外禁止入筛/入维度的列
    default_tables=["cr", "fcr"],
    drill_resolver=my_drill_builder,                    # 数值格下钻
)

钩子说明：

钩子	签名	用途
can_access(user)	→ bool	服务层与 HTTP 层都校验
can_manage(user)	→ bool	决定能否创建/编辑/公开；缺省同 can_access
restricted_cols(user, table)	→ set[int]	该用户看不到的列（结果缓存键含权限指纹，不会串视角）
blocked_header(name, table)	→ bool	额外屏蔽列；引擎默认还会屏蔽表头含"分发/回收/接收/归还"的流转列
drill_resolver(config, row_key, metric_key, restricted)	→ list[dict]	右键下钻明细，返回 [{code,label,count,url}]

5.3 第三步：构造配置并执行

config = {
    # ── 参与统计的表（多表时 __type__ 维度可用）
    "sheets": ["cr", "fcr"],

    # ── 行维度（最多 4 级）；__type__=表类型，__status__=语义状态列，其余写表头名
    "row_dimensions": ["__type__", "__status__"],
    # 维度也可以带值过滤：{"key": "专业", "values": ["EM4", "EM5"]}

    # ── 列维度（交叉透视，最多 1 级）：每个指标按该维度取值扇出多列
    "column_dimension": "部门",

    # ── 指标列（最多 30 个）
    "metrics": [
        {
            "key": "total", "label": "总数", "group": "一期",   # group 相同→多级表头分组
            "agg": "count",                                    # count/sum/avg/min/max/distinct
            "col_filters": [                                   # 本指标独立筛选
                {"name": "关闭日期", "values": [], "mode": "include",
                 "keyword": "",
                 "date_rule": {"col": "关闭日期", "mode": "range",
                               "start": "2026-01-01", "end": "2026-03-31"}}],
            "filter_logic": "and",                             # and / or
            "date_rule": {"col": "导入日期", "mode": "year"},  # 指标自己的日期窗口
            "number_format": {"decimals": 0, "thousands": True}
        },
        {"key": "rev", "label": "金额", "agg": "sum",
         "value_field": "金额",
         "number_format": {"decimals": 2, "thousands": True, "prefix": "￥"}},
        {"key": "share", "label": "占比", "calc": "pct_of_total",
         "calc_source": "rev",
         "number_format": {"percent": True, "decimals": 1}},
        {"key": "cum", "label": "累计", "calc": "running_total",
         "calc_source": "rev"}
    ],

    # ── 全局筛选（所有指标共同行集合；各指标再叠加自己的筛选）
    "global_filters": {
        "date_col": "", "start": "", "end": "",                 # 旧版字段
        "date_rule": {"col": "导入日期", "mode": "month"},      # 新版日期规则（优先）
        "col_filters": [{"name": "专业", "values": ["EM4"], "mode": "exclude"}],
        "filter_logic": "and"
    },

    # ── 行/列控制
    "show_totals": True,            # 合计行
    "show_column_totals": True,     # 列维度下每个指标的"合计"列
    "hide_empty_rows": False,       # 全 0 行隐藏
    "subtotals": True,              # 多级行维度时插入分类小计
    "sort": {"by": "rev", "dir": "desc"},   # 按指标排序
    "row_limit": 10,                # Top N（只保留前 N 个数据行，合计不受影响）

    # ── 手动改值（缓存外应用，自动重算合计）
    "manual_overrides": {'["关闭"]': {"total": 99}},

    # ── 自定义行 / 自定义列（公式见 5.6）
    "custom_rows": [{"id": "r1", "label": "月度目标",
                     "values": {"total": 100}, "formulas": {}}],
    "custom_columns": [
        {"id": "cc1", "label": "翻倍", "template": "=B1*2",
         "cells": {"1": {"t": "n", "v": 3}}}
    ],

    # ── 布局编辑（详见 5.7）
    "spacer_rows": [{"id": "sr1", "after": -1, "height": 30}],
    "spacer_cols": [{"id": "sc1", "after": "dim:__status__", "width": 24}],
    "row_heights": {"data": 26, "totals": 32},
    "col_widths": {"metric:rev": 160},
    "layout_overrides": {"row_order": [], "col_order": [], "hidden_rows": []},

    # ── 文字横幅行/侧边行头、手选合并、备注
    "custom_texts": [], "custom_merges": [],
    "footer_text": "数据来源：变更台账"
}

result = svc.execute(config)

5.4 值聚合方式（Excel "值字段设置"）

agg	含义	是否需要 value_field	说明
count	计数	否	代表行数量（空编码行不计）
sum	求和	是	非数字格忽略
avg	平均	是	只对数字格求平均（非数字不计入分母，同 Excel）
min / max	最小/最大	是
distinct	去重计数	是	数值去重后的个数

数值解析宽松："1,234.5"、"￥200"、"约 300 元" 都能取出数字。

衍生计算：

calc	含义	要求
pct_of_total	占同列合计比例（存 0–1 小数）	calc_source 指定基础指标
running_total	按当前行顺序累计	calc_source 指定基础指标

数字格式 number_format：

{"decimals": 2, "thousands": true, "percent": true,
 "prefix": "￥", "suffix": "元", "multiplier": 1}

xlsx 导出会落成真实的 Excel 单元格格式；CSV/HTML 按格式渲染文本。

5.5 日期规则（date_rule）的 7 种模式

mode	含义	额外字段
today	当天	—
month	本月 1 号 ~ 今天	—
year	本年 1 月 1 号 ~ 今天	—
recent	最近 N 天	days（0–3650）
range	固定区间	start, end（YYYY-MM-DD）
offset	相对偏移（超期未关闭类）	base(today/fixed)、baseDate、sign(plus/minus)、unit(day/month/year)、amount、cmp(gt/lt)
wheel	滚轮窗口（季度至今等）	s/e 各为 {y,m,d,mode,cur} 部件，跟随当前年/月/日

wheel 示例（本季度第一天到今天，起止自动归一防止"起>止"空表）：

{"col": "关闭日期", "mode": "wheel",
 "s": {"y": {"mode": "cur"}, "m": {"mode": "fix", "v": 10}, "d": {"mode": "fix", "v": 1}},
 "e": {"y": {"mode": "cur"}, "m": {"mode": "cur"}, "d": {"mode": "cur"}}}

筛选条件（col_filters）支持：值多选 include/exclude、关键词包含/不包含、组内 AND/OR；空白值哨兵为 (空白) / (非空)。

5.6 公式 DSL（类 Excel，自研解析器，非 eval）

类别	内容
运算	+ - * /、括号、一元正负号、比较 = == <> != < <= > >=（返回 1/0）
单元格	A1、区间 B1:B10（物理坐标，1 基）
跨报表	[12]C3 单格、[12]A1:A10 区间
函数	SUM / AVG / MIN / MAX / COUNT / ROUND / ABS / SQRT / POWER / MOD / IF / AND / OR / NOT
自定义列	template 整列相对填充（锚第 0 行自动平移）；cells 逐格 {t:"n",v} 手填或 {t:"f",v:"=..."} 公式
错误码	#VALUE! #REF! #DIV/0! #CYCLE! #NAME? #NUM!
防护	公式长度 ≤200、嵌套深度 ≤100、跨报表链 ≤20 层、循环引用检测

=SUM(B1:B5)+ROUND(AVG(C1:C5)*0.1, 2)
=IF(B1>=100, C1*0.9, C1)
=[12]C3 + [12]D3

5.7 布局编辑（0.3.0 新增，服务端真实生效）

{
  "col_widths": {"dim:区域": 200, "metric:rev": 160,
                 "ccol:cc1": 120, "spacer:sc1": 24},
  "row_heights": {"title": 36, "header": 28, "data": 24,
                  "subtotal": 26, "totals": 30, "spacer": 12},
  "spacer_rows": [{"id": "sr1", "after": -1, "height": 30},
                  {"id": "sr2", "after": 4, "height": 16}],
  "spacer_cols": [{"id": "sc1", "after": "dim:区域", "width": 24},
                  {"id": "sc2", "after": "metric:rev"}],
  "layout_overrides": {
    "row_order": ["[\"West\"]", "[\"East\"]"],
    "col_order": ["metric:rev", "metric:cnt", "ccol:cc1"],
    "hidden_rows": ["[\"作废]\"]"]
  }
}

• 列宽：token → 像素（8–800），移动列/列维度扇出/插空列后仍准确。
• 行高：按行类别或按具体元素 id（spacer:<id>、customrow:<id>），xlsx 按 ×0.75 转磅。
• 空白行：after 锚数据行序号（-1 顶部）；会像 Excel 插行一样打断维度合并、平移公式/合并坐标；扁平 CSV 自动剔除。
• 空白列：锚点 __first__ / head / dim:<key> / metric:<key> / ccol:<id>，无边框间隙。
• 移动行：row_order 为 JSON 行键数组，列出的置顶排序、未列的自然顺序追加；hidden_rows 删除并重算小计合计。
• 移动列：指标块/自定义列可移动（含列维度扇出时整块移动），行头/维度列固定在前。

所有移动/插入都由统一的列规划 result.column_plan （{blocks, width, metric_phys, ccol_phys, spacer_phys}）驱动，公式坐标、合并校验、表头、xlsx 共用，不会发生坐标漂移。

5.8 导出

buf = svc.export_xlsx(config, report_name="季度报表")   # BytesIO，带全部样式/格式/小计/空白行列
open("r.xlsx", "wb").write(buf.getvalue())

csv_bytes = svc.export_csv(config, formatted=True)      # 扁平 CSV（含 subtotal/total 行标记）
json_bytes = svc.export_json_bytes(config)              # 原始结果 JSON

CSV 首列为行类型标记：data / subtotal / total / custom，方便二次分析。

---

六、教程 C：声明式图表分析（用户零代码自定义图表）

不想为每一种统计图写 fetch()？把 list[dict] 行交给 aggregate_rows，用一份声明式 spec 描述维度和指标即可：

from report_engine_kit import Dimension, Measure, aggregate_rows

data = aggregate_rows(
    rows,                                   # 任意 list[dict]
    dimensions=[
        Dimension("month", "月份", date_trunc="month"),
        Dimension("status", "状态"),        # 第二个维度自动成为系列（多 dataset）
    ],
    measures=[
        Measure("orders", "订单数"),                                # count
        Measure("revenue", "收入", agg="sum", field_name="amount"),
        Measure("aov", "客单价", agg="avg", field_name="amount"),
        Measure("buyers", "客户数", agg="distinct_count",
                field_name="customer_id"),
    ],
    filters=[
        {"key": "status", "op": "in", "values": ["paid"]},
        {"key": "amount", "op": "gte", "values": ["0"]},
        {"key": "dept", "op": "contains", "value": "EM"},
    ],
    series_dimension=None,    # 缺省取第二个维度
    sort="-orders::paid",     # label / -label / 指标键 / -指标键
    top_n=20,
    chart_type="bar",
)

• 过滤算子：in / not_in / contains / not_contains / eq / ne / gt / gte / lt / lte / between / blank / nonblank
• 日期分桶：day / week / month / year；空值归入 (blank) 且始终排序最后
• 同样的 spec 可以通过 HTTP 驱动数据源，终端用户不改服务端代码即可新增图表：

from report_engine_kit import analytics_source_factory, ReportParams, DataSourceRegistry

analytics_source_factory(
    "shop_dyn", "店铺动态报表",
    rows_loader=lambda params: my_db.fetch_all(),   # 返回 list[dict]
    app_label="shop")

# HTTP 侧把 spec 放进 extra
params = ReportParams(extra={
    "dimensions": [{"key": "dept"}],
    "measures": [{"key": "n", "label": "数量"}],
    "filters": [{"key": "status", "op": "in", "values": ["paid"]}],
})
data = DataSourceRegistry.fetch("shop_dyn", params, app_label="shop")

完整可运行示例：examples/04_analytics_chart.py。

---

七、教程 D：命令行 CLI

任何 import 时调用 register_domain(...) 的 Python 模块都可以作为"域模块"。参考 examples/shop_domain.py。

# 列出已注册业务域
report-engine --domain-module shop_domain domains

# 查询可用行维度 / 日期列
report-engine --domain-module shop_domain dimensions --domain shop_orders

# 字段取值（带计数、分页、关键词）
report-engine --domain-module shop_domain field-options --domain shop_orders \
    --field Status --limit 20

# 临时执行一份配置（JSON；装了 [yaml] extra 也支持 YAML）
report-engine --domain-module shop_domain execute --domain shop_orders \
    --config pivot.json -o result.json

# 导出（xlsx / csv / json）
report-engine --domain-module shop_domain export --domain shop_orders \
    --config pivot.json --format xlsx --out r.xlsx --name 季度报表

# 免数据库：用 JSON 文件保存/列表/执行/删除/公开报表
report-engine --domain-module shop_domain reports save \
    --domain shop_orders --store file:reports.json --config pivot.json
report-engine --domain-module shop_domain reports list \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports run 1 \
    --domain shop_orders --store file:reports.json --format xlsx --out r1.xlsx
report-engine --domain-module shop_domain reports public 1 \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports delete 1 \
    --domain shop_orders --store file:reports.json

全局选项：--user-id（默认取环境变量 REPORT_ENGINE_USER_ID）、--admin、--store memory|file:路径。

---

八、教程 E：不用数据库保存报表（JSON 文件存储）

from report_engine_kit.tabular_pivot import (
    PivotService, JsonFileReportStore, SimpleUser, register_domain)

register_domain("orders", "订单",
                load_tables=..., can_access=lambda u: True,
                can_manage=lambda u: True)

store = JsonFileReportStore("data/reports.json")   # 原子写（temp+os.replace）
svc = PivotService("orders", user=SimpleUser(id=1, is_superuser=True), store=store)

record = svc.save_report({"name": "9 月经营看板", **pivot_config})
print(svc.list_reports(scope="available"))
print(svc.list_reports(scope="public"))
result = svc.run_report(record["id"], force=False)
svc.set_public(record["id"], True)

权限语义（内存存储、JSON 文件存储、Django ORM 存储完全一致）：

• 内容编辑只允许属主（内置模板仅超管），其他人用"另存为"；
• 新建/公开需要 can_manage 或超管；
• 读取允许：本人 / 已公开 / 内置模板 / 域管理员（可引用他人私有表）；
• 删除：属主或超管。

存储协议可自行实现 list_reports/get_config/save_fields/delete/set_public，例如接 MongoDB/Redis。

---

九、教程 F：Django 集成

9.1 安装与路由

# settings.py
INSTALLED_APPS = [..., "report_engine_kit.contrib.django"]

# urls.py
from django.urls import include, path
urlpatterns += [
    path("report-engine/tabular/<str:domain_code>/",
         include("report_engine_kit.contrib.django.tabular_urls")),
]

python manage.py migrate report_engine_kit   # app_label = report_engine_kit

App 在 ready() 中自动把报表缓存接到 Django 缓存框架。然后在你自己 app 的 ready() 注册 domain：

from report_engine_kit.tabular_pivot import register_domain
from report_engine_kit.contrib.django.orm_adapters import table_from_queryset
from myapp.models import Order

register_domain(
    "orders", "订单",
    load_tables=lambda force=False: [table_from_queryset(
        Order.objects.all(), key="orders", label="订单",
        fields=["code", "status", "created_at", "dept__name"],
        code_field="code", status_field="status")],
    can_access=lambda u: u.is_authenticated,
    can_manage=lambda u: u.is_staff,
)

打开 /report-engine/tabular/orders/ 即是自包含的拖拽透视设计器页面。

9.2 通用端点

GET  dimensions/        field-options/      config/list/
GET  config/load/<id>/  config/execute/<id>/
POST execute/  export/  drill/  config/save/
POST config/delete/<id>/  config/visibility/<id>/

固定统计图用 report_engine_kit.contrib.django.http.ReportApiMixin （list_sources/get_data/get_drilldown/export/list_fields），自行加登录装饰器。

9.3 ORM 版透视引擎（SQL 聚合而非内存计数）

当指标需要直接在数据库里聚合时，实现一个 PivotContext（钩子：维度元数据、基础 QuerySet、业务域过滤、跨源关联、谓词 Q、谓词快路径、统计项版本……）：

from report_engine_kit.contrib.django.pivot import PivotContext, PivotReportEngine

class OrderPivotContext(PivotContext):
    app_label = "orders"
    config_model = MyReportConfig
    metric_model = None
    def list_dimensions(self, scope_code=""): ...
    def get_base_queryset(self, data_source): ...
    def apply_scope_filter(self, qs, ds, codes): ...

engine = PivotReportEngine(OrderPivotContext())

特点：单条 SQL Count(Q(...)) 条件聚合、失败自动降级逐指标分组、谓词指标 AND/OR 树、HAVING、图表数据、单元格公式、chunked_robust_count（抗 MySQL 2013 断连）。

跨报表快照刷新（守护线程、跨进程缓存锁、close_old_connections() 处理 MySQL wait_timeout）：

from report_engine_kit.contrib.django.pivot.snapshot import (
    register_pivot_domain, start_snapshot_scheduler)
register_pivot_domain("orders", OrderPivotContext, MyReportConfig)
start_snapshot_scheduler("22:30")

完整说明见 docs/django.md，完整 API 参考见 docs/api.md。

---

十、数据口径（计数规则，务必先读）

这是透视引擎最重要的业务约定，直接决定数字对不对：

1. 按编码列去重：code_col 相同的多行视为同一实体；编码为空的行不参与统计。
2. 同编码多行 = 合并单元格：每一列按行顺序取第一个非空值（代表行继承）。例如同一变更号两行，状态分别为空/"关闭"，最终状态 = "关闭"。
3. 每指标独立筛选：全局筛选（含全局日期）确定共同行集合，各指标再叠加自己的列筛选和日期窗口。
4. 日期筛选：启用日期区间时，日期空/不可解析的行不计入；parse_date_lenient 支持 2026/2/9、2026.2.9、2026年2月9日 等宽松格式。
5. 多表对比：__type__ 维度按表 label 分组；同一维度名取各表共有列。
6. 受限列：被 restricted_cols 隐藏的列对该用户既不进维度也不进指标取值；缓存键含精确的隐藏列指纹。
7. 合计口径：合计行由累积器合并而来（avg 是总 sum/总数字数，不是平均的平均；distinct 是值集并集），不会因为排序/Top N 而算错。

---

十一、缓存机制与配置

from report_engine_kit.cache import (
    MemoryCacheBackend, ReportCacheManager, configure_cache)
from report_engine_kit import configure

# 换成 Django 缓存（[django] extra 在 AppConfig.ready() 里会自动做）
configure_cache(DjangoCacheBackend(alias="default"))

# 全局参数
configure(DEFAULT_CACHE_TTL=300,
          LEGACY_SOURCE_CACHE_TTL=120,
          MEMORY_CACHE_MAX_ENTRIES=2048,
          DRILL_PAGE_SIZE_DEFAULT=50,
          DRILL_PAGE_SIZE_MAX=200)

• 核心默认后端 MemoryCacheBackend：线程安全、TTL、容量淘汰、原子 add、glob 模式删除；也可由环境变量 REPORT_ENGINE_DEFAULT_TTL / REPORT_ENGINE_MEMORY_CACHE_MAX_ENTRIES 覆盖。
• 透视结果缓存 30 秒，代表行 L1 缓存 1 小时（最多 64 个键），缓存键 = 配置 + 权限指纹 + app 标识，不同视角绝不串数据。
• 自定义后端只需实现 get/set/delete（可选 add/delete_pattern）。

---

十二、安全模型

• 域访问/管理/列隐藏钩子在服务层强制执行，前端传参不可信；
• 透视输入全部白名单清洗并有数量上限（行维度 ≤4、指标 ≤30、筛选值 ≤200、自定义格 ≤5000、公式长度 ≤200、跨报表链 ≤20……）；
• 跨报表引用 [id] 以查看者身份重算，只允许本人/公开/域管理员可见的报表；
• CSV/XLSX 导出对 = + - @ 开头的值做公式注入中和；下载文件名 RFC 5981/5987 编码；
• 公式是自研 tokenizer/parser，不是 eval，数值 AST 白名单；
• HTML 导出为独立文档，图表 CDN 可关闭；手动数据只接受数字。

---

十三、能力总览（与 Excel 数据透视表对照）

能力	report-engine-kit
行字段（多级）	✅ 最多 4 级，自动合并相同值
列字段	✅ 1 级列维度交叉透视，指标×维度值扇出，可带每指标合计列
值汇总	✅ 计数/求和/平均/最大/最小/去重计数
值筛选 / 报告筛选	✅ 每指标独立筛选 + 全局筛选，AND/OR，关键词，13 种算子
日期分组	✅ 日/周/月/年 + 7 种动态日期规则（含滚轮窗口）
分类汇总（小计）	✅ 多级行维度自动小计，所有聚合精确合并
总计	✅ 行合计、列合计（可开关）
排序 / Top N	✅ 按任意指标升降序 + 保留前 N
占比 / 累计	✅ pct_of_total、running_total
数字格式	✅ 小数位/千分位/百分比/前后缀/乘数，xlsx 真实格式
计算字段/公式	✅ 14 个函数 + 比较运算 + 区间 + 跨报表引用 + 循环检测
手动改值	✅ 单元格覆盖，红字标注，合计重算
自定义行/列	✅ 含公式列、整列模板
插空白行/空白列	✅ 服务端生效，坐标系统一
移动行/列、隐藏行	✅ 服务端生效（引擎+xlsx+CSV 一致）
行高/列宽	✅ 分类/按元素配置，移动后仍准确
手选合并单元格	✅ owner 矩阵校验，过期/非法项静默丢弃
多级表头/文字横幅	✅ 布局引擎单一事实来源
下钻明细	✅ domain 钩子，编码集合 → 明细链接
导出	✅ xlsx / csv / pdf / json / html / md
免数据库部署	✅ CLI + 原子 JSON 文件存储
Web 集成	✅ 可选 Django App（视图/模型/迁移/前端资源）+ ORM 聚合引擎
统计图	✅ 数据源注册表 + 声明式分析（用户零代码配图表）

---

十四、项目结构

report-engine-kit/
├── pyproject.toml / MANIFEST.in / LICENSE / CHANGELOG.md
├── README.md                      # 本文档
├── docs/
│   ├── api.md                     # 核心 API 完整参考（英文）
│   ├── django.md                  # Django 接入与 ORM 透视引擎指南（英文）
│   └── release.md                 # 版本发布手册
├── examples/                      # 4 个可直接运行的教程脚本 + 示例数据
├── scripts/release.ps1            # Windows 发布脚本
├── .github/workflows/publish.yml  # 测试/构建/发 TestPyPI/PyPI
├── tests/                         # 110 个单元测试
└── src/report_engine_kit/
    ├── 固定统计图：contracts/base/registry/cache/export/snapshot/analytics/date_utils/cli
    ├── tabular_pivot/             # 内存表透视引擎（含 stores/loaders/adapters/numbers）
    └── contrib/
        ├── pandas_support.py      # [pandas]
        └── django/                # [django]：app/模型/迁移/HTTP/ORM 透视/前端资源

---

十五、开发、测试与发布

git clone <repo> && cd report-engine-kit
python -m venv .venv
# Windows
.\.venv\Scripts\Activate.ps1
# macOS/Linux
source .venv/bin/activate

pip install -e ".[dev,all]"
pytest                                   # 110 个测试
python examples/02_tabular_pivot.py      # 手工冒烟
python -m build
python -m twine check dist/*

发布（详细步骤见 docs/release.md）：

$env:TWINE_USERNAME = "__token__"
$env:TWINE_PASSWORD = "<token>"
.\scripts\release.ps1 -Version 0.3.0 -Target testpypi   # 先 TestPyPI
.\scripts\release.ps1 -Version 0.3.0 -Target pypi       # 正式 PyPI（有二次确认）

Git 打 v*rc* 标签自动发 TestPyPI，v* 正式标签发 PyPI（GitHub Actions，预置 OIDC trusted publishing）。

---

十六、常见问题 FAQ

Q：为什么我的计数和 Excel COUNTIF 对不上？ A：检查三点：① 编码列是否正确（空编码行不计、同编码多行只算 1 个代表行）；② 指标是否被自己的 col_filters/date_rule 二次过滤；③ 受限列是否把数据藏掉了。口径说明见第十节。

Q：sum/avg 遇到文字单元格怎么办？ A：非数字格被忽略（count 仍计入），avg 的分母只算数字格。金额里的逗号、货币符号会被宽松解析，实在没有数字才算非数字。

Q：配置在前端能拖，导出 Excel 却没有隐藏行/排序？ A：0.3.0 起 row_order/col_order/hidden_rows 在服务端生效；请确认你用的是 0.3.0+，且配置确实保存在 layout_overrides 里。

Q：公式 =[12]C3 报 #REF!？ A：三种原因：报表 12 不存在、当前查看者无权访问（不是本人/公开/域管理员）、或 C3 在目标报表里不是数字格。循环引用会报 #CYCLE!。

Q：不想装 Django / openpyxl 能用吗？ A：能。核心零依赖，csv/json/html/md 导出、全部透视计算、CLI、JSON 文件存储都可用；只有 xlsx/pdf/pandas/Django 相关能力需要对应 extra。

Q：PDF 中文乱码？ A：设置环境变量 REPORT_ENGINE_FONT_PATH 指向字体文件；Linux 可装 fonts-wqy-zenhei 或 noto-cjk。

Q：数据量很大怎么办？ A：内存表引擎适合台账量级（单表几万行字符串代表行以内）。更大数据量请：① 在 load_tables 里先做 SQL 聚合再喂引擎；② 或用 [django] extra 的 ORM PivotReportEngine（单条 SQL 条件聚合）。

Q：如何在 Flask/FastAPI 里用？ A：直接实例化 PivotService，把 request JSON 作为 config 传入即可；权限钩子接收你自己的 user 对象（鸭子类型，只需 .id / .is_superuser）。Django 视图只是薄封装，不依赖 Django 之外的框架。

---

许可证

MIT © bzsystem team