.Net: Working with OpenOffice 3

Thursday, November 6, 2008

(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.

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.
Open the openofficeorg1.cab file (using a utility like 7zip if necessary) and extract the files matching cli_*.dl.
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.
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

Entry Filed under : .Net,OpenOffice,Programming

32 Comments

1. George | November 23rd, 2008 at 14:24

Hi. Thanks heaps for this article – you saved my bacon. After upgrading to OO3 our windows service no longer work… but then I found your article and the tip on the UNO_PATH and now its all working. Thanks again.
2. Renaud | November 23rd, 2008 at 14:28

@Georges: you’re welcome, thanks for reporting back.

Thanks, that helped a lot! I needed a VB.NET version and I like to share it for others to copy-paste.

' Don't know how to do this with VB
'// OpenOffice being a 32 bit app, its registry location is different in a 64 bit OS  
        'if (Marshal.SizeOf(typeof(IntPtr)) == 8)  then
        '    baseKey = "SOFTWARE\Wow6432Node\OpenOffice.org\";      
        'Else
        'End If
        '// so just assume 32bit
        Dim baseKey As String
        baseKey = "SOFTWARE\OpenOffice.org\"

        '// Get the URE directory  
        Dim key As String = baseKey + "Layers\URE\1"
        Dim reg As Microsoft.Win32.RegistryKey
        reg = Microsoft.Win32.Registry.CurrentUser.OpenSubKey(key)
        If (reg Is Nothing) Then reg = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(key)
        Dim urePath As String = reg.GetValue("UREINSTALLLOCATION").ToString
        reg.Close()
        urePath = System.IO.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)
        Dim unoPath As String = reg.GetValue(Nothing).ToString
        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)

4. Renaud | December 3rd, 2008 at 10:12

Thanks for the code Stefan. I’ve added a VB.Net translation to the article.
5. jason | January 10th, 2009 at 04:06

I have the same bootstrap hanging problem. I am using the code you have provided to retrieve the proper path settings, however, the bootstrapper still hangs on my deployed server. I am using a webservice (asp.net/IIS) to create a calc document and saving it to file. The webservice runs properly when tested locally on my development box but hangs when I deploy to the server. Both the server and my box have OOo 3. I notice that the soffice.exe and soffice.bin processes both get started on the server. I read here that the problem may have to do with the aspnet account being used to start the OOo service. Even after setting the webservice to impersonate a valid user with the required permissions, the bootstrapper still hangs. The server OS is MS Windows Server 2003 SP2.
6. jason | January 10th, 2009 at 04:17

I found further information about my problem HERE. It looks like the problem may have to do with .net 2.0. I will try building my webservice with .net 1.1 and see if that helps
7. Aleksandr | January 30th, 2009 at 06:34

Hi. Thanks for the sample. I Used it to upgrade my OOo library from OO 2.x to 3.0. Code sample and a bit of description at http://www.codeproject.com/KB/cs/TblProc.aspx. If you consider this is an adverisement, feel free to block it.
8. Renaud | January 30th, 2009 at 09:51

Hi Aleksandr, thanks for the CodeProject article. I updated this post to include it as a reference.
Your project is actually something I was actually needing so I’ll most probably end up using it myself.
9. Rakshith | January 30th, 2009 at 19:37

Hey! Thank u so much for the code man it helped me a lot:)
10. Pawan | February 13th, 2009 at 18:55
1. My application when run in VS.net (Server) Environment and produce oo report. It producing Open Office Excel file and Charts. Its OK.
2. (B) In IIS Server it not produce the file and processing goes endless. Not produce any error / exception.
3. When wait long for localhost it shows “An un handeled exception(System.CannotUnloadAppDomainException) occured in aspnet_wp.exp”
What I have done. OpenOffice 3.o
- Added the TblProc.dll as reference in my project.
- five cli_ files are in GAC
- Excluding cli_uno.dll file give HResult error when add cli_uno.dll as reference so not added in project and it is not in GAC and in VS Server without it producing file.
Please suggest what I do to resolve this issue.
11. Aleksandr | February 20th, 2009 at 21:31

Developing .Net applications with OpenOffice 3 now requires that the .Net framework 3.5 be installed.

I have a client, which is using .NET 2.0, it manages to work with OOo 3.0. Is it a requirement of OOo itself – to have .NET 3.5 installed or just a typo? I made no tests on a machine without fw 3.5.
12. Renaud | February 20th, 2009 at 23:40

@Aleksandr: this is what the OpenOffice wiki for .Net binding says (cached version, I’m having trouble accessing the site).

I’m not sure which part of the .Net Framework 3.5 is required.
Best is to test thoroughly.
13. Pawan | February 21st, 2009 at 15:22

OpenOffice 3.0 works fine with .Net Framework 2.0. Running the site from IIS Server need some access rights as ASPNET worker process can not access local machine files.
14. teztech.com » Web S&hellip | June 29th, 2009 at 02:02

[…] showed up under “Add Reference” in Visual Studio. After some Google searches, I finally stumbled across a solution. OK, wow, digging though install cab files for DLLs, hacking the registry and manipulating the PATH […]
15. thaler | November 2nd, 2009 at 22:36

I had problems with starting OpenOffice 3.1 remotely from .NET 3.5 via TCP until i realized this:

I used Visual Studio 2008 and my .NET-Programm was a Windows-Service with his own app.config-File. My first and fatal step during my tests was, that i changed the .NET-runtime in my project-properties to .NET 2.0 . Therefore VS2008 inserted ohne row in my app.config-File, which tells my program, to use .NET 2.0 at runtime.

After i read this article, i changed the .NET-runtime to .NET 3.5 and it don’t run. I don’t realize, that this single line in my app.config-File wasn’t removed and therefore my program used .NET 2.0 at runtime anymore.

When i realized this line and removed it, so that my program used realy .NET 3.5, in runs perfect!
16. rohini | February 23rd, 2010 at 20:33

I have done all the above mensioned things but getting error “External component has thrown an exception.” for bootstrap.

code line is XComponentContext oStrap = uno.util.Bootstrap.bootstrap() ;

please help
17. rohini | February 24th, 2010 at 16:31

hI , InitOpenOfficeEnvironment() function works for me to resolve the bootstrap error. Thanks!
18. Pawan Kumar | February 24th, 2010 at 19:18

I would appreciate dot net developer who used open office to produce excel reports deployed over web Server IIS.

I m still facing the problem as this code works well in my developement environment ASP.Net 2.0 on my machine, But fails to produce report when deployed on web server (IIS).

I would like to thanks if some one provide me complete code of this for Visual Studio 2005 to produce reports in scalc.
19. argy13 | August 14th, 2010 at 04:46

It s very helpful text. Nowhere else could not find something for the cli dll’s of OpenOffice 3 and i could n t do anything.

Thank s a lot!!!!
20. Park | January 19th, 2011 at 21:11

Hi. It’s very helpful article. But I Can’t deploy asp.net project using OO CLI to x64 production machine. Is there any help for deploing asp.net project to x64 server.

Thanks in advance
21. Illés András | February 23rd, 2011 at 19:18

Hi!

Thanks for your article! Do you have any experience with oo 3.3? After your help the problem is still on. 🙁

An very dummy question but: what means GAC abbrevation?

Best Regards!

Andris
22. nvithlani | March 5th, 2011 at 17:56

Hi,

I am facing the problem that the code is hanging infinitely at calling Bootstrap.bootstrap(). I have tried many solution and the ones suggested here but all in vain.

Additional specification of my working environment: – I am having Windows 7 Ultimate OS – I am using Visual Studio 2008 – The code is written in 3.5 website

The surprising part is that things are working fine in ASP.NET Web Application but giving problem in ASP.NET Web Site.

Please let me know, if I am missing something.

Thanks..
23. Shital Akruwala | August 12th, 2011 at 20:03

thank u….u helped a lot in finding cli*.dll files…
24. Riti Tiwari | April 16th, 2012 at 16:16

i m getting an error of specified module not found,plz do tell me how to resolve this error
25. ilber | July 26th, 2012 at 17:56

thanks a lot.
26. Hari | December 14th, 2012 at 18:20

Hi Iam Using open office in my asp.net project for generating pdf files for .docx,.xlsx file by using UNO DLLS I m facing the problem as this code works well in my developement environment ASP.Net on my machine, But not getting output (no response browser running for long time but no result) when deployed on web server (IIS).

can any tell how to do it through IIS

Thanks In Advance

had to change the line

            string key = baseKey + @"Layers\URE\1";

 
            string key = baseKey + @"Layers_\URE\1";

in ConfigureOO3x to get this to work for me

Big thanks to you’r post it was really helpfull! Specially thanks for the Platform configuration.

And about this code:

' 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

IntPtr.Size won’t return the correct value if running in 32-bit .NET Framework 2.0 on 64-bit Windows (it would return 32-bit).

USE THIS INSTEAD:

    [DllImport("kernel32.dll", SetLastError = true, CallingConvention = CallingConvention.Winapi)]
    #region DLL import to find bit version
    [return: MarshalAs(UnmanagedType.Bool)]
    public static extern bool IsWow64Process([In] IntPtr hProcess, [Out] out bool lpSystemInfo);
    #endregion
    #region Windows bit version finding
    public static bool Is64Bit()
    {
        bool retVal;
        IsWow64Process(Process.GetCurrentProcess().Handle, out retVal);
        return retVal;
    }
    public static string GetOSBit()
    {
        bool is64bit = Is64Bit();
        if (is64bit)
            return "x64";
        else
            return "x32";
    }
    #endregion

29. Tadej | August 7th, 2014 at 15:20

Hi,

great solution! Has anybody have any experiences doing this with OO (Open Office) 4.1 and .NET 4? It works fine localy, but not when published to IIS although OO process is started.

Br, Tadej
30. Amerigo | May 16th, 2015 at 23:41

I get this runtime error: An unhandled exception of type ‘System.NullReferenceException’ occurred in Complete_Text_Editor.dll

Additional information: Object reference not set to an instance of an object. That points to this line in VB.net: Dim urePath As String = CType(reg.GetValue(“UREINSTALLLOCATION”), String)
31. Axilis | How to use Open &hellip | November 4th, 2015 at 23:09

[…] Office. These two blogposts really helped us start without reading through all the documentation: .Net: Working with OpenOffice 3 and HOW TO: Convert office documents to PDF using Open Office in C#. They are more than enough to […]
32. Arthur | September 21st, 2017 at 19:36

Is it possible to read Base OpenOffice .odb database (tables, views, queries) from .Net application?

Cypris' lookout

Mater artium necessitas