Solved

Displaying Excel spreadsheet in GridView

Posted on 2007-12-05
10
1,634 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now