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.
