Cómo exportar datos a Excel utilizando PHP y MySQL

16 noviembre, 2017

No es raro encontrarnos páginas web que muestran tablas con datos sobre determinados temas y que ofrecen a los usuarios la posibilidad de descargarse esa información en formato Excel para que los puedan ver sin tener que estar conectados a la red. Si estáis interesados en saber cómo hacer esto para vuestra web, hoy os explicaremos los pasos a seguir para exportar los datos almacenados en una base de datos MySQL y crear un archivo Excel utilizando el lenguaje PHP.

Aunque hay librerías desarrolladas en PHP que podemos utilizar para realizar la exportación de datos a Excel, en nuestro ejemplo no utilizaremos ninguna de ella, consiguiendo de esta forma un código más limpio y sencillo de entender.

Descargar White Paper: ‘Cómo exportar datos a Excel utilizando PHP y MySQL’

Creación de la base de datos

Lo primero que haremos, será crearnos la tabla de ejemplo donde tendremos almacenada la información que vamos a exportar a Excel. En nuestro caso será una tabla sencilla de libros que formaría parte de una base de datos de nombre “biblioteca”. A la hora de la verdad, esta tabla puede ser todo lo compleja que necesitéis.

CREATE TABLE `libros` (

`titulo` varchar(255) NOT NULL,

`isbn` varchar(255) NOT NULL,

`autor` varchar(255) NOT NULL,

`editorial` varchar(255) NOT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Código PHP para obtener los datos

Una vez creada la base de datos, será el turno de crear el código que se encargue de devolvernos toda la información almacenada en ella.

$conexion = mysqli_connect (“localhost”, “user”, “pass”);

mysqli_select_db ($conexion, “biblioteca”);

$sql = “SELECT * FROM libros”;

$resultado = mysqli_query ($conexion, $sql) or die (mysql_error ());

$libros = array();

while( $rows = mysqli_fetch_assoc($resultado) ) {

$libros[] = $rows;

}

mysqli_close($conexion);

Con las dos primeras líneas del código anterior, lo que hemos hecho ha sido establecer la conexión con nuestra base de datos y que podamos ejecutar las instrucciones para recuperar la información.

$conexion = mysqli_connect (“localhost”, “user”, “pass”);

mysqli_select_db ($conexion, “biblioteca”);

A continuación hemos creado la sentencia SQL que se encargará de recuperar la información. En nuestro ejemplo, recuperamos todos los registros almacenados en la tabla “libros”.

$sql = “SELECT * FROM libros”;

Una vez escrita la instrucción SQL, es el momento de ejecutar la instrucción. Para ello, utilizamos la función de PHP “mysql_query” a la que se le pasa la variable que almacena la instrucción SQL y el identificador de la conexión que hemos establecido en las primeras líneas.

$resultado = mysqli_query ($conexion, $sql) or die (mysql_error ());

Lo siguiente que hacemos es declararnos un array vacío al que hemos llamado “libros” y donde meteremos todos los registros que hemos recuperado de la base de datos. Esto lo hacemos mediante un bucle “while” y la llamada a la función de PHP “mysqli_fetch_assoc()” en cada iteración. A cada paso, esa función nos devolverá un registro que guardaremos en el array “libros” para su procesamiento posterior.

$libros = array();

while( $rows = mysqli_fetch_assoc($resultado) ) {

$libros[] = $rows;

}

Por último, cerramos la conexión para liberar recursos en el servidor.

mysqli_close($conexion);

Código HTML para mostrar los registros y el botón exportar

Una vez creada la base de datos, será hora de crear el código que nos mostrará los datos a exportar dentro de una tabla.

En nuestra pantalla también colocaremos un botón “Exportar a Excel” para que se lance la descarga del documento con los datos.

<h2>Exportar datos a Excel con PHP y MySQL</h2>

<form action=“ <?php echo $_SERVER[“PHP_SELF”]; ?>“ method=“post”>

<button type=“submit” id=“export_data” name=‘export_data’ value=“Export to excel” class=“btn btn-info”>Exportar a Excel</button>

</form>

</div>

<tr>

<th>Titulo</th>

<th>Autor</th>

<th>Editorial</th>

</tr>

<tbody>

<?php foreach($libros as $libro) { ?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

</div>

Este código no es más que una tabla hecha en HTML que iremos rellenando con los datos almacenados en el array “libros” que hemos visto en el punto anterior. Esto lo hacemos utilizando el bucle “foreach”. A cada pasada se irá pintando una nueva línea con los datos almacenados en la base de datos.

<?php foreach($libros as $libro) { ?>

<tr>

</tr>

<?php } ?>

Exportar datos a Excel

Por último nos faltaría de ver el código encargado de generar el Excel con todos los datos. Este código sólo se ejecutará cuando se pulse el botón de “Exportar a Excel” que hemos visto en el punto anterior.

if(isset($_POST[“export_data”])) {

if(!empty($libros)) {

$filename = “libros.xls”;

header(“Content-Type: application/vnd.ms-excel”);

header(“Content-Disposition: attachment; filename=”.$filename);

$mostrar_columnas = false;

foreach($libros as $libro) {

if(!$mostrar_columnas) {

echo implode(“\t”, array_keys($libro)) . “\n”;

$mostrar_columnas = true;

}

echo implode(“\t”, array_values($libro)) . “\n”;

}

}else{

echo ‘No hay datos a exportar’;

}

exit;

}

Lo primero que hacemos en este código es comprobar que se ha pulsado el botón. Esto lo sabremos si en la variable “$_POST” viene el nombre del botón al que le hemos llamado “export_data”.

if(isset($_POST[“export_data”]))

También comprobaremos que el array “libros” no está vacío. Si lo está mostraremos un mensaje indicando que no hay datos a mostrar.

if(!empty($libros)) {

……

}else{

echo ‘No hay datos a exportar’;

}

Si se ha pulsado el botón y hay datos para exportar, lo siguiente que haremos será indicar el nombre del archivo Excel que tendrá el fichero que vamos a generar.

$filename = “libros.xls”;

A continuación tendremos que indicar las cabeceras necesarias para generar el archivo Excel, incluyendo el nombre del fichero generado.

header(“Content-Type: application/vnd.ms-excel”);

header(“Content-Disposition: attachment; filename=”$filename””);

Tras estas líneas, hemos declarado la variable “$mostrar_columnas” asignándole un valor “false”. Esto nos servirá para saber si ya hemos pintado los nombres de las columnas.

$mostrar_columnas = false;

A continuación pasaría a ejecutarse el bucle “foreach” que recorrería el array de libros.

foreach($libros as $libro) {

….

}

Aquí lo primero que se comprueba es si el valor de variable “$mostrar_columna” es “false”. Si es así, entonces lo que hacemos es un “implode” del “array_keys($libro)”. La función “implode” de PHP, lo que hace es unir los elementos de un array en una cadena mediante un elemento de unión que se le pasa como primer parámetro. En nuestro caso, se le pasa el valor del tabulador (\t). A continuación, se cambia el valor de la variable “$mostrar_columna” a “true” para que no pinte más los nombres de las columnas.

if(!$mostrar_columnas) {

echo implode(“\t”, array_keys($libro)) . “\n”;

$mostrar_columnas = true;

}

En cada pasada del bucle, se ejecuta otro “implode” pero esta vez de los valores que devuelve la función “array_values($libro)” de PHP y que corresponden con la información de los libros.

echo implode(“\t”, array_values($libro)) . “\n”;

Para que nuestro Excel se genere de forma correcta, es necesario incluir al final la instrucción “exit” de PHP.

Aunque a lo largo de nuestro White Paper hemos visto un ejemplo sencillo de exportación de datos almacenados en una base de datos a un archivo Excel, nos puede servir como base para utilizarlos en nuestros proyectos.