Exporting data to DataTable produces an exception "The value of the cell B8 should not be a string value" in .NET

Aspose.Cells Product Family
1

I’m trying to use Cells.ExportDataTable and it is throwing an Aspose.Cells.CellsException:


"The value of the cell B8 should not be a string value."

I believe it is because the worksheet contains merged cells. Attached is an example.

This is clearly a bug.
2

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version:
Aspose.Cells for .NET v8.7.2.1 it works fine.

We have tested your excel file with the following sample code using the latest version and it exported the data from worksheet fine without any exception.

C#

Workbook workbook = new Workbook(“AsposeTest.xls”);

Worksheet worksheet = workbook.Worksheets[0];

Range rng = worksheet.Cells.MaxDisplayRange;

DataTable dt = worksheet.Cells.ExportDataTable(rng.FirstRow, rng.FirstColumn, rng.RowCount, rng.ColumnCount, true);
dt.TableName = “MyTable”;

3

Try it with the following range and it will throw the exception:


Workbook workbook = new Workbook(@“AsposeTest.xls”);

Worksheet worksheet = workbook.Worksheets[0];

DataTable dt = worksheet.Cells.ExportDataTable(3, 0, 28, 19, true);
dt.TableName = “MyTable”;
4

Hi Martin,


Thank you for sharing the code snippet.

Please note, the said problem is caused due to the value in the merged cell B8. You will notice that the value in upper cell of B8 is numeric where as the value in B8 is text so you need to set extra properties using the ExportTableOptions class to correctly export the given range as a column contains mixed type values. Please check the following updated code snippet and give it a try on your side.

C#

Workbook workbook = new Workbook(dir + “AsposeTest.xls”);
Worksheet worksheet = workbook.Worksheets[0];
DataTable dt = worksheet.Cells.ExportDataTable(3, 0, 28, 19, new ExportTableOptions() { CheckMixedValueType = false, ExportColumnName = true, ExportAsString = true});
dt.TableName = “MyTable”;
5

Thanks. This should work for now.


Is it supposed to throw if the values in any column are of mixed type? Seems like you’d an option to default to string on mixed values but if the column is all of a single type, keep that type.
6

Hi,


Good to know that your issue is sorted out.

And, yes, you will get an exception if a column has mixed set of data. Moreover, Aspose.Cells would determine data type from the cell value of the very first row in that column.

Thank you.
7

This was the issue for me. One column had a number in the first three rows and then a string in the following two rows. The column had “Text” format in Excel.

I added explicit options to not CheckMixedValueType:

var exportTableOptions = new ExportTableOptions { CheckMixedValueType = false, ExportColumnName = true, ExportAsString = true }; dataFromExcelSheet = worksheet.Cells.ExportDataTable(firstRow, firstColumn, totalRows, totalColumns, exportTableOptions);

8

@mortenma,

Please set CheckMixedValueType to true if a column has both numeric and string values to escape from error.

Let us know if you still find the issue.