Reading data from Excel cells using OpenXML SDK 2.0

Question

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");

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?

score 4 · Accepted Answer · answered Nov 01 '12 at 10:36

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.

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>

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

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

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

Reading data from Excel cells using OpenXML SDK 2.0

1 Answers1

Linked