Em alguns momentos quando projetamos aplicações, verificamos a necessidade de gerar relatórios em planilhas do excel. Seja para os usuários da aplicação ou então, para a transferência de dados e integração entre sistemas.
A entrega de relatórios em planilhas gera um grande valor aos clientes. Através delas, os usuários poderão gerar filtros, fórmulas, inserir, alterar e excluir as informações presentes no relatório.
|
Neste post irei demonstrar como podemos gerar planilhas através de funções no Genexus. Procurarei utilizar algumas as funções que este tipo de variável nos permite, desde a utilização de Templates à inserção de conteúdos com a fonte desejada.
Além de gerar planilhas, o Genexus nos permite realizar a leitura destas, realizando desta forma, a função contrária à comentada neste tópico. Neste caso, um outro software geraria uma planilha e através da nossa aplicação poderíamos internalizar estes registros.
Vamos iniciar, o objetivo traçado neste tópico é disponibilizar ao usuário uma planilha que contenha a lista de clientes cadastrados em um sistema. Nesta lista estarão inclusos os campos: nome, data de nascimento, idade, sexo e o endereço completo de cada um. Para facilitar a visualização dos dados, iremos gerar esta planilha com base em um arquivo template previamente criado. Este arquivo já irá conter o layout desejado e fórmulas condicionais. Ao fim, a planilha irá ficar semelhante a esta abaixo:
Criando o template de utilização
O que é um template?
Templates são modelos, design, padrões pré-definidos porém sem conteúdo. Um template é utilizado para dar uma certa estrutura à sua planilha. A própria Microsoft dispõem de inúmeros templates para serem utilizados, você pode ver cada um deles clicando aqui.
A primeira etapa é criar um template ao qual a planilha irá se basear. Para isso, crie primeiramente dentro da pasta "web" da sua base de conhecimento, a pasta "Template". Sendo assim o caminho ficará: "...\web\Template\".
Agora, execute o Excel, crie o template que desejar, e salve-o com a extensão .XLT (Modelo do Excel 97-2003) dentro da pasta criada anteriormente. O meu template ficou da seguinte forma:
Criando o Efeito Zebrado - Formatação Condicional
A segunda etapa é a criação de formatações condicionais no nosso template. Para isso utilizaremos a seguinte lógica, as linhas pares terão o fundo cinza quando não forem vazias, já as linhas impares terão o fundo branco quando não forem vazias.
Portanto, abra o template criado, clique em "Formatação Condicional" e em "Nova Regra". Agora clique em "Usar uma fórmula para determinar quais células devem ser formatadas".
Para as linhas pares:
Fórmula: =SE(MOD(LIN();2)=0;$C6<>"")
Visualização: Preenchimento cinza, borda inferior;
Aplica-se a =$B$6:$L$1000 (linhas e colunas onde estarão os dados)
Para as linhas impares:
Fórmula: =SE(MOD(LIN();2)=1;$C6<>"")
Visualização: Preenchimento branco, borda inferior;
Aplica-se a =$B$6:$L$1000 (linhas e colunas onde estarão os dados)
Ao fim, eis o resultado:
Gerando Planilhas através do Genexus
Por fim, iremos criar uma procedure e utilizar as funções apropriadas para a criação da nossa planilha. Nesta procedure não receberei parâmetros, irei facilitar para a demonstração deste exemplo. Então em "Source" defini da seguinte forma:
Considerações:
- não utilizei a função "Clear()" pois como o template contem textos, eles iriam ser excluídos. Logo, para não abrir uma planilha já criada defina o nome com base na data, hora, usuário de geração.
- o endereço de geração da planilha é o endereço onde a aplicação esta hospedada. Para disponibilizar ao cliente por meio de uma interface, você deverá utilizar a função "Link(endereço da planilha)".
//=====Define que as Colunas não irão se auto ajustar=====
&ExcelDocument.AutoFit = 0
//=====Define a utilização do template=====
&ExcelDocument.Template = '..\web\Template\RelatorioClientes.xlt'
//=====Define o endereço e nome do arquivo a ser criado (aberto)=====
&Source = 'C:\Temp\ListaClientes_'+&Today.ToString().Trim()+'.xls'
&Source = StrReplace(&Source,'/','_')
//=====Abrir Arquivo=====
&ExcelDocument.Open(&Source)
//=====Definir a primeira linha de trabalho=====
&Linha = 6
//=====Percorre Lista de Clientes Cadastrados=====
for each ClienteCod
//=====Código do Cliente; 2ª Coluna; Negrito=====
&ExcelDocument.Cells(&Linha,2).Bold = true
&ExcelDocument.Cells(&Linha,2).Number = ClienteCod
//=====Nome do Cliente; 3ª Coluna=====
&ExcelDocument.Cells(&Linha,3).Text = ClienteNome
//=====Data de Nascimento; 4ª Coluna=====
&ExcelDocument.Cells(&Linha,4).Date = ClienteNascimento
//=====Idade; 5ª Coluna; Italico=====
&ExcelDocument.Cells(&Linha,5).Italic = true
&ExcelDocument.Cells(&Linha,5).Number = Age(ClienteNascimento)
//====Sexo; 6ª Coluna; Negrito; Masculino = Azul; Feminino = Vermelho====
&ExcelDocument.Cells(&Linha,6).Bold = true
if ClienteSexo = Sexo.Masculino
&ExcelDocument.Cells(&Linha,6).Color = 5
else
&ExcelDocument.Cells(&Linha,6).Color = 3
endif
&ExcelDocument.Cells(&Linha,6).Text = ClienteSexo.EnumerationDescription()
//=====Endereço; 7ª Coluna=====
&ExcelDocument.Cells(&Linha,7).Text = ClienteEndereco
//=====Número; 8ª Coluna=====
&ExcelDocument.Cells(&Linha,8).Text = ClienteNumero
//=====Complemento; 9ª Coluna=====
&ExcelDocument.Cells(&Linha,9).Text = ClienteComplemento
//=====CEP; 10ª Coluna=====
&ExcelDocument.Cells(&Linha,10).Text = ClienteCEP
//=====Cidade; 11ª Coluna=====
&ExcelDocument.Cells(&Linha,11).Text = ClienteCidade
//=====Estado; 12ª Coluna=====
&ExcelDocument.Cells(&Linha,12).Text = ClienteEstado
//=====Passa para a próxima linha=====
&Linha += 1
endfor
//=====Renomeia Aba (máximo de 31 caracteres)=====
&NomeAba = 'Emissao em '+&Today.ToString().Trim()
&NomeAba = StrReplace(&NomeAba,'/','-')
&ExcelDocument.RenameSheet(&NomeAba)
//=====Salva alterações realizadas=====
&ExcelDocument.Save()
//=====Fecha Arquivo=====
&ExcelDocument.Close()
//=====Mostra Arquivo Gerado=====
&ExcelDocument.Show()
&ExcelDocument.AutoFit = 0
//=====Define a utilização do template=====
&ExcelDocument.Template = '..\web\Template\RelatorioClientes.xlt'
//=====Define o endereço e nome do arquivo a ser criado (aberto)=====
&Source = 'C:\Temp\ListaClientes_'+&Today.ToString().Trim()+'.xls'
&Source = StrReplace(&Source,'/','_')
//=====Abrir Arquivo=====
&ExcelDocument.Open(&Source)
//=====Definir a primeira linha de trabalho=====
&Linha = 6
//=====Percorre Lista de Clientes Cadastrados=====
for each ClienteCod
//=====Código do Cliente; 2ª Coluna; Negrito=====
&ExcelDocument.Cells(&Linha,2).Bold = true
&ExcelDocument.Cells(&Linha,2).Number = ClienteCod
//=====Nome do Cliente; 3ª Coluna=====
&ExcelDocument.Cells(&Linha,3).Text = ClienteNome
//=====Data de Nascimento; 4ª Coluna=====
&ExcelDocument.Cells(&Linha,4).Date = ClienteNascimento
//=====Idade; 5ª Coluna; Italico=====
&ExcelDocument.Cells(&Linha,5).Italic = true
&ExcelDocument.Cells(&Linha,5).Number = Age(ClienteNascimento)
//====Sexo; 6ª Coluna; Negrito; Masculino = Azul; Feminino = Vermelho====
&ExcelDocument.Cells(&Linha,6).Bold = true
if ClienteSexo = Sexo.Masculino
&ExcelDocument.Cells(&Linha,6).Color = 5
else
&ExcelDocument.Cells(&Linha,6).Color = 3
endif
&ExcelDocument.Cells(&Linha,6).Text = ClienteSexo.EnumerationDescription()
//=====Endereço; 7ª Coluna=====
&ExcelDocument.Cells(&Linha,7).Text = ClienteEndereco
//=====Número; 8ª Coluna=====
&ExcelDocument.Cells(&Linha,8).Text = ClienteNumero
//=====Complemento; 9ª Coluna=====
&ExcelDocument.Cells(&Linha,9).Text = ClienteComplemento
//=====CEP; 10ª Coluna=====
&ExcelDocument.Cells(&Linha,10).Text = ClienteCEP
//=====Cidade; 11ª Coluna=====
&ExcelDocument.Cells(&Linha,11).Text = ClienteCidade
//=====Estado; 12ª Coluna=====
&ExcelDocument.Cells(&Linha,12).Text = ClienteEstado
//=====Passa para a próxima linha=====
&Linha += 1
endfor
//=====Renomeia Aba (máximo de 31 caracteres)=====
&NomeAba = 'Emissao em '+&Today.ToString().Trim()
&NomeAba = StrReplace(&NomeAba,'/','-')
&ExcelDocument.RenameSheet(&NomeAba)
//=====Salva alterações realizadas=====
&ExcelDocument.Save()
//=====Fecha Arquivo=====
&ExcelDocument.Close()
//=====Mostra Arquivo Gerado=====
&ExcelDocument.Show()
Parabéns pelo post.
ResponderExcluirDúvida: é preciso ter o office instalado no servidor web?
Fiz um teste no servidor web sem office e o sistema fica "pensando" e não faz nada.
Não é necessário, você precisa ter as DLLs do Jakarta dentro da pasta bin.
ExcluirLink das DLLs: http://sourceforge.net/projects/jbyjsharp/files/Jakarta%20POI/
Abraços
Boa tarde.
ResponderExcluirE para Gravar em 2 planilhas dentro do mesmo arquivo?
Att,
Eder
Olá,
ResponderExcluirtentei definir uma coluna como Quebra automática de linha. Porém isto está sendo aplicado somente na primeira linha em que é inserido o texto, nas linhas abaixo não está sendo aplicado.
Existe alguma forma de definir quebra automática pelo Genexus?
Grato.
Leonardo, defina a quebra de linha para a coluna, e não somente para uma célula, ou então, defina através de uma formatação condicional. Isto no seu template. Abraços!
ExcluirLeonardo, conseguiu resolver o problema, estou enfrentando a mesma situação, mesmo configurando a coluna algumas palavras não aparecem. Uso o Genexus X.
ExcluirUtilizo o Genexus X Ev. 1 U8, com gerador Java, desse modo se adiciono o template ao código a formatação se perde, ou seja, se vai as fontes, cores e etc. Ficando apenas as letras (tanto de cabeçalho quanto outras identificações da planilha). Penso que o POI.jar que está na pasta WEB-INF\bin possui algum problema em relação a templates, ou algo nesse sentido. Você poderia me dizer como resolver templates com o Gx X Ev. 1 U8? Desde já agradeço.
ResponderExcluirVc esta dando ExcelDocument.Clear e deve estar limpando o template depois de abrir o arquivo
ExcluirComecei a trabalhar com Genexus este ano. Em meio a uma comunidade relativamente pequena, blogs como este ajudam bastante. Pena ver que faz qse 2 anos sem uma atualização...
ResponderExcluirGostaria de Saber o cod da tabela de cores, ex azul = 5, alguma dica?
ResponderExcluirOi Thiago, você pode realizar um "for &i = 1 to 255", e dentro deste for um
Excluir&ExcelDocument.Cells(&i,1).Text = "COR" e
&ExcelDocument.Cells(&i,1).Color = &i
Abraços, Eduardo
Beleza Muito Obrigado
ExcluirEste comentário foi removido pelo autor.
ResponderExcluirBoa tarde Eduardo.
ResponderExcluirGostaria de saber se é possível antes de enviar os dados para o Excel formatar uma célula como personalizado = hh:mm
Muito obrigado.
Boa tarde Eduardo.
ResponderExcluirGostaria de saber se é possível antes de enviar os dados para o Excel formatar uma célula como personalizado = hh:mm
Muito obrigado.