PB中数据窗口导出数据到EXCEL的程序（4）

（支持导出分组带、合计带，并且支持多层嵌套报表导出,基本是所见及所得） /// // //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 variableslongi, j, li_posstringls_objects, ls_obj, ls_text, ls_err, ls_sqldatastorelds_saveas//导出数据窗datastorelds_sort//获得根据 object.x 排序的 (band = detail and visible = 1) 的 column/computebooleanlb_return//返回值stringls_pbver//pb 版本信息environment env//环境变量

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

//创建排序列 datastorelds_sort = create datastorels_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 thenlb_return = falsegoto lab1end if

//准备数据====================================================//all controlsls_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 visibleIF (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" ) THENls_text = ad_dw.describe(ls_obj + '_t.text')if ls_text <> '!' and ls_text <> '?' thenlds_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 ifEND IFloop

//the last controlls_obj = ls_objectsIF (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" ) THENls_text = ad_dw.describe(ls_obj + '_t.text')if ls_text <> '!' and ls_text <> '?' thenlds_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 ifEND IF//如果没有列则跳出if lds_sort.rowcount() < 1 then goto lab1//根据 object.x 排序lds_sort.setsort('zx A')lds_sort.sort()

//创建导出 datastorelds_saveas = create datastorels_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'nextls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'lds_saveas.create(ls_sql, ls_err)if len(ls_err) > 0 thenlb_return = falsegoto lab1end 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' thenls_text = ad_dw.describe('evaluate(~'LookUpDisplay(' + ls_obj + ')~', ' + string(i) + ')')elsels_text = ad_dw.describe('evaluate(~'' + ls_obj + '~',' + string(i) + ')')end iflds_saveas.setitem(i, j, ls_text)nextnext

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

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

lab1:destroy lds_sortdestroy lds_saveasSetPointer(Arrow!)return lb_return