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

Answer 1

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