ExportDataTable throwing error as it incorrectly assumes the column type

Aspose.Cells Product Family
1

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

2

Hi Patrick,


Thank you for approaching Aspose support forums.

I am afraid, we require you sample spreadsheet along with the code snippet to replicate the problem on our end. Before you move forward to provide us the above requested information, we would request you to test the case against the latest version of Aspose.Cells for .NET (Latest Version) (in case you are using any older releases). If the problem persists, please provide the problematic sample and the code to reproduce the issue.

3

Hi,


Furthermore, for your information, for ExportDataTable() method the datatype for a column is actually 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, we do not loop though each value in that column for performance considerations which is right. As your underlying column has ambiguous data types, so you are getting exception for that column, I think you may define your desired DataTable based on your desired columns and specify your desired data types accordingly. You may store the while using skip error value choosing the relevant ExportDatatTable() overloaded version accordingly. Please see the sample code segment below for your reference:
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();
options.SkipErrorValue = true;
options.DataTable = dt;

worksheet.Cells.ExportDataTable(0, 0, 100,7,options);


Hope, this helps a bit.

Thank you.
4

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!

5

Hello Amjad,


Thanks for your answer, that has solved my problem.

Cheers,
Patrick
6

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#

string filePath = @"F:\Shak-Data-RW\Downloads\sample.csv";

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);