Lateral Workbook Format (v2)

The lateral workbook is the offline XLSX representation of the lateral EPD editor: a spreadsheet where rows are input fields and columns are EPDs. It is the interchange format for bulk authoring — fill a workbook, import many draft EPDs — and for exporting a project's drafts for offline editing.

This page is the format specification (workbook format version 2). It is the shared contract that both platforms implement:

• V2 (Django) reads and writes it with openpyxl.
• V1 (NestJS) reads and writes it with SheetJS (xlsx).

A file produced by either platform must be importable by the other. The committed golden file (fixtures/lateral/lateral_golden_v1.xlsx, generated by fixtures/lateral/generate_golden.py) is the executable form of this spec: both platforms' importers are tested against it.

Naming

Here V1 = the NestJS platform (/backend, frontend/src/v1) and V2 = the Django platform (/django, frontend/src/v2) — the product naming, which is the opposite of the backend v0/v1 naming used elsewhere in the codebase.

The portability contract: MUST vs SHOULD

Not every feature can be written by both libraries. SheetJS's community build cannot write Data Validation dropdowns or sheet protection; openpyxl can. The format therefore splits into two layers:

Layer	Contents	Written by
MUST (identical across libraries)	Sheet set, row/column layout, hidden field_id column, Description column, cell serialization, _meta version, formula-injection escaping, date encoding	openpyxl and SheetJS
SHOULD (presentation, ignored on read)	Data Validation dropdowns, sheet protection, frozen panes	openpyxl only; SheetJS omits them

Because the SHOULD-layer features are ignored on read, an openpyxl-exported file (with dropdowns and protection) and a SheetJS-exported file (without them) are mutually importable. The committed golden file is the rich openpyxl variant, so both importers are exercised against the maximal feature set.

Workbook structure

Sheet	Visibility	Purpose
EPDs	visible	The main grid: rows = fields, columns = EPDs
Results - <table>	visible	One per results table: rows = EPD x table x indicator, columns = modules
_lists	hidden	Option lists backing the choice-field dropdowns
_meta	hidden	Provenance + workbook format version

There is one Results - ... sheet per results table the PCR spec declares, so a spec with extra or differently named tables round-trips without a code change.

The EPDs (main) sheet

        A            B (hidden)   C                D            E            F
  1  _epd_id                                       <epd_id>     <epd_id>     (blank)    ← hidden
  2  _spec_version_id                              <spec_ver>   <spec_ver>   (blank)    ← hidden
  3  _updated_at                                   <iso ts>     <iso ts>     (blank)    ← hidden
  4  Field        field_id      Description        Concrete4000 Concrete5000 (new EPD)  ← header
  5  General                                                                            ← section
  6  Product name product_name  The commercial …   Concrete 4000 …           …          ← field
  …

• Column A — the human field label (for the person editing the sheet).
• Column B — the stable field_id (hidden). This is the row-match key on import — never the label. A row whose column B is empty is a section header and is skipped by the importer.
• Column C — the field's description: the PCR spec's help_text, the same guidance the in-app form shows, flattened from rich text to plain prose (tags become spaces, entities decode, whitespace collapses). Read-only and never imported — it exists so somebody filling the workbook offline is not working blind. Blank on section-header rows and on fields whose spec carries no help text.
• Columns D onward — one column per EPD. The header (row 4) shows the product name; the real identity is in the hidden metadata rows.
• Rows 1–3 (hidden) carry per-column metadata: epd_id, spec_version_id, updated_at.
• Row 4 is the header; rows 5+ alternate section-header rows and field rows.
• Frozen panes at D5 keep the label/field_id/description columns and the metadata + header rows in view while scrolling (SHOULD layer).

A blank template column — one with no epd_id in row 1 — is treated as import-as-create: it becomes a brand-new draft EPD. Its cells (and its results rows) are read against the PCR spec that new EPD will pin — the project's latest published revision — not against the project's existing EPDs, so a create column imports into a project that has no EPDs yet. A create column none of whose cells can be imported is reported as an error and creates nothing.

Which columns an import writes

Every exported EPD comes back as a column, including the ones a user only kept for reference while filling in template columns. The dry run gives each column one action:

action	Column	On apply
update	An EPD of this project whose values the workbook changes	Written through the bulk save
create	A template column with data	A new draft EPD
skip	Nothing to write — see skip_reason	Left alone (status: "skipped")

A column is skipped (never an error) when:

• skip_reason: "unchanged" — it names an EPD of this project and changes nothing. That includes non-draft EPDs, whose cells are read-only anyway, and results stored with legacy uppercase module keys (A1A2A3), since no cell can change a key's case.
• skip_reason: "other_project" — the workbook's _meta.project_id names a different project and the column is one of that project's EPDs. Nothing about the other project's EPD is looked up; its results rows are dropped with it.

A skipped column is never counted as an update and never stale: the stale check guards a write, so it only applies to a column that would write. When the stored values themselves moved since the export, though, an untouched column reads as a change (it would overwrite the newer values), so it stays stale.

Columns are matched by the hidden epd_id, never by product name. Names are not unique within a project and they are themselves an editable cell, so a rename would silently turn an update into a create, and a seeded template column (which starts out carrying its source EPD's name) would overwrite its source. Instead, a create column whose name is already taken — by an EPD in the project or by another create column in the same workbook — gets a duplicate_name warning, which catches a workbook imported twice or a seeded column nobody renamed. It does not change what is created.

The _meta sheet

A simple key / value table:

key	value (golden file)
project_id	11111111-…
platform	v2
export_timestamp	2026-07-12T00:00:00Z
format_version	2

The format_version lets the layout evolve without silently breaking files sitting on someone's desktop: an importer reads this first and can refuse (or best-effort handle) a version it doesn't understand.

Version history

Version	Change	Import support
1	Original layout. No Description column; EPD columns start at C.	Still read. A workbook someone started filling in before version 2 shipped must keep importing.
2	Adds the read-only Description column (C); EPD columns move to D.	Current export.

An importer MUST resolve the first EPD column from format_version, not from whatever the current export writes. Reading a version-1 sheet at version-2 offsets drops its first EPD silently; reading a version-2 sheet at version-1 offsets ingests the description text as if it were a manufacturer's EPD data. A version outside the supported set is rejected outright with "re-export the workbook" — never best-effort parsed.

When format_version is missing

_meta is hidden, so a workbook can reach an importer without it (hand-built, or passed through a tool that dropped the sheet). Do not assume version 1 in that case — a version-2 workbook read at version-1 offsets starts scanning at the Description column, which carries no epd_id but does carry text on nearly every field row. That is exactly the import-as-create shape, so the PCR guidance gets written into a brand-new EPD with no error raised.

Sniff the header instead:

If the header cell at C4 is exactly Description and C1 (epd_id) is empty, the workbook is version 2. Otherwise it is version 1.

Both conditions matter: every version-2 export writes that header unconditionally, independent of _meta, and the empty-epd_id check keeps a version-1 EPD column that happens to be named "Description" from being misread — a real EPD column always carries its id in row 1.

The _lists sheet

One column per choice field, field_id in the header row and the option values below. openpyxl attaches a Data Validation dropdown to each choice cell that references this sheet (e.g. _lists!$A$2:$A$3). SheetJS omits the dropdown but still writes the _lists sheet and the cell value.

Cell serialization

Cell values use one serialization registry — the same serializeCell / parseCell pair the in-app grid clipboard uses (frontend/src/v2/lateral/serialization.ts, SMA-2437). Never build a second field list or coercion path. Serialization is keyed off each field type's coerce class from the field-type registry (frontend/src/v2/components/ui/fieldTypeRegistry.ts):

coerce class	Field types (examples)	Canonical cell form	Excel format
number	number, integer, decimal, currency, percentage	normalized number (1.50 → 1.5)	native number
boolean	boolean	"true" / "false"	Text (@)
json	unit_value, multiselect, table, array, json, section_list, …	JSON with sorted keys, no spaces	Text (@)
passthrough	text, select, email, url, dates, …	the string value; a structured value (a {from, to} percentage-range, a plant_picker list) is sorted-key JSON, and a percentage-range object cell parses back to the object	Text (@)
passthrough + rich_text	rich_text	whitespace-collapsed HTML	Text (@)

Canonical guarantee (from S1): serialization is deterministic and equality-comparable — serialize(parse(s)) === s for any canonical s. Empty (null / undefined / "") serializes to the empty cell ""; note serializeCell('array', []) is "[]", not "".

Cross-language parity

S1 is TypeScript; the V2 codec is Python (openpyxl). There is no shared code across that boundary — the Python side must reproduce S1's canonical rules (stableStringify, canonicalNumber, canonicalizeRichText) exactly. The golden generator does this in fixtures/lateral/generate_golden.py, and the cross-library golden-file tests are what keep the two in lockstep.

Why Text format for non-number cells

text, boolean, json, and rich_text cells are written with the Excel Text number format (@). This stops Excel from auto-converting them — stripping leading zeros, turning A1-A3 into a date, or mangling a serialized JSON string. Numbers stay native so they remain sortable and human-editable; dates are written as ISO strings in Text cells (importers accept both ISO strings and Excel serial dates, but exporting ISO text avoids re-serialization drift).

Security rules

The workbook is user-authored data opened in other people's Excel, so importers re-validate everything — sheet protection and the hidden metadata are guardrails, not trust boundaries.

Formula-injection escaping

A cell whose serialized value starts with =, +, -, @, tab, or carriage return could execute as a formula in a victim's Excel (EPD product names are user input). Defense is two-layered:

1. Primary: every text/complex/rich_text cell is Text-formatted (@), so Excel never evaluates it.
2. Sentinel: on export, prepend a single literal apostrophe ' ahead of a leading trigger char. On import, strip exactly one leading ' only when it immediately precedes a trigger char, then hand the value to parseCell.

The sentinel is a pure string prepend/strip, identical in openpyxl and SheetJS — deliberately not the quotePrefix cell-style flag, which the two libraries serialize inconsistently. It is added only ahead of trigger chars and stripped only on ' + trigger, so a re-export/re-import cycle is stable. (Edge case, documented and vanishingly rare: a value that legitimately begins with ' followed by a trigger char, e.g. '=foo, loses that apostrophe on import.)

Metadata is untrusted

The hidden epd_id / spec_version_id / updated_at rows are an untrusted hint. On import the server:

• uses epd_id only to match a column to an existing EPD, then verifies it belongs to the project, is a draft, and the user can write it — otherwise nothing is written to it (never a silent create). An id this project doesn't have is a per-column unknown_epd error, unless the workbook came from another project (see Which columns an import writes);
• feeds updated_at into the bulk-save optimistic lock as expected_updated_at (a stale token → 409, reported per column);
• re-derives spec_version_id from the matched EPD — the sheet can never repin an EPD's spec.

A blank epd_id means import-as-create.

A workbook whose _meta.project_id names a different project imports its create columns normally; its EPD columns cannot update anything in the target project, so they are skipped with skip_reason: "other_project" rather than reported as errors.

Upload validation

Importers accept .xlsx only (reject .xlsm / macro workbooks), reject formulas in editable ranges, enforce a size limit, and apply the same catalog-whitelist + prototype-pollution guards as the existing bulk importer.

Edge cases

Case	Handling
Oversize cell (> 32,767 chars — Excel's hard cap)	Export writes a read-only truncation marker (⟪TRUNCATED:oversize⟫); the importer refuses to write it back.
Smart-quote mangling	Excel autocorrect turns " into curly quotes inside serialized JSON/HTML cells; the importer normalizes curly quotes before parsing.
Locale decimals	Import accepts ,-decimals; export always writes canonical .-decimals.
Dates	Import accepts ISO strings and Excel serial dates; export writes ISO strings in Text cells.
Read-only cells (derived, N/A, variant-locked, non-draft columns)	Locked via sheet protection where supported; the importer never writes them regardless (re-validation is the real guard).
Truncated / hand-edited sheet	A field row that is absent from the sheet is left untouched; a present-but-blank cell clears its field. Absent ≠ blank, so a truncated sheet can't silently wipe fields it omits.

Results sheets

Environmental results are not cells on the EPDs sheet. A results table is an indicators x modules grid, which does not fit one cell — and serialized as JSON it exceeds Excel's 32,767-character cap on a real EPD. Each results table (a spec section_list whose item_fields contain a results_matrix) therefore gets its own sheet, mirroring the V1 bulk importer's Results - ... sheets.

        A       B (hidden)   C       D (hidden)  E           F           G...
row 1 | EPD   | epd_id     | Table | table_idx | Indicator | Unit      | A1 | A2 | A3 | A1A2A3 | ...
row 2 | Panel | 019f-...   |       | 0         | GWP-total | kg CO2 eq |    |    |    | 320.5  | ...

Column	Meaning
EPD	The EPDs-sheet column header. Display, and the only match key a not-yet-created EPD has
epd_id	Hidden and authoritative — a row is routed by this whenever it is present
Table	The section item's _title. Display only
table_idx	Hidden ordinal of the stored section item — the authoritative table key
Indicator	The spec indicator id, verbatim. Locked: a typo would retarget the row
Unit	Informational; the spec owns it
G...	One column per information module the spec offers, in lifecycle order with the aggregated production column (A1A2A3) directly after A3. Match columns by header, never by position

Tables are paired by table_idx, never by title. Titles are neither unique (the editor's "add table" creates one with no title at all) nor stable (renaming one would otherwise strand its discussion on an emptied table). A workbook exported before the ordinal column existed falls back to title matching, and a title already claimed by an earlier item is not reused.

Rules:

• Every spec indicator is scaffolded for every editable column, so the sheet is fillable. An untouched all-blank row is ignored, never imported as an empty result.

• ND / N/A / NA / MND mean "declared, not reported": the module value is cleared but the row is kept. This is distinct from a blank cell.

• Cells are Text format, so the scientific notation EPD results are quoted in (3.40E-08) survives the round trip instead of being re-rendered by Excel.

• Module keys are read case-insensitively. normalize_results_matrix_keys canonicalizes a document only as it is written, so many stored matrices still hold the uppercase module ids of their source (A1A2A3, B6). Reading them case-sensitively would export the whole table blank — which then reads back as a deliberate clear. Written values are lower-cased, the same canonical form any other document save produces.

• A cell whose value did not change keeps its stored representation. V1 converted documents hold results as floats while the form writes strings; rebuilding every cell from sheet text would retype a whole table because one cell was edited, which is permanent noise in the review diff.

• unknown_indicator and unknown_module are reported per cell at import. Both are hard-reject codes in epd_update_document, so letting them through would reject the entire column with one opaque message and make the dry-run preview stop predicting the apply.

• A row naming an EPD the workbook has no column for is a workbook-level warning, raised only when the row actually carries data.

• A results sheet that is absent leaves the table untouched; a sheet whose rows are all blank clears it (same absent-vs-blank rule as the EPDs sheet). Because the clear is destructive, the dry run reports a results_cleared warning so it is never silent. This differs from V1, whose template could not display the current results and so read an empty sheet as "keep".

• _labels overrides are carried across. They live inside the matrix, so replacing the matrix wholesale would silently delete the manufacturer's custom indicator names. Overrides naming an indicator the spec no longer declares are dropped, because unknown_label_override is a hard reject.

• Non-numeric values are formula-escaped on export; numbers are not. A results value is free text in the editor, so it can begin with = and openpyxl would write a live formula. Escaping everything would be wrong: - is a formula trigger and module D results are routinely negative.

• Results are never read from the EPDs sheet. An older workbook can still carry the section as a JSON cell; that path does not shape-validate the matrix, so it is ignored with a warning rather than passing the dry run and hard-rejecting the whole column at apply.

Results numerics also remain readable by the stats explorer.

Versioning

An import is versioned exactly like any other document save. EPD.document is tracked by EPDDocumentEvent — an append-only pghistory snapshot written whenever the document actually changes — so each imported column produces one snapshot holding the full post-import document, stamped with the acting user and the request URL.

• A dry run writes nothing.
• An unchanged re-upload writes nothing: the importer skips no-op columns, so re-importing a file you did not edit does not add a version — even for a document whose results still carry legacy uppercase module keys (the next real save canonicalizes those).
• Only columns that actually changed are snapshotted, one row each.

Feature gating

The workbook round-trip and the lateral grid editor are gated separately, because they ship on different schedules:

Flag	Default	Gates
epd_bulk_import_export	enabled	GET/POST .../epds/lateral/export|import/, the Export/Import buttons
lateral_epd_editor	disabled	The grid page, its aggregate read, its bulk save, and clone

Enabling the workbook therefore does not expose the grid. Per-org and per-user overrides still take priority over both defaults.

Seeding a template from an existing EPD

template_source_epd_id fills the blank create columns — fields and results rows — from a finished EPD, so a manufacturer can start from a real one instead of an empty grid (V1's ?sampleEpdId= template). The seeded columns still carry no epd_id, so importing them creates new drafts and never edits the source. The source is resolved inside the project, so an EPD from another project is not found.

The golden file

fixtures/lateral/lateral_golden_v1.xlsx exercises every cell class (scalar, number, percentage, boolean, unit_value, table, multiselect, rich text, choice-with-dropdown), plus the formula-injection sentinel, the oversize marker, a read-only derived cell, and a blank import-as-create column. It is generated deterministically by fixtures/lateral/generate_golden.py, which also emits lateral_golden_v1.expected.json — the single source of truth for every cell's expected canonical string, consumed by both platforms' tests:

• V2 / openpyxl: django/smartepd/epds/tests/test_lateral_workbook_golden.py
• V1 / SheetJS: backend/src/epd/integrations/lateral-workbook-golden.spec.ts

Regenerate after any format change:

cd django && uv run python ../fixtures/lateral/generate_golden.py

Consumers

The endpoints that read and write this format are built in separate tickets and test against the golden file above:

• SMA-2441 — V2 XLSX export (GET /api/v2/projects/{id}/epds/lateral/export/)
• SMA-2442 — V2 XLSX import (dry-run report + apply, import-as-create)
• SMA-2449 — V1 XLSX export/import (SheetJS)