Search code examples
epplus

Reading long sequence of digit as string

I'm using EPPlus to read a customer database. From time to time in a text cell I'm reading as string, the customer wrote a long sequence of digit My code read it as exponential format.

Is there a way to force the reading as string?

Here's the code snippet I'm using

using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(xlsFile))) {
var myWorksheet = xlPackage.Workbook.Worksheets.First();

    while (!string.IsNullOrEmpty(myWorksheet.Cells[rowNum, 1].GetValue<string>())) 
    {
        var cell = myWorksheet.Cells[rowNum, 20];
        var idDoc =  cell.GetValue<string>(),

        // do something with idDoc

        rowNum += 1;
    }
}

when the cell contains, let's say 1790002099190700, idDoc is "1,7900020991907E+15"

Solution

  • What you are seeing make sense as excel will store the value as a double like any other numeric value.

    It looks like you are exporting from a database to excel. I would assume that when exported there is no formatting set in Excel in which case using the .Text value of the cell object simply fall back to "General" in excel.

    Say you have this:

    enter image description here

    So, what you end up with is this:

    [TestMethod]
public void Cell_Digits_As_String()
{
    //https://stackoverflow.com/questions/58058900/reading-long-sequence-of-digit-as-string

    var fi = new FileInfo(@"c:\temp\Cell_Digits_As_String.xlsx");

    using (var package = new ExcelPackage(fi))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.First();
        var valCell = worksheet.Cells[1, 1];

        var valText = valCell.Text; //Applies "General" unless otherwise specified
        var valValue = valCell.Value; //As a BOXED double
        var valString = valCell.GetValue<string>(); //Falls back to ChangeType

        Console.WriteLine($"Value as text: {valText} ({valText.GetType()})");
        Console.WriteLine($"      Same as: {((double)valValue).ToString("0.#####", new CultureInfo("es-US"))}");

        Console.WriteLine($"Value as value: {valValue} ({valValue.GetType()})");
        Console.WriteLine($"       Same as: {valValue.ToString()}");

        Console.WriteLine($"Value as string: {valString} ({valString.GetType()})");
        Console.WriteLine($"        Same as: {Convert.ChangeType(valValue, typeof(double)).ToString()}");
    }
}

    Which shows this in the out:

    Value as text: 1790002099190700 (System.String)
      Same as: 1790002099190700
Value as value: 1.7900020991907E+15 (System.Double)
       Same as: 1.7900020991907E+15
Value as string: 1.7900020991907E+15 (System.String)
        Same as: 1.7900020991907E+15

    So, it would seem that using .Text is the most convenient to get what you want. But if you are concerned about the formatting being altered in any way or just want to be absolutely sure, just do:

    try
{           
    ((double)valCell.Value).ToString("0.#");
}
catch (Exception ex)
{
    //Handle it...
}