[Calc][XML] Tree structures from merged cells.

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

[Calc][XML] Tree structures from merged cells.

Postby Sébastien C » Sun Nov 13, 2016 9:43 pm

 Edit: Nota bene:
This post is a translation of this personal Web page in my so poor English. If somebody want to write to me a private message for communicate to me a best translation (or expression), it is important to know that I have realy nothing against it... 

In the suveys such as those that can be done using spreadsheets, it is common to use merged cells for the column headers.

lines.png (3.02 KiB) Viewed 2277 times

This avoids repetitions and it also makes it possible to present things more simply, especially for novice or inexperienced users who, because this type of presentations is very visual, can easily set up these structures by means of the mouse.

On the other hand, in order to establish a strictly tabular database as much recoverable by a programming language as by a DBMS (R), this clearly complicates the naming of data columns. One can then envisage translating the names of the strict columns by using a separator such as the point ("."), insofar as it is understood that it is not used within the cells themselves. So, for the example that precedes, the second and fourth columns, respectively, may be called "Foot.Diameter" and "Body.Diameter.Inside".

In any case, we are dealing with a tree structure:

littleTree.png (1.05 KiB) Viewed 2277 times

If you have a lot of spreadsheet with structures of this type, it can be very long, or even particularly tedious, to "read" in the perspective of a data port. We can so dream of a macro that would allow the analysis of this tree structure, leaving to the user only the work of its define by means of the merged cells, followed by a simple selection of the set to define the limits, charging the algorithm to make more profits from this analysis...

Because once the tree is decrypted, it can be declared in many things that are very funny and much more significant than merged cells... As already mentioned above, this may be limited to a list of cell names separated by A separator but also be transformed into a TreeView control, or even last but not least, in XML code... So the preceding example can already be translated as follows:

Code: Select all   Expand viewCollapse view
<?xml version="1.0"?>

It is the purpose of the macro presented here to summarize these three different possibilities by means of an appropriate dialog box.

The sequential reading of the cells is very similar to that of the file system directories [French, sorry], but maybe a little more complicated to define, since where an instruction like "getFolderContents" directly returns a full URLs array of each file of a given directory, it is here necessary to define, not only the left and right edges of the cell to analyze the contents located just below it, but also to enumerate the childrenw one by one... This also implies the imaginary projection of a merged cell, just above the selection and measuring exactly its width. This is the mother of all the others. In all cases, since the depth of the branches is not fixed, recursive programming is used, the "Sub" procedure naturally calling itself.

When setting up the merged cells, a rule must be scrupulously observed: all cell layers must reach the last line of the selection, it being understood that no "child" cell is located beneath it. For the example presented above, it can thus be seen that the cells "Diameter" and "Length" which lie under the cell "Foot" are spread over two lines.

But the algorithm of the macro works even if it is the cell "Foot" which spreads on two lines, leaving then only one line to the cells "Diameter" and "Length". In short, this does not change anything as long as you respect the full height filling of the selection.

animLines.gif (9.72 KiB) Viewed 2277 times

Similarly, it is essential that the contents of all merged cells lie in the topmost and left-most content, since this is where the text content is retrieved. Finally, there is no problem with the algorithm of reading a cell merged in width over several columns of the spreadsheet while it does not have a child cell. This is the case for the "Notes" cell (see attached file).

It should be noted that the tree is stored directly in an XML structure, using the DOM available in the Basic of the software suite. Thus, the two types of display in the dialog box ("TreeView" and "Separate fields") are only interpretations of the XML document created ex-nihilo. It is this same DOM that can be found in JavaScript or PHP (to name only mainstream languages) with an extremely similar syntax. To learn the functioning and to integrate its use can therefore give us glimpses of fruits on other trees than our office suite, especially if we project an inter-connectivity of data whose sequel would be the first part of data entry...

To analyze and understand the code or for subsequent retouching, the XML document is saved by the appropriate button in the dialog box. In this regard, I did not bother to visualize the XML file, in a "textField" since web browsers do this very well. So once the file is written, it is requested a visualization within a browser. This is adjustable by the "browser" constant present at the beginning of the macro.

It should also be noted that special attention has been paid to keeping the "columnNumber" attribute in the <CELL> tags, even if this is not useful for treeView control or display in separate fields. It is not (only) to make a demonstration of the possibilities of the DOM... It is well because the purpose of this little joke is, later, to make a selection on the columns of numbers contained in the Spreadsheet to introduce them in the same XML structure. Knowing that the column of the spreadsheet is noted here, it will then become possible to refer to it for the data’s incorporation.

So if the purpose of this macro is not very explicit for the neophytes in these matters, it seems to me, on the other hand, obvious to anyone who knows the advantage and the power of the XML files. It makes it possible to envisage the processing of complex files, particularly to retrieve data that have been entered rather anarchically into spreadsheet, with the obvious objective of standardizing them in databases or, as I do for my profession (organbuilder), in the establishment of a description language, based on XML.

Finally, all this does not belong exactly to the spontaneous generation ... The French forum having provided me the main sources listed here (of course in French, I am sorry):

Last edited by Sébastien C on Mon Nov 14, 2016 5:42 pm, edited 2 times in total.
LibreOffice under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
Posts: 92
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc][XML] Tree structures from merged cells.

Postby Sébastien C » Sun Nov 13, 2016 9:45 pm

 Edit: Nota bene:
Continued here because there are no possibility to put more than three attachments in a message of this forum. 

The code:

"XML" module.

Code: Select all   Expand viewCollapse view
' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Tree structures from merged cells. By S.M.C.J. Code placed in the public domain. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Option Explicit

Private myDB As Object, myXML As Object, myTreeDataModel As Object

Const         browser = "C:\Program Files\Mozilla Firefox\firefox.exe"               ' M$-Window$.
' Const       browser = "firefox"                                                    ' Penguin.
Const       separator = "."                                                          ' Field separator; The string can have any length, including void.

Const        rootTag  = "SELECTION"                                                  ' Name of the root XML tag.
Const         cellTag = "CELL"                                                       ' Name of the cell XML tag.
Const columnAttribute = "columnNumber"                                               ' Name of the attribute of the column number in the cell XML tag.
Const  widthAttribute = "columnWidth"                                                ' Name of the attribute of the column width in the cell XML tag.

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Exploring the cells tree structure of the current selection.                     ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub exploArboCellules()
Dim myDocumentBuilder As Object, myTreeElement As Object

' Ex-nihilo creation.
myDocumentBuilder = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
             myXML = myDocumentBuilder.newDocument()

exploreCell(thisComponent.currentSelection, myXML)                                  ' Scans the current selection in an XML structure.

' Displays the dialog box.
myDB = createUnoDialog(DialogLibraries.Standard.treeDialog)

' Management of the treeView control.
myTreeDataModel = createUnoService("com.sun.star.awt.tree.MutableTreeDataModel")
   myTreeElement = myTreeDataModel.createNode("Selection", true)

elementsLoop(myXML.firstChild, myTreeDataModel.root)                                ' Starting from <SELECTION>.

myDB.getControl("treeControl").Model.DataModel = myTreeDataModel
expandsAllNodes(myDB.getControl("treeControl"), myTreeDataModel.root)               ' Expands all tree branches of the treeView.

myDB.execute() : myDB.Dispose
End Sub

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Exploring the particular cells and written in the DOM.                           ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub exploreCell(byVal celluleEnCours As Object, byVal myXMLObject As Object)
Dim mySelectionAddress As Object,         mySheet As Object,     myCellCursor As Object, myCell As Object, myXMLTag As Object, myNode As Object
Dim             myLine As Integer, myLeftBoundary As Integer, myRightBoundary As Integer,     i As Integer

mySelectionAddress = thisComponent.currentSelection.rangeAddress
            mySheet = thisComponent.sheets(mySelectionAddress.Sheet)

If (celluleEnCours.AbsoluteName = thisComponent.currentSelection.AbsoluteName) Then ' First virtual cell located just above the selection.
     myLeftBoundary = mySelectionAddress.startColumn
    myRightBoundary = mySelectionAddress.endColumn
             myLine = mySelectionAddress.startRow - 1

           myXMLTag = myXMLObject.createElement(rootTag)                             ' Creating the <SELECTION> XML tag.
        myXMLObject = myXMLObject.lastChild
Else                                                                                ' Normal cell (merged or not).
       myCellCursor = mySheet.createCursorByRange(celluleEnCours)

     myLeftBoundary = myCellCursor.rangeAddress.startColumn
    myRightBoundary = myCellCursor.rangeAddress.endColumn
             myLine = myCellCursor.rangeAddress.endRow
End If

' Catalog of cells (possibly merged below "myLine").
i = myLeftBoundary
While (i <= myRightBoundary)
  ' Reading in spreadsheet.
          myCell = mySheet.getCellByPosition(i, myLine + 1)
    myCellCursor = mySheet.createCursorByRange(myCell)

  ' Writing in the XML structure.
        myXMLTag = myXML.createElement(cellTag)                                      ' Creating the <CELL> XML tag.
  myXMLTag.setAttribute(columnAttribute, i)                                          ' Creating the nbColumn="" XML attribute.
                                                                                     ' Creating the widthColumn="" XML attribute.
  myXMLTag.setAttribute(widthAttribute, (myCellCursor.rangeAddress.endColumn - myCellCursor.rangeAddress.startColumn + 1))


          myNode = myXML.createTextNode(myCell.String)

               i = myCellCursor.RangeAddress.endColumn + 1                           ' Jump to the column on the right.

  ' Only causes RECURSION insofar as it does not exceed the limits imposed by the selection.
  If (myCellCursor.RangeAddress.EndRow < mySelectionAddress.endRow) Then exploreCell(myCell, myXMLTag)
End Sub

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Recursive reading of the XML document.                                           ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub elementsLoop(byVal myXMLObject As Object, byVal myTreeBranch As Object)
Dim nodesList As Object, myTreeElement As Object, myElementPath As Object
Dim           i As Integer
Dim   myPath As String

nodesList = myXMLObject.ChildNodes

For i = 0 To (nodesList.length - 1)
  myXMLObject = nodesList.item(i)

  Select Case myXMLObject.getNodeType()
   Case com.sun.star.xml.dom.NodeType.ELEMENT_NODE                                   ' It is a node, therefore a tag              ( =  6 ).
    ' Creating a tree branch of the treeView.
   myTreeElement = myTreeDataModel.createNode(myXMLObject.firstChild.data, False)
    myTreeElement = myTreeBranch.getChildAt(myTreeBranch.ChildCount - 1)

   Case com.sun.star.xml.dom.NodeType.TEXT_NODE                                      ' It is a sheet, therefore a textual content ( = 11 ).
    ' Like the little Poucet (French tale), reconstruction of the path that allowed us to get there...
    myElementPath = myXMLObject.parentNode

    If (myElementPath.ChildNodes.length = 1) Then                                    ' To the extent that we are strictly at the branch’s end...
     While (myElementPath.nodeName <> rootTag)                                       ' Let's go!
      myPath = myElementPath.firstChild.data & separator & myPath
      myElementPath = myElementPath.parentNode

     myPath = left(myPath, (len(myPath) - len(separator)))                           ' The final separator leaves.
     myDB.getControl("textField").model.text = myDB.getControl("textField").model.text & myPath & chr(13)
    End If
  End Select

  If (nodesList.length > 0) Then elementsLoop(myXMLObject, myTreeElement)            ' Recursion.
Next i
End Sub

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Expands all tree branches of the treeView.                                       ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub expandsAllNodes(myTree As Object, myParent As Object)
Dim myChild As Object
Dim       i As Integer

For i = 0 To (myParent.getChildCount() - 1)
  myChild = myParent.getChildAt(i)
  If (myChild.getChildCount() > 0) Then expandsAllNodes(myTree, myChild)             ' Recursion.
Next i
End Sub

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Writing the XML file.                                                            ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub saveXMLFile()
Dim  mySFA As Object, myStream As Object, myService As Object
Dim myFile As String

myFile = saveAs()
If (len(myFile) <> 0) Then                                                          ' Effective saving.
  mySFA = createUNOService("com.sun.star.ucb.SimpleFileAccess")
  If mySFA.exists(myFile) Then mySFA.kill(myFile)                                    ' Avoid the rewriting's bullshit...
  myStream = mySFA.openFileWrite(myFile)
  myXML.setOutputStream(myStream) :  myXML.start() : myStream.closeOutput()

  If (msgBox("The file is correctly saved." & chr(13) & "View its contents in a web browser?", 36, "Saving done") = 6) Then
   myService = createUnoService("com.sun.star.system.SystemShellExecute")
   myService.execute(browser, convertToUrl(myFile), 0)
  End If
Else                                                                                ' Cancel the saving.
  msgBox("The file is not saved.", 16, "Cancel the saving")
End If
End Sub

"Commons" module.

Code: Select all   Expand viewCollapse view

Option Explicit

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Call the OfficeFilePicker dialog box to save a file.                             ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Function saveAs()
Dim myFilePicker As Object

' Configuring the OfficeFilePicker (and not "com.sun.star.ui.dialogs.FilePicker" that works very badly)...
myFilePicker = createUnoService("com.sun.star.ui.dialogs.OfficeFilePicker")

With myFilePicker
  .initialize         (array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION)           )
  .appendFilter       ("XML file"       , "*.xml"                                                          )
  .appendFilter       ("All files (*.*)", "*.*"                                                            )
  .setCurrentFilter   ("XML file"                                                                          )
  .setDisplayDirectory(getDirectory(thisComponent.URL)                                                     )
  .setTitle           ("Save the file of tree structure."                                                  )
  .setDefaultName     ("noName"                                                                            )
  .setValue           (com.sun.star.ui.dialogs.ExtendedFilePickerElementIds.CHECKBOX_AUTOEXTENSION, 0, True) ' Enables the automatic extension check box.
End With

If myFilePicker.execute() Then saveAs = myFilePicker.files(0)
End Function

' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Returns the current directory.                                                   ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Function getDirectory(myPath As String) As String
Dim i As Integer

For i = len(myPath) to 1 step -1
  If mid(myPath, i, 1) = "/" Then getDirectory = left(myPath, i) : Exit for
Next i
End Function
(39.86 KiB) Downloaded 185 times
LibreOffice under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
Posts: 92
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest