Hypaxel for PHP
A pure-PHP XLSX writer with constant memory: stream Excel downloads from PDO, Doctrine or Laravel cursors to Symfony, Laravel or PSR-7.
v0.3.0 unreleasedLast updated View as Markdown
PHP Excel exports often run into memory_limit because the whole workbook is built in memory first. Hypaxel for PHP is an XLSX writer that streams instead: each row is serialized to XML the moment you write it and compressed straight into a small buffer that is flushed to the destination. Memory stays at about 200 KiB of PHP heap per export, for 20,000 rows and for 400,000 alike, and a download can start while your query is still running. It is pure PHP, installed with Composer alone: no extensions to build, only PHP 8.2+ (64-bit) and the bundled zlib extension.
Hypaxel for PHP creates Excel files; it does not read them yet. Streaming readers are available for .NET, Rust, Python, Go and Java, and PHP follows when there is demand.
- Package
- vensas/hypaxel
- Requires
- PHP 8.2+ (64-bit), ext-zlib
- Engine
- Native PHP, no runtime dependencies
- Web
- Xlsx::download(), Symfony StreamedResponse, Laravel streamDownload()
- Registry
- Packagist (not published yet)
- License
- Apache-2.0
Install#
composer require vensas/hypaxelNot published yetvensas/hypaxel is not on Packagist yet. The command above is what installation will look like once it is.
The package is not published yet. Until then, build it from source: see Building from source. It requires PHP 8.2 or later (64-bit) with ext-zlib and has no runtime dependencies.
Quick start#
use Vensas\Hypaxel\ColumnSpec;
use Vensas\Hypaxel\Xlsx;
$orders = [
['id' => 1, 'customer' => 'Ada', 'amount' => 1234.5, 'created' => new DateTimeImmutable()],
['id' => 2, 'customer' => 'Linus', 'amount' => 99.99, 'created' => new DateTimeImmutable()],
];
Xlsx::writeFile('orders.xlsx', $orders);
$bytes = Xlsx::toString($orders, sheetName: 'Orders', columns: [
'id',
new ColumnSpec('customer', header: 'Customer', width: 30),
new ColumnSpec('amount', numberFormat: '#,##0.00'),
]);Records (associative arrays or objects) get a styled, frozen header row and an auto filter. columns selects and orders the keys; without it the keys of the first record are used. Lists are written as plain rows, and columns then supplies the header texts:
Xlsx::writeFile('matrix.xlsx', [[1, 'a'], [2, 'b']], columns: ['Id', 'Label']);The data can be any iterable. Pass a generator or a database cursor and rows are fetched while the file is being written:
$rows = $pdo->query('SELECT id, customer, amount FROM orders', PDO::FETCH_ASSOC);
Xlsx::writeFile('orders.xlsx', $rows);Streaming HTTP responses#
Plain PHP#
use Vensas\Hypaxel\Xlsx;
Xlsx::download('orders.xlsx', $pdo->query('SELECT id, customer, amount FROM orders', PDO::FETCH_ASSOC));download() sends Content-Type and Content-Disposition and streams the workbook to the client.
Symfony#
use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Vensas\Hypaxel\Xlsx;
#[Route('/orders.xlsx')]
public function export(Connection $db): StreamedResponse
{
$orders = $db->iterateAssociative('SELECT id, customer, amount FROM orders');
return new StreamedResponse(static fn () => Xlsx::output($orders), 200, Xlsx::headers('orders.xlsx'));
}Laravel#
use Illuminate\Support\Facades\DB;
use Vensas\Hypaxel\Xlsx;
Route::get('/orders.xlsx', fn () => response()->streamDownload(
static fn () => Xlsx::output(DB::table('orders')->select('id', 'customer', 'amount')->orderBy('id')->cursor()),
'orders.xlsx',
['Content-Type' => Xlsx::CONTENT_TYPE],
));PSR-7 and PSR-15#
For Slim, Mezzio and similar frameworks, write to the body stream through a callback. No PSR package is needed by Hypaxel itself.
use Vensas\Hypaxel\Writer;
use Vensas\Hypaxel\Xlsx;
$body = $response->getBody();
$writer = Writer::toCallback($body->write(...));
$writer->writeWorksheet('Orders', $orders);
$writer->finish();
return $response->withHeader('Content-Type', Xlsx::CONTENT_TYPE);Pull-based bodies#
For ReactPHP, Amp, Swoole and Symfony’s iterable StreamedResponse, Xlsx::stream($orders) is a generator that yields the file in chunks while it consumes its source lazily.
Export helpers#
| Helper | Destination |
|---|---|
Xlsx::writeFile($path, $data, ...) | a file |
Xlsx::writeStream($resource, $data, ...) | any writable stream resource |
Xlsx::toString($data, ...) | a string (memory grows with the file) |
Xlsx::stream($data, ...) | a generator of chunks |
Xlsx::output($data, ...) | the response body, without headers |
Xlsx::download($filename, $data, ...) | the response body, with headers |
Xlsx::headers($filename), Xlsx::contentDisposition($filename), Xlsx::CONTENT_TYPE | header values for frameworks |
All of them take sheetName, columns, header, autoFilter and options as named arguments.
Records and objects#
Arrays, ArrayAccess objects and public properties are read by key. Use a ColumnSpec with a value callable for getters:
new ColumnSpec('total', value: fn (Order $o) => $o->total())Inside a Writer, writeWorksheet($name, $data, columns:, header:, autoFilter:) writes a whole worksheet from rows or records with a header row, and Worksheet::writeRecords($records, $columns) appends one row per array or object.
Full control with Writer#
use Vensas\Hypaxel\Formula;
use Vensas\Hypaxel\HorizontalAlignment;
use Vensas\Hypaxel\SheetOptions;
use Vensas\Hypaxel\Style;
use Vensas\Hypaxel\Styled;
use Vensas\Hypaxel\Writer;
$writer = Writer::toFile('report.xlsx');
$header = $writer->addStyle(Style::create()->bold()->fontColor('#FFFFFF')->fill('#305496')->align(HorizontalAlignment::Center));
$money = $writer->addStyle(Style::create()->numberFormat('#,##0.00'));
$sheet = $writer->addWorksheet('Sales', new SheetOptions(freezeRows: 1, columnWidths: [20, 12, 14]));
$sheet->writeRow(['Product', 'Date', 'Revenue'], $header);
$sheet->writeRows([
['Widget', new DateTimeImmutable('2024-05-01'), new Styled(1234.5, $money)],
['Gadget', new DateTimeImmutable('2024-05-02'), new Styled(99.99, $money)],
]);
$sheet->writeRow(['Total', null, new Styled(new Formula('SUM(C2:C3)'), $money)]);
$writer->finish();Worksheets are written one at a time: adding a worksheet completes the previous one. Always call finish(); it writes the parts that make the file a workbook.
| Constructor | Destination |
|---|---|
Writer::toFile($path) | creates or truncates a file; finish() closes it |
Writer::toStream($resource) | any writable stream resource (php://output, php://memory, sockets, …); stays open |
Writer::toCallback($callable) | every chunk goes to callable(string): mixed |
Writer::toOutput() | echo plus flush() per chunk |
Writer::inMemory() | kept in memory; finish() returns the bytes, drain() takes what exists so far |
new Writer($sink) | your own Vensas\Hypaxel\Sink\Sink (write(string), close()) |
The full method tables for Writer and Worksheet are in the API reference.
Cell values#
| PHP value | Excel cell |
|---|---|
string | text (inline, or shared with sharedStrings) |
int, float | number. NAN and INF become #NUM! |
bool | boolean |
null | empty; with a style, an empty cell carrying the format |
DateTimeInterface | date serial, see Dates |
DateInterval | duration in days, format [h]:mm:ss |
new Formula('SUM(A1:A9)') | formula; a leading = is optional |
new Styled($value, $style) | any of the above with its own format |
backed enum, unit enum, Stringable | the enum’s value, the case name, the string |
| arrays, other objects | UnsupportedValueException |
Styles#
use Vensas\Hypaxel\{Alignment, Border, BorderLine, BorderStyle, Font, HorizontalAlignment, NumberFormat, Style, VerticalAlignment};
$fluent = Style::create()
->fontName('Arial')->fontSize(12)->bold()->italic()->underline()->strikethrough()->fontColor('#C00000')
->fill('#FFF2CC')
->border(BorderStyle::Thin, '#808080')
->align(HorizontalAlignment::Right)->valign(VerticalAlignment::Center)->wrap()->indent(1)->rotation(45)
->numberFormat('0.00%');
$explicit = new Style(
numberFormat: NumberFormat::THOUSANDS2,
font: new Font(name: 'Arial', size: 12, bold: true, color: '#C00000'),
fill: '#FFF2CC',
border: new Border(bottom: new BorderLine(BorderStyle::Double, '#000000')),
alignment: new Alignment(wrapText: true),
);Styles are immutable value objects; every modifier returns a new instance. Colors are #RRGGBB or #AARRGGBB strings or Color objects. BorderStyle has the 13 line styles of the format. NumberFormat has constants for common format codes; any Excel format code works.
Worksheet options#
new SheetOptions(
freezeRows: 1, // rows pinned at the top
freezeColumns: 0, // columns pinned at the left
columnWidths: [20, null, 14], // from column A; null keeps the default
columns: [5 => new Column(width: 30, style: $money, hidden: true)], // by 1-based column number
showGridLines: true,
hidden: false,
tabColor: '#305496',
rowOutlineLevels: 0, // deepest row outline level used, see Row groups
outlineSummaryAbove: false, // group buttons on the summary row above the group
autoFit: false, // true or new AutoFit(...), see Auto-fit columns
);Tables, row grouping and auto-fit#
Row groups#
Rows started with beginRow() can carry an outline level (1-7), which Excel shows as collapsible groups. Excel sizes the outline bar before it reads any row, so declare the deepest level when adding the worksheet; a deeper row throws an InvalidArgumentException.
$sheet = $writer->addWorksheet('Regions', new SheetOptions(rowOutlineLevels: 2, outlineSummaryAbove: true));
$sheet->writeRow(['North', 300]);
$sheet->beginRow(outlineLevel: 1)->writeRow(['Store A', 120]);
$sheet->beginRow(outlineLevel: 2, hidden: true)->writeRow(['Till 1', 60]);
$sheet->beginRow(outlineLevel: 1, collapsed: true)->writeRow(['Store B', 180]);A row started with beginRow() that has no cells yet is filled by the next writeRow(), writeRows() or writeRecords(); once it has cells, those start a new row as before. hidden hides the row, collapsed marks the summary row of a collapsed group (hide the group’s rows as well).
Excel tables#
use Vensas\Hypaxel\{Table, TableStyle};
$sheet->writeRow(['Product', 'Units']);
$sheet->writeRows([['Widget', 3], ['Gadget', 5]]);
$sheet->addTable(new Table(1, 1, $sheet->row(), 2, name: 'Orders', columns: ['Product', 'Units'], style: TableStyle::medium(9)));A table gives a range a name for structured references (=SUM(Orders[Units])), filter buttons and banded formatting. With a header row (the default), columns must repeat the header texts exactly as written: Excel identifies table columns by them and repairs the file otherwise. Without one (headerRow: false) the columns are named Column1, Column2, … unless given.
| Parameter | Default | Meaning |
|---|---|---|
name | Table1, Table2, … | Unique in the workbook (case-insensitive); letters, digits, _, ., no cell references such as A1 or R1C1 |
columns | Column names: unique, 1-255 characters, one per column of the range | |
style | TableStyle::default() (Medium 2) | TableStyle::none(), light(1..21), medium(1..28), dark(1..11) |
headerRow, autoFilter, rowStripes | true | Header row, its filter buttons, banded rows |
columnStripes, highlightFirstColumn, highlightLastColumn | false | Banded columns, emphasized first/last column |
Tables may not overlap each other, merged cells or the worksheet’s autoFilter() range: such calls throw an OverlappingRangeException. Invalid names throw InvalidTableNameException, taken ones DuplicateTableNameException. Definitions are kept in memory until the worksheet completes.
Auto-fit columns#
$sheet = $writer->addWorksheet('Report', new SheetOptions(autoFit: true, columnWidths: [null, 12]));Column widths come before the cells in the file, so with autoFit the worksheet’s XML is held back until the sample has been written: new AutoFit(sampleRows: 1000, maxWidth: 255.0) by default, and at most 8 MiB of XML. The widths are then estimated from the sampled cells and streaming continues; rows after the sample are not measured. Text (its longest line), numbers, booleans and dates are measured with their number format, font size and boldness; formulas and empty cells are not. Columns with an explicit width keep it. With autoFit off, nothing is held back.
Options#
$writer = Writer::toFile('report.xlsx', new Options(sharedStrings: true));| Option | Default | Meaning |
|---|---|---|
compressionLevel | 1 | Deflate level 0-9 |
sharedStrings | false | Shared string table: smaller files for repeated text, memory grows with the number of distinct strings |
flushThreshold | 65536 | Compressed bytes buffered before a chunk goes to the destination (at least 1024) |
cellReferences | false | Write a reference such as B7 on every cell |
date1904 | false | Use the 1904 date system |
Dates#
Excel has no time zones. A DateTimeInterface is written as the wall-clock time of the object’s own time zone: 2024-07-01 14:00 Europe/Berlin becomes 2024-07-01 14:00 in the sheet. Convert with setTimezone() first if you want another zone.
Without a number format, a value at midnight gets Excel’s locale-dependent short date format and any other value the short date-and-time format. Give the cell or the column a format such as NumberFormat::DATE to control it. Dates the date system cannot hold (before 1900, before 1904 with date1904, after 9999) are written as ISO 8601 text.
A DateInterval is written as a duration when it has a fixed length: one from diff(), or one without years and months. Others throw an UnsupportedValueException, because a month has no fixed number of days.
Notes and limits#
- Write order: rows go top to bottom, cells left to right, one worksheet at a time. Column widths and frozen panes are set when the worksheet is added.
- Excel limits apply: 1,048,576 rows, 16,384 columns and 32,767 characters per cell. Exceeding them throws a
LimitExceededException. A row rejected bywriteRow/writeRowsis not written at all, so the file stays valid if you catch the exception and continue. - Exceptions all extend
Vensas\Hypaxel\Exception\XlsxException:InvalidArgumentException,InvalidSheetNameException,DuplicateSheetNameException,InvalidTableNameException,DuplicateTableNameException,OverlappingRangeException,LimitExceededException,UnsupportedValueException,WriterStateException,IoException. - Text encoding: strings must be UTF-8. Invalid byte sequences are replaced with U+FFFD instead of producing a file Excel refuses to open. Control characters and carriage returns are written with Excel’s
_xHHHH_escape and read back unchanged. - Numbers: every Excel number is a double, so integers beyond ±2^53 lose precision. Floats are written in their shortest form that reads back identically.
- Formulas are stored without cached results; Excel recalculates them when the file opens.
- Readers: by default, cell references that follow from a cell’s position are omitted. References are written for the rest of a row after an empty cell and for the rest of a worksheet after a skipped row, which is what PhpSpreadsheet needs to place cells correctly. The test suite reads the default output back with PhpSpreadsheet and OpenSpout. ExcelJS fails on omitted references; pass
cellReferences: truefor such readers. - Styles belong to the writer that created them.
- Merged cells and tables are kept in memory until their worksheet completes.
Performance#
On an Apple M1 Max with PHP 8.4, Hypaxel writes 100,000 rows × 10 columns in about 0.4 s and 1 million rows in about 4.2 s (about 2.9 s with opcache and the tracing JIT), with a peak of 4 MB. That is about 11 times faster than OpenSpout, which streams with equally low memory, and about 40 times faster than PhpSpreadsheet, which builds the workbook in memory and is not run at 1 million rows. These are single runs on a machine that was busy with other work, so treat small differences as noise.
- Hypaxel, rows0.42 s, best
- Hypaxel, records with header0.57 s, 1.4×
- Hypaxel, rows, shared strings0.44 s, best
- Hypaxel, rows, Xlsx::stream()0.45 s, 1.1×
- OpenSpout4.6 s, 11×
- PhpSpreadsheet17.2 s, 41×
| Case | 100k rows | Peak memory (100k) | 1M rows | Peak memory (1M) |
|---|---|---|---|---|
| Hypaxel, rows | 0.42 s | 4 MB | 4.17 s | 4 MB |
| Hypaxel, records with header | 0.57 s | 4 MB | 5.38 s | 4 MB |
| Hypaxel, rows, shared strings | 0.44 s | 13.5 MB | 4.09 s | 90 MB |
| Hypaxel, rows, Xlsx::stream() | 0.45 s | 4 MB | 4.92 s | 4 MB |
| OpenSpout | 4.6 s | 4 MB | 47.83 s | 4 MB |
| PhpSpreadsheet | 17.2 s | 661.1 MB | not run | not run |
- Hypaxel, rows0.3 s, best
- Hypaxel, records with header0.37 s, 1.2×
- Hypaxel, rows, shared strings0.31 s, best
- Hypaxel, rows, Xlsx::stream()0.32 s, 1.1×
- OpenSpout4.11 s, 14×
- PhpSpreadsheet11.81 s, 39×
| Case | 100k rows | Peak memory (100k) | 1M rows | Peak memory (1M) |
|---|---|---|---|---|
| Hypaxel, rows | 0.3 s | 4 MB | 2.93 s | 4 MB |
| Hypaxel, records with header | 0.37 s | 4 MB | 3.64 s | 4 MB |
| Hypaxel, rows, shared strings | 0.31 s | 13.5 MB | 3.07 s | 90 MB |
| Hypaxel, rows, Xlsx::stream() | 0.32 s | 4 MB | 3.18 s | 4 MB |
| OpenSpout | 4.11 s | 4 MB | 40.65 s | 4 MB |
| PhpSpreadsheet | 11.81 s | 659.1 MB | not run | not run |
See Performance for methodology and reproduction, and Benchmarks for the full tables.
Building from source#
The source is at github.com/vensas/hypaxel, in the php/ directory. Report bugs at github.com/vensas/hypaxel/issues.
cd php
composer install
composer test # PHPUnit; reads every file back with PhpSpreadsheet and OpenSpout
composer analyse # PHPStan, level max with strict rules
composer cs # PHP-CS-Fixer (PER-CS), dry run
composer bench