Exception thrown when using ExportDataTable


#1

Hi,

I posted a similar problem a couple of weeks ago - though I got the post deleted. I’m using v3.3.2.0 of Aspose Excel.

The problem is that I take in an Excel file which has numeric and alpha-numeric data. If I have a column full of numeric data and then one cell has an invalid alpha-numeric entry then an exception is thrown when I use both ExportDataTable and ExportDataTableToString. I’m a bit perplexed as to why it’s throwing an exception with the latter because surely it should be converting all data types to String.

Anyway, I’m using VB.Net and my segment of code is:

maxRow_i = dataCells.MaxDataRow + 20 'In case there are blank data rows
maxColumn_b = dataCells.MaxDataColumn + 5 'May be blank columns
’Clear formatting on file
dataCells.ClearFormats(0, 0, maxRow_i, maxColumn_b)

'Export data
Try
dataDataTable = dataCells.ExportDataTable(0, 0, maxRow_i, maxColumn_b)
Catch exA As Exception
Try
dataDataTable = dataCells.ExportDataTableAsString(0, 0, maxRow_i, maxColumn_b)
Catch exB As Exception
'ERROR!!! - not what i expect
excelToDataSet = Nothing
errorReturn_s = modErrors.ERR_GENERAL
Exit Function
End Try
End Try

I’ve attached a file to show the error


#2

Please don’t call Cells.ClearFormats before exporting data. The returned string data depends on the formattings. Please try your code as:

maxRow_i = dataCells.MaxDataRow + 20 'In case there are blank data rows
maxColumn_b = dataCells.MaxDataColumn + 5 'May be blank columns

’Export data
Try
dataDataTable = dataCells.ExportDataTable(0, 0, maxRow_i, maxColumn_b)
Catch exA As Exception
Try
dataDataTable = dataCells.ExportDataTableAsString(0, 0, maxRow_i, maxColumn_b)
Catch exB As Exception
'ERROR!!! - not what i expect
excelToDataSet = Nothing
errorReturn_s = modErrors.ERR_GENERAL
Exit Function
End Try
End Try


#3

Some of the users submit the data in the numeric column with the % formatting enabled. I want to remove this so that the numeric data has maximum decimal places enabled and no % character as ExportDataTableToString wil also export this character. That’s why I use clearFormats.

Is there any other way to make sure that the ExportDataTable… functions take the actual values of the cells and not what is currently displayed on the screen???


#4

No. ExportDataTableAsString should return data as displayed.

To remove % character, you can iterate the data table to remove those extra characters.


#5

But I still lose numeric accuracy e.g. the cell contains 0.1237280947 but is represented on screen as 12.373%. Thus exporting the cell as 12.373% and removing the % character then dividing by 100 will lose me quite some accuracy.

If the exception that is thrown indicated which column the problem was in that would help a lot as I could then report back to the user with more detail as to which column had the error in.


#6

After clearing formats, ExportDataTableAsString method throws an exception. It's a bug and I will fixed it.

I don't recommend to clear format because you may lose needed formattings. For example, if you clear format in Column B, it will return a numeric value instead of DateTime value.

If you are sure that users only input data in column J and column K, you can put the following code before exporting:

Dim styleObj as Style = excel.Styles(excel.Styles.Add())
styleObj.Number = 0
Dim column as Column = cells.Columns(9)
column.Style = styleObj