001.Protected Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
002.
003. If Not IsNothing(myFile1.PostedFile) Then
004.
005. Dim UlFileName As String
006. Dim i As Integer
007.
008. UlFileName = "MyXls/" & System.IO.Path.GetFileName(myFile1.ToString)
009.
010.
011. myFile1.PostedFile.SaveAs(Server.MapPath(UlFileName))
012.
013.
014. Dim xlApp As New Excel.Application
015. Dim xlBook As Excel.Workbook
016. Dim xlSheet1 As Excel.Worksheet
017.
018. xlBook = xlApp.Workbooks.Open(Server.MapPath(UlFileName))
019. xlBook.Application.Visible = False
020.
021. xlSheet1 = xlBook.Worksheets(1)
022.
023.
024. Dim dt As New System.Data.DataTable
025. Dim dr As System.Data.DataRow
026.
027.
028. dt.Columns.Add("ProductID")
029. dt.Columns.Add("PartName")
030. i = 2
031. Do While Not Trim(xlSheet1.Cells.Item(i, 1).Value) = ""
032.
033. dr = dt.NewRow
034. dr("ProductID") = xlSheet1.Cells.Item(i, 1).Value
035. dr("PartName") = xlSheet1.Cells.Item(i, 2).Value
036.
037. dt.Rows.Add(dr)
038. i = i + 1
039. Loop
040.
041.
042.
043.
044. objConn = New SqlConnection
045. With objConn
046. .ConnectionString = strConn
047. If objConn.State = ConnectionState.Closed Then
048. .Open()
049. End If
050. End With
051. Try
052. sqlTmp = "SELECT Count(*) From TbSpecproductest Where ProductID = '" & dt.Rows(0)("ProductID") & "'"
053.
054.
055.
056. objConn = New SqlConnection
057.
058. With objConn
059. .ConnectionString = strConn
060. If objConn.State = ConnectionState.Closed Then
061. .Open()
062. End If
063. End With
064. objCmd = New SqlCommand
065. With objCmd
066. .Connection = objConn
067. .CommandType = CommandType.Text
068. .CommandText = sqlTmp
069. End With
070. objCmd.ExecuteScalar()
071.
072. If objCmd.ExecuteScalar > 0 Then
073. For i = 0 To dt.Rows.Count - 1
074. sqlTmp = "Update TbSpecproductest Set partname = '" & dt.Rows(i)("Partname") & "' "
075. sqlTmp &= "Where ProductID = '" & dt.Rows(i)("ProductID") & "'"
076. objCmd = New SqlCommand
077. With objCmd
078. .Connection = objConn
079. .CommandType = CommandType.Text
080. .CommandText = sqlTmp
081. End With
082. objCmd.ExecuteNonQuery()
083. Next
084. Else
085. For i = 0 To dt.Rows.Count - 1
086. sqlTmp = "INSERT INTO TbSpecproductest (ProductID,PartName)"
087. sqlTmp &= "Values ('" & dt.Rows(i)("ProductID") & "','" & dt.Rows(i)("PartName") & "')"
088. objCmd = New SqlCommand
089. With objCmd
090. .Connection = objConn
091. .CommandType = CommandType.Text
092. .CommandText = sqlTmp
093. End With
094. objCmd.ExecuteNonQuery()
095. Next
096. End If
097.
098.
099.
100.
101.
102. Catch ex As Exception
103.
104. End Try
105.
106.
107. xlApp.Application.Quit()
108. xlApp.Quit()
109. xlSheet1 = Nothing
110. xlBook = Nothing
111. xlApp = Nothing
112. End If
113.
114. End Sub
