How to format currency in ClosedXML as numeric

Question

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:

  //formatting
EstimatedCost = Currency.SafeToString(Currency.Create(n.EstimatedCost)),

We then set the column type to the property type, i.e. String in all cases.

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.

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?

public const string ExcelDataType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";

public static MemoryStream GenerateExcelFileFromData(IEnumerable<KeyValuePair<string, DataTable>> tabs, int colWidth = 100)
{
  var workbook = new XLWorkbook { ColumnWidth = colWidth };
  foreach (var enumerable in tabs)
  {
    workbook.Worksheets.Add(enumerable.Value, enumerable.Key);
  }

...

 public static DataTable ConvertToDataTable<T>(IEnumerable<T> varlist, List<string> 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 => !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;

Answer 1

You can format your currency values this way:

worksheet.Cell(rowIndex, columnIndex).Style.NumberFormat.Format = "$0.00";
worksheet.Cell(rowIndex, columnIndex).DataType = XLCellValues.Number; // Use XLDataType.Number in 2018 and after