PB如何将数据窗口的数据导出到excel

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)

return lb_return

lab1:

destroy lds_sort

destroy lds_saveas

setpointer(arrow!)

return lb_return

string ls_docname, ls_named 

integer li_ret ,value

boolean lb_exist ,lb_save

value=GetFileSaveName("请选择文件", ls_docname, ls_named, "pbl", "xls Files (*.xls),*.xls") 

IF value = 1 THEN

  lb_exist = FileExists(ls_docname) 

  IF lb_exist THEN 

 li_ret = MessageBox("提示：", " 文件已存在，是否覆盖? " + ls_docname, Question!, YesNo!) 

     if li_ret=2 then

       return

     end if

  end if

  lb_save=toexcel(dw_1,ls_docname)

  

  IF value = 1 THEN

     messagebox("提示","文件导出成功!")

  else

     messagebox("提示","文件导出失败!")

  end if

else

  messagebox("提示","创建选择文件失败!")

end if

//dw_1.retrieve()