Problem

When using ExcelWriter's ExcelApplication object to generate a new workbook, custom colors that are assigned in code to fonts, charts, cell backgrounds, etc., display incorrectly on the client when the workbook is generated. OR When creating an Excel File or an Excel file to use as a template with the ExcelTemplate object using Excel 2007 and above, colors are not colors are not preserved in Excel 2003 or older. The Excel .XLSX file format supports millions of colors, so when working in newer versions of Excel, the user interface will give you the option to select any color because Excel does not know what file format you will save in.

• When creating a document using Excel 2007 and later and you save your file as XLS format, everything will appear as normal, but when the XLS file is opened in Excel 2003 the colors will revert to the closest 56 colors in the workbook palette. Depending on your application settings you may or may not receive a Minor loss of fidelity warning.
• If you save the file as an XLSX file you will not receive a warning, but when an XLSX file is opened in Excel 2003 with the compatibility pack, Excel will perform a file conversion back to XLS, which limits the workbook to the 56 colors in the palette.

Since the XLS file format is limited to 56 colors stored in the workbook, special consideration must be taken when designing a document that will be viewed in previous versions of Excel. To ensure that colors appear the same in all versions of Excel, one can customize the workbook palette to include the colors needed. This can be done both manually, and automatically with 3rd party tools.

Solution

Creating a custom palette using Excel Application

If you are using ExcelApplication to programmatically set colors using Palette.GetClosestColor, ExcelWriter will attempt to find the closest color in the palette. If the color or similar color is not in the workbook’s palette, the colors may not appear as expected. You can use ExcelWriter’s SetColorAt method to replace a color in the palette and ensure that your color will be found.  You can also use ExcelWriter’s Palette.GetColorAt method to retrieve specific colors.  By ensuring the colors you are using are in the Workbook Palette you can ensure the document will render with the colors desired.

 ExcelApplication xla = newExcelApplication(); Workbook wb = xla.Create(); //Set a color in the Palette wb.Palette.SetColorAt(1, 240, 217, 216); //Can also use GetColorAt(1); wb[0].Cells["A1"].Style.BackgroundColor = wb.Palette.GetClosestColor(240, 217, 216); xla.Save(wb,"colors.xls"); 

Creating a custom palette using Excel

When designing a template in Excel, the colors in the workbook’s palette can be modified.

Manually creating a custom palette in Excel 2010:

• Open the Workbook whose palette you wish to modify.
• In the upper left hand corner select File
• select Options
• Select the Save menu
• Under Preserve Visual Appearance of a workbook, next to choose what colors will be seen in previous versions of Excel select Colors…
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Manually creating a custom palette in Excel 2007:

• Open the Workbook whose palette you wish to modify.
• In the upper left hand corner press the Microsoft Office Button and select Excel Options
• Select the Save menu
• Under Preserve Visual Appearance of a workbook, next to choose what colors will be seen in previous versions of Excel select Colors…
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Manually creating a custom palette in Excel 2003:

• Open the Workbook whose palette you wish to modify.
• From the Tools menu select Options, and then select the Color tab.
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Automatically converting colors to custom palette:

Since manually editing the workbook color palette can be a time consuming process, some people have tried to automate the process.  Jon from Excel Campus has done just this with his color palette conversion tool. By ensuring the colors you are using are in the workbook’s palette, you can ensure that your workbook will appear the same throughout different versions of Excel.