Fill Color vs Conditional Formatting

Topics: Developer Forum
Jan 28, 2015 at 4:07 PM
At the moment the excel workbook I am generating via PHPExcel takes about 30 seconds. I found that if I remove the coloring steps in the row iterators that the generation is down to 10 seconds.

I would really prefer to keep the color, but I'm wondering how to do so efficiently. Would using conditional formatting be faster than applying fill color? I am worried it would be about the same.
Jan 28, 2015 at 4:35 PM
Conditional formatting is slower than standard styling, and can only be applied to individual cells, so it's slower and more memory hungry; and if you wouldn't use conditional styling for cells in MS Excel, then don't use it in PHPExcel. Only use it if the style to be applied is dependent on the data contained in that cell (e.g. negative numbers in red, positive in green, zeroes in black, etc).

Wherever possible, apply styling to whole ranges of cells in one go rather than to individual cells, so if you were applying the same style to cells A1, A2, A3, A4... An, then change your code to apply that style to the range A1:An.

If you're applying several style elements to a cell or range of cells, then applyFromArray() is more efficient than applying each style element individually
Jan 29, 2015 at 5:08 PM
Thank you for your response.

For some of the sheets I basically need to alternate row color. So it sounds like conditional formatting will be the way to go once the data is written and I can select the range of cells I wish to color.

For one of the sheets however, I need to color the row only if one column's value (on said row) is less than or greater than the value in another column (still same row). Should I still use PHP to evaluate this and fill color case by case, row by row? Or is there a tricky formula that would make conditional formatting viable in this scenario?

Jan 29, 2015 at 5:23 PM
Standard formatting would be better for your alternating rows, because you can set the range for each style as A1:Z1, A2:Z2, A3:Z3, etc, so at least you're setting several cell styles each time

Conditional formatting is more appropriate for your second case, where the style is dependent on the value of one of the cells in the row