Changing an Excel cell's backcolor using hex r

问题:

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.

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

回答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.

回答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.

回答4:

Try worksheet.cells(1,1).interior.color = rgb(241, 220, 219).

EDIT I'm dumb, just noticed you're not using VBA 8). This is a bit of a long stretch but can you try sending it as decimal? For what it's worth, ...interior.color = &HF1DCDB works in VBA as does = 15850715.

回答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.

回答6:

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

回答7:

Just wanted to post my code, too. Since there was no copy&paste solution for me. Based on Sandesh 's code:

private void ColorMe(Color thisColor){
rng = xlApp.ActiveCell;
Color mycolour = ColorTranslator.FromHtml("#" + thisColor.Name.Substring(2, 6));
rng.Interior.Color = Color.FromArgb(mycolour.R, mycolour.G, mycolour.B);
}