Memory validation?

Topics: Developer Forum, User Forum
May 6, 2010 at 3:46 PM
Edited May 7, 2010 at 4:22 PM

PHP Version 5.3.0

Windows XP

PHPExcel version 1.7.2, 2010-01-11

Hi, first off, a warning: I am new to excel, let alone phpexcel. I just downloaded phpexcel today and I was looking through it and reading the test examples. I ran into an error when trying to load an excel template my company uses.



/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';

if (!file_exists("Product_Add_Sheet2.xlsx")) {
    exit("We need the original to copy first.\n");

echo date('H:i:s') . " Load from Product add sheet\n";
$objPHPExcel = PHPExcel_IOFactory::load("Product_Add_Sheet2.xlsx");

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 71 bytes) in C:\xampp\htdocs\downloads\excel\Classes\PHPExcel\Worksheet.php on line 2380

The file does not contain much data it has 4 sheets, about a total of 50 columns between them, and only 2 rows of data.

Some of the columns have validation such as pulldown data and what not.

I am not sure why this is not working. Im not sure if this has anything to do with it but earlier I was trying to manually save the file in xml spreadsheet format I was given an error stating that it was password protected. Is that causing phpexcel to be unable to do its thing?

May 7, 2010 at 4:07 PM
Edited May 7, 2010 at 4:11 PM

So, despite knowing very little about excel, im trying to debug this...

I believe it is being caused by the drop down lists and other validations in the spreadsheet. I have traced the error to the data validations loop in the reader/excel2007.php file. When I print_r the data validation it returns:

SimpleXMLElement Object ( [@attributes] => Array ( [type] => list [allowBlank] => 1 [showInputMessage] => 1 [showErrorMessage] => 1 [sqref] => R16:R65536 ) [formula1] => Years )

It then goes through all of cells R16 - R65536 in the loop, running out of memory about half way.


I know nothing of excel so why is it set to a range of cells 16-65536 when theres only 1 row of data?

And does anyone have an idea of what is happening here? Is the way the validation formula was implemented causing issues when trying to input in the document, or is that a wrong path to be considering.

May 7, 2010 at 8:51 PM

I had a similar issue. I have a few columns in a sheet that had drop down validation on them. That is to say the ENTIRE column from row 2 to row 65536 had the validation pick list applied to them regardless of whether they contained data or not. I think, for whatever reason, PHPExcel wants or needs to read/store all of those validation defs for each and every cell and, eventually, runs out of memory.

To resolve this I simply re-defined my pick list data validation on the first 1000 rows of my columns instead of the entire column. The problem went away. Is this optimal? No, but for my purposes it's unlikely a user will enter in 1000 rows at once so it is effective.

Hope this helps.

May 12, 2010 at 8:33 PM

Hmm well, this could be a big problem for me.

See, right now we have a linux based database system called filepro. We have an export and import functionality which writes the data to a csv then the users can use a custom program to load the csv into an excel template and then make any changes, export from the program to csv again and load it back into the db.

We are changing to a php/mysql platform. My boss no longer wants the whole export to csv then to excel, then back to csv then into the db approach. He wants PHP to simply export to excel then import back in.


I figured this would help me accomplish that, but if I can not read in the template I do not know if I can get it working. I suppose my other option would be to rebuild the template in php, but that seems like a pain. Does anyone have any idea how I can fix or get around this issue  without defining how many rows will be effected by the validation?

May 12, 2010 at 9:41 PM

The next release, due on Monday, includes a number of changes to improve the memory footprints of both PHPExcel itself, and of workbooks loaded into PHPExcel. This won't fix every problem with memory usage in PHPExcel; but the new class autoloader will reduce the memory footprint of the PHPExcel script, while cell caching allows you to reduce the memory required for each cell within your workbooks, and being able to clear the self-references within the PHPExcel objects should allow scripts to unset workbooks or worksheets cleanly.

  • The class autoloader only loads those classes that are actually required by your scripts.
  • You will need to configure cell caching if you want to reduce memory usage (because the default still holds all cell objects in memory as at present), and there is a speed cost in this.
  • Executing a $objPHPExcel->disconnectWorksheets() call before unset($objPHPExcel) will now clear PHPExcel objects from memory.

There are other performance and memory related changes that still need to be made, but this process is ongoing... we estimate an average of 1k/cell overhead for any PHPExcel object: i'd expect 4 sheets with about 50 columns between them, and only 2 rows of data to take no more than 100k of memory. I'll take a look at the data validations loop issue in due course: it should probably set to the max number of rows in the worksheet rather than the 65536 maximum row limit for an Excel5 worksheet, and adjust as necessary when new rows were inserted. The 65536 is probably also a problem for Excel2007 where that maximum limit is 1,048,576.

May 13, 2010 at 2:20 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.