其实不算原创,论坛里有人发的,我正好项目写到这里,缺的语句较多,无法运行,我补充了一下,可以简单的运行了,测试了一下,速度还不错。
VS2015可用,自己添加窗体和控件吧,只需要一个OpenFileDialog和Button,默认文件中带有字段名。
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.ClickTry
Dim fileName As String
OpenFileDialog1.ShowDialog()
fileName = OpenFileDialog1.FileName
'建立EXCEL连接,读入数据,支持 Microsoft Excel 2010
Dim strConn As String = "Provider= Microsoft.Ace.OleDb.12.0;Data Source='" & fileName & "';Extended Properties=Excel 12.0;"
'建立EXCEL连接,读入数据,支持 Microsoft Excel 2003
'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Dim ds As DataSet = New DataSet
da.Fill(ds, "自己的数据表")
Me.DataGridView1.DataSource = ds.Tables(0)
If ds.Tables(0).Rows.Count > 0 Then
Dim strSql As String
Dim CnnStr As String = "server=localhost;database=自己的数据库;user id=sa;password=密码"
Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(CnnStr)
conn.Open()
Dim myCmd As SqlClient.SqlCommand
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1 Step i + 1
'根据自己的字段添加
strSql = "insert into 登录表 VALUES ('" + ds.Tables(0).Rows(i).Item(0).ToString + "','" +
ds.Tables(0).Rows(i).Item(1).ToString + "','" + ds.Tables(0).Rows(i).Item(2).ToString + "','" +
ds.Tables(0).Rows(i).Item(3).ToString + "')"
Application.DoEvents()
myCmd = New SqlClient.SqlCommand(strSql, conn)
myCmd.ExecuteNonQuery()
Next
MsgBox("导入保存成功!", 48, "提示")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub