.
Each Excel document has a single color palette that manages formatting colors and some drawing layer colors. In Excel binary files (.xls), this palette is limited to 56 colors, and all formatting colors must come from this palette. Excel 2007 and higher (.xlsx files) can have unlimited colors. However, it still has a legacy palette with 56 colors. These colors are the only colors that are displayed when opening the file in Excel 2003 or earlier.
When ExcelWriter opens a workbook that does not contain an explicitly defined palette, or when ExcelWriter creates a new workbook, it uses the same default color palette that Excel uses. ExcelWriter never automatically adds colors to the palette.
Because the behavior of the color palette changed in Excel between the Excel 2003 and 2007 file formats, it will have slightly different behavior in ExcelWriter when dealing with .xls files vs the newer file formats.
The color palette in binary (.xls) files
Workbook.Palette returns a Palette object that represents the workbook's color palette. A Color object represents a single color in the palette.
To replace a color in the palette with a new color, call either of the following methods:
This method takes the color to replace as a Color object, and the RGB values of the new color.
This method takes the 0-based index of the color to replace, and the RGB values of the new color.
If you copy an object or cells with a custom color to another workbook, the custom color is replaced by the color in the corresponding position on the other workbook's color palette. To retain the custom color, either copy the customized color palette to the other workbook or change the corresponding color in the workbook.
If you are not sure that a specific color exists in the palette, and you want to assign the color to an element in the workbook, you can call Palette.GetClosestColor. Pass the method the red, green, and blue values of the ideal color. Palette.GetColor retrieves the color with the exact RGB value specified; if this color is not in the palette, an exception will be thrown.
A workbook's palette contains ten system colors that are fixed and cannot be changed by the user: SystemAutomaticFill, SystemAutomaticLine SystemBlack, SystemBlue, SystemCyan, SystemGreen, SystemMagenta, SystemRed, SystemWhite, and SystemYellow.
Drawing objects (pictures, shapes, comments, etc.) support custom RGB colors that may or may not be present in the palette. If you modify the color of an AutoShape (right-click the shape and select "Format AutoShape"), for example, and select a non-palette custom color, the change will not affect the workbook palette or other elements in the workbook to which the replaced color was assigned. To set custom values for shape fill and line colors, use Shape.SetCustomFillColor and Shape.SetCustomLineColor.
The color palette in xml (.xlsx, .xlsm, etc) files
Starting with the Excel 2007 file formats, a workbook is no longer limited to 56 colors. However, there is a legacy palette that contains 56 colors, and can be used to specify the colors used when opening the file in Excel 2003.
Unless you want to create files in the 2007 file formats that fully preserve their color information in older versions of Excel, you do not need to worry about the legacy palette. You can use Palette.GetColor or Palette.GetClosestColor to get any color, regardless of whether or not it is in the legacy palette. You may then use this Color object to set the color on any object in the workbook.
If you need to have the same colors when opened in any version of Excel, then you will need to only use colors that are in the palette. You can ensure that all of your colors are palette colors by using the Palette.GetColorAt and Palette.SetColorAt methods. However, you will be limited to 56 colors, due to the limitations in Excel 2003 and earlier.
Example of using the palette with binary (.xls) files
Example of using colors with Excel 2007 file formats
This example shows how to use colors with 2007 file format workbooks. Because the 2007 file formats support unlimited colors, we do not need to do any work to set up the palette. We can use any colors we want without any additional overhead.
Example of using 2007 file formats with the legacy palette
This example shows how to use the legacy palette in 2007 file formats to ensure that if the file is opened in Excel 2003, the colors will remain the same. Note that in this example, we always use GetColorAt or SetColorAt to get color objects. This ensures that the colors will be in the legacy palette.