Obtaining a Worksheet by it's codename

Question

Every Excel worksheet has a SheetName (name displayed to the user) and a Codename (internal immutable name not visible by the user), see picture below. I need to obtain a worksheet by it's codename instead of by it's SheetName. The reason is that if the user changes the SheetName, my actual code won't work anymore.

This works as long as the suer won't change the SheetName "Sales" to something else:

var myworksheet = Worksheet("Sales");

I need the hypothetic function WorksheetByCodeName so I could write this (which would work also if the user has changed the SheetName from "Sales" to something else:

var myworksheet = WorksheetByCodeName("Sheet1");

This image is only for illustration. The question is unrelated to VBA.

More information about worksheet codenames

Answer 1

You can't do that in ClosedXML. But you can use OpenXML (which you already have as requirement for ClosedXML):

static string GetWorksheetNameForCodeName(string filename, string codename)
{
    DocumentFormat.OpenXml.Packaging.WorksheetPart wsp = SpreadsheetDocument
        .Open(filename, false).WorkbookPart.WorksheetParts
        .SingleOrDefault(ws => ws.Worksheet.SheetProperties.CodeName == codename);

    return wsp == null ? string.Empty : wsp.Worksheet.SheetProperties.LocalName;
}