none
C# and Excel - Incorrect color conversion

    Question

  • Hello,

    I'm currently trying to assign a color to a cell's background. I have tried the suggest example on MSDN on different color than RED :

    this.m_workSheetRange.Interior.Color = ColorTranslator.ToOle(Color.Lavender);

    The conversion does not however fit, since the 'Lavender' color in Excel is referenced as "double = 16751052.0", whereas the color converter outputs : "int = 16443110".

    The code is adapted from the following MSDN page :

    http://msdn.microsoft.com/en-us/library/4zs9xy29(v=VS.100).aspx

     

    Is there anything I've missed in the process ?

    Ha yes, I'm developping in C# in VS 2010. Many apologies if I've posted in the wrong forum or if that question have already been answered, but I have been unable so far to get a workaround.

    Many thanks for any answer.

    • Moved by Cindy Meister MVPMVP Friday, November 18, 2011 6:41 PM Excel-specific question (From:Visual Studio Tools for Office)
    Friday, November 18, 2011 4:42 PM

Answers

  • After some research of this issue. I found something interestings:

    I wrote some simple code for tests:

     

             Excel.Range rng = this.Range["A1", "F5"];
                rng.Interior.Color =16751052; //FF99CC
                Excel.Range r1 = this.Cells[1, 1];    
                Color c = Color.FromArgb(System.Convert.ToInt32(r1.Interior.Color));
    
                //the expected result should be the color of the range has not been change
                //but it has changed actually
                rng.Interior.Color = c; //13408767 here, which is CC99FF in hex
    

    So you see, when color from Excel transfers to color in .NET, the red and blue properties has been interchanged. And acutally it can be confirmed from this article:

     

    http://msdn.microsoft.com/en-us/library/dd355244.aspx

    which defines RGB = Red + (Green*256) + (Blue*256*256), (which is BGR acutally)

    So the color retrieved from Excel can't not convert to color object of .NET library. You would have to write some logic to implement this conversion.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by tjafaas Monday, November 21, 2011 12:44 PM
    Monday, November 21, 2011 11:56 AM
    Moderator
  • Thank you for your answer, Calvin.

    I was aware something was amiss, but I couldn't point out where it was exactly. As you say, I'll do my own converter function, it seems pretty straight forward anyway.

     

    Besides, I've discovered another difficulty :

    Excel's color named "Lavender" has BGR = (FF) (99) (CC), which indeed should have been the standard RGB = (CC) (99) (FF) as can be tested on Paint.

    However, System.Color.Lavender has RGB = (E6) (E6) (FA) !!! This is for VS Version 4.0.30319 SP1 Release.

    If you test these values under paint, you get something closer to gray than any kind of purple. I guess I've stumbled upon the only (I hope :P) color that has wrong RGB to start with.

     

    Moreover, RGB values in System.Drawing.Color are quite different from those found in Excel (2003 in my environnement), for the same given name (as for "Gold" color).

     

    Anyway, thank you very much for your help, I've got a clearer vision of colors now ;)

    • Marked as answer by tjafaas Monday, November 21, 2011 12:45 PM
    Monday, November 21, 2011 12:44 PM

All replies

  •  acolor index is really  (green 256),(red 256),(blue 256) which is 8 bits + 8 bits + 8 bits.  So yo are definine 24 bits which is a long, not double nor int. The Net Library adds 0xFF t e the beginning of the color number.

     

    So 16443110 is really in hex FAE6E6.  So in C# you can simply say the color in 0xFFE6E6.

     

    If you add to your project the system.Drawaing library and add using system.Drawing then you can do this

    xlRange1.Interior.Color = System.Drawing.

    Color.Lavender;

    Lavendar is define as Green=250(FA),Red=236(EC),Blue=250(FA) = 16444666

     

     

     


    jdweng
    Friday, November 18, 2011 9:33 PM
  • Your code makes the following error (in french, so sorry for incorrect translation) :

    Methods with a return type or with System.Color parameters cannot be called through IDispatch.

    Original text is :

    "Les méthodes avec un type de retour ou des paramètres System.Color ne peuvent pas être appelées via IDispatch."

     

    Besides, I'm sorry to say Visual Studio tells me otherwise about numbers :

    this.m_workSheetRange.Interior.Color as long?;
    null
    this.m_workSheetRange.Interior.Color as double?;
    16751052.0
    

    Your explanation about how "ColorTranslator.ToOle" works is interesting, but it  doesn't resolve my actual problem, which is : the result given by the Converter (aka 16443110) is NOT what Excel expects to be (that is 16751052).

    Monday, November 21, 2011 9:03 AM
  • After some research of this issue. I found something interestings:

    I wrote some simple code for tests:

     

             Excel.Range rng = this.Range["A1", "F5"];
                rng.Interior.Color =16751052; //FF99CC
                Excel.Range r1 = this.Cells[1, 1];    
                Color c = Color.FromArgb(System.Convert.ToInt32(r1.Interior.Color));
    
                //the expected result should be the color of the range has not been change
                //but it has changed actually
                rng.Interior.Color = c; //13408767 here, which is CC99FF in hex
    

    So you see, when color from Excel transfers to color in .NET, the red and blue properties has been interchanged. And acutally it can be confirmed from this article:

     

    http://msdn.microsoft.com/en-us/library/dd355244.aspx

    which defines RGB = Red + (Green*256) + (Blue*256*256), (which is BGR acutally)

    So the color retrieved from Excel can't not convert to color object of .NET library. You would have to write some logic to implement this conversion.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by tjafaas Monday, November 21, 2011 12:44 PM
    Monday, November 21, 2011 11:56 AM
    Moderator
  • The Net Library stores the color object as a structure in 4 consecutive bytes.  VBA stores the color number as a 32 bit number.  It is not simple in C# to cast the structure to a number.  As Calvin pointered out the ToInt32 method will do this for you.
    jdweng
    Monday, November 21, 2011 12:11 PM
  • Thank you for your answer, Calvin.

    I was aware something was amiss, but I couldn't point out where it was exactly. As you say, I'll do my own converter function, it seems pretty straight forward anyway.

     

    Besides, I've discovered another difficulty :

    Excel's color named "Lavender" has BGR = (FF) (99) (CC), which indeed should have been the standard RGB = (CC) (99) (FF) as can be tested on Paint.

    However, System.Color.Lavender has RGB = (E6) (E6) (FA) !!! This is for VS Version 4.0.30319 SP1 Release.

    If you test these values under paint, you get something closer to gray than any kind of purple. I guess I've stumbled upon the only (I hope :P) color that has wrong RGB to start with.

     

    Moreover, RGB values in System.Drawing.Color are quite different from those found in Excel (2003 in my environnement), for the same given name (as for "Gold" color).

     

    Anyway, thank you very much for your help, I've got a clearer vision of colors now ;)

    • Marked as answer by tjafaas Monday, November 21, 2011 12:45 PM
    Monday, November 21, 2011 12:44 PM
  • Here is a .NET conversion function.

            public static int ToExcelRGB(this Color color)
            {
                return color.R + color.G * 256 + color.B * 256 * 256;
            }

    Friday, September 06, 2013 12:54 PM