A Simple simple Grasshopper-OpenOffice connector component

Well... as part of learning is also showing and discussing, I have uploaded this chunk of code that actually works.

It came out from the idea of developing an example where the theoretical capabilities of programming with Visual Studio/SharpDevelop over the VB/C# IDE provided in GH would become somewhat more obvious.

However, in this very example the result is quite the opposite, as the imported references (one thing that is not possible to do using the VB component), are not exposed by the OpenOffice SDK so one has to develop using just abstract objects and find their properties digging in the web.

If the program of choice would have been Excel probably it would have made more sense, but in the other hand Excel is not Open Source so it is worth the effort.

The resulting component is a very rudimentary GH-OOff connector. It takes a path to the .ods file (P) and the name of the range (Rg) where data is contained. Then it delivers a list of points (Pt) where the X coordinate is the data and Y and Z are null, along with a list containing the data (DL).

Here is the code, just copy-paste it into a newly configured Visual Studio (either Express or Standard)/SharpDevelop project, within the default .vb file or wherever you have defined a new class and build it into a .gha library (see here how if you dont know yet)

GHOpenOffice.zip contains the Visual Studio 2010 solution, with all the files and configurations needed for my computer (particularly check that I have customized the building routes, so you should do the same as explained in the tutorials).

You can open and build it either in Visual Studio or with SharpDevelop (available here).

Imports Grasshopper.Kernel
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 unoidl.com.sun.star.text
Imports unoidl.com.sun.star.beans
Imports unoidl.com.sun.star.sheet
Imports unoidl.com.sun.star.container
Imports unoidl.com.sun.star.table

Public Class GHComponent
    Inherits GH_Component

    Public OpenOffice As Object
    Public StarDesktop As Object
    Public OOoIntrospection As Object
    Public OOoDisp As Object
    Public Const OOo_connectKO = "OpenOffice connection is impossible"
    Public Const OOo_serviceKO = "Impossible to create service : "

    Private Const OOoErrorN = 2000

    Public Sub New()
        MyBase.New("GH-OpenOffice", "GH-OO", "Connects with OpenOffice Calc", "I/O", "OpenOffice")
    End Sub

    Public Overrides ReadOnly Property ComponentGuid As System.Guid
        Get
            'Don't copy this GUID, make a new one
            Return New Guid("b09e21a9-0432-46d8-b7f8-514c2464de4f")
        End Get
    End Property

    Protected Overrides Sub RegisterInputParams(ByVal pManager As Grasshopper.Kernel.GH_Component.GH_InputParamManager)
        pManager.Register_StringParam("File path", "P", "Path to .ods file")
        pManager.Register_StringParam("Range", "Rg", "Cell Range")
    End Sub

    Protected Overrides Sub RegisterOutputParams(ByVal pManager As Grasshopper.Kernel.GH_Component.GH_OutputParamManager)
        pManager.Register_DoubleParam("Data", "DL", "List with obtained data")
        pManager.Register_PointParam("Punto", "Pt", "Punto en dato")
    End Sub

    Protected Overrides Sub SolveInstance(ByVal DA As Grasshopper.Kernel.IGH_DataAccess)
        Dim OOData As New List(Of Double)
        Dim Rg As String = ""
        Dim Path As String = ""
        Dim Puntos As New List(Of Rhino.Geometry.Point3d)
        Dim Punto As Rhino.Geometry.Point3d
       
        'Collect the input parameters
        DA.GetData(0, Path)
        DA.GetData(1, Rg)
       
        'Because the OpenOffice interface reads the file paths in URL format, we have to adapt
        'just in case
        Path.Replace("/", "\")
       
        'Conect OpenOffice starts all the checkings to make sure the OO service happens
        ConnectOpenOffice()
       
        'Calcconnexion inserts the data extracted from the file in Path at the indicated
        'range Rg into the list OOData
        CalcConnexion(OOData, Path, Rg)
       
        'In this example we make the coordinates of a point out of the data from the OpenOffice range.
        'Unfortunately, making 2D list input parameters is not so straightforward in Grasshopper and
        'attempting to create a series of coordinates out of a 3columns x NumberOfRows is quite tricky...
        For i = 0 To OOData.Count - 1
            Punto = New Rhino.Geometry.Point3d(OOData(i), 0, 0)
            Puntos.Add(Punto)
        Next i
       
        'Put the collected data into the ouput parameters
        DA.SetDataList(0, OOData)
        DA.SetDataList(1, Puntos)
       
        'And close the services that link to OpenOffice
        DisconnectOpenOffice(True)
    End Sub
   
    Protected Overrides ReadOnly Property Icon As System.Drawing.Bitmap
        Get
            Return My.Resources.OOffice
        End Get
    End Property
   
    Sub ConnectOpenOffice()
        If IsOpenOfficeConnected() Then Exit Sub
        Try
            OpenOffice = CreateObject("com.sun.star.ServiceManager")
        Catch
            OpenOffice = Nothing
        End Try
        If isNullEmpty(OpenOffice) Then
            Err.Raise(vbObjectError + OOoErrorN, "OpenOffice", OOo_connectKO)
        Else
            StarDesktop = CreateUnoService("com.sun.star.frame.Desktop")
            OOoIntrospection = CreateUnoService("com.sun.star.beans.Introspection")
            OOoDisp = CreateUnoService("com.sun.star.frame.DispatchHelper")
        End If
    End Sub
   
    Sub DisconnectOpenOffice(Optional ByVal closeOpenOffice As Boolean = False)
        ' release method inspired from http://www.xtremevbtalk.com/showthread.php?t=160433
        ' this sequence tries to avoid error message R6025 from Visual C++ runtime
        OOoIntrospection = Nothing
        OOoDisp = Nothing
        StarDesktop = Nothing
        OpenOffice = Nothing
        GC.Collect() ' force garbage collection
        GC.WaitForPendingFinalizers() ' Wait for end of garbage collection before continuing
        GC.Collect() ' second pass of cleaning
        GC.WaitForPendingFinalizers()
        ' here no variable should be pointing at an OpenOffice object

        If closeOpenOffice Then
            ' reopen a connection only to close OpenOffice !
            Dim ooo As Object, dtp As Object
            ooo = CreateObject("com.sun.star.ServiceManager")
            dtp = ooo.createInstance("com.sun.star.frame.Desktop")
            ' this code may trigger error R6025 from C++ runtime
            ' this is avoided if you run the released code, not the debug code
            dtp.terminate()
            dtp = Nothing
            ooo = Nothing
        End If
    End Sub
   
    Function IsOpenOfficeConnected() As Boolean
        Dim DeskTopbis As Object

        IsOpenOfficeConnected = False
        If isNullEmpty(OpenOffice) Then Exit Function
        Try
            DeskTopbis = OpenOffice.createInstance("com.sun.star.frame.Desktop")
            DeskTopbis = Nothing
            IsOpenOfficeConnected = True
        Catch
            OpenOffice = Nothing
        End Try
    End Function
   
    Function isNullEmpty(ByVal thisVariant As Object) As Boolean
        isNullEmpty = IsNothing(thisVariant) Or IsDBNull(thisVariant)
    End Function
   
    Function CreateUnoService(ByVal serviceName As String) As Object
        ' equivalent to OOoBasic function
        Dim Result As Object

        Result = OpenOffice.createInstance(serviceName)
        If isNullEmpty(Result) Then
            Err.Raise(vbObjectError + OOoErrorN, "OpenOffice", OOo_serviceKO & serviceName)
        End If
        CreateUnoService = Result
    End Function

    Sub CalcConnexion(ByVal OOData As List(Of Double), ByVal Path As String, ByVal Range As String)
        Dim myDoc As Object
        Dim firstSheet As Object
        Dim m As Integer
        Dim aRange As Object
        Dim NumRows As Integer

        'Here is where we connect and extract the data:

        'myDoc is the file that comes from the provided path
        myDoc = StarDesktop.loadComponentFromURL("file:///" & Path, "_default", 0, dummyArray)

        'firstSheet is the first sheet of the workbook. If we wanted to get a different one just change the index
        firstSheet = myDoc.Sheets.getByIndex(0)

        'aRange represents the OpenOffice object of a range, that we make to be the one provided as string by the user
        aRange = firstSheet.getCellRangeByName(Range)

        'We can use the function getRows from OpenOffice to define the iterations over the range
        NumRows = aRange.getrows().getcount()

        'Note that in order to use the function getcellbyposition() the basis is zero, not one
        For m = 0 To NumRows - 1
            OOData.Add(aRange.getcellbyposition(0, m).value)
        Next m

        'The document gets closed after using it
        myDoc.close(True)
    End Sub
   
    Function dummyArray() As Object
        ' creates an empty array for an empty list
        Dim Result(-1) As Object
        dummyArray = Result
    End Function

End Class

Well..hope fully more to come in the near future!

Cheers,

Ra