即使使用单元缓存，PHPExcel内存仍然耗尽 - 其他解决方案

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 = "
";

    $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

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

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