那些数字前面有0的都给去掉了,假如数字太长了它用科学表达式表示,日期也不能正确显示。我在导入之前已经将数字型的转为字符型了,但还是不行。不知谁能给出好的方法解决这问题!
解决方案 »
- 用VB如何实现自动读取DVD光驱文件并播放?
- 连接数据库的树?
- 求助一个sql server的sql语句执行函数的代码
- 请教VB+SQL编写的MIS在一台裸机上安装时是否需要安装SQL SEVER作后台支持
- 大家帮我看一下下面这段查询代码有什么问题,我实在是看不出来,谢谢!
- c写的dll中一个函数参数是二维数组,在vb中如何声明和引用?
- *** 窗口焦点问题 ***
- 关于运行.exe调用dat生成另外dat的问题!谢谢!
- 如何去除一个字符串的后四个字符?或者把除后几个字符之外的字符赋给另一个变量?
- 再問一遍.在SQL語句中.怎麼進行區分大小寫查詢.
- 读写文件的问题
- 在线等待:怎么为datagrid控件的某一格赋值?
Rem 内容如下:
Rem 引用方式: Export(Ado.Recordset) 或 Export(Rds.RecordSet)
Rem 支持 Rds 与 Ado 的记录导出
Rem 得到所有数据类型,有些数据类型EXCEL不支持,已经替换掉Public Function FieldType(intType)
Select Case intType
Case 20
FieldType = "int"
Case 128
FieldType = "binary"
Case 11
FieldType = "bit"
Case 129
FieldType = "char"
Case 135
FieldType = "datetime"
Case 131
FieldType = "varchar"
Case 5
FieldType = "float"
Case 205
FieldType = "image"
Case 3
FieldType = "int"
Case 6
FieldType = "money"
Case 130
FieldType = "char"
Case 203
FieldType = "text"
Case 131
FieldType = "numeric"
Case 202
FieldType = "varchar"
Case 4
FieldType = "real"
Case 135
FieldType = "datetime"
Case 2
FieldType = "int"
Case 6
FieldType = "money"
Case 204
FieldType = "varchar"
Case 201
FieldType = "text"
Case 128
FieldType = "timestamp"
Case 17
FieldType = "varchar"
Case 72
FieldType = "varchar"
Case 204
FieldType = "varbinary"
Case 200
FieldType = "varchar"
End Select
End Function
Public Sub ExportToExcel(AdoRecordSet As ADODB.Recordset)
On Error GoTo Excel_Err
Dim Excel_Dsn As String
Dim Excel_Conn As New ADODB.Connection
Dim Excel_Adodc As New ADODB.Recordset
Dim mySql As String
Dim i, j, TmpField, FileName
Rem 得到文件名
For i = 0 To 100
If Len(i) = 1 Then
FileName = "C:\Query_0" & i
Else
FileName = "C:\Query_" & i
End If
If Dir(FileName & ".xls", vbHidden) = "" Then
Exit For
End If
Next
FileName = FileName & ".xls"
Excel_Dsn = "DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""" & FileName & """;DBQ=" & FileName
Excel_Conn.Open Excel_Dsn
With AdoRecordSet
If Not (.EOF And .BOF) Then
mySql = "Create Table [Query] ("
For i = 0 To .Fields.Count - 1
TmpField = FieldType(.Fields(i).Type)
If TmpField = "char" Or TmpField = "varchar" Or TmpField = "nchar" Or TmpField = "nvarchar" Or TmpField = "varbinary" Then
If .Fields(i).DefinedSize >= 256 Then
mySql = mySql & Trim(.Fields(i).Name) & " text,"
Else
mySql = mySql & Trim(.Fields(i).Name) & " " & FieldType(.Fields(i).Type) & "(" & .Fields(i).DefinedSize & ")" & ","
End If
ElseIf TmpField <> "image" Then
mySql = mySql & Trim(.Fields(i).Name) & " " & FieldType(.Fields(i).Type) & ","
End If
Next
mySql = Left(Trim(mySql), Len(Trim(mySql)) - 1)
mySql = mySql & ")"
Rem 创建表名
Excel_Adodc.Open mySql, Excel_Dsn, adOpenDynamic, adLockPessimistic
Rem 插入数据
For i = 0 To .RecordCount - 1
mySql = "Insert into [Query] Values("
For j = 0 To .Fields.Count - 1
TmpField = FieldType(.Fields(j).Type)
Rem Image 不作保存
If TmpField <> "image" Then
If IsNull(.Fields(j).Value) Then
mySql = mySql & "NULL,"
Else
mySql = mySql & "'" & .Fields(j).Value & "',"
End If
End If
Next
mySql = Left(Trim(mySql), Len(Trim(mySql)) - 1)
mySql = mySql & ")"
Excel_Adodc.Open mySql, Excel_Dsn, adOpenDynamic, adLockPessimistic
.MoveNext
Next
MsgBox "系统提示:" & Chr(13) & " 已经将文件保存到 [ " & FileName & " ]", 64, "系统信息:"
End If
End With
Excel_Conn.Close
Set Excel_Conn = Nothing
Set Excel_Adodc = Nothing
Exit Sub
Excel_Err:
MsgBox "发生错误:" & Err.Description & Chr(13) & "错误代码:" & Err.Number, 64, "系统信息:"
End Sub
If j + 1 Then
xlSheet.Cells(i + 1, j + 1).NumberFormat = "@" '把单元格设置为字符串
End If
xlSheet.Cells(i + 1, j + 1).value = "0101"
Next j其他格式可以通过录制宏,把设置单元格式记录下来。