Exportar datos a Excel con PHP

Cuando queremos que el usuario pueda exportar los datos desde una aplicación web para poder abrirlos como una hoja de cálculo lo más recomendable es hacerlo en formato CSV (Comma-Separted Values) Ahora bien, hay casos en que el cliente insiste en que desea que se le abra inmediatamente su Microsoft Excel al hacer click sobre el link o botón. En estos casos, cuando generamos el fichero “al vuelo”, la cabecera debe ser:

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

En vez de:

header('Content-Type: text/csv'; charset=utf-8);

Si los datos contienen caracteres más allá de los primeros 128 ASCII, por ejemplo acentos, al abrirse Excel veremos que estos caracteres no se visualizan correctamente. ¿Por qué? Aunque UTF-8 es de lejos la codificación más usada en la web, y lo más probable es que tu aplicación trabajé con ella, Microsoft Excel sorprendentemente no trabaja con este formato. Los de Redmond pueden haber perdido parte de su poder pero no su idiosincrasia. ¿Cómo arreglar este desaguisado? Pasando los datos a UTF-16LE (Lower Endian) e indicando la codificación en la cabecera, pues con esta codificación sí trabajan. Para lograrlo, PHP dispone de una función para convertir codificaciones: mb_convert_encoding()

El siguiente código de ejemplo deshace el entuerto. En $participations
tenemos los datos a exportar, es un array. En este caso concreto, el código pertenece a una vista y por lo tanto el array ha llegado desde un controlador. El array $header contiene el nombre de cada columna para la hoja de cálculo y $fields contiene los índices de $participations, que coinciden con los nombres de los campos en la base de datos.

<?php

$participations = $render['main']['participations'];
$header = ['NOMBRE1', 'NOMBRE2', 'NOMBRE3', 'NOMBRE4', 'NOMBRE5'];
$fields = ['campo1', 'campo2', 'campo3', 'campo4', 'campo5'];

header("Content-Type: application/vnd.ms-excel; charset=UTF-16LE");
header('Content-Disposition: attachment; filename=participantes_' . makeUrlPhrase($render['main']['bla']['bla']) . '.xls');
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);

$result = implode("\t", $header);
$result .= "\r\n";
if (!empty($participations)):
 foreach ($participations as $part):
  foreach ($fields as $iField):
   $result .= $part[$iField] . "\t";
  endforeach;
  $result .= "\r\n";
 endforeach;
endif;

echo mb_convert_encoding($result, 'UTF-16LE', 'UTF-8');

Como está haciendo servir el tabulador \t para indicar a Excel separación entre campos y el salto de línea \r\n como separación de registros, si creemos que en los datos puede existir alguno de estos caracteres especiales, deberemos escaparlos con la función preg_replace() De lo contrario la hoja no se importará correctamente.

Excel, fuente inagotable de virtudes, tratará campos como fechas, números de teléfono o códigos postales y los procesará para dejarlos inútiles. Por ejemplo, los códigos postales de Barcelona empiezan por cero: 08080. Excel tratará ese dato como un número entero y lo dejará en 8080. Para evitarlo hay que entrecomillar estos datos y una vez más la función preg_replace() acude a nuestro rescate:

if(preg_match("/^0/", $result) || preg_match("/^\+?\d{8,}$/", $result) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $result)) {
  $result = "'$result";
}

Con todo esto conseguiremos que al hacer click el usuario sobre un link, se descargará un fichero que se abrirá automáticamente en Excel y en el que los datos aparecerán correctamente.

En el caso de necesitar más funcionalidades, como por ejemplo añadir gráficas o imágenes a la hoja de cálculo, acabaremos antes si usamos una librería como PhpSpreadsheet en vez de usar nuestro propio código. En caso contrario, si se trata de una mera exportación de datos, implementar la solución con nuestro propio código, teniendo en cuenta lo aquí explicado, será la forma más productiva de proceder.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.