使用Open XML SDK 2.0按名称访问Excel 2010工作表

Question

我有一个名为Sheet1、Sheet2和Sheet3的Excel 2010电子表格。

我想通过名称获取对工作表的引用。

我正在使用以下代码：

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的引用，但是我找不到方法来实现这一点。

我离目标更近了，但还没到达:

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

这使我得到了工作表的引用，但没有得到工作表上的数据。

谢谢

- Rick Hodder

Answer 1

你真正想要的是WorksheetPart，它包含你正在寻找的SheetData。仅获取Workbook下的Sheets将只为您提供有关工作表的某些元数据。以下是一个示例，用于获取该WorksheetPart（根据需要添加错误检查，因为我假定通过调用First而不是FirstOrDefault已存在sheetName）

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引用，您就能从那里找到所需的数据。

- amurra

Answer 2

这是一些用于处理具有特定选项卡或表名称并转储到类似CSV（我选择了管道而不是逗号）的电子表格的代码。

我希望更容易从单元格中获取值，但我认为这就是我们遇到的问题。您可以看到我引用了MSDN文档，其中大部分是我获得的代码。这就是Microsoft推荐的。

    /// <summary>
    /// Got code from: https://msdn.microsoft.com/en-us/library/office/gg575571.aspx
    /// </summary>
    [Test]
    public void WriteOutExcelFile()
    {
        var fileName = "ExcelFiles\\File_With_Many_Tabs.xlsx";
        var sheetName = "Submission Form"; // Existing tab name.
        using (var document = SpreadsheetDocument.Open(fileName, isEditable: false))
        {
            var workbookPart = document.WorkbookPart;
            var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
            var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
            var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

            foreach (var row in sheetData.Elements<Row>())
            {
                foreach (var cell in row.Elements<Cell>())
                {
                    Console.Write("|" + GetCellValue(cell, workbookPart));
                }
                Console.Write("\n");
            }
        }
    }

    /// <summary>
    /// Got code from: https://msdn.microsoft.com/en-us/library/office/hh298534.aspx
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="workbookPart"></param>
    /// <returns></returns>
    private string GetCellValue(Cell cell, WorkbookPart workbookPart)
    {
        if (cell == null)
        {
            return null;
        }

        var value = cell.CellFormula != null
            ? cell.CellValue.InnerText 
            : cell.InnerText.Trim();

        // If the cell represents an integer number, you are done. 
        // For dates, this code returns the serialized value that 
        // represents the date. The code handles strings and 
        // Booleans individually. For shared strings, the code 
        // looks up the corresponding value in the shared string 
        // table. For Booleans, the code converts the value into 
        // the words TRUE or FALSE.
        if (cell.DataType == null)
        {
            return value;
        }
        switch (cell.DataType.Value)
        {
            case CellValues.SharedString:

                // For shared strings, look up the value in the
                // shared strings table.
                var stringTable =
                    workbookPart.GetPartsOfType<SharedStringTablePart>()
                        .FirstOrDefault();

                // If the shared string table is missing, something 
                // is wrong. Return the index that is in
                // the cell. Otherwise, look up the correct text in 
                // the table.
                if (stringTable != null)
                {
                    value =
                        stringTable.SharedStringTable
                            .ElementAt(int.Parse(value)).InnerText;
                }
                break;

            case CellValues.Boolean:
                switch (value)
                {
                    case "0":
                        value = "FALSE";
                        break;
                    default:
                        value = "TRUE";
                        break;
                }
                break;
        }
        return value;
    }

- Jess