Apr 8, 2011
No mundo da programação é raro o dia em que não nos deparamos com um ou outro obstáculo, sendo o desafio e a superação do mesmo algo que nos leva a fazer o que fazemos, seja com ou sem ajuda. Pois bem, à dias foi necessário exportar para um ficheiro uma listagem de determinado site. Até aqui tudo bem, sem problemas. A maneira mais simples seria criar em tempo real um ficheiro csv (ficheiro de valores separados por vírgulas) que pode ser aberto sem problemas no Excel, OpenOffice ou LibreOffice. Mas além de ser simples este tipo de ficheiro é bastante limitado e não nos permite definir quaisquer formatações para uma melhor aparência/facilidade de leitura, pois é um mero ficheiro de texto.
Depois de pesquisar e testar várias alternativas eis que cheguei ao “Santo Graal”: PHPExcel
O nome diz quase tudo, trata-se de uma classe para PHP para criar ficheiros Excel completamente personalizáveis. Fiquei literalmente de boca aberta com a potencialidade desta classe, pois tudo o que pode fazer no Excel normal é possível fazer nesta classe, desde colocar texto a negrito até colocar fórmulas de cálculo nas células.
Podem efectuar o download desta classe em PHPExcel Downloads (recommended download). Este download é muito completo, pois contém ficheiros de exemplo da sua utilização e uma documentação excelente onde nada é deixado ao acaso, o que me deixou impressionado.
Vou aqui demonstrar alguns exemplos do que podemos fazer com esta classe e recomendo vivamente a utilização do Komodo Edit 6 como ferramenta open source de desenvolvimento em PHP.
Para termos disponível a classe PHPExcel basta enviarmos para o nosso servidor a pasta “Classes” do PHPExcel. Uma vez feito isto a classe pode ser usada em qualquer parte do nosso site, bastando para isso fazer uma chamada à classe:
| PHP | | copiar código | | ? |
| 1 | // Obter acesso à classe PHPExcel |
| 2 | require_once 'Classes/PHPExcel.php'; |
Aqui o caminho correcto para a classe depende onde colocaram a pasta “Classes“, por isso certifiquem-se do caminho correcto.
De seguida vamos criar um objecto de modo a usar a classe:
| PHP | | copiar código | | ? |
| 1 | // Criar um novo objecto PHPExcel |
| 2 | $objPHPExcel = new PHPExcel(); |
Neste momento já podemos aceder a todas as funcionalidades da classe PHPExcel usando, aqui como exemplo, o objecto $objPHPExcel.
Podemos definir as propriedades comuns/normais de um ficheiro Excel:
| PHP | | copiar código | | ? |
| 1 | // As várias propriedades do documento que podemos definir |
| 2 | $objPHPExcel->getProperties()->setCreator("Autor do Documento") |
| 3 | ->setLastModifiedBy("Modificado por...") |
| 4 | ->setTitle("O Título") |
| 5 | ->setSubject("O Assunto") |
| 6 | ->setDescription("A Descrição") |
| 7 | ->setKeywords("As Palavras Chave") |
| 8 | ->setCategory("A Categoria"); |
Caso seja necessário, ou por ser mais prático, podemos inserir dados consecutivos em várias células sem limites:
| PHP | | copiar código | | ? |
| 1 | // Inserir dados nas células A1 e A2 |
| 2 | $objPHPExcel->setActiveSheetIndex(0) |
| 3 | ->setCellValue('A1', 'Título do Exemplo da Classe PHPExcel (vai ficar a negrito)') |
| 4 | ->setCellValue('A2', 'uma excelente classe (vai ficar em itálico a azul)'); |
E sempre que o desejarmos podemos definir a formatação quer das células (tipo de letra, cor, alinhamento) quer das colunas/linhas (ajuste automático da altura/largura), sem esquecer a possibilidade de desenhar limites em torno de uma célula ou de um conjunto de células:
| PHP | | copiar código | | ? |
| 01 | // Definir a largura da coluna A para automático/auto-ajustar |
| 02 | $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); |
| 03 | |
| 04 | // Formatar a célula A1 a negrito |
| 05 | $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); |
| 06 | |
| 07 | // Formatar a célula A2 a itálico |
| 08 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setItalic(true); |
| 09 | |
| 10 | // Formatar a cor do texto da célula A2 a azul |
| 11 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); |
| 12 | |
| 13 | // Formatar o topo da célula A2 com uma borda |
| 14 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); |
| 15 | |
| 16 | // Colocar uma borda em torno da área A1:A5 |
| 17 | $objPHPExcel->getActiveSheet()->getStyle('A1:A5')->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); |
Usar fórmulas também é uma realidade:
| PHP | | copiar código | | ? |
| 1 | // Usar fórmulas |
| 2 | $objPHPExcel->setActiveSheetIndex(0) |
| 3 | ->setCellValue('A3', '15') |
| 4 | ->setCellValue('A4', '20') |
| 5 | ->setCellValue('A5', '=A3+A4'); |
Depois de termos tudo o que queremos no ficheiro temos de indicar a criação do ficheiro com base nos dados guardados até ao momento no objecto $objPHPExcel:
| PHP | | copiar código | | ? |
| 1 | // Indicação da criação do ficheiro |
| 2 | $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); |
E para que se obtenha este documento gerado em tempo real damos indicação para o mesmo abrir no browser (seja para visualização no browser seja para download):
| PHP | | copiar código | | ? |
| 1 | // Encaminhar o ficheiro resultante para abrir no browser ou fazer download |
| 2 | header('Content-Type: application/vnd.ms-excel'); |
| 3 | header('Content-Disposition: attachment;filename="teste.xls"'); |
| 4 | header('Cache-Control: max-age=0'); |
| 5 | $objWriter->save('php://output'); |
Ao corrermos uma página com este código, o resultado será algo deste género:
Ficheiro criado com a classe PHPExcel
Estas são apenas algumas das muitas funcionalidades disponíveis nesta incrível classe PHPExcel à espera de serem exploradas.
Para que possam testar este mesmo código, fica aqui o código exemplo completo:
| PHP | | copiar código | | ? |
| 01 | getProperties()->setCreator("Autor do Documento") |
| 02 | ->setLastModifiedBy("Modificado por...") |
| 03 | ->setTitle("O Título") |
| 04 | ->setSubject("O Assunto") |
| 05 | ->setDescription("A Descrição") |
| 06 | ->setKeywords("As Palavras Chave") |
| 07 | ->setCategory("A Categoria"); |
| 08 | |
| 09 | // Inserir dados nas células A1 e A2 |
| 10 | $objPHPExcel->setActiveSheetIndex(0) |
| 11 | ->setCellValue('A1', 'Título do Exemplo da Classe PHPExcel (vai ficar a negrito)') |
| 12 | ->setCellValue('A2', 'uma excelente classe (vai ficar em itálico a azul)'); |
| 13 | |
| 14 | // Definir a largura da coluna A para automático/auto−ajustar |
| 15 | $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); |
| 16 | |
| 17 | // Formatar a célula A1 a negrito |
| 18 | $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); |
| 19 | |
| 20 | // Formatar a célula A2 a itálico |
| 21 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setItalic(true); |
| 22 | |
| 23 | // Formatar a cor do texto da célula A2 a azul |
| 24 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); |
| 25 | |
| 26 | // Formatar o topo da célula A2 com uma borda |
| 27 | $objPHPExcel->getActiveSheet()->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); |
| 28 | |
| 29 | // Colocar uma borda em torno da área A1:A5 |
| 30 | $objPHPExcel->getActiveSheet()->getStyle('A1:A5')->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); |
| 31 | |
| 32 | // Usar fórmulas |
| 33 | $objPHPExcel->setActiveSheetIndex(0) |
| 34 | ->setCellValue('A3', '15') |
| 35 | ->setCellValue('A4', '20') |
| 36 | ->setCellValue('A5', '=A3+A4'); |
| 37 | |
| 38 | // Indicação da criação do ficheiro |
| 39 | $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); |
| 40 | |
| 41 | // Encaminhar o ficheiro resultante para abrir no browser ou fazer download |
| 42 | header('Content-Type: application/vnd.ms-excel'); |
| 43 | header('Content-Disposition: attachment;filename="teste.xls"'); |
| 44 | header('Cache-Control: max-age=0'); |
| 45 | $objWriter->save('php://output'); |
| 46 | ?> |
Façam bom uso desta classe pois tem muita potencialidade!
Em caso de questões, dúvidas ou sugestões, deixe um comentário no final da página.
Chamo-me Tiago Ramalho e decidi criar o blog Programação Web para partilhar com todos os interessados a minha experiência nesta área de programação, assim como ajudar sempre que possível nas questões dos visitantes.
Muito bom post!
Desta forma estamos livres dos velhinhos ficheiros cvs, nos quais não conseguíamos aplicar qualquer tipo de formatação dos dados.
Muito bom o tutorial. Falta agora explicar sobre como inserir gráficos com o obj: objDrawing
Valeu!
Gostaria de saber como posso formatar as celulas como texto, data…
Olá Bianca!
Uma das maneiras para formatar células é esta:
$objPHPExcel->getActiveSheet()->getCell(‘A1′)->setValueExplicit(’25′, PHPExcel_Cell_DataType::TYPE_NUMERIC);
Onde tens “TYPE_NUMERIC” podes usar o formato que pretendes, por exemplo: TYPE_DATE, TYPE_CURRENCY, TYPE_TEXT
Aconselho vivamente a consultares o manual de referência desta classe, pois lá tens mais exemplos de como usares.
Olá Alexandre!
Pelo que estive a ler no manual penso que ainda não está implementado a inclusão de gráficos nesta classe…
Não consigo contornar um problema:
Eu preciso colocar NÚMEROS em um campo TEXTO, porém o Excel mostra uma fórmula (E1+14.
$objPHPExcel->getActiveSheet()->getStyle(intToAlfa(‘A1′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->setCellValue(‘A1′), ’999999999999999′);
O Excel fica dando palpite e pensando que o campo é numérico. Há como contornar este problema? Colocar números em um campo texto?
Olá Alexandre.
Pelo que estive a ver parece ser mesmo uma confusão que o próprio excel faz com o tipo de dados que se introduzem na célula de texto quando se colocam números muito longos.
Uma das maneiras de resolver essa situação é colocar uma plica extra antes de escrever o número. Ficaria assim:
$objPHPExcel->getActiveSheet()->setCellValue(‘A1′, ‘\’999999999999999′);
Outra forma seria definir o formato da célula como número, FORMAT_NUMBER.
Tiago Ramalho,
eu estou usando está class para gerar o excel mas na hora em que tento abrir o arquivo ou salvar ele está ficando criptografado.
sabes o porque disso?
Olá Bruno!
Possivelmente tens algo errado no código que não permite guardar correctamente os dados no ficheiro gerado.
O ficheiro php onde estás a gerar o excel não pode conter nenhum output a não ser para gerar o ficheiro. Elimina qualquer “echo” ou até mesmo algum espaço em branco no inicio do ficheiro php antes da tag <?php caso contrário o resultado será um ficheiro excel "criptografado" com algo deste tipo: àõÿ À
Aconselho vivamente a leitura da documentação desta classe http://phpexcel.codeplex.com/releases/view/45412 no link “PHPExcel 1.7.6 – Documentation (all-in-one)” que é bastante boa. Fazem referência a este tipo de situações.
Como eu faço para criar varias planilhas no mesmo arquivo com o PHPExcel?
Olá Bruno.
Para criar várias planilhas (folhas em Portugal) basta usar os seguintes comandos:
$objWorksheet1 = $objPHPExcel->createSheet();
$objWorksheet1->setTitle(‘Título da planilha’);
Certo mais quando eu gero o Excel só está escrevendo na primeira planilha. segue abaixo o código
foreach ($listaFornecedor as $objEmpresa){
$teste = $objPHPExcel->createSheet();
//define propriedades do arquivo
$teste->setTitle(“Relatorio”.$ic_sub);
$objPHPExcel->getActiveSheet()->setCellValue($colunas[0].$ic_sub, utf8_encode($objEmpresa->nome));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[1].$ic_sub, Valor2($objEmpresa->total_dolar));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[2].$ic_sub, Valor2($objEmpresa->total_kwanza));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[3].$ic_sub, Valor2($objEmpresa->total_euro));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[4].$ic_sub, Valor2($objEmpresa->total_pago_dolar));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[5].$ic_sub, Valor2($objEmpresa->total_pago_kwanza));
$objPHPExcel->getActiveSheet()->setCellValue($colunas[6].$ic_sub, Valor2($objEmpresa->total_pago_euro));
$totalSaldoDolar = $objEmpresa->saldo_dolar;
$objPHPExcel->getActiveSheet()->setCellValue($colunas[7].$ic_sub,Valor2($totalSaldoDolar));
$totalSaldoKwanza = $objEmpresa->saldo_kwanza;
$objPHPExcel->getActiveSheet()->setCellValue($colunas[8].$ic_sub,Valor2($totalSaldoKwanza));
$totalSaldoEuro = $objEmpresa->saldo_euro;
$objPHPExcel->getActiveSheet()->setCellValue($colunas[9].$ic_sub,Valor2($totalSaldoEuro));
$saldoValorFaturacaoDolar += $objEmpresa->total_dolar;
$saldoValorFaturacaoKwanza += $objEmpresa->total_kwanza;
$saldoValorFaturacaoEuro += $objEmpresa->total_euro;
$saldoValorPagoEmpresaDolar += $objEmpresa->total_pago_dolar;
$saldoValorPagoEmpresaKwanza += $objEmpresa->total_pago_kwanza;
$saldoValorPagoEmpresaEuro += $objEmpresa->total_pago_euro;
$saldoGeralDolar += $objEmpresa->saldo_dolar;
$saldoGeralKwanza += $objEmpresa->saldo_kwanza;
$saldoGeralEuro += $objEmpresa->saldo_euro;
$ic_sub++;
}
Olá Bruno!
Demorei a responder mas aqui está a solução:
Depois da linha:
$teste->setTitle(“Relatorio”.$ic_sub);
escreva o seguinte código:
$teste->setActiveSheetIndexByName(“Relatorio”.$ic_sub);
Desta forma definimos que a folha activa é a que acabámos de criar e será nela que vão ser inseridos os dados que pretende
Como já referi anteriormente aconselho vivamente a leitura da documentação desta classe http://phpexcel.codeplex.com/releases/view/45412 no link “PHPExcel 1.7.6 – Documentation (all-in-one)” que é bastante boa. Recorro muitas vezes a esta documentação sempre que uso esta fabulosa classe.
Tiago,
Estou entusisasmado e achando ótima está classe, mas tenho uma dúvida. Na formatação de células é possível mesclar células?
Ex ter quatro colunas e na primeira linha apenas mesclá-las de modo que entusiasmado ter o titulo centralizado na tabela.
Olá Fagner,
Para juntar essas 4 células (da A1 até à A4) basta usar o seguinte código:
$objPHPExcel->getActiveSheet()->mergeCells(‘A1:A4′);
Viva,
Como é que eu coloco uma cor no fundo da célula?
Olá Luís!
O seguinte código muda a cor de fundo da célula B2 para vermelho.
$objPHPExcel->getActiveSheet()->getStyle(‘B2′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
Este sim o cdigo para mudar a cor de fundo das celulas. so tem de especificar o codigo da cor em hexadecimal.
$objPHPExcel->getActiveSheet()->getStyle(‘B2′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(‘B2′)->getFill()->getStartColor()->setARGB(‘FFFF0000′);
Olá Tiago,
Obrigado pela sua ajuda, já implementei o seu código e ficou a funcionar na perfeição.
Obrigado, um abraço.
oi, Tiago
Usei seu codigo para exportar um relatorio para o excel ele funcionou mas qdo uma palavra tem acendo, a palavra é impressa so ate o acento depois dele não escreve nada, assim por exemplo o nome josé fica jos, vc sabe como posso resolver esse problema
Olá Sizu!
Essa situação é nova para mim.
Experimentou o código que aqui mostrei como exemplo? Também acontece a mesma situação?