Inherits System.Windows.Forms.Form Dim DS1 As New System.Data.DataSet Dim DS2 As New System.Data.DataSet Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=e:\BOOK1.XLS; " & _ "Extended Properties=Excel 8.0;") ' Select the data from Sheet1 of the workbook. MyCommand = New System.Data.OleDb.OleDbDataAdapter( _ "select * from [Sheet1$]", MyConnection) '或者是DS1.ReadXml("c:\abcd.xml") MyCommand.Fill(DS1, "handle") DataGrid1.DataSource = DS1 MyConnection.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim j As Integer Dim person_id, information_id, handle As String Dim handle_time As System.DateTime 'If Me.SqlConnection1.Close() Then Me.SqlConnection1.Open() 'End If For j = 1 To DS1.Tables("handle").Rows.Count - 1 Me.SqlDataAdapter1.InsertCommand.Parameters("@person_id").Value = DS1.Tables("handle").Rows(j).Item(0) Me.SqlDataAdapter1.InsertCommand.Parameters("@information_id").Value = DS1.Tables("handle").Rows(j).Item(1) Me.SqlDataAdapter1.InsertCommand.Parameters("@handle").Value = DS1.Tables("handle").Rows(j).Item(2) Me.SqlDataAdapter1.InsertCommand.Parameters("@handle_time").Value = CDate(DS1.Tables("handle").Rows(j).Item(3)) Me.SqlDataAdapter1.InsertCommand.ExecuteNonQuery() Next 'MessageBox.Show(SqlDataAdapter1.InsertCommand.CommandText) Me.SqlConnection1.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub一个例子,原理就是先把Excel或xml文件的数据通过DataSet读到Datagrid中,再一个个取出保存到数据库中去。
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
CAdoConnection
CAdoRecordSet
是封装的MSADO15好像
Dim DS1 As New System.Data.DataSet
Dim DS2 As New System.Data.DataSet
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=e:\BOOK1.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)
'或者是DS1.ReadXml("c:\abcd.xml") MyCommand.Fill(DS1, "handle")
DataGrid1.DataSource = DS1
MyConnection.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim j As Integer
Dim person_id, information_id, handle As String
Dim handle_time As System.DateTime
'If Me.SqlConnection1.Close() Then
Me.SqlConnection1.Open()
'End If
For j = 1 To DS1.Tables("handle").Rows.Count - 1 Me.SqlDataAdapter1.InsertCommand.Parameters("@person_id").Value = DS1.Tables("handle").Rows(j).Item(0)
Me.SqlDataAdapter1.InsertCommand.Parameters("@information_id").Value = DS1.Tables("handle").Rows(j).Item(1)
Me.SqlDataAdapter1.InsertCommand.Parameters("@handle").Value = DS1.Tables("handle").Rows(j).Item(2)
Me.SqlDataAdapter1.InsertCommand.Parameters("@handle_time").Value = CDate(DS1.Tables("handle").Rows(j).Item(3))
Me.SqlDataAdapter1.InsertCommand.ExecuteNonQuery()
Next 'MessageBox.Show(SqlDataAdapter1.InsertCommand.CommandText)
Me.SqlConnection1.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try End Sub一个例子,原理就是先把Excel或xml文件的数据通过DataSet读到Datagrid中,再一个个取出保存到数据库中去。
1)我是使用ODBC来连接数据库的!!上面的方法好象不适合啊!!!2)使用1楼的方法的时候,出现错误提示“'MICROSOFT.JET.OLEDB.4.0' 报错!!”关注……
我一般也是这样做的