Crear libro de Excel a partir de una base de datos de MySQL

En este artículo voy a explicar cómo podemos exportar los datos de una base de datos de MySQL a un archivo de Excel u hoja de cálculo.

Vamos a usar PDO para conectar a la base de datos, y PHPSpreadSheet para crear un archivo de Excel y escribirle los datos traídos desde la tabla de la BD.

Actualización: mira este post para aprender a copiar datos de Excel a MySQL.

Requisitos y recomendaciones

Por favor lee los siguientes posts, que aunque no son esenciales para esto, te darán una mejor idea de lo que estamos haciendo. Además de eso, aquí expondré todo suponiendo que ya leíste lo que recomiendo.

• Leer archivo de Excel con PHPSpreadSheet y PHP
• Conectar PHP y MySQL
• Parte 2 de PHP con MySQL: cursores y comprobar si valor existe
• Crear archivo de Excel con PHPSpreadSheet y PHP
• Sintaxis corta de Arrays en PHP

Como requisito debes tener la librería instalada (en el primer enlace de arriba se muestra cómo) y obviamente tener PHP, si no lo tienes, mira cómo se instala aquí.

Ahora sí comencemos.

Descripción del proyecto

Vamos a hacer que los datos de una base de datos de MySQL se guarden en un archivo de Excel. Serán 2 tablas: la tabla de clientes y la tabla de productos.

Cada tabla va a guardarse en una hoja distinta del libro, así separamos los datos. Adicionalmente a ello se agregarán encabezados en la primera fila de Excel.

Para demostrar, veamos lo que contiene la base de datos:

Sé que son pocos registros, pero eso no importa. Si hubiera millones de registros igualmente serían exportados. El punto es que al llamar al script y abrir el documento de Excel, el contenido es:

Me he dado a la tarea de subir el documento generado para que puedas comprobarlo por ti mismo, está en mi Google Drive.

Repositorio en GitHub

Puedes explorar el código o hacerle un fork desde el repositorio oficial. Si hay actualizaciones igualmente las pondré ahí.

La base de datos

Voy a usar un fragmento que vimos en el tutorial de PDO para obtener la base de datos de MySQL. Queda así:

<?php

function obtenerBD(){
    # Mira a esquema.sql y también confgura tus credenciales
    # Recomiendo: https://parzibyte.me/blog/2018/02/12/mysql-php-pdo-crud/
    $nombre_base_de_datos = "sistema_ventas";
    $usuario = "root";
    $contraseña = "";
    try {

        $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_OBJ);
        return $base_de_datos;
    } catch (Exception $e) {
        # Nota: ¡en la vida real no imprimas errores!
        exit("Error obteniendo BD: " . $e->getMessage());
        return null;
    }
}

El esquema que usaré es este:

create database if not exists sistema_ventas;
use sistema_ventas;
create table if not exists productos(
    id bigint unsigned not null auto_increment,
    codigo varchar(255) not null,
    descripcion varchar(255) not null,
    precioCompra decimal(9, 2) not null,
    precioVenta decimal(9, 2) not null,
    existencia decimal(9, 2) not null,
    primary key(id)
);

create table if not exists clientes(
    id bigint unsigned not null auto_increment,
    nombre varchar(255) not null,
    correo varchar(255) not null,
    primary key(id)
);

Ya lo dije anteriormente, no importa el número de registros de cada tabla, todos serán exportados.

Las hojas

Por defecto se crea una hoja en el documento y se obtiene con $documento->getActiveSheet(). Si queremos crear una hoja, llamamos a $documento->createSheet() que a su vez devuelve una hoja.

Los títulos de cada hoja se establecen con $hoja->setTitle("el título"). En el ejemplo agrego una hoja para guardar los clientes, y ocupo la primera para los productos.

Escribir encabezado en Excel

La primera fila tendrá la descripción de cada columna. Aquí se introduce un nuevo método que permite poner datos en determinada coordenada a través de un arreglo: $hojaDeCalculo->fromArray() que recibe 3 argumentos.

El primer argumento es un arreglo; que son los datos que se pondrán, el segundo es un filtro que, en caso de ser encontrado dentro del arreglo, no se pondrá dentro de la hoja (en este caso sería null).

El último argumento es la coordenada en donde se colocarán estos datos, se puede omitir pues por defecto es A1 pero lo puse para que cuando alguien lo tome como referencia pueda saber a lo que el campo se refiere. Entonces el código para poner los encabezados queda así:

<?php
# Escribir encabezado de los productos
$encabezado = ["Código de barras", "Descripción", "Precio de compra", "Precio de venta", "Existencia"];
# El último argumento es por defecto A1 pero lo pongo para que se explique mejor
$hojaDeProductos->fromArray($encabezado, null, 'A1');

Lo mismo se hace para la segunda hoja, la de clientes.

Obtener contenido de la base de datos

Se prepara una simple consulta y se recorre con un cursor para mejorar el rendimiento. Queda así:

<?php
$consulta = "select codigo, descripcion, precioCompra, precioVenta, existencia from productos";
$sentencia = $bd->prepare($consulta, [
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
]);
$sentencia->execute();
# Comenzamos en la 2 porque la 1 es del encabezado
$numeroDeFila = 2;
while ($producto = $sentencia->fetchObject()) {
  # Trabajar con $producto aquí :^)
}

Lo mismo se hace con la tabla clientes. Después de eso, en el ciclo while se agregan los datos. La variable llamada $numeroDeFila sirve para llevar un simple contador y saber, valga la redundancia, el número de fila en la que vamos.

Escribir contenido dentro de la hoja de cálculo

Ahora veamos lo que hay dentro del ciclo while para escribir dentro del libro de Excel. Obtenemos los valores de la tabla y luego llamamos a $hojaDeProductos->setCellValueByColumnAndRow() pasándole las coordenadas y el dato.

El código es el siguiente:

<?php
# Obtener los datos de la base de datos
$codigo = $producto->codigo;
$descripcion = $producto->descripcion;
$precioCompra = $producto->precioCompra;
$precioVenta = $producto->precioVenta;
$existencia = $producto->existencia;
# Escribirlos en el documento
$hojaDeProductos->setCellValueByColumnAndRow(1, $numeroDeFila, $codigo);
$hojaDeProductos->setCellValueByColumnAndRow(2, $numeroDeFila, $descripcion);
$hojaDeProductos->setCellValueByColumnAndRow(3, $numeroDeFila, $precioCompra);
$hojaDeProductos->setCellValueByColumnAndRow(4, $numeroDeFila, $precioVenta);
$hojaDeProductos->setCellValueByColumnAndRow(5, $numeroDeFila, $existencia);
$numeroDeFila++;

Así se hace hasta que el ciclo se rompe.

Guardar el documento de Excel

Finalmente se crea un writer y se guarda en el disco duro. Si quieres mandarlo en el navegador para su descarga mira los posts que dejé al inicio, ahí dice cómo.

<?php
# Crear un "escritor"
$writer = new Xlsx($documento);
# Le pasamos la ruta de guardado
$writer->save('Exportado.xlsx');

Con eso tenemos, así de fácil fue exportar dos tablas de MySQL a un documento de Excel.

Poniendo todo junto

Yo sé que sería un pecado explicar todo, dejar los fragmentos de código y terminar el post sin dejar el código completo; por ello es que aquí dejo el script que se encarga de ello:

<?php
/**
 * Ejemplo de cómo usar PDO y PHPSpreadSheet para
 * exportar datos de MySQL a Excel de manera
 * fácil, rápida y segura
 *
 * @author parzibyte
 * @see https://parzibyte.me/blog/2019/02/14/leer-archivo-excel-php-phpspreadsheet/
 * @see https://parzibyte.me/blog/2018/02/12/mysql-php-pdo-crud/
 * @see https://parzibyte.me/blog/2019/02/16/php-pdo-parte-2-iterar-cursor-comprobar-si-elemento-existe/
 * @see https://parzibyte.me/blog/2018/11/08/crear-archivo-excel-php-phpspreadsheet/
 * @see https://parzibyte.me/blog/2018/10/11/sintaxis-corta-array-php/
 *
 */require_once "vendor/autoload.php";

# Nuestra base de datos
require_once "bd.php";

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

# Obtener base de datos
$bd = obtenerBD();

$documento = new Spreadsheet();
$documento
    ->getProperties()
    ->setCreator("Luis Cabrera Benito (parzibyte)")
    ->setLastModifiedBy('Parzibyte')
    ->setTitle('Archivo exportado desde MySQL')
    ->setDescription('Un archivo de Excel exportado desde MySQL por parzibyte');

# Como ya hay una hoja por defecto, la obtenemos, no la creamos
$hojaDeProductos = $documento->getActiveSheet();
$hojaDeProductos->setTitle("Productos");

# Escribir encabezado de los productos
$encabezado = ["Código de barras", "Descripción", "Precio de compra", "Precio de venta", "Existencia"];
# El último argumento es por defecto A1 pero lo pongo para que se explique mejor
$hojaDeProductos->fromArray($encabezado, null, 'A1');

$consulta = "select codigo, descripcion, precioCompra, precioVenta, existencia from productos";
$sentencia = $bd->prepare($consulta, [
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
]);
$sentencia->execute();
# Comenzamos en la 2 porque la 1 es del encabezado
$numeroDeFila = 2;
while ($producto = $sentencia->fetchObject()) {
    # Obtener los datos de la base de datos
    $codigo = $producto->codigo;
    $descripcion = $producto->descripcion;
    $precioCompra = $producto->precioCompra;
    $precioVenta = $producto->precioVenta;
    $existencia = $producto->existencia;
    # Escribirlos en el documento
    $hojaDeProductos->setCellValueByColumnAndRow(1, $numeroDeFila, $codigo);
    $hojaDeProductos->setCellValueByColumnAndRow(2, $numeroDeFila, $descripcion);
    $hojaDeProductos->setCellValueByColumnAndRow(3, $numeroDeFila, $precioCompra);
    $hojaDeProductos->setCellValueByColumnAndRow(4, $numeroDeFila, $precioVenta);
    $hojaDeProductos->setCellValueByColumnAndRow(5, $numeroDeFila, $existencia);
    $numeroDeFila++;
}

# Ahora los clientes
# Ahora sí creamos una nueva hoja
$hojaDeClientes = $documento->createSheet();
$hojaDeClientes->setTitle("Clientes");

# Escribir encabezado
$encabezado = ["Nombre", "Correo electrónico"];
# El último argumento es por defecto A1 pero lo pongo para que se explique mejor
$hojaDeClientes->fromArray($encabezado, null, 'A1');
# Obtener clientes de BD
$consulta = "select nombre, correo from clientes";
$sentencia = $bd->prepare($consulta, [
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
]);
$sentencia->execute();

# Comenzamos en la 2 porque la 1 es del encabezado
$numeroDeFila = 2;
while ($cliente = $sentencia->fetchObject()) {
    # Obtener los datos de la base de datos
    $nombre = $cliente->nombre;
    $correo = $cliente->correo;

    # Escribirlos en el documento
    $hojaDeClientes->setCellValueByColumnAndRow(1, $numeroDeFila, $nombre);
    $hojaDeClientes->setCellValueByColumnAndRow(2, $numeroDeFila, $correo);
    $numeroDeFila++;
}
# Crear un "escritor"
$writer = new Xlsx($documento);
# Le pasamos la ruta de guardado
$writer->save('Exportado.xlsx');

Si lo quieres usar, recuerda primero configurar composer, la librería y todo lo demás que ya he explicado en otros artículos.

También dejo un GIF mostrando el proceso de exportación. Recuerda que este ejemplo puedes ejecutarlo desde el navegador o desde la consola.