Load scientific notation value as numeric in Datatable

Hi Team,

I am using Aspose.Cells for .Net, i am reading the data from excel file using the following:

DataTable wsData = workSheet.Cells.ExportDataTableAsString(0, 0, dataCells.MaxRow + 1, dataCells.MaxColumn + 1, true);

If a cell has a value as 1.23457E+11, in the Datatable i.e wsData the value is read as string instead of its numeric value.

Is there a way to read the numeric value of the scientific value while loading the data in data table in .net.

Thanks.

@siddhanntarora1992,

Thanks for the details.

Since you are using ExportDataTableAsString which surely will take all the values as string values. If you need such numbers should be taken as numeric values, you may try using ExportDataTable method instead.

@Amjad_Sahi If i am not wrong by using ExportDataTable will need to define all columns with data type.

While generating a workbook, we have an option

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.Auto);
** opts.ConvertNumericData = true;**

I am looking for a workaround like this for ExportDatatableAsString, does there exist a workaround similar to this?

Thanks.

@siddhanntarora1992,

It looks like you are using older version of the product. Well, you do not need to define DataTable columns with its type. The ExportDataTable will take care of it (same as ExportDataTableAsString) and will auto generate column types and then fills data into it. And, there is no workaround when using ExportDatatableAsString.

@Amjad_Sahi

That works, but if there is a string value in the same column, aspose throws an exception, is there a way to avoid that?

Below is the data in my excel

1 2 3 4
100 2.5659E+12 200 200
100 200 200 200
100 200 200 200
100 Test 200 200

I am importing data in datatable using below code in C#

DataTable wsData = workSheet.Cells.ExportDataTable(0, 0, dataCells.MaxRow + 1, dataCells.MaxColumn + 1, true);

Since column 2 contains a text value with other numeric value aspose throws an exception.

I want to read the value as it is and not throw an exception

@siddhanntarora1992,

Yes, Aspose.Cells should throw the exception and rightly so for ExportDataTable method. The reason is when it evaluates the type for the values in the second column (i.e., 2.5659E+12, 200, 200, test), it specifies numeric type (as it evaluates based on the very first value (2.5659E+12) in the column) but it has “test” on the bottom which violates it. If there are mixed data/values in a column, a string data type will be the only option. So, it is better you should use ExportDataTableAsString method. Alternatively, define column types (with second column type as string) for the fields in your own DataTable and then use ExportDataTable() method to fill it out.

If i understand correctly, you are suggesting to create a data table with required columns for example

DataTable dt = new DataTable();
dt.columns.Add(1, typeof(string));
dt.columns.Add(2, typeof(string));
dt.columns.Add(3, typeof(string));
dt.columns.Add(4, typeof(string));

And then do exportdatatable(), but in that case too it wont be able to convert the text value and throw error, is that right?

If not please provide an example for the solution you have suggested.

Thanks.

@siddhanntarora1992,

The scientific value in that column would be taken as string value. I think you are not getting my point. How could you put numbers and string values into numeric data (type) filed in a table, this is not possible in any data source. For your information, if a column has mixed data (numbers, strings, etc.), then you have to use string data type for that column.

Also, see the example code for your reference:
e.g
Sample code:

 Workbook workbook = new Workbook("e:\\test2\\Book1.xlsx");

            Cells cells = workbook.Worksheets[0].Cells;
            
            DataTable dt = new DataTable();
            dt.Columns.Add("column1", typeof(double));
            dt.Columns.Add("column2", typeof(string));
            dt.Columns.Add("column3", typeof(double));
            dt.Columns.Add("column4", typeof(double));
            //.............
            ExportTableOptions options = new ExportTableOptions();
            options.SkipErrorValue = true;
            options.DataTable = dt;

            cells.ExportDataTable(0, 0, 100,4,options);

I got your point and had already implemented this way

DataTable dt = new DataTable();
dt.Columns.Add(“column1”, typeof(double));
dt.Columns.Add(“column2”, typeof(string));
dt.Columns.Add(“column3”, typeof(double));
dt.Columns.Add(“column4”, typeof(double));
dt = workSheet.Cells.ExportDataTable(0, 0, dataCells.MaxRow + 1, dataCells.MaxColumn + 1, true);

I hope this wouldnot cause any issue, this is different from the approach suggested by you

Since you are using some older version then you have to use your code segment to cope with it. My sample code will work only with latest version of the product where we introduced ExportTableOptions, so you have to refer to my sample code when using newer versions.

@Amjad_Sahi Your code also works for me.

Just one thing to add here, let say my excel has 5 columns but the custom datatable that i created has only 4 columns, using your code, it throws an exception for the missing columns.

Is it possible to export only those columns from excel that matches the column name specified in the local datatable?

Thanks.

@siddhanntarora1992,

In newer version, we have an attribute i.e., Indexes under ExportTableOptions class which can export your desired columns (based on indexed position) to fill the DataTable, see the following code segment for your reference:
e.g
Sample code:

.......
//Specify export table options - explains the usage of Indexes property
            ExportTableOptions opts = new ExportTableOptions();
            opts.ExportColumnName = true;
            
            opts.Indexes = new int[] { 5, 4, 1, 3, 2 };
....

If you want to persist with your older version, I am afraid, there is no such flexibility while exporting to DataTable.

it is out of scope/context for Aspose.Cells.

@Amjad_Sahi
The indexes are corresponding to the datatable or to the excel columns?

@siddhanntarora1992,

The indexes refer to the column indexes in Ms Excel spreadsheet.

Is there an easy way to find the column indexes with their names?

And does aspose then match index of the datatable and excel column to populate data?

@Amjad_Sahi
I am trying to use the below code
Cells cells = workbook.Worksheets[0].Cells;

        DataTable dt = new DataTable();
        dt.Columns.Add("column1", typeof(double));
        dt.Columns.Add("column2", typeof(string));
        dt.Columns.Add("column3", typeof(double));
        dt.Columns.Add("column4", typeof(double));
        //.............
        ExportTableOptions options = new ExportTableOptions();
        options.SkipErrorValue = true;
        options.DataTable = dt;

opts.Indexes = new int[] {1 , 2,3,4};
cells.ExportDataTable(0, 0, 100,4,options);

It says column4 already exists?

How are the indexes working here?

Thanks.

@Amjad_Sahi
Also, is there a way to skip error value and replace with a custom values instead of empty?

@siddhanntarora1992,

Please note, Indexes are zero based, so you got to make sure that relevant indexes are mentioned in the array for Excel spreadsheet. I tested the scenario/ case using the following sample code with a simple template file (attached in the archive), it works fine and I do not find any exception or issue. Also check the output file (attached in the archive) for your reference:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\Bk_loads1.xlsx");
            Cells cells = workbook.Worksheets[0].Cells;

            DataTable dt = new DataTable();
            dt.Columns.Add("column1", typeof(double));
            dt.Columns.Add("column2", typeof(string));
            dt.Columns.Add("column3", typeof(double));
            dt.Columns.Add("column4", typeof(double));

            //.............
            ExportTableOptions options = new ExportTableOptions();
            options.SkipErrorValue = true;
            options.DataTable = dt;
            options.Indexes = new int[] {0,1,2,3};

            cells.ExportDataTable(0, 0, 100, 4, options);

            Worksheet worksheet1 = workbook.Worksheets[1];
            worksheet1.Cells.ImportData(dt, 0, 0, new ImportTableOptions() {});

            workbook.Save("e:\\test2\\out1.xlsx"); 

Please open the template file first and then open the output file for your complete reference. If you still find any issue with latest version, kindly do create a runnable sample code (same as above) to show the issue. Also attach sample file(s)

You have to write your own code by yourselves as it is out of context for Aspose.Cells APIs.

Error value can be skipped to a certain extent (here you may also try using CheckMixedValueType to true if a column has both numeric and string values to escape from error) but It is not supported to replace with any custom oriented values.
files1.zip (30.0 KB)

Hi Amjad,

Thanks for you responses.

There is one more scenario wanted to check, suppose i am trying to use this code.

Workbook workbook = new Workbook(“e:\test2\Book1.xlsx”);

        Cells cells = workbook.Worksheets[0].Cells;
        
        DataTable dt = new DataTable();
        dt.Columns.Add("column1", typeof(double));
        dt.Columns.Add("column2", typeof(string));
        dt.Columns.Add("column3", typeof(double));
        dt.Columns.Add("column4", typeof(double));
        //.............
        ExportTableOptions options = new ExportTableOptions();
        options.SkipErrorValue = true;
        options.DataTable = dt;

        cells.ExportDataTable(0, 0, 100,4,options);

If you see the attached file, in column2 i have scientific values as well as date values.

After using the code, for both scientific and date values i am getting an integer values.

Is that expected and is there a way to handle this scenario?

Thanks.