Message-ID: <761374106.9311.1711687479148.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_9310_954894191.1711687479132" ------=_Part_9310_954894191.1711687479132 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
When ExcelWriter opens a workbook that does not contain an explicitly de= fined palette, or when ExcelWriter creates a new workbook, it uses the same= default color palette that Excel uses. ExcelWriter never automatically add= s colors to the palette.
Because the behavior of the color palette changed in Excel between the E= xcel 2003 and 2007 file formats, it will have slightly different behavior i= n ExcelWriter when dealing with .xls files vs the newer file formats.
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 f= ollowing methods:
Palette.SetColor(Color color, int red, int green, int blue)=20
This method takes the color to replace as a Color object, and the = RGB values of the new color.
Palette.SetColorAt(int index, int red, int green, int blue)=20
This method takes the 0-based index of the color to replace, and t= he 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 correspon= ding 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 Palet= te.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 canno= t be changed by the user: SystemAutomaticFill, SystemAutomaticLine SystemBl= ack, SystemBlue, SystemCyan, SystemGreen, SystemMagenta, SystemRed, SystemW= hite, and SystemYellow.
Drawing objects (pictures, shapes, comments, etc.) support custom RGB co= lors 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&q= uot;), 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 li= ne colors, use Shape.SetCustomFillColor and Shape.SetCustomLineColor.
= =20 =20 =20Starting with the Excel 2007 file formats, a workbook is no longer limit= ed 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 pres= erve 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.Ge= tClosestColor to get any color, regardless of whether or not it is in the l= egacy palette. You may then use this Color object to set the color on any o= bject 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 ens= ure that all of your colors are palette colors by using the Palette.GetColo= rAt and Palette.SetColorAt methods. However, you will be limited to 56 colo= rs, due to the limitations in Excel 2003 and earlier.
private void SetupColors(Workbook wb) { //--- Workbook.Palette returns the Palette object Palette pal =3D wb.Palette; //--- GetClosestColor finds the color in the palette //--- that most closely matches the specified Red, //--- Green, and Blue values. Color clrDarkBlue =3D pal.GetClosestColor(0, 0, 255); Color clrTitleCell =3D pal.GetClosestColor(100, 100, 255); =20 try { //--- GetColor will find the exact color you request. //--- If the color does not exist in the palette //--- it will throw IllegalArgumentException. Color clrDarkGreen =3D pal.GetColor(51, 153, 102); } catch { //--- The color you requested was not found in the palette } //--- GetColorAt gets the color from the palette at //--- the specified index. This retrieves the second //--- color from the palette. Color clrComment =3D pal.GetColorAt(1); //--- SetColor and SetColorAt are for changing //--- the colors in the palette. Specify a Color to //--- change for SetColor, the index of the color for //--- SetColorAt, and then the RGB values for the desired //--- appearance. pal.SetColor(clrDarkBlue, 0, 0, 200); pal.SetColorAt(0, 0, 255, 0); //--- The Color object has built-in colors Color clrSysGreen =3D Color.SystemColor.Green; Color clrSysBlack =3D Color.SystemColor.Black; } private void StylizeHeaderCharacters(Workbook wb, Worksheet ws) { Palette pal =3D wb.Palette; //--- Get a reference to cell A1 Cell cellHeader =3D ws[0, 0]; Color clrDarkBlue =3D pal.GetClosestColor(0, 0, 255); Color clrDarkGreen =3D pal.GetColor(51, 153, 102); //--- Stylize the first word, starting from the //--- first character, ending at the 10th cellHeader.GetCharacters(0, 10).Font.Size =3D 12; //--- Format the font for the next two words in the cell cellHeader.GetCharacters(11, 12).Font.Color =3D clrDarkBlue; cellHeader.GetCharacters(24, 12).Font.Color =3D clrDarkGreen; }=20
This example shows how to use colors with 2007 file format workbooks. Be= cause 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 a= dditional overhead.
private void StylizeHeaderCharacters(Workbook wb, Worksheet ws) { Palette pal =3D wb.Palette; //--- Get a reference to cell A1 Cell cellHeader =3D ws[0, 0]; //--- In the 2007 file formats, you are guaranteed to get the //--- color you ask for, even if it is not in the legacy palette. //--- Because you are getting the color directly, and not using //--- Palette.GetColorAt or Palette.SetColorAt, the color returned //--- will be a custom color, and will not be a palette color. Color clrDarkBlue =3D pal.GetClosestColor(0, 0, 255); Color clrDarkGreen =3D pal.GetColor(51, 153, 102); //--- Stylize the first word, starting from the //--- first character, ending at the 10th cellHeader.GetCharacters(0, 10).Font.Size =3D 12; //--- Format the font for the next two words in the cell cellHeader.GetCharacters(11, 12).Font.Color =3D clrDarkBlue; cellHeader.GetCharacters(24, 12).Font.Color =3D clrDarkGreen; }=20
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 th= e same. Note that in this example, we always use GetColorAt or SetColorAt t= o get color objects. This ensures that the colors will be in the legacy pal= ette.
private void StylizeHeaderCharacters(Workbook wb, Worksheet ws) { Palette pal =3D wb.Palette; //--- Get a reference to cell A1 Cell cellHeader =3D ws[0, 0]; //--- By using SetColorAt, we ensure that the colors that //--- are returned are part of the legacy palette. We need //--- to use separate palette indices for each color we want //--- in the workbook, limiting us to 56 colors. Color clrDarkBlue =3D pal.SetColorAt(0, 0, 0, 255); Color clrDarkGreen =3D pal.SetColorAt(1, 51, 153, 102); //--- Stylize the first word, starting from the //--- first character, ending at the 10th cellHeader.GetCharacters(0, 10).Font.Size =3D 12; //--- Format the font for the next two words in the cell cellHeader.GetCharacters(11, 12).Font.Color =3D clrDarkBlue; cellHeader.GetCharacters(24, 12).Font.Color =3D clrDarkGreen; }=20