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. For exporting 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 number, it means that the data type of the column would be numeric, which is reasonable. If the very first value is number but there are alphanumeric data or values in the column, a string data type should be assigned. To cope with it, please use ExportDataTable overload which involves ExportDataTableOptions and try to set ExportTableOptions.CheckMixedValueType Boolean attribute to “true” if a column has both numeric and string values to escape from error.
Export Excel Data to DataTable and Check Mixed Data Type
The following sample explains the use of ExportTableOptions.CheckMixedValueType property to export excel data to data table. Please see the sample Excel file, its screenshot and the console output for a reference.
Sample Code
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// Create workbook | |
Workbook workbook = new Workbook("sample.xlsx"); | |
// Access first worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Export Data Table Options | |
ExportTableOptions opts = new ExportTableOptions(); | |
opts.CheckMixedValueType = true; | |
// Export Data Table | |
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 7, 5, opts); | |
// Display the type of DataColumn | |
DataColumnCollection columns = dt.Columns; | |
foreach (DataColumn column in columns) | |
{ | |
Console.WriteLine(column.ColumnName + " = " + column.DataType); | |
} |
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