帮忙实现,谢谢 能否用VB写个程序,从EXCEL表中导入数据到SQL中指定的表的字段里,EXCEL表中有两列对应SQL表中有两个字段,谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我自己写的,速度不快,但还稳定,你自己改一下就是你要的了。Public Sub ExceltoSQL(ExcelSheetName As String, ExcelPath As String, SqlTablename As String, SqlDatabasename As String)MainForm.List1.MousePointer = 11Dim RsExtoSQL As New ADODB.RecordsetDim CnExtoSql As New ADODB.ConnectionDim xlBook As Excel.WorkbookSet xlApp = CreateObject("Excel.Application") '创建EXCEL对象Set xlBook = xlApp.Workbooks.Open(ExcelPath) '打开已经存在的EXCEL工件簿文件xlApp.Worksheets(ExcelSheetName).ActivateDim i As IntegerDim MM As IntegerDim nn As IntegerDim jj As IntegerDim Trans As Stringi = 1Dim Fieldname() As VariantReDim Fieldname(0) As VariantCnExtoSql.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=chang;PWD=assetok;Initial Catalog=" & SqlDatabasename & ";Data Source=192.168.0.250"CnExtoSql.OpenDo While xlApp.Cells(1, i).Value <> "" And Not IsNull(xlApp.Cells(1, i).Value) If Fieldname(UBound(Fieldname)) <> "" Then ReDim Preserve Fieldname(UBound(Fieldname) + 1) Fieldname(UBound(Fieldname)) = Replace(xlApp.Cells(1, i).Value, "£¨", "(") Fieldname(UBound(Fieldname)) = Replace(Fieldname(UBound(Fieldname)), "£©", ")") i = i + 1LoopDim strSQL As StringDim nno As IntegerFor nno = 0 To UBound(Fieldname) strSQL = strSQL & "[" & Fieldname(nno) & "] text not null,"NextOn Error Resume NextCnExtoSql.Execute "drop table [" & SqlTablename & "]"On Error GoTo 0CnExtoSql.Execute "create table [" & SqlTablename & "] ( " & Left(strSQL, Len(strSQL) - 1) & ")"RsExtoSQL.Open "select * from " & SqlTablename, CnExtoSql, adOpenDynamic, adLockOptimisticMM = 2 'ÐÐnn = 1 'ÁÐDo While ExtoAcCheckTable(MM, i - 1)RsExtoSQL.AddNew Do While nn <> i If xlApp.Cells(MM, nn).Value = "" Or IsNull(xlApp.Cells(MM, nn).Value) Then xlApp.Cells(MM, nn).Value = "?" End If Trans = CStr(xlApp.Cells(MM, nn).Value) If Left(Trans, 1) = "." Then RsExtoSQL.Fields.Item(nn - 1).Value = "0" & Trans Else RsExtoSQL.Fields.Item(nn - 1).Value = Replace(Trans, "£¨", "(") RsExtoSQL.Fields.Item(nn - 1).Value = Replace(RsExtoSQL.Fields.Item(nn - 1).Value, "£©", ")") End If' RsExtoSQL.Fields.Item(nn - 1).Value = xlApp.Cells(MM, nn).Value nn = nn + 1 LoopMM = MM + 1nn = 1LoopRsExtoSQL.UpdateSet xlApp = NothingxlBook.Close (False)If CnExtoSql.State <> adStateClosed Then CnExtoSql.CloseMainForm.List1.MousePointer = 0End SubPrivate Function ExtoAcCheckTable(NumberRow As Integer, NumberCol As Integer) As Boolean Dim ab As Integer, ac As Integer, YesNo As Boolean YesNo = False For ab = 0 To 5 '判断后5行是否有值 For ac = 1 To NumberCol If xlApp.Cells(NumberRow, ac).Value <> "" And Not IsNull(xlApp.Cells(NumberRow, ac).Value) Then YesNo = True GoTo Wanle End If Next NumberRow = NumberRow + 1 NextWanle:If YesNo = True Then ExtoAcCheckTable = TrueIf YesNo = False Then ExtoAcCheckTable = FalseEnd Function 另外还有一种方法:将Excel的数据导入SQL server :SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions这些代码写在那里执行,SQL还是VB中,还有,用的是SQL原来的表,只不过改它的两个字段的内容,其他不变,谢谢 放在一个COMMAND里面不就可以了! 放在COMMAND里还没运行就显示红色,语法错误啊!!!,怎回事???? textbox的输入时提示 用WINSOCKET 替代MSCOM,代码如何修改?请高手指教 请问如何调用这个DLL? 如何将PING命令添加到VB程序中取得相应域名的IP地址? 两个问题:Excel问题、Rnd函数问题 把BASIC语言用vb语言来实现,求救!高分! 请问以下问题如何实现(有关树状控件treeview)。 送分,送免费短信帐号,求一问题。谢谢大家帮忙 图象框画图的问题 怎样用vb实现知道服务器的因特网地址? 用winsock收发邮件时怎样显示进度条 请问:已知直线与圆的交点坐标怎么求?
Public Sub ExceltoSQL(ExcelSheetName As String, ExcelPath As String, SqlTablename As String, SqlDatabasename As String)
MainForm.List1.MousePointer = 11
Dim RsExtoSQL As New ADODB.Recordset
Dim CnExtoSql As New ADODB.ConnectionDim xlBook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open(ExcelPath) '打开已经存在的EXCEL工件簿文件
xlApp.Worksheets(ExcelSheetName).Activate
Dim i As Integer
Dim MM As Integer
Dim nn As Integer
Dim jj As Integer
Dim Trans As String
i = 1
Dim Fieldname() As Variant
ReDim Fieldname(0) As VariantCnExtoSql.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=chang;PWD=assetok;Initial Catalog=" & SqlDatabasename & ";Data Source=192.168.0.250"
CnExtoSql.Open
Do While xlApp.Cells(1, i).Value <> "" And Not IsNull(xlApp.Cells(1, i).Value)
If Fieldname(UBound(Fieldname)) <> "" Then ReDim Preserve Fieldname(UBound(Fieldname) + 1)
Fieldname(UBound(Fieldname)) = Replace(xlApp.Cells(1, i).Value, "£¨", "(")
Fieldname(UBound(Fieldname)) = Replace(Fieldname(UBound(Fieldname)), "£©", ")")
i = i + 1
Loop
Dim strSQL As String
Dim nno As Integer
For nno = 0 To UBound(Fieldname)
strSQL = strSQL & "[" & Fieldname(nno) & "] text not null,"
Next
On Error Resume Next
CnExtoSql.Execute "drop table [" & SqlTablename & "]"
On Error GoTo 0
CnExtoSql.Execute "create table [" & SqlTablename & "] ( " & Left(strSQL, Len(strSQL) - 1) & ")"RsExtoSQL.Open "select * from " & SqlTablename, CnExtoSql, adOpenDynamic, adLockOptimistic
MM = 2 'ÐÐ
nn = 1 'ÁÐ
Do While ExtoAcCheckTable(MM, i - 1)
RsExtoSQL.AddNew
Do While nn <> i
If xlApp.Cells(MM, nn).Value = "" Or IsNull(xlApp.Cells(MM, nn).Value) Then
xlApp.Cells(MM, nn).Value = "?"
End If
Trans = CStr(xlApp.Cells(MM, nn).Value)
If Left(Trans, 1) = "." Then
RsExtoSQL.Fields.Item(nn - 1).Value = "0" & Trans
Else
RsExtoSQL.Fields.Item(nn - 1).Value = Replace(Trans, "£¨", "(")
RsExtoSQL.Fields.Item(nn - 1).Value = Replace(RsExtoSQL.Fields.Item(nn - 1).Value, "£©", ")")
End If
' RsExtoSQL.Fields.Item(nn - 1).Value = xlApp.Cells(MM, nn).Value
nn = nn + 1
Loop
MM = MM + 1
nn = 1
Loop
RsExtoSQL.Update
Set xlApp = Nothing
xlBook.Close (False)
If CnExtoSql.State <> adStateClosed Then CnExtoSql.Close
MainForm.List1.MousePointer = 0
End Sub
Private Function ExtoAcCheckTable(NumberRow As Integer, NumberCol As Integer) As Boolean
Dim ab As Integer, ac As Integer, YesNo As Boolean
YesNo = False
For ab = 0 To 5 '判断后5行是否有值
For ac = 1 To NumberCol
If xlApp.Cells(NumberRow, ac).Value <> "" And Not IsNull(xlApp.Cells(NumberRow, ac).Value) Then
YesNo = True
GoTo Wanle
End If
Next
NumberRow = NumberRow + 1
Next
Wanle:
If YesNo = True Then ExtoAcCheckTable = True
If YesNo = False Then ExtoAcCheckTable = False
End Function
将Excel的数据导入SQL server :
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
这些代码写在那里执行,SQL还是VB中,还有,用的是SQL原来的表,只不过改它的两个字段的内容,其他不变,谢谢
放在一个COMMAND里面不就可以了!