CODEWITHSUNDEEP
c#excelinteropexport-to-excel
Christos Karapapas
Christos Karapapas

Reputation: 1086

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

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??

Upvotes: 1

Views: 8619

Answers (4)

Yousi
Yousi

Reputation: 1163

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]);

Upvotes: 0

Christos Karapapas
Christos Karapapas

Reputation: 1086

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";

Upvotes: 4

Gary McGill
Gary McGill

Reputation: 27536

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.]

Upvotes: 2

Prabhakantha
Prabhakantha

Reputation: 660

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

Upvotes: -1

Related Questions