注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

欢迎光临我的博客

 
 
 

日志

 
 

powerbuilder dw数据到出excel  

2018-04-17 19:57:20|  分类: powerbuilder |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
1

收藏的一个导出为 excel 的例子  
(支持导出分组带、合计带,并且支持多层嵌套报表导出,基本是所见及所得)
///////////////////////////////////////////////////////////////////////////
//
// Parameters : ad_dw   : datawindow
//      as_file  : file name
// Returns  : true/false : boolean
// Description : Save the datawindow as a excel file.
//
///////////////////////////////////////////////////////////////////////////
// author  : purplekite
// date  : 2003-01-23
///////////////////////////////////////////////////////////////////////////

SetPointer(HourGlass!)

//declare the local variables
long    i, j, li_pos
string   ls_objects, ls_obj, ls_text, ls_err, ls_sql
datastore  lds_saveas //导出数据窗
datastore  lds_sort  //获得根据 object.x 排序的 (band = detail and visible = 1) 的 column/compute
boolean   lb_return //返回值
string   ls_pbver  //pb 版本信息
environment  env   //环境变量

getenvironment(env)
ls_pbver = string(env.pbmajorrevision)

//创建排序列 datastore
lds_sort = create datastore
ls_sql = 'column=(type=char(1) name = ztext dbname="ztext" )' + '~r~n' + &
   'column=(type=char(1) name = zcol dbname="zcol" )' + '~r~n' + &
   'column=(type=long name = zx dbname="zx" )' + '~r~n'
ls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'
lds_sort.create(ls_sql, ls_err)
if len(ls_err) > 0 then
 lb_return = false
 goto lab1
end if

//准备数据====================================================
//all controls
ls_objects = ad_dw.Describe("datawindow.objects")

//按~t位置作判断开始循环
do while (pos(ls_objects,"~t") > 0)
 li_pos = pos(ls_objects,"~t")
 ls_obj = left(ls_objects,li_pos - 1)
 ls_objects = right(ls_objects,len(ls_objects) - li_pos)
 //(column or compute ) at detail and visible
 IF (ad_dw.Describe(ls_obj+".type") = "column" or &
   ad_dw.Describe(ls_obj+".type") = "compute" ) AND &
   (ad_dw.Describe(ls_obj+".band") = "detail" ) AND &
   (ad_dw.Describe(ls_obj+".visible") = "1" ) THEN
  ls_text = ad_dw.describe(ls_obj + '_t.text')
  if ls_text <> '!' and ls_text <> '?' then
   lds_sort.insertrow(0)
   lds_sort.setitem(lds_sort.rowcount(), 'ztext', ls_text)
   lds_sort.setitem(lds_sort.rowcount(), 'zcol', ls_obj)
   lds_sort.setitem(lds_sort.rowcount(), 'zx', long(ad_dw.describe(ls_obj + '.x')))
  end if
 END IF
loop

//the last control
ls_obj = ls_objects
IF (ad_dw.Describe(ls_obj+".type") = "column" or &
   ad_dw.Describe(ls_obj+".type") = "compute" ) AND &
   (ad_dw.Describe(ls_obj+".band") = "detail" ) AND &
   (ad_dw.Describe(ls_obj+".visible") = "1" ) THEN
  ls_text = ad_dw.describe(ls_obj + '_t.text')
  if ls_text <> '!' and ls_text <> '?' then
   lds_sort.insertrow(0)
   lds_sort.setitem(lds_sort.rowcount(), 'ztext', ls_text)
   lds_sort.setitem(lds_sort.rowcount(), 'zcol', ls_obj)
   lds_sort.setitem(lds_sort.rowcount(), 'zx', long(ad_dw.describe(ls_obj + '.x')))
  end if
END IF
//如果没有列则跳出
if lds_sort.rowcount() < 1 then goto lab1
//根据 object.x 排序
lds_sort.setsort('zx A')
lds_sort.sort()

//创建导出 datastore
lds_saveas = create datastore
ls_sql = ''
for i = 1 to lds_sort.rowcount()
 ls_obj = lds_sort.getitemstring(i, 'zcol')
 ls_sql += 'column=(type=char(1) dbname="' + ls_obj + '" )' + '~r~n'
next
ls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'
lds_saveas.create(ls_sql, ls_err)
if len(ls_err) > 0 then
 lb_return = false
 goto lab1
end if

//向 lds_saveas 中写数据
for i = 1 to ad_dw.rowcount()
 yield()//释放消息队列, 如果数据量较大, 可以使用这个函数
 lds_saveas.insertrow(0)
 for j = 1 to lds_sort.rowcount()
  ls_obj = lds_sort.getitemstring(j, 'zcol')
  if ad_dw.describe(ls_obj + '.type') = 'column' then
   ls_text = ad_dw.describe('evaluate(~'LookUpDisplay(' + ls_obj + ')~', ' + string(i) + ')')
  else
   ls_text = ad_dw.describe('evaluate(~'' + ls_obj + '~',' + string(i) + ')')
  end if
  lds_saveas.setitem(i, j, ls_text)
 next
next

lds_saveas.insertrow(1)
for i = 1 to lds_sort.rowcount()
 lds_saveas.setitem(1, i, lds_sort.getitemstring(i, 'ztext'))
next
//准备数据完毕====================================================

//saveas datawindow
lb_return = (lds_saveas.saveas(as_file, excel!, false) = 1)

lab1:
destroy lds_sort
destroy lds_saveas
SetPointer(Arrow!)
return lb_return

2--------------------

//函数说明:
//用于报表导出功能,主要实现DATAWINDOW导出到EXCLE中,原DATAWINDOW表头的处理问题
//几个问题:表头太复杂,导出后与下面的数据不对应!

int   li_value                                                                                            
string  ls_path,ls_fname                                                                                  
li_value = GetFilesaveName("请选择导出文件", &                                                            
  + ls_path, ls_fname, "XLS", &                                                                           
  + "Excel文件 (*.xls), *.xls," &                                                                         
  + "Word 文件 (*.doc), *.doc,"&                                                                          
  + "文本 文件 (*.txt), *.txt,")                                                                          
IF li_value <> 1 THEN return 0                                                                            
setpointer(hourglass!)                                                                                    
// 删除原文件                                                                                             
if fileexists(ls_path) then                                                                               
 if messagebox('提示信息', '原文件已经存在, 是否覆盖 ?', Question!, YesNo!) = 2 then return 0             
 if not filedelete(ls_path) then                                                                          
  messagebox('提示信息', '删除原文件失败, 该文件可能正在被使用 !')                                        
  return 0                                                                                                
 end if                                                                                                   
end if                                                                                                    
if dw_1.SaveAsascii(ls_path)  =  -1  then                                                                 
 MessageBox("提示信息", "导出数据出错. 不能写入文件 !", Exclamation!)                                     
 return 0                                                                                                 
else                                                                                                      
 messagebox('提示信息','数据导出成功 !', Exclamation!)                                                    
return 1                                                                                                  
end if                                                 

/**********以下程序将导出的EXCEL英文标题替换为汉字*********/
long numcols,numrows,c,r
OLEObject xlapp,xlsub
int ret
numcols=long(dw_1.Object.DataWindow.Column.Count)
numrows=dw_1.RowCount()
//产生oleobject的实例
xlApp=Create OLEObject
//连接ole对象
ret=xlApp.ConnectToNewObject("Excel.Sheet")
if ret<0 then
MessageBox("连接失败!","连接到EXCEL失败,请确认您的系统是否已经安装EXCEL!~r~n"&
+"错误代码:"+string(ret))
return -1
end if
//打开EXCEL文件
xlApp.Application.Workbooks.Open(ls_path)
////使文件可见
//xlApp.Application.Visible=true
//得到活动工作表的引用,改善程序性能
xlsub=xlapp.Application.ActiveWorkbook.Worksheets[1]
string ls_colname,ls_text,ls_modistr,ls_col
//取字段名更改为对应的文本text值
FOR c=1 to numcols
ls_col="#"+string(c)+".name"
ls_colname=dw_1.describe(ls_col)
ls_modistr=ls_colname+"_t.text"
ls_text=dw_1.describe(ls_modistr)
xlsub.cells[1,c]=ls_text
NEXT

xlApp.DisConnectObject()
Destroy xlapp
MessageBox("提示信息","导出数据成功!")
return 1//success

这个函数基本还可以,请大家帮忙看下,优化一下,一个主要问题是导出来如果是字符型的,但到EXCEL里自动转为数值型,大家帮忙看看,还有就是表格复杂时,效果不好,看能不能再优化一下

3--------------------

integer li_rtn,ii,li_asc 

string ls_filename

string ls_pathname

string ls_text

string ls_a

//ls_pathname=string(today(),'yyyy-mm-dd')  

ls_pathname=string('中心')  

boolean lb_exist 

li_rtn=GetFileSaveName("保存文件",ls_pathname,ls_filename,"xls","Excel 文件 (*.xls),*.xls") 

if li_rtn=1 then 

lb_exist = FileExists(ls_pathname) 

IF lb_exist THEN 

li_rtn = MessageBox("保存", ls_pathname+"已经存在,是否覆盖",Exclamation!, YesNo!) 

end if 

if li_rtn=1 then //当文件存在用户选择覆盖,或是文件本就不存在 

li_rtn=w_zhongxin.dw_1.SaveAsAscii(ls_pathname) 

if li_rtn=1 then 

MessageBox("提示信息","导出数据成功!") 

else 

MessageBox("错误信息","导出数据失败!") 

end if 

else 

end if 

else 

end if 

4-------------

OLEObject ole_object , ole_workbooks


ole_object = CREATE OLEObject

IF ole_object.ConnectToNewObject("Excel.Application") <> 0 THEN

MessageBox('OLE错误','OLE无法连接!')

return

END IF

ole_object.workbooks.add

ole_object.Visible = True

ole_workbooks = ole_object.Worksheets(1)

ole_workbooks.cells(1,1).value="中心编号"

ole_workbooks.cells(1,2).value="中心名称"





long l_row

for l_row = 1 to dw_1.rowcount()

ole_workbooks.cells(l_row,1).value=dw_1.getitemstring(l_row,1)

ole_workbooks.cells(l_row,2).value=dw_1.getitemstring(l_row,2)

next

ole_workbooks.SaveAs ("d:\中心.xls")

ole_object.quit()

Ole_Object.DisConnectObject()

Destroy Ole_Object

destroy ole_workbooks

MessageBox('提示','OLE导出d:\中心.xls成功!')

  评论这张
 
阅读(32)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018