OpenXML: Deleting a worksheet from a workbook

Working with OpenXML can be quite challenging a times. While working on an update to my CRM Migration Assistant, I ran across the need to remove a worksheet from a workbook because it did not contain data.

The worksheet was pretty simple, just a header and a few columns. If you have such a situation, the following code should do the trick:

public void DeleteSheet(string sheetId)
{
    var workbookPart = SpreadSheet.WorkbookPart;

    // Get the SheetToDelete from workbook.xml
    var theSheet = workbookPart.Workbook.Descendants<Sheet>()

                               .FirstOrDefault(s => s.Id == sheetId);

    if (theSheet == null)
    {
        return;
    }

    // Remove the sheet reference from the workbook.
    var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheetId));
    theSheet.Remove();

    // Delete the worksheet part.
    workbookPart.DeletePart(worksheetPart);
}

You pass in the ID of the worksheet, as identified by OpenXML when you created it.

An alternative would be to pass in the name of the worksheet ( the text that is actually displayed on the tab in Excel ). If that is your requirement, just replace the following two lines of code:

var theSheet = workbookPart.Workbook.Descendants<Sheet>()
                           .FirstOrDefault(s => s.Id == sheetId);

with:

var theSheet = workbookPart.Workbook.Descendants<Sheet>()
                           .FirstOrDefault(s => s.Name == sheetId);

If you need to delete a more complex worksheet, see this article. It uses the same technique but also insures any related workbook components are also deleted.