Best Way to Read/Write Excel Files

Topics: User Forum
Nov 29, 2010 at 8:37 PM

I've been using PHPExcel for a while for a utility of mine, but, like most others, have found it slow and memory hungry.  I don't ever care about formulas, formatting, PDFs, HTML output or any of the other niceties of this class, but just need to be able to read and write large Excel files without problems....I'm just using the files to import/export data en masse from existing databases.  Is there a better way out there?  Perhaps a PHPExcel "Lite"?

I do remove most of the PDF code and such just to decrease the overall codebase size, but am still saddled with the speed and memory issues when dealing with more than a few thousand rows.  I used to use the PEAR class, but wound up with all kinds of problems with foreign language character encoding.

I'm just hoping somebody, somewhere knows a better solution.

Coordinator
Nov 29, 2010 at 9:15 PM

First off: yes, PHPExcel is slow and memory hungry, and if you've been reading the postings on this forum over the last , you'll know that we've been working hard on that and have made significant improvements. The latest SVN code has made significant changes in both memory usage and speed: I recently posted the latest comparison figures for different versions of PHPExcel here, and have made even further improvements since that was posted. But because PHPExcel is written in pure PHP, it will always be subject to the same limitations as PHP itself.

Are you using any of the cell caching mechanisms? You say you're not using formulae, so are you setting setPreCalculateFormulas(false) in the Writer?

Removing the PHP code to reduce the overall codebase size will not make the remaining code run faster, nor reduce the memory requirements for your scripts: only reduce the footprint "on the disk". The PHPExcel autoloader only includes those classes that are actually required to execute your script, not the entire code on disk: so the PDF writer won't take any memory unless you're actually writing to PDF.

If you're after an alternative that supports foreign language encoding, and that is fast and has a minimal memory footprint, then I can only think of two options: using PHP's COM extension to access MS Excel itself on a Windows server; or using Ilia Alshanetsky's interface to the LibXL library. However, LibXL is a commercial product.

Nov 29, 2010 at 11:02 PM

Thanks for the reply Mark.  I only remove the extra files to make the download smaller for my customers since I supply the class as a separate zip file for them.  Everything I need is run on various web servers, which are of course mostly Linux, so the LibXL solution won't help.  I have a custom utility for managing inventory in an e-Commerce database.  All I really need is raw data in and out.

I did see the timings and such you're working on; it looks like you've indeed been very busy and it's greatly appreciated.  Do you know when the next revision will be available or released as stable?  Also, is there a post/doc anywhere that lists what switches/options would be beneficial to improve speed/footprint for those like me who don't need every potential feature enabled?

Thanks!

Coordinator
Nov 30, 2010 at 11:16 PM
SirWilliam wrote:

Do you know when the next revision will be available or released as stable?  Also, is there a post/doc anywhere that lists what switches/options would be beneficial to improve speed/footprint for those like me who don't need every potential feature enabled?

 

At the moment, I'm testing some of the changes that affect the calculation engine (unfortunately it means running my barrage of function tests, which take quite some time to check); but Maarten and myself have been discussing next week for a release... assuming I can get any problems that crop up through that testing resolved in time.

The 1.7.5 release will also include the first few files in a new set of user documents and examples, initially going through all of the options available for readers and writers. While not specifically targeted at speed/performance, more at providing comprehensive examples of all the features available, they may give a few ideas on how to get the best performance from PHPExcel.