We want to read data from a huge excel file into a datatable in our application and we have tried using aspose for the same.
Following is the piece of code that we are using.
---------------------------------------------------------------------------
FileStream fstream = new FileStream(path, FileMode.Open);
Workbook workbook = new Workbook(fstream);
Worksheet worksheet = workbook.Worksheets[“Investor Schedules”];
DataTable dataTable = worksheet.Cells.ExportDataTable(79, 2, 200, 350, false);
fstream.Close();
---------------------------------------------------------------------------
We are getting an error worksheet.Cells.ExportDataTable function call.
"The value of the cell C158 should not be a string value."
At the forum we found this error is resolved long back, but we are still getting the same in our case.
OUR Application details:
Windows application with Target Framework: .Net Framework 4 Client Profile.
And so using the latest DLLS from net3.5_ClientProfile folder from the latest version available (Version 8.5.0.0)
Please help us on this error…!
Hi,
Thanks for your posting and using Aspose.Cells.
This error occurs because cell C158 value is string type while it should be some other type like integer or double type. The type of the column is determined by first data row. If the cell in the data row is integer, then all the column will be treated as integer and if any cell inside the column will be string, then this exception will be thrown.
Please check the source excel file attached by me. All values in this source excel file worksheet are integer values however the column C contains “data” in cell C6 which is not integer, so this exception will be thrown.
To avoid this exception you should set ExportTableOptions.CheckMixedValueType to true, then Aspose.Cells will not throw this exception.
Please see the following sample code for your reference and see the comment inside it.
C#
Workbook workbook = new Workbook(“source.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
ExportTableOptions opts = new ExportTableOptions();
opts.CheckMixedValueType = true; //commmenting this line will throw exception
opts.ExportColumnName = false;
DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 11, 4, opts);
Thanks a lot…!
It worked.
Also I used ExportDataTableAsString, that also solved the issue. As we have mixed kind of data, we prefer Exporting data table as string only.
Appreciate your quick response. thanks
Hi,