Read Excel into Dataset using FileUpload Control

In my previous article I explain about how to download table content into Excel file.
In this article I will explain about how to read excel file into Dataset and display in Gridview.

For this I am using the FileUpload control to upload the file,dropdown list to select particular sheet and Gridview to display data.

Here we import the three namespaces.
Those are System.Data, System.Data.OleDb and System.IO

First we need to browse the file from disk by using Fileupload control.

By using Oledb we establish the connection.

Below code shows how to establish connection to selected file and read the sheets name from excel file into Dropdownlist.

      Dim postedFile As HttpPostedFile = fu1.PostedFile

        Dim filename As String = Path.GetFullPath(postedFile.FileName)
        Dim extension As String = Path.GetExtension(postedFile.FileName)
        Dim contentLength As Integer = postedFile.ContentLength
        hf.Value = filename

        If extension <> ".xls" AndAlso extension <> ".xlsx" Then
            Response.Write("Please Select Excel Files Only")
            Exit Sub
        End If
        If contentLength <= 2 Then
            Response.Write("Selected File contains No Data")
            Exit Sub
        End If

        Try
            Dim con As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source='" & _
                         filename & "'; " & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1;""")
            con.Open()

            Dim returnValue As DataTable
            returnValue = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
            For l As Integer = 0 To returnValue.Rows.Count - 1
                ddl1.Items.Add(returnValue.Rows(l)("TABLE_NAME"))
            Next

            ddl1.Items.Insert(0, "")

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try