Identifying DDE-linked tables

    From The Document Foundation Wiki
    < Macros‎ | Writer


    Other languages:

    Description

    The menu Edit ▸ Links allows you to recognize the links present in a document, including:

    • File source (URL)
    • Element (range)
    • Type (soffice)
    • Refresh (automatic/manual)

    On the other hand, neither this approach, nor the navigator allows you to know which are the tables concerned (name or position in the document).

    The purpose of the below macro is to locate tables created by DDE links.[1]

    The macro inserts in the first cell of a table a comment consisting of:

    • source (URL of a Calc spreadsheet for example),
    • the range used in the source.

    (More on comment manipulation with macro in Macros/Writer/001.)

    Note that the collection TextFieldMasters would only retrieve the information displayed by the Edit ▸ Links menu and TextTables does not give us any more information.

    The DDE connection of table data is contained in an element <office:dde-source>[2].

    We can therefore browse the file content.xml to search for such elements. The program starts by checking that the current document has this file and then it proceeds to read it.

    For this we will make use of the service com.sun.star.xml.sax.Parser[3] associated with the listner com.sun.star.xml.sax.XDocumentHandler[4].

    Code

    In LibreOffice Basic:

    Option Explicit
    
    ' This library is Copyright (C) 2012 Pierre-Yves Samyn, except otherwise indicated
    ' Purpose: Frame the selection or create an empty frame if no selection
    
    ' This program is free software: you can redistribute it and/or modify
    ' it under the terms of the GNU General Public License as published by
    ' the Free Software Foundation, either version 3 of the License, or
    ' (at your option) any later version.
    ' This program is distributed in the hope that it will be useful,
    ' but WITHOUT ANY WARRANTY; without even the implied warranty of
    ' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ' GNU General Public License for more details.
    ' You should have received a copy of the GNU General Public License
    ' along with this program.  If not, see <http://www.gnu.org/licenses/>.
     
    Dim oDoc As Object
    Dim oDocHandler As Object
    Dim oTableName As String
    
    
    ' Description of macro:
    
    ' Context    :  for LibreOffice Writer odt document
    ' Objective  :  identify the DDE-linked tables
    ' Function   :  insert a comment of the source (URL of spreadsheet for ex.), and the range
    
    ' Info about usage of UNO's XML sax parser via the API:
    ' http://www.oooforum.org/forum/viewtopic.phtml?t=4907
    
    Sub Comment_DDE_Table()
    
        Dim oContent As Object
        Dim oFlux As Object
        Dim oSaxParser As Object
        Dim oDocEventsHandler As Object
        Dim oInputSource As Object
    
        oDoc = ThisComponent
    
        ' Only perform macro if document is a TextDocument
        If oDoc.supportsService("com.sun.star.text.TextDocument") Or _
            oDoc.supportsService("com.sun.star.text.GlobalDocument") Then
    
            ' We want to read the content.xml file, so we check that ThisComponent makes reference to a object with such a file
            If oDoc.DocumentStorage.HasByName("content.xml") Then
            
                ' Test if the document has been modified:  we will read the content.xml file hence we
                ' ask for saving, to be sure to work on the most recent content
                If oDoc.ismodified Then
                    MsgBox "The document must be saved to latest version! (Ctrl+S)", 64, "Comment_DDE_Table()"
                Else 
                    oContent = oDoc.DocumentStorage.GetByName("content.xml")
                    oFlux = oContent.GetInputStream()   
                            
                    ' Create XML Sax parser
                    oSaxParser = createUnoService( "com.sun.star.xml.sax.Parser" )
                    
                    ' Create event manager.
                    ' The names given below reflect content in accordance with
                    ' http://api.libreoffice.org/common/ref/com/sun/star/xml/sax/XDocumentHandler.html
                    oDocHandler = CreateUnoListener( "DocHandler_", "com.sun.star.xml.sax.XDocumentHandler" )
                    oSaxParser.setDocumentHandler( oDocHandler )
                       
                    ' Creation of structure in accordance with:
                    ' http://api.libreoffice.org/common/ref/com/sun/star/xml/sax/InputSource.html
    
                    oInputSource = createUnoStruct( "com.sun.star.xml.sax.InputSource" )
                    oInputSource.aInputStream = oFlux
            
                    ' Tell parser to read the contents of content.xml
                    ' The functions (DocHandler_) will be called in accordance with event.
                    oSaxParser.parseStream( oInputSource )
                    oFlux.closeInput()
                            
                    MsgBox "End of treatment." , 64, "Comment_DDE_Table()"
                End If
            Else
                MsgBox "This macro does not work for this type of document (needs to be odt Writer)" , 64, "Comment_DDE_Table()"
            End If
    
            Else
            MsgBox "This macro does not work for this type of document (needs to be odt Writer)" , 64, "Comment_DDE_Table()"
        End If
    
    End Sub ' Comment_DDE_Table
    
    
    ' Below, the functions of listener, corresponding to events:
    ' http://api.libreoffice.org/common/ref/com/sun/star/xml/sax/XDocumentHandler.html
    
    Sub DocHandler_startDocument()
    End Sub
    
    Sub DocHandler_endDocument()
    End Sub
    
    Sub DocHandler_startElement( cName As String, oAttributes As com.sun.star.xml.sax.XAttributeList )
    
        Dim oTable As Object
        Dim oCursor As Object
        Dim oComment As Object
    
        Dim oDateHour As New com.sun.star.util.DateTime
    
        ' If the element is a table, store its name
        If cName = "table:table" Then
            oTableName = oAttributes.getValueByName("table:name")
        End If   
    
        ' See OASIS Open Document Format for Office Applications:
        ' http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os.html
        ' The DDE data connection of tables is contained in an <office:dde-source> element.
        ' The usage of this element differs between spreadsheet and text document tables. 
        ' For text document tables, the element is contained within the table's <table:table> 
        ' element directly.
        ' Hence test if the table is DDE-linked:
        If cName = "office:dde-source" Then
    
            ' Access the table:
            oTable = oDoc.TextTables.getByName(oTableName)
    
            ' Create a cursor in the first cell of the table:
            oCursor = oTable.getCellByPosition(0, 0).createTextCursor
    
            ' Create a comment:
            oComment = oDoc.createInstance("com.sun.star.text.textfield.Annotation")
    
            ' Get time-stamp of right now:
            With oDateHour
                .Minutes = minute(now)
                .Hours = hour(now)
                .Day = day(now)
                .Month = month(now)
                .Year = year(now)
            End With
             
            ' Fill the comment with DDE source and range
            With oComment
                .Author = "Source: DDE link"
                .Content = oAttributes.getValueByName("office:dde-item") & chr(13) &_
                    oAttributes.getValueByName("office:dde-topic")
                .DateTimeValue = oDateHour
            End With
    
            ' Insert the comment in the cell
            oComment.attach(oCursor.start)
        End If
    
    End Sub ' DocHandler_startElement
    
    
    Sub DocHandler_endElement( cName As String )
    End Sub
    
    Sub DocHandler_characters( cChars As String )
    End Sub
    
    Sub DocHandler_ignorableWhitespace( cWhitespace As String )
    End Sub
    
    Sub DocHandler_processingInstruction( cTarget As String, cData As String )
    End Sub
    
    Sub DocHandler_setDocumentLocator( oLocator As com.sun.star.xml.sax.XLocator )
    End Sub

    Notes

    1. DDE: "Dynamic Data Exchange", allows you to link objects by reference to the file, without embedding them.
    2. See the standard OASIS Open Document Format for Office Applications. The use of this element differs between spreadsheet and word processing. For text documents, the element is contained directly inside the element <table:table>.
    3. See the API documentation for com.sun.star.xml.sax.Parser
    4. See the API documentation for com.sun.star.xml.sax.XDocumentHandler