打开XML SDK 2.0可以按名称访问excel 2010工作表 [英] Open XML SDK 2.0 to get access to excel 2010 worksheet by name

问题描述

我有一个Excel 2010电子表格，其中包含3个工作表，分别名为Sheet1，Sheet2和Sheet3.

I have an Excel 2010 spreadsheet that has 3 worksheets named Sheet1, Sheet2 and Sheet3.

我正在尝试通过名称获取对工作表的引用.

I'm trying to get a reference to a worksheet by name.

我正在使用代码:

using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(FileName, true))
{
    //Access the main Workbook part, which contains all references 
    WorkbookPart workbookPart = myWorkbook.WorkbookPart;

    WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); 

    // this gives me Sheet1
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
}

我正在尝试获取对Sheet2的引用，但是我找不到实现此目的的方法.

I am trying to get a reference to Sheet2, but I cannot find a way to do this.

我越来越近了，但我还没到那里:

I'm getting closer, but I'm not there yet:

var x = workbookPart.Workbook.Sheets.Where(s=> s.GetAttribute("name", "").Value == "Sheet2").FirstOrDefault();

这使我可以参考工作表，但不能参考工作表上的数据

That gets me a reference to the sheet, but not to the data on the sheet

谢谢

推荐答案

您真正想要的是WorksheetPart，它包含您要查找的SheetData.抓住Workbook下的Sheets仅会为您提供有关工作表的某些元数据.这是一个有关如何获取该WorksheetPart的示例(您可以通过调用First而不是FirstOrDefault随意添加错误检查，因为我认为sheetName已经存在)

What you really want is the WorksheetPart which is what contains the SheetData that you are looking for. Grabbing the Sheets under the Workbook will only give you certain metadata about the worksheets. Here is an example on how to grab that WorksheetPart (feel free to add error checking as you see fit as I assume the sheetName already exists by calling First and not FirstOrDefault)

public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
{
    string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;
    return (WorksheetPart)workbookPart.GetPartById(relId);
}

然后只需使用上面的代码来获取正确的SheetData引用，您就可以从那里找到所需的数据.

Then just use your code above to grab the correct SheetData reference and you will be able to find the data you want from there.

这篇关于打开XML SDK 2.0可以按名称访问excel 2010工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！