在PB中导出Excel表

原创

wglzaj 2012-02-24 14:59:49 博主文章分类：开发技术-PB ©著作权

文章标签 method 表 export 都 writing 文章分类 办公效率

©著作权归作者所有：来自51CTO博客作者wglzaj的原创作品，请联系作者获取转载授权，否则将追究法律责任

关于在PB中导出Excel表的问题应该是个老问题了，大家讨论了不知道有多少年了，今天我也遇到了这个问题，上网找了半天大家说什么的都有。总结了一下，把我看到的都贴出来，呵呵。
一.
// ... Init docname
// ... GetFileOpenName or any other method
if dw_1.SaveAs(docname, HTMLTable!, True) = -1 then
MessageBox("Warning", "Unable to export data. Error writing to file!", Exclamation!)
return
end if
// Convert HTML file to Excel native format
OLEObject excel
excel = CREATE OLEObject
if excel.ConnectToObject(docname) = 0 then
excel.application.DisplayAlerts = FALSE
excel.application.workbooks(1).Parent.Windows(excel.application.workbooks(1).Name).Visible = True
excel.application.workbooks(1).saveas(docname, 39)
excel.application.workbooks(1).close()
end if
DESTROY excel
// done
二.
支持导出分组带、合计带，并且支持多层嵌套报表导出,基本是所见及所得。
///////////////////////////////////////////////////////////////////////////
//
// 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
支持导出分组带、合计带，并且支持多层嵌套报表导出,基本是所见及所得。
///////////////////////////////////////////////////////////////////////////
//
// 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!)

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

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

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