Level 1
why dont you use this package https://laravel-excel.com/
Apr 3, 2023
5
Level 8
Impossible to export large amount of data using: Spatie\SimpleExcel
Hello
I try to export a Sql query to excel file , but when the number of rows is bigger than 200k I get no results :
This page isn’t workinglocalhost didn’t send any data.
ERR_EMPTY_RESPONSE
This is my code :
$writer = SimpleExcelWriter::streamDownload('your-export.xlsx');
$starttimer = microtime(true);
$i = 0;
$this->sqlQuery('2022-05-01', '2022-12-15')->lazy() // a function returning the collection result
->each(function ($row) use (&$i, $writer) {
$i++;
$writer->addRow([
'date' => $row->date,
'user_name' => $row->user_name,
]);
if ($i % 100 === 0) {
flush(); // Flush the buffer every 1000 rows
}
});
$writer->toBrowser();
So as I said it works for low amount of data but if it's more than 200K!
any idea ?
Level 73
My guess is that you are running out of memory, why don't you just use MySQL built in write to excel functionality?
https://stackoverflow.com/questions/10295228/exporting-results-of-a-mysql-query-to-excel
Level 8
@Tray2 thanks, I think this is the solution because I tried many packages and it seems not supporting large data
Level 8
I made an example :
$headers = [
'Content-type' => 'application/vnd.ms-excel',
'Content-Disposition' => 'attachment; filename=users.xls',
];
// Create the XLS file using PHP's built-in functions
$output = fopen('php://output', 'w');
fputcsv($output, ['ID', 'Name', 'Email']);
// Use chunking to retrieve and process the data in smaller batches
DB::table('users')->select('id', 'name', 'email')->orderBy('id')->chunk(1000, function ($results) use ($output) {
foreach ($results as $row) {
fputcsv($output, [$row->id, $row->name, $row->email]);
}
});
fclose($output);
// Create a Response instance with the file's content and headers
$response = new Response(file_get_contents('php://output'), 200, $headers);
// Return the response to make the file downloadable
return $response;
but it's not downloading ! it's just displaying the result on the browser ! any idea?
Level 73
@GodziLaravel Of course it does, you fetch the contents of the file here
$response = new Response(file_get_contents('php://output'), 200, $headers);
You need to make it download the file.
You can force it to download
Please or to participate in this conversation.