Export 500,000 records

[khushmeet]

Hi I am trying to export 500,000 records but noting seems to work at the moment.
laravel version : 7.3
Excel version : 3.1.30
Memory_limit : -1

I tried few things but didn’t find any success yet hoping for someone to provide some suggestion
#2209
https://stefanzweifel.io/posts/lazy-collections-in-laravel-excel/

My code

CONTROLLER

(new TagsExport)->queue('exports/tags' . date('Y-m-d H_i') . '.xlsx'); echo back()->withSuccess('Export started!');

TagsExport.

1. Using ShouldQueue as suggested Allowed memory size exhausted #3090
2. Tried both FromQuery, FromCollection
   Below code work for 15000 rows

<?php

namespace App\Admin\Exports;
use App\Admin\Controllers\TagController;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithCustomQuerySize;
use Tag;

class TagsExport implements FromQuery, FromCollection,WithTitle, WithHeadings, WithMapping, ShouldQueue
{
    use Exportable;


    public function collection()
    {
        return  $query = Tag::all()->take(15000);
//        ->cursor();
        ;;
        //     ->when(1, function ($q) {
        //         return $q->where('deleted', 0);
        //     })
        //     ->cursor(); // ← This is the important bit
    }

    public function query()
    {
        //return Tag::query();
                 //->cursor(); // ← This is the important bit
    }

    public function WithCustomQuerySize()
    {
        $query = Tag::all()->take(15000);
        return $query->count();
    }

    public function chunkSize(): int
    {
        return 50;
    }

    /**
     * Get the sheet title
     *
     * @return string
     */
    public function title(): string
    {
        return 'Tags';
    }

    /**
     * Get the header rows
     *
     * @return array
     */
    public function headings(): array
    {
        return [
            'id',
            'Name',
            
        ];
    }
    
    /**
     * Get the mapped results
     *
     * @param  Company $company
     * @return array
     */
    public function map($company): array
    {
        return [
            $company->id,
            $company->name,
        ];
    }

    
}

After using query method it successfully export 20K rows but break 25000 rows with following error

ArgumentCountError: Too few arguments to function Illuminate\Support\Collection::get(), 0 passed in vendor/maatwebsite/excel/src/Jobs/AppendQueryToSheet.php on line 96 and at least 1 expected in vendor/laravel/framework/src/Illuminate/Support/Collection.php:413

// public function collection()
    // {
    //     return  $query = Tag::all()->take(20000);;
    //     //     ->when(1, function ($q) {
    //     //         return $q->where('deleted', 0);
    //     //     })
    //     //     ->cursor(); // ← This is the important bit
    // }

    public function query()
    {
        return  $query = Tag::all()->take(25000);  // same code work for 20000 rows
    }

Khush

[khushmeet]

@patrickbrouwers Any sugestions?

[chatisk]

@khushmeet did you solve it somehow?

[zeliangong]

use this library instead,
https://github.com/openspout/openspout

phpspreadsheet is really bad at exporting large data sets because it highly depends on in memory cache

[jaredsoaringtech]

Thanks for the suggestion!
I love Laravel excel but Open spout turned out to be a better fit for the large dataset I was working with.
Only 12 mins to import 425k rows! Laravel excel seemed to struggle with 100k rows and pretty ram hungry.