Cell.Text now returns doubles in scientific notation in v7.5.3, but not v7.0.4

[DannyBoyk]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.5.3

Spreadsheet application

Excel

Description

We have an existing code base we've been using to parse Excel spreadsheets first written against v7.0.4. Today, we updated to v7.5.3 to get a fix for a zip reading bug. In a section where we are reading doubles from a worksheet, we are suddenly seeing cell.Text return a string in scientific notation rather than the original double value. In v7.0.4, we were receiving the original double value. The double in question is actually just a whole number like 123456789012345, but the text value we now get back is 1.234567890E+14. So, we don't even get the full number of digits.

Is there a setting we can disable to turn off this behavior when loading a workbook? We're not sure what change between v7.0.4 to v7.5.3 to cause this new behavior. We need to be able to read the original double value as-is.

~Dan

[JanKallman]

The Text property returns the formatted value for the cell. What is the number format for the cell?

[DannyBoyk]

The format of the column is just "General". In v7.0.4, that just gave us the number as-is, but v7.5.3 is converting to scientific notation.

Is there, by chance, a way to global disable that?

[JanKallman]

Yes, we updated the "General" format to return what Excel returns as the formatted number...

...or do you get a different display?

[DannyBoyk]

That is indeed what gets displayed. The behavior just differs from what we coded to in hundreds of places and from the behavior of v7.0.4. If it's possible for us to just turn this off at the parsing level, that would be the least painful thing for us.

[JanKallman]

This was a bug fix, to make sure the Text property returns the same output as Excel does for the General format, so we have no setting to go back to the old incorrect behaviour.
Can you programmatically set the correct number format for these cells? (for example "0", to make the cell return the expected text output?).
That is:
worksheet.Cells["A1"].Style.NumberFormat.Format = "0";

If this does not work for you, you can override the default behaviour for the Text property, by setting a custom NumberFormatToTextHandler .
Example:

  [TestMethod]
  public void HandleNumberFormatToText()
  {
      using (var package = new ExcelPackage())
      {
          package.Workbook.NumberFormatToTextHandler = HandleGeneral;
          var ws = package.Workbook.Worksheets.Add("Sheet1");
          ws.Cells["A1"].Value = 12345678901234D;

          Assert.AreEqual("12345678901234", ws.Cells["A1"].Text);
      }
  }
  public string HandleGeneral(NumberFormatToTextArgs args)
  {
      if(args.NumberFormat.Format.Equals("general", StringComparison.InvariantCultureIgnoreCase))
      {
          if(args.Value is double d) //Check if the Value is double. If you have another data types, add a check for that type here.
          {
              return d.ToString("0"); //Return the desired format.
          }
      }
      return args.Text; //Otherwise return the default Text here;
  }