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"]

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.

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:

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.