Cómo Crear Reportes Excel con PHP y PhpSpreadsheet

Generar reportes en Excel es necesario muchas aplicaciones web, especialmente en aquellas que manejan grandes cantidades de datos. En este artículo exploraremos cómo usar la biblioteca PhpSpreadsheet para generar reportes en Excel.

PhpSpreadsheet

PhpSpreadsheet es una librería PHP que permite crear, leer y modificar archivos de hojas de cálculo, como Excel y LibreOffice Calc. Es útil para generar reportes, exportar datos y automatizar tareas relacionadas con hojas de cálculo en aplicaciones web.

Para el funcionamiento de PhpSpreadsheet se requiere tener instalado Composer y PHP 8.1+, con extensiones GD, ZIP. Luego se instala PhpSpreadSheet en línea de comandos con:


composer require phpoffice/phpspreadsheet

Ejemplo Básico

Para crear nuestro primer script, creamos una instancia de Spreadsheet que representa un documento excel, luego obtenemos una instancia de una hoja excel con getActiveSheet, luego usamos setCellValue para agregar valores en las celdas del excel.


<?php
require 'vendor/autoload.php';

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

$spreadsheet = new Spreadsheet();           // create excel instance
$sheet = $spreadsheet->getActiveSheet();    // get sheet instance
$sheet->setTitle('Demo');                   // set sheet title

// populate data
$sheet->setCellValue('A1', 'Nombres:');
$sheet->setCellValue('B1', 'Luis Armstrong');

$sheet->setCellValue('A2', 'Email:');
$sheet->setCellValue('B2', 'louis@gmail.com');

// write file
$writer = new Xlsx($spreadsheet);
$writer->save('tmp/hello.xlsx');

Reporte en Excel

Para crear un reporte, vamos a definir un array con los datos a mostrar, recorremos este arreglo y vamos agregando las celdas con el método setCellValue de forma dinámica y finalmente agregamos los header necesarios para forzar la descarga del excel recién generado.


<?php
require 'vendor/autoload.php';

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

$report = [
    ['id', 'Nombre', 'Email', 'Teléfono'],
    [11, 'Alberto Pérez', 'juan@example.com', 997777777],
    [23, 'Lourdes Gómez', 'ana@example.com', 998888888],
    [35, 'Fernando Torres', 'luis@example.com', 996666666],
];

// create report
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Reporte');

// populate report
for ($i = 0; $i < 4; $i++) {
    $sheet->setCellValue('A'.($i+1), $report[$i][0]);
    $sheet->setCellValue('B'.($i+1), $report[$i][1]);
    $sheet->setCellValue('C'.($i+1), $report[$i][2]);
    $sheet->setCellValue('D'.($i+1), $report[$i][3]);
}

// download file
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="report-basic.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

Factura en Excel

PhpSpreadsheet es muy completo y permite aplicar estilos, formulas, entre otros. Vamos a crear un script que tome un listado de productos (nombre, precio, cantidad) y genere una factura con los subtotales, impuestos y total calculado.

Aplicar estilos

Declaramos tres variables con estilos para nuestras celdas $styleHead (texto en negrita y fondo celeste), $styleBold (texto en negrita) $styleTotal (texto en negrita y fondo amarillo), estos estilos se aplicarán posteriormente con el método applyFromArray.

Aplicar formulas

Aplicar formulas con PhpSpreadsheet es igual a usar Excel normal, las formulas son un texto que empiezan con el simbolo igual, para nuestro ejemplo usaremos las siguientes formulas:

=Ci*Di: para obtener el subtotal por productos (precio x cantidad)
=SUM(E2:E6): para el la suma de los subtotales de productos.
=E8*0.18: para calcular el impuesto a las ventas, el 18% del subtotal.
=E8*1.18: para calcular el total incluido el impuesto a las ventas.


<?php
require 'vendor/autoload.php';

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

// declare products
$products = [
    ['sku' => 765234, 'name' => 'Pasta Dental Sensodyne', 'price' => 15.50, 'quantity' => 2],
    ['sku' => 232462, 'name' => 'Tripack Jabón en Barra', 'price' => 12.50, 'quantity' => 1],
    ['sku' => 455435, 'name' => 'Lejía en Gel Clorox Ma', 'price' => 9.90, 'quantity' => 1],
    ['sku' => 750472, 'name' => 'Bolsas Sanitarias Pets', 'price' => 1.20, 'quantity' => 4],
    ['sku' => 431724, 'name' => 'Pipeta Antipulga Perro', 'price' => 23.99, 'quantity' => 1],
];

// create excel document
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Invoice');

// declare styles
$styleHead = [
    'font' => [ 'bold' => true ],
    'fill' => [ 'fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FF00BFFF'] ],
];

$styleBold = [
    'font' => [ 'bold' => true ]
];

$styleTotal = [
    'font' => [ 'bold' => true ],
    'fill' => [ 'fillType' => Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFEEFE00'] ],
];

// set headers titles
$sheet->setCellValue('A1', 'SKU');
$sheet->setCellValue('B1', 'Producto');
$sheet->setCellValue('C1', 'Precio');
$sheet->setCellValue('D1', 'Cantidad');
$sheet->setCellValue('E1', 'Subtotal');

// set header styles
$sheet->getStyle('A1:E1')->applyFromArray($styleHead);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setWidth(12);
$sheet->getColumnDimension('D')->setWidth(12);
$sheet->getColumnDimension('E')->setWidth(12);

// set content
foreach ($products as $row => $product) {
    $pos = $row + 2;
    $sheet->setCellValue('A'.$pos, $product['sku']);
    $sheet->setCellValue('B'.$pos, $product['name']);
    $sheet->setCellValue('C'.$pos, $product['price']);
    $sheet->setCellValue('D'.$pos, $product['quantity']);
    $sheet->setCellValue('E'.$pos, '=C'.$pos.'*D'.$pos);            // price * quantity
}

// set resume
$totalRows = sizeof($products);

$rowSub = $totalRows + 3;
$rowIGV = $totalRows + 4;
$rowTot = $totalRows + 5;

$sheet->setCellValue('D'.$rowSub,'Subtotal:');
$sheet->setCellValue('E'.$rowSub,"=SUM(E2:E".($totalRows+1).")");   // calculate subtotal

$sheet->setCellValue('D'.$rowIGV,'IGV:');
$sheet->setCellValue('E'.$rowIGV,"=E".$rowSub."*0.18");             // calculate tax

$sheet->setCellValue('D'.$rowTot,'Total:');
$sheet->setCellValue('E'.$rowTot,"=E".$rowSub."*1.18");             // calculate total

$sheet->getStyle('D'.$rowSub.':D'.$rowIGV)->applyFromArray($styleBold);
$sheet->getStyle('D'.$rowTot.':E'.$rowTot)->applyFromArray($styleTotal);

// download file
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="invoice.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

Listo, la tarea no es difícil, sólo es tema de llevar el control de la fila en la que estamos trabajando y agregar las celdas adecuadas.

Consideraciones

Al generar archivos Excel en PHP, es importante tener en cuenta las siguientes consideraciones de memoria:

Tamaño del archivo: Los archivos Excel grandes pueden consumir mucha memoria, limita el tamaño de los archivos o reduce la cantidad de datos a mostrar.
Uso de caché: Configura el caché de celdas para mejorar el manejo de memoria, PhpSpreadsheet permite usar caché en disco para reducir el consumo de memoria RAM.
Liberar memoria: Después de crear y guardar el archivo Excel, asegúrate de liberar los recursos usados, puedes usar unset().
Segmentación de datos: Si tienes que procesar grandes volúmenes de datos, considera segmentarlos en partes más pequeñas y manejables.

Conclusión

Generar reportes en Excel usando PHP es sencillo con la ayuda de PhpSpreadsheet. Esta librería proporciona una API fácil de usar para crear, leer y escribir archivos Excel. Puedes personalizar y expandir estos ejemplos de acuerdo a tus necesidades.