OpenXML与工作表获取表名称 [英] 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")
    {

    }
}

我需要知道我处理这样我就可以不同的方式处理这其中板材。我怎样才能获得工作表的名称（当我在Excel从这里打开它时显示）？

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?

更新：

所以，我确实发现和尝试这个如何使用的OpenXML

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

然而SHEETNAME没有匹配到工作表。

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>();
      ...
 }

这是从工作表中返回的行做不对应于在由SHEETNAME指示的纸张的行。让我来解释进一步

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

我有我的Excel文档中的三个工作表 - 个人，企业，产品（按顺序）

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

在这个循环的第一次迭代 - 我从工作表行获得的数据是指产品表数据，但SHEETNAME称，人们

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

推荐答案

这使得后期的错误的假设，在指数的 WorkbookPart.WorksheetParts 和 Workbook.Sheets 集合一致。 。一要寻找的对决是关系ID，因为这是包部分通信的范例

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.

而不是：

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

一个人能投的 OpenXmlCompositeElement 来的收集组件类型：

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

再通过部分循环时，发现当前部分的关系ID和匹配它的 Sheet.Id （为 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); 
}

另外，根据逆连接可用时，正从该ID的对象

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

public OpenXmlPart OpenXmlContainer.GetPartById (string id);  

这篇关于OpenXML与工作表获取表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！