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
Anonymous
February 05, 2010
I'm not familiar with the "Visual Studio 2008 power tools plugin" you reference in your article. Can you point me to it? Thanks, joshAnonymous
February 05, 2010
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.Anonymous
February 05, 2013
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?Anonymous
February 05, 2013
Hi Scott, Please share the workbook (before deleting worksheet) on skydrive. -PraveenAnonymous
February 05, 2013
Hi Praveen, here is a link to the workbook on skydrive: http://sdrv.ms/Ya5W5m Thanks, ScottAnonymous
February 05, 2013
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
Anonymous
February 05, 2013
Hi Praveen, that works great now, thanks for the quick response!Anonymous
June 20, 2014
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.- Anonymous
January 06, 2017
Old post yet still helpful code to me. But needs to support spaces in doc name/sheet name.Something like string itemName = item.Text.Replace("'", ""); if (itemName.Contains(sheetToDelete + "!"))Hope doc name doesn't collide with sheetToDelete name, otherwise it deletes the wrong def names possibly.
- Anonymous