Getting OOM from large dataset

Question

I'm trying to create a spreadsheet (XLSX) from a array containing 60k entries. When it exports, it gives me a spreadsheet with a PHP OOM warning on it nothing else.

    $spreadsheet = new Spreadsheet();
    $spreadsheet->setActiveSheetIndex(0);
    $activeSheet = $spreadsheet->getActiveSheet();

    $rowIndex = 2;

    foreach ($this->_values as $_val){
        $activeSheet->setCellValueByColumnAndRow(1, $rowIndex, trim($_val['item1']));
        $activeSheet->setCellValueByColumnAndRow(2, $rowIndex, trim($_val['item2']));
        $activeSheet->setCellValueByColumnAndRow(3, $rowIndex, trim($_val['item3']));
        $activeSheet->setCellValueByColumnAndRow(4, $rowIndex, trim($_val['item4']));
        $activeSheet->setCellValueByColumnAndRow(5, $rowIndex, trim($_val['item5']));
        $activeSheet->setCellValueByColumnAndRow(6, $rowIndex, trim($_val['item6']));
        $activeSheet->setCellValueByColumnAndRow(7, $rowIndex, trim($_val['item7']));
        $activeSheet->setCellValueByColumnAndRow(8, $rowIndex, trim($_val['item8']));
        $activeSheet->setCellValueByColumnAndRow(9, $rowIndex, trim($_val['item9']));
        $activeSheet->setCellValueByColumnAndRow(10, $rowIndex, trim($_val['item10']));
        $activeSheet->setCellValueByColumnAndRow(11, $rowIndex, trim($_val['item11']));

        $rowIndex += 1;
    }

    $spreadsheet->garbageCollect();

    $writer = new Xlsx($spreadsheet);
    $writer->setPreCalculateFormulas(false);
    $writer->setUseDiskCaching(true);
    $writer->save("php://temp");

    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);

I can't set the memory limit. Not that I don't know how, but it isn't an option because the code is meant to be on a production server. Batching isn't an option as well because the client wants it on one file — Tanaka, Jun 04 '18 at 18:56
Even if you cannot use the proposed actions in your production environment, you should try them to localize the problem. — Adrian W, Jun 04 '18 at 20:51

score 0 · Accepted Answer · answered Jun 04 '18 at 18:33

0

An out of memory error is because your system is, well, out of memory. You can simply increase the amount of memory using something like ini_set('memory_limit', '750M'); assuming your system has the space

Or, if XLSX isn't needed (that is: if you could use a CSV instead, which Excel can still open) you could stream the data to the spreadsheet instead of loading it all into memory at once, then printing. That would look like this:

foreach ($this->_values as $_val){
    for($i=1; $i<=11; $i++) {
        echo trim($_val['item'.$i]);
        if($i<11) echo ",";
    }
    echo "\n";
}

You could also include the CSV header at the top of the PHP file, so the page would prompt a download instead of showing you the contents of the CSV file. This is the CSV header cal: header("Content-type: text/csv");

answered Jun 04 '18 at 18:33

Bing

3,071
6
42
81

Turning it into a CSV does export it, but I would rather prefer it to be in an XLSX format. – Tanaka Jun 04 '18 at 18:58
@Tanaka Did you try just increasing the memory like I suggested in the first paragraph? Does that work for you? – Bing Jun 04 '18 at 18:59
I just saw your comment on the original question. If batching isn't an option and increasing memory (even just for this one file, which is what that `ini_set` function I gave does) isn't an option, I think CSV is your best answer. – Bing Jun 04 '18 at 19:00
Ok, PhpSpreadsheet could not handle creating an XLSX with large datasets. So had to go with CSV. I hope it gets an update in the future. – Tanaka Jun 09 '18 at 15:45

Getting OOM from large dataset

1 Answers1