domingo, 5 de maio de 2013

Genexus ExcelDocument - Gerando Planilhas

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:

Genexus Excel Document

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:

Genexus Excel Document

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:

Genexus Excel Document

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()

15 comentários:

  1. Parabéns pelo post.

    Dú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.

    ResponderExcluir
    Respostas
    1. Não é necessário, você precisa ter as DLLs do Jakarta dentro da pasta bin.
      Link das DLLs: http://sourceforge.net/projects/jbyjsharp/files/Jakarta%20POI/

      Abraços

      Excluir
  2. Boa tarde.
    E para Gravar em 2 planilhas dentro do mesmo arquivo?

    Att,
    Eder

    ResponderExcluir
  3. Olá,
    tentei 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.

    ResponderExcluir
    Respostas
    1. 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!

      Excluir
    2. Leonardo, conseguiu resolver o problema, estou enfrentando a mesma situação, mesmo configurando a coluna algumas palavras não aparecem. Uso o Genexus X.

      Excluir
  4. Utilizo 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.

    ResponderExcluir
    Respostas
    1. Vc esta dando ExcelDocument.Clear e deve estar limpando o template depois de abrir o arquivo

      Excluir
  5. Comecei 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...

    ResponderExcluir
  6. Gostaria de Saber o cod da tabela de cores, ex azul = 5, alguma dica?

    ResponderExcluir
    Respostas
    1. Oi Thiago, você pode realizar um "for &i = 1 to 255", e dentro deste for um
      &ExcelDocument.Cells(&i,1).Text = "COR" e
      &ExcelDocument.Cells(&i,1).Color = &i
      Abraços, Eduardo

      Excluir
  7. Este comentário foi removido pelo autor.

    ResponderExcluir
  8. Boa tarde Eduardo.
    Gostaria de saber se é possível antes de enviar os dados para o Excel formatar uma célula como personalizado = hh:mm
    Muito obrigado.

    ResponderExcluir
  9. Boa tarde Eduardo.
    Gostaria de saber se é possível antes de enviar os dados para o Excel formatar uma célula como personalizado = hh:mm
    Muito obrigado.

    ResponderExcluir