Powerbuilder与嵌入Excel工作表交互百万数据记录(43619条26列)心得---用时30秒左右

     来到新公司后,发现报表管理确实是个问题!

     以前公司的核心控件Excelpanel.ocx是原技术总监的心血!所以想办法自己在网上找免费控件解决这个问题!

     竟然意外发现微软提供的dsoframer.ocx控件!

     于是开始自己的思路,pb准备好动态二维数组,一次性交互给Excel,填充Range!

     步骤如下:

     1.注册控件,安装dsoframer自安装包,或者下载控件注册(regsvr32 //路径/dsoframer.ocx)

     2.PB窗口W_ocx 嵌入Ole控件 dsoframer

     3.建立一个按钮并输入以下代码:

     dsoframer实现:

     （dsoframer实现的缺点是PB创建静态数组,动态数组未解决,必须解决的问题！速度为35秒）

    
OleObject dsoframer,excel
dsoframer = ole_2.object
dsoframer.open("c:/1.xls",false,"Excel.Sheet")

excel = dsoframer.activedocument.application

//事务对象
transaction trans
trans = create transaction
trans.DBMS = "O90 Oracle9i (9.0.1)"
trans.LogPass = "zhhh"
trans.ServerName = "canppe2"
trans.LogId = "system"
trans.AutoCommit = False
trans.DBParm = ""

connect using trans;

If  trans.sqlcode <> 0 Then api.alert( "数据库没有正确连接,请检查!" )

String ls_sql
DataStore lds_data
ls_sql = "SELECT ID FROM rm.mst_matecode"
lds_data = api.executesqlwithresult( ls_sql,trans ) //数据存储
ltutc_time.start()
string ls_id  //唯一ID

String  larr_temp[43619,26]  
long li_i
long li_j
If lds_data.rowcount( ) > 0 Then
    For li_i = 1 to lds_data.rowcount()
        ls_id = lds_data.getitemstring( li_i,"ID" )
        If li_i = 27 Then ls_sql = "1"
      For li_j = 1 to 26
            larr_temp[ li_i,li_j ] = ls_id
        Next
    Next
End If
OleObject nrange
nrange = excel.sheets(1).range("A1:Y43619")
nrange.value2 =  larr_temp
ltutc_time.finish()
api.alert( ltutc_time.wastetime( true ) )

 

//其中API,是tool.pbl(公司封装,Excelpanelｃｏｎｔｒｏｌ.ocx实现同样的操作中１分３０秒左右，多了３倍左右的时间，优点是有动态数组)

Excelpanelcontrol.ocx实现

(自己记忆的代码,恕不开放ocx)

 // PB中怎样把数据写到已经固定格式的EXCEL表中

server = ole_1.object
server.filename = "新建"  //新建EXCEL对象

OleObject excel
excel = server.excel


//事务对象
transaction trans
trans = create transaction
trans.DBMS = "O90 Oracle9i (9.0.1)"
trans.LogPass = "zhhh"
trans.ServerName = "canppe2"
trans.LogId = "system"
trans.AutoCommit = False
trans.DBParm = ""

connect using trans;

If  trans.sqlcode <> 0 Then api.alert( "数据库没有正确连接,请检查!" )

String ls_sql
DataStore lds_data
ls_sql = "SELECT ID FROM rm.mst_matecode"
lds_data = api.executesqlwithresult( ls_sql,trans ) //数据存储

string ls_id  //唯一ID
String ls_rqfw //日期范围
String ls_addname //录入人
String ls_createwho //创建人
//转化为二维数组
//计算时间
ltutc_time.start()
Integer li_array
OleObject nRange

Long li_i
Integer li_j

nRange = excel.range( "A1:Y43619" )
li_array = server.createdynamicarrayfromexcelrange( nrange,true )
If lds_data.rowcount( ) > 0 Then
    For li_i = 1 to lds_data.rowcount()
        ls_id = lds_data.getitemstring( li_i,"ID" )

      For li_j = 1 to 26
            server.setdynamicarray( li_array,ls_id,li_i,li_j)
       Next
    Next
End If

server.putdynamicarraytoexcelrange( li_array,nrange )
ltutc_time.finish()
api.alert( ltutc_time.wastetime( true ) )