How can I change the Row-Color of an excel-row with NetOffice?

Question

I'm struggling with a simple problem, but I can`t figure it out.

I have an excel document, which I do some processing with (using the NetOffice API). This works fine, but I want to change the row-color after the processing, so each row within the range should have the same color after being processed.

Im getting a COMException (HRESULT: 0x800A03EC) with the following code:

foreach (Excel.Range row in rg)
{
    //do the processing...
    ...
    row.Interior.Color = XlRgbColor.rgbAliceBlue;
}

I also googled for this HRESULT and tried to solve the problem, within the Open()-Method by setting readOnly to false and editable and corruptLoad to true. That didn`t work. I have also tried to set the interactive property to true and saved the excel file in different formats (.xls, .xlsx) but nothing worked out.

I found that the excelfile/workbook is protected. So I tried to unprotect the ActiveWorkbook like so

app.ActiveWorkbook.Unprotect();

But this also went wrong and throws a COMException that the unprotect-property of the Workbook object can not be assigned.

I hope that someone can help me with that.

Thanks in advance,

Cordell

Jignesh Thakker · Answer 1 · 2014-01-27T09:18:02.543

2

On Workbook open pass read only parameter to false and also pass password of excel file.

Excel.Workbook workBook = excelApplication.Workbooks.Open(sMyExcelPath,0,
                                                 False,5,123,123,True,XlPlatform.xlWindows,"\t",False,False,0,True,1,0)

You can change color of Row-Color of excel-row using below code.

Excel.Worksheet workSheet = workBook.Worksheets(1);

workSheet.Rows.Interior.Color = XlRgbColor.rgbAliceBlue;

To Set Border Try below codes:

    workSheet.Rows.Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlDouble;
    workSheet.Rows.Borders(XlBordersIndex.xlInsideHorizontal).Weight = 4;
    workSheet.Rows.Borders(XlBordersIndex.xlInsideHorizontal).Color = ToDouble(Color.Black);

Above code change color of all rows of excel sheet. If you want to change only used range colors then try with below code.

workSheet.UsedRange.Interior.Color = XlRgbColor.rgbAliceBlue;

To set Border Try below Codes:

    workSheet.UsedRange.Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlDouble;
    workSheet.UsedRange.Borders(XlBordersIndex.xlInsideHorizontal).Weight = 4;
    workSheet.UsedRange.Borders(XlBordersIndex.xlInsideHorizontal).Color = ToDouble(Color.Black);

edited Jan 27 '14 at 09:18

answered Jan 24 '14 at 13:26

Jignesh Thakker

3,638
2
28
35

I used password protected excel file for testing and it's working with solution which i mentioned in answer. Let me know if it's working or not ? – Jignesh Thakker Jan 24 '14 at 15:30
Hi Jignesh, it doesn`t work. As I mentioned in my question, I set the readOnly parameter to false from the beginning (January 22nd). :( – cordellcp3 Jan 27 '14 at 07:08
I updated answer. you need to pass password of Excel file on open workbook method. There are two parameters password & writePassword in open method. I have passed 123 value for my Excel file. – Jignesh Thakker Jan 27 '14 at 08:44
Thanks for your answer, but also with password it doesn`t work either :( Still says that the unprotect-property of the Workbook object can not be assigned. I handled it with a different approach and write a log file, this works fine. – cordellcp3 Jan 30 '14 at 07:02
if you passed password on Open method then you don't need to call app.ActiveWorkbook.Unprotect(); method because Unprotect method required password parameter if file is password protected. I am glad that you handled it with different approach and working file. Please share you approach so other can have solution for same problem. But for me above approach (answer) working fine at my side. – Jignesh Thakker Jan 30 '14 at 08:48

How can I change the Row-Color of an excel-row with NetOffice?

1 Answers1