title: C#: OpenXML numeric Excel cells not returning correct values
body: <p>this is my first post so sorry if it doesn't look good or if the formatting is weird.</p>
  
  <p>Anyways, I need to find a way to get the correct value of numeric (non-string) cells using OpenXML, but with some spreadsheets my current method doesn't seem to work.</p>
  
  <p>There seems to be a difference in how I need to code for varying spreadsheets.</p>
  
  <p>Once I've accessed an Excel file and have opened one of its Sheets I get the number of columns and rows for the current sheet and begin my loops. Within these loops is the code below that is used to add each cell to the datatable using the Cell Reference (made using the parent loops) as the guide. This is the looped through code:</p>
  
  <pre><code>//making the cell index/location of the cell (for example: B10, E17, AE14, ...) based on our row/column indexing so far
  cellRef = ConvertColNumToLetter(startColNum + column) + (y + StartRow).ToString();
  //getting the specific cell at the cell index of our cellRef
  workingCell = cells.Where(c =&gt; c.CellReference == cellRef).FirstOrDefault();
  
  //using a try-catch to solve the issue of the program failing whenever the cell has no inner text to take
  try
  {
      if (workingCell.InnerText == null)
          value = "";
      else
      {
          value = workingCell.InnerText;
      }
  
      //if the cell's data is a string
      if (workingCell.DataType != null &amp;&amp; workingCell.DataType.Value == CellValues.SharedString)
      {
          var stringTable = workbookPart.GetPartsOfType&lt;SharedStringTablePart&gt;().FirstOrDefault();
          if (stringTable != null)
              value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
          dataRow[colIterator] = value.ToString();
          colIterator++;
      }
      //if the cell's data is numeric
      else if (workingCell.CellValue != null)
      {
          value = workingCell.CellValue.InnerText.ToString();
          dataRow[colIterator] = value;
          colIterator++;
      }
      //if the cell doesn't have any data
      else
      {
          dataRow[colIterator] = "";
          colIterator++;
      }
  }
  //this will only fail if the cell didn't have any data/the data is null
  catch
  {
      dataRow[colIterator] = "";
      colIterator++;
  }
  </code></pre>
  
  <p>My code works just fine for 99% of the spreadsheets it encounters. My only problem is with numeric values not being accessed correctly on one specific spreadsheet but it seems to get the correct numeric values on all other spreadsheets. </p>
  
  <p>With the normal spreadsheets, whatever numeric value that is stored in the formula is returned. As an example, here is a cell's value from one such spreadsheet: "31313308.87". When my program reaches that cell, here is the workingCell's details (the workingCell is the cell at the current cell reference location): </p>
  
  <p><a href="https://i.stack.imgur.com/fyAuU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fyAuU.png" alt="CellValue and InnerText match the desired value"></a></p>
  
  <p>which as you can see are the values that I would want and would go through the program and store the correct values into the datatable.</p>
  
  <p>But the spreadsheet that's giving me issues is strange. When I get to one of the numeric cells I end up just getting the completely wrong values. Opening up the actual spreadsheet, there is a numeric cell with a value of "502028.6", but my program gets this in the workingCell's details: 
  <a href="https://i.stack.imgur.com/TzWZ2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TzWZ2.png" alt="CellValue and InnerText are both completely wrong"></a></p>
  
  <p>I'm not sure where the "279907" comes from or how I should go about getting the correct values.</p>
  
  <p>Here's another strange thing. The next three values of the spreadsheet are zeros which all return 0 in the CellValue and InnerText of the workingCell. The next value on the spreadsheet is "-160", and in the workingCell's details it shows: </p>
  
  <p><a href="https://i.stack.imgur.com/YhmdR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YhmdR.png" alt="CellValue and InnerText are wrong yet again"></a></p>
  
  <p>1500 in the CellValue and InnerText?</p>
  
  <p>Again, this is the only spreadsheet that this is happening on (or at least the only one that I know of). The program returns the correct string values as well, but on this one specific spreadsheet, none of the numeric values seem to be picked up with my program (despite it working and returning the correct numeric values for the hundreds of other spreadsheets I've used it on so far).</p>
  
  <p>Is there some simple fix that I'm just not seeing? I would love any pointers or suggestions you may have for me, and I will be happy to clarify anything if you need some additional information.</p>
  
  <p>Thank you for all of your help in advance!</p>
  
  <p>--EDIT:<br>
  I forgot to specify when I asked the question, but the incorrect data (the incorrect CellValues and InnerTexts) is actually successfully processed into the else if statement then added to the datatable. I don't have any problem as far as adding values to the datatable goes, but I just can't seem to get the correct CellValues and InnerTexts for numeric cells on the one specific spreadsheet</p>