16

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

worksheet.Cell(i, 7).Value = item.Price.ToString();
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
misho
  • 1,195
  • 6
  • 16
  • 29

2 Answers2

25

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();
}
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Dustin Andrews
  • 350
  • 3
  • 7
  • 2
    There is a Cells property in the worksheet, please check your code again. – Exzile Jul 16 '14 at 19:41
  • 1
    Just for anyone else, these formats will make sure the format dropdown in excel reads "Currency", "Number", etc. instead of "Custom" – jamesSampica Oct 28 '16 at 16:26
10
ExcelPackage package = new ExcelPackage();
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells[1, 1].Value = 1.50;
worksheet.Cells[1, 1].Style.Numberformat.Format = "$0.00";

This will create $1.50 as a number in your speadsheet

mtntrailrunner
  • 761
  • 6
  • 11