• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1645
  • Last Modified:

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

0
gabulish
Asked:
gabulish
  • 5
  • 4
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now