打开XML读取Excel单元格的值 [英] open xml excel read cell value

问题描述

我使用的是开放XML SDK来打开一个Excel .xlsx文件我尝试阅读位置上的A1在cellvalue每一张纸。
我用下面的code：

I am using the Open XML SDK to open an Excel xlsx file and I try to read the cellvalue on position A1 in each sheet. I use the following code:

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位置的第一个工作表中的文本都只是测试不过，在我的控制台我看到了值0作为cell.CellValue.Text

The text in the first worksheet on position A1 is simply 'test' however, in my console I see the value '0' as cell.CellValue.Text

有没有人有一个想法，让电池的正确值？

Does anyone have an idea to get the correct value of the cell?

推荐答案

在Excel工作表的所有字符串存储阵列一状结构叫做SharedStringTable英寸此表的目的是集中所有字符串在基于索引数组，然后，如果该字符串在文档中多次使用，以只在该数组中引用的索引。话虽这么说，当你得到了A1单元格的文本值您收到的0是该指数进入SharedStringTable。要获得真正的价值，你可以使用这个辅助函数：

All strings in an Excel worksheet are stored in a array like structure called the SharedStringTable. The goal of this table is to centralize all strings in an index based array and then if that string is used multiple times in the document to just reference the index in this array. That being said, the 0 you received when you got the text value of the A1 cell is the index into the SharedStringTable. To get the real value you can use this helper function:

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

然后在你的code这样称呼它，以获得真正的价值：

Then in your code call it like this to get the real value:

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

这篇关于打开XML读取Excel单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！