PHPExcel memory still exhausted even with cell caching - other solutions

Question

<b>Fatal error</b>:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 78 bytes) in <b>/var/www/leanne/api/classes/PHPExcel/CachedObjectStorage/PHPTemp.php</b> on line <b>66</b><br />

Hi,

I asked this question a few days ago and was advised to change my code and to use cell caching. While I have changed my code and attempted to use cell caching, I am still getting a memory error. I am desperate to find a solution to this.

Can anyone advise on which caching method would be best for writing excel files ranging between 1 to 100,000 rows of data? If cell caching doesn't work, I may need to use another solution that allows me to append to an xls file in the same way I do with the CSV version.

An example of my current code is below:

if ($count_prods > 0) {

    $format = strtolower($export_data['output']);
    $temp_file_location = '../temp/exports/products/';
    $filename = 'data_' + $shop->ID . '_' . $export_id . '_test';
    $separator = ',';
    $endrow = "\n";

    $fh = fopen($temp_file_location . $filename . '.csv', 'a');

    /*$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
    $cacheSettings = array( ' memoryCacheSize ' => '8MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);*/

    $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_sqlite;
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

    $objPHPExcel = new PHPExcel();

    $rowID = 2;
    $counter = 1;
    for ($i = 0; $i < $count_prods; $i += $batchlimit) {
        $csv = '';
        $limit = $batchlimit * $counter;
        $start = $i + 1;
        $productData = $productExport->getProductData($start, $limit);

        if ($counter == 1) {
            //get column names
            if ($format == 'csv') {
                $column_titles = implode(',', $productExport->product_fields);
                $column_no = count($column_titles);
                $csv = $column_titles . $endrow;
            } else {
                $objPHPExcel->getActiveSheet()->fromArray($productExport->product_fields, NULL, 'A1');
            }
        }

        //loop through data export array
        foreach ($productData as $product_id => $product_details) {
            $columnID = 'A';
            foreach ($product_details as $key => $value) {
                if ($format == 'csv') {
                    $csv .= '"' . str_replace('"', '\'', $product_details[$key]) . '"' . $separator;
                } else {
                    $objPHPExcel->getActiveSheet()->setCellValue($columnID . $rowID, $product_details[$key]);
                }
                $columnID++;
            }
            if ($format == 'csv') {
                $csv = rtrim($csv, $separator);
                $csv .= $endrow;
            }
            $rowID++;
        }
        if ($format == 'csv') {
            fwrite($fh, $csv);
            $csv = '';
        }

        $counter++;
    }
    if ($format == 'csv') {
        fclose($fh);
    }

    //if  XLS file 
    if ($format == 'xls') {
        //$objPHPExcel = $objReader->load($temp_file_location . $filename . '.csv');
        // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //$objWriter->save($temp_file_location . $filename . '.xls');
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($temp_file_location . $filename . '.xlsx');
    }

Answer 1

PHPExcel is an excelent library, and I just love it. However, to support all those Excel features, it eats a lot of memory.

You are doing simple reading and writing, without any advanced Excel stuff.

Give Spout a try. https://github.com/box/spout

From the main description: Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 3MB).

For exporting or importing from excel it is at least 10x faster and eats very little memory, as it is streaming data to and from the file

I have successfully replaced PHPExcel for some very big Excel files for which I only needed exporting data.

Answer 2

you could increase the memory and time allocated to the script using:

ini_set('memory_limit', '2048M'); set_time_limit('1200');