Excel to 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
Here I established the connection first, after that I read the excel file schema into dropdown list using OleDbSchemaGuid.Tables.
I change the Autopostback property of DropDown list to true, to display the Excel file content for every selected sheet from Gridview.
In dropdownlist selectedIndexChanged event I wrote code like below.
Try Dim filename As String = hf.Value Dim con As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source='" & _ filename & "'; " & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1;""") Dim oledad As OleDbDataAdapter Dim ds As New DataSet oledad = New OleDbDataAdapter("select * from [" & ddl1.SelectedItem.Text & "]", con) oledad.Fill(ds) gv1.DataSource = ds.Tables(0).DefaultView gv1.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try
To display data also I established the connection again,if you want you can make global connection for that.
Whenever user selects the particular sheet the gridview displays the data of that sheet.
You can find source code for the sam below.
Download source code here
