将DW数据窗口导出为EXCEL文件的方法(整理)

发表于:2007-05-25来源:作者:点击数: 标签:
本文为摘自CSDN论坛帖子收集整理后汇总版本: ---2004年9月3日整理 ---原贴见: http://community.csdn.net/Expert/topic/3328/3328715.xml?temp=8.050799E-04 鉴于现在很多朋友询问 pb 数据导出到excel 中的问题,导出去后格式和数据 类型不对了,自己写了

本文为摘自CSDN论坛帖子收集整理后汇总版本:

---2004年9月3日整理

---原贴见:

http://community.csdn.net/Expert/topic/3328/3328715.xml?temp=8.050799E-04

鉴于现在很多朋友询问 pb 数据导出到excel 中的问题,导出去后格式和数据
类型不对了,自己写了几个用户对象,
希望能抛砖引玉,加强技术交流,得到大家的支持。

1. nvo_excel 只要是两个接口函数
导出数据存储的数据,可以定义 excel 的标题
public function integer uf_toexcel (datastore ads_data, readonly string as_title)

导出数据窗口的数据,可以定义 excel 的标题
public function integer uf_toexcel (datawindow adw_data, readonly string as_title)

2. 下面是两个用户对象的 sru 文件,自己导入到工程中
$PBExportHeader$uo_ds_base.sru
$PBExportComments$数据存储基类
forward
global type uo_ds_base from datastore
end type
end forward

global type uo_ds_base from datastore
string dataobject = "d_expression"
end type
global uo_ds_base uo_ds_base

forward prototypes
public function any uf_getitemvalue (long al_row, string as_colname)
public function string uf_about ()
public function string uf_globalreplace (string as_source, string as_old, string as_new)
end prototypes

public function any uf_getitemvalue (long al_row, string as_colname);
//*************************************************//
//function : 得到任意的列的值
//parm     :
//         1. al_row       : 指定行
//         2. as_colname   : 指定列的列名
//return   : -1 is fail or suclearcase/" target="_blank" >ccess is value to you
//author   : hzh
//date     : 2002.11.05
//************************************************//

 

String s_tempcoltype
any a_ret
s_tempcoltype = Lower(THIS.Describe(as_colname + ".coltype"))

//for string type
IF Left(s_tempcoltype,4) = "char" OR Left(s_tempcoltype,4) ="varc"then
 a_ret = THIS.GetItemString(al_row,as_colname)
END IF 

//for decimal type
IF Left(s_tempcoltype,7) = "decimal" then
 a_ret = THIS.GetItemDecimal(al_row,as_colname)
END IF

//for date type
IF s_tempcoltype = "date" THEN
 a_ret = THIS.GetItemDate(al_row,as_colname)
END IF

 //for datetime type
IF s_tempcoltype = "datetime" THEN
 a_ret = THIS.GetItemDateTime(al_row,as_colname)
END IF

//for number type
IF s_tempcoltype = "number" THEN
 a_ret = THIS.GetItemNumber(al_row,as_colname)
END IF

//for time type
IF s_tempcoltype = "time" THEN
 a_ret = THIS.GetItemTime(al_row,as_colname)
END IF

//for timestamp type
IF s_tempcoltype = "timestamp" THEN
 a_ret = THIS.GetItemTime(al_row,as_colname)
END IF

//for int or long
IF s_tempcoltype = "int" OR s_tempcoltype = "long" THEN
 a_ret = THIS.GetItemnumber(al_row,as_colname)
END IF

IF IsNull(a_ret) THEN
 RETURN -1
END IF

RETURN a_ret
 
end function

public function string uf_about ();
string s_func = ""

s_func = " 1. 求得表达式的值 (uf_evaluate) " +&
   " 2. 根据 SQL ,创建数据存储 (uf_setsqlselect) ~r~n " +&
   " 3. 得到任意列的值(uf_getitemvalue) ~r~n "
  
//s_func += SUPER :: uf_about()

  
RETURN "uo_ds_base object member's functions : ~r~n" + s_func


end function
public function string uf_globalreplace (string as_source, string as_old, string as_new);
//**************************************************************//
//function                 : 用指定的字符串替换指定字符串
//parm                     :
//         1. as_source    : 原来的字符串
//         2. as_old       : 将要被替换的字符串
//         3. as_new       : 用来替换的字符串
//return   : 新的字符串
//author   : hzh
//date     : 2002.11.14
//*************************************************************//

Long     l_newlen, l_oldlen, l_start
String   s_null, s_source


IF IsNull(as_source) OR IsNull(as_old) OR IsNull(as_new) THEN
 SetNull(s_null)
 RETURN s_null
ELSE
 l_oldlen = Len(as_old)
 l_newlen = Len(as_new)
 as_Old = Lower(as_old)
 s_Source = Lower(as_source)
END IF

l_start = Pos(s_source, as_old)

DO WHILE l_start > 0
 as_source = Replace(as_source, l_start, l_oldlen, as_new)
 s_source = Lower(as_Source)
 l_start = Pos(s_source, as_old, (l_start + l_newlen))
LOOP


RETURN as_source

end function
on uo_ds_base.create
call super::create
TriggerEvent( this, "constructor" )
end on

on uo_ds_base.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

-----------------------------------------------------------------


$PBExportHeader$nvo_excel.sru
$PBExportComments$和 excel 通讯的功能函数
forward
global type nvo_excel from nonvisualobject
end type
end forward

global type nvo_excel from nonvisualobject
end type
global nvo_excel nvo_excel

type prototypes
Private:
 Function uint GetModuleFileNameA(ulong hModule,ref string lpFilename,ulong nSize) Library "kernel32.dll"
end prototypes

type variables

Private:
//存储要导出的数据
uo_ds_base ids_data

//列名
String is_columnname[]

//列的标题
String is_columntitle[]

//列的显示格式
String is_columnformat[]

//列的类型
String is_columntype[]
end variables

forward prototypes
public function integer uf_toexcel (datastore ads_data, readonly string as_title)
public function integer uf_toexcel (datawindow adw_data, readonly string as_title)
private function integer uf_setdatasource (datawindow adw_data)
private function integer uf_setdatasource (datastore ads_data)
private function integer uf_datatoexcel (string as_title)
public function string uf_about ()
private function integer uf_initcolumn ()
end prototypes

public function integer uf_toexcel (datastore ads_data, readonly string as_title);

/**********************************************************/
//Function                 : 转换数据到 excel
//parm                     :
//        1. ads_data      : 包含源数据的对象
//        2. as_title      :  excel 的标题
//return                   : 1 is success and -1 is fail
//Author                   : hzh
//date                     : 2003.12.08
/**********************************************************/

IF THIS.uf_setdatasource(ads_data) <> 1 THEN RETURN -1

IF NOT IsValid(ids_data) THEN RETURN -1

IF ids_data.RowCount() < 1 THEN RETURN -1

THIS.uf_initcolumn()

THIS.uf_datatoexcel(as_title)


RETURN 1
end function

public function integer uf_toexcel (datawindow adw_data, readonly string as_title);
/**********************************************************/
//Function                 : 转换数据到 excel
//parm                     :
//        1. adw_data      : 包含源数据的对象
//        2. as_title      :  excel 的标题
//return                   : 1 is success and -1 is fail
//Author                   : hzh
//date                     : 2003.12.08
/**********************************************************/

IF THIS.uf_setdatasource(adw_data) <> 1 THEN RETURN -1

IF NOT IsValid(ids_data) THEN RETURN -1

IF ids_data.RowCount() < 1 THEN RETURN -1


THIS.uf_initcolumn()

THIS.uf_datatoexcel(as_title)

RETURN 1
end function

private function integer uf_setdatasource (datawindow adw_data);/**********************************************************/
//Function                    : 设置数据存储
//parm                        : None
//return                      :
//Author                      : hzh
//date                        : 2003.12.08
/**********************************************************/


ids_data.dataobject = adw_data.dataobject
//ids_data.uf_setddobject()
RETURN adw_data.ShareData(ids_data)

 

end function

private function integer uf_setdatasource (datastore ads_data);
/**********************************************************/
//Function                    : 设置数据存储
//parm                        : None
//return                      :
//Author                      : hzh
//date                        : 2003.12.08
/**********************************************************/

ids_data.dataobject = ads_data.dataobject
//ids_data.uf_setddobject()
RETURN ads_data.ShareData(ids_data)
end function

private function integer uf_datatoexcel (string as_title);
/**********************************************************/
//Function                 : 转换数据到 excel
//parm                     :
//        1. as_title      :  excel 的标题
//return                   : 1 is success and -1 is fail
//Author                   : hzh
//date                     : 2003.12.08
//Modifier                 :
//                      1. 2003.12.10 by hzh
//Reason                   :
//                      1. 增加对计算列的处理
/**********************************************************/

long l_cnt,l_i,l_cols,l_rows

string s_colnum ,s_colname,s_range

OLEObject xlapp , xlsub

//l_cols = long(ids_data.Object.DataWindow.Column.Count)
l_cols = UpperBound(is_columntitle)


l_rows = ids_data.RowCount()

IF NOT IsValid(xlApp) THEN
 xlApp = CREATE OLEObject
END IF

IF xlApp.ConnectToNewObject( "Excel.Application" ) < 0  THEN
 MessageBox('ga_app.dwmessagetitle',"不能连接 EXCEL 服务器,请检查你的计算机中是 ~r~n " +&
 "否安装了MS EXCEL ? 假如安装,请与程序供应商联系 !",QuesTion!)
    RETURN -1
END IF

//增加空文档 (EXCEL table)
xlApp.Application.Workbooks.add()
xlApp.Application.Visible = TRUE

IF NOT IsValid(xlsub) THEN
 xlsub = CREATE OLEObject
END IF

//定位到第一格
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[1]

//取得最后列的字母表达式
IF Long(l_cols) > 26  then
 //AA、AB...还是BA、BB、BC...
 int i_colstart,i_colend
 i_colstart = Mod(l_cols,26)
 i_colend = l_cols / 26
 s_colnum = "'" + Char(i_colstart + 96 ) + Char(i_colend + 96) + "'"
ELSE
 //是 A,B,C...格式
 s_colnum = char(l_cols + 96)
END IF

//标题的设置
xlsub.cells[1,1] = as_title
xlsub.cells[1,1].HorizontalAlignment = 3
xlsub.cells[1,1].VerticalAlignment = 3
xlsub.cells[1,1].Font.Size = 18


//去处格子
xlsub.range("A1:" + s_colnum + "1").Merge()

FOR l_i = 1 TO l_cols
 //设置标题列的名字
 xlsub.cells[2,l_i] = is_columntitle[l_i]
NEXT

----------------------------------------------------


//画表格线
//数据行从第二行开始
s_range = "A2:" + s_colnum + Trim(String(l_rows + 2))
xlsub.range(s_range).borders(1).linestyle = 1
xlsub.range(s_range).borders(2).linestyle = 1
xlsub.range(s_range).borders(3).linestyle = 1
xlsub.range(s_range).borders(4).linestyle = 1

//将数据写到EXECL
FOR l_i = 1 TO l_cols
 FOR l_cnt = 1 TO l_rows
  IF is_columntype[l_i] = 'column' THEN
   String s_evaluate
   s_evaluate = "Evaluate('LookUpDisplay(" + is_columnname[l_i]
   s_evaluate += ")'," + String(l_cnt) + ')'
   xlsub.cells[l_cnt + 2,l_i].FormulaR1C1 = ids_data.Describe(s_evaluate)
  ELSE
   xlsub.cells[l_cnt + 2,l_i].FormulaR1C1 = &
     ids_data.uf_getitemvalue(l_cnt,is_columnname[l_i])
  END IF
  xlsub.cells[l_cnt + 2,l_i].NumberFormatLocal = is_columnformat[l_i]
   NEXT
NEXT

xlapp.Application.ActiveWorkbook.saved = FALSE

xlApp.DisConnectObject()

IF IsValid(xlApp) THEN
 DESTROY xlapp
END IF

IF IsValid(xlsub) THEN
 DESTROY xlsub
END IF

RETURN 1
end function

public function string uf_about ();

/**********************************************************/
//Function                    : 用户对象功能介绍
//parm                        : None
//return                      : 介绍的文本和对应的公共函数
//Author                      : hzh
//date                        : 2003.12.08
/**********************************************************/


String s_func = ""

//s_func = super :: uf_about()

s_func =  s_func + "~r~nvo_excel object member's functions :  ~r~n"

s_func+= "  1. 用户对象功能介绍 (uf_about) ~r~n" +&
   "  2. 程序数据导出为 excel 表,已经重载 (uf_toexcel) ~r~n"
   

RETURN s_func

end function
private function integer uf_initcolumn ();
/**********************************************************/
//Function                 : 初始化列信息
//parm                     :
//return                   : of no use
//Author                   : hzh
//date                     : 2003.12.08
//Modifier                 :
//                      1. 2003.12.10 by hzh
//Reason                   :
//                      1. 增加对计算列的处理
/**********************************************************/

Int i_cnt
String s_colname,s_datatype


//清空对象
FOR i_cnt = 1 TO UpperBound(is_columnname)
 SetNull(is_columnname[i_cnt])
 SetNull(is_columntitle[i_cnt])
 SetNull(is_columnformat[i_cnt])
 SetNull(is_columntype[i_cnt])
END FOR

String s_objects
uo_ds_base ds_excel
IF NOT IsValid(ds_excel) THEN
 ds_excel = CREATE uo_ds_base
END IF

ds_excel.dataobject = 'dw_excel_columns'
s_objects = ids_data.Describe("DataWindow.Objects")

// 将 DETAIL 区域内的所有可见目标放到 ds_excel 中,
// 并按照 object.x 属性大小排序
DO WHILE Len(s_objects) > 0
   Int i_pos
 Long l_x
 String s_name,s_type,s_band,s_visible,s_objtype
 String s_objzw,s_zw
 
 i_pos = Pos(s_objects, "~t")
 IF i_pos <= 0 THEN i_pos = Len(s_objects) + 1
  // Object 名字
 s_name = Left(s_objects,i_pos - 1)
 s_objects = Mid(s_objects,i_pos + 1, Len(s_objects))
   //Object 数据类型
   s_type = Lower(ids_data.Describe(s_name + ".coltype"))

 // Object X 坐标
 l_x = Long(ids_data.Describe(s_name + ".x"))
  // Object 所属区域
 s_band = Lower(ids_data.Describe(s_name + ".band"))
 // Object 是否可见
 s_visible = ids_data.Describe(s_name + ".visible")
 // Object 类别
 s_objtype = Lower(ids_data.Describe(s_name + ".type"))
 // 如果 object 在 Detail 区,且可见,并且是 column 或 Compute Column
 IF s_band = "detail" AND s_visible = "1" AND &
    (s_objtype = "column" OR s_objtype = "compute" ) THEN

    // Object 中文标头,支持标准命名
  s_objzw = s_name + "_t"
  s_zw    = ids_data.describe(s_objzw + ".text")
        // 去掉标题中多余的换行符,空格和引号
  s_zw = ds_excel.uf_globalreplace(s_zw,"~n","")
      s_zw = ds_excel.uf_globalreplace(s_zw," ","")
  s_zw = ds_excel.uf_globalreplace(s_zw,'"',"")
  Long l_newrow
      l_newrow = ds_excel.InsertRow(0)
    ds_excel.SetItem(l_newrow, "colname",s_name)
  ds_excel.SetItem(l_newrow, "x",l_x)
  ds_excel.SetItem(l_newrow, "coltype",s_type)
  ds_excel.SetItem(l_newrow, "coltitle",s_zw)
  ds_excel.SetItem(l_newrow, "objtype",s_objtype)
   END IF
LOOP
//排序,设置到列数组中
ds_excel.SetSort("x a")
ds_excel.Sort()

FOR i_cnt = 1 TO ds_excel.RowCount()
 is_columnname[i_cnt] = Lower(ds_excel.GetItemString(i_cnt,'colname'))
 is_columntitle[i_cnt] = ds_excel.GetItemString(i_cnt,'coltitle')
 is_columntype[i_cnt] = ds_excel.GetItemString(i_cnt,'objtype')
 s_datatype = Left(Lower(ds_excel.GetItemString(i_cnt,'coltype')),4)
 CHOOSE CASE s_datatype
  CASE 'char','varc','int','long'
   is_columnformat[i_cnt] = 'G/通用格式'
  
   //特别指定日期专用格式为 char(10)
   s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
   IF s_datatype = 'char(10)' THEN
    is_columnformat[i_cnt] = 'yyyy""年""m""月""d""日""'
   END IF
  CASE 'deci'
   is_columnformat[i_cnt] = "0.00_ "
  CASE 'date','datetime'
   is_columnformat[i_cnt] = 'yyyy""年""m""月""d""日""'
  CASE 'time'
   is_columnformat[i_cnt] = 'h""时""mm""分""ss""秒""'
  CASE ELSE
   is_columnformat[i_cnt] = 'G/通用格式'
 END CHOOSE
NEXT


IF IsValid(ds_excel) THEN
 DESTROY ds_excel
END IF

RETURN 1

end function
on nvo_excel.create
call super::create
TriggerEvent( this, "constructor" )
end on

on nvo_excel.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

event constructor;
IF NOT IsValid(ids_data) THEN
 ids_data = CREATE uo_ds_base
END IF
end event

event destructor;

IF IsValid(ids_data) THEN
 DESTROY ids_data
END IF
end event

--------------------------------------------


$PBExportHeader$dw_excel_columns.srd
$PBExportComments$临时得 转换对象
release 7;
datawindow(units=0 timer_interval=0 color=16777215 processing=1 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no grid.lines=0 )
header(height=68 color="536870912" )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=76 color="536870912" )
table(column=(type=char(40) updatewhereclause=no name=coltitle dbname="coltitle" )
 column=(type=char(30) updatewhereclause=no name=colname dbname="colname" )
 column=(type=long updatewhereclause=no name=x dbname="x" )
 column=(type=char(10) updatewhereclause=no name=objtype dbname="objtype" )
 column=(type=char(20) updatewhereclause=no name=coltype dbname="coltype" )
 )
text(band=header alignment="2" text="Coltype" border="2" color="0" x="718" y="4" height="60" width="361"  name=coltype_t  font.face="宋体" font.height="-10" font.weight="400"  font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="80269524" )
text(band=header alignment="0" text="coltitle" border="2" color="0" x="1088" y="4" height="60" width="434"  name=t_1  font.face="宋体" font.height="-10" font.weight="400"  font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="80269524" )
text(band=header alignment="0" text="objtype" border="2" color="0" x="1531" y="4" height="60" width="320"  name=t_2  font.face="宋体" font.height="-10" font.weight="400"  font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="80269524" )
text(band=header alignment="2" text="Colname" border="2" color="0" x="9" y="4" height="60" width="457"  name=colname_t  font.face="宋体" font.height="-10" font.weight="400"  font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="80269524" )
text(band=header alignment="2" text="X" border="2" color="0" x="475" y="4" height="60" width="233"  name=x_t  font.face="宋体" font.height="-10" font.weight="400"  font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="80269524" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="2" color="0" x="1088" y="0" height="72" width="434" format="[general]"  name=coltitle edit.limit=0 edit.case=any edit.autoselect=yes  font.face="宋体" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="134" background.mode="2" background.color="16777215" )
column(band=detail id=4 alignment="0" tabsequence=32766 border="2" color="0" x="1531" y="0" height="72" width="320" format="[general]"  name=objtype edit.limit=0 edit.case=any edit.autoselect=yes  font.face="宋体" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="134" background.mode="2" background.color="16777215" )
column(band=detail id=2 alignment="0" tabsequence=10 border="2" color="0" x="9" y="0" height="72" width="457" format="[general]"  name=colname edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="宋体" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="134" background.mode="1" background.color="536870912" )
column(band=detail id=3 alignment="1" tabsequence=20 border="2" color="0" x="475" y="0" height="72" width="233" format="[general]"  name=x edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="宋体" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="134" background.mode="1" background.color="536870912" )
column(band=detail id=5 alignment="0" tabsequence=30 border="2" color="0" x="718" y="0" height="72" width="361" format="[general]"  name=coltype edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="宋体" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="134" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" )

----------------------------------------------------------


上面的程序 Bug  列表 :
1.

/**********************************************************/
//Function                 : 转换数据到 excel
//parm                     :
//        1. as_title      :  excel 的标题
//return                   : 1 is success and -1 is fail
//Author                   : hzh
//date                     : 2003.12.08
//Modifier                 :
//                      1. 2003.12.10 by hzh
//Reason                   :
//                      1. 增加对计算列的处理
/**********************************************************/

long l_cnt,l_i,l_cols,l_rows

string s_colnum ,s_colname,s_range

OLEObject xlapp , xlsub

FOR l_i = 1 TO UpperBound(is_columntitle)
 IF IsNull(is_columntitle[l_i]) THEN EXIT
 l_cols++
NEXT


2.
函数 : uf_initcolumn  处理 datetime 格式不是很好

CASE 'date'//,'datetime'
   s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
   IF s_datatype = 'datetime' THEN
    is_columnformat[i_cnt] = 'yyyy-m-d h:mm'
   ELSE
    is_columnformat[i_cnt] = 'yyyy""年""m""月""d""日""'
   END IF

3. 这个不是错误,可以加强一下功能
//特别指定日期专用格式为 char(10)
   s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
   IF s_datatype = 'char(10)' THEN
    is_columnformat[i_cnt] = 'yyyy""年""m""月""d""日""'
   END IF
   
   //特别指定时间专用格式为 char(8)
   s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
   IF s_datatype = 'char(8)' THEN
    is_columnformat[i_cnt] = 'h""时""mm""分""ss""秒""'
   END IF

   //特别指定日期时间专用格式为 char(19)
   s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
   IF s_datatype = 'char(19)' THEN
    is_columnformat[i_cnt] = 'yyyy-m-d h:mm'
   END IF

根据代码,只有 char(8),10,19 才有啊,这是我设计日期,时间等的专用格式

-----------------------------------------------------------


我再把另外一个帖子当中中国龙的方法也贴过来,那个帖子可能由于CSDN历史帖子管理原因无法成功添加为FAQ,也很可惜,大家如果有其他好的方法也请一并贴上来:

llitcwl(中国龙):

//====================================================================
// [PUBLIC] Function uf_data2excel 在 u_data2word inherited from nonvisualobject
//--------------------------------------------------------------------
// 说明:将数据倒入excel中,支持计算列及显示格式,要求在题头的计算列要写tag值
//--------------------------------------------------------------------
// 参数1:[value] datawindow adw
//  说明:数据窗口
//--------------------------------------------------------------------
// 返回: (INTEGER) 成功返回1,不成功返回0
//--------------------------------------------------------------------
// 作者: cwl  日期: 2002.03.18
//====================================================================
//变更日志:020515加入对交叉表倒出的支持(主要是修改了保存题头部分)

constant integer ppLayoutBlank = 12
OLEObject ole_object
ole_object = CREATE OLEObject 

integer li_ret,li_crosstab=0
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
//题头区
long ll_headnum
string ls_head[],ls_headtag[]
//合计区
long ll_sumnum,i=1,startpos=1,endpos,li_pos
string ls_sum[],ls_sumtag[],ls_bind,token[],list,ls_temp,ls_crosstabcol
n_cst_string lu_string //PFC string处理对象

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 = false//不可见
END IF

if adw.Object.DataWindow.Processing='4' then //交叉表处理
 adw.Object.DataWindow.Crosstab.StaticMode='true'//将数据静态化
 li_crosstab=1
end if

pointer oldpointer
oldpointer = SetPointer(HourGlass!)

//新增一个工作区
ole_object.Workbooks.Add

 

ls_objects = trim(adw.Describe('datawindow.Objects'))
list=ls_objects
EndPos = pos(list, '~t', StartPos)
//得到对象列表
Do while ( EndPos > 0 )
 token[i] = Mid(list, StartPos, EndPos - StartPos)
 i ++
 StartPos = EndPos + 1
 EndPos = pos(list, '~t', StartPos)
LOOP
token[i] = Mid(list, StartPos)
ll_rownum=UpperBound(token)

for i=1 to ll_rownum
 ls_obj = token[i]
 if ls_obj='title' then messagebox(',adw.Describe(ls_obj + '.type'))
 if lower(adw.Describe(ls_obj + '.type')) = 'column' or &
  lower(adw.Describe(ls_obj + '.type')) = 'compute' then
  ls_bind=lower(adw.Describe(ls_obj + '.band'))
  if ls_bind = 'detail' then
   ll_num += 1
   ls_objs[ll_num] = ls_obj
   if li_crosstab=0 then //一般处理
    ls_objtag[ll_num] = adw.Describe(ls_obj + '_t.text')
   elseif li_crosstab=1 then //交叉表处理
    li_pos=lu_string.of_lastpos(ls_obj,'_',len(ls_obj))//找出最后一次出现'_'的位置
    if li_pos=0 or (not isnumber(mid(ls_obj,li_pos+1))) then //不是交叉列
     ls_objtag[ll_num] = adw.Describe(ls_obj + '_t.text')
    else
     ls_temp=mid(ls_obj,li_pos)
     ls_crosstabcol=mid(ls_obj,1,li_pos - 1)//取出交叉列名
//     messagebox(',ls_crosstabcol+',,,,'+ls_temp)
     ls_objtag[ll_num]=adw.Describe( ls_crosstabcol + "_t"+ls_temp+".Text" )//取出交叉表的题头
    end if
   end if
  elseif (ls_bind = 'summary') then
   ll_sumnum += 1
   ls_sum[ll_sumnum] = ls_obj
   ls_sumtag[ll_sumnum] = adw.Describe(ls_obj + '.tag')
  else
   ll_headnum += 1
   ls_head[ll_headnum] = ls_obj
   ls_headtag[ll_headnum] = adw.Describe(ls_obj + '.tag')
  end if

 end if
 
next

//得到数据窗口数据的列数与行数(行数应该是数据行数 + 2)
ll_colnum = ll_num
ll_rownum = adw.rowcount() + 2

string column_name
string ls_colname
integer j,k
//写题头
for i=1 to ll_headnum
 ls_value = ls_headtag[i]
 if ls_value<>'?' then
  ole_object.cells(1,(i - 1)*2+1).value = ls_value
 end if
 column_name = ls_head[i]
 ls_value=this.uf_getdata(adw,column_name,1)
 ole_object.cells(1,(i)*2).value = ls_value
next
//写结尾
for i=1 to ll_sumnum
 ls_value = ls_sumtag[i]
 if ls_value<>'?' then
  ole_object.cells(ll_rownum+1,(i - 1)*2+1).value = ls_value
 end if
 column_name = ls_sum[i]
 ls_value=this.uf_getdata(adw,column_name,1)
 ole_object.cells(ll_rownum+1,(i)*2).value = ls_value
next

//写标题
for i = 1 to ll_colnum
 //得到标题头的名字
 ls_value = ls_objtag[i]
 ole_object.cells(2,i).value = ls_value
next
//写数据
for i = 3 to ll_rownum
 for j = 1 to ll_colnum
  column_name = ls_objs[j]
  ls_value=this.uf_getdata(adw,column_name,i - 2)
  ole_object.cells(i,j).value = ls_value
 next
next

SetPointer(oldpointer)
ole_object.Visible = True
ole_object.disconnectobject()
DESTROY ole_object

return 1

 

//====================================================================
// [PUBLIC] Function uf_getdata 在 u_data2word inherited from nonvisualobject
//--------------------------------------------------------------------
// 说明:得到一个数据窗口列及计算列的准确显示值
//--------------------------------------------------------------------
// 参数1:[value] datawindow dw_1
//  说明:
// 参数2:[value] string col
//  说明:对象名
// 参数3:[value] integer row
//  说明:行
//--------------------------------------------------------------------
// 返回: (STRING) 值
//--------------------------------------------------------------------
// 作者: cwl  日期: 2002.03.18
//====================================================================
string ls_edittype,ls_value,ls_format
integer id
ls_edittype=lower(dw_1.Describe(col+".Edit.Style"))//得到编缉风格
choose case ls_edittype
 case 'ddlb','dddw'//应该得到显示值
  ls_value=dw_1.describe(  "Evaluate('LookUpDisplay("+col+") ',"+string(row)+" )")
 case else
  id=long(dw_1.Describe(col+".id"))
  ls_format=dw_1.Describe(col+".Format")
  if mid(ls_format,1,1)='[' or ls_format='?' or ls_format=' then //不作格式处理
   if id=0 then //计算列
    ls_value=dw_1.Describe("Evaluate(~"" + dw_1.Describe(col + '.expression')&
     + "~","+string(row)+")")
   else
    ls_value=string(dw_1.object.data[row,id])
   end if
  else
   if id=0 then //计算列
    ls_value=string(dw_1.Describe("Evaluate('" + dw_1.Describe(col + '.expression')&
     + "',"+string(row)+")"),ls_format)
   else
    ls_value=string(dw_1.object.data[row,id],ls_format)
   end if
  end if
end choose
if isnull(ls_value) then ls_value='
return ls_value


或者直接存成html文件

----------------------------------------------------------


继续响应,这是以前一位仁兄的代码:


/**********************************************************/ 
/* 函数名称:uf_dwsaveas_excel
 功能 :将数据窗口数据导出EXCEL文件,并将EXCEL文件默认英文标题替换成中文。
 参数 :datawindow datawin,为用户要导出数据窗口的数据窗口控件名
 返回值:integer 1,success;-1,error
 流程描述:先用saveasAscii()倒出为excel文件,再替换表头为中文名
 设计人:yanhui 2003年11月
 修改人:叶文林 2004.4.8
         原因:为提高程序的可读性作了少量的修改(如:增加注释、改变排版风格等)*/
/**********************************************************/

/***************以下程序将导出为EXCEL文档******************/
integer li_rtn,ii,li_asc
string ls_name,ls_pathname
boolean lb_exist
if datawin.RowCount()<1 then
 MessageBox("提示信息","请先检索数据再导出至Excel!")
 return -1 //error
end if
li_rtn=GetFileSaveName("保存文件",ls_pathname,ls_name,"xls","Excel文件(*.xls),*.xls")

if li_rtn=1 then
 lb_exist = FileExists(ls_pathname)
 IF lb_exist THEN
  li_rtn = MessageBox("保存", ls_pathname+"已经存在,是否覆盖?",Exclamation!, YesNo!)
 end if
 if li_rtn=1 then
 //当文件存在用户选择覆盖,或是文件本就不存在时。注意变量li_rtn
  li_rtn=datawin.SaveAsAscii(ls_pathname)
  if li_rtn=1 then
//   MessageBox("提示信息","导出数据成功!")   
  else
   MessageBox("错误信息","导出数据失败!")
   return -1 //error
  end if
 else
  return -1 //error
 end if
else
 return -1
end if 

/**********以下程序将导出的EXCEL英文标题替换为汉字*********/
long numcols , numrows , c, r
OLEObject xlapp , xlsub
int ret
numcols = long(datawin.Object.DataWindow.Column.Count)
numrows = datawin.RowCount()

// 产生oleobject的实例
xlApp = Create OLEObject

//连接ole对象
ret = xlApp.ConnectToNewObject( "Excel.Sheet" )
if ret < 0  then
 MessageBox("连接失败!","连接到EXCEL失败,请确认您的系统是否已经安装EXCEL!~r~n"&
 +"错误代码:"+string(ret))
    return -1
end if
// 打开EXCEL文件
xlApp.Application.Workbooks.Open(ls_pathname)
////使文件可见
//xlApp.Application.Visible = true

// 得到活动工作表的引用,改善程序性能
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[1]
string ls_colname,ls_text,ls_modistr,ls_col
//取字段名更改为对应的文本text值
FOR c=1 to numcols
 ls_col="#"+string(c)+".name"
 ls_colname=datawin.describe(ls_col)
 ls_modistr=ls_colname+"_t.text"
 ls_text=datawin.describe(ls_modistr)
 xlsub.cells[1,c]=ls_text
NEXT

xlApp.DisConnectObject()
Destroy xlapp
MessageBox("提示信息","导出数据成功!") 
return 1 //success

--------------------------------------------------------------


收藏的一个导出为 excel 的例子  
(支持导出分组带、合计带,并且支持多层嵌套报表导出,基本是所见及所得)
///////////////////////////////////////////////////////////////////////////
//
// 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


原文转自:http://www.ltesting.net