把服务器sql数据库导出excel文件,将DW数据窗口导出为EXCEL文件的方法（整理）-数据库专栏,SQL Server...

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

—2004年9月3日整理

—原贴见：

http://community.youkuaiyun.com/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 success 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 members 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 members 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