Macro LibreOffice : CellRange e setDataArray

Eu tinha uma planilha para completar, com cerca de mil linhas. 180 dessas linhas estavam com 3 das 4 células em branco, à espera da complementação. Havia uma tabela à parte, com os dados que faltavam na tabela maior.

Na foto abaixo aparecem, nas 4 primeiras colunas, o início da tabela a ser completada. Notem os espaços em branco, que ressaltei com molduras em vermelho. Nesses primeiros 6 registros eu devia encaixar, por macro, os dados da tabela à direita, menos, é claro, os nomes (coluna G), que já constavam na tabela maior.

Elaborei uma macro (dentro do Calc LibreOffice) que localizasse, na tabela maior, o primeiro nome da tabela menor. Em seguida, devia a macro copiar o conteúdo das três células à direita do nome e colá-las à direita do mesmo nome constante da tabela incompleta. Era o caso de trabalhar com cópia e colagem, não de células individuais, mas de uma faixa de células (CellRange). Eis a macro:
Sub Main

Dim oDoc As Object
Dim oSheet As Object
Dim Cell As Object
Dim iCol, iCol2 As Integer
Dim iLin, iLin2 As Integer
Dim oRange1(6 to 8 , 2), oRange2(1 to 3 , 2)
Dim nome1, nome2 As String

oDoc = ThisComponent
oSheet = oDoc.Sheets(0)

iCol = 6
iLin = 2
iCol2 = 1
iLin2 = 2
nome1 = “A”
nome2 = “B”

While iLin < 8 and nome2 <> “” and nome1 <> “”

ReDim oRange1(1 to 3 , iLin), oRange2(6 to 8 , iLin)

oCell = oSheet.getCellByPosition(iCol,iLin)
nome1 = oCell.String
oCell = oSheet.getCellByPosition(iCol2, iLin2)
nome2 = oCell.String

If nome1 = nome2 Then

oRange1 = oSheet.getCellRangeByPosition(7,iLin,9,iLin)
oRange2 = oSheet.getCellRangeByPosition(2,iLin2,4,iLin2)
oRange2.setDataArray(oRange1.getDataArray())

iLin = iLin + 1

End If

iLin2 = iLin2 + 1

Wend

End Sub

Esta versão da macro está adaptada ao tamanho reduzido aqui exposto, isto é, só trabalha até transpor os dados do sexto nome da tabela menor.
Importante notar que na declaração das variáveis oRange1 e oRange2, é preciso especificar o conjunto de células de cada uma. No caso, ambas têm 3 colunas e 1 linha. Dim oRange1(1 to 3, 2) e Dim oRange2(6 to 8, 2) indicam as primeiras posições das duas faixas. Não esquecer que a contagem das linhas e colunas começa com zero (0) e não com 1.

ReDim é necessário para que os parâmetros (célula inicial e célula final das faixas) possam mudar de acordo com as exigências do programa. Assim, quando o programa chegar ao segundo nome da tabela menor (ADILSON), teremos oRange1(6 to 8, 3), mudança da linha.
Rodada a macro, a página ficará assim:

Estive consultando o Basic Development Guide, e lá constatei que existe uma outra maneira de obter o resultado acima, só que apagando os dados copiados para a tabela inicialmente incompleta. Adaptei a macro acima, ficando ela assim:

Sub Main
Dim Doc As Object
Dim Cell, Sheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim CellAddress As New com.sun.star.table.CellAddress
Dim iLin, iLin2, contador As Integer
Dim nome1, nome2 As String

Doc = StarDesktop.CurrentComponent
Sheet = Doc.Sheets(0)
iLin = 2
iLin2 = 2
contador = 0
nome1 = “A”
nome2 = “B”

While contador < 27

Cell = Sheet.getCellByPosition(6,iLin)
nome1 = Cell.String
Cell = Sheet.getCellByPosition(1,iLin2)
nome2 = Cell.String
msgbox nome1 & nome2

If nome1 = nome2 Then

CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 7
CellRangeAddress.StartRow = iLin
CellRangeAddress.EndColumn = 9
CellRangeAddress.EndRow = iLin

CellAddress.Sheet = 0
CellAddress.Column = 2
CellAddress.Row = iLin2

Sheet.moveRange(CellAddress, CellRangeAddress)

iLin = iLin + 1
iLin2 = iLin2 + 1
Else
iLin2 = iLin2 + 1
End If
contador = contador + 1
Wend

End Sub

Com essa macro, aquela página inicial ficará assim:

Macro LibreOffice Calc : Como Importar o Texto de uma Página WEB

Estive, há alguns anos, trabalhando com uma macro para fazer esse trabalho. Agora, depois de muito penar, e muitas consultas na WEB, consegui melhorar um pouco a tal macro. Eis a versão atual:

Sub ImportaPaginaWeb

dim oDocument As Object
Dim sUrl As String, sFilter As String
Dim sOptions As String
Dim oSheets As Object, oSheet As Object
dim oCell as Object
dim paginaweb as Long

oDocument = ThisComponent
oSheets = oDocument.sheets
oSheet = oSheets(1)
paginaweb = 200000

sUrl = “http://www.capitalnews.com.br/conteudo.php?cid=&#8221; + paginaweb

REM  apague todos os caracteres que estão entre o sinal de igual e o sinal de mais, substituindo-os por “fecha aspas”; o acréscimo foi feito automaticamente pelo programa wordpress, para completar o link. Mas deixe o sinal de mais e a palavra “paginaweb” (sem aspas, claro).

sFilter = “Text – txt – csv (StarCalc)”
sOptions = “09,34,1,1,1/1/1/1/1/1/1/1”

REM  ver: https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options ; 09 é o código ASCII decimal para separação por tabulação horizontal.

oSheet.link(sUrl, “”, sFilter, sOptions, com.sun.star.sheet.SheetLinkMode.VALUE)

oSheet = oSheets(1)

msgbox “término da sub-rotina”

End Sub

Neste caso, a página web é transcrita, em texto (com as tags html), na segunda folha da planilha (folha 1, já que a primeira é folha zero).

A partir dessa base, incluí outras sub-rotinas para procurar por palavras-chaves constantes em todas as páginas do saite consultado. Essas palavras-chaves, ou tags, permitem que a gente chegue à informação (ou parte dela) que nos interessa. Não transcrevo as sub-rotinas porque não oferecem muita dificuldade.

Se o leitor quiser testar, abra uma planilha nova do Calc (em branco), e acrescente (no sinal +) mais duas folhas. Em seguida vá a ferramentas > macros > organizar macros > libreoffice Basic, e crie uma macro, colando o texto acima. Depois é só executar a macro, e o texto da página web aparecerá nas linhas e colunas da segunda folha da planilha.

Acessar Células da Planilha, nelas Fazendo Anotações

MACROS PARA PLANILHA CALC (LIBREOFFICE).

Macros, para quem não sabe, é um recurso de programação oferecido pelas planilhas eletrônicas (Excel, da Microsoft, ou Calc, da LibreOffice, por exemplo).

Através delas o usuário transfere para o computador tarefas mais pesadas, ou mais extensas, ou tediosas, ou que exijam alto grau de precisão.

Não vale a pena criar uma Macro para que a planilha realize automaticamente tarefas simples, com poucos itens, e que o usuário pode fazer facil e rapidamente através do teclado. Porque criar uma macro exige, também, boa soma de trabalho, às vezes difícil, embora sempre instigante.

Demonstraremos neste blog, numa sequência de artigos, como criar macros utilizáveis na planilha Calc, da LibreOffice (software livre).

Vamos começar com um exemplo.

Estas são as primeiras 30 linhas de um arquivo que tem exatas 38.749 linhas, e que relaciona, com dados resumidos, o desempenho das escolas públicas brasileiras em relação ao IDEB, índice do Governo Federal (edições bianuais 2007 e 2009).

O nosso primeiro desafio é colocar, na coluna B (amarela, de título ORDEM) o número de ordem de cada uma das escolas listadas em ordem decrescente de IDEB 2009. Isto é, as primeiras escolas da lista são as que obtiveram os melhores resultados (as melhores médias das notas de seus alunos).

Se a lista tivesse apenas 30 linhas, seria fácil fazer isto pelo teclado, apenas digitando 1 e 2 para duas escolas de Santa Fé do Sul, 3 para Cajuru, 4 para Barrinha, e assim por diante. Mas temos 38.749 escolas para receberem seus números de ordem. É o caso de apelar para uma Macro.

Agora é com você. Clique aqui para baixar o arquivo exemplo1.xls . Carregue-o em seguida na planilha Calc. No Menu da planilha, procure e clique em Tools (Ferramentas), depois Macros, depois Organize Macros e depois LibreOffice Basic.

Vai abrir uma Caixa de Diálogo, e você vai clicar em My Macros, em Standard e em Module 1. Vai abrir a seguinte caixa:

Bom, aí está a Sub (abreviação de Subrotina) denominada Main (Principal), ainda vazia, esperando que você digite as instruções. E você vai digitar, abaixo de “Sub Main” e antes de “End Sub”, as seguintes linhas (obedecendo o formato e as separações mostradas):

Dim oDoc As Object
Dim oSheet As Object
Dim Cell As Object
Dim iCol As Long
Dim iLin As Long
Dim iLin3 As Long
Dim iOrdem As Long

iCol = 1
iLin = 5
iLin3 = 35
iOrdem = 0

oDoc = ThisComponent
oSheet = oDoc.Sheets(0)

While iLin < iLin3

iLin = iLin + 1
iOrdem = iOrdem + 1
oCell = oSheet.getCellByPosition(iCol, iLin)
oCell.Value = iOrdem

Wend

Explicações:

Dim cria objetos e variáveis. Objetos, para que o programa saiba onde atuar. No nosso exemplo, na planilha que está em uso (ThisComponent), ou seja “exemplo1.ods”, na sua primeira folha (ou sheets(0), lembrando que a contagem, no caso, começa em zero, e não em 1; a primeira folha é a folha zero). Variáveis, para armazenamento provisório de valores, fórmulas ou textos.

Os termos While e Wend  delimitam ações repetitivas sequenciais (loop). Isto é, o programa vai obedecendo as diretrizes dentro de While/Wend, linha de diretriz por linha de diretriz; chegando à última diretriz, volta à primeira e começa tudo de novo.

No presente caso, temos as anotações While iLin < iLin3 e Wend.

iLin < iLin3 estabelece um limite para a ação do programa: ele atuará até que iLin, ou seja, o número da linha da planilha atinja o valor igual a iLin3, que é o valor da última linha com dados (no arquivo exemplo1.xls, linha 35). Em outras palavras, o programa atuará começando por fazer anotação na célula da sétima linha da planilha (que o programa conhece como linha 6) e terminando com anotação na célula da trigésima sexta linha (para o programa, linha 35).

Wend indica o fim das instruções.

Nunca esqueça de colocar limite para a ação de While/Wend. O termo While deve sempre ter uma instrução de limite colada a ele (no presente exemplo, iLin < iLin3). Se não colocar limite, você não conseguirá fazer o programa parar, e acabará acarretando problemas.

Exemplo:

O programa, ao entrar no loop, acrescenta 1 a iLin (cujo valor inicial é 5), obtendo 6, número da linha da anotação inicial, e acrescenta 1 a iOrdem (cujo número inicial é 0), obtendo o número de ordem 1. A seguir, pela instrução oCell = oSheet.getCellByPosition(iCol, iLin)   acessa a célula da linha 6 (e coluna 2) da planilha, nela anotando (instrução oCell.value)   o valor armazenado em iOrdem, que é 1.

Em seguida volta à primeira diretriz (iLin = iLin + 1), acrescenta 1 unidade a iLin (que passa a 7),  vai à segunda diretriz (iOrdem = iOrdem + 1), muda para 2 o número que será anotado na célula da linha seguinte – 7 – da planilha).

E assim por diante.

Mas continuemos com a construção da macro Main. Colando as instruções acima entre Sub Main e End Sub, a caixa fica assim (vista parcial):

As cores diferenciais são fornecidas pelo programa. Salve as alterações feitas na Sub Main (clique no botão Save (ou Salvar) na parte de cima e saia da caixa clicando no X.

Em seguida, clique, na planilha onde estão os dados, em Tools, depois Macros, depois Run Macro. Abre uma caixa de diálogo e nela você clica na flechinha ao lado de My Macros, depois na flechinha ao lado de Standard, depois no nome Module 1. Aparece, à direita, a Sub Main, já selecionada. Então é só clicar em Run. Num segundo (pois o arquivo de exemplo é pequeno) estarão anotados os números de ordem das 30 escolas. O arquivo ficará então assim:

Na próxima postagem desta série sofisticaremos um pouco o programa e o resultado a obter.

Ah, e antes de sair da planilha, salve-a como planilha do Calc, de sufixo .ods. A partir de agora você vai carregar no calc sempre essa nova planilha, e não o de terminação xls.