Openxml Sdk Returning a Number for Cellvalue Instead of Cells Text

OpenXML SDK returning a number for CellValue instead of cells text

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

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

OpenXML reading wrong cell value if integer

You need to check the DataType of the cell as the "1" is stored as an actual number, not a string in the shared strings table.

var cell = row.Elements<Cell>().FirstOrDefault();
string cellValue = null;

if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
    //it's a shared string so use the cell inner text as the index into the 
    //shared strings table
    var stringId = Convert.ToInt32(cell.InnerText);
    cellValue = workbookPart.SharedStringTablePart.SharedStringTable
        .Elements<SharedStringItem>().ElementAt(stringId).InnerText;
}
else
{
    //it's NOT a shared string, use the value directly
    cellValue = cell.InnerText;
}

There are a couple of things to note:
1) The default type if one is not provided is Number
2) There are other types that are not handled by the code above. Dates are particularly awkward.

c# open xml reformatting existing cell to a number

Decompile your xlsx to C# using Microsoft Open XML SDK Tools.

https://www.microsoft.com/en-us/download/details.aspx?id=30425

You'll see that your cells have SharedString data type. It's a kind of id, cell stores id, while values are stored in sharedstring table. These weird numbers you see after changing datetype are these ids in sharedstring table.
Changing DataType does not really change the way values are stored, it changes how they are interpreted. They still will be "Number stored as string" if you change its type to numeric.
For example let's take a look at cell "E17":

Cell cell261 = new Cell(){ CellReference = "E17", StyleIndex = (UInt32Value)11U, DataType = CellValues.SharedString };
CellValue cellValue90 = new CellValue();
cellValue90.Text = "26";

...
Should be 50000 isn't it?

        SharedStringItem sharedStringItem27 = new SharedStringItem();
        Text text45 = new Text();
        text45.Text = "50000";
        sharedStringItem27.Append(text45);

In order to read correct value from spreadsheet you need to use GetCellValue code from ms docs sample.

https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet

Then you can write the following code to read real cell values (I changed it a bit and pass document instead of file path) parse it and save to decimal type:

var value = GetCellValue(d, "Sheet1", cellRef.CellReference);

if (!string.IsNullOrEmpty(value))
{
  if (decimal.TryParse(value, out decimal val))
   {
      cellRef.CellValue = new 
      CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(val));
      cellRef.StyleIndex = 0U;                            
   }
 }

StyleIndex 0U appears to be general format, but since you have correct cell type it does not matter. Even though I still did't get why =sum formulas are not calculated when you open fixed xlsx.

---

Related Topics