2

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.

Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
  • So which of the cell caching options for reducing PHPExcel memory usage have you tried using? We provide and document this feature precisely to allow working with larger spreadsheet files than memory would normally permit – Mark Baker Oct 22 '13 at 16:17
  • I have specificly picked one sheet to be used and converted the file from xlsm to xlsx... this has atleast for now made it possible for me to use the tool. Yay! eg: setLoadSheetsOnly( $sheetname ); – Jimmyt1988 Oct 23 '13 at 14:58
  • @MarkBaker - It's an honour to get to "speak" to the people who make this stuff :) – Jimmyt1988 Oct 23 '13 at 14:59
  • I need to read and Excel file, row by row. It keep running out of memory when the file size is bigger then 11MB. Both `PHPExcel_CachedObjectStorageFactory::cache_to_discISAM` and `PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp` are unable to solve the problem. My PHP memory limit is 134217728 bytes. – hongster Dec 23 '13 at 11:04

2 Answers2

4

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.

SWilk
  • 3,261
  • 8
  • 30
  • 51
  • PHPExcel has documented features to reduce memory usage, such as cell caching; and the SQLite caching option is particularly efficient.... did you try using that? – Mark Baker Oct 22 '13 at 16:22
  • Note that the structure of an Excel file (either BIFF of OfficeOpenXML is not conducive to reading files row by row/cell by cell – Mark Baker Oct 22 '13 at 16:23
  • You are right about the BIFF format, which have all the rows defined first, and then comes cells data. But when you use event based reader like Apache POI, you get an event when the reader reads one row object or one cell object. And you do not have to load to memory all the – SWilk Oct 23 '13 at 07:32
  • Sorry, hit enter and then missed 5 minuts edit time. – SWilk Oct 23 '13 at 07:50
  • What do you mean not conductive? When you use event based reader like Apache POI, you get an event when the reader reads one row object or one cell object. So, you can react just for the cell records, they are stored in order (or at least POI reads them in order), so when you get first cell of the next row, you can store the last full row in db and move on to the next one. As for XML formats, theycan be read by event based reader without building dom, and each cell is child of each row. The only problem is with Shared String Tables, which can be huge, and have to be loaded first. – SWilk Oct 23 '13 at 07:50
  • Point #1. The PHPExcel Readers use SimpleXML, not XMLReader (that's being changed for version 2). Point #2. What I mean is that information about any cell is spread across several XML files, not just one file and the shared data file (you mention shared the string table, but that's just for cell content)... styles are in another, image data another again, etc; so it may entail reading from over 6 or 7 files in all to retrieve all the information about a cell. – Mark Baker Oct 23 '13 at 08:35
  • Point #3. PHPExcel was designed from the start as an in-memory representation of a spreadsheet (as described in the documentation), because it evolved from the old SEW reader (BIFF files), but to support a range of formats including OfficeOpenXML, Gnumeric and OASIS as well; its design principle is to load the whole spreadsheet into memory for ease of working with different formats, or for creating a spreadsheet from new, and providing both read and write between different formats; so simply reading a cell at a time as requested isn't always practical – Mark Baker Oct 23 '13 at 08:39
  • Point #4. As users progressed to working with larger and larger data volumes, cell caching was introduced to help reduce the memory overheads, offering a range of different options with varying trade-offs between memory reduction and speed overheads – Mark Baker Oct 23 '13 at 08:42
  • @MarkBaker I have tried using cell caching with Sqlite3, assuming setting it with the PHPExcel_Settings singleton is all I need to do, but I'm still running out of memory, with memory set to -1, and my computer has plenty to spare so something is going on. " simplexml_load_string(): Memory allocation failed : growing buffer" – Navarr Oct 23 '15 at 16:53
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');
BadHorsie
  • 14,135
  • 30
  • 117
  • 191
  • Fatal error: Maximum execution time of 30 seconds exceeded in... how do I extend execution time ^_^... and although a quick fix will do to make me happy.. i'm not sure how much choice I will have on a live server... but maybe I can move my site to somewhere where I get more choice hehe – Jimmyt1988 Oct 22 '13 at 12:31
  • @JamesT : ini_set('max_execution_time', 0);...maybe you should consider a more resources friendly input format, often good old csv is sufficient ;) – cypherabe Oct 22 '13 at 12:39
  • @JamesT Yep, it is perhaps not a wise choice increasing the memory limit too much, and obviously it's not really a great solution but the only other thing I have found works is staggering the processing of things with PHPExcel if you can. So for example, I had a set of database rows I was trying to output to an Excel file, and I had to create an ajax script which would loop through and do it in chunks, to stop it running out of memory. – BadHorsie Oct 22 '13 at 12:46
  • Yes, shame.. i'm not sure it's an option... may have to disappoint the client with this one hehe. "guys, export that crap to csv please"... i'll say it in those words – Jimmyt1988 Oct 22 '13 at 12:46
  • What's the content of the xlsm file like? – BadHorsie Oct 22 '13 at 12:47
  • Extensive.. It has a few images, 5 sheets, a fair bit of styling and macros that I assume are brought in in some kind of way to the array output. – Jimmyt1988 Oct 22 '13 at 12:49
  • Sounds like it would be a lot to processing. – BadHorsie Oct 22 '13 at 13:19
  • Let's say I am only interested in one sheet of this bad boy... Is there a way to focus in on the 1 sheet or does the PHPExcel still require the entire file in memory... looks like I really do just need csv. I don't have enough time for the above solution, doh. – Jimmyt1988 Oct 22 '13 at 14:00
  • Yes, I think you can go to a specific sheet. http://stackoverflow.com/questions/4170275/how-do-i-read-sheet-two-of-an-xlsx-file-with-phpexcel – BadHorsie Oct 22 '13 at 14:30
  • PHPExcel can be configured to read only one sheet, or several individual sheets from the file: this is all fully described in the user documentation for the Readers – Mark Baker Oct 22 '13 at 16:18
  • See see https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/ReadingSpreadsheetFiles/04-Reader-Options.md for details – Mark Baker Oct 22 '13 at 16:24