CODEWITHSUNDEEP
c#openxmlopenxml-sdk
rocklobster
rocklobster

Reputation: 619

OpenXML get sheet name from Worksheet

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"

Upvotes: 5

Views: 29783

Answers (1)

andrei.ciprian
andrei.ciprian

Reputation: 3025

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);

Upvotes: 19

Related Questions