README

FastExcelTemplator is a part of the FastExcelPhp Project which consists of

FastExcelWriter - to create Excel spreadsheets
FastExcelReader - to read Excel spreadsheets
FastExcelTemplator - to generate Excel spreadsheets from XLSX templates
FastExcelLaravel - special Laravel edition

Introduction

FastExcelTemplator can generate Excel-compatible spreadsheets in XLSX format (Office 2007+) from XLSX templates, very quickly and with minimal memory usage. This library is designed to be lightweight, super-fast and requires minimal memory usage.

Features

Supports XLSX format only (Office 2007+) with multiple worksheets
Transfers from templates to target spreadsheets styles, images, notes
Replaces the entire cell values and substrings
You can use any row from a template as row template to insert and replace a row with new values
The library can read styling options of cells - formatting patterns, colors, borders, fonts, etc.

Which library do I need?

"Working with Excel in PHP" is really three different tasks, each with its own tool in the FastExcelPhp family:

Your task	Use
Read data from an existing file	FastExcelReader
Build a spreadsheet from scratch in code	FastExcelWriter
Fill a ready-made XLSX form with data	FastExcelTemplator (this library)

Reach for FastExcelTemplator when you already have a designed XLSX document (an invoice, act, contract, report) and only need to put data into it — keeping the logo, borders, number formats and formulas that are already in the file. If you catch yourself re-creating in code the formatting that already exists in a file, you want the template approach.

Installation

Use composer to install FastExcelTemplator into your project:

composer require avadim/fast-excel-templator

Requirements

PHP >= 7.4 with the zip, json, mbstring, xmlreader and dom extensions.
Composer pulls the sibling packages automatically. The current 2.x line depends on:

Package Constraint

avadim/fast-excel-reader ^4.0

avadim/fast-excel-writer ^6.15

avadim/fast-excel-helper ^1.3

Package	Constraint
`avadim/fast-excel-reader`	`^4.0`
`avadim/fast-excel-writer`	`^6.15`
`avadim/fast-excel-helper`	`^1.3`

How it works

Understanding the design explains both what the library does effortlessly and where its limits are.

Streaming, not an object model. FastExcelTemplator reads the template through an XML reader and re-emits it through an XML writer, one row at a time, from top to bottom. It never loads the whole sheet into memory, so memory usage stays roughly flat regardless of how many rows you insert.
The output is a copy of your template. On save, the library takes the original template file and splices only the re-written sheets back into it. Everything you did not touch — images, notes, drawings, merged cells, print setup, frozen panes, autofilters — is carried over untouched. That is why formatting is preserved "for free": it is literally the same file.
Forward-only. Because reading and writing advance together in a single pass, you cannot go back and change a row that has already been written. You walk the template once.
Formulas are transferred, not evaluated. The library writes the formula text and does not compute its result — Excel does that when the file is opened. When a captured row template is re-inserted at another row, its formulas are re-based automatically (A1 references shift to the target row), so a =C7*D7 in the template row becomes =C8*D8, =C9*D9, and so on.

Templates Usage

Example of template

From this template you can get a file like this

Step 1 - open template and set replacements

// Open template and set output file
$excel = Excel::template($tpl, $out);
// Get the first sheet
$sheet = $excel->sheet();

$fillData = [
    '{{COMPANY}}' => 'Comp Stock Shop',
    '{{ADDRESS}}' => '123 ABC Street',
    '{{CITY}}' => 'Peace City, TN',
];

// Set replacements of entire cell values for the sheet
// If the value is '{{COMPANY}}', then this value will be replaced,
// but if the value 'Company Name {{COMPANY}}', then this value will not be replaced 
$sheet->fill($fillData);

// Set replacements of any occurring substrings
// If the value is '{{DATE}}' or 'Date: {{DATE}}', then substring '{{DATE}}' will be replaced,
$replaceData = ['{{BULK_QTY}}' => 12, '{{DATE}}' => date('m/d/Y')];
$sheet->replace($replaceData);

fill() vs replace() — the most common gotcha:

fill() replaces the value only if the whole cell equals the key. A cell containing {{COMPANY}} is replaced; a cell containing Company Name {{COMPANY}} is not.
replace() replaces the key as a substring, anywhere inside the cell text.

Both maps apply to every cell the library writes to the output — transferred rows and inserted rows alike. Step 2 - transfer the top of the sheet and the table headers from the template to the output file

// Transfer rows 1-6 from templates to output file
$sheet->transferRowsUntil(6);

There are 6 rows read from template, the output file also contains 6 lines

Step 3 - insert inner table rows

// Get the row number 7 as a template and go to the next row in the template
$rowTemplate = $sheet->getRowTemplate(7);

// Fill row template and insert it into the output
foreach ($allData as $record) {
    $rowData = [
        // In the column A wil be written value from field 'number'
        'A' => $record['number'],
        // In the column B wil be written value from field 'description'
        'B' => $record['description'],
        // And so on...
        'C' => $record['price1'],
        'D' => $record['price2'],
    ];
    $sheet->insertRow($rowTemplate, $rowData);
}

We filled in and inserted rows 7, 8 and 9

Step 4 - Now transfer the remaining rows and save file

// Method transferRows() without arguments transfers remaining rows from the template to the output file 
$sheet->transferRows();

// ...
// Save new file
$excel->save();

You can find code examples in /demo folder

Modification of Spreadsheets

Use the rows() method to read rows, modify them using callback, and write them to the output file.

use avadim\FastExcelTemplator\Excel;

$excel = Excel::template($tpl, $out);
$sheet = $excel->sheet();

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // $rowData is an instance of the RowTemplate

    // skip the first row
    if ($sourceRowNum === 1) {
        return null;
    }
    // $rowData
    // if a value of cell 'A' then break
    if ($rowData->getValue('A') > 5) {
        return false;
    }
    // write value to cell 'B'; if the cell 'B' does not exist, it will be created
    $rowData->setValue('B', $rowData->getValue('A') * 2);
    // return modified row
    return $rowData;
});
$excel->save();

You can add one or more cells to the end of a row in the callback function. The styles and value from the source cell will be copied to the new cell. If you do not explicitly specify a source cell, the last cell in the row will be used as the source.

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the last cell of the row and add them to the end of the row and assign it the value 123
    $rowData->appendCell()->withValue(123);

    return $rowData;
});

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the cell 'B' and add them to the end
    $rowData->appendCell('B');

    // Clone the last cell three times
    $rowData->appendCell(null, 3)->withValues([111, 222, 333]);

    return $rowData;
});

Also, you can clone any cell (with styles and value) to other cell

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the cell 'A' to the cell 'E' and assign it the SUM()
    $rowData->cloneCell('A', 'E')
        ->withValues(['=SUM(A' . $targetRowNum . ':E' . $targetRowNum . ')']);

    return $rowData;
});

If you need to remove cells, use the removeCells().

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the cell 'A' to the cell 'E' and assign it the SUM()
    $rowData->removeCells(['B', 'D']);

    return $rowData;
});

Repeating rows and alternating styles

getRowTemplate($n) grabs a single row as a reusable template. To repeat a block of several rows — or to alternate row styles ("zebra") — capture a range with getRowTemplates($min, $max):

// Capture two differently styled rows (7 and 8) as templates
$rowTemplates = $sheet->getRowTemplates(7, 8);

foreach ($allData as $record) {
    // insertRow() cycles through the templates: 7, 8, 7, 8, ...
    $sheet->insertRow($rowTemplates, ['A' => $record['number']]);
}

Both methods return a RowTemplateCollection; insertRow() takes the next template from it on each call and wraps around at the end.

Working with multiple sheets

A template may contain several worksheets. Address a sheet by name with sheet($name), or iterate over all of them with sheets():

$excel = Excel::template($tpl, $out);

foreach ($excel->sheets() as $sheet) {
    $sheet->fill(['{{TITLE}}' => 'Report']);
    $sheet->transferRows();
}

$excel->save();

Sending the file to the browser

Besides save(), you can stream the generated file straight to the client with the correct HTTP headers:

$excel->download('invoice.xlsx'); // sends download headers and outputs the file
$excel->output('invoice.xlsx');   // output() is an alias of download()

download() sends one file per response, so to hand over many documents at once, save them and pack them into an archive yourself.

Limitations

FastExcelTemplator is built for one job — filling XLSX templates — and deliberately does not do everything:

XLSX only (Office 2007+). The old binary .xls cannot be used as a template; convert it to .xlsx first.
Forward-only. You process the template in a single top-to-bottom pass and cannot edit a row that has already been written.
Formulas are not calculated. The library writes formula text (and re-bases it); the result is computed by Excel when the file is opened.
Placeholders are value substitution only — there is no in-cell logic (no if/loops). A table of unknown length is handled with row templates, not placeholders.
Not a reader or a from-scratch writer. To read data from a file use FastExcelReader; to build a spreadsheet entirely from code use FastExcelWriter.

FAQ

My placeholder was not replaced. Check fill() vs replace(). fill() only replaces a cell whose value equals the key exactly; if the marker sits inside other text (e.g. Date: {{DATE}}), use replace(), which matches substrings.

A formula shows up as text, or the cell is empty until I open the file. The library writes formulas but does not evaluate them — Excel computes the result when the file is opened. Make sure the value is a real formula starting with =.

How do I keep the logo / images / notes from the template? They are preserved automatically. The output is a copy of the template with only the sheet data re-written, so anything you do not touch stays in place — just fill()/replace() and transfer the rows.

I get Allowed memory size exhausted on a large file. The library streams, so the bottleneck is usually your data source. Pull rows from the database with a cursor/generator instead of loading them all into an array (fetchAll()) before the insert loop.

Can I use an old .xls file as a template? No. Only XLSX (Office 2007+) is supported. Convert the .xls to .xlsx first (e.g. in Excel or LibreOffice) and use the result as the template.

List of Functions

Do you like FastExcelTemplator?

if you find this package useful you can support and donate to me for a cup of coffee:

USDT (TRC20) TSsUFvJehQBJCKeYgNNR1cpswY6JZnbZK7
USDT (ERC20) 0x5244519D65035aF868a010C2f68a086F473FC82b
ETH 0x5244519D65035aF868a010C2f68a086F473FC82b

Or just give me a star on GitHub :)

avadim / fast-excel-templator

Maintainers

Package info

Statistics

Security