PB数据窗口导出excel，修改第一行英文标题为中文标题，计算合计值

1.PB数据窗口导出excel

利用dw.saveas(filename,Excel5!,true)方法先导出带英文标题的excel。

int li_ret
string ls_filename,ls_pathname
boolean lb_exist

if dw.rowcount()=0 then
messagebox("提示","无可保存的数据！")
return;
end if

li_ret = GetFileSaveName("保存文件",ls_pathname,ls_filename,"xls","Excel文件(*.xls),*.xls")
if li_ret=1 then
lb_exist = FileExists(ls_pathname)
IF lb_exist THEN
li_ret = MessageBox("保存", ls_pathname+"已经存在,是否覆盖?",Exclamation!, YesNo!)
end if
if li_ret=1 then
  li_ret = dw.saveas(ls_pathname,EXCEL5!, true)//此处必须为true，后面要修改第一行。
  if li_ret=1 then
   //修改标题头，计算合计
   gf_set_title_sum(dw,ls_pathname)
   messagebox('提示','保存成功！')
  end if
end if
end if

2.修改第一行英文标题为中文标题，计算合计值

gf_set_title_sum(datawindow dw,string vs_filename)函数如下：

int li_ret
long ll_column,ll_num,ll_dwrowcount
long ll_xlsrowcount//excel的行数
string ls_objtag[]//标题
string ls_width[]//每列宽度
string ls_obj,ls_value
string ls_objs[]
int i,j
String ls_english = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
string ls_colname
//操作excel
Pointer oldpointer
OLEObject ole_object
ole_object = CREATE OLEObject
//修改标题头，计算合计
li_ret = ole_object.ConnectToNewObject("Excel.Application")
IF li_ret <> 0 THEN
MessageBox('打开错误','无法连接EXCEL!是否已经安装了EXCEL?错误号：' + String(li_ret))
RETURN
END IF
ole_object.Visible = false //ole应用服务是否显示
oldpointer = SetPointer(HourGlass!)
ole_object.application.workbooks.open(vs_filename)//打开文件

//获取标题
ll_column = Long(dw.Object.DataWindow.Column.Count) //取得字段总数
i = 0
FOR ll_num = 1 TO ll_column
IF dw.DESCRIBE("#"+String(ll_num)+".Visible") = "1" THEN //列标志为nun_Visible的不显示
i = i+1
ls_obj = dw.DESCRIBE("#"+String(ll_num)+".name") //字段名称的实际存储值
ls_objs[i] = ls_obj
ls_objtag[i] = dw.DESCRIBE(ls_obj + "_t.text") //字段名称的显示值
ls_width[i] = dw.DESCRIBE(ls_obj + '.width') //每列的宽度
END IF
NEXT

//开始修改标题头
for j=1 to upperbound(ls_objtag)
ole_object.Cells(1,j).value=ls_objtag[j]
next
//标题一行居中
ole_object.rows(1).HorizontalAlignment = 3//居中
//计算合计值
ll_dwrowcount=dw.rowcount()//行数
ll_xlsrowcount=ll_dwrowcount+1//除了合计之外的行数
ole_object.cells(ll_xlsrowcount+1,1).value='合计'
for j=2 to ll_column
ls_colname=Mid(ls_english,j,1)//获取列名B
ole_object.Range(ls_colname+'2:'+ls_colname+string(ll_xlsrowcount)).Select//选中B2:B4
ole_object.Range(ls_colname+string(ll_xlsrowcount+1)).Activate//将B5设为活动单元格
ole_object.ActiveCell.FormulaR1C1 = "=SUM(R[-"+string((ll_xlsrowcount+1)-2)+"]C:R[-1]C)"//计算从第二行到倒数第二行的和值
next
ole_object.application.workbooks(1).worksheets(1).cells.EntireColumn.AutoFit //自适应列宽，行的话换EntireRow
ole_object.application.workbooks(1).save()//保存
ole_object.application.quit()//退出
ole_object.disconnectobject()//断开连接
destroy ole_object//销毁连接