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