PHPExcel load function running out of memory

Question

When I use this function:

$objPHPExcel = PHPExcel_IOFactory::load($fileName);

on smaller excel files, it takes a while but finally gets an extensive array out into the $objPHPExcel... Unfortuantely when I try it on a slightly larger more complex Ecel file I get:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes)

The file is an xlsm file and is 1.7MB... Does this sound right or is something fishy going on?

I think this is all the code you need. I'm running off of default WAMP set up at the moment locally.

Answer 1

I had the same issue. At our company we need to import huge xls(x) files to our database. We have been using PEAR Spreadsheet Excel Reader, but it is no longer supported and we encountered many bugs with newer files, so we have tried to switch to PHPExcel. Unfortunatelly we did not manage to overcome the memory limit issue. And we spent a lof of time trying to. There is no way you can load 100K rows with columns up to 'BB' with PHPExcel.

It is just not right tool for the job. The PHP Excel builds the whole spreadsheet in the memory as objects. There is no way around this.

What you need is a tool that can read the file row by row, cell by cell.

Our solution was to use Java and Apache POI classes with event model - which does read only operations but is very memory and cpu efficient.

If you only need to support the xml based "Office Open XML" formats (xlsx) and not the old OLE based, then you can process it as XML for your own. The format is not so much complicated if you get into it. Just unzip a file and look at the xmls. You have one file with is string table, and one file with rows and cells per each sheet. You should parse the string table first, and then the sheet data with xml reader (not the DOM).

As far as I know, there is no PHP library that can import large excel files out of the box as of October 2013.

Good luck.

Answer 2

In my experience, PHPExcel runs out of memory during a lot of operations.

You can try upping the memory limit for the script.

ini_set('memory_limit','256M');