Edit Spreadsheet without using memory

Topics: User Forum
Sep 22, 2013 at 2:06 PM
I am new to working with PHPExcel, but I have gotten to the point where I can create spreadsheets fresh and from CSV files. The issue that I am running into though is that I am trying to create rather large reports as Excel files that can be downloaded. The CSV data for these files, without the required formatting and formulas, can be over 50MB. Is there a way to create spreadsheets in the filesystem instead of in memory?
Sep 23, 2013 at 10:49 AM
You can minimize the need of memory for PHPExcel using different cache methods it offers.
See section 4.2.1 of the documentation for the implementation, see this topic: https://phpexcel.codeplex.com/discussions/234150?ProjectName=phpexcel to get an idea of earnings according to the methods.
Sep 23, 2013 at 2:11 PM
I tried doing this with the following code:
    // Initialize caching
    $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array('memoryCacheSize' => '32MB');
    \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    // Create the object
    $this->objPHPExcel = new \PHPExcel();
    $this->objPHPExcel->getProperties()->setCreator("VIP");
    $this->objPHPExcel->setActiveSheetIndex(0);
    $this->sheet = $this->objPHPExcel->getActiveSheet();
However, the script still runs out of memory when it reaches the 50mb max execution limit that I have set. Am I implementing the caching incorrectly?

The data in question can be output as CSV with no problem, so it seems to be the Excel methods that are using the memory.
Sep 23, 2013 at 3:25 PM
It seems correct, however, I would not define the memoryCacheSize with this high value: this value represents what php is allowed to store in memory before going to disk.
Note that the method using SQLite3 obtain the largest gain, if your configuration permits, did a test with it.

Try you estimate the need for memory, basic, rule Mark is about 1 K per cell, 30% more on x 64
Sep 23, 2013 at 3:44 PM
Edited Sep 23, 2013 at 3:55 PM
Ok. I have 156000 rows of data that I am trying to write. I reduced the memoryCacheSize to 1MB, and still have the same issue. Execution gets to about 4600 rows and dies. This is the row by row function that writes, in case I should be doing something else to work with the cache or something:
public function processRow($row)
{
    parent::processRow($row);

    echo 'Parsing row '.$this->line_count.' - '.memory_get_usage(false).' of '.memory_get_usage(true)."<br />\n"; //TODO: Delete

    $columnNumber = 0;
    $vals = array();

    foreach ($this->columns as $column) {
        $val = rand(0, 500);
        $vals[] = $val;
        $format = \PHPExcel_Cell_DataType::TYPE_NUMERIC;
        $this->sheet->setCellValueExplicitByColumnAndRow($columnNumber, $this->line_count, $val, $format);
        $columnNumber++;
    }
}
Just to rule out data issues, I'm just making random integers for each column value.
Sep 23, 2013 at 5:13 PM
To provide more information, I ran this with caching on and caching off.

With caching on:
Parsing row 4653 - 52,188,488 of 52,428,800

Fatal error: Allowed memory size of 52428800 bytes exhausted (tried to allocate 345 bytes) in \vendor\PHPExcel\PHPExcel\CachedObjectStorage\PHPTemp.php on line 65

With caching off:
Parsing row 4213 - 48,774,432 of 49,807,360

Fatal error: Allowed memory size of 52428800 bytes exhausted (tried to allocate 8388608 bytes) in \vendor\PHPExcel\PHPExcel\Worksheet.php on line 1123

There is only a small difference in how far the processing goes. What exactly is being cached, and why is so much put in memory?
Sep 24, 2013 at 7:22 AM
For most of the methods, PHPExcel keeps in memory an index to locate cells. In your case the index is larger than the data.
On the other hand, if you use SQLite3 as cache method, you'll see a major fall.
Small test with a workbook that contains a worksheet with 100,000 cells:
In memory: 37.25 MB
phpTemp: 35MB
SQLite3: 1.75 MB
This is to keep the data in memory.
Take into account the additional memory required when writing workbook (between 19 and 24 MB for my test).