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.
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”.
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.
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();
Is this possible for other methods like “CreateExcelFromSqlDataReader” to set datatype for a column is determined by the very first value in that column?
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:
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.