set Range color when creating Excel Document

Question

User1595717143 posted

 Hi,

 I read a lot of posts where people were complaining about  setting the color for an Excel.Range

(using Excel = Microsoft.Office.Interop.Excel)

Besides 

Excel.Range range = null;

range = (Excel.Range)xlsWorkSheet.get_Range(xlsWorkSheet.Cells[1, 1], xlsWorkSheet.Cells[1, 4]);

range.Interior.ColorIndex = 22;

(meaning ColorIndex)

does anybody know another solution?

or how cand I know what index I should  choose for  #fefgghe for example?

 

Thanks.

Answer 1

User1485238302 posted

How about this:  

Excel.Range range = null;
range = (Excel.Range)xlsWorkSheet.get_Range(xlsWorkSheet.Cells[1, 1], xlsWorkSheet.Cells[1, 4]);
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

 

 System.Drawing.Color has three methods FromArgb, FromKnownColor, FromName which should get you the color representation you want.

Answer 2

User1595717143 posted

 Thank you very much, it worked!

But how can I use a color like "#ddeeee" ?

Thanks again.

Answer 3

User1595717143 posted

 If I try

System.Drawing.Color col = System.Drawing.ColorTranslator.FromHtml("#efefef");

        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(col);

 

it gives me an error when the file is saving:

Exception from HRESULT: 0x800A03EC

 

xlsWorkBook.SaveAs(strCurrentDir + strFile, Excel.XlFileFormat.xlWorkbookNormal, .........

 

 thanks

Answer 4

User1772155490 posted

Hello,

Could you try this:

......

Color customColor = Color.FromArgb(46, 184,0);

range.Interior.Color=customColor;

Like this you can create any colors by using the RGB color model.

Here you can find a quick RGB color picker

http://www.colorschemer.com/online.html

Hope this helps [:)]

Answer 5

User1595717143 posted

 It gives me the error:

Methods with System.Color parameters or return type cannot be invoked via IDispatch.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Methods with System.Color parameters or return type cannot be invoked via IDispatch.

Source Error:

Line 100: Color customColor = Color.FromArgb(34, 23, 170); Line 101: Line 102: range.Interior.Color = customColor;

and  If I try:

 

Color customColor = Color.FromArgb(34, 23, 170);

        range.Interior.Color = customColor.ToArgb();

 

it gives me a warning  -  "Some cells or styles in this workbook contains that is not supported by the selected file format. these formats will be converted to the closest format available." -

it has cancel and continue as options.

if I click Cancel :

Exception from HRESULT: 0x800A03EC

if I click Continue, the excel has a different color.

Thanks.

 

Answer 6

User1485238302 posted

.

System.Drawing.Color col = System.Drawing.ColorTranslator.FromHtml("#efefef");

        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(col);

 

I am not sure why you are getting that error but here are some possible explainations in this thread.

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/3725e365-b740-4d5a-8f53-820f38d92513/

I tested your piece of code and it works fine on my machine so there is nothing wrong with your code.

Answer 7

User1595717143 posted

 I use office 2007 and I think this is my problem. (with the Compatibility Checker)

I get the exact message from this link:

http://blogs.msdn.com/excel/archive/2006/08/11/694891.aspx

 

but it's ok, I've tested it with office 2003 and it's working!

Thank you both for your answers[:)]