Importar datos de Excel a base de datos de MySQL usando PHP

Ya vimos cómo exportar datos de MySQL a un libro de Excel. Ahora haremos el proceso inverso: a partir de un archivo de Excel leer algunas hojas y poner sus datos dentro de nuestra base de datos de MySQL.

Digo muy en serio que vamos a hacer el proceso inverso, porque usaré la misma base de datos que en el post anterior así que recomiendo leerlo antes.

Descripción del proyecto

Vamos a leer un archivo de Excel con el lenguaje de programación PHP y una librería llamada PHPSpreadSheet; leeremos las hojas del archivo (que deben seguir un estilo) e insertaremos cada fila.

La estructura de la base de datos es esta:

create database if not exists sistema_ventas;
use sistema_ventas;
create table if not exists productos(
    id bigint unsigned not null auto_increment,
    codigo varchar(255) not null,
    descripcion varchar(255) not null,
    precioCompra decimal(9, 2) not null,
    precioVenta decimal(9, 2) not null,
    existencia decimal(9, 2) not null,
    primary key(id)
);

create table if not exists clientes(
    id bigint unsigned not null auto_increment,
    nombre varchar(255) not null,
    correo varchar(255) not null,
    primary key(id)
);

El documento que vamos a importar es el que se ve en la captura, y que puedes analizar a gusto en mi Google Drive.

Sí sí, ya sé que se parece muchísimo al que hicimos en el de exportar los datos, pero el punto no es ese. Además, aquí está formateado y creado con el software propietario de Microsoft.

Nota: ya sé que la existencia no se debería formatear como dinero, se me pasó esa parte pero igualmente no afecta al post.

Repositorio en GitHub

Puedes explorar el código o hacerle un fork desde el repositorio oficial. Si hay actualizaciones igualmente las pondré ahí.

Posts que recomiendo que leas

Aquí suponemos que el archivo está en la ruta en donde se ejecuta el script, pero si deseas que el usuario proporcione el Excel (yo no lo recomendaría, pero allá tú, sobre todo por eso de la seguridad) mira cómo subir un archivo con PHP.

También dejo la lista de posts que de alguna manera son la base de esto que expongo, o tienen alguna relación:

• Leer archivo de Excel con PHPSpreadSheet y PHP
• Conectar PHP y MySQL
• Parte 2 de PHP con MySQL: cursores y comprobar si valor existe
• Crear archivo de Excel con PHPSpreadSheet y PHP
• Sintaxis corta de Arrays en PHP

Conectar a la base de datos

Usaré el fragmento que se ve en el tutorial de MySQL:

<?php

function obtenerBD(){
    # Mira a esquema.sql y también confgura tus credenciales
    # Recomiendo: https://parzibyte.me/blog/2018/02/12/mysql-php-pdo-crud/
    $nombre_base_de_datos = "sistema_ventas";
    $usuario = "root";
    $contraseña = "";
    try {

        $base_de_datos = new PDO('mysql:host=localhost;dbname=' . $nombre_base_de_datos, $usuario, $contraseña);
        $base_de_datos->query("set names utf8;");
        $base_de_datos->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $base_de_datos->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $base_de_datos->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
        return $base_de_datos;
    } catch (Exception $e) {
        # Nota: ¡en la vida real no imprimas errores!
        exit("Error obteniendo BD: " . $e->getMessage());
        return null;
    }
}

Vamos a hacer múltiples inserts, así que la sentencia de PDO será preparada antes del ciclo. También se hará uso de las transacciones para que el rendimiento se vea mejorado.

<?php
# Preparar base de datos para que los inserts sean rápidos
$bd->beginTransaction();

# Preparar sentencia de productos
$sentencia = $bd->prepare("insert into productos
(codigo, descripcion, precioCompra, precioVenta, existencia) values
(?, ?, ? ,?, ? )");

Abrir archivo y acceder a las hojas

El archivo de Excel se abre con el siguiente código:

<?php
# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;

# Obtener conexión o salir en caso de error, mira bd.php
$bd = obtenerBD();

# El archivo a importar
# Recomiendo poner la ruta absoluta si no está junto al script
$rutaArchivo = "Ventas.xlsx";
$documento = IOFactory::load($rutaArchivo);

Para obtener determinada hoja se accede a ella a través de $documento->getSheet($numero). En este caso la primer hoja guarda los productos y la segunda los clientes.

Leer el contenido de cada hoja

Una vez que tenemos la hoja, podemos leer todo su contenido e ir insertando cada valor en la base de datos.

<?php
# Calcular el máximo valor de la fila como entero, es decir, el
# límite de nuestro ciclo
$numeroMayorDeFila = $hojaDeProductos->getHighestRow(); // Numérico
$letraMayorDeColumna = $hojaDeProductos->getHighestColumn(); // Letra
# Convertir la letra al número de columna correspondiente
$numeroMayorDeColumna = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($letraMayorDeColumna);

// Recorrer filas; comenzar en la fila 2 porque omitimos el encabezado
for ($indiceFila = 2; $indiceFila <= $numeroMayorDeFila; $indiceFila++) {
  # Aquí obtener el valor con:
  $hojaDeProductos->getCellByColumnAndRow(1, $indiceFila);
}

Ya dentro del ciclo podemos obtener el valor dependiendo de la fila en la que vamos, obteniendo cada columna por separado. Ahí mismo ejecutamos la sentencia y le pasamos los valores; esto es para prevenir inyecciones SQL.

<?php
# Las columnas están en este orden:
# Código de barras, Descripción, Precio de Compra, Precio de Venta, Existencia
$codigoDeBarras = $hojaDeProductos->getCellByColumnAndRow(1, $indiceFila);
$descripcion = $hojaDeProductos->getCellByColumnAndRow(2, $indiceFila);
$precioCompra = $hojaDeProductos->getCellByColumnAndRow(3, $indiceFila);
$precioVenta = $hojaDeProductos->getCellByColumnAndRow(4, $indiceFila);
$existencia = $hojaDeProductos->getCellByColumnAndRow(5, $indiceFila);
$sentencia->execute([$codigoDeBarras, $descripcion, $precioCompra, $precioVenta, $existencia]);

Cuando el ciclo termine (bueno, cuando los ciclos para importar ambas hojas terminen) hacemos el commit de la base de datos. Este paso es muy importante, puesto que si no, todo lo que insertamos no se guardará.

$bd->commit();

Con eso aseguramos que los insert sean rápidos, sin afectar el rendimiento ni la seguridad. Oh, por cierto, comenzamos en la fila 2 porque la 1 tiene el encabezado.

Poniendo todo junto

Una vez que ya explicamos paso por paso el código para copiar datos de Excel a MySQL es hora de ver cómo queda el script de PHP final:

<?php
/**
 * Ejemplo de cómo usar PDO y PHPSpreadSheet para
 * importar datos de Excel a MySQL de manera
 * fácil, rápida y segura
 *
 * @author parzibyte
 * @see https://parzibyte.me/blog/2019/02/14/leer-archivo-excel-php-phpspreadsheet/
 * @see https://parzibyte.me/blog/2018/02/12/mysql-php-pdo-crud/
 * @see https://parzibyte.me/blog/2019/02/16/php-pdo-parte-2-iterar-cursor-comprobar-si-elemento-existe/
 * @see https://parzibyte.me/blog/2018/11/08/crear-archivo-excel-php-phpspreadsheet/
 * @see https://parzibyte.me/blog/2018/10/11/sintaxis-corta-array-php/
 *
 */
# Cargar clases instaladas por Composer
require_once "vendor/autoload.php";

# Nuestra base de datos
require_once "bd.php";

# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;

# Obtener conexión o salir en caso de error, mira bd.php
$bd = obtenerBD();

# El archivo a importar
# Recomiendo poner la ruta absoluta si no está junto al script
$rutaArchivo = "Ventas.xlsx";
$documento = IOFactory::load($rutaArchivo);

# Se espera que en la primera hoja estén los productos
$hojaDeProductos = $documento->getSheet(0);

# Preparar base de datos para que los inserts sean rápidos
$bd->beginTransaction();

# Preparar sentencia de productos
$sentencia = $bd->prepare("insert into productos
(codigo, descripcion, precioCompra, precioVenta, existencia) values
(?, ?, ? ,?, ? )");

# Calcular el máximo valor de la fila como entero, es decir, el
# límite de nuestro ciclo
$numeroMayorDeFila = $hojaDeProductos->getHighestRow(); // Numérico
$letraMayorDeColumna = $hojaDeProductos->getHighestColumn(); // Letra
# Convertir la letra al número de columna correspondiente
$numeroMayorDeColumna = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($letraMayorDeColumna);

// Recorrer filas; comenzar en la fila 2 porque omitimos el encabezado
for ($indiceFila = 2; $indiceFila <= $numeroMayorDeFila; $indiceFila++) {

    # Las columnas están en este orden:
    # Código de barras, Descripción, Precio de Compra, Precio de Venta, Existencia
    $codigoDeBarras = $hojaDeProductos->getCellByColumnAndRow(1, $indiceFila);
    $descripcion = $hojaDeProductos->getCellByColumnAndRow(2, $indiceFila);
    $precioCompra = $hojaDeProductos->getCellByColumnAndRow(3, $indiceFila);
    $precioVenta = $hojaDeProductos->getCellByColumnAndRow(4, $indiceFila);
    $existencia = $hojaDeProductos->getCellByColumnAndRow(5, $indiceFila);
    $sentencia->execute([$codigoDeBarras, $descripcion, $precioCompra, $precioVenta, $existencia]);
}

# Ahora vamos con los clientes
$sentencia = $bd->prepare("insert into clientes
(nombre, correo) values (?, ?)");

# Se espera que en la segunda hoja estén los clientes
$hojaDeClientes = $documento->getSheet(1);

# Calcular el máximo valor de la fila como entero, es decir, el
# límite de nuestro ciclo
$numeroMayorDeFila = $hojaDeClientes->getHighestRow(); // Numérico
$letraMayorDeColumna = $hojaDeClientes->getHighestColumn(); // Letra
# Convertir la letra al número de columna correspondiente
$numeroMayorDeColumna = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($letraMayorDeColumna);

// Recorrer filas; comenzar en la fila 2 porque omitimos el encabezado
for ($indiceFila = 2; $indiceFila <= $numeroMayorDeFila; $indiceFila++) {

    # Las columnas están en este orden:
    # Nombre, Correo electrónico
    $nombre = $hojaDeClientes->getCellByColumnAndRow(1, $indiceFila);
    $correoElectronico = $hojaDeClientes->getCellByColumnAndRow(2, $indiceFila);
    $sentencia->execute([$nombre, $correoElectronico]);
}

# Hacer commit para guardar cambios de la base de datos
$bd->commit();

El script de la base de datos lo dejé más arriba. Recuerda que la parte de la instalación de PHPSpreadSheet está en el post que dejé arriba.

Demostración: importar desde Excel a MySQL

Adjunto una imagen GIF para demostrar que el proceso realmente funciona.

¿Por qué no importar cualquier archivo?

Si te das cuenta, estamos dependiendo mucho de la estructura del archivo Excel. Es decir, no leemos todas las hojas ni importamos a ciegas.

Esto es porque no se debería confiar en datos proporcionados por usuario, si dejamos que importe cualquier número de hojas que se convierten a tablas podríamos tener una seria vulnerabilidad.

Conclusiones

Podríamos hacer miles de cosas más con PHPSpreadSheet, espero tener el tiempo para escribir más sobre el tema.

Como referencia dejo esta página que tiene que ver con la documentación oficial del paquete.

Te invito a leer más sobre:

1. PHP
2. MySQL
3. Bases de datos

No olvides seguirme para estar al tanto de mis actualizaciones 😉