Cypris' lookout

.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.
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 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 on his blog (more information, in English, is available on OOoForum ).

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

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:

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
• OOoForum thread on getting sample C# code to work with OpenOffice 3
• OOoForum thread on upgrading code from OpenOffice 2.4 to 3
• Programming with .Net quick start guide.
• CLI UNO bridge not working
• Cross-compatible spreadsheet processor in .Net for Excel and OOo Calc, article from Aleksandr Sazonov on CodeProject.
• Writing client programs – from the OOo documentation

32 comments November 6th, 2008