The Excel equivalent of mail merge: filling a ready XLSX template with data

Word has mail merge: a designer lays out the form, places the fields, and the program substitutes data from a source into them. It is convenient because layout is separated from data. When the same task arrives for Excel — “here is a contract form in xlsx, put the company details into it” — a developer somehow starts assembling the document in code from scratch.

Yet what you need is exactly the Word idea: a template with placeholders plus substitution. For XLSX, FastExcelTemplator provides it. Everything below was verified against version 3.2.0.

The naive path and why it is bad#

The temptation is to open the ready file in PhpSpreadsheet, find the cells by address, and overwrite their values:

$sheet->setCellValue('B3', $company);   // and if the form is edited and B3 moves?
$sheet->setCellValue('B4', $address);   // the cell addresses are hardcoded

There are two downsides. First, you tie yourself to specific addresses: the moment the accountant inserts a row, B3 is no longer the cell you meant. Second, PhpSpreadsheet loads the whole workbook into memory just to do this.

Placeholders remove both problems: you substitute a value not by address but by the {{COMPANY}} marker, wherever it happens to end up.

Two kinds of substitution: fill and replace#

In the template, mark the fields with any convenient marker — {{COMPANY}}, for instance. Then open the template and define the substitutions:

use avadim\FastExcelTemplator\Excel;

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

// fill() — replaces the value of the WHOLE cell
$sheet->fill([
    '{{COMPANY}}' => 'Acme Ltd',
    '{{TAX_ID}}'  => '77-0123456',
]);

// replace() — replaces a SUBSTRING inside the cell's value
$sheet->replace([
    '{{DATE}}' => date('m/d/Y'),
]);

$sheet->transferRows();  // transfer the template rows into the output
$excel->save();

The difference between the two methods is the main beginner's trap, so let's go through it carefully.

• fill() fires only when the cell's value equals the key in full. A cell whose value is {{COMPANY}} gets replaced. But a cell reading Company: {{COMPANY}} will not change through fill(), because its value does not equal the key as a whole.
• replace() looks for a substring anywhere in the text. A cell reading Date: {{DATE}} becomes Date: 07/25/2026 — only the marker is replaced, and the rest of the text stays.

Hence a simple rule: if the field occupies the whole cell, use fill(); if the marker sits inside a phrase, use replace(). When “the placeholder did not get substituted,” nine times out of ten this is why: fill() was applied to a cell where the marker was part of the text.

Where the substitutions apply#

Substitutions defined through fill() and replace() apply to every cell the library writes into the output — there is no need to name addresses or sheets. You declare the dictionary of replacements once, and it fires wherever it meets a marker. That is exactly the separation of layout from data: the form can be reshaped however you like, as long as the markers stay in it.

The replacement value is an ordinary scalar: a string, a number, a date (pre-formatted into a string). The cell's own format (currency, date) comes from the template, so a number is substituted and displayed the way the author of the form intended.

What placeholders do not do#

It is important not to confuse this mechanism with a full template engine like Blade or Twig. Templator's placeholders are value substitution, and nothing more:

• No logic in the template. No {{ if }}, no loops, no expressions inside a cell — a marker is either replaced with a value or it is not.
• Repeating rows are a separate mechanic. If the document has a table whose row count is not known in advance (invoice line items, a list of employees), substitutions alone will not do: that calls for a sample row and repeating it (getRowTemplate() + insertRow()) — a topic for a separate walkthrough.
• XLSX only. A template in the old .xls will not work — re-save it as .xlsx first.
• The library does not evaluate formulas. If the form contains a formula, Excel computes its result when the file is opened.

Who this suits#

Placeholder substitution is the ideal solution when the document is fixed in structure and only the values change: contracts, certificates, warranty letters, title pages, questionnaires. You hand the layout to whoever is good with Excel and keep a short dictionary of replacements in the code. And if the document also has a table of variable length, then row repetition joins fill()/replace() — worth reading about separately.