fast-excel

Fast Excel import/export for Laravel, thanks to [Spout](https://github.com/box/spout). See [benchmarks](#benchmarks) below. ## Quick start Install via composer: ``` composer require rap2hpoutre/fast-excel ``` Export a Model to `.xlsx` file: ```php use Rap2hpoutre\FastExcel\FastExcel; use App\User; // Load users $users = User::all(); // Export all users (new FastExcel($users))->export('file.xlsx'); ``` ## Export Export a Model, Query or **Collection**: ```php $list = collect([ [ 'id' => 1, 'name' => 'Jane' ], [ 'id' => 2, 'name' => 'John' ], ]); // export() returns the absolute path to the written file $path = (new FastExcel($list))->export('file.xlsx'); ``` Export `xlsx`, `ods` and `csv`: ```php $invoices = App\Invoice::orderBy('created_at', 'DESC')->get(); (new FastExcel($invoices))->export('invoices.csv'); ``` Export only some attributes specifying columns names: ```php (new FastExcel(User::all()))->export('users.csv', function ($user) { return [ 'Email' => $user->email, 'First Name' => $user->firstname, 'Last Name' => strtoupper($user->lastname), ]; }); ``` Hide columns from the exported file while still being able to use them inside the callback, using `hideColumnsPrefixedWith()`. This is handy for callback-only data (lookups, computed flags, etc.) that should not appear as a column: ```php (new FastExcel(User::all()))->hideColumnsPrefixedWith()->export('users.csv', function ($user) { return [ 'Name' => $user->name, '_role' => $user->role, // used for logic below, never written to the file ]; }); ``` Columns are hidden from both the header row and every data row. The prefix defaults to `_`, and any other one can be used — `hideColumnsPrefixedWith('tmp_')`. Hiding is opt-in: unless you call this method, every column is exported, so columns that already start with an underscore keep working as before. Download (from a controller method): ```php return (new FastExcel(User::all()))->download('file.xlsx'); ``` ## Import `import` returns a Collection: ```php $collection = (new FastExcel)->import('file.xlsx'); ``` Import a `csv` with specific delimiter, enclosure characters and "gbk" encoding: ```php $collection = (new FastExcel)->configureCsv(';', '#', 'gbk')->import('file.csv'); ``` Import and insert to database: ```php $users = (new FastExcel)->import('file.xlsx', function ($line) { return User::create([ 'name' => $line['Name'], 'email' => $line['Email'] ]); }); ``` Limit the number of data rows imported with `limitRows` (headers excluded). It works with both `import` and `importLazy`: ```php $collection = (new FastExcel)->limitRows(100)->import('file.xlsx'); ``` Start reading at a given row with `startRow`. On its own, `startRow` also treats that row as the header row. Use `headerRow` to read the headers from their real position while data starts further down: ```php // Headers from row 1, data from row 155 onwards. $collection = (new FastExcel)->headerRow(1)->startRow(155)->import('file.xlsx'); ``` Together with `limitRows`, that is how a large file is imported in chunks — one slice per job run, each with the correct header names: ```php $chunk = (new FastExcel) ->headerRow(1) ->startRow(2 + ($page * 1000)) // data begins on row 2 ->limitRows(1000) ->import('file.xlsx'); ``` `headerRow` is opt-in: without it, `startRow` keeps its previous behaviour of using the start row as the header row. Truncate each imported row after a given column with `limitColumns`, which takes either a column reference or a number of columns: ```php $collection = (new FastExcel)->limitColumns('H')->import('file.xlsx'); $collection = (new FastExcel)->limitColumns(8)->import('file.xlsx'); ``` This is useful for files where formatting has been applied to entire rows: the spreadsheet then reports thousands of trailing cells that look like real columns, and importing them yields empty `column_9`, `column_10`… entries on every row. Those cells are dropped from the imported collection (OpenSpout still parses the sheet). Like `limitRows`, it works with both `import` and `importLazy`. Keep specific columns (and drop everything else, including middle empties) with `onlyColumns`. Letters and 1-based indexes can be mixed; order is preserved: ```php $collection = (new FastExcel)->onlyColumns(['A', 'B', 'H'])->import('file.xlsx'); $collection = (new FastExcel)->onlyColumns([1, 2, 8])->import('file.xlsx'); ``` `onlyColumns` and `limitColumns` cannot both be active — setting one clears the other. Passing `null` only clears that setter and leaves the other alone. ## Facades You may use FastExcel with the optional Facade. Add the following line to ``config/app.php`` under the ``aliases`` key. ````php 'FastExcel' => Rap2hpoutre\FastExcel\Facades\FastExcel::class, ```` Using the Facade, you will not have access to the constructor. You may set your export data using the ``data`` method. ````php $list = collect([ [ 'id' => 1, 'name' => 'Jane' ], [ 'id' => 2, 'name' => 'John' ], ]); FastExcel::data($list)->export('file.xlsx'); ```` ## Global helper FastExcel provides a convenient global helper to quickly instantiate the FastExcel class anywhere in a Laravel application. ```php $collection = fastexcel()->import('file.xlsx'); fastexcel($collection)->export('file.xlsx'); ``` ## Advanced usage ### Export multiple sheets Export multiple sheets by creating a `SheetCollection`: ```php $sheets = new SheetCollection([ User::all(), Project::all() ]); (new FastExcel($sheets))->export('file.xlsx'); ``` Use index to specify sheet name: ```php $sheets = new SheetCollection([ 'Users' => User::all(), 'Second sheet' => Project::all() ]); ``` ### Import multiple sheets Import multiple sheets by using `importSheets`: ```php $sheets = (new FastExcel)->importSheets('file.xlsx'); ``` You can also import a specific sheet by its number: ```php $users = (new FastExcel)->sheet(3)->import('file.xlsx'); ``` `sheet()` also accepts a sheet name, so you can select a sheet without knowing its position: ```php $users = (new FastExcel)->sheet('Users')->import('file.xlsx'); ``` Import multiple sheets with sheets names: ```php $sheets = (new FastExcel)->withSheetsNames()->importSheets('file.xlsx'); ``` Use `withSheetContext()` to receive the current sheet name as the first argument of the `importSheets` callback — handy when the same field names need to be handled differently per sheet: ```php $sheets = (new FastExcel) ->withSheetContext() ->importSheets('file.xlsx', function ($sheetName, $row) { if ($sheetName === 'Users' && empty($row['email'])) { return null; // skip rows without an email on the Users sheet } return $row + ['_sheet' => $sheetName]; }); ``` ### Export large collections (low memory) Passing a materialized collection (`User::all()`, `->get()`, `collect([...])`) loads every row into memory *before* the export starts, so it grows with the size of the data and a large enough dataset fails outright with `Allowed memory size of N bytes exhausted`. Feed a lazy source instead and peak memory stays flat, whatever the row count. Eloquent's `cursor()` (or `->lazy()`) returns a [`LazyCollection`](https://laravel.com/docs/collections#lazy-collections), which FastExcel streams row by row — no wrapper needed: ```php // Export consumes only a few MB, even with 10M+ rows. (new FastExcel(User::cursor()))->export('users.xlsx'); ``` For any other source, hand `export()` a generator [using `yield`](https://www.php.net/manual/en/language.generators.syntax.php): ```php function rowsGenerator() { foreach (some_paginated_source() as $row) { yield $row; } } (new FastExcel(rowsGenerator()))->export('test.xlsx'); ``` Exporting 1,000,000 rows (4 columns) under a 512 MB `memory_limit`: | How you export | Peak memory | Result | | --- | --- | --- | | `export()` from a materialized collection | > 512 MB | **fails** once it exceeds `memory_limit` | | `export()` from a cursor / generator (streaming) | ~4 MB | always completes | Streaming does not change export *speed* (that is dominated by the underlying [OpenSpout](https://github.com/openspout/openspout) writer) — it is what keeps memory flat so very large files finish at all. `transpose()` cannot stream, as it must buffer the whole dataset to pivot rows and columns. ### Import large files (low memory) `import` returns a Collection containing every row, so memory grows with the size of the file. On a file larger than your PHP `memory_limit`, the default `import()` fails outright with `Allowed memory size of N bytes exhausted`. To import a large file without running out of memory, pass a callback and **return `null`** — each row is then processed but not accumulated, so memory stays flat: ```php // Memory stays flat regardless of the number of rows. (new FastExcel)->import('file.xlsx', function ($line) { User::create([ 'name' => $line['Name'], 'email' => $line['Email'], ]); return null; // don't keep the row in memory }); ``` > If the callback returns a value (for example the created model), that value is > collected and returned to you — handy for small files, but it keeps every row in > memory. Return `null` when you only need the side effect (e.g. inserting rows). Importing a 730,000-row file (8 columns), measured under a constrained `memory_limit`: | How you import | Peak memory | Result | | --- | --- | --- | | `import($file)` (returns a Collection) | ~440 MB | **fails** once it exceeds `memory_limit` | | `import($file, fn ($row) => null)` (streaming) | ~4 MB | always completes | Reading speed is the same either way — it is dominated by the underlying [OpenSpout](https://github.com/openspout/openspout) parser, not by how the rows are returned. The difference above is memory, which is what lets very large files finish at all. If you would rather keep working with the rows than process them inside a callback, `importLazy` returns a [`LazyCollection`](https://laravel.com/docs/collections#lazy-collections) that streams rows one at a time — you get the full Collection API while memory stays flat: ```php use Illuminate\Support\LazyCollection; (new FastExcel)->importLazy('file.xlsx') ->chunk(1000) ->each(function (LazyCollection $chunk) { User::insert($chunk->all()); }); ``` `importLazy` accepts the same optional callback as `import`, and honors `sheet()`, `withoutHeaders()`, and header de-duplication. Transposing (`transpose()`) is not supported with lazy import. ### Add header and rows style Add header and rows style with `headerStyle` and `rowsStyle` methods. ```php use OpenSpout\Common\Entity\Style\Style; $header_style = (new Style())->setFontBold(); $rows_style = (new Style()) ->setFontSize(15) ->setShouldWrapText() ->setBackgroundColor("EDEDED"); return (new FastExcel($list)) ->headerStyle($header_style) ->rowsStyle($rows_style) ->download('file.xlsx'); ``` You can also style each header column individually with `setHeaderColumnStyles` (the header-row counterpart of `setColumnStyles`). Keys are the zero-based column positions: ```php use OpenSpout\Common\Entity\Style\Color; use OpenSpout\Common\Entity\Style\Style; return (new FastExcel($list)) ->setHeaderColumnStyles([ 0 => (new Style())->setBackgroundColor(Color::YELLOW), 1 => (new Style())->setFontColor(Color::BLUE), ]) ->download('file.xlsx'); ``` ### Set column widths Column widths are an OpenSpout writer option, so they are set through `configureOptionsUsing`. Widths are expressed in Excel's own unit (roughly the number of characters that fit), and column numbers are **1-based**: ```php (new FastExcel($list)) ->configureOptionsUsing(function ($options) { $options->setColumnWidth(40, 1); // first column $options->setColumnWi