Saving Excel2007 fails

Sep 1, 2010 at 6:55 AM
Edited Sep 1, 2010 at 7:16 AM

Hello everybody.

 

I have plugged in your Library into CodeIgniter and everything seems to work like a charm (the examples, smaller tests by myself etc).

Here is what i try to do:

  1. Load data from a MsSQL Server (via Propel)
  2. Iterate over the data
    1. Print each chunk
  3. Save the file
  4. Send it to the user

(I also tried combining parts 3. & 4. via php://output)

I get a maximum_runtime_exceeded exception and the corresponding apache2 process is using

  • CPU
    • 100% for about a minute
    • 5-10% after that
  • RAM
    • 20-50% for about a minute
    • 100% after that

I let those threads live for around 5 to 30 Min (adjusting max_runtime in php.ini if needed) and there was no end.

When i insert a die(); in my script (before 3. starts) the script finishes after around 1:20.

Fetching the data (die(); after part 1) takes around 6-10 seconds.

 

I already tried Excel2007, Excel 2005 and HTML but none of them worked.

Document details:

  • Around 2k rows
    • Actually there are 10-15 rows that a repeated over and over again with different texts inside the cells
  • Around 6-10 columns
  • Huge amount of styling (Everything, from background-colors to underline)
    • The styling is the same every iteration, and every iteration, as mentioned above, is around 10-15 rows
  • One worksheet

Layouting is a guideline and can NOT be altered.

 

I have some ideas to speed up the saving process (or at least make it finish in an acceptable amount of time):

  • Templating
    • Can i read a template, parse my data, append the compiled template to my worksheet and repeat?
  • Flushing
    • Is there a possibility to append the current state of the worksheet to a file and repeat?
  • Caching
    • As runtime for filling the cells is only around one minute, maybe i can use any of the caching mechanisms to ease the saving?

Please tell me, what idea, if any, would work. Do you have any other suggestions?

[EDIT]

Sorry, i forgot to mention some hardware details:

  • Development Server
    • Ubuntu 10.04 Server inside a VM
    • 300 MB of RAM
    • 40 GB HDD
    • PHP 5.3 with MsSQL Extension
  • Production Server
    • Debian 4.0 inside a VM
    • Dynamic RAM, expect it to be around 1 GB
    • 50 GB HDD
    • PHP 5.3 with MsSQL Extension
  • MsSQL Server

[/EDIT]

Thanks for your time,

Timetrick

Sep 1, 2010 at 9:36 AM

Hi again,

I'm sorry that i've stolen your time.

I fixed the issue and it was not a phpExcel one.

I just messed up the row counting and had 3.4 * 10 ^ 28 rows. (and this was ... not what i wanted...)

 

Thank you for your incredible helpful tool.

Coordinator
Sep 1, 2010 at 11:42 AM

I have some ideas to speed up the saving process (or at least make it finish in an acceptable amount of time):

  • Templating
    • Can i read a template, parse my data, append the compiled template to my worksheet and repeat?

Perfectly feasible, and various users do load templates that they can then populate with data.

 

  • Flushing
    • Is there a possibility to append the current state of the worksheet to a file and repeat?

Not really practical: Excel2007 files are zipped, and this can't be handled via an append, only when the "sub-files" have been completely generated can the whole be zipped. Could theoretically be done for the different stream in an Excel5 file, would require a complete rewrite from scratch of the writer.

 

  • Caching
    • As runtime for filling the cells is only around one minute, maybe i can use any of the caching mechanisms to ease the saving?

The Cell Caching methods reduce memory requirements, but at a cost in execution speed, so can't be used to speed up execution

 

Sep 1, 2010 at 11:52 AM

Thank you for your information,

though i do not understand how i can use templating when the resulting file is different in length each time.

Iam parsing a "database dump" to the excel file (with improved styling...)

Lets say, there are 15k records in the database, and each record results in 10 rows of data (from the database...)

PHP loops through all those records and parses these 10 rows for each.

Can i take these 10 lines as a template and load the template, fill it, append it, load fill and append it again and so on?

If i can, how can i do so, and would it be faster than just appending the lines over and over again?

 

 

Thanks,

Timetrick