OpenXML get sheet name from Worksheet

Question

I'm iterating over my worksheets like so

WorkbookPart wbPart = doc.WorkbookPart;
SharedStringTablePart sstPart = wbPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable sst = sstPart.SharedStringTable;

foreach (var wsp in wbPart.WorksheetParts)
{
    Worksheet ws = wsp.Worksheet;

    // i want to do something like this
    if (ws.Name == "People_Sheet")
    {

    }
}

I need to know which sheet i'm processing so I can handle it differently. How can I get the name of the sheet (that is displayed when i open it in excel from here)?

If I get a list of sheets i can find it through attributes

doc.WorkbookPart.Workbook.Sheets.ToList().ForEach(x => Console.WriteLine(x.GetAttribute("name", "").Value));

But what is the relationship between a Sheet and a Worksheet? How can I get the corresponding Sheet or sheet name from a Worksheet?

UPDATE:

So I did find and try this How to retrieve Tab names from excel sheet using OpenXML

However the sheetName did not match up to the worksheet.

 foreach (var wsp in wbPart.WorksheetParts)
 {
      Worksheet worksheet = wsp.Worksheet;
      var sheetName = wbPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;
      var rows = worksheet.Descendants<Row>();
      ...
 }

The rows that are returned from the worksheet do not correspond to the rows that are in the sheet indicated by sheetName. Let me try explain further

I have three worksheets in my excel document - People, Businesses, Products (in that order)

In the first iteration of that loop - the data i get from worksheet rows refers to Products sheet data, but the sheetName says "People"

Janssen

possible duplicate of How to retrieve Tab names from excel sheet using OpenXML – Neu

I'll update my original answer to respond to that comment. – Janssen

I would highly recommend not using Open XML. It's slow and the api is awful. Have a look at the EPPplus project which I find is much better for dealing with Excel. epplus.codeplex.com – Diarmid

@Diarmid - this is year 2022. Your comment about open XML being awful still holds. If you are trying to work with this api for an one off excel project, you will go insane trying to keep track of what's what. They may have over engineered this api for a reason. I just hope someone came with a wrapper that abstracts all the details. Unfortunately, EPPlus is no longer an open source project. You will have to license it for commercial use. – Dobson

Answer 1

That post makes the false assumption that indices in the WorkbookPart.WorksheetParts and Workbook.Sheets collections coincide. The matchup one should be looking for is the relationship id, as that's the paradigm of package part communication.

Instead of:

var workbook = doc.WorkbookPart.Workbook;
workbook.Sheets.ToList().ForEach(x => Console.WriteLine(x.GetAttribute("name", "").Value));

one could cast the OpenXmlCompositeElement to its' collection component type:

var sheets = workbook.Sheets.Cast<Sheet>().ToList();
sheets.ForEach(x => Console.WriteLine(
      String.Format("RelationshipId:{0}\n SheetName:{1}\n SheetId:{2}"
      , x.Id.Value, x.Name.Value, x.SheetId.Value)));

Then when looping through parts, find the relationship id of the current part and match it against the Sheet.Id (as an OpenXmlLeafElement):

foreach (var w in doc.WorkbookPart.WorksheetParts)
{
    string partRelationshipId = doc.WorkbookPart.GetIdOfPart(w);
    var correspondingSheet = sheets.FirstOrDefault(
        s => s.Id.HasValue && s.Id.Value == partRelationshipId);
    Debug.Assert(correspondingSheet != null); 
}

Also the inverse connection is available, getting the object from the id:

public OpenXmlPart OpenXmlContainer.GetPartById (string id);  

Priestly