Cell styling causes long delays or high memory

Topics: User Forum
Jun 3, 2014 at 12:45 AM
I originally was doing this with version 1.6.6, and I've since updated to 1.8.0. I am generating a spreadsheet that has over 1400 columns and 300 rows. Most of the columns need borders on the left or right (or both). The web server is running PHP 5.5.

With 1.6.6, I could generate the spreadsheet without borders in about a minute and a half. When I added the borders, it would always time out. The maximum execution time I had it run for was 20 minutes. I was using shared styles and I would have the shared styles ready before the main loop, and inside the loop adding this line caused the timeout when I went to save the spreadsheet:

$xl->getActiveSheet()->setSharedStyle($style, convert_col($col) . ($row) . ':' . convert_col($col) . ($row));

$style was set to one of three shared styles that I created before the loop. The convert_col function just converts a number to the letter equivalent.

I updated to 1.8.0, and it ran faster and used less memory, but it was still failing. I switched the border portion so that inside the main loop I would record which columns needed which borders, and then after the loop I would apply the styles to the entire column using something like this:

foreach ($columns_left as $c) {
$worksheet->getStyle(convert_col($c))->applyFromArray($style_border_left);
}

With this method, that getStyle line would result in an out of memory error with 1GB allocated to the script. The stack trace shows the last call as PHPExcel_Worksheet->_createNewCell() in PHPExcel/Worksheet.php:1205. It looks like it is trying to allocate another 256MB or so, which seems like an awful lot to allocate at once, e.g.:

PHP Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 268435456 bytes) in PHPExcel/Worksheet.php on line 1218

I also tried this to set the borders, and it also resulted in a timeout:

$worksheet->setSharedStyle($left_cell_style, convert_col($c));

What is the most efficient way to apply borders like this to entire columns? It looks like it is failing in the loop above, which goes over a few hundred columns, and the other 1000 or so are still left to be done after that in other loops.