title: OpenXML Writes Values as String and When Converted to Numbers the Values Change
body: <p>I'm using OpenXML to write some data into Excel.  I've successfully managed to input all the data into a spreadsheet but when I do it's all in String format.  This is because I'm mostly utilizing the example code available on <a href="https://msdn.microsoft.com/en-us/library/office/cc861607.aspx" rel="nofollow noreferrer">MSDN</a> and they insert data into Excel as <code>InsertSharedStringItem</code> (A <code>string</code>).  I did however find two ways to convert the cells into numbers.  </p>
  
  <p>The first was a simple <code>CellValues.Number</code> instead of <code>CellValues.SharedString</code> but that seems to change all the actual values in cells as well as changing them to a number.  An example would be I had 0 in a column now it's 5, 11 is now 0 and 21 is 33, weird stuff like that (and if the number repeats like 0 they all change to, in 0's case, 5).  Obviously that won't work.  </p>
  
  <p>I then found another method for converting strings to numbers in Excel and it was found <a href="https://stackoverflow.com/questions/24256243/changing-datatype-of-cell-to-double/28445748#28445748">here</a> (If you click the link in that answer to go to that users source you need to add 'www.' to the URL).  The problem with this solution is that it will convert all the strings into doubles but the values suffer the same mutation as the first solution just in double format this time.</p>
  
  <p>It's also worth noting that my third column is supposed to be a string and doesn't need to be converted to a number but it does it anyways.</p>
  
  <p>This is what I'm working with:</p>
  
  <pre><code>private static void WriteToExcel(string[] contents, char[] desiredColumns)
  {
      string filePath = @"D:\Folder\test.xlsx";
      try
      {
          using (SpreadsheetDocument StatsCollection = SpreadsheetDocument.Open(filePath, true))
          {
              string sheetName = "";
              int totalRows = contents.Length / 15;
              int contentsIndex = 0;
              for (uint indexRow = 1; indexRow &lt; totalRows; indexRow++)
              {
                  for (int indexCol = 1; indexCol &lt; 16; indexCol++)
                  {
                      sheetName = "Sheet1";
                      SharedStringTablePart shareStringPart;
                      if (StatsCollection.WorkbookPart.GetPartsOfType&lt;SharedStringTablePart&gt;().Count() &gt; 0)
                      {
                          shareStringPart = StatsCollection.WorkbookPart.GetPartsOfType&lt;SharedStringTablePart&gt;().First();
                      }
                      else
                      {
                          shareStringPart = StatsCollection.WorkbookPart.AddNewPart&lt;SharedStringTablePart&gt;();
                      }
                      int index = InsertSharedStringItem(contents[contentsIndex], shareStringPart);
                      WorkbookPart bookPart = StatsCollection.WorkbookPart;
                      Sheet mySheet = bookPart.Workbook.Descendants&lt;Sheet&gt;().Where(s =&gt; s.Name == sheetName).FirstOrDefault();
                      WorksheetPart sheetPart = (WorksheetPart)(bookPart.GetPartById(mySheet.Id));
                      Cell cell = InsertCellInWorksheet(desiredColumns[indexCol - 1].ToString(), indexRow, sheetPart);
                      cell.CellValue = new CellValue(index.ToString());
  
                      //I added the following if structure to check for numbers
                      if (IsNumeric(contents[contentsIndex].ToString()))
                      {
                          cell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.Number);
                      }
                      else
                      {
                          cell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.SharedString);
                      }
  
                      /*I removed the following lines                            
                      Stylesheet styleSheet = bookPart.WorkbookStylesPart.Stylesheet;
                      uint _doubleStyleId = createCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(4));
                      cell.StyleIndex = _doubleStyleId;
                      */
                      sheetPart.Worksheet.Save();
                      contentsIndex++;
                  }
              }
              Console.WriteLine("Copy Complete.");
          }
      }
      catch (Exception ex)
      {
          Console.WriteLine("Cannot find output Excel file.\n" + ex.Message);
      }
  }
  </code></pre>
  
  <p>And here is the <code>createCellFormat</code> method that is from that other solution. <strong>Related to my update.</strong> I no longer need this method due to the changes I made but I'll leave it in since it's related to the original question.</p>
  
  <pre><code>private static UInt32Value createCellFormat(Stylesheet styleSheet, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value numberFormatId)
  {
      CellFormat cellFormat = new CellFormat();
      if (fontIndex != null)
      {
          cellFormat.FontId = fontIndex;
      }
      if (fillIndex != null)
      {
          cellFormat.FillId = fillIndex;
      }
      if (numberFormatId != null)
      {
          cellFormat.NumberFormatId = numberFormatId;
          cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
      }
      styleSheet.CellFormats.Append(cellFormat);
      UInt32Value result = styleSheet.CellFormats.Count;
      styleSheet.CellFormats.Count++;
      return result;
  }
  </code></pre>
  
  <p>When I try to convert cell values from strings to numbers it converts as expected but changes the value.  Why is this happening and how can I solve this issue as well prevent my one column of actual strings from being changed to a number?</p>
  
  <p><strong>Update:</strong></p>
  
  <p>I managed to fix the string to number conversion by following <a href="https://forums.asp.net/post/5217068.aspx" rel="nofollow noreferrer">this</a> solution and I've updated my code accordingly and added the following method.</p>
  
  <pre><code>private static bool IsNumeric(string value)
  {
      double output = 0;
      if(Double.TryParse(value, out output))
      {
          return true;
      }
      else
      {
          return false;
      }
  }
  </code></pre>
  
  <p>Now everything that should be a number is a number and everything that needs to remain a string is a string.  However the actual values of my numbers are still being altered (numbers that should be 0 are 5, 11 is 0, 21 is 33, etc.)</p>