Excel spreadsheets with PHP – a PHPExcel overview

Writing Excel spreadsheets with PHP using PHPExcel:  The pros and cons

PHPExcel is a php library for reading and writing excel spreadsheets.  It is very well documented and has good community support.  When searching the web for an open source solution for writing spreadsheets in PHP I reviewed everything from PHPClasses to Pear and finally I came across this package.  It’s the most up to date looking project out there for excel spreadsheets with PHP.  I didn’t want to use a project that was in the twilight of its development cycle so I opted to try PHPExcel.  Here’s a a brief overview of what I found:

Pros – The good points:

  • Very well documented.
  • Good examples
  • Support forum with active participation.
  • Supports many of the builtin excel functions.
  • Easy to style
  • Supports modern Excel formats plus csv, html, and pdf
  • Write spreadsheets
  • Read spreadsheets
Cons – what I couldn’t stand:
  • SLOW….  This library takes considerable time to produce a spreadsheet when memory conserving disk caching is used.  Consider running your process in the background.
  • Memory Intensive  PHPExcel offers disk caching to reduce its in memory size, but it basically is an in memory spreadsheet and must load completely into memory while being before being written to file.  This can considerably hinder your performance as the spreadsheet object and writer object can take over 25 mg of ram while the program is running with an additional 1 kb of ram per cell loaded in memory.  For an example of how this can impact your server or hosting service consider the load of a 50 column 10000 row spreadsheet which would take 525 mb of ram to load when writing to file.  While its possible to set the scripts memory high enough to handle this you’ll find that if several of these scripts try to run at the same time you’ll have a mess that could crash your server.
  • Write to file is batched processed at the end of the script and happens all at one time.  Regardless of your disk caching settings the entire spreadsheet is loaded into memory and then written to the file.  This creates a serious bottleneck if you have a large file and will oftentimes crash your script.  The only exception to this is when PHPExcel writes a csv file in which it allows for a more granular approach to memory to file writing.
Workarounds that will reduce memory usage and improve speed.
  • Set a cells value explicitly.  Setting the cells value and data type explicitly is much faster than writing cell values for PHPExcel to determine the data type of.
  • Disable formula precalculation by setting it to false.
  • Use disk caching.
  • Don’t try to use it to create reports from large data sets.
In summary:  If you are trying to read and write to small spreadsheets with less than two hundred and fifty thousand fields or cells then this library will be a good fit for your project.  It’s great for summary reporting!!  However if you’ve got extremely large record sets that you need to send to a spreadsheet you’ll need a different approach.  I’ll be writing about how we did that in the next blog post.
Stay tuned and may the source be with you!!
– Joel