Gerando planilhas XLS para o Excel pelo PHP

Recentemente, precisei migrar alguns relatórios CSV para XLS, de forma que pudessem ser abertos diretamente no Excel sem aquelas telas chatas de importação de dados. Utilizei a classe Spreadsheet_Excel_Writer do PEAR e o resultado foi melhor do que o esperado.

Criando planilhas

O arquivo do Excel é organizado em uma pasta de trabalho (workbook) onde ficam uma ou mais planilhas (worksheets). O código PHP seguirá a mesma lógica, instanciando a pasta de trabalho e adicionando a ela um objeto para cada planilha. As planilhas poderão ser preenchidas conforme a posição da linha e coluna, ambas começando do zero.

<?php
require_once "Spreadsheet/Excel/Writer.php";
 
$workbook = new Spreadsheet_Excel_Writer();
 
$worksheet =& $workbook->addWorksheet("Título da planilha");
 
$worksheet->write(0, 0, "Primeira linha, primeira coluna");
$worksheet->write(0, 1, "Primeira linha, segunda coluna");
$worksheet->write(1, 0, "Segunda linha, primeira coluna");
$worksheet->write(1, 1, "Segunda linha, segunda coluna");

Observe que a planilha foi adicionada utilizando o operador de referência &. No PHP 4 isto é necessário para que a variável $worksheet deste escopo represente efetivamente a planilha dentro da pasta de trabalho $workbook. Do PHP 5 em diante este recurso não é necessário, já que variáveis apontam sempre para o mesmo objeto (exceto, claro, quando propositalmente clonados).

void Worksheet::write ( integer $row , integer $col , mixed $token , mixed $format=0 )

• integer $row – número da linha onde escrever (começando do 0)
• integer $col – número da coluna onde escrever (começando do 0)
• mixed $token – conteúdo da célula
• mixed $format – opcional, especifica a formatação da célula

O método write tenta identificar o tipo do conteúdo como texto, número, link, fórmula ou vazio. Esta identificação automágica acabou me levando a um resultado inesperado ao preencher células com número de CPF, já que alguns começavam com zero e, ao serem convertidos de texto para número, acabaram tendo os primeiros dígitos removidos. Para ter certeza do formato da célula, é possível utilizar diretamente as demais funções de escrita:

• writeBlank para escrever uma célula vazia, útil para formatar o estilo sem especificar valor
• writeFormula para escrever uma fórmula, da mesma forma que seria feito no Excel
• writeNumber para escrever um número
• writeString para escrever um texto livre
• writeUrl para escrever um link; o texto visível pode ser especificado no quarto parâmetro e a formatação, se alguma, no quinto parâmetro

Como a identificação de tipo pelo método write é feita com uma série de expressões regulares, utilizar os métodos de escrita para determinado tipo pode melhorar o desempenho do script com planilhas muito grandes.

Além da escrita por célula, é possível preenchermos toda uma linha ou coluna a partir de um array. Estes métodos são especialmente úteis para preencher a planilha a partir de uma consulta ao banco de dados:

mixed Worksheet::writeRow ( integer $row , integer $col , array $val , mixed $format =null )

• integer $row – número da linha onde escrever
• integer $col – número da coluna onde começar a escrever os valores
• array $val – valores das células que serão escritas na linha
• mixed $format – opcional, especifica a formatação da célula

mixed Worksheet::writeCol ( integer $row , integer $col , array $val , mixed $format =null )

• integer $row – número da linha onde começar a escrever os valores
• integer $col – número da coluna onde escrever
• array $val – valores das células que serão escritas na coluna
• mixed $format – opcional, especifica a formatação da célula

Formatando a célula

Enquanto eu migrava alguns relatórios para XLS, percebi que outra grande novidade pro usuário em comparação à importação de CSV seria melhorar o visual da planilha. A formatação de estilo é adicionada à pasta de trabalho e em seguida associada às células que receberão o estilo.

$header =& $workbook->addFormat();
$header->setFgColor(15);
$header->setBold();
 
$relatorio =& $workbook->addWorksheet();
 
$relatorio->write(0, 0, "Aluno", $header);
$relatorio->write(0, 1, "Matrícula", $header);
$relatorio->write(0, 2, "E-mail", $header);

O estilo adicionado define um tom claro de cinza para o fundo e a fonte em negrito. O Excel utiliza uma paleta de cores própria; para adicionar uma cor diferente, o método setCustomColor deve ser utilizado, passando o índice da cor como primeiro argumento (que será utilizado para identificar a cor em funções como setFgColor e setColor) e os níveis RGB como segundo, terceiro e quarto argumentos, respectivamente.

Também é possível passar um array com as configurações diretamente para o método addFormat:

$estilo_padrao =& $workbook->addFormat(array('Size' => 10));
 
$estilo_aluno =& $workbook->addFormat(array('Size' => 10, 'Align' => 'right', 'Bold' => 1));
 
$linha = 0;
 
$relatorio->writeString(++$linha, 0, "Homer", $estilo_aluno);
$relatorio->writeString($linha, 1, "S0001", $estilo_padrao);
$relatorio->writeString($linha, 2, "homer@example.com", $estilo_padrao);
 
$relatorio->writeString(++$linha, 0, "Bart", $estilo_aluno);
$relatorio->writeString($linha, 1, "S0002", $estilo_padrao);
$relatorio->writeString($linha, 2, "bart@example.com", $estilo_padrao);

Todas as opções de formatação podem ser vistas na documentação do addFormat.

Não vi nenhuma forma de deixar as colunas com largura automática, como quando damos dois cliques no divisor de colunas e a largura é reajustada automaticamente, mas o método setColumn pode ser utilizado para determinar uma largura fixa para um conjunto de colunas.

void Worksheet::setColumn ( integer $firstcol , integer $lastcol , float $width , mixed $format=0 , integer $hidden=0 )

• integer $firstcol – primeira coluna do intervalo onde aplicar a formatação
• integer $lastcol – última coluna do intervalo
• float $width – largura das colunas
• mixed $format – opcional, especifica a formatação da célula
• integer $hidden – define se as colunas devem estar ocultas

Enviando para download

Uma vez que a pasta de trabalho esteja montada, basta enviar o header com o tipo de conteúdo e o nome do arquivo adequados para que o browser faça o download corretamente.

$workbook->send("teste.xls");
 
$workbook->close();

O método send envia os headers necessários e o close carrega o arquivo binário.

Instalando a classe

A instalação da classe é feita pelo próprio PEAR, com o detalhe de resolver as dependências necessárias e especificar que a classe ainda está em estado beta:

# pear install –alldeps Spreadsheet_Excel_Writer-beta

Como utilizei a classe na intranet, o comando pôde ser executado como root pelo administrador do servidor para instalar a classe no sistema. Veja o link Installation of a local PEAR copy on a shared host para instruções de como utilizar classes PEAR em servidores compartilhados.

Documentação

Veja ainda a documentação da classe Spreadsheet_Excel_Writer para mais detalhes sobre diversos outros recursos.