Getting Worksheet based on the sheet name

 

When programmatically working with existing Excel documents I find that I need to modify a particular sheet in the Excel file and I would like to be able to find it by the name that the user sees in Excel. This is particularly handy when generating files from an existing Excel file that I am using as a template.

Here is the snippet of code to do just that.

private WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName)
{     Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
    if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", sheetName));
    else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
}

 

Example Usage:

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open("MyFile.xlsx", true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookPart, "Sheet1");

}

 

Let’s break this into easy chunks. The first line just opens the Excel file called MyFile.xlsx. The second line gets the one and only WorkbookPart that all Excel files need. The third line calls our method. This is where it is a little strange in my opinion.

Breaking down the GetWorksheetFromSheetName method.

On the first line we get the Workbook itself (the root element) that is related to the WorkbookPart object. Then we ask it to give us all the child elements that are of type Sheet and only give us the first one we find with the name specified by sheetName parameter.

On the second line we check if the result was null. If it was null this means we didn’t find the sheet and we throw an exception.

On the third line we get the Id of the sheet we found and as the workbookPart to give us the WorksheetPart