Multiple tabbed PHPExcel datasets - memory exhausted

Question

Using PHPExcel, I can run each tab separately and get the results that I want, but if I add them all to one excellent, it just stops, there is no error or any thing.

Each tab consists of 60 to 80 thousand entries, and I have 15 to 20 tabs. So, about 1,600,000 entries are divided into several tabs (this number is likely to increase as well).

I also tested the limit on 65,000 lines with .xls using the .xlsx extension without any problems if I ran each tab in which it has its own excel file.

Pseudocode:

read data from db run the PHPExcel process analyze the data for each page (some stylization / formatting, but not very) (each numeric field value is summed in the totals column at the bottom of excel using the SUM formula) save excel (xlsx format)

I have 3 GB of RAM, so this is not a problem , and the script is configured to run without a timeout.

I used PHPExcel on a number of projects and had great results, but having such a large dataset seems to be a problem.

Does anyone have such a problem? work around? tip? etc...

UPDATE:

in the error log --- memory exhausted

Besides adding more RAM to the box, are there any other tips I could do?

Does anyone keep current state and edit excel with new data?

Answer 1

I had the same problem and a google search did not find a valuable solution.

Since PHPExcel generates objects and saves all the data in memory before finally creating a document file, which itself will also be stored in memory, setting higher memory limits in PHP will never solve this problem completely - this solution does not scale very well.

To really solve the problem, you need to generate the XLS file on the fly. What I did, and now I can be sure that "loading SQL results as XLS" works regardless of how many (millions) rows are returned to the database.

It is a pity that I could not find any library that has XLS (X) generation in it.

I found this article in IBM Developer Works that provides an example of how to generate XLS XML on the fly: http://www.ibm.com/developerworks/opensource/library/os-phpexcel/#N101FC

Works well for me - I have some sheets with lots of data and don't even touch the PHP memory limit. Libra is very good.

Please note that this example uses the simple Excel XML format (file extension "xml"), so you can send uncompressed data directly to the browser. http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

If you really need to create an XLSX, things get even more complicated. XLSX is a compressed archive containing multiple XML files. To do this, you must write all your data to disk (or memory - the same problem as with PHPExcel), and then create an archive with this data. http://en.wikipedia.org/wiki/Office_Open_XML

It may also be able to generate compressed archives on the fly, but this approach seems very complicated.