Importar archivo Excel a MySQL con PHPSpreadsheet

Importar archivo Excel a MySQL. En el presente artículo vamos a implementar un script usando una librería muy potente con base de actualización periódica lo que nos garantiza una amplia documentación.

Las copias de seguridad y restauración de bases de datos o la importación de registros en masa suele ser la parte más importante y es una herramienta imprescindible de cada sistema web profesional.

En caso de desastre de seguridad que puede colapsar su sistema. Sin embargo, en una situación tan desafortunada, será una gran salvación si tienes un respaldo en cualquier formato de archivo.

Recordemos que los archivos de copia de seguridad pueden estar en cualquier formato. Es posible que vea que muchos de los clientes de base de datos admiten archivos de formato SQL, Excel o CSV para importar datos externos hacia la base de datos.

Importar archivo Excel a MySQL con PHPSpreadsheet

Vamos a crear una base de datos para importar datos de Excel usando el presente ejemplo.

Restaurar la copia de seguridad de Excel en una base de datos a través de la programación nos ahorrará tiempo. Por lo tanto, vamos a implementar una restauración rápida mediante la importación de datos masivos de archivos de Excel.

Si está buscando el ejemplo de exportación, puede visitar mis datos de exportación en formato EXCEL.

Salida: Importación de Excel y listado de datos

Vista en la tabla MySQL

¿Cómo funciona el presente ejemplo de importación de Excel?

Nos apoyaremos en un formulario para cargar el fichero Excel. Usaremos una plantilla de Excel de ejemplo con este recurso en la ZIP descargable que dejare el final del artículo.

Con un archivo válido y sus campos definidos, el envío del formulario HTML llamará a PHP para procesar el fichero de Excel.

En PHP, la biblioteca PHPSpreadSheet se utiliza para leer los datos del archivo de Excel y convertirlos en una matriz.

Al iterar los datos de la matriz, el código se ejecutará e insertara fila por fila a la base de datos.

Estructura de archivos

A continuación, mostrare la estructura de archivos del ejemplo de importación de Excel. La carpeta del script tiene la biblioteca PHPSpreadsheet y depende de ella.

En la carpeta «Ejemplo» existe un ejemplo de archivo de Excel para probar la operación de importación.

El fichero b_importar_excel.sql tiene el script para crear la base de datos de destino.

Script de base de datos

CREATE TABLE `tbl_productos` (
`id` int(11) NOT NULL,
`producto` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`descripcion` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`fecha` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indices de la tabla `tbl_productos`
--
ALTER TABLE `tbl_productos`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT de la tabla `tbl_productos`
--
ALTER TABLE `tbl_productos`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Creación de la interfaz de usuario con formulario

<main>
<div class="container py-4">
<header class="pb-3 mb-4 border-bottom"> <a href="/" class="d-flex align-items-center text-dark text-decoration-none"> <span class="fs-4">Importar archivo Excel a MySQL con PHPSpreadsheet</span> </a> </header>
<div class="row align-items-md-stretch">
<div class="col-md-6">
<form class="row g-3" action="" method="post" name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">
<div class="col-md-9">
<input class="form-control form-control-sm" id="file" name="file" type="file" accept=".xls,.xlsx">
</div>
<div class="col-md-3">
<button type="submit" id="submit" name="importar" class="btn btn-primary btn-sm mb-3">Importar</button>
</div>
</form>
</div>
</div>
<div class="row align-items-md-stretch">
<div class="col-md-6">
<div id="respuesta" class="alert alert-<?php if(!empty($type)) { echo $type . " display-block"; } ?>">
<?php if(!empty($message)) { echo $message; } ?>
</div>
</div>
</div>
<div class="row align-items-md-stretch">
<div class="col-md-12">
<?php
$sqlSelect = "SELECT * FROM tbl_productos";
$result = $db->select($sqlSelect);
if (! empty($result)) {
?>
<table class="table table-sm table-striped">
<thead>
<tr>
<th scope="col">Producto</th>
<th scope="col">Descripción</th>
</tr>
</thead>
<tbody>
<?php
foreach ($result as $row) {
?>
<tr>
<td scope="row"><?php echo $row['producto']; ?></td>
<td><?php echo $row['descripcion']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
}
?>
</div>
</div>

<!--End contenidos-->
<footer class="pt-3 mt-4 text-muted border-top"> &copy; <?=date("Y");?> </footer>
</div>
</main>

Analizar datos de Excel usando PHPSpreadsheet

Con la validación correcta, el análisis de archivos de Excel lee y convierte los datos en una matriz. Cada índice de matriz tiene una matriz de datos de fila. Después de leer los datos de fila no vacíos, ejecuto la inserción de la base de datos y muestra la respuesta encima del formulario ya sea verdadera o falso.

<?php
use Baulphp\DBClass;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

require_once 'DBClass.php';
$db = new DBClass();
$conexion = $db->getConnection();
require_once ('./vendor/autoload.php');

if (isset($_POST["importar"])) {

$allowedFileType = [
'application/vnd.ms-excel',
'text/xls',
'text/xlsx',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
];

if (in_array($_FILES["file"]["type"], $allowedFileType)) {

$targetPath = 'subidas/' . $_FILES['file']['name'];
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

$Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

$spreadSheet = $Reader->load($targetPath);
$excelSheet = $spreadSheet->getActiveSheet();
$spreadSheetAry = $excelSheet->toArray();
$sheetCount = count($spreadSheetAry);

for ($i = 0; $i <= $sheetCount; $i ++) {
$producto = "";
if (isset($spreadSheetAry[$i][0])) {
$producto = mysqli_real_escape_string($conexion, $spreadSheetAry[$i][0]);
}
$descripcion = "";
if (isset($spreadSheetAry[$i][1])) {
$descripcion = mysqli_real_escape_string($conexion, $spreadSheetAry[$i][1]);
}

if (! empty($producto) || ! empty($descripcion)) {
$query = "insert into tbl_productos(producto,descripcion) values(?,?)";
$paramType = "ss";
$paramArray = array(
$producto,
$descripcion
);
$insertId = $db->insert($query, $paramType, $paramArray);

if (! empty($insertId)) {
$type = "success";
$message = "Datos de Excel importados a la base de datos";
} else {
$type = "danger";
$message = "Problema al importar datos de Excel";
}
}
}
} else {
$type = "danger";
$message = "Tipo de archivo invalido. Cargar archivo de Excel.";
}
}
?>

Instalación del script de importación a MySQL

Use Composer para instalar PHPSpreadsheet en su proyecto:

composer require phpoffice/phpspreadsheet

Salida en la consola con la descarga exitosa de la libreria PhpSpreadsheet

Nota: Al ejecutar composer se creara una carpeta llamada «vendor». La documentacion de la libreria aquí 

Esta captura de pantalla muestra la salida mostrando la lista de filas importadas de la base de datos.

Generar Excel con PhpSpreadsheet y PHP & MySQL

Conclusión

La implementación de esta herramienta para nuestros sistemas es imprescindible y en este artículo hemos aprendido como implementarlo.

Hemos usado la librería PhpSpreadsheet que en la actualidad es la más recomendada para estos trabajos.
Además, hemos usado el framework BootStrap5 para darle estilos al ejemplo

Espero que esta breve explicación les ayude a implementar esta estupenda herramienta en vuestros proyectos.

Descarga del Script