Export 10M+ rows in XLSX with Laravel without memory issues
Tags: laravel, php, webdev, tutorial
Published: 2019-08-30 10:14:30

---
title: Export 10M+ rows in XLSX with Laravel without memory issues
published: true
description: How to use FastExcel to export data with generators (using yield and yield from) in Laravel
tags: Laravel, php, webdev, tutorial
---

_TL;DR: This post introduce FastExcel for Laravel, gives a quick overview over PHP generators and finally shows how to use both to generate Excel files from collections while preserving memory._

# About FastExcel

[Laravel FastExcel](https://github.com/rap2hpoutre/fast-excel) is intended at being Laravel-flavoured [Spout](https://github.com/box/spout), with the goal of simplifying **imports and exports**. It could be considered as a faster (and memory friendly) alternative to [Laravel Excel](https://laravel-excel.maatwebsite.nl/) with a different approach and less features. Getting started in 2 steps.

Install with composer:

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

Then export a **Model** or a **Collection** to `XLSX`, `CSV` or `ODS`:

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

More information on the [`README`](https://github.com/rap2hpoutre/fast-excel/blob/master/README.md) of the [project homepage](https://github.com/rap2hpoutre/fast-excel).

# Generators

[Generators](https://www.php.net/manual/en/language.generators.syntax.php) were introduced in PHP 5, many years ago. A generator function looks just like a normal function, except that instead of returning a value, a generator *yields* values. Then you can iterate over the generator function. 

On of the goal of such a function is to *lazy iterate* over data without building an array. So it preserves memory when manipulating large datasets. Quoting [PHP documentation](https://www.php.net/manual/en/language.generators.overview.php):

> A generator allows you to write code that uses foreach to iterate over a set of data without needing to build an array in memory, which may cause you to exceed a memory limit, or require a considerable amount of processing time to generate.

Assuming you have a `User` model with 10M+ entries in database and you want to iterate over it in your code, instead of just calling `User::all()`, you could use a generator:

```php
function usersGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

$users = usersGenerator();
foreach($users as $user) {
    // Do something with each user without hitting memory limit
}
```

The example above runs queries to only take users one by one. It only uses the memory needed to load 1 user `N` times.

# Export large dataset using FastExcel and Generators

Since v1.3.0, [FastExcel](https://github.com/rap2hpoutre/fast-excel) accepts generator function as a parameter. Using the previous example, you could just pass the generator to `fastexcel` function:


```php
function usersGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

// Export consumes only a few MB, even with 10M+ rows.
$users = usersGenerator();
fastexcel($users)->export('test.xlsx');
```

[FastExcel](https://github.com/rap2hpoutre/fast-excel) internally creates rows one by one when using generator, so it will not consume extra memory. It will be a long process, so make sure it does not hit `max_execution_time` (you could use Queue, any asynchronous technique, increase max execution time, or even run it from CLI). Still, your server will be grateful to not consume all its memory for one export.

So thanks to generators, you can now export thousands of models to `XLSX`, `CSV` and `ODS` files, with a few lines of code in a Laravel project.

Learn more about he library in the repository: https://github.com/rap2hpoutre/fast-excel