C# Create Excel workbook with 1 sheet by default

Question

I'm trying to create an Excel file with C# COM interop but seems it create it by default with 3 sheets instead of empty or only one. What is needed to create it Empty or just with one:

Excel.Application xl = null;
Excel._Workbook wb = null;

// Create a new instance of Excel from scratch
xl = new Excel.Application();
xl.Visible = true;     
wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value));

wb.SaveAs(@"C:\a.xls", Excel.XlFileFormat.xlWorkbookNormal,
 null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
 false, false, null, null, null);

I think this is the default behavior in Excel. If you open excel and create a new document, it has three sheets. Maybe you just need to delete sheets two and three. — Klaus Byskov Pedersen, Nov 09 '11 at 14:10
Or you can try your hand on a simple but handly library NPOI http://code.google.com/p/npoi/ that gives you alot of flexibility especially for basic to intermediate excel features. If your output only needs to be opened from Office 2010 or later, you can also consider OpenXML. COM Interop is likely to give you alot of problem in unattended, non-interactive client applications. See this http://support.microsoft.com/kb/257757 — John Gathogo, Nov 09 '11 at 14:19

Matt · Accepted Answer · 2011-11-09T14:28:14.517

27

Take a look at MSDN's explanation of Workbooks.Add Method.

Try Workbooks.Add(XlWBATemplate.xlWBATWorksheet), or
See if you can set the xl.SheetsInNewWorkbook property to 0 or 1.

I went ahead and verified this. Here is the code:

using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Application xl = null;
            _Workbook wb = null;

            // Option 1
            xl = new Application();
            xl.Visible = true;
            wb = (_Workbook)(xl.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));

            // Option 2
            xl = new Application();
            xl.SheetsInNewWorkbook = 1;
            xl.Visible = true;
            wb = (_Workbook)(xl.Workbooks.Add(Missing.Value));

        }
    }
}

edited Nov 09 '11 at 14:28

answered Nov 09 '11 at 14:13

Matt

14,353
5
53
65

3

I used the option 2: "xl.SheetsInNewWorkbook =1" and thats solve the problem. – Moti Nov 09 '11 at 14:30
@Moti, Glad to help. Seeing your new here, if the answer is correct, it's customary to accept the answer (check the check-mark). Additionally helpful answers can also be up voted (the up arrow). :) – Matt Nov 09 '11 at 14:32
Seems I can't answer on the question for at least 8 hours as I have "•Users with less than 100 reputation can't answer their own question for 8 hours after asking. You may self-answer in 7 hours. Until then please use comments, or edit your question instead". – Moti Nov 09 '11 at 14:45

score 2 · Answer 2 · edited Aug 22 '17 at 14:06

2

Excel.Application xl = null;
Excel._Workbook wb = null;

xl = new Excel.Application();
xl.SheetsInNewWorkbook = 1;
xl.Visible = true;

wb = (_Workbook)(xl.Workbooks.Add(Missing.Value));

edited Aug 22 '17 at 14:06

shA.t

16,580
5
54
111

answered Jun 24 '13 at 02:38

RAYANED

21
3

C# Create Excel workbook with 1 sheet by default

2 Answers2

Linked