Solved

Displaying Excel spreadsheet in GridView

Posted on 2007-12-05
10
1,640 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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
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 this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 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