Question about ExportDataTable

Aspose.Cells Product Family
1

Hi,

We’re getting an error with ExportDataTable. I’m looking to read in the data in a spreadsheet for processing using:

Worksheet oWS = oWB.Worksheets[iSelectedSheet];
DataTable dt = new DataTable();
dt = oWS.Cells.ExportDataTable(1, 0, oWS.Cells.MaxRow + 1, oWS.Cells.MaxColumn + 1);

Which normally works fine, but if we have a column with data in it that starts in numeric format and then changes to a string, it throws an error such as:

Input string was not in a correct format. Couldn’t store in Column14 Column. Expected type is Double.

If we go through every field in that column and add an apostrophe before the data, it’s fine, but for a lot of data that’s obviously a long job and formatting as text doesn’t help.

Any ideas how to get around this?

Thanks.

2

Hi,

Which version of Aspose.Cells you are using. Could you post your template excel file here, so that we can reproduce (extracting data from a worksheet to fill a datatable) the issue you have mentioned.

Thank you.

3

Hi,

It’s 4.4.1.10

Example file attached, this one gives me the error:
Input string was not in a correct format.Couldn’t store in Column4 Column. Expected type is Double.

All the cells here have been formatted as text.

Thanks

4

Hi,

Please use Cells.ImportDataTableAsString() method instead for your scenario. Following is my sample code using your template file which works fine.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(@"d:\test\ExampleData.xls");
Worksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = new DataTable();
dataTable = worksheet.Cells.ExportDataTableAsString(1, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
int i = workbook.Worksheets.Add();
workbook.Worksheets[i].Cells.ImportDataTable(dataTable,false,0,0,worksheet.Cells.MaxRow + 1,worksheet.Cells.MaxColumn + 1,false);
workbook.Save("d:\\test\\out_ExampleData.xls");

Thank you.

5

Hi Amjad,

That works great, thanks very much.