Write to Excel without scientifc notation using EPPlus

Question

I have a very straightforward question. I have a C# application which does some data processing and then outputs to an excel file using EPPlus. The problem is that some of my numbers are very long, for example, a data value could be something like 20150602125320, which turns into 2.01506E+13 in the excel sheet. I am trying to avoid this. Converting the value to string works but I need them as numbers for further operations within excel. Anyone know any way I can keep the data in its original form without the scientific notation? Thank you!

Answer 1

You have to set the format string to match what you are looking for:

[TestMethod]
public void Big_Number_Format()
{
    //http://stackoverflow.com/questions/31124487/write-to-excel-without-scientifc-notation-using-epplus
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package2 = new ExcelPackage(existingFile))
    {
        var ws = package2.Workbook.Worksheets.Add("Sheet1");
        ws.Cells[1, 1].Value = 20150602125320;
        ws.Cells[1, 2].Value = 20150602125320;

        ws.Cells[1, 1].Style.Numberformat.Format = "0";
        ws.Cells[1, 2].Style.Numberformat.Format = "0.00";
        package2.Save();
    }
}

If you want to see the a general list of the "built in" formats you see in the ribbon in excel you can see them here but you cant access them direct in you app since they are marked as internal:

https://github.com/JanKallman/EPPlus/blob/master/EPPlus/Style/XmlAccess/ExcelNumberFormatXml.cs#L62

/// <summary>
/// Id for number format
/// 
/// Build in ID's
/// 
/// 0   General 
/// 1   0 
/// 2   0.00 
/// 3   #,##0 
/// 4   #,##0.00 
/// 9   0% 
/// 10  0.00% 
/// 11  0.00E+00 
/// 12  # ?/? 
/// 13  # ??/?? 
/// 14  mm-dd-yy 
/// 15  d-mmm-yy 
/// 16  d-mmm 
/// 17  mmm-yy 
/// 18  h:mm AM/PM 
/// 19  h:mm:ss AM/PM 
/// 20  h:mm 
/// 21  h:mm:ss 
/// 22  m/d/yy h:mm 
/// 37  #,##0 ;(#,##0) 
/// 38  #,##0 ;[Red](#,##0) 
/// 39  #,##0.00;(#,##0.00) 
/// 40  #,##0.00;[Red](#,##0.00) 
/// 45  mm:ss 
/// 46  [h]:mm:ss 
/// 47  mmss.0 
/// 48  ##0.0E+0 
/// 49  @
/// </summary>