C# Excel range properties return DBNull for some worksheets

Question

I'm developing an Excel add-in to perform several checks on an Excel sheet. The checks are for layout, text and formulas. I created a Ribbon that calls a WinForm when you press a button. On the form, you can set different options and then click 'Check' to perform a full check.

The sheet always has the same header, so I have a class to check the layout in the header. In this class I have methods to check the alignment, the font, the height etc. This is the code for the methode to check the alignment in a cell. It takes 3 arguments, the range, the expected horizontal alignment and the vertical alignment.

private void CheckRangeAlignment(Excel.Range range, Excel.XlHAlign expHA,
    Excel.XlVAlign expVA) {
        Excel.XlHAlign actHA = Excel.XlHAlign.xlHAlignLeft;
        Excel.XlVAlign actVA = Excel.XlVAlign.xlVAlignTop;
        if (!Convert.IsDBNull(range.HorizontalAlignment))
            actHA = (Excel.XlHAlign)range.HorizontalAlignment;
        if (!Convert.IsDBNull(range.VerticalAlignment))
            actVA = (Excel.XlVAlign)range.VerticalAlignment;

        if (expHA != Excel.XlHAlign.xlHAlignLeft) {
            if (actHA != expHA || actVA != expVA) {
                this.FeedbackItems.AddDataItem(range.Row, range.Column, "Uitlijning");
                this._errors++;
            }
        } else {
            if (actHA != expHA && actHA != Excel.XlHAlign.xlHAlignGeneral || actVA != expVA) {
                this.FeedbackItems.AddDataItem(range.Row, range.Column, "Uitlijning");
                this._errors++;
            }
        }

        MRCO(actHA);
        MRCO(actVA);
    }

A strange thing happens in some sheets. The property 'HorizontalAlignment' of the range is empty. When debugging in VS2012, it gives '{}'. So the code gave an error saying it could not convert Microsoft.Office.Interop.Excel.XlHAlign to System.DBNull. That's why I now check for the DBNull value. Now here is the really strange thing: I have one document where it doesn't give a problem and another one where it does. And it also happens to other properties like Font.Size, VerticalAlignment, Font.Bold and Font.Underline.

Could someone help me out on this one? I'm quite new to Excel in C# so maybe I'm overlooking something? Thanks in advance

Answer 1

It was a small issue after all. When it is a range, those properties will be empty and so resulting in DBNull. I fixed it by making an extra variable that selects the first cell in the range. This way, the properties are never DBNull.

Excel.Range xlcell = range.Cells[1,1];