Decimal values in Excel cells are being read as Exponential values

Aspose.Cells Product Family
1

We use the Aspose .Net library to generate Excel reports. I’m reusing an old thread title found here: Decimal values are being read as Exponential values. Since it has been a while, I’m wondering if there is any new way to tackle this issue.

Secondly, I’m getting exceptions for casting the cell value to decimal even I already have the same check on the cell type to see if it’s CellValueType.IsNumeric or not. Should I always have to cast the value to double then decimal? Ideally I would prefer casting directly to decimal to avoid any precision loss. (Actually when I took a close at the results, all the exponent fractional numbers became 0 after the castings: i.e., 8.2324E-6 ==> 0)

Thank you.

2

@sanshi
By creating sample file for testing on the latest version v24.4, we can obtain the correct values. Please refer to the attachment. sample.zip (5.7 KB)
The sample code as follows:

Console.WriteLine("version: " + CellsHelper.GetVersion());
Workbook workbook = new Aspose.Cells.Workbook(filePath + "sample.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
Console.WriteLine(cells["A1"].Value);

The output result:

version: 24.4
8.2324E-06

Would you like to provide your sample file and executable Console project? We will check it soon.

3

Actually, what I want to do is have the value 0.0000082324 in your sample.xlsx file read into a decimal variable so that it remains as 0.0000082324 and not 0.00.

Sorry my company won’t let me share anything.

Thank you for the reply and the sample.

4

@sanshi,

Please note, this is Microsoft Excel’s behavior as it stores and displays large decimal numbers/values in scientific notations. Therefore, this is not an issue with Aspose.Cells and there is nothing to be done on Aspose.Cells’ part. If you need to read the complete original value/number, you have to set the number format and include the desired amount of decimals, or set a custom number format (with decimal places as per your original number) using Aspose.Cells.
Please see the sample code below that works according to your needs, so you may amend your code accordingly for your decimal values.
e.g.
Sample code:

Console.WriteLine("version: " + CellsHelper.GetVersion());
Workbook workbook = new Aspose.Cells.Workbook("g:\\test2\\sample.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
Cell cell = cells["A1"];

Style style = cell.GetStyle();
style.SetCustom("0.0000000000", false);
style.ShrinkToFit = true;

cell.SetStyle(style);

if (cell.IsNumericValue)
{
    int decimalDigits = BitConverter.GetBytes(decimal.GetBits((decimal)cell.DoubleValue)[3])[2];
    var format = "F" + decimalDigits;
    Console.WriteLine(cell.DoubleValue.ToString(format));//0.0000082324 -Ok
}

Console.WriteLine(cell.StringValue);//0.0000082324 -Ok
Console.WriteLine(cell.GetStringValue(CellValueFormatStrategy.DisplayString));//0.0000082324 - Ok

Hope, this helps a bit.

5

Sorry for the late reply. Cell properties IsNumericValue and DoubleValue worked perfectly for my case. Thank you so much.

6

@sanshi,

Good to know that your issue has been sorted out by the suggested code segment that works for your needs. Please feel free to contact us at any time if you have further queries or comments. We will be happy to assist you soon.