这是我用VB.net作的应用程序,从EXCEL 到SQL里,首先要在引用里添加
Microsoft Excel 8.0 Object Library
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim a1, a2 As String
Dim constr As String = "server=lizh;database=test;uid=sa;pwd=sa"
Dim cnn As New System.Data.SqlClient.SqlConnection(constr)
Dim ExApp As New Excel.Application
Dim wks As New Workbook
wks = ExApp.Workbooks.Open(TextBox1.Text)
Dim xxsheet As Worksheet
xxsheet = wks.Sheets("sheet1")
Dim l As Integer = 1
While (Convert.ToString(xxsheet.Cells(l, 1).Value)) <> ""
l += 1
End While
Dim i As Integer
For i = 2 To l
cnn.Open()
a1 = Convert.ToString(xxsheet.Cells(i, 1).value)
a2 = Convert.ToString(xxsheet.Cells(i, 2).value)
Dim str As String = "insert into test values('" & a1 & "','" & a2 & "')"
Dim cmd As New System.Data.SqlClient.SqlCommand(str, cnn)
cmd.ExecuteNonQuery()
cnn.Close()
Next
End Sub
Microsoft Excel 8.0 Object Library
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim a1, a2 As String
Dim constr As String = "server=lizh;database=test;uid=sa;pwd=sa"
Dim cnn As New System.Data.SqlClient.SqlConnection(constr)
Dim ExApp As New Excel.Application
Dim wks As New Workbook
wks = ExApp.Workbooks.Open(TextBox1.Text)
Dim xxsheet As Worksheet
xxsheet = wks.Sheets("sheet1")
Dim l As Integer = 1
While (Convert.ToString(xxsheet.Cells(l, 1).Value)) <> ""
l += 1
End While
Dim i As Integer
For i = 2 To l
cnn.Open()
a1 = Convert.ToString(xxsheet.Cells(i, 1).value)
a2 = Convert.ToString(xxsheet.Cells(i, 2).value)
Dim str As String = "insert into test values('" & a1 & "','" & a2 & "')"
Dim cmd As New System.Data.SqlClient.SqlCommand(str, cnn)
cmd.ExecuteNonQuery()
cnn.Close()
Next
End Sub
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
用opendatasoucre
這樣效率好一些
小弟在VB窗口中试用的过程中,在
Dim wks As New Workbook 行中断
出现如下错误
未处理的“System.Runtime.InteropServices.COMException”类型的异常出现在 Project6.exe 中
其他信息:带有 CLSID {00020819-0000-0000-C000-000000000046} 的 COM 对象无效或未注册。
望指明原由,谢谢
你的我也试用了一下,出现了如下问题,不知什么原因
错误行在:cmd.ExecuteNonQuery() 未处理的“System.Data.SqlClient.SqlException”类型的异常出现在 system.data.dll 中其他信息:系统错误。
能否具体说一下如何做,这个我没用过,能否说的详细点
Sub Macro1()
'
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'使用数据源来连接数据库
conn.Open "Provider=MSDAORA;Data Source=aris;User ID=smart; Password=smart;"
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "insert into testtable (col1,col2) values (1,1)", conn
End Sub