Difference between revisions of "Office Automation"
(Added Python UNO topic) |
|||
Line 4: | Line 4: | ||
== Using the OpenOffice UNO Bridge == | == Using the OpenOffice UNO Bridge == | ||
− | OpenOffice has language bindings for C++, Java and Python | + | OpenOffice has language bindings for C++, Java, JavaScript and Python. On Windows, OpenOffice can also be manipulated in Pascal via COM Automation (see below), but there is currently no easy way of using OpenOffice's UNO (Universal Network Objects) from Pascal on OS X and Linux. If you're interested in developing an OO "bridge" for Pascal, please refer to these links for more information (caution: these links are quite techie in true Sun fashion): |
[http://api.openoffice.org/ api.openoffice.org] | [http://api.openoffice.org/ api.openoffice.org] | ||
[http://wiki.services.openoffice.org/wiki/Uno/Article/About_Bridges About_Bridges] | [http://wiki.services.openoffice.org/wiki/Uno/Article/About_Bridges About_Bridges] | ||
+ | |||
+ | See also the topic below about Python. | ||
== Using COM Automation to interact with OpenOffice and Microsoft Office== | == Using COM Automation to interact with OpenOffice and Microsoft Office== | ||
Line 97: | Line 99: | ||
</PRE> | </PRE> | ||
+ | |||
+ | == Attempting to use Python to manipulate OpenOffice == | ||
+ | |||
+ | Since OpenOffice includes support for Python, it would seem possible to run Python scripts from Pascal to manipulate OO, in lieu of actual Pascal language bindings. Here are the steps for one possible approach to doing this: | ||
+ | |||
+ | <OL> | ||
+ | <LI>Test UNO via Python macro run within OO | ||
+ | <LI>Test UNO via Python standalone script | ||
+ | <LI>Support for running Python scripts in Pascal | ||
+ | <LI>Test UNO via Python script run in Pascal | ||
+ | <LI>Pascal class that wraps Python UNO | ||
+ | </OL> | ||
+ | |||
+ | Note: The following scripts were tested with OpenOffice 2.3.1 on Windows XP and NeoOffice 2.2.5 Patch 6 on Mac OS X 10.4.11 (PowerPC). | ||
+ | |||
+ | ==== Step 1. Test UNO via Python macro run within OO ==== | ||
+ | |||
+ | OO has tools for creating JavaScript macros, but not Python macros, so use a text editor to save the following script to file test_macro.py and place it in OO's user macro folder. On Windows, this folder is: | ||
+ | |||
+ | <pre> | ||
+ | C:\Document and Setting\<username>\Application Data\OpenOffice.org2\user\Scripts\python\Library1 | ||
+ | </pre> | ||
+ | |||
+ | On Mac OS X, this folder is: | ||
+ | |||
+ | <pre> | ||
+ | ~/Library/Preferences/NeoOffice-2.2/user/Scripts/python/Library1 | ||
+ | </pre> | ||
+ | |||
+ | On both platforms, you'll need to create the python/Library1 folder. | ||
+ | |||
+ | Here is the code for test_macro.py, adapted from the OO Pascal example above: | ||
+ | |||
+ | <pre> | ||
+ | # Python macro that tests UNO by creating new document and inserting some text. | ||
+ | |||
+ | import uno | ||
+ | |||
+ | def TestNewDoc(): | ||
+ | ctx = uno.getComponentContext() | ||
+ | smgr = ctx.ServiceManager | ||
+ | desktop = smgr.createInstance('com.sun.star.frame.Desktop') | ||
+ | doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ()) | ||
+ | textCursor = doc.Text.createTextCursor() | ||
+ | doc.Text.insertString(textCursor, 'Hello World', 0) | ||
+ | </pre> | ||
+ | |||
+ | In OO, choose Tools | Macros | Organize Macros | Python and run the macro to make sure it works. | ||
+ | |||
+ | ==== Step 2. Test UNO via Python standalone script ==== | ||
+ | |||
+ | Here is the same code as a standalone script: | ||
+ | |||
+ | <pre> | ||
+ | # Python script that tests UNO by creating new document and inserting some text. | ||
+ | |||
+ | import sys | ||
+ | |||
+ | if sys.platform == 'darwin': | ||
+ | sys.path.append('/Applications/NeoOffice.app/Contents/MacOS') | ||
+ | |||
+ | import officehelper | ||
+ | |||
+ | ctx = officehelper.bootstrap() | ||
+ | smgr = ctx.ServiceManager | ||
+ | desktop = smgr.createInstance('com.sun.star.frame.Desktop') | ||
+ | doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ()) | ||
+ | textCursor = doc.Text.createTextCursor() | ||
+ | doc.Text.insertString(textCursor, 'Hello World', 0) | ||
+ | </pre> | ||
+ | |||
+ | Save this to file test.py and run it like this on Windows from a command line: | ||
+ | |||
+ | <pre> | ||
+ | "\program files\openoffice.org 2.3\program\python" test.py | ||
+ | </pre> | ||
+ | |||
+ | On Mac OS X, run the script like this from a Terminal window: | ||
+ | |||
+ | <pre> | ||
+ | #!/bin/sh | ||
+ | export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH":/Applications/NeoOffice.app/Contents/MacOS" | ||
+ | python2.3 test.py | ||
+ | </pre> | ||
+ | |||
+ | Unfortunately, this script doesn't work either on Windows or Mac OS X. On Windows, it displays an error dialog with no text (!) and then outputs an error message to the console that says "Cannot connect to soffice server". On Mac OS X, it starts NeoOffice and creates the new document, then NeoOffice shuts down abruptly. | ||
+ | |||
+ | ==== UNO Python To-Do ==== | ||
+ | |||
+ | Obviously additional investigation is needed before we can proceed to step 3. You are welcome to work on this. Here are a couple things to try: | ||
+ | |||
+ | * Test on Linux | ||
+ | * Test on more recent versions of OpenOffice | ||
+ | |||
+ | Note: On Windows and Linux, use the version of Python included with OO; on Mac OS X, use the system's Python 2.3. | ||
+ | |||
+ | |||
+ | ---- | ||
== Using the Free Pascal Spreadsheet Library == | == Using the Free Pascal Spreadsheet Library == | ||
Line 102: | Line 202: | ||
== Writing an Excel file using ADO == | == Writing an Excel file using ADO == | ||
− | please write me. | + | please write me (who?). |
== External links == | == External links == |
Revision as of 22:27, 1 January 2009
│
Deutsch (de) │
English (en) │
español (es) │
français (fr) │
italiano (it) │
русский (ru) │
中文(中国大陆) (zh_CN) │
The ability to interact with office software and generate spreadsheets, text documents and presentations from code can be invaluable in the office, and win a lot of time for those that can do it. One example of this is the creation of applications that can read files in an arbitrary format and output an Excel file, a task much more efficient to be done with code then manually.
Using the OpenOffice UNO Bridge
OpenOffice has language bindings for C++, Java, JavaScript and Python. On Windows, OpenOffice can also be manipulated in Pascal via COM Automation (see below), but there is currently no easy way of using OpenOffice's UNO (Universal Network Objects) from Pascal on OS X and Linux. If you're interested in developing an OO "bridge" for Pascal, please refer to these links for more information (caution: these links are quite techie in true Sun fashion):
See also the topic below about Python.
Using COM Automation to interact with OpenOffice and Microsoft Office
Automation is unique to Windows so the following two examples won't work on OS X or Linux. For those platforms, please refer to Making do without Windows COM Automation. If you only need to create and/or view a word processing document from your program, take a look at the XDev Toolkit.
Here's a simple example of how to open a document with your program using the OpenOffice Automation server. Note that this works only on Windows.
program TestOO; {$IFDEF FPC} {$MODE Delphi} {$ELSE} {$APPTYPE CONSOLE} {$ENDIF} uses SysUtils, Variants, ComObj; const ServerName = 'com.sun.star.ServiceManager'; var Server : Variant; Desktop : Variant; LoadParams : Variant; Document : Variant; TextCursor : Variant; begin if Assigned(InitProc) then TProcedure(InitProc); try Server := CreateOleObject(ServerName); except WriteLn('Unable to start OO.'); Exit; end; Desktop := Server.CreateInstance('com.sun.star.frame.Desktop'); LoadParams := VarArrayCreate([0, -1], varVariant); {Create new document} Document := Desktop.LoadComponentFromURL('private:factory/swriter', '_blank', 0, LoadParams); TextCursor := Document.Text.CreateTextCursor; {Insert existing document} //Substitute your path and doc TextCursor.InsertDocumentFromURL('file:///C|/my/path/mydoc.doc', LoadParams); end.
Here's a simple example of how to open a document with your program using the Word Automation server. Note that this example works only on Windows and only when compiled with Delphi; Free Pascal 2.2.2 can now compile the code but running it does not work. Please check back later or test with a future version of FPC.
program TestMsOffice; {$IFDEF FPC} {$MODE Delphi} {$ELSE} {$APPTYPE CONSOLE} {$ENDIF} uses SysUtils, Variants, ComObj; const ServerName = 'Word.Application'; var Server : Variant; begin if Assigned(InitProc) then TProcedure(InitProc); try Server := CreateOleObject(ServerName); except WriteLn('Unable to start Word.'); Exit; end; {Open existing document} //Substitute your path and doc Server.Documents.Open('c:\my\path\mydoc.doc'); Server.Visible := True; {Make Word visible} end.
Attempting to use Python to manipulate OpenOffice
Since OpenOffice includes support for Python, it would seem possible to run Python scripts from Pascal to manipulate OO, in lieu of actual Pascal language bindings. Here are the steps for one possible approach to doing this:
- Test UNO via Python macro run within OO
- Test UNO via Python standalone script
- Support for running Python scripts in Pascal
- Test UNO via Python script run in Pascal
- Pascal class that wraps Python UNO
Note: The following scripts were tested with OpenOffice 2.3.1 on Windows XP and NeoOffice 2.2.5 Patch 6 on Mac OS X 10.4.11 (PowerPC).
Step 1. Test UNO via Python macro run within OO
OO has tools for creating JavaScript macros, but not Python macros, so use a text editor to save the following script to file test_macro.py and place it in OO's user macro folder. On Windows, this folder is:
C:\Document and Setting\<username>\Application Data\OpenOffice.org2\user\Scripts\python\Library1
On Mac OS X, this folder is:
~/Library/Preferences/NeoOffice-2.2/user/Scripts/python/Library1
On both platforms, you'll need to create the python/Library1 folder.
Here is the code for test_macro.py, adapted from the OO Pascal example above:
# Python macro that tests UNO by creating new document and inserting some text. import uno def TestNewDoc(): ctx = uno.getComponentContext() smgr = ctx.ServiceManager desktop = smgr.createInstance('com.sun.star.frame.Desktop') doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ()) textCursor = doc.Text.createTextCursor() doc.Text.insertString(textCursor, 'Hello World', 0)
In OO, choose Tools | Macros | Organize Macros | Python and run the macro to make sure it works.
Step 2. Test UNO via Python standalone script
Here is the same code as a standalone script:
# Python script that tests UNO by creating new document and inserting some text. import sys if sys.platform == 'darwin': sys.path.append('/Applications/NeoOffice.app/Contents/MacOS') import officehelper ctx = officehelper.bootstrap() smgr = ctx.ServiceManager desktop = smgr.createInstance('com.sun.star.frame.Desktop') doc = desktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, ()) textCursor = doc.Text.createTextCursor() doc.Text.insertString(textCursor, 'Hello World', 0)
Save this to file test.py and run it like this on Windows from a command line:
"\program files\openoffice.org 2.3\program\python" test.py
On Mac OS X, run the script like this from a Terminal window:
#!/bin/sh export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH":/Applications/NeoOffice.app/Contents/MacOS" python2.3 test.py
Unfortunately, this script doesn't work either on Windows or Mac OS X. On Windows, it displays an error dialog with no text (!) and then outputs an error message to the console that says "Cannot connect to soffice server". On Mac OS X, it starts NeoOffice and creates the new document, then NeoOffice shuts down abruptly.
UNO Python To-Do
Obviously additional investigation is needed before we can proceed to step 3. You are welcome to work on this. Here are a couple things to try:
- Test on Linux
- Test on more recent versions of OpenOffice
Note: On Windows and Linux, use the version of Python included with OO; on Mac OS X, use the system's Python 2.3.
Using the Free Pascal Spreadsheet Library
Another way to automate repetitive work with spreadsheets is to generate the file using the FPSpreadsheet library. This method doesn't require having any external application installed on the machine and several formats are supported.
Writing an Excel file using ADO
please write me (who?).
External links
- Excel file format description on the OpenOffice website