首先不要忘记在项目(工程)里加上引用(Excel 2.5 就行了),这个是必要的库哦~
好了,下面是实现代码:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog2.FileName = "*.xls" '通用对话框
OpenFileDialog2.InitialDirectory = "C:/"
OpenFileDialog2.Filter = "Excel文件|*.xls"
OpenFileDialog2.FilterIndex = 1
If OpenFileDialog2.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim xlApp1 As Excel.Application '定义EXCEL类
Dim xlBook1 As Excel.Workbook '定义工件簿类
Dim xlSheet1 As Excel.Worksheet '定义工作表类
xlApp1 = CreateObject("Excel.Application") '打开Excel
xlApp1.Workbooks.Open(OpenFileDialog2.FileName)
xlBook1 = xlApp1.Workbooks("名单.xls")
xlSheet1 = xlBook1.Worksheets(1)
Dim objConn As New OleDbConnection(strConn) '打开数据库
Dim objAdap As New OleDbDataAdapter()
objConn.Open()
For i = 1 To xlSheet1.UsedRange.Rows.Count - 2 '此循环正是在批量的导入
Dim BH As New OleDbParameter("@BH", OleDbType.Integer, 6)
Dim XM As New OleDbParameter("@XM", OleDbType.Char, 10)
Dim YGBH As New OleDbParameter("@YGBH", OleDbType.Char, 10)
Dim strin As String = "Insert into 名单(编号,姓名,员工编号) " & " Values(@BH,@XM,@YGBH)"
Dim objcmd As New OleDbCommand(strin, objConn)
objcmd.Parameters.Add(BH)
objcmd.Parameters.Add(XM)
objcmd.Parameters.Add(YGBH)
BH.Value = Val(xlSheet1.Cells(i + 1, 1).value) '导入 这里有些IDE的value 打点时出不来,不用怕,直接写上就OK了~
XM.Value = xlSheet1.Cells(i + 1, 2).value
YGBH.Value = xlApp1.Cells(i + 1, 3).value
objcmd.ExecuteNonQuery()
objcmd = Nothing
Next
objConn.Close()
MsgBox("名单写入成功!", MsgBoxStyle.OkOnly, "提示")
xlBook1.Close(True) '关闭EXCEL工作簿
xlApp1.Quit() '关闭EXCEL
xlApp1 = Nothing '释放EXCEL对象
End If
End Sub
还有,做以下解释,我举得数据库例子是在我做的抽奖系统中取出来的,以后我会在其他文章中介绍抽奖系统
还有一些全局的变量的定义,和一些包的导入,在此就不罗列了,如果懂得一点数据库知识的都应该能补充上的
Good Luck!