Changing an Excel cell's backcolor using hex results in Excel displaying completely different color in the spreadsheet
Solution 1
I finally figured it out, after lots of tests, and it was something really simple. Apparently, Excel's Interop library has a bug and is reversing the Red and Blue values, so instead of passing it a hex of RGB, I need to pass BGR, and suddenly the colors work just fine. I'm amazed that this bug isn't documented anywhere else on the internet.
So if anyone else ever runs into this problem, simply pass Excel values in BGR values. (Or if using Color.FromArgb(), pass in Color.FromArgb(B, G, R))
Solution 2
You need to convert the color from hex to Excel's color system as follows:
ColorConverter cc = new ColorConverter();
worksheet.Cells[1, 1].Interior.Color = ColorTranslator.ToOle((Color)cc.ConvertFromString("#F1DCDB"));
It's not really a bug, since Excel's color system has always been this way. It's just one more thing that makes C# - Excel interop a pain.
Solution 3
Please note that this is not a bug!Red starts from the lower bit and Green is in the middle and Blue takes the highest bits
B G R
00000000 00000000 00000000
The calculation is: (65536 * Blue) + (256 * Green) + (Red)
Thank you.
Solution 4
The RGB colour alone can be parsed from an HTML hex
string:
Color colour = ColorTranslator.FromHtml("#E7EFF2");
If you have a separate alpha value you can then apply this (docs):
Color colour = ColorTranslator.FromHtml("#E7EFF2");
Color transparent = Color.FromArgb(128, colour);
Solution 5
This is background information that may explain the answers.
If with HTML you specify colour #FF9900 you will get what Excel calls Light orange. If you specify colour #003366 you will get what Excel calls Dark teal. But if you want Light orange or Dark teal with vba you must specify &H0099FF and &H663300.
That is, although the vba function is RGB(&Hrr, &Hgg, &Hbb) the number it generates is &Hbbggrr because that is what the Excel display engine wants.
I would guess the person who coded the Excel Interop was unaware that Excel uses non standard numbers to specify colours.
Amberite
Updated on October 20, 2020Comments
-
Amberite over 3 years
So I am setting an Excel cell's Interior Color to a certain value, like below:
worksheet.Cells[1, 1].Interior.Color = 0xF1DCDB;
However, when I then open up the spreadsheet in Excel, I see that the color that came out is completely different (in the above case, the color in the resulting spreadsheet is 0xDCDCEF). I tried a few different colors and it always changes it, and I don't see a pattern.
Is there any reason for this? I even tried setting the color by writing Color.FromArgb(241, 220, 219).ToArgb(), and the same thing happened.
-
Tim Williams over 12 yearsWhat version of excel? Prior to Excel 2007 you could only have 56 different colors, and Excel would silently map the color you were tying to set to the closest one in the palette (with sometimes unpredictable results).
-
Amberite over 12 yearsHi Tim - I'm using Excel 2010, and through everything I'm reading, this should be possible.
-
user1703401 over 12 yearsSo the spreadsheet got opened in compatibility mode?
-
Amberite over 12 yearsHans - I'm not sure I understand the relevance, but from what I know, C# is opening Excel in normal mode, and when I open up the Excel program itself on my own later, it is also not in compatibility mode.
-
-
Amberite over 12 yearsYea, I'm using C# and Excel Interop :) ... but yes this is a very strange and annoying problem. I've tried using decimal, using hex (obviously), using 1580715, everything produces that other color, and I have NO idea how Excel even comes up with that color. If there was some pattern I would at least code that in...
-
aevanko over 12 yearsThis is brilliant. I was just making a function that converts interior.color to RGB with VBA and this explains why it wasn't working right!
-
codeConcussion over 12 yearswow. i ran into this a few months ago and it drove me insane. so glad i randomly stumbled across it. thanks!
-
Amberite about 12 yearsNo, you aren't correct here. Excel's help files themselves state to pass it in as RGB, not BGR. Bits don't matter, since the intended usage is to pass in RGB. When you need to screw with .NET's own built-in functions such as FromArgb and reverse the parameters in order to make Excel work correctly, that's a bug.
-
Leo Gurdian over 7 yearsThanks for documenting that on here. Made the prescribed changes and it the color translated just fine.