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

gabulishAsked:
Who is Participating?
 
VBRocksCommented:
Here's a function that will do it for you:

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



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

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

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

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

        adapter.Fill(table)
        adapter.Dispose()

        Return table

    End Function

0
 
divinewind80Commented:
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?
0
 
gabulishAuthor Commented:
Yes, if you are considering the first row of data as column headers.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
VBRocksCommented:
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

0
 
VBRocksCommented:
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

0
 
gabulishAuthor Commented:
It worked!!! but.... how would I then select certain columns (in my code) within the gridview and upload the data to SQL Server?  
0
 
VBRocksCommented:
You can do it like this:

        Dim connectionString As String = "your connection string"

        'Get the datasource from your grid
        Dim table As DataTable = _
            Me.DataGridView1.DataSource

        'List the columns you want to upload
        Dim listOfColumns As String() = _
            New String() {"Col1", "Col2", "Col3"}

        'Get the data from the grid's data source
        Dim uploadTable As DataTable = _
            table.DefaultView.ToTable(False, listOfColumns)

        '*** Set the table name in this SELECT Command to the name of the table to update ***
        Dim SelectCommand As String = "SELECT * FROM Table1"

        Dim adapter As New SqlClient.SqlDataAdapter(SelectCommand, connectionString)
        Dim cb As New SqlClient.SqlCommandBuilder(adapter) ' auto create commands

        adapter.Update(uploadTable)

        cb.Dispose()
        adapter.Dispose()

        MsgBox("Done")

0
 
gabulishAuthor Commented:
Very close...

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

"String or binary data would be truncated.The statement has been terminated."}
0
 
gabulishAuthor Commented:
I got it. It was a problem with a datatype of one of my columns.
0
 
gabulishAuthor Commented:
All the help was GREAT!!! Thanks for the timely responses and the detailed answers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.