asked on

Displaying Excel spreadsheet in GridView

I am trying to extract the contents of an Excel (11.0) Spreadsheet and display it in a vb.NET gridvew. After the the spreadsheet is loaded in the Gridview I also want to provide the user with the option of uploading the Gridview data to a SQL Server DB.

When I attempt tp execute the attached code I receive the below error:

Exception Details: System.Data.OleDb.OleDbException: External table is not in the expected format.

I've been struggling with this one for a while so any advice would be greatly appreciated?

Dim gv As New GridView()
 
        Dim DBConnection = New OleDbConnection( _
         "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\ExcelFiles\test.xls;" & _
        "Extended Properties=Excel 8.0;")
 
        DBConnection.Open()
 
        Dim SQLString As String = "SELECT * FROM [test$]"
        Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
        Dim DBReader As Data.IDataReader = DBCommand.ExecuteReader()
 
        gv.DataSource = DBReader
        gv.DataBind()
 
        DBReader.Close()
        DBConnection.Close()

Open in new window

divinewind80

I have not done this before, but I am guessing by the error that your Excel table does not have column headers on all columns. Can you confirm this?

gabulish

ASKER

Yes, if you are considering the first row of data as column headers.

ASKER CERTIFIED SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VBRocks

If you are still having problems loading your data, then try this. I added an additional function to check
for the existence of the table in the file. A list of tables in the file will be written to the Output window:

'Just call it like this:
Me.DataGridView1.DataSource = GetExcelData( _
"CC:\ExcelFiles\test.xls", _
"test", True)

'And the function:
Public Function GetExcelData( _
ByVal path As String, ByVal tableName As String, _
ByVal bFirstRowIsHeader As Boolean) _
As DataTable

Dim connectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & _
path & "';Extended Properties='Excel 8.0;HDR=" & _
bFirstRowIsHeader & ";IMEX=1'"

If TableExists(connectionString, tableName) = False Then
MessageBox.Show("Table '" & tableName & _
"' does not exist in the file. " & vbCrLf & vbCrLf & _
"Please see the Output window for a list of tables in file.")

Return Nothing
End If

Dim table As New DataTable(IO.Path.GetFileNameWithoutExtension(path))

Dim adapter As New OleDb.OleDbDataAdapter( _
"SELECT * FROM [" & tableName & "$]", connectionString)

adapter.Fill(table)
adapter.Dispose()

Return table

End Function

'Check for the existence of the table in the file
Private Function TableExists( _
ByVal connectionString As String, ByVal tableName As String)

Dim bExists As Boolean = False

Dim table As DataTable
Dim cn As New OleDb.OleDbConnection(connectionString)
Try
cn.Open()
table = cn.GetSchema("Tables")

For Each row As DataRow In table.Rows
Debug.WriteLine(row("TABLE_NAME")) 'write to Output window

If row("TABLE_NAME") = tableName Then
bExists = True
Exit For
End If
Next
Catch ex As Exception
MessageBox.Show("Error connecting to database")

Finally
cn.Close()
End Try

Return bExists

End Function

VBRocks

My apologies, I caught a mistake in that function, so I've corrected it here. Replace the function above
with this function:

'Check for the existence of the table in the file
Private Shared Function TableExists( _
ByVal connectionString As String, ByVal tableName As String)

Dim bExists As Boolean = False

Dim table As DataTable
Dim cn As New OleDb.OleDbConnection(connectionString)
Try
cn.Open()
table = cn.GetSchema("Tables")

For Each row As DataRow In table.Rows
Debug.WriteLine(row("TABLE_NAME")) 'write to Output window

If row("TABLE_NAME").ToString().ToLower() = _
(tableName & "$").ToLower() Then

bExists = True
Exit For
End If
Next
Catch ex As Exception
MessageBox.Show("Error connecting to database")

Finally
cn.Close()
End Try

Return bExists

End Function

gabulish

ASKER

It worked!!! but.... how would I then select certain columns (in my code) within the gridview and upload the data to SQL Server?

SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gabulish

ASKER

Very close...

I get this error on the adapter.Update(uploadTable) code:

"String or binary data would be truncated.The statement has been terminated."}

gabulish

ASKER

I got it. It was a problem with a datatype of one of my columns.

gabulish

ASKER

All the help was GREAT!!! Thanks for the timely responses and the detailed answers.