Uploading Excel Sheet to Database using VB.Net

1. Open aspx page in design view add:
File Uploader(ID=FileUpload1),
Button(ID=btnUpload),
Label(ID=lblMessage)
controls inside a Pannel control(ID=Pannel1)

AND add:

Label(ID=lblFileName),
DropDownList(ID=ddlSheets) add [sheet1$],[sheet2$],[sheet3$]as list items to
DropDownList,
Button(ID=btnSave),
add all these inside a pannel(ID=Pannel2)control.


2. Connection string for Excel 1997-2003 and Excel>2003 are different
declare the connection for the formats of Excel:

Dim Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

Dim Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"


3.Write the following event handler code for upload button click event(btnUpload_Click)


Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
If FileUpload1.HasFile Then

Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

Dim FolderPath As String = ActualPath
Dim FilePath As String = ActualPath + FileName
FileUpload1.SaveAs(FilePath)
Panel2.Visible = True
Panel1.Visible = False
lblFileName.Text = FileName
End If
End Sub


4. Add the following code in code behind:

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim SQLTable As String = "Your Database Table" '"Dot_Net_Prod_Range_Line" '"temp_tbl" "EmployeeMaster'
Dim FileName As String = lblFileName.Text
Dim Extension As String = Path.GetExtension(FileName)
Dim FolderPath As String = ActualPath + FileName

Dim FilePath As String = ActualPath + FileName
Dim sheet As String = ddlSheets.SelectedItem.Text
Dim UploadTable As String = "SELECT * FROM " & sheet
Dim ClearTable As String = "DELETE FROM " & SQLTable

Dim conStr As String = "" ' DALObj.M_SqlConStr()

Select Case Extension

Case ".xls"

'Excel 97-03

conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties='Excel 8.0;HDR={1}'"

Exit Select

Case ".xlsx"

'Excel 07
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties='Excel 8.0;HDR=Yes'"

Exit Select

End Select

Try
con = New SqlConnection(Your SQL Connection String)
cmd = New SqlCommand(ClearTable, con)

con.Open()
cmd.ExecuteNonQuery()
con.Close()

'Series of commands to bulk copy data from the excel file into our SQL table
oledbCon = New OleDbConnection(conStr)
oleCmd = New OleDbCommand(UploadTable, oledbCon)
oledbCon.Open()

Dim dr As OleDbDataReader = oleCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(Your SQL Connection String)

bulkCopy.DestinationTableName = SQLTable
bulkCopy.WriteToServer(dr)
oledbCon.Close()
lblMessage.Text = " Records are uploaded successfully"

Catch ex As Exception

lblMessage.ForeColor = System.Drawing.Color.Red

lblMessage.Text = ex.Message

Finally

oledbCon.Close()

oledbCon.Dispose()

Panel1.Visible = True

Panel2.Visible = False

End Try

End Sub


NOTE: The Number of Columns in in Excel Sheet and Database should be equal and there should be heading column in Excel sheet.