Export Excel Data to DataTable and Check Mixed Data Type

Possible Usage Scenarios

If a column contains data of various types, the program will throw a type exception when exporting data to a DataTable. When exporting a data table, by default, Aspose.Cells evaluates the data type for the values based on the very first (cell) value in the column. So, if the value is a number, it means that the data type of the column would be numeric, which is reasonable. If the very first value is a number but there are alphanumeric data or values in the column, a string data type should be assigned. To cope with this, please use the ExportDataTable overload which involves ExportDataTableOptions and try to set the ExportTableOptions.CheckMixedValueType Boolean attribute to true if a column has both numeric and string values to avoid the error.

Export Excel Data to DataTable and Check Mixed Data Type

The following sample explains the use of the ExportTableOptions.CheckMixedValueType property to export Excel data to a DataTable. Please see the sample Excel file, its screenshot, and the console output for reference.

Sample Code

Screenshot

Console Output

Below is the console debug output of the above sample code

Column1 = System.String
Column2 = System.String
Column3 = System.Double
Column4 = System.Double
Column5 = System.String