Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Changing an Excel cell's backcolor using hex results in Excel displaying completely different color in the spreadsheet

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.

like image 510
Amberite Avatar asked Sep 14 '11 21:09

Amberite


3 Answers

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

like image 137
Amberite Avatar answered Nov 18 '22 13:11

Amberite


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.

like image 38
CtrlDot Avatar answered Nov 18 '22 13:11

CtrlDot


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.

like image 2
kta Avatar answered Nov 18 '22 15:11

kta