Dim da As New SqlDataAdapter()
cmdselect.Connection = cn
cmdInsert.Connection = cn
da.SelectCommand = cmdselect
da.InsertCommand = cmdInsert
'da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.AcceptChangesDuringFill = True
Dim myapp As New Excel.Application()
Dim xlsbook1 As Excel.Workbook
xlsbook1 = myapp.Workbooks.Open("D:\a.xls")
Dim xlssheet1 As Excel.Worksheet
xlssheet1 = xlsbook1.Sheets("sheet1")
Dim ds As New DataSet()
da.Fill(ds, "usertb")
Dim mytable As DataTable = ds.Tables("usertb")
Dim l As Integer = 1
While (Convert.ToString(xlssheet1.Cells(l, 1).Value)) <> ""
l += 1
End While
Dim i As Integer
For i = 2 To l - 1
Dim mydatarow As DataRow = mytable.NewRow
mydatarow.Item(0) = xlssheet1.Cells(i, 1).Value
mydatarow.Item(1) = xlssheet1.Cells(i, 2).Value
mydatarow.Item(2) = xlssheet1.Cells(i, 3).Value
mytable.Rows.Add(mydatarow)
Next
da.Update(ds, "usertb")
Dim username As String
Dim truename As String
Dim useridentity As String
Dim cn As New SqlConnection("server=localhost;database=test;uid=sa;pwd=sa;")
Dim myapp As New Excel.Application()
Dim xlsbook1 As Excel.Workbook
xlsbook1 = myapp.Workbooks.Open("D:\a.xls")
Dim xlssheet1 As Excel.Worksheet
xlssheet1 = xlsbook1.Sheets("sheet1")
Dim l As Integer = 1
While (Convert.ToString(xlssheet1.Cells(l, 1).Value)) <> ""
l += 1
End While
Dim i As Integer
For i = 2 To l
cn.Open()
username = Convert.ToString(xlssheet1.Cells(i, 1).Value)
truename = Convert.ToString(xlssheet1.Cells(i, 2).Value)
useridentity = Convert.ToString(xlssheet1.Cells(i, 3).Value)
Dim strsql As String = "insert into usertb values('" & username & "','" & truename & "','" & useridentity & "')"
Dim cmd As New SqlCommand(strsql, cn)
cmd.ExecuteNonQuery()
cn.Close()
Next