Phpspreadsheet Laravel Package

Getting Started

Install

composer require phpoffice/phpspreadsheet

Requirements to check on your host before anything else: PHP 8.2+ (2.x dropped 7.4), plus ext-zip, ext-xml, ext-mbstring, ext-gd (images/charts), ext-iconv. Run php -m | grep -E 'zip|xml|mbstring|gd'. In Docker/Forge environments a missing ext-zip surfaces as a confusing "Class ZipArchive not found" at write time, not install time.

There is no Laravel service provider, no config file, no facade. It's a plain library — you use classes directly. That's fine; it also means you own the glue code. If you want Eloquent-aware exports (FromQuery, WithHeadings, queued exports, ShouldAutoSize sugar), the community wrapper maatwebsite/excel sits on top of PhpSpreadsheet. For anything non-trivial (styling, templating, charts, unusual formats) you'll eventually drop to raw PhpSpreadsheet anyway, so learning the raw API is not wasted.

Where to look first

1. https://phpspreadsheet.readthedocs.io — the "Accessing cells", "Recipes", and "Memory saving" topics cover ~80% of daily work.
2. The samples/ directory in the installed package (vendor/phpoffice/phpspreadsheet/samples/) — runnable 01_Simple.php through chart/image samples. Faster than the docs for styling questions.
3. The changelog — 2.0 removed all deprecations and strengthened typing; old StackOverflow answers frequently reference removed PHPExcel-era methods.

First use case: a download endpoint

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Route::get('/reports/orders', function () {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Orders');

    $sheet->fromArray(['ID', 'Customer', 'Total', 'Placed'], null, 'A1');

    $row = 2;
    foreach (Order::with('customer')->cursor() as $order) {
        $sheet->fromArray([
            $order->id,
            $order->customer->name,
            $order->total,
            $order->created_at->toDateTime(),
        ], null, "A{$row}");
        $row++;
    }

    $sheet->getStyle('A1:D1')->getFont()->setBold(true);
    foreach (range('A', 'D') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    $writer = new Xlsx($spreadsheet);
    $spreadsheet->disconnectWorksheets(); // free memory before streaming

    return response()->streamDownload(function () use ($writer) {
        $writer->save('php://output');
    }, 'orders.xlsx', [
        'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    ]);
});

Note the ordering: build → disconnectWorksheets() → stream. Sending the file via response()->download($tempPath)->deleteFileAfterSend() also works and is easier to debug, at the cost of writing to disk.

Implementation Patterns

1. Wrap it in a dedicated export/import class, not controllers. A ReportExporter with ->build(): Spreadsheet and ->save(string $path): void keeps controllers thin and makes the spreadsheet unit-testable. Inject the data source (collection, query builder) rather than querying inside the exporter.

2. Streaming vs. buffering.

Queued exports are the standard escape hatch. Store the file, set a status column, broadcast or poll.

3. Chunked reading with a ReadFilter for imports. Naive $reader->load() on a 50MB file will exhaust a 128MB memory_limit. Two mitigations, both needed:

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class ChunkFilter implements IReadFilter
{
    public function __construct(private int $start, private int $end) {}

    public function readCell(string $columnAddress, int $row, string $worksheetName = ''): bool
    {
        return $row >= $this->start && $row <= $this->end;
    }
}

$reader = IOFactory::createReaderForFile($path);
$reader->setReadDataOnly(true);          // skip styles, huge saving
$reader->setReadFilter(new ChunkFilter(2, 5001));
$reader->setLoadSheetsOnly(['Orders']);  // if you know the sheet name
$sheet = $reader->load($path)->getActiveSheet();

Loop the chunk window until you hit an empty block. setReadDataOnly(true) is the single biggest win — often 5–10× memory reduction — but it means formulas come back unevaluated and formatting is dropped, which is usually what you want for imports.

4. Use toArray() with the $formatData flag consciously.

$rows = $sheet->rangeToArray('A2:D100', null, true, false, true); // 4th arg = formatData

formatData: true gives you '2024-01-15' strings and '45.00' for currency; false gives raw floats and Excel date serials. Raw values are better for persisting to the DB — you convert with Date::excelToDateTimeObject() yourself and avoid locale/format surprises.

5. Template-driven exports. For anything with a designed layout, don't build the styling in code — ship an .xlsx template in resources/templates/, load it, fill named cells or placeholders, write out. This is where PhpSpreadsheet beats wrapper libraries decisively.

$spreadsheet = IOFactory::load(resource_path('templates/invoice.xlsx'));
$spreadsheet->getActiveSheet()->setCellValue('B4', $invoice->number);

6. Formulas: write them, but control calculation.

$sheet->setCellValue('E2', '=SUM(B2:D2)');
$sheet->getCell('E2')->getCalculatedValue(); // triggers the calc engine

For exports, set $writer->setPreCalculateFormulas(false) — Excel recalculates on open, and you skip a lot of CPU. For imports where you must read computed results, set $spreadsheet->getCalculationEngine()->getConfig()->setCalculationCacheEnabled(true) (2.x uses a PSR-16 cache via setValueBinder/Settings) and don't load circular-reference worksheets.

7. Security: never trust an uploaded file. IOFactory::identify() sniffs the real format. Validate extension + MIME in FormRequest, then:

$reader = IOFactory::createReader(IOFactory::identify($path));
$reader->setReadDataOnly(true);

Reject Xls (BIFF) uploads if you can — older binary parsing is the riskier surface. If you accept them, keep the library patched; releases are frequent and include security fixes.

8. CSV in/out. IOFactory handles it, but set the delimiter and input encoding explicitly. Excel on Windows emits UTF-8 with BOM and semicolons under some locales:

$reader->setInputEncoding('UTF-8');
$reader->setDelimiter(',');

When writing CSV for Excel consumption, prepend "\xEF\xBB\xBF" yourself, or open it with fopen('php://output') and fwrite the BOM before $writer->save('php://output').

9. Coordinates. Column letters are easy to get wrong. Use Coordinate::stringFromColumnIndex($i) and Coordinate::columnIndexFromString('AA') rather than hand-rolled base-26 logic.

10. Testing. You can assert on a generated file without touching Excel: build the spreadsheet, call $sheet->getCell('B2')->getValue(), and for round-trips write to a temp file with TempFile and read it back with setReadDataOnly(true) to assert the persisted values. Spreadsheet can be constructed without any I/O, so most of your exporter tests need no filesystem at all.

Gotchas and Tips

Memory is the #1 production incident. A file with 500k rows and modest styling will happily eat 1–2GB. Every one of these helps: setReadDataOnly(true), setLoadSheetsOnly(), setReadFilter(), iterating with $sheet->getRowIterator() instead of toArray() (which materialises everything), and calling $spreadsheet->disconnectWorksheets() as soon as you've written. Also raise memory_limit inside the artisan command/queue worker — ini_set('memory_limit', '1G') there, not just in the web SAPI, because the CLI php.ini often differs. If you routinely exceed a few hundred thousand rows, stop and use openspout/openspout for that job; PhpSpreadsheet is not a streaming writer.

Excel dates are serial numbers. getValue() on a date cell returns an int/float like 45307, not a date. Use \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value) and remember the 1900 leap-year bug means serials before 1900-03-01 are off by one. Going the other way, Date::PHPToExcel($carbon) and then set a date number format, or Excel shows a raw number.

toFormattedString() / toArray(..., formatData: true) always return strings (since 1.28, documented in 2.0). This silently breaks ->sum() on columns that were numeric in the DB but come back as '1,234.00'. Format with false and cast at the boundary instead.

Beware values that start with =. setCellValue('A1', '=CMD|...') creates a formula — a classic CSV/XLSX injection vector when echoing user input. Use setCellValueExplicit($coord, $value, DataType::TYPE_STRING) for any user-supplied string. Same trap for values starting with +, -, @.

Leading zeros and long IDs. Phone numbers, IBANs, EAN-13, and 18-digit IDs become floats and lose precision. Set them explicitly as strings (above) and set the column format to FORMAT_TEXT so Excel doesn't re-coerce on open.

Styling is per-cell and expensive. applyFromArray() on a 100k-row range is slow. Prefer column-level defaults (getColumnDimension('D')->setStyle(...)), and never call setAutoSize(true) on wide data columns — PhpSpreadsheet computes widths by walking cells. Hardcode sensible setWidth() values instead.

Stray output corrupts the file. A BOM in a PHP file, a stray echo/dd(), or whitespace after ?> in an included class produces a "file is corrupt" dialog in Excel. Symptom: the download works, Excel refuses it. Grep for ?> and use ob_clean() / ensure no output before save('php://output').

Temp directory permissions. Xlsx writer uses temp files. On hardened containers with a read-only sys_get_temp_dir() you'll get obscure ZipArchive or permission errors. Point it somewhere writable or configure upload_tmp_dir.

IOFactory::createWriter() vs. explicit writer. IOFactory::createWriter($spreadsheet, 'Xlsx') is convenient but some features (charts, images, data validation) are unevenly supported across formats. Xlsx is the only fully-featured target; Ods and Html are close-but-not-equal; Xls is legacy and drops emoji/some characters (fixed only recently). If a customer reports "the chart disappears in LibreOffice", that's an Ods writer gap, not your bug.

Locking and protection are confusing. Sheet protection via getProtection()->setSheet(true) does nothing unless cells are also getProtection()->setLocked(Protection::PROTECTION_PROTECTED) — cells are locked by default, so the practical gotcha is the inverse: users unlock specific input cells before enabling protection. 2.0 added helpers to query whether a cell is actually locked (considering row/column style inheritance), which is worth using if you ever inspect uploaded files.

CSV delimiter/encoding guessing is unreliable. Don't rely on auto-detection for user uploads. Let the user pick, or attempt with a strict check and fail with a clear message. Also strip the BOM before parsing the header row, or your first column name becomes "\u{FEFF}id" and silently doesn't match.

Formula engine ≠ Excel. PhpSpreadsheet reimplements Excel's function library in PHP. Niche functions, locale-specific behaviour, and dynamic arrays may return #VALUE!/#NAME? or differ in edge cases. Never treat a calculated value as authoritative for billing or compliance; if you must, validate the formula results against a known-good file and fail loudly on error tokens rather than storing #REF! in the database.

Iteration pitfalls. getRowIterator() / getCellIterator() skip rows that have no row dimension by default, so $row->getRowIndex() won't be contiguous. Pass iterateOnlyExistingCells: true (addable in constructors since 2.0) for sparse sheets, or expect to filter nulls. When writing, getHighestRow() ignores rows whose only content is formatting.

Extension points worth knowing.

• IReadFilter — selective/chunked loading (the main memory lever).
• IWriter / subclassing Xlsx — e.g. force setIncludeCharts(false) for speed.
• Value binder (Settings::setValueBinder()) — a global hook to coerce all incoming values (e.g. treat everything as string). Powerful, easy to over-apply; scope it per-import.
• PSR-16 cell caching — still supported for huge reads but slower; the modern answer is "use a read filter," not caching.
• Settings::setLibXmlLoaderOptions() and setZipClass() — escape hatches for libxml/zip extension oddities in minimal containers.

Version pinning. Require a caret range (^2.0) and keep it updated — 2.x releases bundle real bug fixes for readers (row/column style inheritance, defined-name recalculation, tables on other sheets). Locking to an exact old 1.x version is the most common source of "this file opened fine in Excel but not here" reports. Type declarations were strengthened in 2.0; if you subclass Spreadsheet or implement its interfaces, expect to adjust signatures and re-run PHPStan.