极速倒入sql记录到excel表格,19个子段5万条记录只需30秒
在网上看到一段程序,没有使用vba编程将sql数据倒入excel表格,速度极快,贴出于大家共赏.
其主要思想是:将EXCEL作为一个数据库使用,它的名字就是数据库的名字,工作表就是一张数据库中的表。
建立一个工程,引用dao,添加command1,粘贴一下代码
'声明API函数 Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long '定义变量 Private Sub Command1_Click() '如果EXCEL文件已经打开,需要先关闭它. Dim lpClassName As String Dim lpCaption As String Dim Handle As Long lpClassName = "XLMAIN" lpCaption = "Microsoft Excel - MyExcel.xls" Handle = FindWindow(lpClassName$, lpCaption$) If Handle <> 0 Then MsgBox "请先关闭EXCEL文件!", vbOKOnly + vbInformation, "不能对已经打开的文件进行写操作!" Exit Sub End If '检查EXCEL文件是否存在,如果存在则删除 If Dir(App.Path & "\MyExcel.xls") <> "" Then Kill App.Path & "\MyExcel.xls" '进行数据转换 Dim dbs As Database '打开数据库 Set dbs = OpenDatabase("", False, False, "ODBC;DSN=idms;DATABASE=idms;UID=sa;PWD=;") '连接字符串,请根据自己的情况修改 '把数据导入EXCEL dbs.Execute "SELECT " & "PersonId as 住户编号, Name as 姓名, Sex as 性别," & _ " Birthday as 出生日期, Nation as 民族,NativePlace as 籍贯," & _ " Politics as 政治面貌, IdCard as 身份证号码,Study as 学历," & _ " WorkPlace as 工作单位, WorkPhone as 单位电话, HomePhone as 家庭电话," & _ " MobilePhone as 手机或BP机, CarCard as 车牌号码, StartDate as 入住日期," & _ " Patch as 片区, DepartmentId as 公寓号, UnitNo as 单元号," & _ " RoomId as 房间号, ContractId as 购房合同号 " & " INTO [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] FROM " & "tbl_Tenement" '关闭数据库对象 dbs.Close '释放数据库对象 Set dbs = Nothing '调用EXCEL打开产生的EXCEL表格 Shell "d:\Program Files\Microsoft Office\Office10\EXCEL.EXE " & App.Path & "\MyExcel.xls", vbMaximizedFocus End Sub
19个字段,5万条记录,只需30-60秒,而采用直接用vba写入cell的方法两万条记录就需 83分钟,提速何止百倍,但这个方法有些局限,水平有限没能解决.希望大家讨论讨论,予以完善.
1、这段代码可能会随即出现“系统不支持选择的排序方式”错误,在增加resume next后解决,请问这是什么问题引发的,能不能排除掉。
2、问一下一张excel表格可以存储多少条记录,我在测试十万条记录的存入时出现“电子表格已满的错误”
欢迎大家讨论,让所有为导入数据到excel的速度困扰的朋友看到这段代码