Filling an invoice form from an XLSX template: a tour of FastExcelTemplator

A familiar task: you need to generate an invoice, a delivery note, or a statement. Not “a table with some data,” but a proper branded form — with the logo in the corner, merged cells in the header, borders of the right weight, a currency format with thousands separators, and a total formula at the bottom. The accountant already made that form in Excel in five minutes. And now they are being asked for “the same thing, but generated from the database.”

Why the naive approaches stall#

The first thing that comes to mind is to take PhpSpreadsheet and assemble the document cell by cell in code. And that is where the pain begins:

$sheet->getStyle('A1:F1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A5:F5')->getBorders()->getAllBorders()
    ->setBorderStyle(Border::BORDER_THIN);
$sheet->getStyle('E10')->getNumberFormat()->setFormatCode('#,##0.00');
$sheet->mergeCells('A1:D1');
// ...and another hundred lines to reproduce what is already drawn in the file

You are manually rewriting in code the formatting that already exists in a ready file. Any edit to the form (“make the header blue,” “add a VAT column”) turns into hunting for the right lines in PHP. And if the file is large, PhpSpreadsheet loads the whole workbook into an object model and easily runs into the memory limit on tens of thousands of rows.

The second approach — “open a sample file and swap the values inside it” — is possible in PhpSpreadsheet too, but that again means loading the full model into memory, with everything that follows.

The FastExcelTemplator idea: the template is an ordinary XLSX#

FastExcelTemplator turns the task around. The formatting stays where it belongs — in the Excel file itself. A designer or an accountant lays out the form by hand and puts in placeholders like {{COMPANY}}, and the code only substitutes the data.

Technically the library reads the template as a stream with an XML reader and rewrites it on the fly with an XML writer, replacing cell values and inserting data rows along the way. It does not load the sheet into an object model — it walks it top to bottom. Hence the low memory use and the high speed even on large exports.

Everything in this article was verified against version 2.4.0.

Walking through an invoice#

Step 0. Prepare the template#

In Excel, draw the form however you like and leave placeholders:

• in the header — {{COMPANY}}, {{ADDRESS}}, Invoice dated {{DATE}};
• row 7 — the sample line-item row: columns A (number), B (description), C (quantity), D (price), E (amount). Cell E7 already holds the formula =C7*D7;
• at the bottom — {{TOTAL}} for the grand total.

Save the file, with all its styles, logo, and borders, as invoice-tpl.xlsx.

Step 1. Open the template and define the substitutions#

use avadim\FastExcelTemplator\Excel;

$excel = Excel::template('invoice-tpl.xlsx', 'invoice-out.xlsx');
$sheet = $excel->sheet();

// fill() — replaces the WHOLE cell: it fires only when the entire
// value equals the key (the cell is exactly '{{COMPANY}}')
$sheet->fill([
    '{{COMPANY}}' => 'Acme Ltd',
    '{{ADDRESS}}' => '1 Garden Street, Springfield',
]);

// replace() — replaces a SUBSTRING: it fires inside text as well
// (the cell 'Invoice dated {{DATE}}' becomes 'Invoice dated 07/25/2026')
$sheet->replace([
    '{{DATE}}'  => date('m/d/Y'),
]);

The difference between fill() and replace() is the most common beginner's trap, so memorize it right away:

• fill() replaces the value only if the whole cell equals the key. {{COMPANY}} is replaced; Company: {{COMPANY}} is left alone.
• replace() looks for a substring anywhere in the cell's text.

Both substitutions apply to every cell the library writes into the output — both the ones transferred from the template and the ones inserted.

Step 2. Transfer the header#

Copy the top of the form (rows 1–6) from the template into the output as is:

$sheet->transferRowsUntil(6);

Step 3. Repeat the table rows#

Take row 7 as the sample and insert as many rows from it as you have line items:

$rowTemplate = $sheet->getRowTemplate(7);

foreach ($positions as $item) {
    $sheet->insertRow($rowTemplate, [
        'A' => $item['num'],
        'B' => $item['name'],
        'C' => $item['qty'],
        'D' => $item['price'],
        // column E is left alone — the formula from the template lands on its own
    ]);
}

The array keys are column letters. Every inserted row inherits the styles, number formats, and merged cells of the sample row.

A word about the formula. In the template, E7 holds =C7*D7. When the sample row is captured, the library converts the formula into relative (RC) notation, so on insertion it is rebased onto the target row number: the first line item gets =C7*D7, the next one =C8*D8, and so on. You do not need to generate formulas as strings in PHP — putting one into the template once is enough.

Step 4. The total, the rest of the form, and saving#

// Substitute the grand total as an ordinary whole-cell replacement
$sheet->fill(['{{TOTAL}}' => array_sum(array_column($positions, 'total'))]);

// Transfer the remaining template rows (footer, company details, signature)
$sheet->transferRows();

$excel->save();

Done. The output is the very same form the accountant drew, but with data from the database.

What carries over for free#

The main value of the approach is that everything you did not touch stays untouched. The library keeps a copy of the original XLSX and inserts only the rewritten sheets into it, so the output preserves:

• cell styles — fonts, colors, fills, borders, number and date formats;
• merged cells;
• images (the logo) and comments;
• print settings, autofilter, frozen panes.

All of that without a single line of code about formatting.

Not only forms: editing existing files#

The same engine can modify rows on the fly instead of inserting them. The rows() method reads each row, passes it to a callback, and writes the result:

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // skip the heading row
    if ($sourceRowNum === 1) {
        return null;             // null — skip the row
    }
    if ($rowData->getValue('A') === 'STOP') {
        return false;            // false — stop processing
    }
    // change a cell value
    $rowData->setValue('C', $rowData->getValue('C') * 1.2);
    return $rowData;             // return the modified row
});
$excel->save();

The callback also gives you finer operations on the row: appendCell() adds a cell at the end (with the styles of its neighbor), cloneCell('A', 'E') copies a cell into another column, removeCells(['B', 'D']) drops columns you do not need.

Sending the file to the browser#

Besides save() to a file, the finished document can go straight to the user as a download or into the output stream:

$excel->download('Invoice 128.xlsx');  // headers + the file sent to the browser
// output() is an alias of download() with the same behavior
$excel->output('Invoice 128.xlsx');

Honest boundaries#

FastExcelTemplator is a tool for a specific class of tasks, and it is important to know what it does not do:

• XLSX only (Office 2007+). A template in the old binary .xls will not work — the whole mechanism is built around the XLSX structure (a ZIP of XML parts). If your source is .xls, re-save it as .xlsx first.
• Forward-only movement. The library walks the sheet top to bottom and writes the output as a stream. You cannot go back and fix a row that has already been written — that is the price of low memory use.
• The library does not evaluate formulas. It writes the formula as text (and rebases it correctly), while the result itself is computed by Excel when the file is opened. Until then the XLSX holds no cached value for the formula.
• You need a ready template. This is a template engine: to substitute something, the file must contain placeholders, and to repeat a row it needs a sample row. Building a table from scratch entirely in code is a job for the sibling fast-excel-writer.
• It is not about reading data. If you need to pull data out of someone else's Excel rather than generate your own, look at fast-excel-reader.

Who this suits#

FastExcelTemplator is your tool if:

• the document has a fixed corporate look that is easier to draw in Excel than to describe in code: invoices, delivery notes, statements, quotes, contract appendices;
• the table has repeating rows with identical formatting: registers, specifications, schedules;
• you need to generate look-alike files in bulk from one form (thousands of invoices in a loop) on a modest memory budget;
• the formatting is owned by someone who is not a programmer (an accountant, an analyst), and you want edits to the form not to require a code release.

If instead you need to build an arbitrary table entirely from data, take fast-excel-writer; and for reading, fast-excel-reader. Together they cover the whole cycle of working with Excel in PHP.