title: How to format currency in ClosedXML as numeric
body: <p>we're using ClosedXML to convert datatable objects into Excel spreadsheets for presentation to the user. The DataTable object is build simply by assigning all db values (from NHibernate) to strings and then formating them like below:</p>
  
  <pre><code>  //formatting
  EstimatedCost = Currency.SafeToString(Currency.Create(n.EstimatedCost)),
  </code></pre>
  
  <p>We then set the column type to the property type, i.e. String in all cases.</p>
  
  <p>What happens in the output Excel sheet as that the column is set for currency but has the number as text warning, then it won't sort correctly.</p>
  
  <p>My problem is that since we build all the data into a DataTable, I don't have a chance to decorate the ClosedXML columns correctly. Is there a quick way to do this that I am not thinking of?</p>
  
  <pre><code>public const string ExcelDataType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
  
  public static MemoryStream GenerateExcelFileFromData(IEnumerable&lt;KeyValuePair&lt;string, DataTable&gt;&gt; tabs, int colWidth = 100)
  {
    var workbook = new XLWorkbook { ColumnWidth = colWidth };
    foreach (var enumerable in tabs)
    {
      workbook.Worksheets.Add(enumerable.Value, enumerable.Key);
    }
  </code></pre>
  
  <p>...</p>
  
  <pre><code> public static DataTable ConvertToDataTable&lt;T&gt;(IEnumerable&lt;T&gt; varlist, List&lt;string&gt; excludedColumns, bool titleizeColumn = false)
   {
     var dtReturn = new DataTable();
  
     // column names 
     PropertyInfo[] oProps = null;
  
     if (varlist == null) return dtReturn;
  
      foreach (T rec in varlist)
      {
       // Use reflection to get property names, to create table, Only first time, others will follow 
          if (oProps == null)
          {
             oProps = rec.GetType().GetProperties();
                foreach (PropertyInfo pi in oProps)
                 {
                      if (excludedColumns.Contains(pi.Name))
                      {
                          continue;
                      }
                      var colType = pi.PropertyType;
                      dtReturn.Columns.Add(new DataColumn(GetColumnName(pi, titleizeColumn), colType));
                  }
            }
  
          DataRow dr = dtReturn.NewRow();
  
          foreach (var pi in oProps.Where(pi =&gt; !excludedColumns.Contains(pi.Name)))
            {
               try
                  {
                      dr[GetColumnName(pi, titleizeColumn)] = pi.GetValue(rec, null) ?? DBNull.Value;
                  }
                  catch (ArgumentException)
                  {
                      dr[GetColumnName(pi, titleizeColumn)] = DBNull.Value;
                  }
              }
              dtReturn.Rows.Add(dr);
          }
          return dtReturn;
  </code></pre>