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 The Color Palette

The Color Palette

Each Excel document has a single color palette that manages formatt= ing colors and some drawing layer colors. In Excel binary files (.xls), thi= s palette is limited to 56 colors, and all formatting colors must come from= this palette. Excel 2007 and higher (.xlsx files) can have unlimited color= s. 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 e= arlier.

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.

The color pal= ette 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 f= ollowing methods:

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 Icon=20
=20

For more information on creating a custom palette for XLS files, refer t= o Excel's Color Palette Explained<= /a>.

=20
=20
=20

The c= olor palette in xml (.xlsx, .xlsm, etc) files

Starting 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.

Ex= ample of using the palette with binary (.xls) files

=20
=20 Icon=20
=20

Wherever type Color is referenced in the following example, the type is = SoftArtisans.OfficeWriter.ExcelWriter.Color. If you are using ExcelWriter i= n a class that imports the .NET framework class System.Drawing (added by de= fault by when a new webform is added to a VS.NET project), you will get an = "ambiguous reference" error at compile-time because System.Drawin= g also contains a type Color. The solution is to either remove the System.D= rawing import statement, or use the fully qualified name of ExcelWriter's C= olor object.

=20
=20
=20
=20
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

Exa= mple of using colors with Excel 2007 file formats

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.

=20
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

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 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.

=20
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
------=_Part_9310_954894191.1711687479132--