(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:
- Download and install Microsoft's "Windows Server 2003 Resource Kit Tools" (Ref. 13). This provides the needed instsrv.exe and srvany.exe programs
- Run, in the Command Prompt, "InstSrv.exe LibreOfficeService drive:\path\to\SrvAny.exe" (may also have to provide the full path to InstSrv.exe).
- Run regedit and follow the instructions from Ref. 1 to create the needed keys.
- Application: C:\Users\Public\LibreOfficePortable\App\libreoffice\program\soffice.exe
- AppParameters: -headless -accept=socket,port=8100;urp; -nologo -nodefault -nofirststartwizard -nolockcheck -norestore -invisible -env:UserInstallation=file:///C:/Users/Public/LibreOfficePortable/Data/settings/$USERNAME
- Start and test to see if the service is running. See Ref. 3 for details.
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
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
- 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
- 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
- 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
- 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
- [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
- 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
- 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
- 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
- .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
- 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
- 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
- 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
- Windows Server 2003 Resource Kit Tools; Microsoft; "http://www.microsoft.com/en-us/download/details.aspx?id=17657"; last visited 2013-13-03
- 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
- 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 - The Apache OpenOffice API Project; website; "http://www.openoffice.org/api/"; last visited 2013-13-03
