vb.net将 excel的数据 导入ACCESS中
excel的sheet1中有4列数据，结构和ACCESS中的一个表一样，如何用代码将excel的数据导入到aCCESS中？
[解决办法]
用程式将EXCEL中的数据导入数据库，参考下面：
OpenFileDialog1.Filter = "Excel files(*.xls)
[解决办法]
*.xls"
OpenFileDialog1.ShowDialog()
Dim strPath As String = OpenFileDialog1.FileName.ToString
If OpenFileDialog1.FileName = "" Then
MessageHandling.showMessage(“错误提示信息”)
Exit Sub
End If
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';data source=" & strPath
'查询语句
Dim strSql As String = "SELECT * FROM [Sheet1$]"
Dim dtTable As New DataTable
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, strConn)
Try
da.Fill(dtTable)
da.Dispose()
Catch ex As Exception
MessageHandling.showMessage(“错误提示信息”)
da.Dispose()
Exit Sub
End Try
上面就是把EXCEL里面的数据导入一个DATATABLE里面，然后写入数据库就可以了。
[解决办法]
后面这么写,先写个入库的函数
Public Sub insertd(ByVal s1 As String, ByVal s2 As String, ByVal s3 As String)
Dim sql As String
Dim con As New SqlClient.SqlConnection("Data Source=10.168.1.5;Initial Catalog=data;User ID=sa;password=sa;Integrated Security=False")
con.Open()
Dim sqlCommand As SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(String.Format("select Count(*) from newtable where a= '{0}'", s1), con)
Dim rowsAffected As Integer = sqlCommand.ExecuteScalar()
If sqlCommand.ExecuteScalar() > 0 Then
MessageBox.Show(s1 & " 数据已经存在")
Else
sql = "insert into newtable(a,b,c) values('" + s1 + "','" + s2 + "','" + s3 + "')"
sqlCommand.CommandText = sql
sqlCommand.ExecuteNonQuery()
MessageBox.Show(s1 & " 成功添加!")
End If
sqlCommand.Dispose()
sqlCommand.Clone()
End Sub
再按上面的代码,读取表dtTable中的数据,写到数据库中
OpenFileDialog1.Filter = "Excel files(*.xls)
[解决办法]
*.xls"
OpenFileDialog1.ShowDialog()
Dim strPath As String = OpenFileDialog1.FileName.ToString
If OpenFileDialog1.FileName = "" Then
MessageBox.Show("错误提示信息")
Exit Sub
End If
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';data source=" & strPath
'查询语句
Dim strSql As String = "SELECT * FROM [Sheet1$]"
Dim dtTable As New DataTable
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, strConn)
Try
da.Fill(dtTable)
For Each dr As DataRow In dtTable.Columns
insertd(dr(0).ToString(), dr(1).ToString(), dr(2).ToString())'入库
Next
da.Dispose()
Catch ex As Exception
MessageBox.Show("错误提示信息")
da.Dispose()
Exit Sub
End Try
