c# open xml reformatting existing cell to a number

Question

I have an existing spreadsheet. I would like to reformat some cells from general or custom to Number. But for some reason when I reformat them to number the cell values change to some random number.

Here is what my code looks like

           using (var d = SpreadsheetDocument.Open(_docPath, true))
            {
                var workSheet = GetWorksheetPart(d.WorkbookPart.Workbook.WorkbookPart, "Project Summary");

                foreach (var cellRef in _ProjectRequirementsFormService.NumberCellReferences)
                {
                    Cell theCell = workSheet.Worksheet.Descendants<Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
                    if (theCell != null) {
                        //For some reason This line of code replaces the cell value with a value 5197
                        //and every iteration of the loop increments the value of the next cell found 5198, 5199, etc....
                        theCell.DataType = CellValues.Number;
                    }
                }
...
}

    public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
    {
        string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;
        return (WorksheetPart)workbookPart.GetPartById(relId);
    }

Now for some reason This line of code theCell.DataType = CellValues.Number; replaces the cell value with a value 5197 and every iteration of the loop increments the value of the next cell found 5198, 5199, etc....

When I comment that defective line I get the correct values in the cell but the format is not a Number

Here is what it looks like when I add the line of code to change it to number format. Why is it changing all of the cell values and how can I fix it?

Link to example file

https://drive.google.com/file/d/0B7UImeY4gR3VdnBBZmdWSHJqN2lhRmFqY1N6THJXNmIzZDhF/view?usp=sharing

Answer 1

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.