将SQL中的表导入到Excel中,我看到网上有很多方法,但都要引用Excel对象库,我装的是office2003,引用"Microsoft Excel 11.0 object library"。我现在碰到的问题是,在一些版本和我不同的机子上,我这个就用不了。有没有什么办法可以解决这个问题?还有可不可以不引用这个直接把SQL的表导入到Excel中的办法?
另外,我用一个ADO连接到Excel文件后,好像不能在里面建表。还有我在把从SQL表中读出的记录用文本的方式写入保存为csv格式时,本来在SQL中的字符串数字会把前面的0去掉(如:00166变成166)。有办法可以做到吗?谢谢!
另外,我用一个ADO连接到Excel文件后,好像不能在里面建表。还有我在把从SQL表中读出的记录用文本的方式写入保存为csv格式时,本来在SQL中的字符串数字会把前面的0去掉(如:00166变成166)。有办法可以做到吗?谢谢!
解决方案 »
- 急等~~~~~~~~~~~
- csdn的高手哪去了,一个问题问了一个周了,也没几个人回答,求求你们了
- MsHFlexGrid能直接打印么?
- 新手问一下,如何使用dll,在线等急
- label的caption为email:[email protected],请问怎样:鼠标点击label,弹出发email的email软件出来?
- 能不能改变DataGrid的前景色,使之和窗体(比如窗体的颜色是兰色)的颜色一样?
- 想学习RS232串口编程,有几个问题问一下.
- 不要笑我,我很菜鸟的!帮个忙了
- 如何按照指定的规则统计文件夹里面文件的个数
- ********高分请教*********
- 在线急等,关于windows网上邻居
- 关于drivelistbox?
----在00166前面加上“'”就可以了
'**
'** VB将数据导出到EXCEL,没有安装EXCEL的一样也可以导出.
'**
'** 调用方式: s_Export2Excel(Ado.Recordset) 或 s_Export2Excel(Rds.RecordSet)
'** 支持 Rds 与 Ado 的记录导出
'**
'*************************************************************************
'得到所有数据类型,有些数据类型EXCEL不支持,已经替换掉
Public Function f_FieldType$(ByVal sType&)
Dim iRe$
Select Case sType
Case 2, 3, 20
iRe = "int"
Case 5
iRe = "float"
Case 6
iRe = "money"
Case 131
iRe = "numeric"
Case 4
iRe = "real"
Case 128
iRe = "binary"
Case 204
iRe = "varbinary"
Case 11
iRe = "bit"
Case 129, 130
iRe = "char"
Case 17, 72, 131, 200, 202, 204
iRe = "varchar"
Case 201, 203
iRe = "text"
Case 7, 135
iRe = "datetime"
Case 205
iRe = "image"
Case 128
iRe = "timestamp"
End Select
f_FieldType = iRe
End Function'导出ADO记录集到EXCEL
Public Function f_Export2Excel(ByVal sRecordSet As ADODB.Recordset, ByVal sExcelFileName$ _
, Optional ByVal sTableName$, Optional ByVal sOverExist As Boolean = False) As Boolean
'On Error GoTo lbErr
Dim iConcStr, iSql$, iFdlist$, iDb As ADODB.Connection
Dim iI&, iFdType$, j, TmpField, FileName
Dim iRe As Boolean
'检查文件名
If Dir(sExcelFileName) <> "" Then
If sOverExist Then
Kill sExcelFileName
Else
iRe = False
GoTo lbexit
End If
End If
'生成创建表的SQL语句
With sRecordSet
For iI = 0 To .Fields.Count - 1
iFdType = f_FieldType(.Fields(iI).Type)
Select Case iFdType
Case "char", "varchar", "nchar", "nvarchar", "varbinary"
If .Fields(iI).DefinedSize > 255 Then
iSql = iSql & ",[" & .Fields(iI).Name & "] text"
Else
iSql = iSql & ",[" & .Fields(iI).Name & "] " & iFdType & _
"(" & .Fields(iI).DefinedSize & ")"
End If
Case "image"
Case Else
iSql = iSql & ",[" & .Fields(iI).Name & "] " & iFdType
End Select
Next
If sTableName = "" Then sTableName = .Source
iSql = "create table [" & sTableName & "](" & Mid(iSql, 2) & ")"
End With
'数据库连接字符串
iConcStr = "DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;" & _
"CREATE_DB=""" & sExcelFileName & """;DBQ=" & sExcelFileName
'创建Excel文件,并创建表
Set iDb = New ADODB.Connection
iDb.Open iConcStr
iDb.Execute iSql
'插入数据
With sRecordSet
.MoveFirst
While .EOF = False
iSql = ""
iFdlist = ""
For iI = 0 To .Fields.Count - 1
iFdType = f_FieldType(.Fields(iI).Type)
If iFdType <> "image" And IsNull(.Fields(iI).Value) = False Then
iFdlist = iFdlist & ",[" & .Fields(iI).Name & "]"
Select Case iFdType
Case "char", "varchar", "nchar", "nvarchar", "text"
iSql = iSql & ",'" & .Fields(iI).Value & "'"
Case "datetime"
iSql = iSql & ",#" & .Fields(iI).Value & "#"
Case "image"
Case Else
iSql = iSql & "," & .Fields(iI).Value
End Select
End If
Next
iSql = "insert into [" & sTableName & "](" & _
Mid(iFdlist, 2) & ") values(" & Mid(iSql, 2) & ")"
iDb.Execute iSql
.MoveNext
Wend
End With '处理完毕,关闭数据库
iDb.Close
Set iDb = Nothing
MsgBox "已经将数据保存到 [ " & sExcelFileName & " ]", 64
iRe = True
GoTo lbexitlbErr:
MsgBox "发生错误:" & Err.Description & vbCrLf & _
"错误代码:" & Err.Number, 64, "错误"
lbexit:
f_Export2Excel = iRe
End Function'调用示例
Sub test()
Dim iRe As ADODB.Recordset
Dim iConc As String
iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=F:\My Documents\客户资料.mdb"
Set iRe = New ADODB.Recordset
iRe.Open "维护员", iConc, adOpenKeyset, adLockOptimistic
f_Export2Excel iRe, "c:\b.xls", , True
iRe.Close
End Sub
还有一个问题,就是在填充数据的时候,如果字符串数据中本来有 ' 符号的话,会出现错误。