locked
Drop table(sheet) in excel using OLEDB connection RRS feed

  • Question

  • Good Morning

    We are trying to find a way to drop a sheet in an excel workbook (2007/2010/2013) using an OLEDB connection. We cannot use Interop due to the design pattern as well as where the application will be running.

    We are looking for something in the same vein as the select/insert statements to work with the workbook.

    private OleDbCommand CreateExcelTextCommand()
    {
    OleDbCommand cmd = new OleDbCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = CurrentConnection;
    cmd.Transaction = CurrentTransaction;
    return cmd;
    }
    /// <summary>
    /// Drops the specified target
    /// </summary>
    /// <param name="targetName">Name of the target sheet.</param>
    /// <returns></returns>
    /// <exception cref="System.ArgumentNullException">TargetName</exception>
    public IResult DropSchema(string targetName)
    {
    if (string.IsNullOrEmpty(targetName)) throw new ArgumentNullException("TargetName");
    try
    {
    if (DoesTableExist(targetName))
    {
    targetName = CommonStaticMethods.MakeSheetName(targetName);
    OleDbCommand cmd;
    cmd = CreateExcelTextCommand();
    cmd.CommandText = string.Format("DROP TABLE [{0}];",targetName);
    cmd.ExecuteNonQueryAsync();
    }
    
    return new Result { Success = !DoesSchemaExist(targetName) };
    }
    catch
    {
    throw;
    }
    }

    PLEASE NOTE THAT WE CANNOT USE INTEROP

    Thank you

    Theo

    Friday, May 20, 2016 10:16 AM

Answers

  • >>>We are trying to find a way to drop a sheet in an excel workbook (2007/2010/2013) using an OLEDB connection. 

    According to your description, unfortunately, you cannot delete a worksheet using ADO.NET for Excel.

    >>>We cannot use Interop due to the design pattern as well as where the application will be running.

    You could use Open XML SDK to delete a worksheet from Excel, refer to below code:
    public static void DeleteSheet(string sheetName)
    {
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open("your Excel file path", true))
        {
            var workbookPart = spreadSheet.WorkbookPart;
    
            // Get the SheetToDelete from workbook.xml
            var theSheet = workbookPart.Workbook.Descendants<Sheet>()
                                        .FirstOrDefault(s => s.Name == sheetName);
    
            if (theSheet == null)
            {
                return;
            }
    
            Console.WriteLine(theSheet.Name);
                    
            // Remove the sheet reference from the workbook.
            var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(theSheet.Id));
            theSheet.Remove();
    
            // Delete the worksheet part.
            workbookPart.DeletePart(worksheetPart);
    
            workbookPart.Workbook.Save();
            spreadSheet.Close();
        }
    }
    For more information, click here to refer about Spreadsheets (Open XML SDK)

    • Marked as answer by Theo Jacobs Wednesday, May 25, 2016 12:37 PM
    Monday, May 23, 2016 6:17 AM

All replies

  • >>>We are trying to find a way to drop a sheet in an excel workbook (2007/2010/2013) using an OLEDB connection. 

    According to your description, unfortunately, you cannot delete a worksheet using ADO.NET for Excel.

    >>>We cannot use Interop due to the design pattern as well as where the application will be running.

    You could use Open XML SDK to delete a worksheet from Excel, refer to below code:
    public static void DeleteSheet(string sheetName)
    {
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open("your Excel file path", true))
        {
            var workbookPart = spreadSheet.WorkbookPart;
    
            // Get the SheetToDelete from workbook.xml
            var theSheet = workbookPart.Workbook.Descendants<Sheet>()
                                        .FirstOrDefault(s => s.Name == sheetName);
    
            if (theSheet == null)
            {
                return;
            }
    
            Console.WriteLine(theSheet.Name);
                    
            // Remove the sheet reference from the workbook.
            var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(theSheet.Id));
            theSheet.Remove();
    
            // Delete the worksheet part.
            workbookPart.DeletePart(worksheetPart);
    
            workbookPart.Workbook.Save();
            spreadSheet.Close();
        }
    }
    For more information, click here to refer about Spreadsheets (Open XML SDK)

    • Marked as answer by Theo Jacobs Wednesday, May 25, 2016 12:37 PM
    Monday, May 23, 2016 6:17 AM
  • Thank you David, I have sent the response on to the developer that requested this information

    Theo Jacobs | Technical Team Lead / Senior Developer (MCT, MCP, MCPD, MCTS, MCAS) CulminIT | Durbanville | South Africa Phone (27) 21 914 8009 | tjacobs@culminit.co.za | theojacobs@workmail.co.za | http://www.culminit.co.za

    Monday, May 23, 2016 6:56 AM
  • Hi, Theo Jacobs

    Have you resolved your issue? if have, please mark the reply as an answer if you find it is helpful.

    Thanks for your understanding.
    Tuesday, May 24, 2016 5:38 AM
  • Hi everyone

    This was sort of the answer as this only works on Xlsx documents not the older xls documents.

    As such, i am marking this as the correct answer.

    Thank you


    Theo Jacobs | Technical Team Lead / Senior Developer (MCT, MCP, MCPD, MCTS, MCAS) CulminIT | Durbanville | South Africa Phone (27) 21 914 8009 | tjacobs@culminit.co.za | theojacobs@workmail.co.za | http://www.culminit.co.za

    Wednesday, May 25, 2016 12:37 PM