.Net Excel Interop Deleting a worksheet

29

votes

I'm trying to delete a worksheet from a excel document from a .Net c# 3.5 application with the interop Excel class (for excel 2003).

I try many things like :

Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
worksheet.Delete();

It's doesn't work and doesn't throw any error ...

c#.netexcelinterop

72

votes

After more than one hour looking I found the answer:

xlApp.DisplayAlerts = false;
worksheet.Delete();
xlApp.DisplayAlerts = true;

12

votes

When dealing with deleting Excel Worksheets, there are two important things to know:

Excel interop counts from 1 (and not from zero), therefore, removing the second item will cause the third item to take its place!. so, the proper way to remove worksheets is from the last to the first:
```
// Remove LAST worksheet
MyWorkBook.Worksheets[3].Delete();

// and only then remove the second (which is the last one)
MyWorkBook.Worksheets[2].Delete();
```
alternatively, you can delete the second item ([2]) on the list twice, which will give you the same result.
The following line will throw exception when you only got one worksheet left:
```
 MyWorkBook.Worksheets[1].Delete();
```

8

votes

It is also important to note that the workbook must contain at least one worksheet; this means you cannot delete all worksheets in a workbook.

2

votes

Microsoft.Office.Interop.Excel.Worksheet worksheet = Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];
worksheet.Delete();

-1

votes

Try to find worksheet by name:

var app = new Microsoft.Office.Interop.Excel.Application();
var workbook = app.Workbooks.Add();
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["Sheet3"]).Delete();

-1

votes

we can delete the work sheet like this

 Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

                if (xlApp == null)
                {

                    return;
                }


                xlApp.DisplayAlerts = false;
                string filePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
                                        + "\\Sample.xlsx";
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                Excel.Sheets worksheets = xlWorkBook.Worksheets;

                worksheets[4].Delete();
                worksheets[3].Delete();
                xlWorkBook.Save();
                xlWorkBook.Close();

                releaseObject(worksheets);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

and use this

  static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                throw ex;

            }
            finally
            {
                GC.Collect();
            }
        }

-1

votes

We delete excel worksheets from a c# console application like this:

 Microsoft.Office.Interop.Excel.Worksheet worksheet = 
 (Worksheet)workbook.Worksheets["Worksheet_Name" (or) "Countings"];
 worksheet.Delete();

.Net Excel Interop Deleting a worksheet

7 Answers