PHPExcel - Severe performance slowdown when applying number formats

Question

I've profiled my Excel generation code with xdebug and reviewed the grind file. Apparently the majority of my execution time is spent in:

setFormatCode(...)

enter image description here

And the offending code is here:

protected function formatRow($line)
{
    // /*
    foreach ($this->getIntegerColumns() as $column) {
        $style = $this->worksheet->getStyle($column . $line);
        $style->getNumberFormat()->setFormatCode('#,##0');
    }
    foreach ($this->getFloatColumns() as $column) {
        $style = $this->worksheet->getStyle($column . $line);
        $style->getNumberFormat()->setFormatCode('#,##0.00');
    }
    foreach ($this->getPercentageColumns() as $column) {
        $style = $this->worksheet->getStyle($column . $line);
        $style->getNumberFormat()->setFormatCode('0.00%');
    }
    foreach ($this->getDateColumns() as $column) {
        $style = $this->worksheet->getStyle($column . $line);
        $style->getNumberFormat()->setFormatCode('mm/dd/yyyy');
    }
    // */
    return $this;
}

My reports typically take:

getSummaryData: Elapsed time: 4
getInteractionData: Elapsed time: 10
getVideoData: Elapsed time: 2
new CampaignDetailReport: Elapsed time: 0
new CampaignDetailReportWriter: Elapsed time: 0
new write(): Elapsed time: 125

~ 140 seconds

But if I comment out the guts of the formatRow() function, it takes:

getSummaryData: Elapsed time: 4
getInteractionData: Elapsed time: 9
getVideoData: Elapsed time: 2
new CampaignDetailReport: Elapsed time: 0
new CampaignDetailReportWriter: Elapsed time: 0
new write(): Elapsed time: 32

~ 50 seconds

Which is still long, but a substantial improvement (nearly 50% faster).

I've reviewed the answer here: PHPExcel very slow - ways to improve?, but I'm not sure how to implement the fixes. Ideally, I think one solution would be to apply the style to the entire column, not on a cell-by-cell basis. If the column is something like 'cost', I know all the column values will be a float, so it's probably safe to apply the number format to all the cells. Just now sure how to actually do that.

UPDATE:

In response to Mark Bakers answer, I did the following:

foreach($this->getIntegerColumns() as $column) {
        $this->worksheet->getStyle(sprintf("%s%s:%s%s", $column, 5, $column, $this->line))
             ->getNumberFormat()
             ->setFormatCode('#,##0');
    }

Which styles all the columns of type integer for all the cells within the column. All my report metrics start at 5 and goto this->line, which is internal counter. This is working well so far. It took about 4 additional seconds to completely style one sheet (I have 3x sheets in my report). So at that rate I should be able to style all sheets and only incur an additional ~12 seconds of execution time, taking it to ~65 vs ~125 !!!

score 4 · Accepted Answer · answered Mar 07 '13 at 21:05

4

Take advantage of the fluent interface.

Apply your formats to a range of cells where possible:

$this->worksheet->getStyle('A1:A21')
    ->getNumberFormat()
    ->setFormatCode('0.00%');

Use the style's applyFromArray() if you want to apply several style settings to a cell (or range of cells) in one go.

All of those techniques will help improve speed.

answered Mar 07 '13 at 21:05

Mark Baker

209,507
32
346
385

Great - I moved my formatting code a bit higher up in my class hierarchy and performed what you suggested. Working a lot better so far - have to update all my "sheets" to use this. Thanks. Updated my answer as well w/the actual code. – mr-sk Mar 07 '13 at 22:13
I seem to recall a planned feature to implement as follows: $this->objPHPExcel->getActiveSheet()->getStyle($columnIndex)->getNumberFormat()->setFormatCode('$#,##0.00'); Did this not happen? – Ross May 09 '14 at 14:47
No, row and column styles are not yet available – Mark Baker May 09 '14 at 15:07

PHPExcel - Severe performance slowdown when applying number formats

1 Answers1