Cómo exportar una tabla de MySQL a Excel usando PDO

Seguro que te ha pasado algo como esto: creaste una aplicación con su base de datos, con unas funcionalidades espectaculares, fantásticos reportes y al momento de la demo… la pregunta tan temida:

«¿Cómo puedo hacer para llevar toda esta información a Excel?»

Y en tu cabeza suena:

«¿A Excel? ¿En serio? Pero si se puede hacer todo mucho mejor con esta aplicación… ¿para qué querrías usar una planilla de cálculo?»

Creeme, no estás solo, a todos nos ha pasado.

La triste realidad es que difícilmente vayas a ganar la batalla… más vale amigarte con el Excel (y ganar algo de dinero mientras tanto, ¿no?).

Una de las razones más comunes que se escuchan por ahí es que a través de Excel es fácil compartir la información con otros sistemas (Personalmente elegiría una integración basada en webservices, pero… el cliente manda).

Otra razón que, aunque a veces duela, la veo más entendible es la facilidad para analizar datos que tiene Excel (La posibilidad de aplicar filtros, sumatorias y demás).

Pero como este no es un blog de Excel si no de Programación, pasemos a lo nuestro.

En este artículo te voy a mostrar los pasos que tendrás que dar para generar archivos que puedan ser abiertos con Excel a partir de tu base de datos MySQL.

Encontrarás ejemplos de código que, con pocas modificaciones, podrás adaptar a tu escenario particular.

¡Acompañame!

Conectarse a MySQL usando PDO

Lo primero que necesitarás para realizar esta exportación es conectarte a tu base de datos.

Siendo que se trata de MySQL puedes usar la librería específica (mysqli) o la genérica (PDO).

Personalmente, prefiero ir por PDO porque si eventualmente necesito cambiar de motor de BD es muy fácil hacerlo.

Todo comienza por la creación de un objeto:

<?php

$db = new PDO('mysql:host=localhost;dbname=mydb', 'root', '');

Con este código estoy intentando hacer una conexión a una base de datos MySQL instalada en la misma computadora donde se está corriendo el script.

Digo intentando porque, como siempre, algo podría salir mal… para este ejemplo voy a dejarlo así, pero lo correcto sería incluir este código dentro de un bloque try…catch.

(Si no tenés muy claros los conceptos de objetos, excepciones y/o PDO este libro puede ayudarte 😉

Una vez realizada la conexión debemos hacer alguna consulta que nos permita obtener aquellos registros que queremos exportar.

Realizar una consulta usando PDO

Existen varios modos de realizar la consulta mediante PDO, pero hay algo de lo que no podrás escapar: escribir el SQL correspondiente.

Usualmente lo que yo hago es algo como:

$sql = "SELECT * FROM table WHERE field > 10";

$results = $pdo->query($sql, PDO::FETCH_NUM);

Nuevamente, en un caso real habría que verificar si la consulta se ejecutó o hubo algún error antes de avanzar… para no hacer un post kilométrico lo dejo aquí.

Para darle algo de formato al resultado que vamos a generar vamos a necesitar los nombres de los campos (que, si usamos un SELECT * no siempre serán los mismos!)

Obtener los nombres de los campos usando PDO

$columns = [];

for ($i = 0; $i < $results->columnCount(); $i++) {
    $columns[] = $results->getColumnMeta($i)['name'];
}

Una vez obtenidos los registros y los nombres de las columnas a exportar podemos comenzar a generar la respuesta.

Y aquí tenemos varias opciones.

Independientemente de cuál sea la que elijas para generar el resultado, seguramente quieras forzar la descarga del mismo, veamos cómo lograrlo.

Forzar la descarga de un archivo

Para obligar al navegador a guardar el resultado devuelto por el servidor en un archivo en el disco local del visitante debe usarse la función header:

header("Content-Type:$contentType"); 
header("Content-Disposition:attachment;filename=output.$outputFileExtension"); 

A continuación te mostraré tres opciones diferentes para generar la respuesta.

Las variables $contentType y $outputFileExtension dependerán de cuál de las opciones disponibles quieras utilizar.

Generar un archivo CSV y abrirlo en Excel

Esta es probablemente la manera más sencilla de exportar los resultados, usando un archivo de valores separados por comas (CSV).

header("Content-Type:application/csv"); 
header("Content-Disposition:attachment;filename=output.csv"); 

$outputFile = fopen('php://output', 'w+'));

fputcsv($outputFile, $columns);
foreach ($results as $result) {
        fputcsv($outputFile, $result);
}

Este código permitirá que el usuario reciba un archivo separado por comas que podrá ser abierto usando Excel y eventualmente guardado en formato nativo (xls, xlsx, etc…).

Generar un archivo HTML y abrirlo en Excel

Una característica interesante del Excel (Y algunas otras aplicaciones tipo planilla de cálculo modernas) es que pueden interpretar archivos HTML que tengan estructuras de tablas y mostrarlos como planillas.

En este caso el código de la generación de la salida sería algo como:

header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
header("Content-Disposition:attachment;filename=output.xls"); 

$outputFile = fopen('php://output', 'w+'));

?>
<table>
        <thead>
                <tr>
                <?php foreach($columns as $column): ?>
                        <th><?php echo $column; ?></th>
                <?php endforeach; ?>
                </tr>
        </thead>
        <tbody>
                <?php foreach( $results as $result ): ?>
                <tr>
                        <?php foreach ($result as $value): ?>
                        <td><?php echo $value; ?></td>
                        <?php endforeach; ?>
                </tr>
                <?php endforeach; ?>
        </tbody>
</table>

Generar un archivo Excel usando PHP

Si bien estas dos opciones pueden resultar aceptables en una gran cantidad de casos, existen otros en los que realmente se necesita exportar a un formato específico, como XLSX.

Estos archivos se basan en XML (es decir, en texto) con lo cual podrías generarlos usando simples strings (O a lo sumo valiéndote de SimpleXMLElement) pero… ¿para qué reinventar la rueda no?

Hay una librería realmente buena que podés usar: PhpSpreadsheet.

Esta librería te permite tanto escribir como leer archivos con formato específico para muchas planillas de cálculo (Excel, LibreOffice, etc…).

Una característica que la hace sumamente atractiva es que presenta una API orientada a objetos realmente simple de usar:

$spreadsheet = new Spreadsheet();

$activeSheet = $spreadsheet->getActiveSheet();

foreach($columns as $i => $column) {
        $activeSheet->setCellValueByColumnAndRow($i + 1, 1, $column);
}

foreach($results as $result) {
        $activeSheet->insertNewRowBefore($activeSheet->getHighestRow() + 1);
        foreach ($result as $k => $field) {
                $activeSheet->setCellValueByColumnAndRow($k + 1, $activeSheet->getHighestRow(), $field);
        }
}

$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');

Un detalle importante: para usar esta librería hay que instalarla primero (Preferentemente usando composer).

Qué se puede hacer con Excel usando PHP

Así como es posible exportar información hacia un archivo Excel es perfectamente posible importar información contenida en un archivo Excel hacia una base de datos MySQL o manipular un archivo existente.

La librería PhpSpreadsheet permite realizar prácticamente cualquier operación que Excel soporte, todo lo que se requiere es leer un poco de documentación 😉