The Quest for Accessing LibreOffice Through VB .NET

(The Legend of The Headless Service)

The Goal: use LibreOffice (or OpenOffice) to provide layout and exporting of reports for an ASP .NET based website.

I lean towards LibreOffice as my preferred office suite, and chose to see if I could use the portable version of it to more easily avoid double install issues. Plus this theoretically makes it usable in situations where administration rights are not available. I have gone down the path of using the headless server mode as a Windows service, but it could be started and controlled from a webservice.

Getting the latest LibreOffice to run from VB .NET is not that hard if you are not trying to run in headless mode. Following most of the tutorials out there will get you running without too much problem. You run off the edge of the map whenever you try to connect to a headless version from .NET ("HC SVNT DRACONES" - "Here Be Dragons").

Development Environment

• Window 8 Pro 64bit
• Microsoft Visual Basic 2012
• LibreOffice 4.1 Portable
  • install path: C:\Users\Public\LibreOfficePortable

Creating the service

References 1 and 3 provide detailed instructions for setting up LibreOffice as a service. Please read and follow their instructions for the basic procedure.

In a nut shell:

1. Download and install Microsoft's "Windows Server 2003 Resource Kit Tools" (Ref. 13). This provides the needed instsrv.exe and srvany.exe programs
2. Run, in the Command Prompt, "InstSrv.exe LibreOfficeService drive:\path\to\SrvAny.exe"  (may also have to provide the full path to InstSrv.exe).
3. Run regedit and follow the instructions from Ref. 1 to create the needed keys.
   
   1. Application: C:\Users\Public\LibreOfficePortable\App\libreoffice\program\soffice.exe
   2. AppParameters: -headless -accept=socket,port=8100;urp; -nologo -nodefault -nofirststartwizard -nolockcheck -norestore -invisible -env:UserInstallation=file:///C:/Users/Public/LibreOfficePortable/Data/settings/$USERNAME
4. Start and test to see if the service is running. See Ref. 3 for details.

The AppParameters key value are the LibreOffice command line arguments passed by the SrvAny program to LibreOffice when starting it up. This starts up LibreOffice in server mode listening for requests on port 8100. The -env:UserInstallation setting sets the path of LibreOffice's user defaults to a non-standard location to help prevent conflicts with other LibreOffice installations.

Writing the code

‘Bind him hand and foot and cast him into the outer darkness. In that place there will be weeping and gnashing of teeth.’ Matthew 22:13

I can look back now, and things don't seem that horrible, but it was a long couple days of roaming the Net, trying new bits of code, and working my way through one road block after another. I was almost ready to give up before I found the right combination of code to make things connect to a headless LibreOffice. The following test code shows how to create a new calc doc, add some fields to it and save it in ODS and XLS 97 formats.

For the impatient:

Imports System
Imports unoidl.com.sun.star.lang
Imports unoidl.com.sun.star.uno
Imports unoidl.com.sun.star.bridge
Imports unoidl.com.sun.star.frame
Imports Microsoft.Win32
Imports System.Runtime.InteropServices

Module Module1
  Private Const baseInstallationPath As String = "C:\Users\Public\LibreOfficePortable"

  Sub Main()

    InitOpenOfficeEnvironment()

    Dim arg(1) As unoidl.com.sun.star.beans.PropertyValue

    Dim localContext As unoidl.com.sun.star.uno.XComponentContext = uno.util.Bootstrap.bootstrap()

    Dim localServiceFactory As unoidl.com.sun.star.lang.XMultiComponentFactory = localContext.getServiceManager()

    Dim resolver As unoidl.com.sun.star.bridge.XUnoUrlResolver = localServiceFactory.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext)

    'Initialize a Windows socket
   
    Dim s As System.Net.Sockets.Socket = New System.Net.Sockets.Socket(System.Net.Sockets.AddressFamily.InterNetwork, Net.Sockets.SocketType.Stream, Net.Sockets.ProtocolType.IP)

    Dim multiServiceFactory As unoidl.com.sun.star.lang.XMultiServiceFactory = resolver.resolve("uno:socket,host=localhost,port=8100;urp;StarOffice.ServiceManager")

    Dim oDesk As unoidl.com.sun.star.frame.XComponentLoader = multiServiceFactory.createInstance("com.sun.star.frame.Desktop")

    'Create a new calc doc

    Dim oDoc As unoidl.com.sun.star.lang.XComponent = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, arg)

    Dim oSheets As unoidl.com.sun.star.sheet.XSpreadsheets = DirectCast(oDoc, unoidl.com.sun.star.sheet.XSpreadsheetDocument).getSheets
    Dim oXSheets As unoidl.com.sun.star.container.XIndexAccess = oSheets
    Dim oSheet As unoidl.com.sun.star.sheet.XSpreadsheet = DirectCast(oXSheets.getByIndex(0).Value, unoidl.com.sun.star.sheet.XSpreadsheet)
    oSheet.getCellByPosition(0, 0).setFormula("Month")
    oSheet.getCellByPosition(0, 1).setFormula("Dec")
    oSheet.getCellByPosition(1, 1).setValue(3900.37)

    'Save the doc
    arg(0) = Nothing
    'Save to ods format
    Call DirectCast(oDoc, unoidl.com.sun.star.frame.XStorable).storeToURL(PathConverter("C:\Users\Public\Documents\test") + Now.ToString("yyyyMMddHHmm") + ".ods", arg)
    'Save to xsl 97 format
    arg(0) = MakePropertyValue("FilterName", "MS Excel 97")
    Call DirectCast(oDoc, unoidl.com.sun.star.frame.XStorable).storeToURL(PathConverter("C:\Users\Public\Documents\test") + Now.ToString("yyyyMMddHHmm") + ".xls", arg)
    'Close the doc
    oDoc.dispose()
    oDoc = Nothing

  End Sub

  Private Sub InitOpenOfficeEnvironment()

    Dim urePath As String = baseInstallationPath + "\App\libreoffice\URE\bin"
    Dim unoPath As String = baseInstallationPath + "\App\libreoffice\program"
    Dim path As String = String.Format("{0};{1}", System.Environment.GetEnvironmentVariable("PATH"), urePath)
    System.Environment.SetEnvironmentVariable("PATH", path)
    System.Environment.SetEnvironmentVariable("UNO_PATH", unoPath)
  End Sub

  Public Function MakePropertyValue(ByVal cName As Object, ByVal uValue As Object) As Object
    Dim oPropertyValue As unoidl.com.sun.star.beans.PropertyValue = New unoidl.com.sun.star.beans.PropertyValue

    oPropertyValue.Name = cName
    Try
      oPropertyValue.Value = New uno.Any(uValue.GetType, uValue)
    Catch ex As unoidl.com.sun.star.uno.Exception
      ' All your unrecognized types are strings to me
      oPropertyValue.Value = New uno.Any(uValue.ToString)
    End Try

    MakePropertyValue = oPropertyValue
  End Function

  Private Function PathConverter(ByVal file As String) As String
    Try
      file = file.Replace("\", "/")
      Return "file:///" & file
    Catch ex As System.Exception
      Throw ex
    End Try
  End Function

End Module

To get this code working you will have to add references to the following libraries to your project.

• cli_uno.dll
• cli_ure.dll
• cli_uretypes.dll
• cli_oootypes.dll
• cli_cppuhelper.dll
• cli_basetypes.dll

These libraries can be found in the  "...\LibreOfficePortable\App\libreoffice\URE\bin" directory. If you are not using a portable version of LibreOffice, the libraries can be extracted from the installer .msi file (Ref 14).

UPDATE 2013-12-04: After loading LibreOffice 4.2.0.0 Alpha, I started getting the dreaded "Could not load type 'unoidl.com.sun.star.frame.XComponentLoader' from assembly 'cli_oootypes, Version=1.0.8.0, Culture=neutral, PublicKeyToken=ce2cb7e279207b9e'." error. The libraries shipped with the standard version of the office suite don't include everything in the cli_oootypes like previous versions did, or the portable version does. .NET is designed to trust and default to libraries in the GAC, so it will use them over the portable ones. So far the solution to this problem is to uninstall the regular version and just use the portable version.

Summary

These are the first major steps to my goal of creating an ASP .NET report service using LibreOffice. The code works when run from Visual Studio. I take it as a good sign that it won't be too difficult to set up the ASP environment correctly. My next post will be a more detailed breakdown of the code above. Explaining the various roadblocks I ran into, and how they were resolved.

References

1. Creating a User Defined Service in Windows 7; Tim Davis; blog; 2009-09-28; "http://www.timdavis.com.au/general/creating-a-user-defined-service-in-windows-7/"; last visited 2013-13-03
2. How to initialize the Windows socket library without admin rights; stackoverflow; forum; 2013-04-18; "http://stackoverflow.com/questions/16090329/how-to-initialize-the-windows-socket-library-without-admin-rights"; last visited 2013-13-03
3. Run OpenOffice.org 3 as Windows service (updated instructions); A25 Apps; blog; 2009-09-29; "http://blog.a25apps.com/2009/09/29/run-openoffice-org-3-as-windows-service-updated-instructions/"; last visited 2013-13-03
4. LibreOffice user profile; The Document Foundation;  Wiki; 2013-07-25 (last modified); "https://wiki.documentfoundation.org/UserProfile#Resetting_the_user_profile"; last visited 2013-13-03
5. [Solved] How to use CLI UNO in MS Basic; Apache OpenOffice community forum; forum; 2009-08-10; "https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=21494"; last visited 2013-13-03
6. uno.Any() problem with c#; Apache OpenOffice community forum; forum; 2010-07-01; "https://forum.openoffice.org/en/forum/viewtopic.php?f=44&t=32021#p252656"; last visited 2013-13-03
7. MBARBON/OpenOffice-UNO-0.07/UNO.pm; meta::cpan; Mattia Barbon; "https://metacpan.org/source/MBARBON/OpenOffice-UNO-0.07/UNO.pm"; last visited 2013-13-03
8. Programming OpenOffice.org with Visual Basic; Bernard Marcelly, Laurent Godard, Christophe Thibierge; 2006-05-06; Version 0.8; "http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html"; last visited 2013-13-03
9. .Net: Working with OpenOffice 3; Cypris' lookout; Renaud Bompuis; blog; 2008-11-06; "http://blog.nkadesign.com/2008/net-working-with-openoffice-3/"; last visited 2013-13-03
10. How can I use OpenOffice in server mode as a multithreaded service?; stackoverflow; forum; 2009-04-09; "http://stackoverflow.com/questions/625241/how-can-i-use-openoffice-in-server-mode-as-a-multithreaded-service"; last visited 2013-13-03
11. OpenOffice DLL CLI_UNO - Not compatible with .NET 4.0 framework; stackoverflow; forum; 2013-07-19; "http://stackoverflow.com/questions/17746328/openoffice-dll-cli-uno-not-compatible-with-net-4-0-framework"; last visited 2013-13-03
12. OpenOffice in server mode; Technicus stultissimus; blog; 2011-03-12; "http://tecnicambalandia.blogspot.com/2011/03/openoffice-in-server-mode.html"; last visited 2013-13-03
13. Windows Server 2003 Resource Kit Tools; Microsoft; "http://www.microsoft.com/en-us/download/details.aspx?id=17657"; last visited 2013-13-03
14. How To Extract a Single File From a Large MSI (4 Ways); CSI:\>windows; Darwin Sanoy; 2010-04-18; "http://csi-windows.com/blog/all/27-csi-news-general/158-how-to-extract-a-single-file-from-a-large-msi-4-ways"; last visited 2013-13-03
15. How to make libreoffice headless to work on CentOS 6.3; beta; Diego La Monica; blog; 2013-04-14; "http://diegolamonica.info/how-to-make-libreoffice-headless-to-work-on-centos-6-3/"; last visited 2013-13-03
16. The Apache OpenOffice API Project; website; "http://www.openoffice.org/api/"; last visited 2013-13-03