Worksheet caching?

Topics: Developer Forum, User Forum
Jan 9, 2012 at 1:31 PM

Is there a way to cache built worksheets to be used in different excel documents? 

I noticed PHPExcel_Worksheet is dependent on the parent, a PHPExcel object. At least, that's been my experience. I've tried creating independent PHPExcel_Worksheet objects, but kept running into errors with formatters, etc, with undefined object ($this->getParent()->blah()).

My dilemma:

I have a list of about 50 or so users, with commission info. I'm generating a document, for each user, with their detailed info. The issue: some of these users are managers, and their document is to include people under them. An excel document is taking a good ~2 seconds *per person* to create. I'm trying to find a way to optimize this the best way I can. I'm looking for a way to create a document off cached, or previously generated, excel worksheets. But, I'm running into trouble. A worksheet is tied to a specifical PHPExcel parent, even though PHPExcel has an addSheet() option. There isn't any setParent() option in PHPExcel_Worksheet. I've tried overloading, and adding my own, but that actually didn't work too well, either.

I need some way to cache generated Excel Worksheets. Is there any way to do this?

I've tried all sorts of ways: saving $activeSheet to an array, saving it to a Zend_Cache repository, overloading PHPExcel_Worksheet to add setParent(). Nothing's working.

Coordinator
Jan 9, 2012 at 5:45 PM

No, Worksheets cannot exist independently of workbooks in MS Excel, nor in PHPExcel. A lot of information in Excel (such as formatting details) is held at workbook level, and only linked by a reference id from the individual worksheets or cells... and this behaviour is reflected in PHPExcel.

Is it possible to create a worksheet "standalone" or clone a worksheet from a template workbook, and then attach it to another workbook using the addExternalSheet() method.

Jan 9, 2012 at 7:30 PM
Edited Jan 9, 2012 at 7:31 PM
MarkBaker wrote:

then attach it to another workbook using the addExternalSheet() method.

 

You know, that may have actually worked for cached worksheets:

if ( ($activeSheet = $this->cache->load($key)) == true ) {
    $excel->addExternalSheet($activeSheet);
    continue; 
}

In my initial local tests, that actually worked. Running some larger scales now. Thanks!