Identifying DDE-linked tables

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Macros
• General Macros
• Macros for Writer
• Macros for Calc
• Macros for Impress
• Macros for Draw
• Macros for Base
• Macros for Math
• Basic/Python

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.

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

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 associated with the listner com.sun.star.xml.sax.XDocumentHandler.

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