怎样用VB.NET读取Excel表格里的数据?

[tianch]

我有一个Excel文件(a.xls)，文件里有(用户名，姓名，身份证号)这几个字段，我想用VB.net将数据读出来，写到SQL数据库里，请问怎么样实现？
请给一个实例代码！谢谢！！

[rock29]

在sql中人工导入/导出数据,注意最后一步选择MTS包,生成的文件可以用记事本打开,那就是程序了

[dongbeiren]

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

[mainone]

http://www.csdn.net/develop/Read_Article.asp?Id=15544

[dongbeiren]

Dim cn As New SqlConnection("server=localhost;database=test;uid=sa;pwd=sa;")
Dim cmdInsert As New SqlCommand()
Dim cmdselect As New SqlCommand()

' Add the Insert command
cmdInsert.CommandText = "INSERT usertb(username,truename,useridentity) VALUES(@username,@truename,@useridentity)"
cmdInsert.Parameters.Add("@username", SqlDbType.VarChar, 50, "username")
cmdInsert.Parameters.Add("@truename", SqlDbType.VarChar, 50, "truename")
cmdInsert.Parameters.Add("@useridentity", SqlDbType.Char, 18, "useridentity")
'add the select command
cmdselect.CommandText = "SELECT username,truename,useridentity FROM usertb"

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")

数据库中usertb表中有uid,username,truename,useridentity四个字段，uid为主键，是自增的