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


Update (8 Feb 2013):- The sample code has been fixed for issues where it did not correctly delete all defined names and calculation cells.

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)
        {
            List<DefinedName> defNamesToDelete = new List<DefinedName>();

            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 + "!"))
                    defNamesToDelete.Add(Item);
            }

            foreach (DefinedName Item in defNamesToDelete)
            {
                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);
            List<CalculationCell> calcsToDelete = new List<CalculationCell>();
            foreach (CalculationCell Item in calChainEntries)
            {
                calcsToDelete.Add(Item);
            }

            foreach (CalculationCell Item in calcsToDelete)
            {
                Item.Remove();
            }

            if (calChainPart.CalculationChain.Count() == 0)
            {
                wbPart.DeletePart(calChainPart);
            }
        }

        // Save the workbook.
        wbPart.Workbook.Save();
    }
}

 


Comments (8)

  1. Josh says:

    I’m not familiar with the “Visual Studio 2008 power tools plugin” you reference in your article.  Can you point me to it?

    Thanks,

    josh

  2. Hi Josh,

    You can download the Microsoft Visual Studio Tools for the Office System Power Tools from:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=46B6BF86-E35D-4870-B214-4D7B72B02BF9&displaylang=en

    HTH,

    Praveen.

  3. Scott says:

    I'm having trouble getting the last part of this code (which removes the CalculationCells from the CaclulcationChain) to work successfully. Stepping through it in VS2010 and using Open XML SDK 2.0 (2.0.5022), the foreach executes only once, removing only the first CalculationCell, leaving all of the others for the sheet I am deleting. As a result, the workbook is corrupt and cannot be opened by Excel. Help, what am I doing wrong?

  4. Hi Scott,

    Please share the workbook (before deleting worksheet) on skydrive.

    -Praveen

  5. Scott says:

    Hi Praveen, here is a link to the workbook on skydrive: http://sdrv.ms/Ya5W5m

    Thanks,

    Scott

  6. Hi Scott,

    Thanks for the workbook.

    Yes, the above code is incorrectly removing the calc entry in the same foreach loop that it is iterating. We should store the items to remove in a list or something and delete them outside that foreach loop. That is,

    replace :-

    ———–

    foreach (CalculationCell Item in calChainEntries)

                {

                    Item.Remove();

                }

    ———-

    with :-

    ————–

    List<CalculationCell> calcsToDelete = new List<CalculationCell>();

    foreach (CalculationCell Item in calChainEntries)

    {

         calcsToDelete.Add(Item);

    }

    foreach (CalculationCell Item in calcsToDelete)

    {

           Item.Remove();

    }

    ————–

    This applies to the code that is deleting the definedNames as well.  That is, we will need to replace:-

    —————

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

    }

    —————

    with:-

    ————–

    List<DefinedName> defNamesToDelete = new List<DefinedName>();

    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 + "!"))

           defNamesToDelete.Add(Item);

    }

    foreach (DefinedName Item in defNamesToDelete)

    {

       Item.Remove();

    }

    ————–

    Thanks for reporting the problem. I will update the post to reflect these corrections in some time.

    Thanks,

    Praveen

  7. Scott says:

    Hi Praveen, that works great now, thanks for the quick response!

  8. Auston says:

    Great solution! I was able to convert this to VB and use it in my project and only had to change a couple structural things to make it work. Will reference this article in my code.