Save step-by-step spreadsheets

[fakhamatia]

I have a database for output to XLSX.
The number of records is very high, thousands of records

$spreadsheet = new Spreadsheet();
$spreadsheet->getDefaultStyle()->getFont()->setName('B Lotus');
$spreadsheet->getProperties()->setDescription("ReportID: $reportId");
foreach ($records as $key => $record) {
    $excelArray = [];
    $excelArray[] = $record['someKey1'];
    $excelArray[] = $record['someKey2'];
    $excelArray[] = $record['someKey3'];
    // ....
    $spreadsheet->getActiveSheet()->fromArray($excelArray, NULL, "A" . ($key + 1));
    foreach (range('A', 'Z') as $c) {
        $spreadsheet->getActiveSheet()->getColumnDimension($c)->setAutoSize(true);
        $spreadsheet->getActiveSheet()->getStyle($c . ($key + 1))->applyFromArray(['alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]]);
    }
}
$writer = new Xlsx($spreadsheet);
$writer->save("fileName.xlsx");

I have two problems.
1- Low memory error if the record number is too high:

PHP Fatal error:  Allowed memory size of x bytes exhausted (tried to allocate x bytes) in /var/www/html/composer/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php on line 157

Server don't have enough RAM, so ini_set('memory_limit', '-1') not an option.

2- Saving time is too much at once at the last line

Also, I use cache for saving memory , but saving time was very, very slow.

use Symfony\Component\Cache\Adapter\FilesystemAdapter;
use Symfony\Component\Cache\Psr16Cache;
$psr6Cache = new FilesystemAdapter();
$psr16Cache = new Psr16Cache($psr6Cache);
\PhpOffice\PhpSpreadsheet\Settings::setCache($psr16Cache);

How can I save the excel file step by step?
I save records to HTML format every 10000 records by file_put_contents and very fast and no memory problem.
Thanks if you have any advice and tell me about getting faster and better and optimized.

[MarkBaker]

Unfortunately, native format spreadsheet files are not structured in a linear manner like CSV or HTML files, so there's no simple way to speed up the save, or reducing the memory usage without applying caching. sing caching helps memory usage, but at a cost in speed.

However, your code is being extremely inefficient in setting the column with and style for every row that you write. Move them outside of the write loop, you can apply a style to a range of cells with one call.

$spreadsheet = new Spreadsheet();
$spreadsheet->getDefaultStyle()->getFont()->setName('B Lotus');
$spreadsheet->getProperties()->setDescription("ReportID: $reportId");
foreach ($records as $key => $record) {
    $excelArray = [];
    $excelArray[] = $record['someKey1'];
    $excelArray[] = $record['someKey2'];
    $excelArray[] = $record['someKey3'];
    // ....
    $spreadsheet->getActiveSheet()->fromArray($excelArray, NULL, "A" . ($key + 1));
}

foreach (range('A', 'Z') as $c) {
    $spreadsheet->getActiveSheet()->getColumnDimension($c)->setAutoSize(true);
}
$spreadsheet->getActiveSheet()->getStyle('A1:Z' . ($key + 1))->applyFromArray(['alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]]);

$writer = new Xlsx($spreadsheet);
$writer->save("fileName.xlsx");

This change should speed up writing the data to PhpSpreadsheet, and setting the style for the entire worksheet is also better for the memory usage.