Archivo de Excel generado con consulta SQL de MySQL usando PHP

Consulta de MySQL a CSV y Excel con PHP

Dejar un comentario / bases de datos, mysql, php / Por

En este post de programación con PHP y MySQL vamos a ver cómo exportar los resultados de una consulta SQL como CSV (valores separados por coma) y como archivo Excel, además de mostrarlos como HTML.

A partir de las filas que sean devueltas por una consulta vamos a crear un archivo CSV y un archivo de Excel.

Estructura de la tabla

En este ejemplo vamos a trabajar con una tabla llamada personas, cuyas columnas están descritas a continuación. En el script que se muestra a continuación también vas a encontrar algunos datos de prueba.

CREATE TABLE IF NOT EXISTS personas(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(255) NOT NULL,
    edad INTEGER NOT NULL,
    altura DECIMAL(9, 2) NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO personas (nombre, edad, altura) VALUES ('Juan Pérez', 30, 175.50);
INSERT INTO personas (nombre, edad, altura) VALUES ('María Gómez', 25, 160.75);
INSERT INTO personas (nombre, edad, altura) VALUES ('Carlos Ruiz', 40, 180.25);
INSERT INTO personas (nombre, edad, altura) VALUES ('Ana Torres', 22, 165.30);
INSERT INTO personas (nombre, edad, altura) VALUES ('Luis Fernández', 35, 170.10);
INSERT INTO personas (nombre, edad, altura) VALUES ('Carmen Díaz', 28, 168.80);
INSERT INTO personas (nombre, edad, altura) VALUES ('Jorge Martínez', 33, 172.50);
INSERT INTO personas (nombre, edad, altura) VALUES ('Laura Jiménez', 27, 162.45);
INSERT INTO personas (nombre, edad, altura) VALUES ('Marta Sánchez', 31, 169.60);
INSERT INTO personas (nombre, edad, altura) VALUES ('Pedro López', 29, 174.90);

Funciones

Comencemos viendo el código PHP que va a tener una función para obtener la base de datos y una que realiza la consulta SQL  y devuelve las filas de la tabla:

<?php
function obtenerBaseDeDatos()
{
	$contraseña = "";
	$usuario = "root";
	$nombre_base_de_datos = "personas";
	$base_de_datos = new PDO('mysql:host=localhost;dbname=' . $nombre_base_de_datos, $usuario, $contraseña);
	$base_de_datos->query("set names utf8;");
	$base_de_datos->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
	$base_de_datos->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	#$base_de_datos->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	return $base_de_datos;
}


function obtenerPersonas()
{
	$bd = obtenerBaseDeDatos();
	$sentencia = $bd->query("SELECT id, nombre, edad, altura FROM personas");
	return $sentencia->fetchAll(PDO::FETCH_ASSOC);
}

Tenemos a la función que obtiene la base de datos. Debes configurar las credenciales de acceso y el nombre de la base de datos. En mi caso específico la base de datos se llama personas, y he creado una tabla con el mismo nombre dentro de esa base de datos. Puede que en tu caso el usuario, contraseña o nombre de la base de datos sea distinto.

Por otro lado, la segunda función devuelve la lista de personas haciendo la consulta SQL e invocando a la función que obtiene la base de datos. Esta función será usada en los 3 casos: al mostrar los datos como HTML, como CSV y como Excel. La única diferencia radica en el formato de los datos.

Generar reporte como HTML

En el primer caso vamos a ver cómo mostrar los resultados de la consulta en el navegador web para que el usuario pueda revisarlos. Simplemente invocamos a la función y dibujamos una tabla HTML recorriendo el arreglo de personas.

Para lograr esto, estoy incluyendo el archivo funciones.php que contiene las 2 funciones necesarias:

<?php
include_once "funciones.php";
$personas = obtenerPersonas();
?>

Luego, estoy dibujando la tabla:

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Nombre</th>
            <th>Edad</th>
            <th>Altura</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($personas as $persona) { ?>
            <tr>
                <td><?php echo $persona["id"]; ?></td>
                <td><?php echo $persona["nombre"]; ?></td>
                <td><?php echo $persona["edad"]; ?></td>
                <td><?php echo $persona["altura"]; ?></td>
            </tr>
        <?php } ?>
    </tbody>
</table>

Estoy recorriendo todas las filas devueltas por la consulta en el foreach. Después, en cada paso, dibujo una nueva celda con la etiqueta td y accedo a cada propiedad usando la notación [], por ejemplo, para acceder a la edad de la persona acceso con $persona["edad"]

Reporte MySQL como HTML - Listo para exportar como CSV o Excel
Reporte MySQL como HTML – Listo para exportar como CSV o Excel

Fíjate en que ahí mismo tengo los botones para descargar el reporte de MySQL como CSV o como Excel. Lo importante es que sepas que voy a usar la misma función para generar esos 2 archivos.

Generando CSV a partir de consulta SQL

Veamos el siguiente apartado en donde simplemente concatenamos una cadena y vamos agregando los valores separados por coma. Estamos usando un foreach al igual que en la ocasión anterior, pero ahora en cada paso agregamos contenido a la cadena.

En este caso estoy usando sprintf para evitar concatenaciones confusas y para tener un código más limpio.

Después, simplemente muestro la cadena como respuesta a la petición del cliente, pero antes de ello establezco los encabezados para que el navegador sepa que no debe mostrar el contenido, sino forzar su descarga como archivo CSV:

<?php
include_once "funciones.php";

$personas = obtenerPersonas();

$nombreArchivoSalida = "personas.csv";

$contenidoCsv = "Id,Nombre,Edad,Altura\n";
foreach ($personas as $persona) {
    $contenidoCsv .= sprintf(
        "%d,%s,%d,%0.2f\n",
        $persona["id"],
        $persona["nombre"],
        $persona["edad"],
        $persona["altura"],
    );
}
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=$nombreArchivoSalida");
echo $contenidoCsv;

Cuando alguien visite el archivo csv.php se le va a descargar un archivo CSV con todos los datos de la tabla SQL.

CSV generado a partir de reporte MySQL
CSV generado a partir de reporte MySQL

Ya lo dije anteriormente pero lo repito: seguimos usando la misma función, por lo que el reporte HTML, CSV y Excel van a tener los mismos datos.

Excel a partir de consulta con MySQL

Pasemos al último caso: la generación de un archivo de Excel usando MySQL. En este caso vamos a necesitar la librería PHPSpreadSheet que se instala con:

composer require phpoffice/phpspreadsheet

Cuando descargues el código completo simplemente ejecuta: composer install en la ubicación raíz del proyecto, eso va a crear una carpeta llamada vendor en donde estarán todas las dependencias.

Luego incluimos esas clases en el archivo que se encarga de generar el reporte, así como nuestras propias funciones:

<?php
require __DIR__ . "/vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

include_once "funciones.php";
$personas = obtenerPersonas();

Después creamos un nuevo documento y accedemos a la primer hoja, aprovechando también para colocar el título de la misma:

$documento = new Spreadsheet();
$documento
    ->getProperties()
    ->setTitle('Reporte de personas');

$hoja = $documento->getActiveSheet();

$hoja->setTitle("Personas");

Explicando setCellValue

Es necesario hacer una explicación adicional para el caso de setCellValue. En este caso la vamos a invocar con la siguiente sintaxis:

setCellValue([$indiceColumna, $indiceFila], "valor");

El índice de columna es el equivalente a las letras en Excel, de modo que la A es la columna 1. El índice de fila es el número de fila de Excel, de modo que el 1 es la primer fila. Así que si invocamos a:

setCellValue([3,5], "Luis");

Sería el equivalente a colocar el valor Luis en la celda C5 usando Excel.

En este caso voy a usar algunas banderas de índices para llevar un seguimiento de la columna y fila actual, ya sea al momento de escribir los encabezados de la tabla o al momento de agregar cada persona al documento.

Recorrer filas de SQL y escribirlas en Excel

Comenzamos iniciando las variables ayudantes para los índices de filas y columnas. Después, escribimos el encabezado:

$indiceFila = 1;
$indiceColumna = 1;
$columnas = ["id", "nombre", "edad", "altura"];
$encabezados = ["Id", "Nombre", "Edad", "Altura"];
foreach ($encabezados as $encabezado) {
    $hoja->setCellValue([$indiceColumna, $indiceFila], $encabezado);
    $indiceColumna++;
}
$indiceFila++;

Después recorremos el arreglo de personas, mismo que será devuelto por la función que vimos desde el principio.

foreach ($personas as $persona) {
    $indiceColumna = 1;
    foreach ($columnas as $columna) {
        $hoja->setCellValue([$indiceColumna, $indiceFila], $persona[$columna]);
        // La edad va como número
        if ($indiceColumna === 3) {
            $hoja->getStyle([$indiceColumna, $indiceFila])->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER);
        }
        // La altura va con decimales
        if ($indiceColumna === 4) {
            $hoja->getStyle([$indiceColumna, $indiceFila])->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_00);
        }
        $indiceColumna++;
    }
    $indiceFila++;
}

Fíjate en que estoy accediendo a $persona[$columna], esto es una manera simple de dibujar todas las columnas. Podría hacerse con $persona["id"], después con $persona["nombre"] y así sucesivamente, pero es más fácil definir un arreglo de las propiedades que tiene cada fila devuelta por MySQL y usar dicho valor como clave.

En algunos casos estoy definiendo el formato, ya que los números y flotantes deben ser definidos como lo que son. Para ello, reviso el índice de la columna de personas en el que estoy, y si es el 3 (que corresponde a la edad) o el 4 (altura) agrego el formato con setFormatCode.

Descargar Excel

Cuando terminemos de recorrer la lista de personas vamos a tener el documento con todos los datos, pero resta enviarlo como respuesta al cliente. Para ello veamos el siguiente código:

$nombreDelDocumento = "Reporte de personas.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $nombreDelDocumento . '"');
header('Cache-Control: max-age=0');

$writer = IOFactory::createWriter($documento, 'Xlsx');
$writer->save('php://output');
exit;

Cuando el usuario visite el enlace, se le va a descargar el archivo de Excel de manera automática:

Archivo de Excel generado con consulta SQL de MySQL usando PHP
Archivo de Excel generado con consulta SQL de MySQL usando PHP

Poniendo todo junto

Te he mostrado las partes más importantes, pero el código completo está en GitHub. Ahí vas a encontrar la definición de la tabla, el archivo composer.json y los archivos completos para generar HTML, CSV y Excel usando PHP y MySQL: https://github.com/parzibyte/mysql-csv-excel-php

Cuando lo descargues, instala las dependencias con composer install, configura la base de datos en funciones.php y asegúrate de crear la tabla como se indica en esquema.sql.

Recuerda: si hay algún error, el mensaje de error contendrá los detalles necesarios para arreglarlo. El código está probado y funciona perfectamente, solo que en ocasiones es necesario habilitar extensiones o actualizar la versión de PHP.

Estoy aquí para ayudarte 🤝💻

Estoy aquí para ayudarte en todo lo que necesites. Si requieres alguna modificación en lo presentado en este post, deseas asistencia con tu tarea, proyecto o precisas desarrollar un software a medida, no dudes en contactarme. Estoy comprometido a brindarte el apoyo necesario para que logres tus objetivos. Mi correo es parzibyte(arroba)gmail.com, estoy como@parzibyte en Telegram o en mi página de contacto

No te pierdas ninguno de mis posts 🚀🔔

Suscríbete a mi canal de Telegram para recibir una notificación cuando escriba un nuevo tutorial de programación.

Dejar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *