Lets Learn

Opinion Matters

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 7 other followers

  • Blog Stats

    • 72,532 hits
  • Ankush

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

How to Delete a Worksheet from Excel using Open XML SDK 2.0

Posted by Ankush on February 11, 2010


Recently I worked on a scenario where a user wanted to delete a worksheet from a workbook using Open XML SDK 2.0. The worksheet may  contains some formulas, defined names, pivot tables etc…  Though MSDN provides a code snippet which explains how to delete a worksheet, it doesn’t cover the scenarios when we have formula, defined names, pivot tables etc. If you delete the worksheet following the MSDN article, Excel may not open the workbook and would throw an error (Error info goes here).

This blog post demonstrates how to delete a worksheet so that the Workbook opens without errors.

A worksheet in a workbook, apart from its part, also contains other dependent parts, entries inside the workbook. We need to delete the dependent/linked parts and the dependencies along with deleting the worksheet part to be able to completely/correctly delete the worksheet.

Here is the sample code which deletes the formula, defined names, pivot tables, CalculationChain associated with the worksheet being deleted. This probably is not covering all sorts of dependencies that a sheet can have, so you may still see errors even after using this code. In such a case, I encourage you to open the workbook in Visual Studio 2008 (using the cool Visual Studio 2008 power tools plugin) and look for any other traces of the worksheet that may be left over. If you do find something that is currently not covered by the code below, please leave me a comment on this post and I will try to incorporate that.

private void DeleteAWorkSheet(string fileName, string sheetToDelete)
{
     string Sheetid="";
    //Open the workbook
     using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        WorkbookPart wbPart = document.WorkbookPart;

         // Get the pivot Table Parts
         IEnumerable<PivotTableCacheDefinitionPart>  pvtTableCacheParts =wbPart.PivotTableCacheDefinitionParts;
         Dictionary<PivotTableCacheDefinitionPart,string > pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart,string>();
         foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
         {
             PivotCacheDefinition pvtCacheDef= Item.PivotCacheDefinition;
             //Check if this CacheSource is linked to SheetToDelete
             var pvtCahce=pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
             if (pvtCahce.Count() > 0)
             {
                 pvtTableCacheDefinationPart.Add(Item, Item.ToString());
             }
         }
         foreach (var Item in pvtTableCacheDefinationPart)
         {
             wbPart.DeletePart(Item.Key);
         }
         //Get the SheetToDelete from workbook.xml
        Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
        if (theSheet == null)
        {
          // The specified sheet doesn't exist.
        }
         //Store the SheetID for the reference
        Sheetid = theSheet.SheetId;
         
        // Remove the sheet reference from the workbook.
        WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
        theSheet.Remove();

        // Delete the worksheet part.
        wbPart.DeletePart(worksheetPart);

         //Get the DefinedNames
        var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
        if (definedNames != null)
        {
            foreach (DefinedName Item in definedNames)
            {
                // This condition checks to delete only those names which are part of Sheet in question
                if (Item.Text.Contains(sheetToDelete + "!"))
                Item.Remove();
            }
        }
        // Get the CalculationChainPart 
        //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the 
         //workbook whose value is calculated from any formula

         CalculationChainPart calChainPart;
         calChainPart = wbPart.CalculationChainPart;
         if (calChainPart != null)
         {
             var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
             foreach (CalculationCell Item in calChainEntries)
             {
                 Item.Remove();
             }
             if (calChainPart.CalculationChain.Count() == 0)
             {
                 wbPart.DeletePart(calChainPart);
             }
         }
            
        // Save the workbook.
        wbPart.Workbook.Save();
    }
}
Advertisements

3 Responses to “How to Delete a Worksheet from Excel using Open XML SDK 2.0”

  1. PandaWood said

    If you leave ActiveTab set to an index that no longer exists you get a corrupt workbook, so we added this:
    foreach (WorkbookView workbookView in wbPart.Workbook.BookViews) { workbookView.ActiveTab = 0; }

  2. sudipta said

    Great!!! Meterial

  3. Hey Ankush,
    The information available here is great and very helpful in getting insights about open xml.
    I am looking to read an excel which contains pivot tables, but I have not been able to figure out the relation between the pivot tables/cache definitions etc and the data stored in them.
    I need to read the data present in them just like we can read the cell’s of the excel using open xml.
    Can you please provide some information/guide and code snippets.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: