ExportDataTable scientific notation

Aspose.Cells Product Family
1

Hi,

I have a Worksheet containing a value 0.000001, when this is exported using Aspose the output is the scientific notation 1E-06 and not the decimal 0.000001. Any other decimal values are exported fine, e.g. 0.052 etc
How can I rectify this please?
At the moment I am using:

ExportDataTable(0, 0, 2, 1, true);

Thanks

2

@Ian_Baldwin,

Could you please zip and attach your Excel file here. We will check your issue soon.

3

Hi Amjad,

Yes no problem. I have attached the simplest form of the file.

The code I am using is: var dataTable = worksheet.Cells.ExportDataTable(0, 0, 2, 1, true);

Aspose.zip (6.3 KB)

4

@Ian_Baldwin
There is no issue on our end. The exported value is double 0.000001

var dataTable = workbook.Worksheets[0].Cells.ExportDataTable(0, 0, 2, 1, true);
double t = (double)dataTable.Rows[0][0];
double x = 0.000001;
Console.WriteLine(t == x);
5

I am using v20.10.0. of Aspose.Cells Try this code with the file I supplied:

                        var wb = new Workbook("C:\\Aspose.xlsx");
                        var ws = wb.Worksheets[0];
                        var dt = ws.Cells.ExportDataTable(0, 0, 2, 1, true);

and view the results (attached)

image.png (121.1 KB)

6

Consider this larger file (with the code from above).

Aspose.zip (6.8 KB)

image.png (111.1 KB)

7

@Ian_Baldwin,

In the exported data table, the value is a double value. Our code is just to show that the exported value is the correct one(double value). “0.000001” and “1.0E-6” are just different string expressions for the same double value in visual tools such as your IDE. And different tools may show the double value differently. Do you think there is any issue for the double value we exported to the datatable?

Maybe what you want is the formatted result of the cell in ms excel, if so, please use another method: ExportDataTableAsString, ExportDataTableAsString or ExportDataTable

8

I realise 1E-06 is equivalent to 0.000001

The issue I have is that I am using the results of the ExportDataTable to feed a TVP in SQL server and it doesn’t like the format 1E-06 - it gives a ‘error converting varchar to numeric’

Thanks

9

@Ian_Baldwin,

Even with your newly provided template file, we do not find the mixed type for the exported data. All exported are double values:

            DataTable dt = cells.ExportDataTable(0, 0, 44, 1, true);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var v = dt.Rows[i][0];
                Console.WriteLine(v.GetType().Name + ": " + v);
            }

the output we got:
Double: 0.01
Double: 0.01
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.05
Double: 0.052
Double: 0.052
Double: 0.05
Double: 0.1
Double: 1E-06
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.052
Double: 0.052
Double: 0.052
Double: 0.052
Double: 0.052
Double: 0.052
Double: 0.052
Double: 0.0251
Double: 0.052
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.052
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.0251
Double: 0.05
Double: 0.01
Double: 0.0251
Double: 0.052

1 Like