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 : // 1. al_row : 指定行// 2. as_colname : 指定列的列名//return : -1 is fail or success is value to you //author : hzh//date : 2002.11.05//************************************************//
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 : 原来的字符串// 2. as_old : 将要被替换的字符串// 3. as_new : 用来替换的字符串//return : 新的字符串//author : hzh//date : 2002.11.14//*************************************************************//
Long 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 : 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 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//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 1end function
private function integer uf_setdatasource (datawindow adw_data);/**********************************************************///Function : 设置数据存储//parm : None//return : //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 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) 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] NEXTNEXT
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 : 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 functionprivate 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_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" 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 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""日""' 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 CHOOSENEXT
IF IsValid(ds_excel) THEN DESTROY ds_excelEND IF
RETURN 1
end functionon nvo_excel.createcall super::createTriggerEvent( this, "constructor" )end on
on nvo_excel.destroyTriggerEvent( this, "destructor" )call super::destroyend on
event constructor;IF NOT IsValid(ids_data) THEN ids_data = CREATE uo_ds_baseEND IFend event
event destructor;
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=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 才有啊，这是我设计日期，时间等的专用格式
