datawindow导出excel函数_good

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//functionname: f_export_excel_01
// 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

// modify: chengang@dongguan
// date : 2008-04-05
// 返回类型改为int
// 四个"////"注释的地方为我修改之处。
// note :只针对grid风格导出合适
//可以在导出时给用户选择是用本函数还是Dw2xls导出，这个函数非常快，因为不用Ole.而dw2xls导出效果好。但数据上千行后速度难于忍受。
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

open(w_export_excel_progress) //进度指示窗口。popup样式。
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
int li_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
li_return = 0
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') //取得column title
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(12) dbname="' + ls_obj + '" )' + '~r~n' ////这里很重要char(12)决定输出的栏位宽度。原程序用char(1),效果太差.
next
ls_sql = 'release ' + ls_pbver + ';~r~ntable(' + ls_sql + ')'
lds_saveas.create(ls_sql, ls_err)
if len(ls_err) > 0 then
li_return = 0
goto lab1
end if

string ls_type,ls_style,ls_checkbox_on

//向 lds_saveas 中写数据
w_export_excel_progress.trigger event ue_set_maxpos(ad_dw.rowcount())
for i = 1 to ad_dw.rowcount()
yield() //释放消息队列, 如果数据量较大, 可以使用这个函数
w_export_excel_progress.trigger event ue_change_pos(i)
w_export_excel_progress.trigger event ue_show_info("写入数据到临时区,进度: " + string(i) + "行!")
lds_saveas.insertrow(0)
for j = 1 to lds_sort.rowcount()
ls_obj = lds_sort.getitemstring(j, 'zcol')

////因为chckebox风格的显示下,checkbox.text通常留空,所以必须取得checkbox.on和数据库原值进行比较，相等为checked.
ls_type =string(ad_dw.describe(ls_obj + '.type'))
ls_style = string(ad_dw.describe(ls_obj + '.edit.style'))
ls_checkbox_on = string(ad_dw.describe(ls_obj + '.checkbox.on'))

if ls_type = 'column' and ls_style <> 'checkbox' then
ls_text = ad_dw.describe('evaluate(~'LookUpDisplay(' + ls_obj + ')~', ' + string(i) + ')')
elseif ls_type = 'column' and ls_style = 'checkbox' then ////对checkbox风格的特殊处理
ls_text = ad_dw.describe('evaluate(~'' + ls_obj + '~',' + string(i) + ')')
if ls_text = ls_checkbox_on then
ls_text = "√" ////可以根据需要修改，比如"是","否"或者"选择","非选择"
else
ls_text = "×"
end if
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) //col_title插入第一行
for i = 1 to lds_sort.rowcount()
lds_saveas.setitem(1, i, lds_sort.getitemstring(i, 'ztext'))
next
//准备数据完毕

====================================================

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

lab1:
destroy lds_sort
destroy lds_saveas
SetPointer(Arrow!)
close(w_export_excel_progress)
return li_return