数据导出到excel代码求优化

lzj_ykds · 发表于 2012-7-19 12:42:10

本帖最后由 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()
import console
console.log(t2-t1)
win.msgbox("ok")

look · 发表于 2012-7-19 12:42:11

原来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()
import console
console.log(t2-t1)
win.msgbox("ok")

look · 发表于 2012-7-19 14:02:27

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()
import console
console.log(t2-t1)
win.msgbox("ok")

cjc0045 · 发表于 2012-7-19 14:17:15

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

lzj_ykds · 发表于 2012-7-19 14:43:20

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()
import console
console.log(t2-t1)
win.msgbox("ok")

lzj_ykds · 发表于 2012-7-19 15:26:06

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()
import console
console.log(t2-t1)
win.msgbox("ok")

lzj_ykds · 发表于 2012-7-19 16:39:24

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

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

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

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

look · 发表于 2012-7-19 16:44:03

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

lzj_ykds · 发表于 2012-7-19 17:01:49

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

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

roking · 发表于 2012-7-20 05:20:31

只要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();

import console;
console.log(t2-t1);
execute("pause");
;

lzj_ykds · 发表于 2012-7-20 08:11:48

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

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

lzj_ykds · 发表于 2012-7-20 08:19:13

本帖最后由 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();

import console;
console.log(t2-t1);
execute("pause");
;

roking这个方法实在是太快了，消化一下

不争 · 发表于 2012-7-20 09:05:21

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

cjc0045 · 发表于 2012-7-22 00:27:46

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

zsbad123 · 发表于 2012-7-22 08:55:21

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

qqmmcc · 发表于 2012-7-22 23:57:49

mark 学习

sj123 · 发表于 2012-7-23 23:03:23

谢谢分享了啊，辛苦了啊

zhlzhang · 发表于 2012-7-26 12:45:02

学习中，有机会实践一下

Kevinch · 发表于 2013-1-15 22:39:14

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

用户名		自动登录	找回密码
密码			注册会员

数据导出到excel代码求优化

最佳答案

点评

点评

点评

数据导出到excel代码 求优化

最佳答案

点评

点评

点评

数据导出到excel代码求优化