Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set currency format for Excel cell that is created with ExcelPackage

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

worksheet.Cell(i, 7).Value = item.Price.ToString();
like image 567
misho Avatar asked Sep 02 '10 09:09

misho


People also ask

How do I change the custom Currency in Excel?

On the Home tab, click the Dialog Box Launcher next to Number. Tip: You can also press Ctrl+1 to open the Format Cells dialog box. In the Format Cells dialog box, in the Category list, click Currency or Accounting. In the Symbol box, click the currency symbol that you want.


2 Answers

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();
}
like image 110
Dustin Andrews Avatar answered Sep 16 '22 13:09

Dustin Andrews


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

like image 28
mtntrailrunner Avatar answered Sep 20 '22 13:09

mtntrailrunner