Spreadsheet ML Text Color (Colour) Rendering

Question

Hi All,

I am writing a tool which generates some Spreadsheet ML (XML) to create an Excel spreadsheet for my users.

I have defined a style as follows:

<Style ss:ID="GreenText">
  <Font ss:FontName="Arial" ss:Size="9" ss:Color="#8CBE50" />
</Style>

This works to an extent, but when I open it in Excel the colour rendered for the text isn't the one I specified - it's a brighter version. I can use the same colour reference for a cell border and the colour is rendered correctly.

Can anyone shed any light on why the text colour isn't rendered correctly?

Thanks!

Answer 1

Excel's limited to a palette of 56 colors. It only stores color indexes rather than the actual RGB values. They do permit custom colors in the palette, but I don't know how to change them programmatically.

Edit:
I haven't used office xml documents but this might help (indexedColors tag for defining palette):
http://openxmldeveloper.org/forums/thread/309.aspx

Also there's a Workbook.Colors property for changing the palette from VBA.

Answer 2

David is correct that Excel 2003 and previous versions of Excel are limited to a 56 color palette.

Excel 2007 added support for 24 bit colors as well as theme colors. Excel 2007 can write xls workbooks which contain this additional color information, and which Excel 2003 can read, but Excel 2003 will still be limited to the 56 color palette. Excel 2007 can load these workbooks and display the exact colors.

SpreadsheetGear for .NET supports the new 24 bit colors and theme colors, as well as the old palette indexed colors, just as Excel 2007 does. You can use SpreadsheetGear to create a workbook with 24 bit colors which will display correctly in Excel 2007, or modify the palette and they will display correctly in Excel 2007 and Excel 2003. Below is an example of both.

You can download a free trial here and try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Here is the sample code:

            // Create a new empty workbook with one worksheet.
            IWorkbook workbook = Factory.GetWorkbook();
            // Get the worksheet and change it's name to "Person".
            IWorksheet worksheet = workbook.Worksheets[0];
            worksheet.Name = "Colors";
            // Put "Hello World!" into A1.
            IRange a1 = worksheet.Cells["A1"];
            a1.Value = "Hello World!";
            a1.Font.Color = System.Drawing.Color.FromArgb(0x8C, 0xBE, 0x50);
            // Save the workbook as xls (Excel 97-2003 / Biff8) with default palette.
            //
            // This workbook will display the exact color in Excel 2007 and
            // SpreadsheetGear 2009, but will only display the closest available 
            // palette indexed color in Excel 2003.
            workbook.SaveAs(@"C:\tmp\GreenDefaultPalette.xls", FileFormat.Excel8);
            // Save as xlsx / Open XML which will also display the exact color.
            workbook.SaveAs(@"C:\tmp\GreenDefaultPalette.xlsx", FileFormat.OpenXMLWorkbook);
            // Now, modify the palette and save. This workbook will display the exact
            // color in Excel 2003 as well as in SpreadsheetGear 2009 and Excel 2007.
            // 
            // Note that modifying the palette will change the color of any cells which
            // already reference this palette indexed color - so be careful if you are
            // modifying pre-existing workbooks.
            workbook.Colors[0] = a1.Font.Color;
            workbook.SaveAs(@"C:\tmp\GreenModifiedPalette.xls", FileFormat.Excel8);