title: C# Open XML 2.0 NumberFormatId range
body: <p>Working with Open XML 2.0 using c# to parse large excel files.  Issue I'm running into is the cell I'm parsing does not have a DataType I then check the NumberFormatId to determine if it is decimal, number or date.  I'm looking for the exact NumberFormatId range for numbers/decimals vs dates.  They seem to be all over the place some numbers/decimals have formats of 189,212,214,305 and dates having values of 185, 194, 278 etc.  Does anyone know if the specification defines these ranges?</p>
  
  <p><strong>Edited - More Information</strong></p>
  
  <p>Below is an example of the number format of 194 from the style.xml file inside the xl folder.</p>
  
  <p>The excel sheets are from different regions of the world so I'm thinking the number formats are different, but do they overlap?  Will numFmtId 194 be something other than a date on different culture settings?</p>
  
  <p>Below is how I'm converting c.CellValues like "40574" to dates, but the issue is how do I know if "40574" is a date and not a number? </p>
  
  <pre><code> DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
  </code></pre>
  
  <p>Currently I'm doing this by checking if there is no DataType than check the CellFormat but there are issues when some of the NumberFormatId are not in my check.</p>
  
  <pre><code>    private Object FormatCellValue(Cell c, SharedStringTable ssTable, CellFormats cellFormats)
              {
                  if (c.CellValue != null)
                  {
                      // If there is no data type, this must be a string that has been formatted as a number
                      if (c.DataType == null)
                      {
                          CellFormat cf;
                          if (c.StyleIndex == null)
                          {
                              cf = cellFormats.Descendants&lt;CellFormat&gt;().ElementAt&lt;CellFormat&gt;(0);
                          }
                          else
                          {
                              cf = cellFormats.Descendants&lt;CellFormat&gt;().ElementAt&lt;CellFormat&gt;(Convert.ToInt32(c.StyleIndex.Value));
                          }
  
  
                          if ((cf.NumberFormatId &gt;= 14 &amp;&amp; cf.NumberFormatId &lt;= 22) ||
                              (cf.NumberFormatId &gt;= 165 &amp;&amp; cf.NumberFormatId &lt;= 180) || 
                                  cf.NumberFormatId == 278 || cf.NumberFormatId == 185 || cf.NumberFormatId == 196 || 
                                  cf.NumberFormatId == 217 || cf.NumberFormatId == 326) // Dates
                          {
  
                              try
                              {
  
                                  DateTime dt;
                                  dt = DateTime.FromOADate(Convert.ToDouble(c.CellValue.Text));
  
  ...CODE CONTINUES
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>In my updated post I forgot to post the value I found in the style.xml file:</p>
  
  <pre><code>&lt;numFmt numFmtId="323" formatCode="mmm/yy;@"/&gt;
  </code></pre>
  
  <p>So with this my question would be how do I get the formatCode and parse it to determine if it is a date?</p>
  
  <p>Below is the output from the immediate debug window of the numberformat 323</p>
  
  <pre><code>{DocumentFormat.OpenXml.Spreadsheet.CellFormat}
      base {DocumentFormat.OpenXml.OpenXmlCompositeElement}: {DocumentFormat.OpenXml.Spreadsheet.CellFormat}
      Alignment: {DocumentFormat.OpenXml.Spreadsheet.Alignment}
      ApplyAlignment: "1"
      ApplyBorder: "1"
      ApplyFill: "1"
      ApplyFont: "1"
      ApplyNumberFormat: "1"
      ApplyProtection: "1"
      BorderId: "64"
      ExtensionList: null
      FillId: "0"
      FontId: "83"
      FormatId: "37992"
      LocalName: "xf"
      NumberFormatId: "323"
      PivotButton: null
      Protection: {DocumentFormat.OpenXml.Spreadsheet.Protection}
      QuotePrefix: "1"
  </code></pre>