Q5/ws_objects/xls_Billprint_uo.pbl.src/uo_xlsmanager.sru

$PBExportHeader$uo_xlsmanager.sru
forward
global type uo_xlsmanager from nonvisualobject
end type
end forward

shared variables

end variables

global type uo_xlsmanager from nonvisualobject
event ue_saveprintersetting ( )
end type
global uo_xlsmanager uo_xlsmanager

type variables
uo_win32api uo_api
uo_xls_constant uo_const


olecontrol ole_con
s_xls_templates s_tem
Boolean AllowEdit = False
Boolean ifclose = False
Boolean enableselect = False
long actionid = 0

s_xls_billlist str_billlist

w_xls ins_w_ole

Private:
oleobject obj_workbook,obj_sheet,obj_cell,obj_xls,obj_flag[],obj_pic,obj_ole
String CurrentDirectory = ''
Long xls_handle

uo_xls_constant uo_constant 



end variables

forward prototypes
public subroutine of_reset ()
public subroutine of_set_userpwd (string arg_pwd)
public subroutine of_throwexception (string arg_msg)
private subroutine of_filldata ()
public function boolean of_chksavepath (ref string arg_savepath)
public subroutine of_workbookunlock ()
public subroutine of_workbooklock ()
public subroutine of_showexcel (boolean arg_show)
public subroutine of_setpagebreakpreview ()
public function string of_replacestring (string arg_orgstr, string arg_replacestr, string arg_replacevalue)
public function integer of_sendtoexcel (ref string arg_msg)
public subroutine of_script ()
public subroutine of_activetemplate ()
public subroutine of_set_caption (boolean arg_flag)
public subroutine of_setxlsmax ()
public subroutine of_set_screenupdating (boolean arg_flag)
public subroutine of_setsign (string arg_signname, integer arg_flag, integer arg_type, string arg_billflag, string arg_billname, boolean arg_chk, boolean arg_addchk)
public subroutine of_setexcelwindowsize (integer arg_windowstate)
private function string of_getvalue (string arg_columnname, datastore arg_ds, long arg_row)
end prototypes

event ue_saveprintersetting();//ue_saveprintersetting


		
	if isnull (uo_constant)  or isnull(obj_workbook) then return
		//save page setting to ini 
		
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'LeftMargin' , string(obj_workbook.ActiveSheet.PageSetup.LeftMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'RightMargin' , string(obj_workbook.ActiveSheet.PageSetup.RightMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'TopMargin' ,  string(obj_workbook.ActiveSheet.PageSetup.TopMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'BottomMargin' , string(obj_workbook.ActiveSheet.PageSetup.BottomMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'HeaderMargin' , string(obj_workbook.ActiveSheet.PageSetup.HeaderMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'FooterMargin' , string(obj_workbook.ActiveSheet.PageSetup.FooterMargin))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'Orientation' , string(obj_workbook.ActiveSheet.PageSetup.Orientation))
	uo_constant.of_setprofilestring( string(str_billlist.billid)+'-' +trim(str_billlist.templatesname), 'PaperSize' , string(obj_workbook.ActiveSheet.PageSetup.PaperSize))

//messagebox('系统提示','保存打印页面设置成功')
end event

public subroutine of_reset ();SetNull(obj_workbook)
SetNull(obj_sheet)
SetNull(obj_cell) 
SetNull(obj_xls)

SetNull(ole_con)



//uo_const.create_new_pwd( )


AllowEdit = FALSE

ifclose = FALSE


s_xls_templates s_temnull
s_tem = s_temnull


end subroutine

public subroutine of_set_userpwd (string arg_pwd);uo_const.pwd = arg_pwd
end subroutine

public subroutine of_throwexception (string arg_msg);runtimeerror err
err = Create runtimeerror
err.Text = arg_msg
err.objectname = this.classname( )
THROW err


end subroutine

private subroutine of_filldata ();
TRY
	Long i, rowcnt
	Long mxrow = 0
	Long mxbeginrow = 0
	Long ls_i,ll_j,ll_k
	Long signcnt = 0
	String strvalue,tempstr,Sign,Value
	oleobject obj_first
	
	String findsign
	findsign = uo_const.sign_begin+'*'+uo_const.sign_end //模糊查找标记
	
	IF IsNull(obj_workbook) THEN
		of_throwexception( "没有WorkBook对象")
	END IF
	obj_sheet = obj_workbook.activesheet //获取当前活动worksheet的引用
	
	FOR ls_i = 1 TO str_billlist.count //循环所有明细表格，查找相应明细标所在的位置
	
		IF str_billlist.bill[ls_i].parentid  = 0 THEN CONTINUE
		str_billlist.bill[ls_i].mxflag = obj_sheet.Cells.Find(str_billlist.bill[ls_i].billflag) //查找明细标记
		IF NOT IsNull(str_billlist.bill[ls_i].mxflag) THEN
			mxbeginrow = str_billlist.bill[ls_i].mxflag.row
		ELSE
			mxbeginrow = 0
		END IF
		
		IF mxbeginrow > 0 THEN //根据数据源的行数预先复制明细行到Excel
			rowcnt = str_billlist.bill[ls_i].ds_data.RowCount()
			
			Open(w_xls_sys_wait_jdt) //初始化进度条		
			w_xls_sys_wait_jdt.Show()
			w_xls_sys_wait_jdt.wf_accepttol(str_billlist.bill[ls_i].ds_data.RowCount() - 1)
			w_xls_sys_wait_jdt.st_msg.Text = "正在初始化模版......"
			FOR i = 1 TO rowcnt - 1 //根据明细行数复制明细行
				obj_sheet.rows(mxbeginrow).Copy()
				obj_sheet.rows(mxbeginrow).Insert(-4121)
				w_xls_sys_wait_jdt.wf_inc(i)
			NEXT
			Close(w_xls_sys_wait_jdt)
		END IF
	NEXT
	
	signcnt = 0
	obj_first = obj_sheet.Cells.Find(findsign)
	obj_cell = obj_first
	DO WHILE NOT IsNull(obj_cell) //循环查找所有标记，并把所有标记的引用放入数组
		signcnt++
		obj_flag[signcnt] = obj_cell
		obj_cell = obj_sheet.Cells.Find(findsign,obj_cell)
		IF IsNull(obj_cell) THEN EXIT
		IF obj_cell.Address = obj_first.Address THEN EXIT
	LOOP
	mxrow = 1
	Open(w_xls_sys_wait_jdt) //初始化进度条
	w_xls_sys_wait_jdt.Show()
	w_xls_sys_wait_jdt.wf_accepttol(signcnt)
	w_xls_sys_wait_jdt.st_msg.Text = "正在发送数据..."
	s_xls_bill str_bill
	
	FOR ls_i = 1 TO signcnt //循环所有标记引用
		obj_cell = obj_flag[ls_i]
		strvalue = Trim(obj_cell.Value)
		FOR ll_j = 1 TO str_billlist.count //循环查找标记引用里的所有标记，并进行内容替换
			str_bill = str_billlist.bill[ll_j]
			IF Pos(strvalue, str_bill.billflag) > 0  THEN
				strvalue  = of_replacestring(strvalue,str_bill.billflag,'')
			END IF
			IF strvalue = ''  OR Pos(strvalue,uo_const.sign_begin) <= 0 OR  Pos(strvalue,uo_const.sign_end ) <= 0  THEN EXIT
			IF NOT IsValid(str_bill.ds_data ) THEN CONTINUE
			IF str_bill.ds_data.RowCount( ) <= 0  THEN CONTINUE
			IF  str_bill.parentid = 0 THEN
				mxrow = 1
			ELSE
				IF IsNull(str_bill.mxflag ) THEN CONTINUE
				mxrow = obj_cell.row - (str_bill.mxflag.row - str_bill.ds_data.RowCount( ) )
				//				messagebox(string(mxrow),string(str_bill.mxflag.row ))
			END IF
			
			IF mxrow <= 0 OR mxrow > str_bill.ds_data.RowCount( ) THEN CONTINUE
			FOR ll_k = 1 TO str_bill.count
				IF Pos(Lower(strvalue),Lower(Trim(str_bill.column[ll_k].signnameformat)))  > 0 THEN
					Value = of_getvalue(str_bill.column[ll_k].columnname,str_bill.ds_data,mxrow)
					IF str_bill.column[ll_k].columntype = 1 THEN
						strvalue  = of_replacestring(strvalue,str_bill.column[ll_k].signnameformat,'')
						IF FileExists(Value) THEN
							decimal pic_width,pic_height
							decimal pic_left,pic_top
							obj_cell.SELECT
							obj_xls.Selection.Activate
							
							//按比例缩放图片
							pic_width = obj_xls.Selection.Width
							pic_height = obj_xls.Selection.Height
							obj_pic = obj_workbook.activesheet.Pictures.Insert(Value)
							obj_pic.SELECT
							IF obj_xls.Selection.ShapeRange.Height < pic_height AND obj_xls.Selection.ShapeRange.Width < pic_width THEN
								pic_height = obj_xls.Selection.ShapeRange.Height
								pic_width = obj_xls.Selection.ShapeRange.Width
							ELSE
								If (obj_xls.Selection.ShapeRange.Width / obj_xls.Selection.ShapeRange.Height) > (pic_width/pic_height) THEN
									pic_width = pic_width
									pic_height = pic_width / (obj_xls.Selection.ShapeRange.Width / obj_xls.Selection.ShapeRange.Height)
								ELSE
									pic_width = pic_height * (obj_xls.Selection.ShapeRange.Width / obj_xls.Selection.ShapeRange.Height)
									pic_height = pic_height
								END IF
							END IF
							
							pic_left = (obj_cell.Width - pic_width) / 2
							pic_top = (obj_cell.Height - pic_height) / 2 
							//
							obj_xls.Selection.ShapeRange.LockAspectRatio = FALSE
							obj_xls.Selection.ShapeRange.Height = pic_height
							obj_xls.Selection.ShapeRange.Width = pic_width
							obj_pic.Name = str_bill.column[ll_k].signnameformat+'['+String(mxrow)+']'  
//							obj_pic.Left = pic_left + obj_cell.Left
//							obj_pic.Top = pic_top + obj_cell.Top
							obj_xls.Selection.ShapeRange.left = pic_left + obj_cell.Left
							obj_xls.Selection.ShapeRange.Top = pic_top + obj_cell.Top
						END IF
					ELSE
						strvalue  = of_replacestring(strvalue,str_bill.column[ll_k].signnameformat,Value)
					END IF
					IF strvalue = ''  OR Pos(strvalue,uo_const.sign_begin) <= 0 OR  Pos(strvalue,uo_const.sign_end ) <= 0  THEN EXIT
				END IF
			NEXT
		NEXT
		obj_cell.Value = strvalue
		w_xls_sys_wait_jdt.wf_inc(ls_i)
	NEXT
	Close(w_xls_sys_wait_jdt)
Catch(runtimeerror err)
	IF IsValid(w_xls_sys_wait_jdt) THEN Close(w_xls_sys_wait_jdt)
	err.Text = '填充数据失败:~n'+err.Text
	throw err
END TRY




























end subroutine

public function boolean of_chksavepath (ref string arg_savepath);TRY
	
	IF IsNull(arg_savepath) THEN arg_savepath = ''
	
	arg_savepath = Trim(arg_savepath)
	
	IF arg_savepath = '' THEN
		This.of_throwexception(  '没有输出路径')
	END IF
	IF Pos(arg_savepath,":") = 0 THEN
		arg_savepath = CurrentDirectory +"\"+arg_savepath
	END IF
	
	Long Len = 1
	DO WHILE Pos(Right(arg_savepath,Len),'\') = 0
		Len++
		IF Len >= Len(arg_savepath) THEN EXIT
	LOOP
	Len = Len(arg_savepath)-Len
	IF Len <= 0 THEN
		This.of_throwexception( '保存Excel文件的路径不正确')
	END IF
	
	IF Not Directoryexists(Left(arg_savepath,Len)) THEN
		This.of_throwexception( '保存Excel文件的路径不正确')
	END IF
	
	IF FileExists(arg_savepath) THEN
		IF MessageBox ('系统提示',"目标模版已经存在，是否覆盖?",Question!,YesNo! ) = 2 THEN
			This.of_throwexception( '操作被取消')
		END IF
		IF Not FileDelete(arg_savepath) THEN
			This.of_throwexception( '目标文件可能正在使用，不能覆盖')
		END IF
	END IF	
	RETURN True
Catch(runtimeerror err)
	throw err
	RETURN False
END TRY




end function

public subroutine of_workbookunlock ();TRY
	obj_workbook.unProtect(uo_const.pwd)
	obj_workbook.activesheet.unProtect(uo_const.pwd )
	
Catch(runtimeerror err)
	err.text = '解锁WorkBook失败:~n'+err.text
	throw err
END TRY

end subroutine

public subroutine of_workbooklock ();TRY

	obj_workbook.Protect(uo_const.pwd,TRUE,false )
	obj_workbook.activesheet.Protect(uo_const.pwd,TRUE,TRUE,TRUE )
	if not enableselect then obj_workbook.activesheet.EnableSelection = 1
Catch(runtimeerror err)
err.text = '锁定WorkBook失败:~n'+err.text
throw err
END TRY




end subroutine

public subroutine of_showexcel (boolean arg_show);TRY
	//obj_xls.Visible = arg_show
Catch(runtimeerror err)
err.text = '隐藏Excel进程失败:~n'+err.text
	throw err
END TRY



end subroutine

public subroutine of_setpagebreakpreview ();//String 
TRY
	//	if of_connecttoexcel(arg_msg) <> 1 then
	//		return 0 
	//	end if
	//	obj_xls.visible = false
	IF Not obj_xls.isalive( ) THEN
		This.of_throwexception( '连接Excel失败')
	END IF
	obj_xls.Application.WindowState  = -4137
	obj_xls.ActiveWindow.WindowState  = -4137
	obj_xls.ActiveWindow.View = 2
	obj_xls.ActiveWindow.Zoom = 100
	obj_xls.Application.displayalerts = False
	
Catch(runtimeerror err)
	err.Text = '设置浏览模式失败:~n'+err.Text
	throw err
END TRY











end subroutine

public function string of_replacestring (string arg_orgstr, string arg_replacestr, string arg_replacevalue);Long 	i
if isnull(arg_orgstr) then arg_orgstr = ''
if isnull(arg_replacestr) then arg_replacestr = ''
if isnull(arg_replacevalue) then arg_replacevalue = ''
if arg_orgstr = ''  then return arg_orgSTR
if arg_replacestr = ''  then return arg_orgSTR
i = Pos(arg_orgSTR,arg_replacestr)
DO WHILE i > 0
	arg_orgSTR = Replace(arg_orgSTR,i,Len(arg_replacestr),arg_replacevalue)	
	i = Pos(arg_orgSTR,arg_replacestr)
LOOP
RETURN arg_orgSTR


end function

public function integer of_sendtoexcel (ref string arg_msg);Long rslt = 1

TRY
	String savepath = '',Filename = ''
	Long li_rc
	IF actionid = 3 Or actionid = 5 THEN
		li_rc = GetFileSaveName ( "保存文件", savepath, Filename,  "Excel",   "Excel Files(*.xls),*.xls,Excel 模版(*.xlt),*.xlt")
		IF li_rc <> 1 THEN
			RETURN 1
		END IF
	END IF
	of_activetemplate() //激活Excel
	of_showexcel(False) //隐藏窗口
	of_set_ScreenUpdating(False) //暂停更新界面
	of_set_caption(False) //隐藏窗口标题栏的删除按钮
	IF actionid <> 4 And actionid <> 5 THEN
		of_filldata() //填充数据			
	END IF
	//	IF of_setpagebreakpreview(arg_msg) <> 1 THEN RETURN 0 //设置浏览模式为页面预览
	
	IF Not AllowEdit THEN //锁定workbook
		of_workbooklock()
	END IF
	
	//get page setting from ini 
	//messagebox('','get page setting from ini' )
	
try 
	 obj_workbook.ActiveSheet.PageSetup.LeftMargin   = integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'LeftMargin', string(obj_workbook.ActiveSheet.PageSetup.LeftMargin)))
	 obj_workbook.ActiveSheet.PageSetup.RightMargin  =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'RightMargin', string(obj_workbook.ActiveSheet.PageSetup.RightMargin)))
	 obj_workbook.ActiveSheet.PageSetup.TopMargin    =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'TopMargin', string(obj_workbook.ActiveSheet.PageSetup.TopMargin)))
	 obj_workbook.ActiveSheet.PageSetup.BottomMargin =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'BottomMargin', string(obj_workbook.ActiveSheet.PageSetup.BottomMargin)))
	 obj_workbook.ActiveSheet.PageSetup.HeaderMargin =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'HeaderMargin', string(obj_workbook.ActiveSheet.PageSetup.HeaderMargin)))
	 obj_workbook.ActiveSheet.PageSetup.FooterMargin =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'FooterMargin', string(obj_workbook.ActiveSheet.PageSetup.FooterMargin)))
	 obj_workbook.ActiveSheet.PageSetup.Orientation  =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'Orientation', string(obj_workbook.ActiveSheet.PageSetup.Orientation)))

	
	if integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'PaperSize',string(obj_workbook.ActiveSheet.PageSetup.PaperSize)))  <> 256  then
			obj_workbook.ActiveSheet.PageSetup.PaperSize    =  integer(uo_constant.of_profilestring( string(str_billlist.billid)+'-' + trim(str_billlist.templatesname), 'PaperSize',string(obj_workbook.ActiveSheet.PageSetup.PaperSize))) //
	end if
Catch(runtimeerror er)
	//throw er
end try
	
	of_set_ScreenUpdating(True) //更新屏幕
	of_showexcel(True)
	
	CHOOSE CASE actionid
		CASE 0 //发送数据
			ifclose = False
			
			//保存设置的窗口
			if isvalid( this.ins_w_ole ) then
				this.ins_w_ole.ins_obj =this
				this.ins_w_ole.show()
			end if
			
			
		CASE 1 //打印预览	
			ifclose = True
			
			obj_workbook.printpreview
			
		CASE 2 //直接打印
			ifclose = True
			//打印设置
			//			obj_workbook.activesheet.pagesetup.PrintTitleRows = ""
			//			   obj_workbook.activesheet.pagesetup.PrintTitleColumns = ""
			//				obj_workbook.ActiveSheet.PageSetup.PrintArea = ""
			//        obj_workbook.ActiveSheet.PageSetup.LeftHeader = ""
			//        obj_workbook.ActiveSheet.PageSetup.CenterHeader = ""
			//        obj_workbook.ActiveSheet.PageSetup.RightHeader = ""
			//        obj_workbook.ActiveSheet.PageSetup.LeftFooter = ""
			//        obj_workbook.ActiveSheet.PageSetup.CenterFooter = ""
			//        obj_workbook.ActiveSheet.PageSetup.RightFooter = ""
			//        obj_workbook.ActiveSheet.PageSetup.LeftMargin = obj_workbook.Application.InchesToPoints(0.748031496062992)
			//        obj_workbook.ActiveSheet.PageSetup.RightMargin = obj_workbook.Application.InchesToPoints(0.748031496062992)
			//        obj_workbook.ActiveSheet.PageSetup.TopMargin = obj_workbook.Application.InchesToPoints(0.984251968503937)
			//        obj_workbook.ActiveSheet.PageSetup.BottomMargin = obj_workbook.Application.InchesToPoints(0.984251968503937)
			//        obj_workbook.ActiveSheet.PageSetup.HeaderMargin = obj_workbook.Application.InchesToPoints(0.511811023622047)
			//        obj_workbook.ActiveSheet.PageSetup.FooterMargin = obj_workbook.Application.InchesToPoints(0.511811023622047)
			//        obj_workbook.ActiveSheet.PageSetup.Orientation = 1
			//        obj_workbook.ActiveSheet.PageSetup.PaperSize = 9
			//        obj_workbook.ActiveSheet.PageSetup.PrintHeadings = False
			//        obj_workbook.ActiveSheet.PageSetup.PrintGridlines = False
			//        obj_workbook.ActiveSheet.PageSetup.PrintComments =   -4142 
			//        obj_workbook.ActiveSheet.PageSetup.PrintQuality = 600
			//        obj_workbook.ActiveSheet.PageSetup.CenterHorizontally = False
			//        obj_workbook.ActiveSheet.PageSetup.CenterVertically = False
			//        obj_workbook.ActiveSheet.PageSetup.Draft = False
			//        obj_workbook.ActiveSheet.PageSetup.FirstPageNumber = xlAutomatic
			//        obj_workbook.ActiveSheet.PageSetup.Order = 1
			//        obj_workbook.ActiveSheet.PageSetup.BlackAndWhite = False
			//        obj_workbook.ActiveSheet.PageSetup.Zoom = 100
			//        obj_workbook.ActiveSheet.PageSetup.PrintErrors = 0	
			obj_workbook.activesheet.printout()
		CASE 3 //发送数据并保存
			
			ifclose = True
			of_set_ScreenUpdating(False) //暂停更新界面
			of_chksavepath(savepath)
			obj_workbook.SaveAs(savepath)
			of_set_ScreenUpdating(True) //更新界面
		CASE 4 //浏览模板
			ifclose = False
		CASE 5 //保存模板
			ifclose = True
			of_set_ScreenUpdating(False) //暂停更新界面
			of_chksavepath(savepath)
			obj_workbook.SaveAs(savepath)
			of_set_ScreenUpdating(True) //更新界面
	END CHOOSE
	
	uo_api.u_setwindowpos( xls_handle)
	IF Not IsNull(ole_con) And ifclose THEN //自动关闭窗口
		obj_workbook.Close()
	END IF

if isvalid( this.ins_w_ole ) then this.ins_w_ole.wf_setwinpos()

Catch(runtimeerror err)
	IF IsValid(obj_workbook) THEN
		obj_workbook.Close()
	END IF
	arg_msg = err.Text
	rslt = 0
END TRY


RETURN rslt




end function

public subroutine of_script ();//保存图片

//用Clipboard
//
//Dim Picture As StdPicture
//...
//Selection.Copy
//Picture = Clipboard.GetData() 
//SavePicture Picture, "c:\myPic.bmp"
//...


//
//Excel VBA cell的一些属性和toolsbar
//ken_kang 发表于 2007-7-5 16:25:00  
//    sht.Cells(1, i).Interior.ColorIndex = 15 ‘cell的颜色
//    sht.Cells(1, i).VerticalAlignment = 1'cell内向上还是向下靠齐
//    sht.Cells(1, i).WrapText = True’cell内的文字是否自动回行
//    sht.Cells(1, i).Borders. = "1"‘表格的边界，1正常的那种
//    sht.Cells(1, i).Borders.Weight = "3"’表格的粗细
//
//
//sht.row(1).insert 在第一行的上面插入一行。
//
//
//sht.Rows("7:7").Select  '选定EXCEL表的第7行
//sht.Selection.Delete shift:=xlUp '删除选定的行
//
//
//实施打印的语句：
//
//
//ActiveSelectedSheets.PrintOut copies:=TextBox1.Text ‘根据打印份数输出到打印机
//
//Toolsbar
//
//
//    Set ourToolBar = CommandBars.Add(Name:="mybar", Position:=msoBarTop)
//    ourToolBar.Visible = True
//    ourToolBar.Enabled = True
//    ourToolBar.Controls.Add Type:=msoControlButton
//    With ourToolBar.Controls(1)
//        .Width = 60
//'        .Style = msoButtonIcon
//        .OnAction = "Start"
//        .TooltipText = "Pls open the xls file and click Start"
//        .Caption = "Process the format of s"
//        .FaceId = 111
//        .Visible = True
//    End With 
// 
//
end subroutine

public subroutine of_activetemplate ();	try
	
	ole_con.Activate( OffSite!) //激活excel
	obj_workbook = ole_con.Object //Workbook对象的引用
	obj_xls = obj_workbook.Application
	obj_xls.DisplayAlerts = False  //不显示特定的警告和消息
	xls_handle = obj_xls.Hwnd  //窗口句柄
catch(runtimeerror err)
err.text ='激活Excel失败~n'+err.text

end try


end subroutine

public subroutine of_set_caption (boolean arg_flag);TRY
	return
	Long lWnd
	lWnd  = uo_api.GetWindowLong(xls_handle, uo_api.GWL_STYLE)
	IF arg_flag THEN
		lWnd += uo_api.WS_CAPTION
	ELSE
		lWnd -= uo_api.WS_CAPTION
	END IF
	uo_api.SetWindowLong(xls_handle,uo_api.GWL_STYLE,lWnd )	
Catch(runtimeerror err)
	err.text  = '隐藏窗口标题栏失败:~n'+err.text
throw err
END TRY




end subroutine

public subroutine of_setxlsmax ();
//String 
TRY
	obj_xls.Application.WindowState  = -4137
	obj_xls.ActiveWindow.WindowState  = -4137
	obj_xls.ActiveWindow.View = 1
	obj_xls.ActiveWindow.Zoom = 100
Catch(runtimeerror err)
err.text = '最大化Excel失败~n'+err.text
	throw err
END TRY








end subroutine

public subroutine of_set_screenupdating (boolean arg_flag);//String 
TRY
//	if of_connecttoexcel() <> 1 then
//		return 0 
//	end if
	obj_xls.Application.ScreenUpdating =  arg_flag

Catch(runtimeerror err)
err.text = '关闭屏幕更新失败:~n'+err.text
throw err
END TRY



end subroutine

public subroutine of_setsign (string arg_signname, integer arg_flag, integer arg_type, string arg_billflag, string arg_billname, boolean arg_chk, boolean arg_addchk);
TRY
	String arg_msg,Value
	oleobject obj_mxcell
	obj_cell = obj_xls.ActiveCell
	obj_sheet = obj_workbook.activesheet
	IF IsNull(obj_cell) THEN
		This.of_throwexception( '请选定要填充标记的单元格')
	END IF
	String ls_msg = ''
	IF arg_chk THEN
		IF  arg_addchk THEN
			ls_msg = '是否确定要把标记"'+arg_signname+'"追加到单元格('+String(obj_cell.Address()  )+')'
		ELSE
			ls_msg = '是否确定要把标记"'+arg_signname+'"覆盖到单元格('+String(obj_cell.Address()  )+')'
			
		END IF
		IF MessageBox ('系统提示',ls_msg,Question!,YesNo! ) = 2 THEN
			RETURN
		END IF
	END IF
	
	IF arg_flag = 0  THEN
		IF IsNull(obj_cell.Value) THEN
			obj_cell.Value = arg_signname
		ELSE
			Value = Trim(String(obj_cell.Value))
			IF  arg_addchk THEN
				IF Pos(Value, arg_signname) > 0 THEN
					This.of_throwexception( '请不要在同一单元格里追加重复标记')
				END IF
				obj_cell.Value = Value + arg_signname
			ELSE
				obj_cell.Value =  arg_signname
			END IF
		END IF
	ELSEIF arg_flag = 1 THEN
		obj_mxcell = obj_sheet.Cells.Find(arg_billflag)
		//		messagebox(arg_signname,arg_billflag)
		IF arg_signname = arg_billflag THEN
			IF  Not IsNull(obj_mxcell) THEN
				This.of_throwexception('模版中已经存在明细表'+arg_billname+'的标记，请不要重复添加!')
			ELSE
				IF Not IsNull(obj_cell.Value ) THEN
					IF MessageBox ('系统提示','是否确定要清空单元格('+String(obj_cell.Address()  )+')的内容以填充明细表'+arg_billname+'的标记',Question!,YesNo! ) = 2 THEN
						RETURN
					END IF
				END IF
				obj_cell.Value = arg_signname
				obj_mxcell = obj_cell
			END IF
		ELSE
			IF IsNull(obj_mxcell) THEN
				This.of_throwexception('要添加明细表'+arg_billname+'的项，请先添加明细标记!')
			END IF
			IF obj_mxcell.row <> obj_cell.row THEN
				This.of_throwexception('明细项必须添加到明细标记'+arg_billname+'所在行!')
			END IF
			IF obj_mxcell.column <= obj_cell.column THEN
				String str
				Long cnt = 1
				cnt = 1
				IF IsNull(obj_sheet.Cells(obj_mxcell.row,obj_cell.column +cnt  ).Value) THEN
					str = ''
				ELSE
					str = Trim(obj_sheet.Cells(obj_mxcell.row,obj_cell.column +cnt  ).Value)
				END IF
				DO WHILE  str <> ''
					cnt ++
					IF cnt > 1000 THEN
						This.of_throwexception('系统出现异常，移动明细标记失败，请重试!')
					END IF
					IF IsNull(obj_sheet.Cells(obj_mxcell.row,obj_cell.column +cnt  ).Value) THEN
						str = ''
					ELSE
						str = Trim(obj_sheet.Cells(obj_mxcell.row,obj_cell.column +cnt  ).Value)
					END IF
				LOOP
				obj_sheet.Cells(obj_mxcell.row,obj_cell.column +cnt  ).Value	 = obj_mxcell.Value
				obj_mxcell.Value = ''
			END IF
			IF IsNull(obj_cell.Value) THEN
				obj_cell.Value = arg_signname
			ELSE
				Value = String(obj_cell.Value)
				IF  arg_addchk And  Pos(Value, arg_signname) > 0 THEN
					This.of_throwexception('请不要在同一单元格里插入重复标记!')
				END IF
				IF  arg_addchk THEN
					obj_cell.Value = Value + arg_signname
				ELSE
					obj_cell.Value =  arg_signname
				END IF
			END IF
		END IF
	END IF
Catch(runtimeerror err)
	throw err
	return
END TRY














end subroutine

public subroutine of_setexcelwindowsize (integer arg_windowstate);TRY
	IF arg_WindowState = 0 THEN
		IF Not IsNull(obj_xls) THEN
			IF obj_xls.isalive( ) THEN
				//obj_xls.ActiveWindow.WindowState  = -4137
				obj_xls.Application.WindowState = -4140				
			END IF
		END IF
	ELSE
		IF Not IsNull(obj_xls) THEN
			IF obj_xls.isalive( ) THEN
				//obj_xls.ActiveWindow.WindowState  = -4137
				IF obj_xls.Application.WindowState = -4140  THEN
					obj_xls.Application.WindowState = -4143
				END IF
			END IF
		END IF
	END IF
	
Catch(runtimeerror err)
	throw err	
END TRY


end subroutine

private function string of_getvalue (string arg_columnname, datastore arg_ds, long arg_row);String result = ''
result = arg_ds.Describe("Evaluate('Lookupdisplay("+arg_columnname+")'," + String (arg_row) + ")")
IF IsNull(result) THEN result = ''
result = trim(result)
RETURN result

end function

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

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

event constructor; uo_api = create uo_win32api 

  uo_constant =create uo_xls_constant

//if use_userpwd then  uo_const.pwd = userpwd
  
 CurrentDirectory = uo_api.u_getcurrentdirectory( )  
 of_reset()
 
  
end event

event destructor;IF IsValid(w_xls_sys_wait_jdt) THEN Close(w_xls_sys_wait_jdt)
String arg_msg
TRY
	of_set_caption(True)
	//obj_workbook.Close()
	//obj_xls.Close()
	uo_api.SetWindowPos(xls_handle,1,0,0,0,0,3)
Catch(runtimeerror err)
END TRY

end event