This project has moved. For the latest updates, please go here.

enumeration for Indexed -> System.Drawing.Colors?

Oct 19, 2010 at 10:10 PM

Hello!

Nifty library, excellent work!

A spreadsheet I'm trying to read/parse stores the foreground & background colors as Indexes.  They seem to align with the Office Open XML Documentation (ECMA-376), Part 4, "Markup Language Reference".  Specifically, section 3.8.26 on indexedColors (Color Indexes) on page 2122.  It's referred to as "A deprecated indexing scheme for colours that is still required for some records, and for backwards compatibility with legacy formats".  This was a real pain to cross reference.

Anyway...  I have to work with these Index now (via Style.Fill parameters), but I'd much rather use System.Drawing.Color enumerations, either via a lookup table or built-in functions.  Is this already in place, or on the TO DO list?  I'll probably begin work on a lookup mapping while I wait for a reply :)

source: http://www.ecma-international.org/publications/standards/Ecma-376.htm

Oct 19, 2010 at 11:22 PM

Bah,

Not all the colors have an analogous Known Color;  looks like at least the first 16 are usable at least.  Here's what I'm using to convert the 1-63 indice to an HTML color code.  Hope others find this useful, even if it is pulled directly from "freely available sources":

        // reference extracted from ECMA-376, Part 4, Section 3.8.26
        private string[] rgbLookup = 
        {
            "#000000",
            "#FFFFFF",
            "#FF0000",
            "#00FF00",
            "#0000FF",
            "#FFFF00",
            "#FF00FF",
            "#00FFFF",

            "#000000",
            "#FFFFFF",
            "#FF0000",
            "#00FF00",
            "#0000FF",
            "#FFFF00",
            "#FF00FF",
            "#00FFFF",

            "#800000",
            "#008000",
            "#000080",
            "#808000",
            "#800080",
            "#008080",
            "#C0C0C0",
            "#808080",
            
            "#9999FF",
            "#993366",
            "#FFFFCC",
            "#CCFFFF",
            "#660066",
            "#FF8080",
            "#0066CC",
            "#CCCCFF",
            
            "#000080",
            "#FF00FF",
            "#FFFF00",
            "#00FFFF",
            "#800080",
            "#800000",
            "#008080",
            "#0000FF",
            
            "#00CCFF",
            "#CCFFFF",
            "#CCFFCC",
            "#FFFF99",
            "#99CCFF",
            "#FF99CC",
            "#CC99FF",
            "#FFCC99",
            
            "#3366FF",
            "#33CCCC",
            "#99CC00",
            "#FFCC00",
            "#FF9900",
            "#FF6600",
            "#666699",
            "#969696",
            
            "#003366",
            "#339966",
            "#003300",
            "#333300",
            "#993300",
            "#993366",
            "#333399",
            "#333333",
        };

Coordinator
Oct 20, 2010 at 3:35 PM

Thanks

I guess I should make the Index property writable (or create a new one as as enum-type) some time in the future. I'll add that to my TODO list.

Jan

Dec 13, 2010 at 11:00 PM

Jan,

The ExcelColor contents seem to change, based on wich version of Excel saves the file.  .Indexed is used often if saving from Excel 2003;  .RGB is used for colors, if saving from Excel 2007;  .Tint is used for shades of grey, if saving from Excel 2007.  I've been using a translation function to ensure a consistent result across my available spreadsheets.  Feel free to include it in a future version.

 

        string LookupColor(OfficeOpenXml.Style.ExcelColor theColor)
        {
            int iTint = 0;
            string translatedRGB = "";

            // reference extracted from ECMA-376, Part 4, Section 3.8.26
            string[] rgbLookup =
            {
                "#FF000000", // 0
                "#FFFFFFFF",
                "#FFFF0000",
                "#FF00FF00",
                "#FF0000FF",
                "#FFFFFF00",
                "#FFFF00FF",
                "#FF00FFFF",
                "#FF000000", // 8
                "#FFFFFFFF",
                "#FFFF0000",
                "#FF00FF00",
                "#FF0000FF",
                "#FFFFFF00",
                "#FFFF00FF",
                "#FF00FFFF",
                "#FF800000",
                "#FF008000",
                "#FF000080",
                "#FF808000",
                "#FF800080",
                "#FF008080",
                "#FFC0C0C0",
                "#FF808080",
                "#FF9999FF",
                "#FF993366",
                "#FFFFFFCC",
                "#FFCCFFFF",
                "#FF660066",
                "#FFFF8080",
                "#FF0066CC",
                "#FFCCCCFF",
                "#FF000080",
                "#FFFF00FF",
                "#FFFFFF00",
                "#FF00FFFF",
                "#FF800080",
                "#FF800000",
                "#FF008080",
                "#FF0000FF",
                "#FF00CCFF",
                "#FFCCFFFF",
                "#FFCCFFCC",
                "#FFFFFF99",
                "#FF99CCFF",
                "#FFFF99CC",
                "#FFCC99FF",
                "#FFFFCC99",
                "#FF3366FF",
                "#FF33CCCC",
                "#FF99CC00",
                "#FFFFCC00",
                "#FFFF9900",
                "#FFFF6600",
                "#FF666699",
                "#FF969696",
                "#FF003366",
                "#FF339966",
                "#FF003300",
                "#FF333300",
                "#FF993300",
                "#FF993366",
                "#FF333399",
                "#FF333333", // 63
            };

            if ((0 <= theColor.Indexed) && (rgbLookup.Count() > theColor.Indexed))
            {
                // coloring by pre-set color codes
                translatedRGB = rgbLookup[theColor.Indexed];
            }
            else if (null != theColor.Rgb && 0 < theColor.Rgb.Length)
            {
                // coloring by RGB value ("FFRRGGBB")
                translatedRGB = "#" + theColor.Rgb;
            }
            else
            {
                // coloring by shades of grey (-1 -> 0)
                iTint = ((int)(theColor.Tint * 160) + 0x80);
                translatedRGB = ((int)(decimal.Round(theColor.Tint * -512))).ToString("X");
                translatedRGB = "#FF" + translatedRGB + translatedRGB + translatedRGB;
            }

            return translatedRGB;
        }

I'm a little sketchy on Excel's RGB value; specifically, the leading 2 FF characters.  If those are Intensity, then the "tint" logic could be simplified to be #NNFFFFFF, where NN is the hex-normalized tint value.  Alternatively, the leading 2 characters could be trimmed from the excel format, leaving a standard 6-digit html hex code (with leading #).

Amazing library, thanks for the latest release!

Coordinator
Dec 14, 2010 at 5:36 PM

Great!

I'll try to include it in the next release.

Jan