Problems with ExportDataTable

Aspose.Cells Product Family
1

I am consistantly getting Input sting not valid when attempting to call ExportDataTable then updating my datatable.

heres my routine:


Try

mExcel.Open(txtImportFilePath.Text)

Dim workSheet As Aspose.excel.Worksheet = mExcel.Worksheets(0)

Dim datatable As DataTable = workSheet.Cells.ExportDataTable(0, 0, workSheet.Cells.MaxRow + 1, workSheet.Cells.MaxColumn + 1)

datatable.TableName = ConfigurationSettings.AppSettings(CONFIG_TABLE_NAME)

For iRowCounter As Integer = 1 To datatable.Rows.Count - 1

For iColumnCounter As Integer = 0 To datatable.Columns.Count - 1

sdaLosses.InsertCommand.Parameters.Item("@" & datatable.Rows(0)(iColumnCounter).ToString).Value = datatable.Rows(iRowCounter)(iColumnCounter).ToString()

datatable.Columns(iColumnCounter).ColumnName = datatable.Rows(0)(iColumnCounter).ToString

Next

sdaLosses.Update(datatable)

datatable.AcceptChanges()

Next

Catch ex As Exception

Throw New Exception(“Import to SQL has failed. This may have occurred due to the following reasons” & vbCrLf & " 1) You may have the Excel file open." & vbCrLf & " 2) The file may not have been generated using this tool.")

End Try


2

The column data type of the exported data table may be string, integer, double or DateTime. Please make sure that the data type of each column is same as the type of SqlParameters.

You can also try to change your code to:

Dim datatable As DataTable = workSheet.Cells.ExportDataTableAsString(0, 0, workSheet.Cells.MaxRow + 1, workSheet.Cells.MaxColumn + 1)

3

@SeanB,
Aspose.Cells has replaced Aspose.Excel which is discarded now. This new product supports exporting data in a variety of ways. Here is an example which demonstrates the feature of exporting data from a worksheet to a DataTable object.

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.ExportDataTable(0, 0, 11, 2, true);

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

You can visit the following link to get more information on this feature:
Export Data from Worksheet

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

Get a ready to run solution here having multiple examples demonstrating different features of this product.