# 0036. The catalogue round trip is an xlsx workbook

- **Status:** Accepted
- **Date:** 2026-09-07

## Context

Before go-live the client wants to check and correct the catalogue — 15 sites and 516 units, each with its
dynamic properties. Two reasons a spreadsheet rather than the admin:

- 516 units × 30 properties is 15 000 fields. Walking them one screen at a time is not going to happen.
- The 38 **location** properties are entirely empty in the database: utility suppliers, meter and serial
  numbers, the POD identifier, gate codes, the community charge, contract expiry dates. Only the client
  knows them, and they will be filled in from paperwork, not from a web form.

Whatever we hand over comes back edited and has to be imported. The people editing it are not technical.

## Decision

**An `.xlsx` workbook, built with `phpoffice/phpspreadsheet`**, through
`./yii import/export-xlsx` and `./yii import/import-xlsx`.

Three sheets: **Helyszínek**, **Raktárak**, and a read-only **Segédlet** listing the values every dropdown
column accepts. The `Azonosító` column is locked, the header row is frozen and bold, enum columns carry a
real dropdown with a stop-on-error validation, booleans are `igen` / `nem`, numbers are numbers and dates are
dates.

The round-trip rules:

- **`id` identifies the row.** A row with no id on the units sheet is a new unit; on the sites sheet it is an
  error, because a site needs an address, a floor map and a place in the navigation.
- **An empty cell means "clear this value"**, not "leave it alone" — so any column the client must not touch
  is marked read-only and is never read back.
- **One unreadable cell rejects the whole workbook.** A file that came back with a mistyped dropdown is a
  file to hand back, not to half-apply. The write runs in one transaction.
- **`slug` is never regenerated**, even when the name changes: the unit's URL is what search engines have.
- **`description` is not in the workbook.** Long multilingual HTML, edited properly in the admin.
- The importer writes through the models **with validation on**, unlike the Drupal importer: these values
  were typed by a person.

Reading a cell is generous, and that generosity is tested rather than assumed: `igen`, `Igen`, `1` and `yes`
are all true; `99 900`, `1.234` and `35 900 Ft` are all numbers; an option matches its label ignoring case
and surrounding space. `helpers/SpreadsheetValue.php` holds that, free of the library so it can be unit
tested; `services/SpreadsheetService.php` holds the library's concerns.

**The export and the import live in one class.** `locationColumns()` and `storageColumns()` are the schema of
both directions — label, type, options, how a value is read off a record and written back. Splitting the
directions would mean two copies of that list, and the first divergence would silently drop an edit. This is
the deliberate exception to services being read paths.

## Alternatives

- **CSV.** No new dependency, and that is the whole of its case. Excel decides the encoding and the
  separator on save, turns `1,5` into a date and `06301234567` into 6301234567, and there is no second sheet
  and no validation. With 15 000 cells edited by non-technical hands, every one of those is a data loss we
  would find weeks later.
- **A bulk-edit screen in the admin.** The right long-term answer and far too much work for a one-off
  pre-launch check — and it does not travel to whoever holds the utility contracts.
- **Google Sheets through its API.** Better collaboration, an OAuth integration and an external dependency
  for a task that happens twice.
- **Export only, corrections typed back by hand.** 15 000 cells.

## Consequences

- **A new dependency**, `phpoffice/phpspreadsheet`, which needs `ext-zip` — present everywhere, and
  `composer install` fails loudly if it is not.
- **The workbook is generated from the schema**, so a new property appears in it with no code change.
- **A rejected workbook costs a round trip** with the client. Accepted deliberately: the alternative is a
  half-applied import nobody can reconstruct.
- **`--dryRun=1` prints the change list without writing**, and the list is identical to what a real run
  applies, so the diff can be reviewed before it lands.
- **Sheet protection has no password.** It stops a stray edit in a locked column, not a determined editor —
  and the importer ignores read-only columns anyway, so protection is a courtesy rather than a guarantee.
- **`area` and `volume` are read-only in the workbook** and recomputed from the width, length and height
  properties after those are written.
- Excel's own dropdowns are capped at 255 characters of inline list, so the longest option lists rely on the
  reference sheet instead. Multi-value columns do too — a cell there holds several labels separated by commas.