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í:

See the gist on github.

El esquema que usaré es este:

See the gist on github.

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í:

See the gist on github.

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í:

See the gist on github.

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:

See the gist on github.

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.

See the gist on github.

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:

See the gist on github.

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.