搜索
查看: 8760|回复: 33

数据导出到excel代码 求优化

  [复制链接]

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
发表于 2012-7-19 12:42:10 | 显示全部楼层 |阅读模式
50专家分
本帖最后由 lzj_ykds 于 2012-7-24 08:27 编辑

有一个acccess文件,里面的表khzl中有三个字段,现在想把数据导出到excel中,要求数字不能是科学计数法,时间格式为yyyy/mm/dd.
数据文件如下链接,是access2007文件,里面有3万多的数据
文件名:Database2.7z

我自己写了一段代码,导出速度很慢,求优化

import win
import com;
import com.excel
import time.ole;
import string
import time.performance
var t1=time.performance.tick()

var connstr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\桌面\Database2.accdb;Persist Security Info=False"

var conn = com.CreateObject("ADODB.Connection");

conn.Open(connstr);
var rs = com.CreateObject("ADODB.RecordSet") ;
rs.Open(
"select * from khzl",conn);
var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)
sheet.Cells.NumberFormatLocal =
"@"

for(j=1;rs.Fields.Count;1){
   sheet.cells(1,j).value2=rs.Fields.Item(j-1).Name
}
i=2
while(!rs.eof){
   
for(j=1;rs.Fields.Count;1){
        
var fldvle = "";
        
select(rs.Fields.Item(j-1).type) {
            
case 131 {
               
if(rs(j-1).value==null){
                    fldvle=
""
                }
else {
                    fldvle=string.format(
"%.0f",rs(j-1).value);
                }
            }
            
case 7 {
               
if(rs(j-1).value==null){
                  fldvle=
""
                }
else {
                    tbl=rs(j-1).value;
                    tbl.format =
"%Y/%m/%d %H:%M:%S";
                    fldvle=
tostring(tbl);
                }
            }
        }
        sheet.cells(i,j).value2=fldvle

    }
    i++
    rs.movenext()
}

rs.close()
conn.close()
var t2=time.performance.tick()
io.open()
io.print(t2-t1)
win.msgbox(
"ok")


最佳答案

回复

使用道具 举报

75

主题

849

帖子

5008

积分

六级会员

The only one

Rank: 9Rank: 9Rank: 9

积分
5008
发表于 2012-7-19 12:42:11 | 显示全部楼层

[quote][size=5][b]原来400秒,现在12秒就搞定了![/b][/size][/quote][quote][align=left][font=新

原来400秒,现在12秒就搞定了!
import win
import com;
import com.excel
import time.ole;
import string
import time.performance
var t1=time.performance.tick()

var connstr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database2.accdb;Persist Security Info=False"

var conn = com.CreateObject("ADODB.Connection");

conn.Open(connstr);
var rs = com.CreateObject("ADODB.RecordSet") ;
rs.Open(
"select * from khzl",conn);
var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)
sheet.Cells.NumberFormatLocal =
"@"

var count=rs.Fields.Count

for(j=1;count;1){
   sheet.cells(1,j).value2=rs.Fields.Item(j-1).Name
}

var i=2
var o=i;
var rows={}
while(!rs.eof){
   
var  row={}
   
for(j=1;count;1){
        
var fldvle;
        
if(rs(j-1).value==null){
            table.push(row,
"")
            
continue ;
        }
        
select(rs.Fields.Item(j-1).type) {
            
case 131 {
                fldvle=string.format(
"%.0f",rs(j-1).value);
            }
            
case 7 {
               
var tbl=rs(j-1).value;
                tbl.format =
"%Y/%m/%d %H:%M:%S";
                fldvle=
tostring(tbl);
            }
        }
        table.push(row,fldvle)
    }
    table.push(rows,row)
    i++;
    rs.movenext()
   
if(i%10000=0){
        sheet.Range(
"A"++o++":C"++(i-2)).value2=rows
        rows={};
        o=i;
    }
}
//剩余的填入
sheet.Range("A"++o++":C"++(i-2)).value2=rows

rs.close()
conn.close()

var t2=time.performance.tick()
io.open()
io.print(t2-t1)
win.msgbox(
"ok")


点评

同意: 5.0 真棒: 5.0
继续努力: 5.0
真棒: 5
  发表于 2012-7-19 22:33
是改成 sheet.Range("A"++o++":C"++(i-1)).value2=rows  发表于 2012-7-19 16:57
同意: 5 真棒: 5 继续努力: 5
这句 sheet.Range("A"++o++":C"++(i-2)).value2=rows 改成 sheet.Range("A"++o++":C"++(i-2)).value2=rows 就对了   发表于 2012-7-19 16:56
小时候,幸福是一件东西,拥有就是幸福; 长大了,幸福是一个目标,达到就是幸福; 成熟后,发现幸福原来是一种心态,领悟就是幸福.
回复

使用道具 举报

75

主题

849

帖子

5008

积分

六级会员

The only one

Rank: 9Rank: 9Rank: 9

积分
5008
发表于 2012-7-19 14:02:27 | 显示全部楼层

[color=#000][backcolor=rgb(249, 249, 249)][font=新宋体]excel[/font][/backcolor][/


excel 写入速度受限,全部写入空格还要很长时间,数据够多!优化了下节省40秒左右


import win
import com;
import com.excel
import time.ole;
import string
import time.performance
var t1=time.performance.tick()

var connstr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database2.accdb;Persist Security Info=False"

var conn = com.CreateObject("ADODB.Connection");

conn.Open(connstr);
var rs = com.CreateObject("ADODB.RecordSet") ;
rs.Open(
"select * from khzl",conn);
var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)
sheet.Cells.NumberFormatLocal =
"@"

var count=rs.Fields.Count
for(j=1;count;1){
   sheet.cells(1,j).value2=rs.Fields.Item(j-1).Name
}
i=2
while(!rs.eof){
   
for(j=1;count;1){
        
var fldvle;
        
if(rs(j-1).value==null){
            sheet.cells(i,j).value2=
"";
            
continue ;
        }
        
select(rs.Fields.Item(j-1).type) {
            
case 131 {
                fldvle=string.format(
"%.0f",rs(j-1).value);
            }
            
case 7 {
               
var tbl=rs(j-1).value;
                tbl.format =
"%Y/%m/%d %H:%M:%S";
                fldvle=
tostring(tbl);
            }
        }
        sheet.cells(i,j).value2=fldvle
    }
    i++
    rs.movenext()
}

rs.close()
conn.close()
var t2=time.performance.tick()
io.open()
io.print(t2-t1)
win.msgbox(
"ok")


小时候,幸福是一件东西,拥有就是幸福; 长大了,幸福是一个目标,达到就是幸福; 成熟后,发现幸福原来是一种心态,领悟就是幸福.
回复

使用道具 举报

12

主题

204

帖子

1251

积分

荣誉会员

Rank: 8Rank: 8

积分
1251
QQ
发表于 2012-7-19 14:17:15 来自手机 | 显示全部楼层

留贴,继续观察高手发言 [size=1][color=#708090]来自:aardio编程论坛 Android客户端[/color][/size]

留贴,继续观察高手发言
来自:aardio编程论坛 Android客户端来自: Android客户端
回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-19 14:43:20 | 显示全部楼层

[quote][size=2][color=#999999]look 发表于 2012-7-19 14:02[/color] [url=forum.php?mo

look 发表于 2012-7-19 14:02
excel 写入速度受限,全部写入空格还要很长时间,数据够多!优化了下节省40秒左右

在look优化代码的基础上,我改为一行数据一个table,然后给excel整行赋值的方式,速度有很大的提升
import win

import com;
import com.excel
import time.ole;
import string
import time.performance
var t1=time.performance.tick()

var connstr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\桌面\Database2.accdb;Persist Security Info=False"

var conn = com.CreateObject("ADODB.Connection");

conn.Open(connstr);
var rs = com.CreateObject("ADODB.RecordSet") ;
rs.Open(
"select * from khzl",conn);
var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)
sheet.Cells.NumberFormatLocal =
"@"

var count=rs.Fields.Count
var row={}
for(j=1;count;1){
     table.push(row,rs.Fields.Item(j-1).Name)
}
sheet.Range(
"A1:C1").value2=row

i=2
while(!rs.eof){
    row={}
   
for(j=1;count;1){
        
var fldvle;
        
if(rs(j-1).value==null){
            table.push(row,
"")
            
continue ;
        }
        
select(rs.Fields.Item(j-1).type) {
            
case 131 {
                fldvle=string.format(
"%.0f",rs(j-1).value);
            }
            
case 7 {
               
var tbl=rs(j-1).value;
                tbl.format =
"%Y/%m/%d %H:%M:%S";
                fldvle=
tostring(tbl);
            }
        }
        table.push(row,fldvle)
    }
    sheet.Range(
"A"++i++":C"+i).value2=row
    i++
    rs.movenext()
     
}

rs.close()
conn.close()

var t2=time.performance.tick()
io.open()
io.print(t2-t1)
win.msgbox(
"ok")

回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-19 15:26:06 | 显示全部楼层

[quote][size=2][color=#999999]lzj_ykds 发表于 2012-7-19 14:43[/color] [url=forum.ph

lzj_ykds 发表于 2012-7-19 14:43
在look优化代码的基础上,我改为一行数据一个table,然后给excel整行赋值的方式,速度有很大的提升
impor ...

考虑可以用2维table来赋值进一步提高速度,因为下面的代码可以执行
import com
import com.excel

var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)

var row={}
var rows={}
table.push(row,1,2,3)
table.push(rows,row)
row={}
table.push(row,3,4,5)
table.push(rows,row)
sheet.Range(
"A1:C2").value2=rows


于是写了如下代码,但是报如下错误,不知有什么好的办法

---------------------------
aardio:运行时错误
---------------------------

文件:[string "import win..."]
错误行号:#60
错误:stack overflow (There was not enough memory to complete the operation)
---------------------------
确定   
---------------------------

代码如下
import win

import com;
import com.excel
import time.ole;
import string
import time.performance
var t1=time.performance.tick()

var connstr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\桌面\Database2.accdb;Persist Security Info=False"

var conn = com.CreateObject("ADODB.Connection");

conn.Open(connstr);
var rs = com.CreateObject("ADODB.RecordSet") ;
rs.Open(
"select * from khzl",conn);
var excel = com.excel( true )
excel.visible=
true
var wkbook=excel.workbooks.add()
var sheet=wkbook.sheets(1)
sheet.Cells.NumberFormatLocal =
"@"

var count=rs.Fields.Count
var row={}
var rows={}
for(j=1;count;1){
     table.push(row,rs.Fields.Item(j-1).Name)
}
table.push(rows,row)

i=2
while(!rs.eof){
    row={}
   
for(j=1;count;1){
        
var fldvle;
        
if(rs(j-1).value==null){
            table.push(row,
"")
            
continue ;
        }
        
select(rs.Fields.Item(j-1).type) {
            
case 131 {
                fldvle=string.format(
"%.0f",rs(j-1).value);
            }
            
case 7 {
               
var tbl=rs(j-1).value;
                tbl.format =
"%Y/%m/%d %H:%M:%S";
                fldvle=
tostring(tbl);
            }
        }
        table.push(row,fldvle)
    }
    table.push(rows,row)
    i++
    rs.movenext()
     
}

rs.close()
conn.close()
sheet.Range(
"A1:C"++(i-1)).value2=rows
var t2=time.performance.tick()
io.open()
io.print(t2-t1)
win.msgbox(
"ok")

回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-19 16:39:24 | 显示全部楼层

[quote][size=2][color=#999999]look 发表于 2012-7-19 16:24[/color] [url=forum.php?mo

look 发表于 2012-7-19 16:24

嗯,对这个例子来说已经相当不错了,

现在的问题是,比如我想做的通用一点,比如字段数可能不止三个,记录条数还可能更多,那么每批次多少条记录合适就不太好确定了。

对这个例子,我也试了几次,大概10000条是比较合适的,因为我测每次 超出12500条,写入excel是就会报内存不足的错误了
回复

使用道具 举报

75

主题

849

帖子

5008

积分

六级会员

The only one

Rank: 9Rank: 9Rank: 9

积分
5008
发表于 2012-7-19 16:44:03 | 显示全部楼层

2000和10000速度差不多!现在已经是通用的了,字段可变,条数可变!

2000和10000速度差不多!现在已经是通用的了,字段可变,条数可变!
小时候,幸福是一件东西,拥有就是幸福; 长大了,幸福是一个目标,达到就是幸福; 成熟后,发现幸福原来是一种心态,领悟就是幸福.
回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-19 17:01:49 | 显示全部楼层

[quote][size=2][color=#999999]look 发表于 2012-7-19 16:44[/color] [url=forum.php?mo

look 发表于 2012-7-19 16:44
2000和10000速度差不多!现在已经是通用的了,字段可变,条数可变!

嗯,先这样吧,你的算最佳答案了,也希望其他人能继续给出不同的答案
回复

使用道具 举报

5

主题

64

帖子

756

积分

荣誉会员

Rank: 8Rank: 8

积分
756
发表于 2012-7-20 05:20:31 | 显示全部楼层

[b][color=#ff0000][font=新宋体]只要1秒钟 [/font][/color][/b][align=left][font=新宋体][col

只要1秒钟
import com.excel
import time.performance;

var t1=time.performance.tick();

var excel = com.excel(true);
var wkbook=excel.workbooks.add();
var sheet=wkbook.sheets(1);
excel.visible =
true;

with (sheet.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database2.accdb;Persist Security Info=False", sheet.Range("$A$1"), "select * from KHZL")){
    refresh(
false);
    delete();
}

sheet.columns(
"A:A").NumberFormatLocal = "0_ ";
sheet.columns(
"B:B").NumberFormatLocal = "yyyy/m/d h:mm:ss;@";
sheet.columns(
"C:C").NumberFormatLocal = "0_ ";

var t2=time.performance.tick();

io.open();
io.print(t2-t1);
execute("pause");
io.close();

点评

真棒: 5.0 同意: 5.0
继续努力: 5.0
同意: 5 真棒: 5 继续努力: 5
记录...  发表于 2016-3-6 22:35
真棒: 5
又学到一招sheet.QueryTables.Add方法。  发表于 2012-7-20 18:05
山外青山天外天,白云深处有神仙。神仙本是凡人做,只怕凡人志不坚。
回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-20 08:11:48 | 显示全部楼层

[quote][size=2][color=#999999]roking 发表于 2012-7-20 05:20[/color] [url=forum.php?

roking 发表于 2012-7-20 05:20
只要1秒钟
import com.excel
import time.performance;

毫无疑问,这个速度是巨快啊,但是有一个问题没有解决,就是第一列,你看原表是精度为16位的小数,小数位为零,这样导出后如果数字长度为16位的,最后一位就变成零了,与原始数据有出入了

点评

嗯!没看源表的内容,已超出Excel的数值精度范围  发表于 2012-7-20 08:56
回复

使用道具 举报

37

主题

184

帖子

1240

积分

四级会员

Rank: 6Rank: 6

积分
1240
 楼主| 发表于 2012-7-20 08:19:13 | 显示全部楼层

[i=s] 本帖最后由 lzj_ykds 于 2012-7-20 08:32 编辑 [/i] [quote][size=2][color=#999999]ro

本帖最后由 lzj_ykds 于 2012-7-20 08:32 编辑
roking 发表于 2012-7-20 05:20
只要1秒钟
import com.excel
import time.performance;

精度的办法有了,可以在写查询语句时对这个字段进行转换,先转成字符串,这个问题解决了,可以说这个是最优的方案了,不知是不是还有更好的,哈哈
语句写成这样
select str(user_id),open_date,str(user_id_kd) from KHZL

完整源码如下

import com.excel
import time.performance;

var t1=time.performance.tick();

var excel = com.excel(true);
var wkbook=excel.workbooks.add();
var sheet=wkbook.sheets(1);
excel.visible =
true;

sheet.columns("B:B").NumberFormatLocal = "yyyy/m/d h:mm:ss;@";

with (sheet.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\桌面\Database2.accdb;Persist Security Info=False", sheet.Range("$A$1"), "select str(user_id),open_date,str(user_id_kd) from KHZL")){
    refresh(
false);
    delete();
}


var t2=time.performance.tick();

io.open();
io.print(t2-t1);
execute("pause");
io.close();


roking这个方法实在是太快了,消化一下
回复

使用道具 举报

4

主题

874

帖子

4704

积分

荣誉会员

Rank: 8Rank: 8

积分
4704
发表于 2012-7-20 09:05:21 | 显示全部楼层

强,论坛的牛人还是不少的

强,论坛的牛人还是不少的
回复

使用道具 举报

12

主题

204

帖子

1251

积分

荣誉会员

Rank: 8Rank: 8

积分
1251
QQ
发表于 2012-7-22 00:27:46 来自手机 | 显示全部楼层

回来观望,看来果然有强淫现身啊!mark! [size=1][color=#708090]来自:aardio编程论坛 Android客户端[/color][/s

回来观望,看来果然有强淫现身啊!mark!
来自:aardio编程论坛 Android客户端来自: Android客户端
回复

使用道具 举报

42

主题

125

帖子

900

积分

三级会员

Rank: 4

积分
900
发表于 2012-7-22 08:55:21 | 显示全部楼层

受教了,刚好用的着,谢啦

受教了,刚好用的着,谢啦
回复

使用道具 举报

52

主题

1340

帖子

7326

积分

荣誉会员

Rank: 8Rank: 8

积分
7326
发表于 2012-7-22 23:57:49 | 显示全部楼层

mark 学习

mark 学习
回复

使用道具 举报

0

主题

14

帖子

157

积分

一级会员

Rank: 2

积分
157
发表于 2012-7-23 23:03:23 | 显示全部楼层

谢谢分享了啊,辛苦了啊

谢谢分享了啊,辛苦了啊
回复

使用道具 举报

0

主题

4

帖子

69

积分

一级会员

Rank: 2

积分
69
发表于 2012-7-26 12:45:02 | 显示全部楼层

学习中,有机会实践一下

学习中,有机会实践一下
回复

使用道具 举报

1

主题

51

帖子

636

积分

三级会员

Rank: 4

积分
636
发表于 2013-1-15 22:39:14 | 显示全部楼层

好像可以用 sheet.range("a1").CopyFromRecordset rs 直接导出到excel中

好像可以用
sheet.range("a1").CopyFromRecordset rs
直接导出到excel中
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册会员

本版积分规则

手机版|未经许可严禁引用或转载本站文章|站长邮箱|aardio.com|aardio官方社区 ( 皖ICP备09012014号 )

GMT+8, 2018-12-11 19:34 , Processed in 0.093761 second(s), 24 queries .

Powered by Discuz! X3.4

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表