CODEWITHSUNDEEP
c#asp.net.netexcelepplus
Tonto
Tonto

Reputation: 3102

EPPlus number format

I have an Excel sheet generated with Epplus, I am experiencing some pain points and I wish to be directed by someone who have solved a similar challenge.

I need to apply number formatting to a double value and I want to present it in Excel like this.

Here is my code

ws.Cells[row, col].Style.Numberformat.Format = "##0.0";

The final Excel file always append E+0 to the end of this format and therefore presents the final values like this instead.

When I check in the format cells of the generated Excel sheet, I see that my format appears as ##0.0E+2 instead of ##0.0 that I applied.

What may be wrong?

Upvotes: 96

Views: 134010

Answers (5)

Rafael Parenza
Rafael Parenza

Reputation: 122

I solved it as follows, so I just load the model and change as per my model if it is int ordatetime

var li = typeof(Model)
              .GetProperties()
              .ToArray();


 using (var package = new ExcelPackage(stream))
            {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");

var i = 0;
                foreach (var c in li)
                {
                    i++;
                    if(c.PropertyType.Name == typeof(DateTime).Name || c.PropertyType.Name == typeof(DateTime?).Name)
                        workSheet.Column(i).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; ;

                    if (c.PropertyType.Name == typeof(int).Name || c.PropertyType.Name == typeof(int?).Name)
                        workSheet.Column(i).Style.Numberformat.Format = "0";                    
                }

}

Upvotes: 1

Atiq Baqi
Atiq Baqi

Reputation: 662

And if you want to format a specific column like column "B" to number format you can do it this way-

using (var package = new ExcelPackage())
{
  var worksheet = package.Workbook.Worksheets.Add("SHEET1");
  worksheet.Cells["A1"].LoadFromDataTable(dataTable, PrintHeaders: true);
  for (var col = 1; col < dataTable.Columns.Count + 1; col++)
  {
    if (col == 2)//col number 2 is equivalent to column B
    {
      worksheet.Column(col).Style.Numberformat.Format = "#";//apply the number formatting you need
    }
    worksheet.Column(col).AutoFit();
  }
  return File(package.GetAsByteArray(), XlsxContentType, "report.xlsx");//downloads file
}

Upvotes: 0

Matt Kemp
Matt Kemp

Reputation: 2920

Another addition to the accepted answer: you can use nullable values and the formatting all looks good BUT it ends up being a string in Excel and you can't SUM, AVG etc.

So make sure you use the actual Value of the nullable.

Upvotes: 2

Afshin Razaghi
Afshin Razaghi

Reputation: 450

Addition to Accepted Answer, because value Accept Object you must pass Number to Value For Example if your input is in string :

var input = "5";    
ws.Cells["A1:A25"].Value = double.Parse(input);

Upvotes: 4

VDWWD
VDWWD

Reputation: 35564

Here are some number format options for EPPlus:

//integer (not really needed unless you need to round numbers, Excel will use default cell properties)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0";

//integer without displaying the number 0 in the cell
ws.Cells["A1:A25"].Style.Numberformat.Format = "#";

//number with 1 decimal place
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.0";

//number with 2 decimal places
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.00";

//number with 2 decimal places and thousand separator
ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";

//number with 2 decimal places and thousand separator and money symbol
ws.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";

//percentage (1 = 100%, 0.01 = 1%)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0%";

//accounting number format
ws.Cells["A1:A25"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";

Don't change the decimal and thousand separators to your own localization. Excel will do that for you.

By request some DateTime formatting options.

//default DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

//custom DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";

Upvotes: 225

Related Questions