Dividir y exportar en varios archivos en Excel con PHP
Dividir y exportar en varios archivos en Excel con PHP. En este artículo abordaremos la exportación de registros en MySQL. Por lo tanto, en algún momento cuando estamos exportando gran cantidad de registros en Excel o CSV, aparece un detalle y es la cantidad enorme de registros que puede contener nuestra tabla.
Ahora, solo pensemos que tenemos dos millones de registros en nuestra tabla. Sin embargo, lo crucial seria si exportamos esa gran cantidad de registros en un solo archivo de Excel.
¿Que haríamos si se presentara tal situación?
La solución inmediata seria lo siguiente:
Debemos agrupar los registros de dicha tabla y dividirlos en varios archivos según el resultado para que sean fáciles de manejar y en cantidades razonables.
Solo recordemos, que un fichero de gran tamaño que supere los 100MB ya es problema para cualquier computadora o laptop, incluso nuestro ordenador puede llegar a colgarse.
Dividir y exportar en varios archivos en Excel con PHP
¿Cómo funciona este sistema para exportar registros divididos?
Para empezar en este artículo, se ha creado un sistema web con un botón que nos permitirá exportar los registros de nuestra base de datos. Por lo tanto, los resultados de la base de datos MySQL se recuperan y se enumeran en una tabla en HTML con la opción de exportación mediante ficheros Excel.
Al presionar el botón exportación desencadenara la acción Ajax que llamara a un fichero PHP y este realizara el listado de la información. Sin embargo, los resultados de la base de datos se separarán por una constante de límite definida por el usuario.
Ahora, mediante el uso de la barrera de límite, los resultados obtenidos se dividen en partes iguales y se descargaran secuencialmente en varios archivos de Excel, que el usuario debe almacenar en una carpeta.
Mostrando lista de resultados de base de datos con el botón de exportación
A continuación, mostraremos un ejemplo de una tabla mostrando registros de la base de datos. Puede integrar la paginación de PHP para mostrar los resultados paginados. Sin embargo, el botón de exportación se mostrará debajo de la tabla.
Al hacer clic en este botón, la acción de exportación se manejará con el script de jQuery y Ajax.
ESTRUCTURA Y RECURSOS QUE USAREMOS
A continuación, veremos una serie de procesos que necesitaremos para nuestro sistema.
Fichero Index.php
Mostraremos nuestro fichero HTML y librerías externas que usaremos en este proceso.
<!doctype html>
<html lang="es">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<title>Dividir y exportar en varios archivos de hoja de Excel mediante php | BaulPHP</title>
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<!-- Bootstrap core CSS -->
<link href="dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Custom styles for this template -->
<link href="assets/sticky-footer-navbar.css" rel="stylesheet">
</head>
<body>
<header>
<!-- Fixed navbar -->
<nav class="navbar navbar-expand-md navbar-dark fixed-top bg-dark"> <a class="navbar-brand" href="#">BaulPHP</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav mr-auto">
<li class="nav-item active"> <a class="nav-link" href="index.php">Inicio <span class="sr-only">(current)</span></a> </li>
</ul>
<form class="form-inline mt-2 mt-md-0">
<input class="form-control mr-sm-2" type="text" placeholder="Buscar" aria-label="Search">
<button class="btn btn-outline-success my-2 my-sm-0" type="submit">Busqueda</button>
</form>
</div>
</nav>
</header>
<!-- Begin page content -->
<div class="container">
<h3 class="mt-5">Dividir y exportar en varios archivos de hoja de Excel</h3>
<hr>
<div class="row">
<div class="col-12 col-md-12">
<!-- Contenido -->
<button class="btn btn-primary" id="btn-export" onClick="getHTMLSplit();">Exportar fichero Excel</button>
<!-- Fin Contenido -->
</div>
</div>
<!-- Fin row -->
</div>
<!-- Fin container -->
<footer class="footer">
<div class="container"> <span class="text-muted">
<p>Códigos <a href="https://www.baulphp.com/" target="_blank">BaulPHP</a></p>
</span> </div>
</footer>
<!-- Bootstrap core JavaScript
================================================== -->
<script src="dist/js/bootstrap.min.js"></script>
<!-- Placed at the end of the document so the pages load faster -->
</body>
</html>
Conexión con MySQL y lectura de la información
Primeramente realizaremos la conexión con la base de datos MySQL a través del fichero DBcontroller.php. Sin embargo, para solicitar la información usaremos la instrucción preparada con mysqli para ejecutar consultas de base de datos. El código php para leer y mostrar el resultado de la base de datos es el siguiente.
Leer la información de la base de datos
Un pequeño código encargado de realizar la consulta SQL.
<?php
// Pedimos un archivo externo para la conexión
require_once ("DBController.php");
$db_handle = new DBController();
// Realizamos la consulta para extraer la información
$consulta = "select * from tbl_personal";
$mi_resultado = $db_handle->runBaseQuery($consulta);
?>
Mostrar la información de la base de datos MySQL
Para tal acción, nos apoyaremos en el ciclo FOREACH del PHP y las tablas del lenguaje HTML5.
<?php
if (! empty($mi_resultado)) {
?>
<table class="table">
<thead class="thead-dark">
<tr>
<th scope="col">#</th>
<th scope="col">Nombres</th>
<th scope="col">Cargo</th>
<th scope="col">Salario</th>
<th scope="col">Edad</th>
</tr>
</thead>
<?php
foreach ($mi_resultado as $k => $v) {
?>
<tr>
<td width="10%"><?php echo $mi_resultado[$k]['id']; ?></td>
<td width="40%"><?php echo $mi_resultado[$k]['nombres']; ?></td>
<td width="30%"><?php echo $mi_resultado[$k]['cargo']; ?></td>
<td width="20%"><?php echo $mi_resultado[$k]['salario']; ?></td>
<td width="20%"><?php echo $mi_resultado[$k]['edad']; ?></td>
</tr>
<?php
}
?>
</table>
<?php
}
?>
Leer datos de la división a través de JQuery y Ajax
Este proceso es el más importante. Por lo tanto, en este código los datos se solicitan desde la base de datos y se dividen en varias tablas HTML. Sin embargo, este proceso se realiza con una llamada AJAX de jQuery por medio del lenguaje PHP.
El Ajax solicita la acción del archivo DividirRegistros.php, para obtener los resultados que serán devueltos en la tabla HTML particionada. La matriz de los resultados de la tabla HTML se codificará en un formato JSON. El callback de éxito de Ajax recibe la respuesta JSON del PHP y ejecuta la acción de exportación de JavaScript simultáneamente.
<script type="text/javascript">
function getHTMLSplit() {
$.ajax({
url: 'DividirRegistros.php',
type: 'POST',
dataType: 'JSON',
data: {record_count:<?php echo count($mi_resultado); ?>},
success:function(response){
exportHTMLSplit(response);
}
});
}
function exportHTMLSplit(response) {
var random = Math.floor(100000 + Math.random() * 900000)
$(response).each(function (index) {
var excelContent = response[index];
var excelFileData = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
excelFileData += "<head>";
excelFileData += "<!--[if gte mso 9]>";
excelFileData += "<xml>";
excelFileData += "<x:ExcelWorkbook>";
excelFileData += "<x:ExcelWorksheets>";
excelFileData += "<x:ExcelWorksheet>";
excelFileData += "<x:Name>";
excelFileData += "{worksheet}";
excelFileData += "</x:Name>";
excelFileData += "<x:WorksheetOptions>";
excelFileData += "<x:DisplayGridlines/>";
excelFileData += "</x:WorksheetOptions>";
excelFileData += "</x:ExcelWorksheet>";
excelFileData += "</x:ExcelWorksheets>";
excelFileData += "</x:ExcelWorkbook>";
excelFileData += "</xml>";
excelFileData += "<![endif]-->";
excelFileData += "</head>";
excelFileData += "<body>";
excelFileData += excelContent;
excelFileData += "</body>";
excelFileData += "</html>";
var sourceHTML = excelFileData + response[index];
var source = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(sourceHTML);
var fileDownload = document.createElement("a");
document.body.appendChild(fileDownload);
fileDownload.href = source;
fileDownload.download = "Hoja_" + random + '_'+(index+1)+'.xls';
fileDownload.click();
document.body.removeChild(fileDownload);
})
}
</script>
Fichero DividirRegistros.php
Este código PHP establece los registros máximos permitidos por archivo de Excel con una constante de límite que podemos personalizar de acuerdo a nuestras necesidades. Basándose en estas constantes, el conteo de iteraciones del resultado de la base de datos y el conteo de resultados de tabla HTML serán variados según su personalización.
<?php
require_once ("DBController.php");
$db_handle = new DBController();
define("RECORD_LIMIT_PER_FILE", 4);
$start = 0;
$rowcount = $_POST["record_count"];
$lastPageNo = ceil($rowcount / RECORD_LIMIT_PER_FILE);
for ($i = $start; $i < $lastPageNo; $i ++) {
$query = " SELECT * FROM tbl_personal limit " . $start . " , " . RECORD_LIMIT_PER_FILE;
$result = $db_handle->runBaseQuery($query);
$splitHTML[$i] = '<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Nombres</th>
<th>Cargo</th>
<th>Salario</th>
<th>Edad</th>
</tr>
</thead>';
foreach ($result as $k => $v) {
$splitHTML[$i] .= '<tr>
<td width="10%">' . $result[$k]['id'] . '</td>
<td width="40%">' . $result[$k]['nombres'] . '</td>
<td width="30%">' . $result[$k]['cargo'] . '</td>
<td width="20%">' . $result[$k]['salario'] . '</td>
<td width="20%">' . $result[$k]['edad'] . '</td>
</tr>';
}
$splitHTML[$i] .= '</table>';
$start = $start + RECORD_LIMIT_PER_FILE;
}
print json_encode($splitHTML);
?>
DBController.php
Fichero importante que realiza la conexión con el servidor de base de datos MySQL. Por lo tanto, este es el controlador de base de datos para preparar la sentencia Query con mysqli para manejar el acceso a la base de datos desde PHP.
<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "root";
private $database = "php_splitexcel";
private $conn;
function __construct() {
$this->conn = $this->connectDB();
}
function connectDB() {
$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
return $conn;
}
function runBaseQuery($query) {
$result = $this->conn->query($query);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
return $resultset;
}
function runQuery($query, $param_type, $param_value_array) {
$sql = $this->conn->prepare($query);
$this->bindQueryParams($sql, $param_type, $param_value_array);
$sql->execute();
$result = $sql->get_result();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
if(!empty($resultset)) {
return $resultset;
}
}
function bindQueryParams($sql, $param_type, $param_value_array) {
$param_value_reference[] = & $param_type;
for($i=0; $i<count($param_value_array); $i++) {
$param_value_reference[] = & $param_value_array[$i];
}
call_user_func_array(array(
$sql,
'bind_param'
), $param_value_reference);
}
}
?>
CONCLUSIÓN
- Con el avance de la tecnología que hoy en día se da a pasos agigantados gracias al internet, podemos deducir que el uso de estas herramientas disponibles en internet es muy imprescindibles para cualquier sistema que maneja exportación de información.
- Si bien, la exportación de información se da la gran problemática era el tamaño del fichero exportado. Sin embargo, gracias a esta pequeño script podemos solucionar esta problemática rápidamente.
- Su uso es muy recomendable.
DESCARGA
Les dejare un fichero comprimido para que puedan descargarlo e implementarlo en sus sistemas web.
[sociallocker id=5099] Descargar [/sociallocker]
