Criando Planilhas Excel com PHP

Muitas vezes precisamos que algum programa exporte seus dados para planilhas do Excel ou OpenOffice. Existem diversas maneiras de fazer isso, uma interessante é utilizando a biblioteca PHPSpreadsheet.

Para utilizá-la, primeiro devemos instalá-la através do composer.

composer require phpoffice/phpspreadsheet

Criar uma planilha é bastante simples, vejamos um exemplo básico.

include('../vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Teste!');

$writer = new Xlsx($spreadsheet);
$writer->save('teste.xlsx');

Primeiro na linha 6 criamos um objeto Spreadsheet este objeto é nosso documento inteiro. Na linha 7 pegamos um objeto de Worksheet através do método getActiveSheet que retorna a “aba” atualmente ativa da planilha.

Na linha 8 adicionamos um valor na planilha, para isso utilizamos o método setCellValue do objeto Worksheet passando a coordenada(coluna como letra e linha como número, como utilizamos na planilha) e o valor desejado.

Uma vez com os valores inseridos vamos salvar o documento, para isso precisamos de um objeto writer, para cada formato de planilha temos um writer diferente, como no caso vamos salvar em xlsx, então criamos um objeto Xlsx (linha 10) passando o objeto Spreadsheet que representa o documento para seu construtor. Com o objeto writer chamamos o método save passando o caminho e nome do documento ser salvo.

Para salvar em outro formato apenas mudamos o objetto writer que iremos utilizar, por exemplo, para salvar em ods(openoffice) alteramos somente as duas últimas linhas.

$writer = new Ods($spreadsheet);
$writer->save('teste.ods');

Como vimos para adicionamos um valor na planilha utilizamos o método setCellValue, com ele podemos adicionar qualquer valor string, int, float, bool. Também podemos adicionar um valor a planilha chamando o método getCell passando a coordenada da célula, o que irá retornar um objeto Cell, do qual podemos chamar o método setValue para adicionar um valor.

Se quisermos inserir uma fórmula na planilha a adicionamos ela como uma string da mesma forma como se fosse digitada diretamente na planilha.

Vejamos um exemplo de uma planilha utilizando uma fórmula.

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Produto');
$sheet->setCellValue('B1', 'Preço');

$sheet->getCell('A2')->setValue( 'Monitor'); // inserindo utilizando getCell
$sheet->getCell('B2')->setValue(800.99);

$sheet->getCell('A3')->setValue('Notebook');
$sheet->getCell('B3')->setValue(4200.50);

$sheet->setCellValue('A4', 'Teclado');
$sheet->setCellValue('B4', 250.20);

$sheet->setCellValue('A5','Total');
$sheet->setCellValue('B5','=SUM(B2:B4)'); // fórmula

Caso os dados que desejamos inserir na planilha venham de um array, podemos adicionar diretamente através do método fromArray.

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$data = [
    ['Produto','Preço'],
    ['Monitor', 800.99],
    ['Notebook',4200.50],
    ['Teclado',250.20]
];

$sheet->fromArray($data, null, 'B2');

$sheet->setCellValue('B6','Total');
$sheet->setCellValue('C6','=SUM(C3:D5)');

No exemplo acima criamos um array na linha 4, que poderia facilmente ter vindo de um banco de dados, e então passamos para o método fromArray como primeiro parâmetro, nos segundo parâmetro informamos qual valor no nosso array não vai ser inserido planilha(no caso valores null ficarão em branco) e o terceiro é a coordenada inicial onde os dados do array irão ser inseridos, ou seja, a célula onde o elemento $data[0][0] será inserido, mais ou menos como quando estamos colando os dados de uma planilha para outra.

Caso lidar com as coordenadas no formato de planilhas(A1, C5, D8, etc) não seja possível no seu projeto, você pode utilizar os métodos setCellValueByColumnAndRow e getCellByColumnAndRow para utilizar coordenadas somente numéricas. Os dois métodos recebem como parâmetro dois inteiros em vez de uma string, sendo eles a coluna e linha. Lembrando que diferente de indices de arrays aqui a primeira posição é a 1.

$sheet->setCellValueByColumnAndRow(1, 1, 'Produto');
$sheet->setCellValueByColumnAndRow(2, 1, 'Preço');

$sheet->getCellByColumnAndRow(1, 2)->setValue( 'Monitor');
$sheet->getCellByColumnAndRow(2, 2)->setValue(800.99);

$sheet->getCellByColumnAndRow(1, 3)->setValue('Notebook');
$sheet->getCellByColumnAndRow(2, 3)->setValue(4200.50);

$sheet->setCellValueByColumnAndRow(1, 4, 'Teclado');
$sheet->setCellValueByColumnAndRow(2, 4, 250.20);

$sheet->setCellValueByColumnAndRow(1, 5,'Total');
$sheet->setCellValueByColumnAndRow(2, 5,'=SUM(B2:B4)');

Para adicionarmos um valor do tipo data em uma célula da planilha através de um objeto DateTime, devemos convertê-lo através do método PHPToExcel, inserindo na célula o seu valor de retorno.

$data = new DateTime('now');

$sheet->setCellValue('A1', 'Data');
$sheet->setCellValue('B1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($data) );

Formatando dados de uma planilha

Para formatar os dados de um planilha utilizamos o método getStyle (passando a célula ou o intervalo de células) para obter um objeto Style, onde podemos manipular a formatação, tanto de formato de dados, quanto a aparência de um ou grupo de células. Lembrando que é muito mais eficiente mudar o estilo de várias células através de um intervalo do que fazer isso através de um loop chamando o método para cara célula.

Para formatar o tipo de dado chamamos o método getNumberFormat, para obter um objeto NumberFormat e deste chamamos o método setFormatCode para definir passando o código de formatação. Nesse código é o mesmo que utilizamos quando formatamos a planilha no Excel quando formatamos e escolhemos personalizar a formatação.

A classe NumberFormat possui diversas constantes com formatos bastante comuns para utilizarmos, entre eles:

FORMAT_NUMBER_00: 0.00

FORMAT_DATE_DDMMYYYY: dd/mm/yyyy

FORMAT_DATE_TIME1: h:mm AM/PM

FORMAT_PERCENTAGE: 0%

FORMAT_CURRENCY_USD_SIMPLE: “$”#,##0.00_-

$data = new DateTime('now');

$sheet->setCellValue('A1', 'Data');
$sheet->setCellValue('B1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($data) );

$sheet->setCellValue('A2', 'Número');
$sheet->setCellValue('B2', 130.9);

$sheet->setCellValue('A3', 'Dinheiro');
$sheet->setCellValue('B3', 1500);

$sheet->getStyle('B1')->getNumberFormat()->setFormatCode(
    NumberFormat::FORMAT_DATE_DDMMYYYY
);

$sheet->getStyle('B2')->getNumberFormat()->setFormatCode(
    NumberFormat::FORMAT_NUMBER_00
);

$sheet->getStyle('B3')->getNumberFormat()->setFormatCode(
    '_-R$ * #.##0,00_-;-R$ * #.##0,00_-;_-R$ * "-"??_-;_-@_-'
);

Fonte

Para modificarmos as fontes utilizamos o método getFont para obter um objeto Font nos dando acesso a vários métodos para alterá-la. Para alterar a cor primeiro chamamos o método getColor e em sequencia o método setARGB passando a cor desejada, sendo ela no formato ARGB em string ou utilizando uma constante da classe Color.

Para deixar uma fonte em negrito chamamos o método setBold passando true para ativar. Para definir que a fonte seja em itálico chamamos o método setItalic. Para alterar a familia da fonte utilizamos o método setName passando o nome da fonte. E para definir o tamanho da fonte utilizamos o método setSize.

$sheet->getStyle('A1')->getFont()->getColor()->setARGB(Color::COLOR_RED);
$sheet->getStyle('A1')->getFont()->setBold(true);
$sheet->getStyle('A1')->getFont()->setItalic(true);

$sheet->getStyle('A1')->getFont()->setName('Verdana');
$sheet->getStyle('A1')->getFont()->setSize(24);

Para definir o alinhamento horizontal da célula chamamos o método getAlignment e em sequencia o método setHorizontal passando a direção, normalmente uma das constantes da classe Alignment, como por exemplo: HORIZONTAL_RIGHT, HORIZONTAL_CENTER, HORIZONTAL_LEFT, HORIZONTAL_JUSTIFY, etc. O mesmo se aplica para o alinhamento vertical, mas utilizamos o método setVertical e outras constantes como valor passado.

$sheet->getStyle('A1:A3')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

$sheet->getStyle('A1:A3')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

Fundo

Para definir uma cor de fundo primeiro chamamos o método getFill e em seguida o método setFillType para definir o tipo de preenchimento, onde passamos uma constante da classe Fill, como FILL_SOLID. Depois chamamos o getStartColor e em seguida o setARGB da mesma forma que utilizamos com a cor da letra.

Também podemos definir o tipo de preenchimento para Gradiente utilizando a constante Fill:FILL_GRADIENT_LINEAR, se o utilizarmos devemos definir a cor de inicio getStartColor e a cor final getEndColor, bem como o ângulo que será utilizado no degrade com setRotation.

$sheet->getStyle('A1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setARGB(Color::COLOR_GREEN);


$sheet->getStyle('A3')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR);
$sheet->getStyle('A3')->getFill()->getStartColor()->setARGB(Color::COLOR_GREEN); // cor de inicio
$sheet->getStyle('A3')->getFill()->getEndColor()->setARGB(Color::COLOR_BLUE);//cor final 
$sheet->getStyle('A3')->getFill()->setRotation(45); // angulo do degrade linear

Bordas

Para adicionarmos bordas primeiro chamamos o método getBorders que retorna um objeto Borders, deste objeto chamamos um método para selecionar qual parte da borda queremos, para todos utilizamos o método getAllBorders, para somente a borda do topo utilizamos getTop, para a esquerda getLeft, para a inferior getBottom e para a direita getRight. Este métodos irão retornar um objeto Border do qual chamaos o método setBorderStyle para definir o tipo de borda, sendo os valores utilizados da constante da classe Border, como : BORDER_THICK, BORDER_DASHED, BORDER_DASHED, etc.

Podemos também definir a cor da borda chamando o método setColor do Objeto Borders e passando um objeto Color para ele.

$sheet->getStyle('A1:B3')->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);

$sheet->getStyle('A1:B3')->getBorders()->getAllBorders()->setColor(new Color('FF00FF00'));

Forma alternativa de aplicar estilo

De forma alternativa existe outra maneira de aplicar estilos em um planilha, para isso criamos um array com as propriedades que queremos definir e atribuímos seus valores. Após passamos este array para o método applyFromArray.

$style = [
    'font' => [
        'bold' => true,
        'italic' => true,
        'name' => 'Verdana',
        'size' => 24
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => [
            'argb' => Color::COLOR_GREEN
        ]
    ]
];
$sheet->getStyle('A1')->applyFromArray($style);

Dimensões de Linhas e Colunas

Para alterar as dimensões das colunas e das linhas primeiro chamamos o método getColumnDimension para a coluna e getRowDimension para a linha. Depois chamamos setWidth para a definir a largura da coluna e setRowHeight para definir a altura da linha. Os dois métodos recebem um float como primeiro parâmetro e o segundo a unidade de medida a ser utilizada. 

$sheet->getColumnDimension('A')->setWidth(100,'px');
$sheet->getRowDimension(3)->setRowHeight(100,'px');

Merge

Para fazer o merge entre células se utiliza o método mergeCells, passando o intervalo de células em que se deseja realizar o merge. 

$sheet->setCellValue('D1', 'Titulo');
$sheet->mergeCells('D1:F1');

Abas

Para adicionar uma outra aba chamamos o método createSheet do objeto Spreadsheet, e podemos alterar seu titulo chamando setTitle. Também podemos alterar a “aba” ativa chamando o método setActiveSheetIndex passando a posição(começando em 0) da aba que queremos ativar.

$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('outra aba');

$spreadsheet->setActiveSheetIndex(1);

Bom o básico da biblioteca para a criação de planilhas em PHP é isso, muito mais detalhes podem ser encontrado na documentação da biblioteca.

Então era isso T++.