How to avoid exponential format while reading excel number?

Aspose.Cells Product Family
1

How to avoid exponential format while reading excel number?
Is there any specific property to modify to keep the number as it is?

2

@emeghana can you please attach your source file.

4

Sample.zip (8.2 KB)
i have given sample file. please check and let me know on priority

1 Like
5

@emeghana,

If the cell value is numeric( IsNumericValue), you may get the double value( DoubleValue) or int value( IntValue), then you can format the got numeric value as any format you expect.

6

Its a numeric value but still reading it as “E” And i dont want to format each value here as my excel is very huge and i have many number of columns in the template.

7

@emeghana is not clear to me if want to read the complete number in MS Excel app or in a custom application using Aspose.Cells:

  1. Reading complete number in an app using Aspose.Cells. To achieve this you need to format the double value that you obtain:
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells["B3"];
if (cell.IsNumericValue)
{
    int decimalDigits = BitConverter.GetBytes(decimal.GetBits((decimal)cell.DoubleValue)[3])[2];
    var format = "F" + decimalDigits;
    Console.WriteLine(cell.DoubleValue.ToString(format));
}
  1. If you want to read the complete number in MS Excel app you will need to set the number format as Number and include the amount of decimals that you require in the Excel app or set a Custom format using the Aspose.Cells API:
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells["B3"];

Style s = cell.GetStyle();
s.SetCustom("0.0000000000000000000", false);
s.ShrinkToFit = true;

cell.SetStyle(s);

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

Or in MS Excel app:

8

i appreciate your effort in giving solution. But my problem is little different here,

Observer below code, this is how I read the excel sheet into datatable. In the Excel, i have data in almost 165 column (i.e till FK) and 120 rows. it difficult to format each numeric cell as this excel holds 80% of data which is numeric & also very huge. Is there any way to handle while exporting data into datatable.

var exportTableOptions = new ExportTableOptions
{
ExportAsString = false,
ExportColumnName = true,
CheckMixedValueType = true

        };

DataTable dt = worksheet.Cells.ExportDataTable(firstRowIndex, firstColumnIndex, totalRowCount, totalColumnCount, exportTableOptions);

9

@emeghana if you want to have the full values in the resultant DataTable you must to know that C# by default convert to string Double and Decimal using scientific notation, this is a language feature and cannot do do anything regarding this. So, you must save all values as string (ExportAsString = true), but this is not enough, because the values in the cells will be the same that the values that you can see in MS Excel, at this point you have 2 options:

  1. Modify the cells using approach that I posted before.
  2. Or modify the data in the DataTable (this option will be faster) using the following code:
//Get worksheet
Worksheet worksheet = workbook.Worksheets[0];

var exportTableOptions = new ExportTableOptions
{
    ExportAsString = true,
    ExportColumnName = true,
    CheckMixedValueType = true,
    FormatStrategy = CellValueFormatStrategy.DisplayString
};
DataTable dt = worksheet.Cells.ExportDataTable(firstRowIndx, firstColIndx, totalRows, totalCols, exportTableOptions);
foreach(DataRow row in dt.Rows)
{
    foreach(DataColumn col in dt.Columns)
    {
        var colName = col.ColumnName;
        object? value = row[colName];
        if (value != null)
        {
            var strVal = value.ToString();
            if (double.TryParse(strVal, out var decimalValue))
            {
                int decimalDigits = BitConverter.GetBytes(decimal.GetBits((decimal)decimalValue)[3])[2];
                var format = "F" + decimalDigits;
                var transformedVal = decimalValue.ToString(format);
                row[colName] = transformedVal;
            }
        }
    }
}

1 Like
10

Thank you for the solution. its working.

i have observed that there is a default option in HTML to handle it through load options
[HTMLLoadOptions.KeepPrecision]

and in java

TxtLoadOptions options = new TxtLoadOptions(LoadFormat.Excel);
options.setKeepPrecision(true);

Like this, dont we have any load options to deal in .Net C#?

11

@emeghana,

No, there is no such option available when loading Excel files (e.g., XLS, XLSX, etc.). You may adopt suggested techniques mentioned above when exporting data with underlying values to datatable.

12

Thank You @eduardo.canal @amjad.sahi

13

@emeghana,

You are welcome.

14

@emeghana

Actually, the data exported for Numbers column with your code are all double type.
Please check the srceenshot: screenshot.png (119.7 KB)

Also, here is a similar case for your reference: