Set currency format for Excel cell that is created with ExcelPackage

misho picture misho · Sep 2, 2010 · Viewed 31.4k times · Source

How can I set the currency format for an Excel cell that is created with ExcelPackage?

worksheet.Cell(i, 7).Value = item.Price.ToString();

Answer

Dustin Andrews picture Dustin Andrews · Jun 15, 2011

ExcelPackage will read out the NumberFormats on cells. So you can just make a sample in Excel then read out the cells and see what the format is for things you want to do.

Here is an example of three different ways to format currencies. Note the last one "hardcodes" the $ character, which may not be a best practice.

using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo("testReport.xlsx")))
{
  ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add("worksheet");

  ws.Cells[1, 1, 3, 1].Value = 0d;
  ws.Cells[1, 2, 3, 2].Value = -14.957d;
  ws.Cells[1, 3, 3, 3].Value = 5000000.00d;
  ws.Cells[1, 4, 3, 4].Value = -50000000000.00d;
  ws.Cells[1, 1, 1, 4].Style.Numberformat.Format = "#,##0.00;(#,##0.00)";
  ws.Cells[2, 1, 2, 4].Style.Numberformat.Format = "#,##0.00;-#,##0.00";
  ws.Cells[3, 1, 3, 4].Style.Numberformat.Format = "\"$\"#,##0.00;[Red]\"$\"#,##0.00";
  ws.Cells[1, 1, 3, 4].AutoFitColumns();

  excelPackage.Save();
}