2/3 以改成 wscript.exe 來執行對程式按滑鼠右鍵選開起檔案, 選擇你要的軟體, 若沒有可以選擇 windows 安裝路徑下的 system32\wscript.exe 第一個範例 : excel 比較簡單, 我們先說明之 ' 第一個定意新的物件 com.sun.star.service

Size: px

Start display at page:

Download "2/3 以改成 wscript.exe 來執行對程式按滑鼠右鍵選開起檔案, 選擇你要的軟體, 若沒有可以選擇 windows 安裝路徑下的 system32\wscript.exe 第一個範例 : excel 比較簡單, 我們先說明之 ' 第一個定意新的物件 com.sun.star.service"

节蔡
9 years ago
Views:

1 1/3 wscript(vbs) 控制 OpenOffice 建立日期 :2009/11/06 作者目前大部份的程式 (ERP) 會呼叫 office(word 或是 excel) 來產生要的報表, 因為免費軟體 OpenOffice 已經越來越穩定, 是不是也可以使用 OpenOffice 取代 Office 嗎? 目前在網路上找到一篇想要依樣畫葫蘆但是沒有 scalc, 後來也找到好幾篇, 但是根據文章來實作, 發現有很多問題都不可行, 因此自已錄製 OOo 的巨集, 並猜測試可行! 要求支援的作業平台 (Windows 95, 98, NT4, ME, 2000, XP) 快速入門 1.calc Set osm =CreateObject("com.sun.star.ServiceManager") 'Set ocr = osm.createinstance("com.sun.star.reflection.corereflection") Set odesktop = osm.createinstance("com.sun.star.frame.desktop") Dim aargs() 2.writer Set odoc = odesktop.loadcomponentfromurl("private:factory/scalc","_blank",0,aargs) Set osheet = odoc.getsheets().getbyindex(0) Call osheet.getcellbyposition(0,0).setvalue( ) Call osheet.getcellbyposition(0,1).setstring("abc") Dim osm Dim odesk Dim arg() 'Instanciate OOo : this line is mandatory with VB for OOo API Set osm = CreateObject("com.sun.star.ServiceManager") 'Create the first and most important service Set odesk = osm.createinstance("com.sun.star.frame.desktop") ' 建立一個新的文件 Set odoc = odesk.loadcomponentfromurl("private:factory/swriter", "_blank", 0, arg()) ' 關閉這個文件 ' 開啟一個已經存在的檔案 ( 請注意第一個參數 ) Set odoc = odesk.loadcomponentfromurl("file:///c:/dev/ooo/test.doc", "_blank", 0, arg()) ' 儲存檔案 Call odoc.storetourl("file:///c:/dev/ooo/test2.sxw", arg()) ' 關閉檔案將上述兩檔存成副檔名 vbs, 就可以直接執行, 有人執行會出現 dos 的視窗, 是因為它是使用 cscript.exe 來執行, 你可

2 2/3 以改成 wscript.exe 來執行對程式按滑鼠右鍵選開起檔案, 選擇你要的軟體, 若沒有可以選擇 windows 安裝路徑下的 system32\wscript.exe 第一個範例 : excel 比較簡單, 我們先說明之 ' 第一個定意新的物件 com.sun.star.servicemanager Set osm = CreateObject("com.sun.star.ServiceManager") ' 建立一個實體 Set odesktop = osm.createinstance("com.sun.star.frame.desktop") ' 定義讀取參數, 我們不會變, 因此定義的空陣列, 一般寫入才會變 Dim aargs() ' 設定要開啟新的檔案 calc Set odoc = odesktop.loadcomponentfromurl("private:factory/scalc","_blank",0,aargs) ' 取得第一個空白頁 Set osheet = odoc.getsheets().getbyindex(0) ' 輸入值,SetValue 是設定數字,SetString 是設定文字 Call osheet.getcellbyposition(0,0).setvalue( ) Call osheet.getcellbyposition(0,1).setstring("abc") 第二個範例 : ' 這裏有一個多了定義, 其實也可以不用定義, 在 wscript 不需要定義 as object 否則會有錯誤訊息產生 Dim osm 'Root object for accessing OpenOffice from VB Dim odesk, odoc As Object 'First objects from the API ' 定義讀取參數, 我們不會變更, 因此定義空陣列, 一般寫入才會變 Dim arg() ' 第一個定意新的物件 com.sun.star.servicemanager Set osm = CreateObject("com.sun.star.ServiceManager") ' 建立一個實體 Set odesk = osm.createinstance("com.sun.star.frame.desktop") ' 開啟一個新的文件 writer Set odoc = odesk.loadcomponentfromurl("private:factory/swriter", "_blank", 0, arg()) ' 關閉這個文件 ' 開啟一個已經存在的檔案 (pay attention to the syntax for first argument) Set odoc = odesk.loadcomponentfromurl("file:///c:/dev/ooo/test.doc", "_blank", 0, arg()) ' 儲存檔案, 因為我們沒有要變更模式因此不變更寫入參數 Call odoc.storetourl("file:///c:/dev/ooo/test2.sxw", arg()) ' 關閉檔案 ' 刪除物件特殊函式在 OOo API 中有一些屬性的類型是 structure, 它是使用者自定的類型, 並無法直接存取, 要使用 Bridge_GetStruct 來取得屬性, 及指定屬性 ' ' 建立及設定 com.sun.star.beans.propertyvalue 屬性 ' Public Function MakePropertyValue(cName, uvalue) Dim ostruct, oservicemanager Set oservicemanager = CreateObject("com.sun.star.ServiceManager") Set ostruct = oservicemanager.bridge_getstruct("com.sun.star.beans.propertyvalue") ostruct.name = cname ostruct.value = uvalue ' 設定傳回值 Set MakePropertyValue = ostruct End Function

3 3/3 陣列 ' 定義, 是必須的,vbs 可以定義空的陣列, 但是 autoit3 不可以定義空的陣列 Dim stest(0) ' 指定 Set stest(0) = MakePropertyValue("ToPoint","$A$4") ' 引用 stest() #wscript 的定義陣列, 我試了好久, 找一下資料才找到 Dim abc(3) 可以存 4 個值 abc(0),abc(1),abc(2),abc(3) Dim abc(0) 可以存 1 個值輸入值的方法 1. osheet.getcellbyposition(0,0).setvalue Call osheet.getcellbyposition(2,2).setvalue( ) 3. Call osheet.getcellbyposition(1,1).setstring("abc") 4. osheet.getcellbyposition(1,1).setstring "abc" 5. osheet.getcellrangebyname("b1").value = 12 1 和 2 是相同的 3 和 4 是相同的選取選取 B3 call odesktop.currentcomponent.currentcontroller.select(osheet.getcellrangebyname("b3")) 選取 B3:G5(B3 到 G5) call odesktop.currentcomponent.currentcontroller.select(osheet.getcellrangebyname("b3:g5")) ' 置中 Dim t3(0) set t3(0) = MakePropertyValue("HorizontalAlignment", 2) call dispatcher.executedispatch(document, ".uno:horizontalalignment", "", 0, t3) ' 原 OOo 巨集中的 com.sun.star.table.cellhorijustify.center 的值是無法直接取得, 因此使用數字來取代 '0 正常,1 靠左,2 置中,3 靠右 ' 移動 ' 移動到 a3:f17, 這是一個選取的空間 set t4(0) = MakePropertyValue("ToPoint","$A$3:$F$17") call dispatcher.executedispatch(document, ".uno:gotocell","",0,t4) ' 開啟檔案 ' 路徑 c:\tmp\sample.rtf 變更為 file:///c \tmp\sample.rtf, 其中代表 : Set odoc = odesktop.loadcomponentfromurl("file:///c \tmp\sample.rtf","_blank",0,aargs) 附件共 3 件 1.scalc.vbs 2.swriter.vbs 3.autoit3.vbs

4 scalce.vbs 頁頁 :1/1 C:\Documents and Settings\cs_chen\ 桌頁 \svn\doc\openoffice\ 上上上上上上 : 2009/11/6 上上 08:55:22 1: ' 建立 com 2: Set osm = CreateObject("com.sun.star.ServiceManager") 3: ' 建立實體 4: Set odesktop = osm.createinstance("com.sun.star.frame.desktop") 5: ' 定義陣列,vbs 的陣列定義和一般不同 : 如 t1(0), 可以存 1 個 0->0 一個, 而 t1(1), 可以存 2 個 0->1 6: Dim aargs(),t1(0),t2(7),t3(0),t4(0),saveparam(0) 7: Set odoc = odesktop.loadcomponentfromurl("private:factory/scalc","_blank",0,aargs) 8: Set osheet = odoc.getsheets().getbyindex(0) 9: Set document = odesktop.currentcomponent.currentcontroller.frame 10: Set dispatcher = osm.createinstance("com.sun.star.frame.dispatchhelper") 11: 12: ' 輸入數字 13: osheet.getcellbyposition(0,0).setvalue 1 14: Call osheet.getcellbyposition(0,1).setvalue(2) 15: osheet.getcellrangebyname("a3").value = 3 16: osheet.getcellrangebyname("a4").setvalue 4 17: 18: ' 輸入字串 19: osheet.getcellbyposition(1,0).setstring "a" 20: Call osheet.getcellbyposition(1,1).setstring("b") 21: 22: ' 讀取值 23: ocell = osheet.getcellrangebyname("b3").value 24: MsgBox ocell 25: 26: ' 選取欄位並輸入文字 27: osheet.getcellrangebyname("d8").setstring( " 中文字測試 ") 28: 29: ' 設字型大小 30: call odesktop.currentcomponent.currentcontroller.select(osheet.getcellrangebyname("b3:g5")) 31: set t1(0) = MakePropertyValue("FontHeight.Height",14) 32: call dispatcher.executedispatch( document, ".uno:fontheight","", 0, t1) 33: 34: ' 設定邊界 35: set t2(0) = MakePropertyValue("OuterBorder.LeftBorder",Array(0,0,2,0)) 36: set t2(1) = MakePropertyValue("OuterBorder.LeftDistance",0) 37: set t2(2) = MakePropertyValue("OuterBorder.RightBorder",Array(0,0,2,0)) 38: set t2(3) = MakePropertyValue("OuterBorder.RightDistance",0) 39: set t2(4) = MakePropertyValue("OuterBorder.TopBorder",Array(0,0,2,0)) 40: set t2(5) = MakePropertyValue("OuterBorder.TopDistance",0) 41: set t2(6) = MakePropertyValue("OuterBorder.BottomBorder",Array(0,0,2,0)) 42: set t2(7) = MakePropertyValue("OuterBorder.BottomDistance",0) 43: call dispatcher.executedispatch( document, ".uno:setborderstyle","", 0, t2) 44: 45: ' 移動 46: set t4(0) = MakePropertyValue("ToPoint","$A$3:$F$17") 47: call dispatcher.executedispatch(document, ".uno:gotocell","",0,t4) 48: 49: ' 置中 50: 'com.sun.star.table.cellhorijustify.center 的值是無法直接取得, 因此使用數字來取代 51: '0 正常,1 靠左,2 置中,3 靠右 52: set t3(0) = MakePropertyValue("HorizontalAlignment", 2) 53: call dispatcher.executedispatch(document, ".uno:horizontalalignment", "", 0, t3) 54: 55: 56: ' 存成 PDF 57: Set SaveParam(0) = MakePropertyValue("FilterName", "writer_pdf_export") 58: Call odoc.storetourl("file:///c:/tmp/testdoc.pdf", SaveParam) 59: 60: Function MakePropertyValue(cName, uvalue) 61: Dim opropertyvalue 62: Dim osm 63: Set osm = CreateObject("com.sun.star.ServiceManager") 64: Set opropertyvalue = osm.bridge_getstruct("com.sun.star.beans.propertyvalue") 65: opropertyvalue.name = cname 66: opropertyvalue.value = uvalue 67: Set MakePropertyValue = opropertyvalue 68: 69: End Function 70: 71: PSPad editor (2356) /11/6 下上 12:44:44 cs_chen

5 writer.vbs 頁頁 :1/2 C:\Documents and Settings\cs_chen\ 桌頁 \svn\doc\openoffice\ 上上上上上上 : 2009/11/4 下下 05:24:10 1: 'VBScript 範例 2: 'The service manager is always the starting point 3: ' 定義 service manager 4: Set objservicemanager= WScript.CreateObject("com.sun.star.ServiceManager") 5: 6: ' 建立實體 7: Set objcorereflection= objservicemanager.createinstance("com.sun.star.reflection.corereflection") 8: 9: ' 建立在桌面 10: Set objdesktop= objservicemanager.createinstance("com.sun.star.frame.desktop") 11: 12: ' 開啟新的文件 13: Dim args() 14: Set objdocument= objdesktop.loadcomponentfromurl("private:factory/swriter", "_blank", 0, args) 15: 16: ' 定義文字物件 17: Set objtext= objdocument.gettext 18: 19: ' 建立一個暫時的物件 20: Set objcursor= objtext.createtextcursor 21: 22: ' 插入一些文字 23: objtext.insertstring objcursor, " 第一行在新建的文件中." & vblf, false 24: 25: ' 再插入第二行 26: objtext.insertstring objcursor, "Now we're in the second line", false 27: 28: ' 建立一個文字表格 4*4 29: Set objtable= objdocument.createinstance( "com.sun.star.text.texttable") 30: objtable.initialize 4, 4 31: 32: ' 插入表格 33: objtext.inserttextcontent objcursor, objtable, false 34: 35: ' 取得一列 36: Set objrows= objtable.getrows 37: Set objrow= objrows.getbyindex( 0) 38: 39: ' 設定表格的背景顏色 40: objtable.setpropertyvalue "BackTransparent", false 41: objtable.setpropertyvalue "BackColor", : 43: ' 設定第一列使用不同的背景顏色 44: objrow.setpropertyvalue "BackTransparent", false 45: objrow.setpropertyvalue "BackColor", : 47: ' 填入表格第一列 48: insertintocell "A1","FirstColumn", objtable 49: 'insertintocell is a helper function, see below 50: insertintocell "B1","SecondColumn", objtable 51: insertintocell "C1","ThirdColumn", objtable 52: insertintocell "D1","SUM", objtable 53: 54: objtable.getcellbyname("a2").setvalue : objtable.getcellbyname("b2").setvalue : objtable.getcellbyname("c2").setvalue : objtable.getcellbyname("d2").setformula"sum " 58: 59: objtable.getcellbyname("a3").setvalue : objtable.getcellbyname("b3").setvalue : objtable.getcellbyname("c3").setvalue : objtable.getcellbyname("d3").setformula "sum " 63: 64: objtable.getcellbyname("a4").setvalue : objtable.getcellbyname("b4").setvalue : objtable.getcellbyname("c4").setvalue : objtable.getcellbyname("d4").setformula "sum " 68: 69: 'Change the CharColor and add a Shadow 70: objcursor.setpropertyvalue "CharColor", : objcursor.setpropertyvalue "CharShadowed", true 72: 73: 'Create a paragraph break PSPad editor (2356) /11/6 下下 12:46:23 cs_chen

6 writer.vbs 頁頁 :2/2 C:\Documents and Settings\cs_chen\ 桌頁 \svn\doc\openoffice\ 上上上上上上 : 2009/11/4 下下 05:24:10 74: 'The second argument is a com::sun::star::text::controlcharacter::paragraph_break constant 75: objtext.insertcontrolcharacter objcursor, 0, false 76: 77: 'Inserting colored Text. 78: objtext.insertstring objcursor, " This is a colored Text - blue with shadow" & vblf, false 79: 80: 'Create a paragraph break ( ControlCharacter::PARAGRAPH_BREAK). 81: objtext.insertcontrolcharacter objcursor, 0, false 82: 83: 'Create a TextFrame. 84: Set objtextframe= objdocument.createinstance("com.sun.star.text.textframe") 85: 86: 'Create a Size struct. 87: Set objsize= createstruct("com.sun.star.awt.size") 88: 'helper function, see below 89: objsize.width= : objsize.height= : objtextframe.setsize( objsize) 92: 93: ' TextContentAnchorType.AS_CHARACTER = 1 94: objtextframe.setpropertyvalue "AnchorType", 1 95: 96: 'insert the frame 97: objtext.inserttextcontent objcursor, objtextframe, false 98: 99: 'Get the text object of the frame 100: Set objframetext= objtextframe.gettext 101: 102: 'Create a cursor object 103: Set objframetextcursor= objframetext.createtextcursor 104: 105: 'Inserting some Text 106: objframetext.insertstring objframetextcursor, "The first line in the newly created text frame.", _ 107: false 108: objframetext.insertstring objframetextcursor, _ 109: vblf & "With this second line the height of the frame raises.", false 110: 111: 'Create a paragraph break 112: 'The second argument is a com::sun::star::text::controlcharacter::paragraph_break constant 113: objframetext.insertcontrolcharacter objcursor, 0, false 114: 115: 'Change the CharColor and add a Shadow 116: objcursor.setpropertyvalue "CharColor", : objcursor.setpropertyvalue "CharShadowed", false 118: 119: 'Insert another string 120: objtext.insertstring objcursor, " That's all for now!!", false 121: 122: On Error Resume Next 123: If Err Then 124: MsgBox "An error occurred" 125: End If 126: 127: 128: Sub insertintocell( strcellname, strtext, objtable) 129: Set objcelltext= objtable.getcellbyname( strcellname) 130: Set objcellcursor= objcelltext.createtextcursor 131: objcellcursor.setpropertyvalue "CharColor", : objcelltext.insertstring objcellcursor, strtext, false 133: End Sub 134: 135: Function createstruct( strtypename) 136: Set classsize= objcorereflection.forname( strtypename) 137: Dim astruct 138: classsize.createobject astruct 139: Set createstruct= astruct 140: End Function 141: 142: PSPad editor (2356) /11/6 下下 12:46:23 cs_chen

7 autoit3.au3 C:\Documents and Settings\cs_chen\ 桌頁 \svn\doc\openoffice\ 1: #include <Array.au3> 2: Global $setoooprop 3: $osm=objcreate("com.sun.star.servicemanager") 4: if IsObj($oSM) Then 5: $odesktop = $osm.createinstance("com.sun.star.frame.desktop") 6: 7: If IsObj($oDesktop) Then 8: Dim $aargs[1] 9: Dim $t1[1] 10: Dim $t2[1] 11: $aargs[0] = setprop("readonly",false) 12: ;$aargs[1] = setprop("password","secret") 13: ;$aargs[2] = setprop("hidden",false) 14: $odoc = $odesktop.loadcomponentfromurl("private:factory/scalc","_blank",0,$aargs) 15: $document = $odesktop.currentcomponent.currentcontroller.frame 16: $dispatcher = $osm.createinstance("com.sun.star.frame.dispatchhelper") 17: If IsObj($oDoc) Then 18: $osheet = $odoc.getsheets().getbyindex(0) 19: $osheet.getcellbyposition(5,5).setstring( " 中文字測試 ") 20: $odesktop.currentcomponent.currentcontroller.select select($osheet.getcellbyposition(5,5)) 21: $t2[0] = setprop("fontheight.height",14) 22: $dispatcher.executedispatch($document,".uno:fontheight","",0,$t2) 23: $t1[0] = setprop("topoint","a3:f17") 24: $dispatcher.executedispatch($document,".uno:gotocell","",0,$t1) 25: Else 26: MsgBox(0,"oDoc","not object") 27: EndIf 28: Else 29: MsgBox(0,"oDesktop","not object") 30: EndIf 31: Else 32: MsgBox(0,"oExcel","not object") 33: EndIf 34: 35: Func setprop($cname, $uvalue) 36: $osm = ObjCreate("com.sun.star.ServiceManager") 37: $opropertyvalue = $osm.bridge_getstruct("com.sun.star.beans.propertyvalue") 38: $opropertyvalue.name = $cname 39: $opropertyvalue.value = $uvalue 40: $setoooprop = $opropertyvalue 41: Return $setoooprop 42: EndFunc 43: 頁頁 :1/1 上上上上上上 : 2009/11/6 上上 08:47:14 PSPad editor (2356) /11/6 下上 12:45:48 cs_chen

1 1 Excel VBA 說明 ( ) (_) STEP4 Excel 2 STEP5 A1 1 B2 2 C3 3 STEP6 A1 STEP7 > > 1-11

1-3 1-3-1 Excel VBA VBA OK CD DVD Excel VBA Excel VBA Excel Visual Basic A1 1 B2 2 C3 3 STEP1 Excel Ch01_VBA.xlsm 1 > > STEP2 Excel 1 2 STEP3 1-10 1 1 Excel VBA 說明 ( ) (_) STEP4 Excel 2 STEP5 A1 1 B2 2