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.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();
    }
}