Generate Id with macro in LibreOffice

Question

I have a CSV with the following structure:

    ID290;ID11;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69402_accuse reception courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69402_accuse reception courrier.pdf;Item;T30_1300254_69402_accuse reception courrier.pdf
    ID293;ID11;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69417_accuse reception courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69417_accuse reception courrier.pdf;Item;T30_1300254_69417_accuse reception courrier.pdf
    ID11;ID10;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254;;RecordGrp;Dossier n° 1300254
    ID333;ID294;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_53417_accuse reception courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_53417_accuse reception courrier.pdf;Item;T30_1300826_53417_accuse reception courrier.pdf
    ID336;ID294;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59180_accuse mise a disposition courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59180_accuse mise a disposition courrier.pdf;Item;T30_1300826_59180_accuse mise a disposition courrier.pdf
    ID339;ID294;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59390_accuse reception courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59390_accuse reception courrier.pdf;Item;T30_1300826_59390_accuse reception courrier.pdf
    ID294;ID10;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826;;RecordGrp;Dossier n° 1300826
    ID400;ID340;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69841_accuse mise a disposition courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69841_accuse mise a disposition courrier.pdf;Item;T30_1300897_69841_accuse mise a disposition courrier.pdf
    ID403;ID340;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69858_accuse reception courrier.pdf;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69858_accuse reception courrier.pdf;Item;T30_1300897_69858_accuse reception courrier.pdf
    ID340;ID10;..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897;;RecordGrp;Dossier n° 1300897

The rows correspond to a tree structure where Id and ParentId contain the parent and child nodes. I'd like to generate an identifier in column H that takes the following constraints into account: wherever the rows contain “RecordGrp”, I create an id whose pattern is “2022-54-”. I then create the rows of the parent nodes incrementally.

Here's the macro I use:

    Dim oSheet As Object
    Dim oCursor As Object
    Dim lastRow As Long
    Dim currentId As String
    Dim counter As Long
    Dim parentMap As Object
    Dim row As Long

    ' Initialiser la feuille active et les variables
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
    lastRow = oCursor.RangeAddress.EndRow
    Set parentMap = CreateObject("Scripting.Dictionary")
    counter = 1
    currentId = "2022-54-"

    ' Parcourir les lignes
    For row = 1 To lastRow
        Dim title As String
        Dim descriptionLevel As String
        Dim parentId As String
        Dim id As String
        Dim newId As String
        
        title = oSheet.getCellByPosition(5, row).String  ' Colonne F : Content.Title
        descriptionLevel = oSheet.getCellByPosition(4, row).String  ' Colonne E : Content.DescriptionLevel
        id = oSheet.getCellByPosition(0, row).String  ' Colonne A : Id
        parentId = oSheet.getCellByPosition(1, row).String  ' Colonne B : ParentId

        If descriptionLevel = "RecordGrp" Then
            ' Nouvel identifiant pour les "RecordGrp"
            newId = currentId & counter
            parentMap.Add id, newId
            oSheet.getCellByPosition(7, row).String = newId  ' Colonne H pour l'ID
            counter = counter + 1
        ElseIf parentMap.exists(parentId) Then
            ' Utiliser l'identifiant du parent pour les enfants
            newId = currentId & counter
            oSheet.getCellByPosition(7, row).String = newId  ' Colonne H pour l'ID
            counter = counter + 1
        Else
            ' Si aucun parent trouvé, signaler une erreur
            oSheet.getCellByPosition(7, row).String = "Erreur : Parent introuvable"
        End If
    Next row
    
    MsgBox "Identifiants générés avec succès dans la colonne H !"
End Sub

The lines of child nodes do not generate an Id. I would like the following result:

ID290,ID11,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69402_accuse reception courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69402_accuse reception courrier.pdf,Item,T30_1300254_69402_accuse reception courrier.pdf,,2022-54-2
ID293,ID11,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69417_accuse reception courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254\T30_1300254_69417_accuse reception courrier.pdf,Item,T30_1300254_69417_accuse reception courrier.pdf,,2022-54-3
ID11,ID10,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300254,,RecordGrp,Dossier n° 1300254,,2022-54-1
ID333,ID294,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_53417_accuse reception courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_53417_accuse reception courrier.pdf,Item,T30_1300826_53417_accuse reception courrier.pdf,,2022-54-5
ID336,ID294,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59180_accuse mise a disposition courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59180_accuse mise a disposition courrier.pdf,Item,T30_1300826_59180_accuse mise a disposition courrier.pdf,,2022-54-6
ID339,ID294,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59390_accuse reception courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826\T30_1300826_59390_accuse reception courrier.pdf,Item,T30_1300826_59390_accuse reception courrier.pdf,,2022-54-7
ID294,ID10,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300826,,RecordGrp,Dossier n° 1300826,,2022-54-4
ID400,ID340,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69841_accuse mise a disposition courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69841_accuse mise a disposition courrier.pdf,Item,T30_1300897_69841_accuse mise a disposition courrier.pdf,,2022-54-9
ID403,ID340,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69858_accuse reception courrier.pdf,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897\T30_1300897_69858_accuse reception courrier.pdf,Item,T30_1300897_69858_accuse reception courrier.pdf,,2022-54-10
ID340,ID10,..\Phase2-fusionner_dossiers\Dossiers\TA30\Dossier n° 1300897,,RecordGrp,Dossier n° 1300897,,2022-54-8

JohnSUN · Accepted Answer · 2024-11-20 16:05:09Z

Since you are asking about tree traversal, it is very difficult to do without a recursive algorithm. One possible solution looks like this.

On the first pass through all the data to select the rows marked "RecordGrp", you check the data for validity. If the record ID is less than or equal to the parent ID, then this is a bad record, processing this row will most likely lead to a program loop.

On the second pass, you iterate over all the found root records and recursively find all its descendants, putting indexes.

You can leave unprocessed rows with empty indexes. Or you can write a warning about the absence of a parent ID for this record in another loop:

Option Explicit 

Dim counter As Long
Dim aData As Variant
Dim lastRow As Long
Dim lastColumn As Long
Const prefixId = "2022-54-"

Sub renumItems
Dim oSheet As Object
Dim oCursor As Object
Dim oRange As Object
Dim oService As Object
Dim aRoots As Variant
Dim i As Long 
Dim curID As Long, parID As Long 
' Initialiser la feuille active et les variables
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
    lastColumn = oCursor.RangeAddress.EndColumn+2
    lastRow = oCursor.RangeAddress.EndRow
    oRange = oSheet.getCellRangeByPosition(0, 0,  lastColumn, lastRow)
    aData = oRange.getDataArray()
    aRoots = Array()
    Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")    
    counter = -1

' Parcourir les lignes
    For i = 1 To lastRow
        curID = CLng(oService.callFunction("REGEX", Array(aData(i)(0), "\D","","g")))
        parID = CLng(oService.callFunction("REGEX", Array(aData(i)(1), "\D","","g")))
        If (curID < parID) Then 
            aData(i)(lastColumn) = "Erreur : Parent introuvable"
        ElseIf aData(i)(4) = "RecordGrp" Then
            counter = counter + 1
            ReDim Preserve  aRoots(counter)
            aRoots(counter) = Array(aData(i)(0),i)
        EndIf 
    Next i
' Liste tous les parents
    counter = 0
    For i = LBound(aRoots) To UBound(aRoots)
        counter = counter + 1
        aData(aRoots(i)(1))(lastColumn) = prefixId & counter
' Trouver tous les enfants pour cet identifiant
        renumChilds(aRoots(i)(0))
    Next i
    For i = 1 To lastRow
        If Trim(aData(i)(lastColumn)) = "" Then 
            aData(i)(lastColumn) = "Aucun parent trouvé pour cette entrée"
        EndIf 
    Next i
    oRange.setDataArray(aData)
    MsgBox "Identifiants générés avec succès dans la colonne H !"
End Sub

Sub renumChilds(parentID As String)
Dim i As Long 
    For i = 1 To lastRow
        If Trim(aData(i)(lastColumn)) = "" Then
            If aData(i)(1) = parentID Then
                counter = counter + 1
                aData(i)(lastColumn) = prefixId & counter
                renumChilds(aData(i)(0))
            EndIf 
        EndIf 
    Next i      
End Sub

Yes, I see that in the data samples you provided there are only two levels of nesting and in this case it would be possible to get by with two nested loops without recursion.

Collectives™ on Stack Overflow

Generate Id with macro in LibreOffice

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related