Solved

Displaying Excel spreadsheet in GridView

Posted on 2007-12-05
10
1,638 Views
Last Modified: 2012-06-21
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
Comment
Question by:gabulish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 9

Expert Comment

by:divinewind80
ID: 20411448
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
 

Author Comment

by:gabulish
ID: 20411472
Yes, if you are considering the first row of data as column headers.
0
 
LVL 27

Accepted Solution

by:
VBRocks earned 500 total points
ID: 20411765
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 27

Expert Comment

by:VBRocks
ID: 20411900
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 20412024
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
 

Author Comment

by:gabulish
ID: 20412199
It worked!!! but.... how would I then select certain columns (in my code) within the gridview and upload the data to SQL Server?  
0
 
LVL 27

Assisted Solution

by:VBRocks
VBRocks earned 500 total points
ID: 20412506
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
 

Author Comment

by:gabulish
ID: 20412963
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
 

Author Comment

by:gabulish
ID: 20413836
I got it. It was a problem with a datatype of one of my columns.
0
 

Author Closing Comment

by:gabulish
ID: 31412843
All the help was GREAT!!! Thanks for the timely responses and the detailed answers.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question