Free Support Forum - aspose.com

ExportDataTable changes the datatype of columns

Hi team,
this is an urgent bug and I would like to know how fast can we workaround it.

We are trying to import excel data into the database. For that we use the API:
sheet1.Cells.ExportDataTable(dtDuplicate, headerRowIndex, 0, dataRowCount + 1, true, true);

dtDuplicate is a DataTable object that has all the columns required. There is no data though. It is supposed to be filled by the data from the excel.

headerRowIndex is a 0-based index of excel rows from where to read data.

One of the columns in the excel is "EntityName". This is a string in the dataTable. But as soon as I export, its type changes to int32 !!!!

Help is needed as we have a client demo on Monday. This has never happened before.

Sunil

ok. after doing further research, I found that it is not related to any particular column name. Rather, ANY data in column “AF” (which translates to column index 31, 0 based) will change to datatype int32 even if the data is marked as string.


Hope this helps.

Sunil

Hi,


Well, Cells.ExportDataTable() decides which data type would be setup for particular column in Excel sheet. By default, it is based on the first value (normally) in the column, if it is string value, then the DataColumn would have string data type for the data table, if it has numeric value, the numeric data type would be taken for the column. Please make sure that if it goes in that way.

If you still have problem or confusion, give us your template Excel file here, we will check it soon.
Also, give me your fourth parameter value “dataRowCount”.

Also, which version of the product you are using?

Thank you.



Please close the issue. This is fixed.

How can force ExportDataTable to read data based on configuration rather than data in the columns. Example, I have an excel column which is alphanumeric, it works fine when there is an alphanumeric data in the first row ( reads as string) if the data in the first row is numeric, it reads as double and it fails because processing logic expects it to be a string… Is there any way I can say column 1 is int and column 2 is string etc.

Hi Jeff,


Well, yes, (for ExportDataTable() method), the datatype for a column is determined by the very first value in that column, if it is numeric then the data type would be numeric for the field, if it is string or alphanumeric , then it will be string data type.

I think you may define your desired DataTable based on your desired columns and specify your desired data types accordingly. Then use the relevant ExportDatatTable() overloaded version to store the data into it. Please see the sample code segment below:
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(string));
dt.Columns.Add(“column4”, typeof(double));
dt.Columns.Add(“column5”, typeof(double));
dt.Columns.Add(“column6”, typeof(double));
dt.Columns.Add(“column7”, 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,7,options);

Thank you.

Is this possible for other methods like “CreateExcelFromSqlDataReader” to set datatype for a column is determined by the very first value in that column?

@tbs.akshay,

There is no such method, i.e., “CreateExcelFromSqlDataReader” available in Aspose.Cells APIs. Aspose.Cells has Cells.ImportDataReader() method which is used to import data reader object into Excel sheet, see the document what other data importing options are available for your reference:
https://docs.aspose.com/display/cellsnet/Import+Data+into+Worksheet

Yes,
My question is about the same method Cells.ImportDataReader().

Is this possible for Cells.ImportDataReader() method to set datatype for a column is determined by the very first value in that column?

@tbs.akshay,

I am afraid, the Cells.ImportDataReader() method is for importing data from data reader object to worksheet and not exporting from worksheet to data reader object. So, there is no question for the data type of a column to be determined, it will import data from data reader as it is there.