How can I retrieve the cell's color?

Question

I want to get the cell color with epplus. In an Excel cell I have coloured text. This code not return the cell color. I cannot understand why. I want to get all the colors of the cell and then add it to a Dictionary with text.

FileInfo file = new FileInfo("K:\\1.xlsx");
var package = new ExcelPackage(file);

ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
    for (int row = start.Row; row <= end.Row; row++)
        { 
              for (int col = start.Column; col <= end.Column; col++)
              { 
                 var color = worksheet.Cells[row, col].Style.Font.Color;

              }
         }

Answer 1

There are two ways text can become colored in .xlsx files.

• There can be a style applied to the cell (the approach you are trying to use above)
• The cell can contain "Rich Text" content which is itself colored

If the cell contains text of multiple colors then it is the latter of these options and we must therefore use the .RichText property to access the colors. We can also test to determine if a cell contains Rich text with the .IsRichText property.

As an example take this workbook:

Then when running this code:

    static void Main(string[] args)
    {
        Dictionary<string, Color> dictionaryTextToColour = new Dictionary<string, Color>();

        var fInfo = new FileInfo(@"C:\Temp\sample.xlsx");
        using (var package = new ExcelPackage(fInfo))
        {

            var a1 = package.Workbook.Worksheets.First().Cells[1, 1];

            if (a1.IsRichText)
            {
                var richText = a1.RichText;
                dictionaryTextToColour = richText 
                    .ToDictionary(rt => rt.Text, rt => rt.Color); //NOT recommended to use a dictionary here
            }
        }

        foreach (var substring in dictionaryTextToColour.Keys)
        {
            Console.WriteLine(substring + ": " + dictionaryTextToColour[substring]);
        }
    }

we see this output:

    Some: Color [Empty]
    TextW: Color [A=255, R=0, G=176, B=80]
    ithVarious: Color [A=255, R=0, G=32, B=96]
    Colour: Color [A=255, R=255, G=0, B=0]
    s: Color [Empty]

The .RichText property of type ExcelRichTextCollection which implements IEnumerable<ExcelRichText> so we could just as well have iterated over each text segment with something like:

foreach (var rtSubString in a1.RichText)
{
    //do something with the string segment rtSubString
}

N.B. I have added the colors to a dictionary keyed with the text as that is what was asked for in the original question but I don't think that is the best data structure as the same substring could be repeated with different colors