使用OpenXML SDK 2.0从Excel单元读取数据 [英] Reading data from Excel cells using OpenXML SDK 2.0

问题描述

我正试图通过这种方式从Excel单元中获取价值:

I'm trying to get value from Excel cells in this way:

    SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, true);

    WorksheetPart worksheetPart = getWorksheetByName(spreadSheetDocument, DEFAULT_SHEET_NAME);

    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    Cell theCell1 = worksheetPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == "A5");
    Cell theCell2 = worksheetPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == "A6");
    Cell theCell3 = worksheetPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == "B5");
    Cell theCell4 = worksheetPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == "B6");

然后我正在检查Cell1.CellValue.Text属性，并且得到了一些奇怪的数据，例如4,5,248等，它实际上与真实数据相去甚远.我可以使用Excel查看和编辑实际值.

Then I'm checking theCell1.CellValue.Text propetry and I'm getting some strange data like 4,5,248 etc, which is actually far from real data. Real values I can view and edit using Excel.

有人猜为什么会这样吗?

Does anybody have any guess why it so?

推荐答案

每个Excel单元格中的值(大部分情况下)都存储在称为SharedStringTable的公共位置.该表就像一个数组，在其中添加每个唯一值，然后将其索引作为值放入实际的Excel单元格中.这意味着您要检索的4、5、248实际上是指向该表的实际索引，这些索引指向该单元格的实际值.该表的重点是帮助减少存储的冗余数据量.例如，如果两个单元格包含相同的字符串，则Excel仅需要将该字符串存储在SharedStringTable中一次，然后引用相同的字符串两次作为该单元格的值.这将有助于减小文件的整体大小，因为您不需要在构成Excel文件的实际XML中存储足够多的文本.

The values in each Excel cell (for the most part) are stored a common place called the SharedStringTable. This table acts like an array where each unique value is added and then its index is put as the value in actual Excel cell. That means the 4, 5, 248 that you are retrieving are actually indices into this table that point to the actual values of that cell. The point of this table is to help reduce the amount of redundant data that is stored. For instance, if two cells contain the same string, Excel only needs to store the string once in the SharedStringTable and then reference the same string twice as the value of the cell. This will help to reduce the overall size of the file as you don't need to store as much text in the actual XML that makes up the Excel file.

例如，我在单元格A1和A2中添加了文本"test"，在单元格A3中添加了文本唯一"，这就是SharedStringTable XML的样子:

For example, I added the text 'test' to cells A1 and A2 and the text 'unique' to cell A3 and this is what the SharedStringTable XML looks like:

<x:sst count="3" uniqueCount="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:si>
    <x:t>test</x:t>
  </x:si>
  <x:si>
    <x:t>unique</x:t>
  </x:si>
</x:sst>

注意测试仅存储一次.这是单元格值:

Notice how test is only stored once. Here is the cell values:

<x:c r="A1" t="s">
    <x:v>0</x:v>
  </x:c>
  <x:c r="B1" t="s">
    <x:v>0</x:v>
  </x:c>
  <x:c r="C1" t="s">
    <x:v>1</x:v>
</x:c>

请注意，由于A1和A2都指向SharedStringTable中的相同文本，因此它们的值均为0.

Notice how A1 and A2 both have a value of 0 since they both point to the same text in the SharedStringTable.

通过索引访问SharedStringTable的简单代码段将是:

A simple code snippet to access the SharedStringTable by the index would be:

workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(index);

这篇关于使用OpenXML SDK 2.0从Excel单元读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！