I am exporting data into a DataTable and one of the columns has multiple data types in the one column (i.e. 10 rows of integers and then 3 rows of strings) which is valid data in my spreadsheet and the ExportDataTable routine incorrectly assumes it’s an integer column and throws an exception while trying to create the data table
Hi Patrick,
Hi,
Hello Babar, I have provided enough information for this to be replicated, and yes I have already upgraded the version of Aspose.Cells, Amjad in the next post seems to have provided a great answer and I am trying that out right now!
Hello Amjad,
Hi Patrick,
Thanks for your feedback and using Aspose.Cells.
We have recently added a property ExportTableOptions.CheckMixedValueType. If you do not want to specify the data types for your columns as shared in the above post. Then you can use this property to enable Aspose.Cells to find appropriate data type for the column by itself.
Please see the following code and screenshot attached for your reference.
As you can see inside the screenshot, all values in column Account are integers except the last value which is non-integer.
C#
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
int row, col;
CellsHelper.CellNameToIndex("A1", out row, out col);
//Now calculate total rows and total columns
int totalColumns = worksheet.Cells.MaxDataColumn - col + 1;
int totalRows = worksheet.Cells.MaxDataRow - row + 1;
ExportTableOptions options = new ExportTableOptions();
options.CheckMixedValueType = true;
options.ExportColumnName = true;
DataTable dt = worksheet.Cells.ExportDataTable(row, col, totalRows, totalColumns, options);