Why PHP SpreadSheet doesn't free memory after writing to disk?

Question

I have a function, that write data and apply some style to an Excel file using PHPSpreadSheet (with Symfony):

use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
function writeExcel(array $sheets_data, string $excel_path): void
{
    // Load pieces
    list($data_1, $data_2, , $data_3) = $sheets_data;

    // write data to excel
    // load template
    $reader = IOFactory::createReader("Xlsx");
    $spreadsheet = $reader->load('some_template');
    $sheet_1 = $spreadsheet->getSheet('first_sheet');
    $sheet_2 = $spreadsheet->getSheet('second_sheet');
    $sheet_3 = $spreadsheet->getSheet('third_sheet');

    // piece 1 style
    $sheet_1->fromArray($data_1, NULL_VALUE, 'A3');
    self::formatSheet1($sheet_1, count($data_1));

    // piece 2
    $years_count = count($data_2['values']);
    $appel_name_row = 13 + $years_count + 4;
    self::fillSheet2($sheet_2, $data_2, $appel_name_row);
    self::applySheet2Style($sheet_2, $years_count, $appel_name_row);

    // null presta
    $sheet_3->fromArray($data_3, NULL_VALUE, startCell: "A3");
    SdisData::formatSheet3($sheet_3, count($data_3), -1);

    $spreadsheet->setFirstSheetIndex(0);
    $spreadsheet->setActiveSheetIndex(0);

    // Write sheet
    $writer = new XlsxWriter($spreadsheet);
    $writer->save($excel_path);
}

Each time this function is called, it writes to each sheet between 20 and ~1000 rows.

My problem is, each time I call this function, it takes ~500mb of RAM, and even after the function has finished, memory isn't freed (It is problematic, as I have to call this function 1-120 times consecutively).

I tested it with this code:

$start = memory_get_usage();
self::writeExcel($pieces, $excel_path);
echo 'Memory increase: ' . (memory_get_usage() - $start);

Which outputs:

Memory increase: 509151792 (~510mb)
Memory increase: 509973088 (~510mb)
Memory increase: 537694520 (~540mb)

After trying multiple things, I realized that by removing the part where I write the file to disk (the last 2 lines of the function), memory is drastically less used, as my code outputs:

Memory increase: 14405696 (~14mb)
Memory increase: 12522072 (~12.5mb)
Memory increase: 1256720  (~1.2mb)

So my questions are, why is so much memory used when writing the SpreadSheet to disk, why is memory used only when writing to disk,and most importantly, how can I use less memory/free it ?

EDIT: I modified the template that is loaded (by reducing the range to which style is applied), and now PHPSpreadSheet uses less memory - but still, it doesn't free it once it is done writing the file, which is a big issue for me.

user3045127 · Answer

Please be aware that PHPSpradSheet keeps a full PHP-representation of your excel-File in memory. Multiply cells * rows * formatting etc. to estimate a raw size.

You seem to load a template:

$spreadsheet = $reader->load('some_template');

How large is it? If you want to find out what's happening where go for xdebug, enable profiler and use qcachegrind to identify where time and memory are consumed.

My guess is that when you call $writer->save($excel_path); the template is loaded and your changes are applied.

Why PHP SpreadSheet doesn't free memory after writing to disk?

1 Answers