OLEDB for EXCEL - Drop Table [SheetName$] - Doen't Delete Sheet

Question

I am using drop table [SheetName$] to delete a worksheet from excel.

This just clears the data of the sheet but does not delete the sheet.

I have tried using xls and xlsx. Doesn't work with both versions !

OleDbConnection connection = new OleDbConnection();

try
{
connection.ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='drop.xlsx";
connection.Open();
OleDbCommand command = new OleDbCommand("Drop Table [MySheetName_1$]", connection);
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}

Any Help / Pointers appreciated ! Thanks

Solution

Unfortunately, you cannot delete a worksheet using ADO.NET for Excel. Instead, you will need to use the Excel Interop to perform this task. The basic code for the actual DELETE statement would look something like this:

using MSExcel = Microsoft.Office.Interop.Excel;

private MSExcel._Application excel;
private MSExcel._Workbook workbook;
private MSExcel._Worksheet worksheet;
private MSExcel.Sheets sheet;

Excelapp.DisplayAlerts = false;
((Excel.Worksheet)workBook.Worksheets[3]).Delete();
Excelapp.DisplayAlerts = true;

This is the basic rundown of how it would look. The DisplayAlerts lines are to fix an issue some people had with deleting a sheet. Also note that you cannot delete the last sheet in the Excel file. That issue will get you if you don't watch it.

Here are some links to help you out:

MSDN on deleting sheeting in Excel

Post discussing the possibility of using ADO.NET to DROP a sheet in Excel

SO question about deleting a sheet in Excel using the Interop

Comments

[Baljeetsingh]

Thanks for your reply. Problem is i can not have excel installed on my server because of some X reasons. Which mean i can not use inerop :(.

[IAmTimCorey]

@Baljeetsingh - That's too bad but I definitely understand. I'll look around for other options but I think this might be your only (free) option.

[Juliusz]

@BiggsTRC - Excel is not free as far as know[?] There are open soource libraries which are cabable of doing it without having Excel on the server.

[IAmTimCorey]

@Juliusz - Excel is only free if you already have it, which is what the OP was indicating. No, it isn't free. I was merely saying that there are third-party apps that work with Excel but they usually cost money. If you know of an open-source library that can delete Excel sheets, please share it with us.

[Juliusz]

@BiggsTRC - for xls: code.google.com/p/excellibrary for xlsx I believe epplus.codeplex.com is capable of doing it

[IAmTimCorey]

@Juliusz - I was not able to verify that the ExcelLibrary would handle deleting sheets. It also looks like it has been abandoned (two years with no updates and there are quite a few major outstanding issues). As for the EPPlus library, I didn't see the delete function there either but that looks more promising. However, the one issue (if it does work) is that this code is licensed under the GPLv2 license. That might be a problem in a corporate environment.

[Juliusz]

@BiggsTRC - I don't know from where the two years came from: code.google.com/p/excellibrary/source/list. Licensing issues usually are caused by perception not real legal facts. But back to the subject - I was able to read and create a file using that library, so I would be surprised if you could not get spread sheet removed. It would be probably one line - remove object from a colleciton.