Agrupar contas TreeControl

WBastos · April 23, 2022, 3:02pm

Olá pessoal,
Estou tentando implementar um projeto com uma TreeControl no Basic com base em uma vídeo aula do VBA, no entanto me esbarrei no agrupamento das contas, conforme imagem abaixo:
TreeView

Segue código e planilha abaixo:

REM  *****  BASIC  *****
'__________________________________________________
Option Explicit
'__________________________________________________
Const FCT =  "com.sun.star.sheet.FunctionAccess"
Const PCT = "com.sun.star.awt.tree.MutableTreeDataModel"
'__________________________________________________
Dim fs 			As Variant
Dim wb 			As Object
Dim ws 			As Object
Dim Coluna 		As String 
Dim uLinha 		As Long
Dim BaseDados	As Object  
Dim wPlan 		As Object
Dim Arr()
Dim nLin		As Integer 

Dim Dlg 		As Object
Dim frmPContas	As Object
Dim oPContas	As Object 
Dim mPContas	As Object
Dim oMTD
Dim NoRaiz		As Object
Dim NoPai		As Object
Dim NoFilho		As Object
Dim NoNeto		As Object
Dim NoBisneto	As Object 
'__________________________________________________
Private Sub Dados
	wb = ThisComponent
	Set ws = wb.Sheets().getByName("Plano de Contas")
	Coluna = "A:A"
	
	uLinha = Linha (ws , Coluna)	
	BaseDados = ws.getCellRangeByName("A1:D" & uLinha )
	Arr = BaseDados.getDataArray()	 
End Sub
'__________________________________________________
Private Sub FrmInitialize
	DialogLibraries.loadLibrary("Standard")
	Dlg = createUnoDialog(DialogLibraries.Standard.getByName("dlgPContas"))
	
	frmPContas = dlg.Model
	oPContas = Dlg.getControl("PContas")
	mPContas = oPContas.Model
	
	Call Dados
	Call PlanoContas
	
	Dlg.Execute
	Dlg.Dispose	
End Sub 
'__________________________________________________
Sub PlanoContas
	oMTD = createUnoService( PCT )
	NoRaiz 	= oMTD.createNode( "Plano de Contas", True )

		For nLin = lBound( Arr )+1 To uBound( Arr )
			oMTD.setRoot ( NoRaiz )
				NoPai = oMTD.createNode (ws.getCellByPosition ( 0 , nLin ).String , True )
				NoRaiz.appendChild( NoPai )
					NoFilho = oMTD.createNode(ws.getCellByPosition( 1 , nLin).String , True)
					NoPai.appendChild( NoFilho )
						NoNeto = oMTD.createNode(ws.getCellByPosition( 2 , nLin).String , True)
						NoFilho.appendChild( NoNeto )
							NoBisneto = oMTD.createNode(ws.getCellByPosition( 3 , nLin).String , False)
							NoNeto.appendChild( NoBisneto )
			mPContas.DataModel = oMTD 
			oPContas.expandNode ( NoRaiz ) = true
			oPContas.expandNode ( NoPai ) = true
			oPContas.expandNode ( NoFilho ) = true 
			oPContas.expandNode ( NoNeto ) = true
					  		
		Next nLin		
End Sub 
'__________________________________________________
Private Function Linha ( ws , Range )
	fs = createUnoService( FCT )
	Linha = fs.callFunction( "COUNTA" , (Array(ws.getCellRangeBYname( Coluna ) )))
End Function

A pergunta é: Tem como fazer o agrupamento das contas de acordo com a imagem?
plano-de-contas-array.ods (93.2 KB)

WBastos · April 23, 2022, 8:27pm

Embora não seja o que eu pretendia, mas funciona.
Vou estudar as propriedades e métodos pra ver se consigo encontrar uma forma melhor de resolver o desafio.

Segue código:

REM  *****  BASIC  *****
'Seguindo referência do vídeo disponível no Youtube
'https://www.youtube.com/watch?v=KNKfJDkhJGY
'Ponto de partida: 
'https://wiki.openoffice.org/wiki/Going_further_with_Dialog_and_Component
'__________________________________________________
Option Explicit
'__________________________________________________
Const FCT =  "com.sun.star.sheet.FunctionAccess"
Const PCT = "com.sun.star.awt.tree.MutableTreeDataModel"
'__________________________________________________
Dim fs 			As Variant
Dim wb 			As Object
Dim ws 			As Object
Dim Coluna 		As String 
Dim uLinha 		As Long
Dim BaseDados	As Object  
Dim wPlan 		As Object
Dim Arr()
Dim nLin		As Integer 

Dim Dlg 		As Object
Dim frmPContas	As Object
Dim oPContas	As Object 
Dim mPContas	As Object
Dim oMTD
Dim NoRaiz		As Object
Dim NoPai		As Object
Dim NoFilho		As Object
Dim NoNeto		As Object
Dim NoBisneto	As Object 
'__________________________________________________
Private Sub Dados
	wb = ThisComponent
	Set ws = wb.Sheets().getByName("BaseTeste")
	Coluna = "D:D"
	
	uLinha = Linha (ws , Coluna)	
	BaseDados = ws.getCellRangeByName("A1:D" & uLinha )
	Arr = BaseDados.getDataArray()	 
End Sub
'__________________________________________________
Private Sub FrmInitialize
	DialogLibraries.loadLibrary("Standard")
	Dlg = createUnoDialog(DialogLibraries.Standard.getByName("dlgPContas"))
	
	frmPContas = dlg.Model
	oPContas = Dlg.getControl("PContas")
	mPContas = oPContas.Model
	 
		oPContas.DesignMode = false
		oPContas.StyleSettings.FieldTextColor = RGB ( 136 , 19 , 61 ) 
 
	Call Dados
	Call PlanoContas
	
	Dlg.Execute
	Dlg.Dispose	
End Sub 
'__________________________________________________
Sub PlanoContas
	oMTD = createUnoService( PCT )
	NoRaiz 	= oMTD.createNode( "Plano de Contas", True )

		For nLin = lBound( Arr )+1 To uBound( Arr )
			oMTD.setRoot ( NoRaiz )
			
				If ws.getCellByPosition ( 0 , nLin ).String <> "" Then
					NoPai = oMTD.createNode(ws.getCellByPosition ( 0 , nLin ).String , True)
					NoRaiz.appendChild( NoPai )
				End If 		
					If ws.getCellByPosition( 1 , nLin ).String <> "" Then 
							NoFilho = oMTD.createNode(ws.getCellByPosition ( 1 , nLin ).String , True)
							NoPai.appendChild( NoFilho ) 
					End If
						If ws.getCellByPosition( 2 , nLin ).String <> "" Then 
								NoNeto = oMTD.createNode(ws.getCellByPosition ( 2 , nLin ).String , True)
								NoFilho.appendChild( NoNeto ) 
						End If

							NoBisneto = oMTD.createNode(ws.getCellByPosition( 3 , nLin).String , False)
							NoNeto.appendChild( NoBisneto )
			
			mPContas.DataModel = oMTD 
			oPContas.expandNode ( NoRaiz ) = True
			oPContas.expandNode ( NoPai ) = True
			oPContas.expandNode ( NoFilho ) = True 
			oPContas.expandNode ( NoNeto ) = False
			oPContas.StyleSettings.LabelTextColor = RGB ( 255 , 0 , 0 )
		Next nLin
End Sub 
'__________________________________________________
Private Function Linha ( ws , Coluna )
	fs = createUnoService( FCT )
	Linha = fs.callFunction( "COUNTA" , (Array(ws.getCellRangeBYname( Coluna ) )))
End Function
'__________________________________________________

plano-de-contas-array-rev01.ods (100.0 KB)

elmau · April 23, 2022, 9:18pm

Se modificar os seus dados de origem para ter esta estrutura:

	data = Array ( _
		Array(1, 0, "1 - Ativo"), _
		Array(2, 1, "1.1 - Circulante"), _
		Array(3, 2, "1.1.1 -  Disponível"), _
		Array(4, 3, "1.1.1.001 - Caixa"), _
		Array(5, 2, "1.1.2 -  Realizações"), _
		Array(2, 0, "2 - Passivo"), _
	)

Então, tem:

parents = com.sun.star.container.EnumerableMap.create("long", "any")
	For Each row In data
		If parents.containsKey(row(1)) Then
			parent = parents.get(row(1))
		Else
			parent = NoRaiz
		End If
		child = oMTD.createNode(row(2), False)
		parent.appendChild(child)
		parents.put(row(0), child)
	Next
	
	mPContas.DataModel = oMTD

WBastos · April 23, 2022, 11:03pm

Obrigado pelo retorno, @elmau. Vou testar na segunda-feira.

Vou compartilhar o arquivo já com inserção de ícones nos respectivos nodes, para fins de futuras consultas, mas a medida que o projeto for evoluindo, vou compartilhando aqui.

plano-de-contas-array-rev01.ods (103.5 KB)