Error with converting worksheet into datatable

[rkoncarevic]

Hi Aspose team,

We have problem with converting workbook into datatable. We use Aspose.Cells 16.10.0.0 package licensed version. I created simple console application in order to reproduce problem.
Here is our code snippet:
Workbook workbook = new Workbook(@“somepath/Input.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
DataTable datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);

Plase find attached Input template that we are using.Input.zip (8.7 KB)

Thanks in advance,
Koncarevic Radovan

[Amjad_Sahi]

@rkoncarevic,

Thanks for the template file, sample code and details.

Well, since your data column F contains mixed value types (number, strings, etc.), so you got this error. Aspose.Cells will generally determine the datatype of a column based on the very first cell value in the column (F2 in your case). Since F2 is numeric value, so it tries to specify the numeric data type for the exported column but the column do have strings also, so you cannot insert strings into numeric data type column and you got the error.

To cope with your issue, you should instantiate ExportTableOptions and set the Boolean attribute “CheckMixedValueType” to true, see the following sample code that works fine:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\input.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];
            ExportTableOptions options = new ExportTableOptions();
            options.CheckMixedValueType = true; //Since your F column has mixed value, so you should set it true to escape from error.
            options.ExportColumnName = true;
            DataTable datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, options);

Hope, this helps a bit.

Thank you.