Skip to content

Instantly share code, notes, and snippets.

@eamexicano
Last active March 2, 2016 19:03
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save eamexicano/054a0a97cf1590cdcccd to your computer and use it in GitHub Desktop.
Save eamexicano/054a0a97cf1590cdcccd to your computer and use it in GitHub Desktop.
Exporta los datos de MySQL a XLS incluyendo el nombre de los campos
<?php
/**
* The MIT License (MIT)
* Copyright (c) 2016 Emmanuel Ayala Mexicano @eamexicano
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*
*
* MySQL2XLS te permite exportar los registros de una tabla - incluyendo los nombres de los campos - como xls.
* Ingresa los datos de conexión a tu DBMS (MySQL) y elige el nombre de la tabla con los datos que quieres exportar.
* Si lo quieres hacer un poco más dinámico puedes asignar el nombre de la tabla con un parámetro y / o puedes
* modificar la consulta para exportar registros específicos.
*
*
*/
mb_internal_encoding('UTF-8');
mb_http_output('UTF-8');
mb_http_input('UTF-8');
mb_language('uni');
mb_regex_encoding('UTF-8');
ob_start('mb_output_handler');
define('DBNAME', '');
define('DBHOST', '');
define('DBUSER', '');
define('DBPASS', '');
$tabla = "";
$conexion = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);
if (mysqli_connect_errno()) {
echo "Error de conexión: " . mysqli_connect_error();
exit();
}
$conexion->query("SET NAMES utf8mb4");
$conexion->query("SET CHARACTER SET utf8mb4");
$header = "";
$data = "";
$query = "SELECT * FROM $tabla";
$result = $conexion->query($query);
$fields = mysqli_num_fields($result);
for ($i = 0;$i < $fields; $i++) {
$header .= mysqli_fetch_field_direct($result, $i)->name . "\t";
}
while($row = $result->fetch_row()){
$line = '';
foreach ($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = '" ' . $value . ' "' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
if ($data == "") {
$data = "\nNo hay registros en la base de datos.\n";
}
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=contactos.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", "$header\n$data");
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment