.Net: Working with OpenOffice 3

(__Updated Wednesday 30JAN2009.__) Developing applications that manipulate OpenOffice documents has always been rather tricky; not very difficult, but just tricky to get right.

With OpenOffice 3, things got trickier and applications that used to work will now fail.
I’ve just spend nearly a day trying to get a mail-merge application I built to work again with the new version of OO.

###Changes and limitations###
Developing .Net applications with OpenOffice 3 [now requires that the .Net framework 3.5 be installed][10].
Only one version of OpenOffice/StarOffice must be installed.

###Getting the CLI assemblies in your project###
OpenOffice used to ship with a bunch of CLI DLLs for manipulating Open Documents from .Net.
With OpenOffice version 3, these DLLs are directly installed in the GAC and not available in the Program installation folder any longer, making them unavailable when you need to add them as references to your Visual Studio project.

The trick is to extract the DLLs from the installation CAB, then reference those and make sure you exclude them in your setup file so only the latest version installed on the user’s computer’s GAC will get used.

1. Open the installation folder where the installation files are extracted (usually left on your desktop under a folder like `OpenOffice.org 3.0 (en-US) Installation Files` during the OO installation process.
2. Open the `openofficeorg1.cab` file (using a utility like [7zip][7zip] if necessary) and extract the files matching `cli_*.dl`.
3. Add the ones you need to your VS Project’s references and make sure the properties of these references have their _Copy Local_ and _Specific Version_ properties set to _false_.
4. If you have a setup project that added these references to the list of files, right-click each of them and select _Exclude_ to make sure they won’t be packaged.

The reason for not deploying the DLLs is that they are very specific to a given OpenOffice version and the latest assemblies relevant to the user’s OpenOffice installation will already be deployed to the target machine’s GAC.
When .Net loads assemblies, if they are not included in the path of the application being launched, they will be loaded from the GAC if they can be found there.

###Path and environment setup###
Before being able to call OpenOffice from your application you now must set your application’s environment variables to the correct paths so the assemblies can find the actual OpenOffice library and program files.

Basically, you need to add to your `PATH` the path to the folder where the UNO java libraries reside.
You also need to add a `UNO_PATH` environment variable that points to the program folder of OpenOffice.
Basically, before any call to OpenOffice functions you must:

* Append to `PATH` something like `C:\Program Files\OpenOffice.org 3\URE\bin`.
* Create a `UNO_PATH` variable set to `C:\Program Files\OpenOffice.org 3\program`.

Because there is no guarantee that these paths will not change or are valid for all systems you must get them from specific keys located in the Registry:

* `PATH` is appended with the vaue of `HKLM\SOFTWARE\OpenOffice.org\Layers\URE\1\UREINSTALLLOCATION` to which you must append the `bin` directory.
* `UNO_PATH` is set to the content of the `HKLM\SOFTWARE\OpenOffice.org\UNO\InstallPath` key.

See the C# and VB.Net code below for working examples.

###Special Considerations for x64 systems###
My development machine runs Windows Server 2008 x64 and I’ve ran into some specific issues that you’re likely to encounter when deploying to a 64 bits OS.

__OpenOffice is 32 bits only__
That means that your .Net project must be set to target x86 systems only:
Open your Solution’s _Configuration Manager_ and under _Active solution platform_ click _New…_ then:

Make sure you repeat this for both the _Debug_ and _Release_ configurations.

__Registry keys are elsewhere__
32 bit applications see their registry keys normally expected under:
`HKEY_LOCAL_MACHINE\Software` moved to:
`HKEY_LOCAL_MACHINE\Software\Wow6432Node` instead.
This of course creates issues when you’re trying to read a registry key that’s not where it should be…

###The Code###
The code below will allow you to correctly connect to OpenOffice 3 under 32 or 64 bit systems.
It reads the registry to find the proper paths and appends the `PATH` and creates the `UNO_PATH` environment variables expected by the the bootstrapper to find the OpenOffice program and libraries.

The code is built upon information and a test program made available by [Marten Feldtmann][1] on his [blog][2] (more information, in English, is available on [OOoForum][3] ).

Please let me know if this works for you or if you have any corrections.

~~~~brush:csharp
private void InitOpenOfficeEnvironment() {
string baseKey;
// OpenOffice being a 32 bit app, its registry location is different in a 64 bit OS
if (Marshal.SizeOf(typeof(IntPtr)) == 8)
baseKey = @”SOFTWARE\Wow6432Node\OpenOffice.org\”;
else
baseKey = @”SOFTWARE\OpenOffice.org\”;

// Get the URE directory
string key = baseKey + @”Layers\URE\1″;
RegistryKey reg = Registry.CurrentUser.OpenSubKey(key);
if (reg==null) reg = Registry.LocalMachine.OpenSubKey(key);
string urePath = reg.GetValue(“UREINSTALLLOCATION”) as string;
reg.Close();
urePath = Path.Combine(urePath, “bin”);

// Get the UNO Path
key = baseKey + @”UNO\InstallPath”;
reg = Registry.CurrentUser.OpenSubKey(key);
if (reg==null) reg = Registry.LocalMachine.OpenSubKey(key);
string unoPath = reg.GetValue(null) as string;
reg.Close();

string path;
path = string.Format (“{0};{1}”, System.Environment.GetEnvironmentVariable(“PATH”), urePath);
System.Environment.SetEnvironmentVariable(“PATH”, path);
System.Environment.SetEnvironmentVariable(“UNO_PATH”, unoPath);
}
~~~~

In VB.Net:

~~~~brush:vb
Private Sub InitOpenOfficeEnvironment()
Dim baseKey As String
‘ OpenOffice being a 32 bit app, its registry location is different in a 64 bit OS
If (Marshal.SizeOf(GetType(IntPtr)) = 8) Then
baseKey = “SOFTWARE\Wow6432Node\OpenOffice.org\”
Else
baseKey = “SOFTWARE\OpenOffice.org\”
End If

‘ Get the URE directory
Dim key As String = (baseKey + “Layers\URE\1”)
Dim reg As RegistryKey = Microsoft.Win32.egistry.CurrentUser.OpenSubKey(key)
If (reg Is Nothing) Then
reg = Registry.LocalMachine.OpenSubKey(key)
End If
Dim urePath As String = CType(reg.GetValue(“UREINSTALLLOCATION”),String)
reg.Close
urePath = Path.Combine(urePath, “bin”)

‘ Get the UNO Path
key = (baseKey + “UNO\InstallPath”)
reg = Microsoft.Win32.Registry.CurrentUser.OpenSubKey(key)
If (reg Is Nothing) Then
reg = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(key)
End If
Dim unoPath As String = CType(reg.GetValue(Nothing),String)
reg.Close

Dim path As String
path = String.Format (“{0};{1}”,System.Environment.GetEnvironmentVariable(“PATH”),urePath)
System.Environment.SetEnvironmentVariable(“PATH”, path)
System.Environment.SetEnvironmentVariable(“UNO_PATH”, unoPath)
End Sub
~~~~

###Updates###
07MAY2009 – Added reference link to OOo documentation.
03DEC2008 – Added VB.Net translation. Thanks to Stefan for suggesting it.
30JAN2009 – Added reference to Aleksandr Sazonov’s article on CodeProject (thanks for the the article).

###References###

* [CLI Language Binding][cli]
* [OOoForum thread][forum1] on getting sample C# code to work with OpenOffice 3
* [OOoForum thread][3] on upgrading code from OpenOffice 2.4 to 3
* [Programming with .Net quick start guide][quickstart].
* [CLI UNO bridge not working][3]
* [Cross-compatible spreadsheet processor][4] in .Net for Excel and OOo Calc, article from Aleksandr Sazonov on CodeProject.
* [Writing client programs – from the OOo documentation][5]

[3]: http://qa.openoffice.org/issues/show_bug.cgi?id=95255
[CM]: /wp-content/uploads/2008/11/sshot-4.png
[0]: /2008/mac-installing-windows-server-2008-x64-on-a-macbook-pro/
[1]: http://www.schrievkrom.de/blog/
[2]: http://www.schrievkrom.de/blog/?p=199
[4]: http://www.codeproject.com/KB/cs/TblProc.aspx
[10]:http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/ProUNO/CLI/CLI_Language_Binding
[5]:http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/ProUNO/CLI/Writing_Client_Programs
[cli]: http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/ProUNO/CLI/CLI_Language_Binding
[forum1]: http://www.oooforum.org/forum/viewtopic.phtml?t=76356&start=0&postdays=0&postorder=asc&highlight=
[3]: http://www.oooforum.org/forum/viewtopic.phtml?p=302441
[quickstart]: http://opendocument4all.com/content/view/68/47/
[7zip]: http://www.7-zip.org/