这个前面很多前辈说过的方法综合一下就可以实现了,不用模板也可以
关键的分组报表部分
我建立了一个结构,主要包含列名,分组列名,总求何列名
利用自定义函数使它们三个对应起来
在忘EXCEL单元里写数据时,对于某一列,
for k = 1 to rds[j].rowcount()
ls_express=rds[j].describe(ls_celname+'.visible')
if k=1 then
ole.ActiveSheet.cells.item(long(ls_row)+ k - 1,ll_cell).value = ls_value
else
if k=rds[j].findgroupchange(k,1) then
//ole.ActiveSheet.cells[long(ls_row)+k,ll_cell].EntireRow.Insert
//写入汇总信息
if ll_cell=1 then
ole.ActiveSheet.cells.item(long(ls_row)+ k - 1+li_xiaoji ,ll_cell).value = '小计:'
else
ole.ActiveSheet.cells.item(long(ls_row)+ k - 1+li_xiaoji ,ll_cell).value = ''
end if
for li_si = 1 to upperbound(groupfield[])
if groupfield[li_si].dwindex=j then
if groupfield[li_si].fields=ls_celname then
ls_trailer=groupfield[li_si].computes
ls_v=rds[j].Describe("Evaluate('" + ls_trailer + '.expression' + "',"+string(k -1)+")")
if isnull(ls_v) then ls_v = ''
ole.ActiveSheet.cells.item(long(ls_row)+ k - 1+li_xiaoji ,ll_cell).value =ls_v
end if
end if
next
//写入当前分组第一行的数据
ole.ActiveSheet.cells.item(long(ls_row)+ k -1+li_xiaoji+1 ,ll_cell).value = ls_value
li_xiaoji++//***********
else//不是分组第一行
ole.ActiveSheet.cells.item(long(ls_row)+ k -1+li_xiaoji ,ll_cell).value = ls_value
PowerBulider数据窗口转MicroSoft Execl、Word程序源代码
一、f_cncharnum函数 f_cncharnum.srf
$PBExportHeader$f_cncharnum.srf
$PBExportComments$得到字符串中汉字或者双字节的个数
global type f_cncharnum from function_object
end type
forward prototypes
global function integer f_cncharnum (string aString)
end prototypes
li_ret = ole_object.ConnectToObject("","Excel.Application")
IF li_ret <> 0 THEN
//如果Excel还没有打开,则新建。
li_ret = ole_object.ConnectToNewObject("Excel.Application")
if li_ret <> 0 then
MessageBox('OLE错误','OLE无法连接!错误号:' + string(li_ret))
return 0
end if
ole_object.Visible = True
END IF
pointer oldpointer
oldpointer = SetPointer(HourGlass!)
ole_object.Workbooks.Add
long ll_colnum,ll_rownum
string ls_value
string ls_objects,ls_obj,ls_objs[],ls_objtag[]
long ll_pos,ll_len,ll_num = 0
string ls_colname
integer i,j,k
for i = 1 to ll_colnum
//得到标题头的名字
ls_value = ls_objtag[i]
ole_object.cells(1,i).value = ls_value
next
string column_name
for i = 2 to ll_rownum
for j = 1 to ll_colnum
column_name = ls_objs[j]
if adw.Describe(column_name + '.type') = 'column' then
ls_value = adw.Describe("Evaluate('LookupDisplay("+column_name+")',"+string(i - 1)+")")
end if
if adw.Describe(column_name + '.type') = 'compute' then
ls_value = adw.Describe("Evaluate('" + adw.Describe(column_name + '.expression') + "',"+string(i - 1)+")")
end if
ole_object.cells(i,j).value = ls_value
next
next
SetPointer(oldpointer)
ole_object.disconnectobject()
DESTROY ole_object
return 1
end function
三、PBToWord函数f_outputtoword_new.srf
$PBExportHeader$f_outputtoword_new.srf
global type f_outputtoword_new from function_object
end type
forward prototypes
global function integer f_outputtoword_new (datawindow adw)
end prototypes
li_ret = ole_object.ConnectToObject("","word.application")
IF li_ret <> 0 THEN
//如果Word还没有打开,则新建。
li_ret = ole_object.ConnectToNewObject("word.application")
if li_ret <> 0 then
MessageBox('OLE错误','OLE无法连接!错误号:' + string(li_ret))
return 0
end if
ole_object.Visible = True
END IF
long ll_colnum,ll_rownum
constant long wdWord9TableBehavior = 1
constant long wdAutoFitFixed = 0
constant long wdCell = 12
string ls_value
pointer oldpointer
oldpointer = SetPointer(HourGlass!)
string ls_objects,ls_obj,ls_objs[],ls_objtag[]
long ll_pos,ll_len,ll_num = 0
for i = 1 to ll_colnum
//得到标题头的名字
ls_value = ls_objtag[i]
ole_object.Selection.TypeText(ls_value)
for k = 1 to f_cncharnum(ls_value)
ole_object.Selection.TypeBackspace()
next
ole_object.Selection.MoveRight(wdCell)
next
adw.setredraw(false)
ole_object.Selection.MoveLeft(wdCell)
string column_name
for i = 2 to ll_rownum
for j = 1 to ll_colnum
column_name = ls_objs[j]
if adw.Describe(column_name + '.type') = 'column' then
ls_value = adw.Describe("Evaluate('LookupDisplay("+column_name+")',"+string(i - 1)+")")
end if
if adw.Describe(column_name + '.type') = 'compute' then
ls_value = adw.Describe("Evaluate('" + adw.Describe(column_name + '.expression') + "',"+string(i - 1)+")")
end if
ole_object.Selection.MoveRight(wdCell)
ole_object.Selection.TypeText(ls_value)
for k = 1 to f_cncharnum(ls_value)
ole_object.Selection.TypeBackspace()
next
next
next
adw.setredraw(true)
constant long wdFormatDocument = 0
SetPointer(oldpointer)
//保存新建的文档
if messagebox("保存","文档已经成功完成,是否保存?",Question!,YesNo!) = 1 then
string docname, named
integer value
value = GetFileSaveName("选择文件",docname, named, "DOC","Doc Files (*.DOC), *.DOC")
IF value = 1 THEN
ole_object.ActiveDocument.SaveAs(docname, 0,False,"",True,"",False,False,False, False,False)
end if
end if
//断开OLE连接
Ole_Object.DisConnectObject()
Destroy Ole_Object