Search code examples
c#excelasp.net-coreepplus

How can I force Excel to display scientific notation via EPPlus?

Is there any way to control whether a number displays in scientific notation using the EPPlus library to generate an Excel file in C#? 

worksheet.Cells[row, col].Value = someValue; //example value: 0.0000152691
worksheet.Cells[row, col].StyleName = RegularCenterAlign;
worksheet.Cells[row, col].Style.Numberformat.Format = "WHAT DO I PUT HERE???";

I tried "0.00e0" for the format and that causes excel to throw an error:

enter image description here

I didn't see it in the documentation but if you look in Excel at the formatting options, scientific notation is available, so you'd think EPPlus has a string code we could use for this.

What seems to be happening now is Excel is making its own determination about whether to format a given number in scientific notation. The same code inserted all of the numbers in the following screenshot. No format was specified but you can see that last one did not use scientific notation.

enter image description here

Solution

  • Use:

    ws.Cells["A1"].Style.Numberformat.Format = "0E+00";

    You can add the number of decimals with something like:

    ws.Cells["A1"].Style.Numberformat.Format = "0.0000E+00";

    Don't forget that your value should be numeric. This value will be formatted:

    ws.Cells["A1"].Value = 0.0000152691;

    this one won't:

    ws.Cells["A1"].Value = "0.0000152691";