Exception thrown when using ExportDataTable

Aspose.Cells Product Family
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

7

@LeeAtMarkit,
Aspose.Excel is discontinued and no more available now. A new product Aspose.Cells has replaced it that supports its predecessor product as well as contains all the latest features available in different versions of MS Excel. Here is an example that demonstrates this feature.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
string filePath = dataDir + "Book1.xlsx";

// Instantiating a Workbook object
Workbook workbook = new Workbook(filePath);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 11, 2, true);

foreach (DataRow r in dataTable.Rows)
{
    foreach (DataColumn c in dataTable.Columns)
    {
        string value = r.Field<string>(c);
        Console.Write(value + "            ");
    }
    Console.WriteLine();
}

You may follow the link below to get more information about exporting data from worksheet.
Export data from worksheet

Download the free trial version of this new product here:
Aspose.Cells for .NET (Latest Version)

A comprehensive runnable solution can be downloaded here to test the features of this new product.