Gerando planilhas em Excel com PHP

Em alguns momentos podemos nos deparar com a necessidade de exportar os dados em um formato diferenciado. Normalmente, para geração de relatórios utilizamos alguma biblioteca de PDF como mPDF ou  fPDF para padronizar a impressão e formatação no geral. Mas podemos também utilizar relatórios em Excel. Permitindo ao usuário final interagir com os dados, da mesma maneira que faria com uma planilha que o mesmo tivesse criado. Isso é possível gerando planilhas com a classe PHPExcel.

Uma das classes mais utilizadas para criar arquivos Excel (.xls e .xlsx)  com o PHP é PHPExcel. Ela conta com diversas funcionalidades bem variadas, permite desde a criação de arquivos com uma única planilha simples até arquivos com mais de uma planilha, com fórmulas pré-definidas e formatação de campos e colunas. Ou seja, dá conta do recado.

A classe tem cerca de 110MB, embora seja grande não é de difícil a utilização. Depois de baixar os arquivos da classe e extrair para seu projeto, basta incluir o arquivo PHPExcel.php e instanciar a classe. Vamos criar uma página que gere um .xls diretamente para download do usuário:

<?php

// Incluimos a classe PHPExcel
include  'phpexcel/Classes/PHPExcel.php';

// Instanciamos a classe
$objPHPExcel = new PHPExcel();

// Definimos o estilo da fonte
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

// Criamos as colunas
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Listagem de Credenciamento' )
            ->setCellValue('B1', "Nome " )
            ->setCellValue("C1", "Sobrenome" )
            ->setCellValue("D1", "E-mail" );

// Podemos configurar diferentes larguras paras as colunas como padrão
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(90);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);

// Também podemos escolher a posição exata aonde o dado será inserido (coluna, linha, dado);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, "Fulano");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, " da Silva");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 2, "fulano@exemplo.com.br");

// Exemplo inserindo uma segunda linha, note a diferença no segundo parâmetro
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, "Beltrano");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, " da Silva Sauro");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 3, "beltrando@exemplo.com.br");

// Podemos renomear o nome das planilha atual, lembrando que um único arquivo pode ter várias planilhas
$objPHPExcel->getActiveSheet()->setTitle('Credenciamento para o Evento');

// Cabeçalho do arquivo para ele baixar
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="arquivo_de_exemplo01.xls"');
header('Cache-Control: max-age=0');
// Se for o IE9, isso talvez seja necessário
header('Cache-Control: max-age=1');

// Acessamos o 'Writer' para poder salvar o arquivo
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

// Salva diretamente no output, poderíamos mudar arqui para um nome de arquivo em um diretório ,caso não quisessemos jogar na tela
$objWriter->save('php://output'); 

exit;

?>

Ao acessar a página o navegador irá forçar o download do arquivo, segue como fica o arquivo gerado:

Screen Shot 2013-12-17 at 09.41.32

Exemplo do Arquivo gerado pelo código. Note a diferença de largura das colunas

Como podemos ver é uma interface bem simples de utilizar, embora a o PHPExcel contenha diversos recursos, não é complicado utilizar suas funções. Você pode procurar ações mais específicas na documentação do projeto.

Um pouco além : Cores e Fonte

Com o PHPExcel é possível criar mais planilhas no arquivo, oferecendo ainda mais organização aos seus relatórios. No exemplo abaixo, além de criar um arquivo .xls com mais de uma planilha, também está exemplificado como alterar as cores e fontes das colunas.

 // Podemos definir as propriedades do documento
$objPHPExcel->getProperties()->setCreator("Volts Digital")
        ->setLastModifiedBy("Volts Digital")
        ->setTitle("Exemplo Volts")
        ->setSubject("Artigo  - Labs")
        ->setDescription("Artigo escrito para exemplificar o uso do PHPExcel");

// Adicionamos um estilo de A1 até D1 
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray(
        array('fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'E0EEEE')
            ),
        )
);


// Criando uma nova planilha dentro do arquivo
$objPHPExcel->createSheet();

// Cria as colunas na segunda planilha
$objPHPExcel->setActiveSheetIndex(1) //Passar por parâmetro qual planilha definir como ativa
            ->setCellValue('A1', 'Listagem de Credenciamento 2' )
            ->setCellValue('B1', "Nome " )
            ->setCellValue('C1', "Valor Pago " );


//Seta o cabeçalho e os estilos da Planilha 1( Planilha 0 é a primeira, Planilha 1 segunda....)
$objPHPExcel->setActiveSheetIndex(1); 
  $objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray(
        array('fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'F28A8C')
            ),
        )
);

// Alterando o tamanho da fonte
$objPHPExcel->getActiveSheet()->getStyle('A2:C1')->getFont()->setSize(14);

// Define a planilha ativa para o PHPExcel operar
$objPHPExcel->setActiveSheetIndex(0);

// Define o título da planilha 
$objPHPExcel->getActiveSheet()->setTitle('Planilha de Credeciamento 1');

// Define a largura das colunas de modo automático
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

// Exemplo de preenchimento de dados de maneira dinâmica, a partir de um resultado do banco de dados por exemplo.
$credenciados = $wpdb->get_results($query);
$linha = 3; 
foreach ($credenciados as $key => $credenciado) {
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue("B". $linha, $credenciado->nome)
        ->setCellValue("C". $linha, $credenciado->sobrenome)
        ->setCellValue("D". $linha, $credenciado->email);
    $linha++;
}

// Agora, vamos adicionar os dados na segunda planilha
$objPHPExcel->setActiveSheetIndex(1);


$objPHPExcel->getActiveSheet()->setTitle('Planilha de Pagantes 1');

// Inserção de dados na segunda planilha
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, "Fulano");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, " SIM");

// Define a planilha como ativa sendo a primeira, assim quando abrir o arquivo será a que virá aberta como padrão
$objPHPExcel->setActiveSheetIndex(0);

// Salva  o arquivo 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("arquivo.xls");

Como podemos ver nas imagens abaixo, temos duas planilhas no documento com o cabeçalho do estilo aplicado. Até o  momento a classe PHPExcel opera com o padrão de cores RGB para definir esses atributos de customização

Screen Shot 2013-12-17 at 10.27.38

As cores do cabeçalho foram definidos em RGB, perceba que a primeira planilha a abrir é essa a de Credenciamento, porque definimos assim no código.

Veja como ficou o cabeçalho da segunda planilha:

Screen Shot 2013-12-17 at 10.27.29

Exemplo da customização do cabeçalho em RGB.

Limitações do PHPExcel

Embora a classe seja muito completa e tenha atendido todas as minhas necessidades até agora, no que diz respeito a formatação e afins, ela é muito grande e ocupa muita memória. Quando ultrapassa de 10.000 – 15.000 registros  tende a consumir um valor de memória muito alto, o que pode extrapolar o limite do servidor, é preciso de cautela para utilizado. No caso, a solução foi remover a quantidade de colunas ao máximo para poder gerar o relatório. Diminuir os  itens de customização também colabora para otimizar o consumo de memória.

É isso, espero que tenha ficado claro o uso das principais funções da classe PHPExcel ,  o suficiente para  conseguir gerar relatórios de qualidade no Excel. Qualquer dúvida estamos aí.

baixar

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *


*