logo标签列表

OpenXML SDK返回的是单元格值而不是单元格文本

c#openxmlopenxml-sdk
59

我正在使用 Open XML SDK 打开一个 Excel xlsx 文件，并尝试读取每个工作表中 A1 单元格的值。 我使用以下代码：

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(openFileDialog1.FileName, false))
{
    var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>();

    foreach (Sheet sheet in sheets)
    {
        WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
        Worksheet worksheet = worksheetPart.Worksheet;

        Cell cell = GetCell(worksheet, "A", 1);

        Console.Writeline(cell.CellValue.Text);
     }
}

private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
     Row row = GetRow(worksheet, rowIndex);

     if (row == null)
         return null;

     return row.Elements<Cell>().Where(c => string.Compare
               (c.CellReference.Value, columnName +
               rowIndex, true) == 0).First();
}

// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
    return worksheet.GetFirstChild<SheetData>().
          Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}

第一个工作表中A1位置的文本只是“test”，但在控制台中，我看到单元格的值为“0”，即cell.CellValue.Text。

有人有想法如何获取单元格的正确值吗？

5个回答
85

Excel工作表中的所有字符串都存储在称为SharedStringTable的类似数组的结构中。该表的目标是将所有字符串集中到基于索引的数组中，然后如果该文档中多次使用该字符串，则只引用此数组中的索引。话虽如此，当您获取A1单元格的文本值时收到的0是SharedStringTable中的索引。要获取实际值，您可以使用此辅助函数：

public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
{
    return workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
}

然后在您的代码中像这样调用它以获取实际值：

Cell cell = GetCell(worksheet, "A", 1);

string cellValue = string.Empty;

if (cell.DataType != null)
{
    if (cell.DataType == CellValues.SharedString)
    {
       int id = -1;

       if (Int32.TryParse(cell.InnerText, out id))
       {
           SharedStringItem item = GetSharedStringItemById(workbookPart, id);

           if (item.Text != null)
           {
               cellValue = item.Text.Text;
           }
           else if (item.InnerText != null)
           {
               cellValue = item.InnerText;
           }
           else if (item.InnerXml != null)
           {
               cellValue = item.InnerXml;
           }
       }
    }
}
7
这是正确的，但未涉及所需解决的所有问题。在查找SST中的单元格值之前，您需要实际确定单元格值是否表示SST索引或实际上是一个值。 - Samuel Neff
4
默认情况下，Excel会将所有基本字符串放入SST中，在这个问题中，他只关心获取这个基本字符串值。不需要过于复杂化基本的场景。如果他处理的是公式或其他数据片段，则显然需要更改以上代码以纳入您的评论。 - amurra
6
我倾向于支持Amurra的观点——原帖只是询问基本价值。通过这些评论，他现在知道可能需要考虑其他因素，这个答案已经足够回答问题。其他像公式之类的东西可以在另一个问题中询问。 - Todd Main
13
我添加这个评论是因为实际上如何确定单元格值是否表示SST索引的解决方案从某些原因未被发布（非常恼人）：如果（cell.DataType！= null && cell.DataType == CellValues.SharedString）。 - genki
Samuel和Genki是正确的。如果有一个日期或其他不共享字符串的值，这段代码将无法从ID中获取SI，因为ID已经是一个值，而不是实际的SI标识。 - donkz
显示剩余3条评论
16

Amurra的回答似乎已经解决了问题的大部分，但可能需要一些细微差别。

1）函数“GetSharedStringItemById”返回一个SharedStringItem而不是字符串，因此调用代码示例将无法工作。为了获取实际值作为字符串，我认为您需要请求SharedStringItem的InnerText属性，如下所示：

public static string GetSharedStringItemById(WorkbookPart workbookPart, int id)
{
    return workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id).InnerText;
}

2）该函数在其签名中也（正确地）要求一个整数，但是示例代码调用提供了一个字符串cell.CellValue.Text。将字符串转换为整数很简单，但需要这样做，因为按照编写的代码可能会令人困惑。

12

我很久以前找到了这个非常有用的片段，所以无法提及作者。

private static string GetCellValue(string fileName, string sheetName, string addressName)
    {
        string value = null;

        using(SpreadsheetDocument document =  SpreadsheetDocument.Open(fileName, false))
        {
            WorkbookPart wbPart = document.WorkbookPart;

            // Find the sheet with the supplied name, and then use that Sheet
            // object to retrieve a reference to the appropriate worksheet.
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
              Where(s => s.Name == sheetName).FirstOrDefault();

            if(theSheet == null)
            {
                throw new ArgumentException("sheetName");
            }

            // Retrieve a reference to the worksheet part, and then use its 
            // Worksheet property to get a reference to the cell whose 
            // address matches the address you supplied:
            WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().
              Where(c => c.CellReference == addressName).FirstOrDefault();

            // If the cell does not exist, return an empty string:
            if(theCell != null)
            {
                value = theCell.InnerText;

                // If the cell represents a numeric value, 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(theCell.DataType != null)
                {
                    switch(theCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            // For shared strings, look up the value in the shared 
                            // strings table.
                            var stringTable = wbPart.
                              GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            // If the shared string table is missing, something is 
                            // wrong. Return the index that you found 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;
    }
8
这是来自 MSDN 网站的链接：http://msdn.microsoft.com/zh-cn/library/office/ff921204(v=office.14).aspx。 - nickvane
2
这段代码太慢了，加载超过5x5的表格根本不实用。添加一行需要大约200毫秒！ - donkz
1
这很可能是因为它每次都在打开文件。 :) - Chris Rae
4

我发现这篇读取整个Excel数据作为数据表的文章非常有帮助。它还使用了Open-XML SDK。

using System;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static DataTable ReadAsDataTable(string fileName)
{
    DataTable dataTable = new DataTable();
    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData sheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> rows = sheetData.Descendants<Row>();

        foreach (Cell cell in rows.ElementAt(0))
        {
            dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));
        }

        foreach (Row row in rows)
        {
            DataRow dataRow = dataTable.NewRow();
            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
            {
                dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
            }

            dataTable.Rows.Add(dataRow);
        }

    }
    dataTable.Rows.RemoveAt(0);

    return dataTable;
}

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.CellValue.InnerXml;

    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

注意：读取Excel时，可能会忽略每行中的空单元格。因此，当您确定每行中的每个单元格都有一些数据时，此代码最适合使用。如果您希望进行适当处理，则可以执行以下操作：

更改for循环的代码：

dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

为了

Cell cell = row.Descendants<Cell>().ElementAt(i);
int actualCellIndex = CellReferenceToIndex(cell);
dataRow[actualCellIndex] = GetCellValue(spreadSheetDocument, cell);

并添加以下方法，该方法用于上述修改后的代码片段：

private static int CellReferenceToIndex(Cell cell)
{
    int index = 0;
    string reference = cell.CellReference.ToString().ToUpper();
    foreach (char ch in reference)
    {
        if (Char.IsLetter(ch))
        {
            int value = (int)ch - (int)'A';
            index = (index == 0) ? value : ((index + 1) * 26) + value;
        }
        else
            return index;
    }
    return index;
}

我从这个答案中得到了这个解决方法。

2
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接