Download the PHP package avadim/fast-excel-templator without Composer

FastExcelTemplator

🇬🇧 English · 🇷🇺 Русский

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:

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:

Requirements

• PHP >= 7.4 with the zip, json, mbstring, xmlreader and dom extensions.

• Composer pulls the sibling packages automatically. The current 3.x line depends on:

  Package	Constraint
  avadim/fast-excel-reader	^4.4
  avadim/fast-excel-writer	^6.16.1

  avadim/fast-excel-helper is installed transitively through these packages.

How it works

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

• Streaming. Sheet data is read and written one row at a time, without loading the entire sheet into memory. Memory usage also depends on styles, metadata and the number of captured row templates.
• The output is assembled from your template. The library replaces sheet data and retains original archive parts and metadata, including images, notes, conditional formatting and validation rules. Metadata ranges are not automatically remapped when rows are inserted or removed; see the XLSX support matrix.
• Forward-only output. A row cannot be changed after it has been written. Template capture and metadata preservation use separate reads of the template without loading the entire sheet.
• 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

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

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

Step 3 - insert inner table rows

We filled in and inserted rows 7, 8 and 9

Step 4 - Now transfer the remaining rows and save file

You can find code examples in /demo folder

Modification of Spreadsheets

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

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.

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

If you need to remove cells, use the .

removeCell() and removeCells() affect the current row, not the workbook's column structure. Values, styles and horizontal merges move together; an interior removal shrinks a horizontal merge. Removals affecting multi-row merges throw an exception. See the reference semantics.

Use $rowData->setValue('B', null) to clear content and the previous formula while retaining formatting. Zero, false and the empty string remain distinct valid values. Strings starting with = are written as formulas.

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):

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

Each insertion uses a copy of its template. Columns omitted from $cellData retain the template's values and formulas; they do not inherit values from the previous insertion. The template is unchanged. The insertion cycle is independent of foreach over the collection. Missing rows within a valid template range become empty templates.

Capturing templates advances the main read cursor to the requested range end by default. Pass true as the last argument — getRowTemplate(7, true) or getRowTemplates(7, 8, true) — to retain its position. Ranges must be positive and ordered; when dimension is present, its bounds are checked. An empty collection cannot be inserted.

nextTemplate() selects from the same cycle used by insertRow(), independently of foreach, next() and rewind(). Adding or deleting a template restarts the insertion cycle. Public next() retains its legacy cyclic behavior.

Transferring and skipping rows

null processes to EOF, 0 does nothing, and negative values throw an exception. Empty positions in sparse sheets count toward the limit. Until methods do not consume a later existing row beyond the boundary. Repeated EOF operations do not add rows.

Transfer callbacks receive the source row number, target row number and RowTemplate. Return the row to write it, null to omit it, or false to stop. A stopped row has already been read but is not written; a subsequent call resumes with the following row.

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():

Sheets can be processed in any order. Generated data is mapped to each sheet's original XML part even when workbook order differs from sheetN.xml numbering.

Saving the result

Call save() explicitly. The workbook is generated in a temporary file in the destination directory before the completed file replaces the output. Generation or publication failures preserve the previous output. The destination directory must exist and allow file creation.

save($path, false) refuses to overwrite even a file created during generation. This mode uses a hard link and requires filesystem support for it. Saving to the source template path is rejected: choose a separate output file. Destroying the object does not automatically save the workbook.

Checking metadata preservation

Default mode saves with the limitations in the XLSX support matrix and records diagnostics. For example, validation and conditional-formatting ranges retain their template coordinates when rows are inserted.

Strict-mode diagnostics remain available after catching the exception; saving stops at the first detected limitation and preserves the existing destination. See the diagnostic codes and exception example.

Sending the file to the browser

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

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

Limitations

See the XLSX support matrix for metadata preservation, reference semantics and structural limitations. Pass ['strict_metadata' => true] as the third argument of Excel::template() to reject unsupported transformations. metadataDiagnostics() returns diagnostics from the latest save attempt. setValue('A', null) clears cell content and its formula while retaining formatting.

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 output. You cannot edit a row that has already been written. Template capture and metadata preservation use separate reads.
• 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? Ordinary fill()/replace() and row transfer retain original images, notes and relationships. After structural changes, their anchors retain template coordinates; diagnostics report this limitation and strict mode rejects it.

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

Changes in Unreleased.

• Class Excel
• Class SheetTemplate
• Class RowTemplate
• Class RowTemplateCollection

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 :)