来到新公司后,发现报表管理确实是个问题!
以前公司的核心控件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(公司封装,Excelpanelcontrol.ocx实现同样的操作中1分30秒左右,多了3倍左右的时间,优点是有动态数组)
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 ) )