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

Answer 2

You can get all of the columns you know are currency and set the NumberFormat.Format. Below is a sample of how it might be done. The correct format to use for US dollar currency is "[$$-en-US]#,##0.00_);[Red]([$$-en-US]#,##0.00)". When this format is used, if you open the spreadsheet in Excel and go to Cell Format, you will see it set as Currency.

    // Assume all columns that have "price" in the heading are currency types and format as currency.
    var priceColumns = table.Columns(c => c.FirstCell().Value.ToString().Contains("price", StringComparison.InvariantCultureIgnoreCase));
    foreach (var priceColumn in priceColumns)
    {
        try
        {
            // Get all the cells in this column, except for the header row
            var cells = priceColumn.Cells(2, rowCount);
            cells.Style.NumberFormat.Format = @"[$$-en-US]#,##0.00_);[Red]([$$-en-US]#,##0.00)";
        }
        catch { } // Exception here means not all of the cells have a numeric value.
    }

Answer 3

One easy way to get the format right is to go to excel itself, choose the format you want your cell to be, then right click in the cell and choose Format Cells, after that you can copy the Format Code from your chosen format and you are good to go.

// I had to escape double quotes though
string myFormat = "\"$\"#,##0;[RED]-\"$\"#,##0";
worksheet.Cell("A1").Style.NumberFormat.Format = myFormat;

Answer 4

var currency = "$";
worksheet.Cell(rowIndex, columnIndex)
.SetDataType(XLDataType.Number)
.Style.NumberFormat.SetFormat($"{currency}0.00");

Answer 5

ws.Cell(ro, co).Style.NumberFormat.Format = "[$$-en-US] #,##0.00";
ws.Cell(ro, co).DataType = XLDataType.Number;