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.