I want to add only one sheet after creating an Excel workbook through C#

Question

The code is this

        Excel.Application appC = new Excel.Application();  
        appC.Visible = true;              
        Excel.Workbook bookC = appC.Workbooks.Add(1);  
        Excel.Worksheet sheetC = bookC.Worksheets.Add();  
        sheetC.Name = "something";

The command Workbook.Add() takes one parameter that is supposed to determine how many sheets will be created in the workbook... right?

So why do I get 2 sheets... one named "something" and one named "sheet 2"? What am I doing wrong??

score 4 · Accepted Answer · edited Jul 18 '11 at 14:40

4

This is the code to create an Excel application object and open a workbook with only ONE sheet and name it as you wish:

Excel.Application appC = new Excel.Application();    
appC.SheetsInNewWorkbook = 1;       
appC.Visible = true;     
Excel.Workbook bookC = appC.Workbooks.Add();    
Excel.Worksheet sheetC = appC.Sheets.get_Item(1);   
sheetC.Name = "name-of-sheet";

edited Jul 18 '11 at 14:40

Jonas Heidelberg

4,984
1
27
41

answered Jul 17 '11 at 17:20

Christos Karapapas

1,018
3
19
40

score 2 · Answer 2 · answered Jul 17 '11 at 11:42

2

The parameter to Workbooks.Add does NOT specify the number of sheets.

See the MSDN description of the Add method.

You should probably use the constant xlWBATWorksheet rather than just "1".

[I'm not at Work and don't have Excel handy; it may be that the value of that constant is actually 1, in which case this will make no (functional) difference. The alternative is to set the SheetsInNewWorkbook property before creating the workbook, or simply deleting the unwanted sheets after creating the workbook.]

answered Jul 17 '11 at 11:42

Gary McGill

26,400
25
118
202

Tnx for the answer, you are right Gary McGill i found that in the way... i was aware of the msdn description however it isn't very useful.. it says that the SheetsInNewWorkbook property does that but for a novice user like me that's not helpful at all, how was i supposed to know that SheetsInNewWorkbook is a property of Application and not Workbook or Worksheet.. took me 2 hours to find out.. that's why i posted the question here.. msdn definitely needs more descriptions, more clear and at least one example for every class, method, property.. thanks again i will post the code soon! – Christos Karapapas Jul 17 '11 at 13:51

score 0 · Answer 3 · answered Dec 12 '13 at 09:39

I faced the same problem. You need to add a sheet like this:

//add 1 sheet
_workbookTemp.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);

//move this sheet to the last position
_workbookTemp.ActiveSheet.Move(After: _workbookTemp.Sheets[_workbookTemp.Sheets.Count]);

score -1 · Answer 4 · answered Jul 17 '11 at 09:04

if you are using vs 2010 it is diffrent you can use the below code to add a work sheet to a work book this i have tried this in VS 2010 this works for me im using excel 2007 work book project template

void AddSheet()
{
 OpenFileDialog excelSheetToOpen = new OpenFileDialog();
            excelSheetToOpen.Filter = "Excel 97- 2003 WorkBook (*.xls)| *.xls | Excel 2007 WorkBook (*.xlsx) | *.xlsx | All files (*.*)|*.*";
            excelSheetToOpen.FilterIndex = 3;
            excelSheetToOpen.Multiselect = false;

             Excel.Worksheet ws = Globals.ThisWorkbook.Worksheets.get_Item("RunningParameters");


             if (excelSheetToOpen.ShowDialog() == DialogResult.OK)
             {

                 Excel.Application excelApp = new Excel.Application();
                 String workbookPath = excelSheetToOpen.FileName;
                 Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath);
                 Excel.Sheets excelWorkBookSheets = excelWorkbook.Sheets;

                 Excel.Range _UsedRangeOftheWorkSheet;


                 foreach (Excel.Worksheet _Sheet in excelWorkBookSheets)
                 {
                     if (_Sheet.Name == ws.get_Range("B3").Value)
                     {
                         _Sheet.UsedRange.Copy();
                         _UsedRangeOftheWorkSheet = _Sheet.UsedRange;

                         Object [,] s = _UsedRangeOftheWorkSheet.Value;                        


                         Excel.Worksheet _WorkingSheet = Globals.ThisWorkbook.Sheets.Add(ws);
                         _WorkingSheet.Name = "WorkingSheet";
                         _WorkingSheet.Paste();



                     }
                 }  

             }


}

This code is directly extracted from my projecte please ammed the code as needed hope this will help to solve your problem

thanks

thanks for your response! but are you sure that this code is generating you only one sheet ? — Christos Karapapas, Jul 17 '11 at 09:20
if this code part executes once it will add one excel sheet to the work book if it runns more than once the it wil throw's an exception saying that another sheet is existing in the work book with a same name. — Prabhakantha, Jul 17 '11 at 10:30
I don't think this code addresses the OP's question... @Prabhakantha maybe you can have another look at your code and how it relates to *creating* a workbook which *contains* only one sheet, instead of *adding* one sheet to an *existing* workbook... — Jonas Heidelberg, Jul 18 '11 at 14:38

I want to add only one sheet after creating an Excel workbook through C#

4 Answers4

Linked