Custom Palette for custom fill colors

Topics: Developer Forum, Project Management Forum, User Forum
Apr 22, 2011 at 11:45 PM

Hi all,

I am using the latest version of the API and was wondering if there is anyway to add custom colors to the Excel palette. (i am using Excel Writer 2007).

Thanks a lot!

Coordinator
Apr 23, 2011 at 12:00 AM

The simple answer is "no". You can set cells and cell content to any RGB colour, but this doesn't link the colour to the palette/theme. Effectively, when saving, PHPExcel ignores palette/theme settings and stores the individual colours as RGB. When reading, cells using palette/theme colours are converted to their RGB equivalents.

Apr 23, 2011 at 12:17 AM

Thanks Mark for the quick reply. 

So, this means that I can't set any font or fill colors which are not a part of default Excel palette? 

Thanks!

Vinay

Coordinator
Apr 23, 2011 at 12:37 AM
revdev wrote:

So, this means that I can't set any font or fill colors which are not a part of default Excel palette? 

No! It means the exact reverse. that you can set any RGB colour that you want, irrespective of whether it's part of any defined palette/theme.

Apr 23, 2011 at 12:42 AM

Actually I tried setting custom color but when I opened the file in MS Excel, the colors which were not present in the default palette displayed as white. I assumed that to use such custom colors,  I will have to alter the default palette. Any suggestions to fix this issue?

Thanks!  

Coordinator
Apr 23, 2011 at 12:08 PM

OK! I'll rephrase what I said. PHPExcel should allow you to set any RGB colour, irrespective of the template. In order to resolve this problem, I need a few more details:

  • What Writer are you using? or have you found that this is occurring with every writer.
  • What version of MS Excel (and platform) are you using to open the file?

 

Apr 25, 2011 at 10:20 PM
  • What Writer are you using? or have you found that this is occurring with every writer.

I am using PHPExcel_Writer_Excel2007.

  • What version of MS Excel (and platform) are you using to open the file?

I am using MS Excel 2008 for Mac, version 12.2.0 .

Thanks!

Coordinator
Apr 25, 2011 at 10:39 PM
revdev wrote:

I am using MS Excel 2008 for Mac, version 12.2.0 .

I don't know if that could be the problem... I've heard rumours that Mac only uses palette colours, but never been able to find any definitive confirmation... especially as Mac 2008 has the same colour selection options (theme, standard colours, more colours) as Excel for Windows. If you have a copy of Excel for Windows available, can you try opening in that and see how the colours look there.

Apr 26, 2011 at 6:57 PM

I think you are right. It worked perfectly on my PC with excel 2010. Is that a known problem with Mac ? What solution do you suggest?

Thanks!

Coordinator
Apr 26, 2011 at 9:58 PM
revdev wrote:

I think you are right. It worked perfectly on my PC with excel 2010. Is that a known problem with Mac ? What solution do you suggest?

Thanks!


It wasn't a known problem: just that I'd heard a few rumours from the Office for Mac forums, without every hearing anything to verify that this is the case (and I'm not a Mac user myself).

I don't have a clean solution: as I said previously, PHPExcel doesn't use themes when writing (and I know that they're a PITA to read) but sets each cell colours directly as RGB values. Changing that to work with themes isn't a trivial task to start; and could easily result in variations in the shading of colours because it's all based on a percentage intensity of the actual theme colour.

  • If it was designed to allow any RGB colour as at present, and then map those RGBs to theme colours and intensities; then it could be a performance drain for the writer... so I'd want to make it a feature that needed to be explicitly enabled as a variation on the current default behaviour.
  • The alternative is writing colour setting logic that only permits colours/intensities from a predefined theme. This would entail changing the PHPExcel_Style_Color (or providing a PHPExcel_Style_ThemeColor class), and themes (and a theme class, with Reader/user options to set/update that class).

Neither of these options is easy to implement, though I do already have a PHPExcel_Reader_Excel2007_Theme class that serves as a theme colour map for the Excel2007 Reader, working with some static methods in the PHPExcel_Style_Color class to adjust brightness/intensity... and these could provide a starting point for either solution.

May 4, 2011 at 11:23 PM

Thanks a lot Mark for describing everything in detail. Luckily it works perfectly on mac excel 2011. We are going to make an exception for excel 2008. Hopefully this is good enough for now. 

Thanks again for all the prompt responses and for writing this really cool library!

May 5, 2011 at 12:46 AM
Edited May 5, 2011 at 12:48 AM

revdev: I don't know if it helps for your situation or not, but you can alter the default palette manually by editing 'PHPExcel/Style/Color.php'. The colors are defined starting on line 371. PHPExcel 1.7.6.

This would change the palette for any spreadsheet created with your edited version of PHPExcel. However, since it edits the palette, instead of the color assigned to each cell, it should achieve what you're after (you would of course still need to style the cell appropriately). I do not know if there is any special caveat working with a Mac however. This is what I had to do for my project (which must output an Excel 2005 file) and it worked wonderfully. It might be another thing for you to try.

It sounds like Mark has a better solution rattling around in his head (and maybe a future release), so this would definitely be a quick and dirty/duct tape method by comparison.