将DW数据窗口导出为EXCEL文件的方法（整理）
本文为摘自CSDN论坛帖子收集整理后汇总版本：
---原贴见：
鉴于现在很多朋友询问 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$数据存储基类forwardglobal type uo_ds_base from datastoreend typeend forward
global type uo_ds_base from datastorestring dataobject = "d_expression"end typeglobal uo_ds_base uo_ds_base
forward prototypespublic 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
: 指定行//
2. as_colname
: 指定列的列名//return
: -1 is fail or success is value to you //author
: hzh//date
: //************************************************//
String s_tempcoltypeany a_rets_tempcoltype = Lower(THIS.Describe(as_colname + ".coltype"))
//for string typeIF Left(s_tempcoltype,4) = "char" OR Left(s_tempcoltype,4) ="varc"then a_ret = THIS.GetItemString(al_row,as_colname)END IF
//for decimal typeIF Left(s_tempcoltype,7) = "decimal" then a_ret = THIS.GetItemDecimal(al_row,as_colname)END IF
//for date typeIF s_tempcoltype = "date" THEN a_ret = THIS.GetItemDate(al_row,as_colname)END IF
//for datetime typeIF s_tempcoltype = "datetime" THEN a_ret = THIS.GetItemDateTime(al_row,as_colname)END IF
//for number typeIF s_tempcoltype = "number" THEN a_ret = THIS.GetItemNumber(al_row,as_colname)END IF
//for time typeIF s_tempcoltype = "time" THEN a_ret = THIS.GetItemTime(al_row,as_colname)END IF
//for timestamp typeIF s_tempcoltype = "timestamp" THEN a_ret = THIS.GetItemTime(al_row,as_colname)END IF
//for int or longIF s_tempcoltype = "int" OR s_tempcoltype = "long" THEN a_ret = THIS.GetItemnumber(al_row,as_colname)END IF
IF IsNull(a_ret) THEN RETURN -1END 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 functionpublic function string uf_globalreplace (string as_source, string as_old, string as_new);//**************************************************************////function
: 用指定的字符串替换指定字符串//parm
1. as_source
: 原来的字符串//
: 将要被替换的字符串//
: 用来替换的字符串//return
: 新的字符串//author
: hzh//date
: //*************************************************************//
l_newlen, l_oldlen, l_startString
s_null, s_source
IF IsNull(as_source) OR IsNull(as_old) OR IsNull(as_new) THEN SetNull(s_null) RETURN s_nullELSE 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 functionon uo_ds_base.createcall super::createTriggerEvent( this, "constructor" )end on
on uo_ds_base.destroyTriggerEvent( this, "destructor" )call super::destroyend on
-----------------------------------------------------------------
$PBExportHeader$nvo_excel.sru$PBExportComments$和 excel 通讯的功能函数forwardglobal type nvo_excel from nonvisualobjectend typeend forward
global type nvo_excel from nonvisualobjectend typeglobal nvo_excel nvo_excel
type prototypesPrivate: 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 prototypespublic 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
: /**********************************************************/
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 1end 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
: /**********************************************************/
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 1end function
private function integer uf_setdatasource (datawindow adw_data);/**********************************************************///Function
: 设置数据存储//parm
: None//return
: //Author
: hzh//date
: /**********************************************************/
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
: /**********************************************************/
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
: //Modifier
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 OLEObjectEND IF
IF xlApp.ConnectToNewObject( "Excel.Application" ) & 0
THEN MessageBox('ga_app.dwmessagetitle',"不能连接 EXCEL 服务器,请检查你的计算机中是 ~r~n " +& "否安装了MS EXCEL ? 假如安装，请与程序供应商联系 !",QuesTion!)
RETURN -1END IF
//增加空文档 (EXCEL table)xlApp.Application.Workbooks.add() xlApp.Application.Visible = TRUE
IF NOT IsValid(xlsub) THEN xlsub = CREATE OLEObjectEND 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 = 3xlsub.cells[1,1].VerticalAlignment = 3xlsub.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 = 1xlsub.range(s_range).borders(2).linestyle = 1xlsub.range(s_range).borders(3).linestyle = 1xlsub.range(s_range).borders(4).linestyle = 1
//将数据写到EXECLFOR 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)
xlsub.cells[l_cnt + 2,l_i].FormulaR1C1 = &
ids_data.uf_getitemvalue(l_cnt,is_columnname[l_i])
xlsub.cells[l_cnt + 2,l_i].NumberFormatLocal = is_columnformat[l_i]
xlapp.Application.ActiveWorkbook.saved = FALSE
xlApp.DisConnectObject()
IF IsValid(xlApp) THEN DESTROY xlappEND IF
IF IsValid(xlsub) THEN DESTROY xlsubEND IF
RETURN 1end function
public function string uf_about ();
/**********************************************************///Function
: 用户对象功能介绍//parm
: None//return
: 介绍的文本和对应的公共函数//Author
: hzh//date
: /**********************************************************/
String s_func = ""
//s_func = super :: uf_about()
s_func + "~r~nvo_excel object member's functions :
s_func+= "
1. 用户对象功能介绍 (uf_about) ~r~n" +&
2. 程序数据导出为 excel 表,已经重载 (uf_toexcel) ~r~n"
RETURN s_func
end functionprivate function integer uf_initcolumn ();/**********************************************************///Function
: 初始化列信息//parm
: of no use//Author
: hzh//date
: //Modifier
by hzh//Reason
1. 增加对计算列的处理/**********************************************************/
Int i_cntString 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_objectsuo_ds_base ds_excelIF NOT IsValid(ds_excel) THEN ds_excel = CREATE uo_ds_baseEND 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"
= 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 IFLOOP//排序,设置到列数组中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""日""'
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""秒""'
is_columnformat[i_cnt] = 'G/通用格式' END CHOOSENEXT
IF IsValid(ds_excel) THEN DESTROY ds_excelEND IF
end functionon nvo_excel.createcall super::createTriggerEvent( this, "constructor" )end on
on nvo_excel.destroyTriggerEvent( this, "destructor" )call super::destroyend on
IF NOT IsValid(ids_data) THEN ids_data = CREATE uo_ds_baseEND IFend event
IF IsValid(ids_data) THEN DESTROY ids_dataEND IFend event
--------------------------------------------
$PBExportHeader$dw_excel_columns.srd$PBExportComments$临时得 转换对象release 7;datawindow(units=0 timer_interval=0 color= 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="" )summary(height=0 color="" )footer(height=0 color="" )detail(height=76 color="" )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="" )text(band=header alignment="0" text="coltitle" border="2" color="0" x="1088" y="4" height="60" width="434"
font.face="宋体" font.height="-10" font.weight="400"
font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="" )text(band=header alignment="0" text="objtype" border="2" color="0" x="1531" y="4" height="60" width="320"
font.face="宋体" font.height="-10" font.weight="400"
font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="" )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="" )text(band=header alignment="2" text="X" border="2" color="0" x="475" y="4" height="60" width="233"
font.face="宋体" font.height="-10" font.weight="400"
font.family="0" font.pitch="2" font.charset="134" background.mode="2" background.color="" )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="" )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="" )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="" )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="" )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="" )htmltable(border="1" )htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" )
----------------------------------------------------------
上面的程序 Bug
/**********************************************************///Function
: 转换数据到 excel //parm
1. as_title
excel 的标题//return
: 1 is success and -1 is fail//Author
: hzh//date
: //Modifier
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'
is_columnformat[i_cnt] = 'yyyy""年""m""月""d""日""'
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""日""'
//特别指定时间专用格式为 char(8)
s_datatype = Lower(ds_excel.GetItemString(i_cnt,'coltype'))
IF s_datatype = 'char(8)' THEN
is_columnformat[i_cnt] = 'h""时""mm""分""ss""秒""'
//特别指定日期时间专用格式为 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'
根据代码，只有 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
日期: //====================================================================//变更日志：020515加入对交叉表倒出的支持(主要是修改了保存题头部分)
constant integer ppLayoutBlank = 12OLEObject ole_objectole_object = CREATE OLEObject
integer li_ret,li_crosstab=0long ll_colnum,ll_rownumstring ls_valuestring ls_objects,ls_obj,ls_objs[],ls_objtag[]long ll_pos,ll_len,ll_num = 0//题头区long ll_headnumstring ls_head[],ls_headtag[]//合计区long ll_sumnum,i=1,startpos=1,endpos,li_posstring ls_sum[],ls_sumtag[],ls_bind,token[],list,ls_temp,ls_crosstabcoln_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=1end if
pointer oldpointeroldpointer = SetPointer(HourGlass!)
//新增一个工作区ole_object.Workbooks.Add
ls_objects = trim(adw.Describe('datawindow.Objects'))list=ls_objectsEndPos = pos(list, '~t', StartPos)//得到对象列表Do while ( EndPos & 0 ) token[i] = Mid(list, StartPos, EndPos - StartPos) i ++ StartPos = EndPos + 1
EndPos = pos(list, '~t', StartPos)LOOPtoken[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')
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" )//取出交叉表的题头
elseif (ls_bind = 'summary') then
ll_sumnum += 1
ls_sum[ll_sumnum] = ls_obj
ls_sumtag[ll_sumnum] = adw.Describe(ls_obj + '.tag')
ll_headnum += 1
ls_head[ll_headnum] = ls_obj
ls_headtag[ll_headnum] = adw.Describe(ls_obj + '.tag')
end if next
//得到数据窗口数据的列数与行数（行数应该是数据行数 + 2）ll_colnum = ll_numll_rownum = adw.rowcount() + 2
string column_namestring ls_colnameinteger 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_valuenext//写结尾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_valuenext
//写标题for i = 1 to ll_colnum //得到标题头的名字 ls_value = ls_objtag[i] ole_object.cells(2,i).value = ls_valuenext//写数据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 nextnext
SetPointer(oldpointer)ole_object.Visible = Trueole_object.disconnectobject()DESTROY ole_object
//====================================================================// [PUBLIC] Function uf_getdata 在 u_data2word inherited from nonvisualobject//--------------------------------------------------------------------// 说明:得到一个数据窗口列及计算列的准确显示值//--------------------------------------------------------------------// 参数1:[value] datawindow dw_1//
说明:// 参数2:[value] string col//
说明:对象名// 参数3:[value] integer row//
说明:行//--------------------------------------------------------------------// 返回: (STRING) 值//--------------------------------------------------------------------// 作者: cwl
日期: //====================================================================string ls_edittype,ls_value,ls_formatinteger idls_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)+")")
ls_value=string(dw_1.object.data[row,id])
if id=0 then //计算列
ls_value=string(dw_1.Describe("Evaluate('" + dw_1.Describe(col + '.expression')&
+ "',"+string(row)+")"),ls_format)
ls_value=string(dw_1.object.data[row,id],ls_format)
end ifend chooseif 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月 修改人：叶文林
原因：为提高程序的可读性作了少量的修改(如：增加注释、改变排版风格等)*//**********************************************************/
/***************以下程序将导出为EXCEL文档******************/integer li_rtn,ii,li_ascstring ls_name,ls_pathnameboolean lb_existif datawin.RowCount()&1 then MessageBox("提示信息","请先检索数据再导出至Excel!") return -1 //errorend ifli_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("提示信息","导出数据成功!")
MessageBox("错误信息","导出数据失败!")
return -1 //error
end if else
return -1 //error end ifelse return -1end if
/**********以下程序将导出的EXCEL英文标题替换为汉字*********/long numcols , numrows , c, rOLEObject xlapp , xlsubint retnumcols = 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 -1end 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_textNEXT
xlApp.DisConnectObject()Destroy xlappMessageBox("提示信息","导出数据成功!") return 1 //success
--------------------------------------------------------------
收藏的一个导出为 excel 的例子　　（支持导出分组带、合计带，并且支持多层嵌套报表导出,基本是所见及所得）/////////////////////////////////////////////////////////////////////////////// Parameters : ad_dw
: datawindow//
: file name// Returns
: true/false : boolean// Description : Save the datawindow as a excel file./////////////////////////////////////////////////////////////////////////////// author
: purplekite// date
: ///////////////////////////////////////////////////////////////////////////
SetPointer(HourGlass!)
//declare the local variableslong
i, j, li_posstring
ls_objects, ls_obj, ls_text, ls_err, ls_sqldatastore
lds_saveas //导出数据窗datastore
//获得根据 object.x 排序的 (band = detail and visible = 1) 的 column/computeboolean
lb_return //返回值string
//pb 版本信息environment
//环境变量
getenvironment(env)ls_pbver = string(env.pbmajorrevision)
//创建排序列 datastorelds_sort = create datastorels_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 lab1end if
//准备数据====================================================//all controlsls_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 IFloop
//the last controlls_obj = ls_objectsIF (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 ifEND IF//如果没有列则跳出if lds_sort.rowcount() & 1 then goto lab1//根据 object.x 排序lds_sort.setsort('zx A')lds_sort.sort()
//创建导出 datastorelds_saveas = create datastorels_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'nextls_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 lab1end 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) + ')')
ls_text = ad_dw.describe('evaluate(~'' + ls_obj + '~',' + string(i) + ')')
lds_saveas.setitem(i, j, ls_text) nextnext
lds_saveas.insertrow(1)for i = 1 to lds_sort.rowcount() lds_saveas.setitem(1, i, lds_sort.getitemstring(i, 'ztext'))next//准备数据完毕====================================================
//saveas datawindowlb_return = (lds_saveas.saveas(as_file, excel!, false) = 1)
lab1:destroy lds_sortdestroy lds_saveasSetPointer(Arrow!)return lb_return
Dreamweaver中表格数据的导入与导出
DW导出到excel文件（标题、数据）
将dw中的数据导出成excel文件！
没有更多推荐了，