Allow Queueing to be used with Laravel's Batching feature

[andrewskm]

The current queueing process that laravel-excel has works great, especially with FromQuery and being able to export large amounts of data with chunking. The handling of failed exports works really well too.

With Laravel 8 came Bus::batch() feature, so it would be nice if the queue was able to be added onto a batch with other jobs like so:

$batch = Bus::batch([
    (new ExportMultipleSheets($data))->queue($filepath)
])
->then(function(Batch $batch) use ($completionJobs)
{
    Bus::chain($completionJobs)->dispatch();
})
->catch(function(Batch $batch, \Throwable $e)
{
    $message = GetExceptionMessage::run('Export', $e, $this->user);
    \Mail::to($this->user)->send(new FailedExport($this->data->get('reportName'), [$message], $batch->id));
})->name('Queued Export')->onQueue('export')->dispatch();

My use case for this is during exporting FromQuery chunks I use laravel-excel for xlsx and TCPDF wrapped in a laravel-actions AsJob in order to handle pdfs in a queue. I would like to replace the batch parameter with either xlsx or pdf based on the request so that the above code is written once. The work around right now is to have the above for pdfs, then this for xlsx (which again, all works great):

(new ExportMultipleSheets($data))
->queue($filepath)
->onQueue('export')
->chain($chain);

Doing some playing around it seems like the Maatwebsite\Excel\Jobs\QueueExport class would need the Illuminate\Bus\Batchable trait added, which allows it to run on the batch properly, but if there is a failure it just keeps running the Maatwebsite\Excel\Jobs\AppendQueryToSheet as many times as the retries is set rather than cancelling the batch.
So it then seems like there should be a check somewhere to cancel and/or check to see if the batch has been cancelled.

That was the extent of what I was able to test out so far.
Thoughts?

[patrickbrouwers]

To be honest, I don't think it will be added, at least not for exports. Appending data to a sheet, has proven to be less performant than hoped; each queue job increases in memory and time because unfortunately it will have to reload all data in memory; it's not like csv which just can append new data to the end of the stream. Things will change in next releases around queuing bigger exports.

However for imports it might be a possibility in the future. I've havent given it any thoughts yet, but I think it will suite that feature pretty well.

[andrewskm]

I get that.

The only memory problems I came across with the queue was during formatting of ranged cells.
So my workaround was to just format the entire column instead, which is fine for what I'm working with, but might not be for others.
I have been able to queue an export with 14k rows by 10 columns with a 1000 chunk size locally with ease, so no complaints here.

[patrickbrouwers]

Yeah indeed 14k with 10 columns won't be an issue. Of course really dependent on how performant the user implementation is, like you mentioned things like ranged cell formatting will use lots more than entire column.

[atymic]

I think this makes more sense for importing, because the current method runs each job one at a time (chained) With batching, we could have the same after job triggers /etc, but concurrent processing of chunks.

What do you think @patrickbrouwers

[patrickbrouwers]

As I mentioned in a previous thread, it will most likely be added to imports in the future, however no specific plans yet. Batch importing also assumes that the order of importing doesn't matter, so it's something that needs to be carefully thought out. Also as this is a Laravel 8+ feature, it won't be something that will be added as a default (until we make a clear cut off point, which most likely will be 4.0)

[fabricecw]

Hey @patrickbrouwers
Do you currently have any intensions to dig deeper into this topic? We're also facing limits with exports > 100k rows. I guess the AppendQueryToSheet is the bottleneck of the current export process. Further, if you have multiple exports running at the same time, the files are written parallel instead of completing the exports in sequence. This means the user with the "earliest" export waits a lot longer.

If you let us know about your ideas (if you already made some thoughts) we're ready to support here.

[patrickbrouwers]

You can check this PR: #3974
Unfortunatly I can't merge it as-is, as it doesn't work < Laravel 8

[patrickbrouwers]

Also as previously mentioned, I wouldn't recommend using the built in queuing for exports. PhpSpreadsheet doesn't support appending rows to an existing file without reading the entire file back into memory (as far as I know), so the memory bottleneck will increase on each chunk, I'd recommend creating a long running queue job and just do an Excel::store in there.