PHPExcel messes around with style.xml when using templates

Topics: Developer Forum
Dec 31, 2010 at 11:24 AM
Edited Dec 31, 2010 at 12:08 PM


PHPExcel changes something in styles when using xlsx templates and resulting file differs. Let's make simple test:

1. create empty xlsx file in Excel2007 and save it as template.xlsx

2. execute this simple php code:


$objPHPExcel = PHPExcel_IOFactory::load("template.xlsx");
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

3. open result.xlsx in Excel2007. At first it looks the same, but try to edit some cell by hitting F2. Background of this cell changes to black!!


When you overwrite style.xml in result.xlsx with this one from template.xlsx it backs to normal.

Am I doing something wrong? Is there any workaround?




I'm working with PHPEXcel 1.7.5

If I change Style/Fill.php line 120 from:

		$this->_endColor			= new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK, $isSupervisor);



it seems to work normal. What do you think about this?

Jan 1, 2011 at 2:43 AM

I'm not sure what to think... I'll run some tests in debug over the next week and see if I can identify the cause of this behaviour.

I've spent a pleasant New Year's Eve reading through the MS documentation on fill colours (while listening to Jools Holland's Hootenanny, so I have been celebrating at the same time). There are some potential issues that I've identified when colours are selected from a theme in MS Excel, and not translated correctly by the Excel2007 Reader. Annoyingly, the colours are referenced by a name in the themes file (eg dk1 and dk2, lt1 and lt2, accent1 to accent6, hlink and folHlink) but by number in the style index (and there's nothing to indicate whether dk1 is number 0 or number 1) and with an optional "tint" that adjusts the colour value (precisely how isn't explained in the documentation or in the ECMA spec). I'm not certain if this is the cause of the behaviour you've encountered - I've a feeling that it isn't related - although the Excel2007 Reader interprets all theme colours incorrectly as black.

According to the documentation, endColor should only be applied when the fill type is a gradient or a pattern fill: for a solidFill (the default fill style) only startColor is used. Of course, not everything in the MS documentation is completely accurate. While endColor must always be set (irrespective of the fill style), it defaults to indexed colour 64 (which doesn't actually exist in the colour chart) so the code defaults this to FF000000 (Black) because the PHPExcel_Style_Color class doesn't differentiate how/where that colour is being used. Within the Excel2007 Writer, we don't use indexed colours, just rgb values, and it is explicitly written as FF000000 (Black). It's more likely that this is the cause of the problem: to fix it, I'd need to change the default behaviour when a colour wasn't identified in the indexed colour chart to return white (FFFFFFFF) for fill colours, black (FF000000) for font colours.

So a couple of potential causes for the problem, both of which I'll look at fixing over the next week. Hopefully, one or other will resolve this issue.

Jan 1, 2011 at 2:44 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.