Excel VSTO cell precision-CodePudding

When I read very small values from Excel sheet these are shown as scientific precision. For example, the -0.00002 is always read as -2E05 using Cells().Values function. Below are the conversion lines I have used, without any success. How to get the actual value instead of the scientific format?

  var canConvert = decimal.TryParse(ws.Cells[1, 1].Value.ToString(), out _); // result in false
  var cellString = ws.Cells[1, 1].Value.ToString("R"); // -2E05
  var cellStrin2g = ws.Cells[1, 2].Value.ToString(); // -2E05

CodePudding user response：

It seems you need to set up the required NumberFormat first:

ws.Range("A17").NumberFormat = "General"

The format code is the same string as the Format Codes option in the Format Cells dialog box.

CodePudding user response：

After several trial I have came up to the following solution, which works well in my model.

double number = 0;
var canConvert = double.TryParse(ws.Cells[row, column].Value.ToString(), out number3);
if (canConvert)
  string doubleAsString = cell.ToString("F99").TrimEnd('0');

Bear in mind that the above scenario works well upto 99 following digits. Feel free to extend it to your needs.