Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Excel
Partial Class WebSpecProduct_frmspecProduct
Inherits System.Web.UI.Page
'Dim dt As New DataTable
Dim xlApp As New Excel.Application
Protected Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
If Not IsNothing(myFile1.PostedFile) Then
Dim UlFileName As String
Dim i As Integer
UlFileName = "MyXls/ProductSpecification.xls" & System.IO.Path.GetFileName(myFile1.ToString)
'*** Save Images ***'
myFile1.PostedFile.SaveAs(Server.MapPath(UlFileName))
'*** Create Excel.Application ***'
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
xlBook = xlApp.Workbooks.Open(Server.MapPath(UlFileName))
xlBook.Application.Visible = False
xlSheet1 = xlBook.Worksheets(1)
'*** Create DataTable ***'
Dim dt As New System.Data.DataTable
Dim dr As System.Data.DataRow
'*** Column ***'
dt.Columns.Add("CustomerID")
dt.Columns.Add("EmployeeName")
dt.Columns.Add("Designation")
dt.Columns.Add("Posting")
dt.Columns.Add("Dept")
i = 2
Do While Not Trim(xlSheet1.Cells.Item(i, 1).Value) = ""
'*** Rows ***'
dr = dt.NewRow
dr("CustomerID") = xlSheet1.Cells.Item(i, 1).Value
dr("EmployeeName") = xlSheet1.Cells.Item(i, 2).Value
dr("Designation") = xlSheet1.Cells.Item(i, 3).Value
dr("Posting") = xlSheet1.Cells.Item(i, 4).Value
dr("Dept") = xlSheet1.Cells.Item(i, 5).Value
dt.Rows.Add(dr)
i = i + 1
Loop
'*** End DataTable ***'
'*** Insert to Database ***'
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim strConnString As String
Dim strSQL As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database/dbSpecProduct.mdb") & ";"
objConn = New OleDbConnection(strConnString)
objConn.Open()
For i = 0 To dt.Rows.Count - 1 '*** Insert Record ***'
strSQL = "INSERT INTO Employee (CustomerID,EmployeeName,Designation,Posting,Dept) " &
" VALUES ('" & dt.Rows(i)("CustomerID") & "','" & dt.Rows(i)("EmployeeName") & "' " &
",'" & dt.Rows(i)("Designation") & "','" & dt.Rows(i)("Posting") & "' " &
",'" & dt.Rows(i)("Dept") & "') "
objCmd = New OleDbCommand(strSQL, objConn)
Next
Me.lblText.Text = "Record Inserted."
'*** Quit And Clear Object ***'
'objConn.Close()
'objConn = Nothing
xlApp.Application.Quit()
xlApp.Quit()
xlSheet1 = Nothing
xlBook = Nothing
xlApp = Nothing
End If
End Sub