README

Spreadsheets described as a layout instead of written cell by cell.

A template is a list of blocks — a table, a heading, a logo, a key/value panel — each placed absolutely, relative to another block, or flowing down the page. Styles are named and inheritable, so tableHeader means one thing across every sheet and a colour change is one override rather than a search through render code.

The point of describing a report this way is that the description is data: it can be stored, versioned, shipped as JSON and recoloured at runtime, without anybody deploying a new exporter.

The same description reads files back. A bind links a path in the data to a cell, and the link runs both ways, so a template that renders a report also imports it — and a template with only a read section imports a file somebody else's system wrote. See Importing.

No framework. The engine never reads a file, a config value or a container it was not handed — whatever it needs, the caller gives it. It works in plain PHP, and a Laravel application gets a service provider it never has to register.

composer require reynotech/sheet-layout

Requires PHP 8.2+, phpoffice/phpspreadsheet and symfony/expression-language.

Using it

use ReynoTECH\SheetLayout\SheetLayout;

$spreadsheet = SheetLayout::fromFile(__DIR__.'/templates/report.json')
    ->withStyles(['tableHeader' => ['fill' => $headerColor]])
    ->withResources(['logo' => $logoPath])
    ->render([
        'project' => ['name' => 'Sample Project', 'reference' => 'REF-0001'],
        'lines' => $lines,
        'total' => $total,
    ]);

render() returns a PhpSpreadsheet Spreadsheet, so anything that library can do with a workbook still applies. There is also save($path, $data), toString($data) for the bytes, and renderInto($worksheet, $data) for one workbook with a sheet per record.

In Laravel, type-hint SpreadsheetTemplateCompiler and the container builds it.

Backends

What a template says is separate from what writes the file. Block renderers talk to a SheetWriter; a WorkbookWriter turns those calls into an xlsx. Two ship with the package:

Backend	Use it for
`PhpSpreadsheetWorkbook` (default)	Everything. `render()` always uses it, because it returns a `Spreadsheet`.
`XlsWriterWorkbook`	Large exports. Needs `pecl install xlswriter`.

use ReynoTECH\SheetLayout\Writers\XlsWriterWorkbook;

SheetLayout::fromFile(__DIR__.'/templates/report.json')
    ->using(fn () => new XlsWriterWorkbook)
    ->save($path, $data);          // or ->toString($data)

using() takes a factory, because a workbook writes one file and is then spent.

Both backends bind values the same way — "15%" is a percentage, "2026-01-15" a date, but text becomes a date only when it has a four-digit year, so a code like "01.1" or "1-2" stays text — and the test suite renders one template through both and compares the files cell by cell. On 50,000 table rows, XlsWriterWorkbook wrote the file about 12× faster with a third of the memory.

What `XlsWriterWorkbook` cannot write

libxlsxwriter, or the extension's binding of it, has no way to say these:

Feature	Template key	Instead
SVG images	`image` block whose resource is an `.svg`	Export the logo as PNG.
Page centering	`page.horizontalCentered`, `page.verticalCentered`	Leave them out, or use the default backend.
Page views other than normal	`page.view`: `pageLayout`, `pageBreakPreview`	Leave `view` out (it is only how Excel opens the sheet).
A different first-page header or footer	`page.headerFooter.first`, `differentFirst`	Use only `default`.
Different odd and even page headers or footers	`page.headerFooter.even`, `differentOddEven`	Use only `default`.
Resized header or footer images	`width` / `height` on a header/footer `image` item	Resize the image file itself.

A template that asks for any of these fails with UnsupportedFeatureException, whose message names the feature, rather than producing a file without it. The same template still renders with PhpSpreadsheetWorkbook.

Two things are written differently without changing what the reader sees:

An error value in the data ("#N/A", "#DIV/0!") becomes a formula that evaluates to that error, since libxlsxwriter cannot store an error cell.
A formula's cached result is not calculated; Excel computes it on open.

A backend of your own implements WorkbookWriter and SheetWriter; the styles it receives are already resolved into the template's vocabulary (['fill' => ['color' => '002164']]), never into another library's constants.

A template

{
  "version": 1,
  "styles": {
    "title": { "font": { "bold": true, "size": 16, "color": "002164" } },
    "tableHeader": { "font": { "bold": true, "color": "FFFFFF" }, "fill": "002164" },
    "money": { "numberFormat": "money", "alignment": { "horizontal": "right" } },
    "moneyTotal": { "extends": ["money"], "font": { "bold": true } }
  },
  "sheets": [
    {
      "name": { "bind": "project.name", "default": "Report" },
      "blocks": [
        { "type": "text", "at": "A1", "bind": "project.name", "style": "title" },
        {
          "type": "keyValue",
          "at": "A3",
          "items": [
            { "label": "Reference:", "bind": "project.reference" },
            { "label": "Value:", "bind": "project.value", "style": "money" }
          ]
        },
        {
          "type": "table",
          "id": "lines",
          "at": "A7",
          "source": "lines",
          "as": "line",
          "headerStyle": "tableHeader",
          "columns": [
            { "key": "code", "header": "Code", "bind": "line.code", "width": 12 },
            { "key": "description", "header": "Description", "bind": "line.description", "width": 40 },
            { "key": "amount", "header": "Amount", "bind": "line.amount", "style": "money" }
          ]
        },
        { "type": "text", "after": "@lines", "expr": "'Total: ' ~ total", "style": "moneyTotal" }
      ]
    }
  ]
}

Blocks

Type	What it draws
`text`	One value, optionally merged across a range with `to`.
`keyValue`	A label/value panel — `items`, each with its own style.
`table`	A header row and a row per record, with column widths, per-column styles, `cellRules` and native Excel tables.
`image`	A file the caller named through `withResources()`.
`range`	A styled rectangle: rules, fills, merged cells.
`spacer` / `pageBreak`	Vertical room, and where a printed page ends.
`repeat`	Its child blocks once per record.

Placing a block

"at": "A14" — an exact cell.
"after": "@lines" — under a block that has an id, wherever that one ended up.
Neither — down the page from where the last block finished, plus spacingBefore.

Any block with an id can be referenced later as @id.lastRow, @id.lastColumn, @id.firstRow, @id.firstColumn or @id.headerRow. That is how a heading spans exactly as wide as the table underneath it, whatever the data turned out to be.

Values

Every place a value is expected takes exactly one of:

"value": "literal"
"bind": "project.reference" — a dotted path into the data.
"expr": "upper(line.code ?? '')" — a Symfony expression, validated against the data's top-level keys when the template is checked, so a typo is an error with a path rather than a blank cell.

"when": "project.logo != null" on a block skips it entirely.

Styles

Named, and extends composes them — a total is a money cell that is also bold, not a second copy of the number format. Circular inheritance is refused by name.

numberFormat takes a name — date, dateUs, monthYear, datetime, number, integer, money, percentage, text — so a typo is an error instead of a column of ####. No fixed vocabulary covers every report, so { "code": "#,##0.0000" } states one directly.

Tables

source is where the records come from; as is what a column calls each one. item and index always work, so as is about the template reading like the report it produces.

dynamicColumns expands one column definition into many from the data — the case where the columns themselves are not known until the report runs.

Importing

$result = SheetLayout::fromFile(__DIR__.'/templates/report.json')->read($uploadedPath);

$result->isValid();  // false
$result->data();     // ['project' => ['name' => 'Sample Project', …], 'lines' => [[…], …]]
$result->errors();   // [ImportError: Report!C11 lines[3].amount: Expected a number, got "N/A".]

read() takes a path or a Spreadsheet. Problems with the file are collected, all of them, each with its sheet, cell and the path its value was going to — the person who uploaded it needs the whole list, not one error per attempt. The data is always complete in shape: a field that could not be read is its default, a row with a bad cell is still a row. Problems with the template still throw TemplateException, with their path.

What a template already says

A template written to render is read back without writing anything else:

In the template	Reads as	Found by
`text` with `bind` and `at`	that value	the cell `at`
`keyValue` item with `bind`	that value	the cell right of its label
`table` with `source` and columns that `bind` into the record	a list of records	its Excel table name; else the defined name `<table>_range`; else its first header
a style's `numberFormat`	the cast — `money` reads as a number, `date` as `YYYY-MM-DD`

A column's bind: "line.amount" under as: "line" is the record's amount. Both writers leave a defined name over every table, so a table is found exactly even by a reader that cannot see Excel tables, and even after someone inserted rows above it.

Some things cannot be followed backwards, and are skipped rather than guessed at: a value computed by expr, a block placed by the flow of the page (its row came from the data, and there is no data yet), dynamicColumns, repeat. A block, keyValue item or column can say more with its own "read" — { "below": "Notes" } — or opt out with "read": false. A block with when is optional: its absence is not an error.

readSchema() returns what will be read and, under skipped, every part of the template that will not be, with the reason. Check it once; the derived half should never be a guess.

A `read` section

For a file the template never wrote, or to change what is derived:

{
  "version": 1,
  "read": {
    "strict": false,
    "fields": {
      "title": "A1",
      "account": { "rightOf": "Account:", "required": true },
      "issued": { "below": "Issued", "cast": "date", "format": "d/m/Y" },
      "lines": {
        "type": "table",
        "find": "Reference",
        "stopAt": "Total",
        "columns": {
          "reference": { "header": "Reference", "required": true },
          "amount": { "header": "Amount", "cast": "money" },
          "contact.name": "Contact"
        }
      },
      "signer": { "rightOf": "Signed by:", "after": "@lines" }
    }
  }
}

Each key under fields is where the value goes — the dotted path a bind would read. A column's key is the same inside the record. fields reads the first sheet; "sheets": [{ "sheet": "Totals", "fields": … }] reads several, by name or position.

On a template that also renders, a field here is merged over the derived one of the same path: an anchor written here replaces the derived ones, columns merge by key, and false drops a field or a column.

Key	Meaning
`at`	A cell: the value itself, or a table's header cell.
`rightOf`, `below`	The cell next to, or under, the one whose text matches (`offset` moves further).
`find`	A table whose header row contains this text.
`table`, `definedName`	A table bounded by an Excel table or a named range; the first row is the header.
`after`	Look only below where another field — or table block id — ended.
`cast`, `format`	`text`, `integer`, `number`, `boolean`, `date`, `datetime`, or a number format name. `format` parses dates typed as text.
`required`, `optional`, `default`	A blank value is an error; a missing anchor is not; what a blank becomes.
`until`, `stopAt`, `limit`	Where an open table ends: the first blank row (default) or the end of the sheet; a row with this text; a number of records.
`strict`	A header the template does not name, or a column it names that is missing, is an error.

Several anchors are fallbacks, tried in the order above. Text matches ignore case and surrounding whitespace, so "Amount " is the Amount column. Columns are found by header, so a file whose columns were reordered, or that has extra ones, still reads. An open table's columns are the unbroken run of headers around the one it was found by.

Backends

Reader	Use it for
`PhpSpreadsheetWorkbookReader` (default)	Everything: xlsx, xls, ods, csv, and Excel tables. Loads one sheet, values only.
`XlsWriterWorkbookReader`	Large xlsx. Streams a row at a time. Needs `pecl install xlswriter`.

use ReynoTECH\SheetLayout\Readers\XlsWriterWorkbookReader;

$rows = SheetLayout::fromFile(__DIR__.'/templates/report.json')
    ->readUsing(fn () => new XlsWriterWorkbookReader)
    ->readRows($uploadedPath, 'lines');

foreach ($rows as $row) {
    // $row->data, $row->errors, $row->row — one record, as soon as it is read
}

$rest = $rows->getReturn(); // every other field, and their errors

readRows() hands one table over record by record, so its records never exist as one array. The engine reads each sheet in a single forward pass and stops as soon as every field is found, so nothing asks a backend for a row twice. On 50,000 table rows, XlsWriterWorkbookReader read the file about 3× faster than the default, and readRows() through it peaked at 4 MB.

XlsWriterWorkbookReader cannot see Excel table objects: a table anchor falls back to the <table>_range defined name, then to find, and fails with UnsupportedFeatureException when there is neither. It reads only xlsx. Otherwise both readers give the same raw values — text stays text even when it looks like a number, booleans stay booleans, dates are Excel serial numbers until a cast says otherwise — and the test suite reads the same files through both and compares. A reader of your own implements WorkbookReader and SheetReader.

Errors

Every failure names its path in the template: sheets[0].blocks[3].columns[2].key. A layout is authored by hand, so the error has to say which hand-written line is wrong.

Template data may only contain arrays and scalars. Passing an Eloquent model or an arbitrary object is refused, because a template that can walk a model can reach anything the model can.

Tests

composer install
composer test

The tests render real workbooks and read the cells back. The failure this engine exists to prevent is a spreadsheet that opens and is wrong, and no assertion on an intermediate object catches that. Importing is tested the same way: data is rendered through every writer, saved, read back through every reader, and has to come back identical.

Releasing

Versions are git tags; Composer reads them. From a clean working tree:

composer release:patch     # or release:minor, release:major

bin/bump-version.sh runs the tests and the style check, works out the next vX.Y.Z from the latest tag, moves the ## Unreleased section of CHANGELOG.md under that version with today's date, commits, tags and pushes. --dry-run shows what it would do, --no-push keeps the commit and tag local, --skip-checks skips the tests. Composer passes extra arguments after --: composer release:minor -- --dry-run.

reynotech / sheet-layout

Package info

Security

Statistics

README

Using it

Backends

What `XlsWriterWorkbook` cannot write

A template

Blocks

Placing a block

Values

Styles

Tables

Importing

What a template already says

A `read` section

Backends

Errors

Tests

Releasing

reynotech / sheet-layout

Package info

Security

Statistics

README

Using it

Backends

What XlsWriterWorkbook cannot write

A template

Blocks

Placing a block

Values

Styles

Tables

Importing

What a template already says

A read section

Backends

Errors

Tests

Releasing

What `XlsWriterWorkbook` cannot write

A `read` section