1/3 wscript(vbs) 控制 OpenOffice 建立日期 :2009/11/06 作者 :cschen33.tw@gmail.com 目前大部份的程式 (ERP) 會呼叫 office(word 或是 excel) 來產生要的報表, 因為免費軟體 OpenOffice 已經越來越穩定, 是不是也可以使用 OpenOffice 取代 Office 嗎? 目前在網路上找到一篇 http://wiki.services.openoffice.org/wiki/documentation/devguide/prouno/bridge/automation_bridge http://www.kalitech.fr/clients/doc/vb_apiooo_en.html 想要依樣畫葫蘆但是沒有 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(1000.32) 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/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(1000.32) 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 陣列 ' 定義, 是必須的,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 1000.32 2. Call osheet.getcellbyposition(2,2).setvalue(1000.32) 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
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 4.5.4 (2356) www.pspad.com 2009/11/6 下上 12:44:44 cs_chen
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", 13421823 42: 43: ' 設定第一列使用不同的背景顏色 44: objrow.setpropertyvalue "BackTransparent", false 45: objrow.setpropertyvalue "BackColor", 6710932 46: 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 22.5 55: objtable.getcellbyname("b2").setvalue 5615.3 56: objtable.getcellbyname("c2").setvalue -2315.7 57: objtable.getcellbyname("d2").setformula"sum " 58: 59: objtable.getcellbyname("a3").setvalue 21.5 60: objtable.getcellbyname("b3").setvalue 615.3 61: objtable.getcellbyname("c3").setvalue -315.7 62: objtable.getcellbyname("d3").setformula "sum " 63: 64: objtable.getcellbyname("a4").setvalue 121.5 65: objtable.getcellbyname("b4").setvalue -615.3 66: objtable.getcellbyname("c4").setvalue 415.7 67: objtable.getcellbyname("d4").setformula "sum " 68: 69: 'Change the CharColor and add a Shadow 70: objcursor.setpropertyvalue "CharColor", 255 71: objcursor.setpropertyvalue "CharShadowed", true 72: 73: 'Create a paragraph break PSPad editor 4.5.4 (2356) www.pspad.com 2009/11/6 下下 12:46:23 cs_chen
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= 15000 90: objsize.height= 400 91: 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", 65536 117: 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",16777215 132: 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 4.5.4 (2356) www.pspad.com 2009/11/6 下下 12:46:23 cs_chen
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 4.5.4 (2356) www.pspad.com 2009/11/6 下上 12:45:48 cs_chen