ExportDataTable create float column instead of int

Aspose.Cells Product Family
1

Hi,

I’m trying to import excel data to database with ExportDataTable function. One of the sheet’s columns contains only integers (kind of IDs), but after calling the function above, in the filled dataset appears that this column’s type is set as float instead of int.
I also set CheckMixedValueType = true in ExportTableOption.
Do I something wrong, or is the default numeric type float when exporting into dataset?

Please let me know if I misunderstand something.

2

@korosi,

Thanks for some details.

Well, it might be default behavior. I think you may easily cope with it defining your custom (blank) DataTable with columns of your desired data types which you will fill using ExportDataTable, see the sample code for your reference:
e.g
Sample code:

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

    Workbook wb = new Workbook("e:\\test2\\Book1.xlsx");
    Worksheet worksheet = wb.Worksheets[0];
    ExportTableOptions options = new ExportTableOptions();
    options.SkipErrorValue = true;
    options.DataTable = dt;

    worksheet.Cells.ExportDataTable(0, 0, 100,3,options);

Hope, this helps a bit.

3

OK, but my solution is meant to be a general data export to database, so without loading at least the first row of each sheet, I don’t know which column has to be int/float/datatime etc.

Any other workaround?

4

@korosi,

There may not be any other way around. Could you provide us template file and sample code (runnable), we will check and may log a ticket for investigation if we could figure it out or not.

5

Please find attached the demo code and demo excel.

Sorry, I probably misled you: the ExportDataTable makes double from int, then I use (DbType)typeConverter.ConvertFrom(fromType.Name) C# function, which makes float from double…
But my problem still exists: I need integer, not anything else.

aspose_demo.zip (20.7 KB)

6

@korosi,

Thanks for the sample project.

After initial evaluation, I guess this might be the behavior of MS Excel or .NET when storing numeric values and nothing to do with Aspose.Cells APIs. Anyways, I observed that Cells.ExportDataTable creates double type column instead of int type when filling values.

I have logged an investigation ticket with an id “CELLSNET-46074” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

7

@korosi,

We did evaluate your issue thoroughly. We could not simply detect the column type as int. Please assign the column type by yourself (as recommended), see the following sample code:
e.g
Sample code:

DataTable table = new DataTable(workSheet.Name);
 
                    table.Columns.Add("ID", typeof(int));
                    tableOptions.DataTable = table;
                    workSheet.Cells.ExportDataTable(0, 0, rowCount, 1,tableOptions);