FastExcel

https://github.com/rap2hpoutre/fast-excel
Admin
FastExcel provides fast, memory-friendly Excel and CSV import and export capabilities for Laravel...
Tokens:7,623
Snippets:82
Trust Score:10
License:MIT
Update:1 month ago
Show doc for...
Tokens:
Raw
### Clone and install FastExcel

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/CONTRIBUTING.md

Clones the FastExcel repository, changes into the project directory, and installs dependencies with Composer.

```bash
git clone https://github.com/rap2hpoutre/fast-excel.git
cd fast-excel
composer install
```

--------------------------------

### Install FastExcel via Composer

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Install the FastExcel package via Composer.

```bash
composer require rap2hpoutre/fast-excel
```

--------------------------------

### Add header and rows style with headerStyle and rowsStyle

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Apply bold styling to the header row and custom font, wrap, and background color to all data rows using headerStyle and rowsStyle. The example downloads the resulting xlsx file.

```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');
```

--------------------------------

### Set header row and start row for import

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Reads headers from row 1 and starts reading data from row 155. The headerRow method is used to specify the actual header row when startRow is used.

```php
// Headers from row 1, data from row 155 onwards.
$collection = (new FastExcel)->headerRow(1)->startRow(155)->import('file.xlsx');
```

--------------------------------

### Style individual header columns with setHeaderColumnStyles

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Style individual header columns using setHeaderColumnStyles, the header-row counterpart of setColumnStyles. Keys are zero-based column positions; here column 0 gets a yellow background and column 1 blue font color.

```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');
```

--------------------------------

### Escape formulas with escapeFormulas()

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Writes string values as literal text cells instead of live formulas, preventing formula injection. Use when exporting untrusted input that may start with '='.

```php
(new FastExcel($rows))->escapeFormulas()->export('file.xlsx');
```

--------------------------------

### Use global fastexcel() helper

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Use the global helper to quickly instantiate FastExcel anywhere in a Laravel application. The helper can import and export.

```php
$collection = fastexcel()->import('file.xlsx');
fastexcel($collection)->export('file.xlsx');
```

--------------------------------

### Export data using FastExcel facade

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Using the facade, set export data with the data method and export to a file. The constructor is not available when using the facade.

```php
$list = collect([
    [ 'id' => 1, 'name' => 'Jane' ],
    [ 'id' => 2, 'name' => 'John' ],
]);

FastExcel::data($list)->export('file.xlsx');
```

--------------------------------

### Import multiple sheets with importSheets

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Import multiple sheets using importSheets.

```php
$sheets = (new FastExcel)->importSheets('file.xlsx');
```

--------------------------------

### Export with generator using yield

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

For any other source, hand export() a generator using yield to stream rows and keep memory low.

```php
function rowsGenerator() {
    foreach (some_paginated_source() as $row) {
        yield $row;
    }
}

(new FastExcel(rowsGenerator()))->export('test.xlsx');
```

--------------------------------

### Export large collection with cursor()

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export large collections with low memory by feeding a lazy source like Eloquent's cursor(). Peak memory stays flat.

```php
// Export consumes only a few MB, even with 10M+ rows.
(new FastExcel(User::cursor()))->export('users.xlsx');
```

--------------------------------

### Use raw OpenSpout Cell instances

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Uses raw OpenSpout Cell instances for full control over a single cell's type or style. Other scalar values in the row keep their normal handling.

```php
use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Style\Style;

$users = collect([
    ['name' => 'John', 'note' => Cell::fromValue('paid', (new Style())->setFontBold())],
    ['name' => 'Jane', 'note' => 'pending'],
]);

(new FastExcel($users))->export('users.xlsx');
```

--------------------------------

### Import multiple sheets with names

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Import multiple sheets with sheet names using withSheetsNames.

```php
$sheets = (new FastExcel)->withSheetsNames()->importSheets('file.xlsx');
```

--------------------------------

### Import and insert rows into database

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Imports an Excel file and processes each row with a callback, inserting the data into a database using the User model. The callback receives each row as an associative array.

```php
$users = (new FastExcel)->import('file.xlsx', function ($line) {
    return User::create([
        'name' => $line['Name'],
        'email' => $line['Email']
    ]);
});
```

--------------------------------

### Register FastExcel facade alias in config/app.php

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Add this line to the aliases key in config/app.php to register the FastExcel facade.

```php
'FastExcel' => Rap2hpoutre\FastExcel\Facades\FastExcel::class,
```

--------------------------------

### Set column widths with setColumnWidth

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Set column widths via configureOptionsUsing. Widths are in Excel's character unit and column numbers are 1-based. This works with streaming exports because widths are written when the file is finalized.

```php
(new FastExcel($list))
    ->configureOptionsUsing(function ($options) {
        $options->setColumnWidth(40, 1);      // first column
        $options->setColumnWidth(15, 2, 3);   // second and third columns
    })
    ->export('file.xlsx');
```

--------------------------------

### Run FastExcel tests

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/CONTRIBUTING.md

Runs the test suite for FastExcel. All new behavior should come with a test, and bug fixes are typically added to tests/IssuesTest.php as testIssue<number>().

```bash
composer test
```

--------------------------------

### Import an Excel file with FastExcel

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Imports an Excel file and returns a Collection. This is the basic usage of the FastExcel import method.

```php
$collection = (new FastExcel)->import('file.xlsx');
```

--------------------------------

### Import CSV with custom delimiter and encoding

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Imports a CSV file with a custom delimiter, enclosure character, and encoding. The configureCsv method accepts the delimiter, enclosure, and encoding in that order.

```php
$collection = (new FastExcel)->configureCsv(';', '#', 'gbk')->import('file.csv');
```

--------------------------------

### Import large file in chunks with headerRow, startRow, and limitRows

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Imports a chunk of data from a large file, using headerRow, startRow, and limitRows together. This is used to process a large file in slices, one per job run, with correct header names.

```php
$chunk = (new FastExcel)
    ->headerRow(1)
    ->startRow(2 + ($page * 1000)) // data begins on row 2
    ->limitRows(1000)
    ->import('file.xlsx');
```

--------------------------------

### Import specific sheet by name

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Import a specific sheet by name using the sheet method.

```php
$users = (new FastExcel)->sheet('Users')->import('file.xlsx');
```

--------------------------------

### Import large files with streaming callback returning null

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Use this streaming import to process a large file without exhausting PHP's memory_limit. The callback must return null so rows are not accumulated; returning a value collects rows and grows memory. This approach keeps memory flat regardless of file size.

```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
});
```

--------------------------------

### Export a Model to .xlsx

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export an Eloquent model collection to an .xlsx file. The export method returns the absolute path to the written file.

```php
use Rap2hpoutre\FastExcel\FastExcel;
use App\User;

// Load users
$users = User::all();

// Export all users
(new FastExcel($users))->export('file.xlsx');
```

--------------------------------

### Export multiple sheets with SheetCollection

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export multiple sheets by creating a SheetCollection and passing it to FastExcel.

```php
$sheets = new SheetCollection([
    User::all(),
    Project::all()
]);
(new FastExcel($sheets))->export('file.xlsx');
```

--------------------------------

### Import sheets with sheet context callback

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Use withSheetContext to receive the current sheet name as the first argument of the importSheets callback. Handy when the same field names need different handling 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];
    });
```

--------------------------------

### Set column width for a range with setColumnWidthForRange

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Set a uniform width for a contiguous range of columns using setColumnWidthForRange. Here columns 1 through 4 are set to width 20.

```php
(new FastExcel($list))
    ->configureOptionsUsing(function ($options) {
        $options->setColumnWidthForRange(20, 1, 4); // columns 1 through 4
    })
    ->export('file.xlsx');
```

--------------------------------

### Export a Collection to .xlsx

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export a collection to an .xlsx file. The export method returns the absolute path to the written file.

```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');
```

--------------------------------

### Download Exported File

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Download the exported file directly from a controller method.

```php
return (new FastExcel(User::all()))->download('file.xlsx');
```

--------------------------------

### Import specific sheet by number

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Import a specific sheet by its number using the sheet method.

```php
$users = (new FastExcel)->sheet(3)->import('file.xlsx');
```

--------------------------------

### Guard setColumnWidth for csv compatibility

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Guard setColumnWidth with method_exists because only xlsx and ods support widths; csv does not define setColumnWidth and calling it raises 'Error: Call to undefined method'. Use this when the same code path may export csv.

```php
->configureOptionsUsing(function ($options) {
    if (method_exists($options, 'setColumnWidth')) {
        $options->setColumnWidth(40, 1);
    }
})
```

--------------------------------

### Limit imported rows with limitRows

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Limits the number of data rows imported to 100, excluding headers. Works with both import and importLazy.

```php
$collection = (new FastExcel)->limitRows(100)->import('file.xlsx');
```

--------------------------------

### Export with Custom Columns

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export only specific attributes with custom column names using a callback. The callback receives each model and returns an associative array mapping column headers to values.

```php
(new FastExcel(User::all()))->export('users.csv', function ($user) {
    return [
        'Email' => $user->email,
        'First Name' => $user->firstname,
        'Last Name' => strtoupper($user->lastname),
    ];
});
```

--------------------------------

### Stream rows with importLazy and chunk

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Use importLazy to stream rows as a LazyCollection, giving you the full Collection API while keeping memory flat. It accepts the same optional callback as import, honors sheet(), withoutHeaders(), and header de-duplication, but does not support transpose().

```php
use Illuminate\Support\LazyCollection;

(new FastExcel)->importLazy('file.xlsx')
    ->chunk(1000)
    ->each(function (LazyCollection $chunk) {
        User::insert($chunk->all());
    });
```

--------------------------------

### Specify sheet names in SheetCollection

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Use array keys to specify sheet names in a SheetCollection.

```php
$sheets = new SheetCollection([
    'Users' => User::all(),
    'Second sheet' => Project::all()
]);
```

--------------------------------

### Force all values to text with stringValues()

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Forces all values to be written as text cells, preserving leading zeros and long numeric IDs. Use when you need to keep values like phone numbers intact.

```php
// 0660123456 stays "0660123456" instead of becoming 660123456
(new FastExcel($users))->stringValues()->export('users.xlsx');
```

--------------------------------

### Skip rows during export with callback

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Skips rows during export when the callback returns null or false. Use to filter rows based on custom logic.

```php
(new FastExcel($users))->export('file.xlsx', function ($user) {
    return $user->active ? ['name' => $user->name, 'email' => $user->email] : null;
});
```

--------------------------------

### Limit imported columns with limitColumns

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Truncates each imported row after a given column, either by column reference (e.g., 'H') or by number of columns (e.g., 8). Useful for files with formatting applied to entire rows, which can cause empty trailing columns.

```php
$collection = (new FastExcel)->limitColumns('H')->import('file.xlsx');
$collection = (new FastExcel)->limitColumns(8)->import('file.xlsx');
```

--------------------------------

### Export a Query to CSV

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Export an Eloquent query result to a CSV file. Supports xlsx, ods, and csv formats.

```php
$invoices = App\Invoice::orderBy('created_at', 'DESC')->get();
(new FastExcel($invoices))->export('invoices.csv');
```

--------------------------------

### Hide Columns with hideColumnsPrefixedWith

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Hide columns prefixed with an underscore (default) from the exported file while still using them in the callback. The prefix can be changed with hideColumnsPrefixedWith('tmp_'). Hiding is opt-in; without calling this method, all columns are exported.

```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
    ];
});
```

--------------------------------

### Keep only specific columns with onlyColumns

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Keeps only the specified columns and drops all others, including empty middle columns. Accepts a mix of column letters and 1-based indexes, preserving the given order.

```php
$collection = (new FastExcel)->onlyColumns(['A', 'B', 'H'])->import('file.xlsx');
$collection = (new FastExcel)->onlyColumns([1, 2, 8])->import('file.xlsx');
```

--------------------------------

### Override column types with setColumnFormat()

Source: https://github.com/rap2hpoutre/fast-excel/blob/master/README.md

Overrides the cell type per column using setColumnFormat(), which takes precedence over stringValues(). Use when you need mixed types in the same export.

```php
(new FastExcel($users))
    ->stringValues()                                  // text by default
    ->setColumnFormat([
        'id'    => 'number',                          // keep as number
        'phone' => 'string',                          // keep as text
    ])
    ->export('users.xlsx');
```
Tokens:
Raw