How can I remove all gridlines on an Excel spreadsheet except those explicitly added by me after clearing them all?

Question

Yes, this question has been asked before, but the answer provided does not work for me. To be more specific:

Wanting to only have gridlines where there is data (which I add myself), and remove the lighter gridlines that appear by default everywhere else altogether, I found this accepted answer...but it doesn't work for me.

Using this code to get set up:

using Excel = Microsoft.Office.Interop.Excel;
. . .
private Excel.Application _xlApp;

...this initialization of the Excel.Application compiles (assigning a bool to _xlApp's DisplayGridLines property outside of its initializer):

_xlApp = new Excel.Application
{
    SheetsInNewWorkbook = 1,
    StandardFont = "ponceDeLeon",
    StandardFontSize = 11
};
_xlApp.Windows.Application.ActiveWindow.DisplayGridlines = false;

...but this (adding the code to the initializer) does not compile (I get, "Invalid initializer member declarator" and "The name 'Windows' does not exist in the current context"):

_xlApp = new Excel.Application
{
    SheetsInNewWorkbook = 1,
    StandardFont = "ponceDeLeon",
    StandardFontSize = 11,
    Windows.Application.ActiveWindow.DisplayGridlines = false
};

...nor does it compile with "Excel." prepended to "Windows.", even though it seems to want that (offering to import 'Microsoft.Office.Interop.Excel.Windows' and all other references in file"). The compiler's complaints are then:

An object reference is required for the non-static field, method, or property 'Microsoft.Office.Interop.Excel.Windows.Application.get'"

---and:

Invalid initializer member declarator

I already have Microsoft.Office.Interop.Excel.Windows in my project's References folder and, in fact, am successfully generating the .xlsx file. I'm to the "gingerbread/finish" part of the project now (cosmetics/formatting).

Even in the case of the compiling code, it doesn't actually work - it fails at runtime with: "System.NullReferenceException was unhandled . . . Object reference not set to an instance of an object."

So how can I "wipe the slate clean" of gridlines, and only let those I explicitly apply display on the sheet?

Answer 1

You need to set _xlApp.Windows.Application.ActiveWindow.DisplayGridlines = false; at another time. You have to open or create a workbook first before you set the value. Then it should work fine.

When you open an existing Excel-file:

        Excel.Application app = new Excel.Application();
        //app.ActiveWindow.DisplayGridlines = false;//Error
        Excel.Workbooks workbooks = app.Workbooks;
        //app.ActiveWindow.DisplayGridlines = false;//Error

        workbooks.Open(filename);
        app.ActiveWindow.DisplayGridlines = false;//No Error

When you create a new Excel-file:

        Excel.Application app = new Excel.Application();
        Excel.Workbook workbook = app.Workbooks.Add(System.Reflection.Missing.Value);
        app.ActiveWindow.DisplayGridlines = false;