1

I want to avoid generated spreadsheets having empty/superfluous sheets named "Sheet1" and such. I thought I could do that by specifying how many sheets a workbook should have this way:

_xlApp = new Excel.Application { SheetsInNewWorkbook = 1 };

...But I'm still getting an unwanted "Sheet1" in addition to the sheet I create. Here is the pertinent code:

using Excel = Microsoft.Office.Interop.Excel;
. . .
private Excel.Application _xlApp;
private Excel.Workbook _xlBook;
private Excel.Sheets _xlSheets;
private Excel.Worksheet _xlSheet;
. . .
private void InitializeSheet()
{
    _xlApp = new Excel.Application { SheetsInNewWorkbook = 1 };
    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    _xlSheets = _xlBook.Worksheets;
    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
    _xlSheet.Name = String.Format("Price Compliance {0} {1}", _month, _year);
}

So since setting SheetsInNewWorkbook to 1 in the Excel.Application instance doesn't do the trick, what do I need to do to prevent these tramp sheets from showing up?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    Not sure about why it's adding the sheet, but maybe add a line that removes a sheet called "Sheet1" at the end, after you set up your sheet? – BruceWayne Dec 15 '15 at 22:27
  • @Batman: How is that done? I have never removed a sheet. – B. Clay Shannon-B. Crow Raven Dec 15 '15 at 22:34
  • 1
    Unfortunately I'm not well versed (or even terribly versed) in C# for Excel. However, it looks like [this thread](http://stackoverflow.com/questions/16328923/delete-excel-worksheets-programatically) has that answer to that question. Does that help? – BruceWayne Dec 15 '15 at 22:50
  • 1
    @Batman: Thanks, that link had two good ideas, the water pistol, which will probably work, and the water cannon, which I might use just for the heck of it. – B. Clay Shannon-B. Crow Raven Dec 15 '15 at 23:03
  • @pnuts - if he changed that to `0` would it create a single sheet? Since the default is 1, I'm thinking the number he has, `1`, technically means *in addition to default number*? – BruceWayne Dec 15 '15 at 23:04
  • 1
    @BruceWayne The `SheetsInNewWorkbook` property must be between 1 and 255. But @B.ClayShannon, I'm confused because simply removing your line `_xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);` would do the trick since you'd simply be renaming `Sheet1` – test Dec 15 '15 at 23:10
  • 1
    @test - you're right; that was a bogus call to Worksheets.Add(). Removing it solved the superfluency. Make that an answer, and I'll mark it as such. – B. Clay Shannon-B. Crow Raven Dec 15 '15 at 23:24
  • 2
    @pnuts - I was thinking it'd be understood as `create 0 additional sheets to default amount`. But that's probably not right. And it looks like B.ClayShannon found his solution anyways, so huzzah! – BruceWayne Dec 15 '15 at 23:26

1 Answers1

3

The answer to your question can be found in the documentation of the Template parameter on the Workbook.Add method.

[...] If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).

Your code is omitting it, therefore it is creating a single Worksheet for you (since you've set SheetsInNewWorkbook to 1.

That property is also constrained to be between 1 and 255, so you aren't able to add a work book without a sheet (unless you use a file template).

Also from the Template parameter documentation:

If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following Microsoft.Office.Interop.Excel.XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet.

So an alternative way to do this is:

_xlApp = new Excel.Application();
_xlBook = _xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
_xlSheets = _xlBook.Worksheets;
_xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
_xlSheet.Name = String.Format("Price Compliance {0} {1}", _month, _year);

Which simply renames the single created sheet.

test
  • 2,589
  • 2
  • 24
  • 52