How can I add gridlines to one sheet and not the other (C# Excel Interop)?

Question

Similar to some of the answers here, I turn gridlines in my Excel file off this way:

private ApplicationClass _xlApp;
. . .
_xlApp = new ApplicationClass { UserControl = true };
_xlApp.ActiveWindow.DisplayGridlines = false;

However, in my workbook, I create two sheets, and the second one needs to display gridlines. How can I toggle the displaying of gridlines at the Worksheet level?

I tried this:

private ApplicationClass _xlApp;
private ApplicationClass _xlApp2;
. . .
_xlApp = new ApplicationClass { UserControl = true };
_xlApp.ActiveWindow.DisplayGridlines = false;
. . .
_xlApp2 = new ApplicationClass { UserControl = true };
_xlApp2.ActiveWindow.DisplayGridlines = true;

...but that emitted an electronic epistle informing me at runtime that "Object reference not set to an instance of an object" on the last line displayed above.

So can I set one sheet to gridlined and the other ungridlined, or will I have to take matters into my own mitts and add universal borders to the second sheet?

UPDATE

The link from David Tansey was intiguing, but it didn't provide any concrete - or even abstract - example of how to use the Worksheetview object. So I binged (bang?) "c# excel interop worksheetview displaygridlines example" and found this.

I then extrapolated this "Virtual Buffoonery" code:

Dim wsv As WorksheetView 
Set wsv = wnd.SheetViews(1) 
' Display formulas and zeros, but hide 
' gridlines, headings, and outlines: 
wsv.DisplayFormulas = True 
wsv.DisplayGridlines = False 
wsv.DisplayHeadings = False 
wsv.DisplayOutline = False 
wsv.DisplayZeros = True

...and C#ified it thus:

// existing:
private ApplicationClass _xlApp;
_xlApp = new ApplicationClass { UserControl = true };

// new / extrapolated:
WorksheetView wsv = _xlApp.ActiveWindow.SheetViews(2);
wsv.DisplayGridlines = true;
wsv.DisplayZeros = true;

...but got the compile-time fingerwag, "Non-invocable member 'Microsoft.Office.Interop.Excel.Window.SheetViews' cannot be used like a method."

So, I tried this:

WorksheetView wsv = (WorksheetView)_xlApp.Sheets[2];    
wsv.DisplayGridlines = true;
wsv.DisplayZeros = true;

It compiled, but at runtime I'm sorely disappointed (and even put out, to quote Humperdinck) by "Unable to cast COM object type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.WorksheetView.'...No such interface supported"

So is there a way to do this in C#, or is this one of those areas where Virus Bits has it over C#?

UPDATE 2

This variation on the theme elicits the same response from the electronic spirits:

_xlSheetDelPerf = (Worksheet)_xlSheets.Item[2];
WorksheetView wsv = (WorksheetView)_xlSheetDelPerf;
wsv.DisplayGridlines = true;
wsv.DisplayZeros = true;

Take a look at the `WorksheetView` object, which has a `DisplayGridLines` property https://msdn.microsoft.com/EN-US/library/office/ff837066.aspx — David Tansey, Dec 01 '15 at 20:04
Well it looks like your specific issue is that your cast to `WorksheetView` is not working. Is your call to Sheets[2] null? Does it even return a `WorksheetView`? — The Muffin Man, Dec 01 '15 at 21:31
@TheMuffinMan: This works fine: _xlSheetDelPerf = (Worksheet)_xlSheets.Item[2]; — B. Clay Shannon, Dec 01 '15 at 21:39

score 0 · Accepted Answer · answered Dec 01 '15 at 22:24

I was unable to find a simple way to do it, so I "brute forced it" like so:

// Add borders to the sheet
var delPerfDataRange =
    _xlSheetDelPerf.Range[_xlSheetDelPerf.Cells[1, _xlSheetDelPerf.UsedRange.Columns.Count],            
_xlSheetDelPerf.Cells[_xlSheetDelPerf.UsedRange.Rows.Count, _xlSheetDelPerf.UsedColumns.Count]];
Borders _dataBorders = delPerfDataRange.Borders;
_dataBorders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
_dataBorders.Color = Color.Black;

Actually, I ended up needing to restrict just which range of the sheet was gridified, anyway, so I did this:

// Add borders around all the data
var delPerfDataRange =
    _xlSheetDelPerf.Range[_xlSheetDelPerf.Cells[DEL_PERF_FIRST_DATA_ROW, PROACT_DISTRIBUTOR_COLUMN],            
        _xlSheetDelPerf.Cells[curDelPerfRow - 1, TOTAL_PACKAGE_COUNT_COLUMN]];
Borders _dataBorders = delPerfDataRange.Borders;
_dataBorders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
_dataBorders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
_dataBorders.Color = Color.Black;

How can I add gridlines to one sheet and not the other (C# Excel Interop)?

UPDATE

UPDATE 2

1 Answers1

Linked