L'ordinateur

Structures arborescentes
à partir de cellules fusionnées.

Macros de LibreOffice

.

 L'Hydraule


Dans des relevés chiffrés tels que ceux que l'on peut faire à l'aide des tableurs, il est courant que des en-têtes de colonnes utilisent des cellules fusionnées.

Notes Pied Corps Bouche
Diamètre Longueur Diamètre Longueur Largeur Hauteur
Intérieur Extérieur

Cela évite des redites et cela permet aussi de présenter les choses plus simplement, particulièrement pour les utilisateurs novices ou peu expérimentés qui, parce que ce type de présentations est très visuel, peuvent aisément mettre en place ces structures au moyen de la souris.

Par contre, dans la perspective d'établir une base de données strictement tabulaire autant récupérable par un langage de programmation que par un SGBD (R), cela complexifie nettement la dénomination des colonnes de données. On peut alors envisager traduire les noms des strictes colonnes en se servant d'un séparateur tel le point (« . »), dans la mesure entendue où il ne soit pas utilisé au sein-même des cellules. Donc, pour l'exemple qui précède les deuxième et quatrième colonnes peuvent respectivement se nommer : « Pied.Diamètre » et « Corps.Diamètre.Intérieur ».

 


 
Dans tous les cas, on a bien là affaire à une structure arborescente :

Si l'on possède beaucoup de feuilles de tableur avec des structures de ce type, cela peut s'avérer très long, voire particulièrement fastidieux à « lire » dans l'optique d'un portage de données... On se prend alors à rêver d'une macro qui permettrait de faire l'analyse de cette structure arborescente, ne laissant à l'utilisateur que le travail de sa mise en place au moyen des cellules fusionnées, suivie d'une simple sélection de l'ensemble pour en définir les limites, à charge à l'algorithme de faire ensuite plus ample fruits de cette analyse...

  
	<SELECTION>
		<CELLULE>Notes</CELLULE>
		<CELLULE>
			Pied
			<CELLULE>Diamètre</CELLULE>
			<CELLULE>Longueur</CELLULE>
		</CELLULE>
		<CELLULE>
			Corps
			<CELLULE>
				Diamètre
				<CELLULE>Intérieur</CELLULE>
				<CELLULE>Extérieur</CELLULE>
			</CELLULE>
			<CELLULE>Longueur</CELLULE>
		</CELLULE>
		<CELLULE>
			Bouche
			<CELLULE>Largeur</CELLULE>
			<CELLULE>Hauteur</CELLULE>
		</CELLULE>
	</SELECTION>

Car une fois l'arborescence déchiffrée, elle peut se décliner en beaucoup de choses très rigolotes et nettement plus signifiantes que des cellules fusionnées... Comme déjà dit ci-dessus, cela peut certes se limiter à une liste des noms de cellules séparés par un séparateur mais aussi se voir transformé en un contrôle d'arborescence (TreeView), voire last but not least en code XML... Donc, l'exemple qui précède peut déjà s'envisager traduit comme ci-contre :


____________


C'est la finalité de la macro présentée ici qui résume ces trois différentes issues au moyen d'une boite de dialogue appropriée.

La lecture séquentielle des cellules est très proche de celle des répertoires de système de fichiers si ce n'est, peut être quand même, un peu plus compliquée à définir puisque là où une instruction comme « getFolderContents » renvoie directement un tableau des URLs complètes de chaque fichier d'un répertoire donné, il faut ici, non seulement définir les bords gauche et droit de la cellule pour en analyser le contenu situé juste en dessous mais également énumérer les enfants un par un... Ceci implique aussi la projection imaginaire d'une cellule fusionnée, juste au-dessus de la sélection et faisant exactement sa largeur ; c'est là la mère de toutes les autres. Dans tous les cas, comme la profondeur des branches n'est pas fixe, on emploie la programmation récursive, la procédure « Sub » s'appelant naturellement elle-même.

Dans la mise en place de cellules fusionnées, il faut respecter scrupuleusement une règle : l'ensemble des couches de cellules doivent atteindre la dernière ligne de la sélection étant acquis qu'aucune cellule « enfant » ne se trouve située sous elle. Pour l'exemple présenté ci-dessus, on peut donc constater que les cellules « Diamètre » et « Longueur » qui se trouvent sous la cellule « Pied » s'étalent sur deux lignes.

Mais l'algorithme de la macro fonctionne même si c'est la cellule « Pied » qui s'étale sur deux lignes, ne laissant alors plus qu'une seule ligne aux cellules « Diamètre » et « Longueur ». Bref, cela ne change rien pour autant que l'on respecte bien le remplissage total en hauteur de la sélection.


Notes Pied Corps Bouche
Diamètre Longueur Diamètre Longueur Largeur Hauteur
Intérieur Extérieur

De même, il est capital que le contenu de toutes cellules fusionnées se trouve dans celle située la plus en haut et à gauche puisque c'est à cet endroit précis qu'est récupéré le contenu textuel. Enfin, il ne pose pas de problème à l'algorithme de lire une cellule fusionnée en largeur sur plusieurs colonnes de la feuille du tableur alors qu'elle ne possède pas de cellule enfant. C'est ici le cas de la cellule « Notes » (Cf. fichier joint).

  Boite de dialogue appelée.
On notera que la mise en mémoire de l'arborescence se fait directement dans une structure XML, en employant le DOM disponible dans le Basic de la suite logicielle. Ainsi, les deux types d'affichage de la boite de dialogue (« TreeView » et « Champ séparés ») ne sont-ils que des interprétations du document XML créé ex-nihilo. C'est ce même DOM que l'on retrouve en JavaScript ou PHP (pour ne citer que des langages grand-public) avec une syntaxe extrêmement similaire. En apprendre le fonctionnement et en intégrer l'usage peut donc laisser entrevoir des fruits sur d'autres arbres que notre suite bureautique surtout si l'on projette une inter-connectivité de données dont la suite serait le premier maillon de saisie...

Pour analyser et comprendre le code ou en vue de retouches ultérieures, on enregistre le document XML par le bouton approprié de la boite de dialogue. À ce propos, je n'ai pas pris la peine de visualiser le fichier XML, dans un « testField » puisque les navigateurs Web font cela fort bien. Donc une fois le fichier écrit, il est demandé une visualisation au sein d'un navigateur. Celui-ci est réglable par la constante « navigateur » présente en début de macro.

On notera aussi qu'il a été porté une attention particulière à conserver les attributs « noColonne » et « largColonne » dans les balises <CELLULE> même si cela n'est pas utile pour le contrôle de treeView ou l'affichage en champs séparés. Ce n'est pas (seulement) pour faire une démonstration des possibilités du DOM... C'est bien parce que le but de cette petite plaisanterie est, plus tard, de refaire une sélection sur les colonnes de chiffres contenues dans la feuille de tableur pour les introduire dans la même structure XML. Sachant que la colonne du tableur se trouve ici notée, il deviendra alors possible d'y faire référence pour l'incorporation des données.

Donc, si la finalité de cette macro n'est pas très explicite pour les néophytes en ces matières, elle me semble, par contre, évidente pour qui connaît l'avantage et la puissance des fichiers XML. Elle laisse envisager le traitement de fichiers complexes, particulièrement pour récupérer des données saisies assez anarchiquement au sein de feuilles de tableur, avec l'optique manifeste de les standardiser dans des bases de données ou, comme c'est mon cas, dans l'établissement d'un langage de description métier reposant sur du XML.

Enfin, tout ceci ne relève pas exactement de la génération spontanée... On pourra aussi consulter mes principales sources énumérées ici :



Télécharger le fichier au format ODS.

Le code.

Module « XML » :

 
' Structures arborescentes à partir de cellules fusionnées. Par S.M.C.J. - Code placé dans le domaine public.

Option Explicit

Private maBD As Object, monXML As Object, monArbreDataModel As Object

' Const navigateur = "C:\Program Files\Mozilla Firefox\firefox.exe"                  ' M$-WouinDaube.
Const      navigateur = "firefox"                                                    ' Manchot.
Const      separateur = "."                                                          ' Séparateur de champs ; la chaine peut avoir n'importe quelle longueur, y compris nulle.

Const baliseRacine  = "SELECTION"                                                    ' Nom de la balise XML racine.
Const baliseCellule = "CELLULE"                                                      ' Nom de la balise XML de cellule.
Const   attributCol = "noColonne"                                                    ' Nom de l'attribut de numéro de colonne dans la balise XML de cellule.
Const  attributLarg = "largColonne"                                                  ' Nom de l'attribut de largeur de colonne dans la balise XML de cellule.

' Explore l'arborescence des cellules de la sélection courante.
Sub exploArboCellules()
 Dim monDocumentBuilder As Object, monArbreElement As Object

 ' Création ex-nihilo.
 monDocumentBuilder = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
             monXML = monDocumentBuilder.newDocument()

 explorerCellule(thisComponent.currentSelection, monXML)                             ' Scanne la sélection courante dans une structure XML.

 ' Affiche la boite de dialogue.
 dialogLibraries.LoadLibrary("Standard")
 maBD = createUnoDialog(DialogLibraries.Standard.dialogueArbo)

 ' Gestion du contrôle « treeView ».
 monArbreDataModel = createUnoService("com.sun.star.awt.tree.MutableTreeDataModel")
   monArbreElement = monArbreDataModel.createNode("Sélection", true)
 monArbreDataModel.setRoot(monArbreElement)

 boucleElements(monXML.firstChild, monArbreDataModel.root)                           ' On part bien de <SELECTION>.

 maBD.getControl("treeControl").Model.DataModel = monArbreDataModel
 etendToutNoeuds(maBD.getControl("treeControl"), monArbreDataModel.root)             ' Étend toutes les branches de l'arbre du « treeView ».

 maBD.execute() : maBD.Dispose
End Sub


' Explore les cellules particulière et écrit dans le DOM.
Sub explorerCellule(byVal celluleEnCours As Object, byVal monObjetXML As Object)
 Dim monAdresseSelection As Object,       maFeuille As Object, monCurseurCellule As Object, maCellule As Object, maBaliseXML As Object, monNoeud As Object
 Dim             maLigne As Integer, maLimiteGauche As Integer,   maLimiteDroite As Integer,        i As Integer

 monAdresseSelection = thisComponent.currentSelection.rangeAddress
           maFeuille = thisComponent.sheets(monAdresseSelection.Sheet)

 If (celluleEnCours.AbsoluteName = thisComponent.currentSelection.AbsoluteName) Then ' Première cellule virtuelle située juste au dessus de la sélection.
     maLimiteGauche = monAdresseSelection.startColumn
     maLimiteDroite = monAdresseSelection.endColumn
            maLigne = monAdresseSelection.startRow - 1

        maBaliseXML = monObjetXML.createElement(baliseRacine)                        ' Création de la balise XML <SELECTION>.
      monXML.appendChild(maBaliseXML)
        monObjetXML = monObjetXML.lastChild
 Else                                                                                ' Cellule normale (fusionnée ou pas).
  monCurseurCellule = maFeuille.createCursorByRange(celluleEnCours)
  monCurseurCellule.collapseToMergedArea
 
     maLimiteGauche = monCurseurCellule.rangeAddress.startColumn
     maLimiteDroite = monCurseurCellule.rangeAddress.endColumn
            maLigne = monCurseurCellule.rangeAddress.endRow
 End If

 ' Catalogue des cellules (éventuellement fusionnées situées en dessous de « maMigne ».
 i = maLimiteGauche
 While (i <= maLimiteDroite)
  ' Lecture dans la feuille du tableur.
          maCellule = maFeuille.getCellByPosition(i, maligne + 1)
  monCurseurCellule = maFeuille.createCursorByRange(maCellule)
  monCurseurCellule.collapseToMergedArea

  ' écriture dans la structure XML.
  maBaliseXML = monXML.createElement(baliseCellule)                                  ' Création de la balise XML <CELLULE>.
  maBaliseXML.setAttribute(attributCol, i)                                           ' Création de l'attribut XML « noColonne="" ».
                                                                                     ' Création de l'attribut XML « largColonne="" ».
  maBaliseXML.setAttribute(attributLarg, (monCurseurCellule.rangeAddress.endColumn - monCurseurCellule.rangeAddress.startColumn + 1))

  monObjetXML.appendChild(maBaliseXML)
 
  monNoeud = monXML.createTextNode(maCellule.String)
  monObjetXML.lastChild.appendChild(monNoeud)

  i = monCurseurCellule.RangeAddress.endColumn + 1                                   ' Saut sur la colonne située à droite.

  ' Ne provoque la RéCURSION que dans la mesure où elle ne dépasse pas les limites imposées par la sélection.
  If (monCurseurCellule.RangeAddress.EndRow < monAdresseSelection.endRow) Then explorerCellule(maCellule, maBaliseXML)
 Wend
End Sub


' Lecture récursive du document XML.
Sub boucleElements(byVal monObjetXML As Object, byVal maBrancheArbre As Object)
 Dim listeNoeuds As Object, monArbreElement As Object, monCheminElement As Object
 Dim           i As Integer
 Dim   monChemin As String

 listeNoeuds = monObjetXML.ChildNodes

 For i = 0 To (listeNoeuds.length - 1)
  monObjetXML = listeNoeuds.item(i)

  Select Case monObjetXML.getNodeType()
   Case com.sun.star.xml.dom.NodeType.ELEMENT_NODE                                   ' C'est un nœud, donc une balise             ( = 6  ).
    ' Crée une branche de l'ardre du « treeView ».
	monArbreElement = monArbreDataModel.createNode(monObjetXML.firstChild.data, False)
	maBrancheArbre.appendChild(monArbreElement)
    monArbreElement = maBrancheArbre.getChildAt(maBrancheArbre.ChildCount - 1)

   Case com.sun.star.xml.dom.NodeType.TEXT_NODE                                      ' C'est une feuille, donc un contenu textuel ( = 11 ).
    ' À la manière du petit Poucet, reconstitution du chemin qui nous a permis d'en arriver là...
    monCheminElement = monObjetXML.parentNode

    If (monCheminElement.ChildNodes.length = 1) Then                                 ' Dans la mesure entendue oû nous soyons strictement en fin de branche...
     While (monCheminElement.nodeName <> baliseRacine)                               ' C'est parti !
      monChemin = monCheminElement.firstChild.data & separateur & monChemin
      monCheminElement = monCheminElement.parentNode
     Wend

     monChemin = left(monChemin, (len(monChemin) - len(separateur)))                 ' Le séparateur final dégage.
     maBD.getControl("textFieldChamps").model.text = maBD.getControl("textFieldChamps").model.text & monChemin & chr(13)
    End If
  End Select

  If (listeNoeuds.length > 0) Then boucleElements(monObjetXML, monArbreElement)      ' Récursion
 Next i
End Sub


' Étend toutes les branches de l'arbre du « treeView ».
Sub etendToutNoeuds(monArbre As Object, monParent As Object)
 Dim monEnfant As Object
 Dim         i As Integer

 For i = 0 To (monParent.getChildCount() - 1)
  monEnfant = monParent.getChildAt(i)
  monArbre.expandNode(monEnfant)
  If (monEnfant.getChildCount() > 0) Then etendToutNoeuds(monArbre, monEnfant)       ' Récursion.
 Next i
End Sub


' Écriture du fichier XML.
Sub ecritFichierXML()
 Dim     monSFA As Object, monStream As Object, sv As Object
 Dim monFichier As String

 monFichier = enregistrerSous()
 If (len(monFichier) <> 0) Then                                                      ' Enregistrement effectif.
  monSFA = createUNOService("com.sun.star.ucb.SimpleFileAccess")
  
  If monSFA.exists(monFichier) Then monSFA.kill(monFichier)                          ' Éviter les emmerdes de réécritures...
  
  monStream = monSFA.openFileWrite(monFichier)
  monXML.setOutputStream(monStream) :  monXML.start() : monStream.closeOutput()

  If (msgBox("Le fichier a été enregistré." & chr(13) & "Visualiser son contenu dans un navigateur Web ?", 36, "Enregistrement effectué") = 6) Then
   sv = createUnoService("com.sun.star.system.SystemShellExecute")
   sv.execute(navigateur, convertToUrl(monFichier), 0)
  End If
 Else                                                                                ' Annulation de l'enregistrement.
  msgBox("Le fichier n'a pas été enregistré.", 16, "Enregistrement annulé")
 End If
End Sub
 


Module « communs » :

 
Option Explicit

'Appel de la boite de dialogue OfficeFilePicker pour enregistrement d'un fichier.
Function enregistrerSous()
 Dim monFilePicker As Object

 'Configuration de l'OfficeFilePicker (et non de "com.sun.star.ui.dialogs.FilePicker" qui fonctionne très mal...).
 monFilePicker = CreateUnoService("com.sun.star.ui.dialogs.OfficeFilePicker")
 
 With monFilePicker
  .Initialize(array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_AUTOEXTENSION))
  .appendFilter("Fichier XML"            , "*.xml")
  .appendFilter("Document ODL"           , "*.odl")
  .appendFilter("Tous les fichiers (*.*)", "*.*"  )
  .setCurrentFilter("Document ODL")
  
  .setDisplayDirectory(donneDossier(thisComponent.URL))
  .setTitle("Enregistrer le fichier d'arborescence")
  .setDefaultName("Sans nom")
  .setValue(com.sun.star.ui.dialogs.ExtendedFilePickerElementIds.CHECKBOX_AUTOEXTENSION, 0, True) ' Active la case à cocher d'extension automatique.
 End With

 If monFilePicker.execute() Then enregistrerSous = monFilePicker.files(0)
 monFilePicker.dispose
End Function

' Renvoi le répertoire courant.
Function donneDossier(chemin As String) As String
 Dim i As Integer

 For i = Len(chemin) to 1 step -1
  If Mid(chemin, i, 1) = "/" Then donneDossier = Left(chemin, i) : Exit for
 Next i
End Function
 


Le résultat sous forme de fichier XML :