Les principaux objets LO Calc

Sources :

Pour écrire une macro-commande, il est indispensable de savoir comment accéder aux différents éléments composant un classeur. Ce billet en décrit donc  les principaux  et  quelques méthodes et propriétés associées dans les paragraphes 1 à 7 suivants.
Il fournit ensuite quelques exemples de code de macro-commande ainsi que la façon de les mettre en oeuvre.

1. Le classeur actif : thisComponent

Le classeur actif (en cours d'utilisation) est accessible dans Apache OpenOffice  via l'objet ThisComponent ou via  l'objet StarDesktop.CurrentComponent

Dim LeClasseur as Object
LeClasseur = thisComponent

Le méthode createInstance de l'objet thisComponent, crée un objet "feuille de calcul" lorsqu'elle a pour argument le service "com.sun.star.sheet.Spreadsheet"

Dim leClasseur as Object
leClasseur = thisComponent
Dim LaNouvelleFeuille as Object
LaNouvelleFeuille = leClasseur.createInstance("com.sun.star.sheet.Spreadsheet")

Ci-dessous, une version plus compacte de ce code

Dim LaNouvelleFeuille as Object
LaNouvelleFeuille = thisComponent.createInstance("com.sun.star.sheet.Spreadsheet")

Voir aussi l'utilisation des arguments

2. La liste des feuilles d'un classeur : Sheets

Pour être manipulée, LaNouvelleFeuille créée par le code du paragraphe ci-dessus doit être "insérée" dans la liste "Sheets" des feuilles du classeur par les méthodes insertByName ou insertNewByName.

  • LeClasseur.Sheets.insertByName("Feuille1", LaNouvelleFeuille)  insère l'objet "LaNouvelleFeuille" dans la liste "sheets" du classeur "LeClasseur" sous l'étiquette "Feuille1"
  • LeClasseur.Sheets.insertNewByName("Feuille1",0) insère, à la position 1, une nouvelle feuille dans la liste "sheets" du classeur "LeClasseur" sous l'étiquette "Feuille1"

Dim LeClasseur, LaNouvelleFeuille as Object

LeClasseur = thisComponent
LaNouvelleFeuille = LeClasseur.createInstance("com.sun.star.sheet.Spreadsheet")
LeClasseur.Sheets.insertByName("Feuille2", LaNouvelleFeuille)
LeClasseur.Sheets.insertNewByName("Feuille3",0)

Voir ci-dessous l'onglet InsertFeuille.

La propriété "count" de l'objet sheets rend le nombre de feuilles de la liste.
La méthode "copyByName ("feuilleSource", feuilleCible, position) place au rang position de la liste sheets une copie nommée "feuilleCible" de la feuille "feuilleSource".

Une feuille de calcul d'un classeur est accessible soit

  • par son numéro dans la liste des feuilles :

Dim  LaFeuille as Object.
LaFeuille = thisComponent.Sheets(0)

  • par son nom via la méthode "getByName" de l'objet "Sheets":

Dim LaFeuille as Object.
LaFeuille    = thisComponent.Sheets.getByName("Feuille1")
 

L'objet LaFeuille obtenu dispose des méthodes :

  • isVisible(boolean) :
    • LaFeuille.isVisible(False) pour cacher la feuille
    • LaFeuille.isVisible(True) pour afficher la feuille
  • pageStyle(string) :
    • LaFeuille.pageStyle("monModeleDeFeuille")

En complément de "getByName" et "insertByName", l'objet Sheets dispose  de la méthode  hasByName(string). Cette méthode, qui teste l'existence d'une feuille dans la liste sheets, doit être utilisée avant les méthodes getByName ou InsertByName pour éviter une erreur d'exécution.

  • ThisComponent.Sheets.hasByName("Feuille1") rend True si la feuille "Feuille1" existe, sinon rend False

Ci-dessous un exemple de code, utilisant les propriétés vues ci-dessus, ainsi que la méthode "copyByName" de sheets pour créer une copie d'une feuille existante.

    Dim MaFeuille as Object
    Dim NbFeuilles as Integer
    
    NbFeuilles = thisComponent.sheets.count
    if thisComponent.sheets.hasByName("Feuille1") then
        thisComponent.Sheets.copyByName("Feuille1","CopyFeuille1",NbFeuilles)
        MaFeuille= thisComponent.Sheets.getByName("CopyFeuille1")
        thisComponent.currentController.setActiveSheet(CopyFeuille1)
    end if

La propriété name de l'objet feuille rends le nom de la feuille.
En savoir plus .... https://wiki.documentfoundation.org/Macros/Basic/Calc/Sheets

3. La feuille active : activeSheet

La propriété activeSheet permet

  • d'accéder à la feuille active par

thisComponent.currentController.activeSheet

  • de changer de feuille active (LaNouvelleFeuilleActive est un objet Feuille (cf $ précédent))

thiscomponent.currentController.activeSheet = LaNouvelleFeuilleActive

La propriété setActiveSheet permet de changer de feuille active

Dim Mafeuille as Object
if thisComponent.sheets.hasByName("LaFeuilleData") then
      MaFeuille = thisComponent.sheets.getByName("LaFeuilleData")
      thisComponent.currentController.setActiveSheet(MaFeuille)
end if

La propriété name d'activeSheet rends le nom de la feuille active.

dim nomFeuilleActive as string
nomFeuilleActive=thiscomponent.currentController.activeSheet.name

 

4. Les lignes et les colonnes d'une feuille : Rows et Columns

Les méthodes Rows et Columns d'un objet "feuille de calcul" donne accès à la liste des lignes "Rows(integer)" ou à la liste des colonnes "Columns(integer)"

Dim LaFeuille as object
Dim ColonneA as object
dim Ligne1 as Object
Ligne1      = thisComponent.sheets(0).Rows(0)
ColonneA = thisComponent.sheets(0).Colums(0)

Les méthodes insertByIndex(X as integer, Y as integer) et removeByIndex(X as integer, Z as integer) appliquées aux objets lignes et colonnes permettent respectivement d'ajouter ou de supprimer Y lignes et Z colonnes

thisDocument.sheets(0).Rows.insertByIndex(4,2) : insère 2 lignes après la ligne 5

Quelques propriétés : isVisible(boolean) ; height(long) pour les lignes width(long) pour les colonnes

5. La cellule :

     getCellRangeByName et getCellByPosition

    Exemple :

    La méthode getCellRangeByName( "A1") d'un objet "feuille de calcul" donne accès à la cellule A1
    La méthode  getCellByPosition( 0, 0 ) donne accès à la cellule de coordonnées (0, 0) soit A1

    Les propriétés à utiliser sur une cellule dépendent du contenu de la cellule :

    • formula: le contenu est (sera) une formule (voir le $8 pour un exemple)
    • formulaArray : id pour les formules matricielles
      information complémentaire disponible en cliquant ici
      Les formules (simples ou matricielles doivent être écrites en anglais (par exemple : VLOOKUP pour RechercheV)
    • string : le contenu est (sera) une texte
    • value : le contenu est (sera) un nombre

    La propriété Type utilisée sur un cellule rends 0 (si la cellule est vide : EMPTY), 1 ( si elle contient un nombre : VALUE), 2 ( si elle contient du texte : STRING), 3 (si elle contient une formule : FORMULA).
    Ces constantes sont définies et énumérées dans com.sun.star.table.CellContentType

    Voir ci-dessous l'onglet "CelluleType"

    absoluteName :

    La propriété absoluteName appliquée, par exemple, à la cellule "A1" de la feuille "maFeuille"  rend le nom de la cellule sous la forme $maFeuille!$A$1.
    Appliquée à la plage "A1:C3" de la même feuille, elle rendra $maFeuille!$A$1:SC$3

    NB : obtenir le nom relatif de la cellule (sous la forme B2 par exemple) en cliquant ICI

    Dim LaCellule as Object
    LaCellule = thisComponent.currentController.activeSheet.getCellRangeByName("A5")
    msgBox LaCellule.absoluteName, MB_ICONINFORMATION

    getCellAdress ou cellAddress:

    La propriété getCellAddress (ou cellAddress ) rend le couple de coordonnées de la cellule

    • cellAddress.row rend le numéro de la ligne
    • cellAdress.column rend le numéro de la colonne

    Dim LaCellule as Object
    Dim LaLigne, LaColonne as Integer
    LaCellule = thisComponent.currentController.activeSheet.getCellRangeByName("A5")
            LaLigne      = LaCellule.cellAddress.row
            LaColonne = LaCellule.cellAddress.column
    msgBox ( "coordonnées : ("+ LaColonne + " , " + LaLigne + ")"), MB_ICONINFORMATION

    6. La plage de cellules :

    getCellRangeByName et getCellRangeByPosition

    Exemple :

    La méthode  getCellRangeByName( "C1:F6") d'un objet "feuille de calcul" donne accès à la plage de cellules "C1:F6".
    La méthode getCellRangeByPosition( 2,0, 5,5) donne accès à la même plage

    Lorsque A est une constante listée dans l'énumération "com.sun.star.sheet.GeneralFunction", la méthode computeFunction( A) appliquée à une plage effectue l'opération correspondant à la constante A

    Dim LaPlage As Object
    LaPlage = thisComponent.Sheets(0).getCellRangeByName("A1:C3")
    MsgBox LaPlage.computeFunction(com.sun.star.sheet.GeneralFunction.SUM), MB_ICONINFORMATION

    getRangeAddress ou rangeAddress

    La propriété getRangeAddress ou rangeAddress rend la liste de coordonnées de la plage

    • rangeAddress.startRow rend le numéro de la 1ère ligne de la plage
    • rangeAddress.startColumn rend le numéro de la 1ère colonne de la plage
    • rangeAddress.endColumn rend le numéro de la dernière colonne de la plage
    • rangeAddress.endRow rend le numéro de la dernière ligne de la plage

    NB : obtenir le nom relatif de la plage (sous la forme B2:F3 par exemple) en cliquant ICI

    Dim LaPlage as Object
    Dim LigneDepart, ColonneDepart, LigneFin, colonneFin as Integer
    LaPlage = thisComponent.currentController.activeSheet.getCellRangeByName("A5:F10")
            LigneDepart      = LaPlage.rangeAddress.startRow
            ColonneDepart = LaPlage.rangeAddress.startColumn
            LigneFin            = LaPlage.rangeAddress.endRow
            ColonneFin       = LaPlage.rangeAddress.endColumn
    msgBox ( "coordonnées : ("+ ColonneDepart + " , " + LigneDepart +" - " + colonneFin +" , " + ligneFin + ")"), MB_ICONINFORMATION

    columns  et rows

    columns et rows repère la liste des colonnes et des lignes d'une plage. Par exemple, columns(0) représente la première colonne de la plage.

    Les listes columns et rows d'une plage de cellule dispose de la propriété count qui rend respectivement le nombre de colonnes ou de lignes de la plage.
    Chaque colonne d'une liste columns dispose de la propriété name qui rend le nom de la colonne

    Dim LaPlage as Object
    Dim num as integer
    LaPlage = thisComponent.currentController.activeSheet.getCellRangeByName("A5:F10")
    num = LaPlage.columns.count
    msgBox ( LaPlage.columns(num -1).name, MB_ICONINFORMATION

    Pour en savoir plus : https://wiki.documentfoundation.org/Macros/Basic/Calc/Ranges

    7. La sélection active : currentSelection

    Dans un feuille de calcul, la sélection courante peut correspondre à :

    • une seule cellule ;
    • une plage de cellule ;
    • plusieurs plages disjointes.

    La propriété currentSelection de l'objet "thisCoponent" (le classeur actif) donne accès à la sélection courante

    Dim SelectionCourante as Object
    SelectionCourante = ThisComponent.CurrentSelection

    Pour distinguer la nature de la sélection courante, il faut utiliser les services :

    • com.sun.star.sheet.SheetCell lorsque la sélection est une seule cellule ;
    • com.sun.star.sheet.SheetCellRange lorsque la sélection est une seule cellule ;
    • com.sun.star.sheet.SheetCellRanges lorsque la sélection est une seule cellule

    grâce à sa propriété supportsService. Lorsque la nature est distinguée, on utilisera les propriétés correspondant à l'objet  (cf § ci-dessous) pour gérer les données des cellules

    Dim SelCourante as object

    SelCourante = ThisComponent.CurrentSelection
    if SelCourante.supportsService("com.sun.star.sheet.SheetCell") then
        ' la sélection est une seule cellule
            with SelCourante.cellAddress
                  msgBox ( "coordonnées : ("+ .column + " , " + .row+ ")"), MB_ICONINFORMATION
         end with
        '
    elseif SelCourante.supportsService("com.sun.star.sheet.SheetCellRange") then
       'la sélection est une plage de cellules
        with SelCourante.rangeAddress
                msgBox ( "coordonnées : ("+ .startColumn + " , " + .startRow + ") - ("  + .endColumn + " , " + .endRow + ")"), MB_ICONINFORMATION
       end with
    else
        msgbox "la sélection doit être soit une cellule, soit une plage de cellules", MB_ICONINFORMATION
    end if

    Pour obtenir le nom (relatif)  de la sélection courante, on pourra utiliser le service de conversion

    •   "com.sun.star.table.CellAddressConversion" si la sélection est une cellule
    •   "com.sun.star.table.CellRangeAddressConversion" si la sélection est une plage de cellules

    Dim SelCourante, Info as Object

    SelCourante = ThisComponent.CurrentSelection
    if SelCourante.supportsService("com.sun.star.sheet.SheetCell") then
        ' la sélection est une seule cellule
                   Info= ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
                   Info.Address = SelCourante.getCellAddress
                   msgbox  (Info.UserInterfaceRepresentation &  "  " & Info.PersistentRepresentation), MB_ICONINFORMATION
        '
    elseif SelCourante.supportsService("com.sun.star.sheet.SheetCellRange") then
       'la sélection est une plage de cellules
                  Info = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
                  Info.address = SelCourante.rangeAddress
                  msgbox  (Info.UserInterfaceRepresentation &  "  " & Info.PersistentRepresentation), MB_ICONINFORMATION
    else
        msgbox "la sélection doit être soit une cellule, soit une plage de cellules", MB_ICONINFORMATION
    end if

    8. Accès aux fonctions internes

    Pour simplifier l'écriture de ce paragraphe, je prends l'exemple de la fonction MOYENNE() mais les indications fournies ici,  sont vraies avec toutes les fonctions internes de LO CALC.

    Lorsqu'une fonction interne de tableur ( ici MOYENNE ) est donnée, sous sa traduction anglaise (donc AVERAGE),  en paramètre de  l'attribut "formula" de cette cellule, la valeur de la cellule est la moyenne calculée. Cette valeur peut être récupérée par l'attribut "value" de cette cellule.

    Dans une macro-commande il est possible d'obtenir cette valeur sans passer par la cellule grâce à la méthode "callFunction" du service "com.sun.star.sheet.FunctionAccess" comme le montre l'exemple ci-après : 

     Dim laPlage as Object
     Dim parametres as variant
     Dim moy as double

         laPlage = thisComponent.currentController.activeSheet.getCellRangeByName("$C:$C")
         parametres = Array(laPlage)
         moy = CreateUnoService("com.sun.star.sheet.FunctionAccess").callFunction("AVERAGE", parametres())

    La méthode moins "propre" qui utilise une cellule en intermédiaire

     Dim laCellule as Object
     Dim laFormule as string
     Dim moy as double

         laCellule = thisComponent.currentController.activeSheet.getCellRangeByName("A1")
         laFormule  = "=AVERAGE($C:$C)"
         laCellule.formula = laFormule
         moy = laCellule.value

     

    9. Exemples simples de macros :

    • InsertionFeuille : cette macro ajoute 2 feuilles de calcul dans le classeur courant en utilisant les  méthodes "insertByName" et "insertNewByName"
    • CelluleType : cette macro affiche dans une boite de dialogue le type de la  cellule "A1" de la feuille active du classeur courant.
    • Copie et + : cette macro crée une copie d'une feuille de calcul lorsque 2 critères sont vérifiés :
      • la feuille source existe dans le classeur
      • la feuille cible n'existe pas
    • Sélection Courante : cette macro analyse la sélection courante et affiche :
      • les coordonnées puis le nom de la cellule lorsque la sélection est une cellule
      • le nom de la plage lorsque la sélection est une plage de cellule
    information complémentaire disponible en cliquant ici
    Analyse en cours de rédaction. Merci de patienter.
    Cliquez sur le bouton pour copier le code
    dans le presse papier
        
    Dim MonClasseur, MaFeuille As Object
    Dim Position as Integer
     
    MonClasseur = ThisComponent
     
    If MonClasseur.Sheets.hasByName("MaBelleFeuille")= False Then
       MaFeuille = MonClasseur.createInstance("com.sun.star.sheet.Spreadsheet")
       MonClasseur.Sheets.insertByName("MaBelleFeuille", MaFeuille)
    End If
             
    If MonClasseur.Sheets.hasByName("Feuille3")= False Then 
       MonClasseur.Sheets.insertNewByName("Feuille3", Position)
    End If
    
    Cliquez sur le bouton pour copier le code
    dans le presse papier
    Dim LaCellule As Object
     
    LaCellule = thisComponent.Sheets(0).getCellByPosition(0,0)
     
        With com.sun.star.table.CellContentType
            
            Select Case LaCellule.Type
                 Case .EMPTY '0
                       MsgBox "La cellule est vide", MB_ICONINFORMATION
                 Case .VALUE '1
                       MsgBox "La cellule contient un nombre",MB_ICONINFORMATION
                 Case .TEXT '2
                       MsgBox  "La cellule contient du texte", MB_ICONINFORMATION
                 Case .FORMULA '3
                       MsgBox  "La cellule contient une formule", MB_ICONINFORMATION
            End Select
        End with
         
    Cliquez sur le bouton pour copier le code
    dans le presse papier
    Dim MaFeuille, LaCellule as Object
        Dim NbFeuilles as Integer
        Dim nomFeuilleSource, nomFeuilleCible as String
        
        NbFeuilles = thisComponent.sheets.count
        nomFeuilleSource = inputBox ("nom de la feuille à copier ?",  Mb_OK, "Feuille1")
        if thisComponent.sheets.hasByName(nomFeuilleSource)  then    
            nomFeuilleCible = inputBox ("quel nom pour la copie ?", "Opération Copie", "Copie de ...")
            if thisComponent.sheets.hasByName(nomFeuilleCible) = False then
                thisComponent.Sheets.copyByName(nomFeuilleSource,nomFeuilleCible,NbFeuilles)
                MaFeuille= thisComponent.Sheets.getByName(nomFeuilleCible)
                thisComponent.currentController.setActiveSheet(MaFeuille)
            end if
        end If
            
        LaCellule = thisComponent.currentController.activeSheet.getCellRangeByName("A1")
        LaCellule.string = "Copie effectuée"
        msgBox (" dans la cellule A1 est inscrit : copie effectuée ",MB_ICONINFORMATION, "info système", MB_ICONINFORMATION
         
    Cliquez sur le bouton pour copier le code
    dans le presse papier
    Dim SelCourante, Info as object
    Dim ligne, colonne as integer
    Dim nomSelection as string
    
    SelCourante = ThisComponent.CurrentSelection
    Info= ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
       if SelCourante.supportsService("com.sun.star.sheet.SheetCell") then
            ' la sélection est une seule cellule
             with SelCourante.cellAddress
                     ligne = .row
                     colonne = .column
                    msgBox ( "les coordonnées de la cellule sélectionnée sont : ("+ cStr(colonne) + " , " + cStr(ligne) + ")", MB_ICONINFORMATION)
             end with
    
        Info.Address = SelCourante.cellAddress
        'Info ressemble à feuille1.E1
        nomSelection = split(Info.UserInterfaceRepresentation,".")(1)
        msgBox ( "la cellule sélectionnée est :" + nomSelection , MB_ICONINFORMATION)
    
        elseif SelCourante.supportsService("com.sun.star.sheet.SheetCellRange") then
           'la sélection est une plage de cellules
            with SelCourante.rangeAddress
                 msgBox ( "les coordonnées de la plage sélectionnée sont : ("+ .startColumn + " , " + .startRow + ") - ("  + .endColumn + " , " + .endRow + ")", MB_ICONINFORMATION)
           end with
    
           nomSelection = SelCourante.AbsoluteName
           'nomSelection ressemble à $feuille1.$E$1:$F$15
            nomSelection = Join(Split(nomSelection, "$"), "")
            nomSelection = split(nomSelection,".")(1)
            msgBox ( "la plage sélectionnée est : "+ nomSelection, MB_ICONINFORMATION)
    
        else 
           msgbox ("la sélection doit être soit une cellule, soit une plage de cellules", MB_ICONINFORMATION)
    
        end if
    

    Pour tester les portions de code des macro-commandes, figurant dans les onglets ci-dessus, il faut les coller dans l'EDI  (Environnement de développement Intégré) d'OpenOffice. Celui-ci est accessible par "Outils - Macros - Editer les Macros" d'un classeur dès lors que vous avez autorisé les macros dans les options d'Open Office (Options - Sécurité  et Options -.Avancé) cf image ci-contre).

    Pour exécuter une macro-commande  :

    1. A partir de l'EDI  : 

    Utiliser l'item "exécuter" de la barre de menus ou la touche de fonction F5 ou le bouton    de la barre d'outils.

    Le code doit être entre les mots clé sub et end sub ou function  et end function comme le montre les exemples ci-dessous.

    sub test1
    rem ici figure le code que j'ai copié
    end sub

    function test1()
    rem ici figure le code que j'ai copié
    end function

    1. - A partir d'une cellule de la feuille de calcul :
    Le code doit obligatoirement être encadré par function et end function.

    Taper le nom de cette fonction (avec les parenthèses, même si il n'y a pas de paramètres) comme formule dans une cellule quelconque de la feuille de calcul :  ex : = test1()

    L'Environnement de développement intégré de LibreOffice Calc

    EDI : Cliquer sur l'image pour l'agrandir

    _____________________

    _____________________