Cell cache not working?

Topics: User Forum
Sep 22, 2010 at 9:05 PM
Edited Sep 22, 2010 at 9:37 PM

My apologies if this has been covered before. I tried searching but cannot find an exact answer.

I've got a large spreadsheet (32,000 rows * 50 columns, all on 1 worksheet) that are all styled in one way or another (currency, colors, alignment, etc). I've got 1GB allocated for PHP, but PHPExcel keeps running out of memory. I've been reading up about PHPExcel's difficulties w/ memory (1k per cell, etc), and I downloaded the newest PHPExcel (1.7.4) to try out the new Cell Cache stuff.

Unfortunately, I'm either doing it wrong, or the spreadsheet is still too big. I've tried cache_in_memory_serialized, cache_in_memory_gzip, cache_to_phpTemp, and cache_to_discISAM. Everything hits the memory limit. Here's how I'm using it:

 

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

$oPHPExcel = new PHPExcel();
...

 

This is the correct way to use caching, right?

[edit]
I've tried using the latest SVN copy of PHPExcel as well (PHPExcel-61245), but it didn't work either. :(
[/edit]

The only other thing I can think of to mention is that we're using the Excel5 writer...

Are there any other tricks I may not know of for further optimizing the spreadsheet?

 

Finally, I'd like to say a huge thanks for making PHPExcel so amazing. It's been a life saver at work and a huge improvement over the old way we did things. Thanks a million!

Coordinator
Sep 22, 2010 at 9:53 PM
Edited Sep 22, 2010 at 9:54 PM

You are using cell caching the correct way, but 32,000 rows x 50 columns is 1.6 Million cells... without using caching, that would require 1.6GB for the cells x 2 overhead for the writer + 10-25MB code footprint, so about 3.2GB. Using diskISAM, you could expect 535MB for the cells x 2 overhead for the writer + 10-25MB code footprint, so about 1.1GB.... remembering that this is still just a rough estimate... and performance will be slow when working with that many cells

 

I'm still looking at ways in which the writer (and reader) overhead can be reduced, and if I can reduce the overall cell memory usage still further (possibly by breaking the OO paradigm), and also at other cell caching locations such as redis (which I've heard described as memcache on steroids) and shmem, but haven't managed to achieve any real success yet. However, since the release of 1.7.4, my main priority has been new functionality (such as the work on charting, and additional readers/writers); so unless I stumble on a new memory saving technique by chance, it's likely to be a while before I can spend some serious time trying to reduce memory overheads still further.

Sep 23, 2010 at 3:03 PM

Thanks for the quick reply, and thanks a million for your work on this great library.

I didn't know the footprint was so big even when using caching. I wish you the best of luck in trying to find ways to minimize that. That's just crazynuts...

The box only has 2GB RAM on it, and even if I kept the diskISAM caching and bumped the memory limit to 1.5GB I'm sure another user would come along and want a 60,000 row spreadsheet. I guess for now I'll have to go back to generating 2003XML spreadsheets from scratch...

But again, I don't want to sound ungrateful. Thanks for all your hard work to make PHPExcel the best PHP-based library around!