Ya vimos cómo escribir a una hoja de cálculo usando PHPSpreadSheet; hoy veremos el proceso “inverso” y es leer el contenido de todo un documento de Excel.
Lo que haremos será leer todas las hojas (llamadas sheets) de un documento con extensión XLSX, así como iterar por todas las filas y leer cada celda de tres maneras:
Esta última manera permite calcular la fórmula en determinada columna y celda; pues si solamente obtenemos el valor crudo se mostrará la fórmula, no el resultado de la misma.
También vamos a ver cómo obtener la fila (es decir, el número, como 1) y la columna como letra (es decir, algo como A o B).
Por cierto, para trabajar con Excel y PHP vamos a usar un documento que tiene dos hojas de cálculo que he preparado especialmente para demostrar la lectura de un archivo XLSX con PHPSpreadSheet y PHP.
Eres libre de descargarlo: click aquí para ver el documento.
Recuerda que siempre trato de dar los ejemplos más simples y a la vez completos; para que puedas adaptarlos a tu proyecto.
Nota: primero explicaré cada cosa y después pondré el código, no te desesperes y lee todo cuidadosamente.
Por favor mira el tutorial anterior, pues en el mismo se muestra cómo instalar la librería, sus requisitos y otras cosas.
El código fuente lo voy a ir exponiendo a través del post, pero también puedes visitar el repositorio en GitHub por si en el futuro hago cambios.
Si quieres descargar el código para probarlo sin configurar nada, visita la página de releases, descarga el zip y ponlo en tu carpeta pública.
En caso de clonar el repositorio recuerda instalar las dependencias con composer install
.
Por cierto, si no tienes XAMPP mira cómo instalarlo aquí.
Para leer una hoja de cálculo primero necesitamos leer el archivo del disco duro. Para ello se llama a IOFactory::load("ruta_del_archivo.xlsx")
En este caso voy a trabajar con un archivo con extensión xlsx, aunque se podría con otros formatos pero se recomienda este último. Lo que devuelve load es el documento listo para trabajar.
Una hoja de cálculo puede tener múltiples hojas, por eso es que a veces a los documentos se les dice “libros”.
El número de hojas en un libro puede ser determinado con $documento->getSheetCount()
que devuelve un número entero; y para ir cambiando entre hojas podemos hacer esto:
$hojaActual = $documento->getSheet($indice);
Es decir, obtener una hoja por su índice, comenzando en 0. Para dejar claras las cosas, la hoja con índice 0 es la primer hoja.
Podemos obtener una celda de distintas maneras. Una vez que tenemos la hoja, podemos llamar a getCell
o a getCellByColumnAndRow
.
Cuando llamamos a getCell
le pasamos las coordenadas como cadena, de la forma “LetraNúmero”. Por ejemplo A1, B2, etcétera.
En cambio, cuando llamamos a getCellByColumnAndRow
le pasamos dos argumentos: el número de columna (comenzando en 1, no en 0) y el número de fila (igualmente comenzando en 1, no en 0).
Como lo dije, hay 3 tipos de valores. El valor crudo o así como está, sin formatear (también devuelve la fórmula) se obtiene con $celda->getValue()
El valor formateado (por ejemplo, si está formateado como dinero o con decimales) se obtiene llamando a $celda->getFormattedValue()
Para obtener el valor calculado (esto es útil en las celdas con fórmulas) en lugar del valor normal, se llama a $celda->getCalculatedValue()
Lo que normalmente vamos a querer hacer es iterar por todas las filas y columnas del documento, la librería ya proporciona unos iteradores.
Una vez que tenemos la hoja, podemos obtener el iterador de las filas con $hojaActual->getRowIterator()
y a su vez recorrerlo con un foreach. Algo así:
foreach($hojaActual->getRowIterator() as $fila){}
Una vez que tenemos la fila, podemos obtener el iterador de las celdas. Para ello se llama a $fila->getCellIterator()
e igualmente se puede usar en un foreach:
foreach($fila->getCellIterator() as $celda){}
Nota: es get cell iterator, puede ser confuso por la letra ele (L) y la letra (I). Cuando ya tenemos la celda podemos obtener sus valores como lo explico arriba.
Adicionalmente esta librería permite acceder a las filas y columnas por índices, en un ciclo for normal con un entero como índice.
Para hacer el ciclo, necesitamos saber el número de filas y columnas; lo complicado es lo segundo, pues las columnas se identifican con letras y no con números.
Si se desea saber el número que tiene la fila mayor, es decir, la fila más grande o fila final, se llama a $hojaActual->getHighestRow()
la cual devuelve un entero.
Y para saber el número más grande de columna, la última columna o la columna mayor, se llama a $hojaActual->getHighestColumn()
la cual devuelve la letra de la columna.
Aquí podría haber un pequeño problema, ya que no podemos hacer un ciclo con comparando un número con letras; pero para nuestra fortuna la librería ya tiene un método que convierte la letra de la columna a un número:
$numeroMayorDeColumna = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($letraMayorDeColumna);
De manera que ahora sí podemos hacer un ciclo y en cada iteración llamar a $hojaActual->getCellByColumnAndRow()
pasándole el número de columna y fila como vimos anteriormente; en el ejemplo se ve a detalle.
Ahora que ya expliqué cómo hacer cada cosa, veamos algunos ejemplos. Recuerda que ya dejé el documento al inicio, para que veas que la salida que muestro coincide con lo que existe en el libro de Excel.
Una vez que ya expliqué todo lo de arriba veamos cómo leer el documento hoja por hoja y obtener la primera celda obteniéndola por sus coordenadas A1.
<?php
/**
* Demostrar lectura de hoja de cálculo o archivo
* de Excel con PHPSpreadSheet: leer determinada fila
* y columna por coordenadas
*
* @author parzibyte
*/# Cargar librerias y cosas necesarias
require_once "vendor/autoload.php";
# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;
# Recomiendo poner la ruta absoluta si no está junto al script
# Nota: no necesariamente tiene que tener la extensión XLSX
$rutaArchivo = "LibroParaLeerConPHP.xlsx";
$documento = IOFactory::load($rutaArchivo);
# Recuerda que un documento puede tener múltiples hojas
# obtener conteo e iterar
$totalDeHojas = $documento->getSheetCount();
# Iterar hoja por hoja
for ($indiceHoja = 0; $indiceHoja < $totalDeHojas; $indiceHoja++) {
# Obtener hoja en el índice que vaya del ciclo
$hojaActual = $documento->getSheet($indiceHoja);
echo "<h3>Vamos en la hoja con índice $indiceHoja</h3>";
$coordenadas = "A1";
# Lo que hay en A1
$celda = $hojaActual->getCell($coordenadas);
# El valor, así como está en el documento
$valorRaw = $celda->getValue();
# Formateado por ejemplo como dinero o con decimales
$valorFormateado = $celda->getFormattedValue();
# Si es una fórmula y necesitamos su valor, llamamos a:
$valorCalculado = $celda->getCalculatedValue();
# Imprimir
echo "En <strong>$coordenadas</strong> tenemos el valor <strong>$valorRaw</strong>. ";
echo "Formateado es: <strong>$valorFormateado</strong>. ";
echo "Calculado es: <strong>$valorCalculado</strong><br><br>";
}
La salida es esta:
Estamos obteniendo la celda que está en la esquina superior izquierda.
Lo mismo que hicimos en el ejemplo 2 vamos a hacerlo con este ejemplo, pero ahora obteniendo la celda por número de columna y fila. El código es el siguiente:
<?php
/**
* Demostrar lectura de hoja de cálculo o archivo
* de Excel con PHPSpreadSheet: leer determinada celda
* por número de columna y fila
*
* @author parzibyte
*/# Cargar librerias y cosas necesarias
require_once "vendor/autoload.php";
# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;
# Recomiendo poner la ruta absoluta si no está junto al script
# Nota: no necesariamente tiene que tener la extensión XLSX
$rutaArchivo = "LibroParaLeerConPHP.xlsx";
$documento = IOFactory::load($rutaArchivo);
# Recuerda que un documento puede tener múltiples hojas
# obtener conteo e iterar
$totalDeHojas = $documento->getSheetCount();
# Iterar hoja por hoja
for ($indiceHoja = 0; $indiceHoja < $totalDeHojas; $indiceHoja++) {
# Obtener hoja en el índice que vaya del ciclo
$hojaActual = $documento->getSheet($indiceHoja);
echo "<h3>Vamos en la hoja con índice $indiceHoja</h3>";
# Nota: las columnas y filas comienzan en 1, no en 0
$columna = 1;
$fila = 1;
# Lo que hay en 1, 1
$celda = $hojaActual->getCellByColumnAndRow($columna, $fila);
# El valor, así como está en el documento
$valorRaw = $celda->getValue();
# Formateado por ejemplo como dinero o con decimales
$valorFormateado = $celda->getFormattedValue();
# Si es una fórmula y necesitamos su valor, llamamos a:
$valorCalculado = $celda->getCalculatedValue();
# Imprimir
echo "En <strong>$columna, $fila</strong> tenemos el valor <strong>$valorRaw</strong>. ";
echo "Formateado es: <strong>$valorFormateado</strong>. ";
echo "Calculado es: <strong>$valorCalculado</strong><br><br>";
}
La salida es la misma, pero ahora estamos obteniendo la celda por número de columna y fila.
El ejemplo más completo es el que expongo a continuación. En él leemos todas las hojas y por cada una vamos iterando la fila y columna; de esta forma leemos todo el contenido de un libro de Excel usando PHP.
Así podríamos leer cualquier libro u hoja de cálculo sin importar el número de filas o columnas. Sin más que decir, aquí está el código:
<?php
/**
* Demostrar lectura de hoja de cálculo o archivo
* de Excel con PHPSpreadSheet: leer todo el contenido
* de un archivo de Excel
*
* @author parzibyte
*/# Cargar librerias y cosas necesarias
require_once "vendor/autoload.php";
# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;
# Recomiendo poner la ruta absoluta si no está junto al script
# Nota: no necesariamente tiene que tener la extensión XLSX
$rutaArchivo = "LibroParaLeerConPHP.xlsx";
$documento = IOFactory::load($rutaArchivo);
# Recuerda que un documento puede tener múltiples hojas
# obtener conteo e iterar
$totalDeHojas = $documento->getSheetCount();
# Iterar hoja por hoja
for ($indiceHoja = 0; $indiceHoja < $totalDeHojas; $indiceHoja++) {
# Obtener hoja en el índice que vaya del ciclo
$hojaActual = $documento->getSheet($indiceHoja);
echo "<h3>Vamos en la hoja con índice $indiceHoja</h3>";
# Iterar filas
foreach ($hojaActual->getRowIterator() as $fila) {
foreach ($fila->getCellIterator() as $celda) {
// Aquí podemos obtener varias cosas interesantes
#https://phpoffice.github.io/PhpSpreadsheet/master/PhpOffice/PhpSpreadsheet/Cell/Cell.html
# El valor, así como está en el documento
$valorRaw = $celda->getValue();
# Formateado por ejemplo como dinero o con decimales
$valorFormateado = $celda->getFormattedValue();
# Si es una fórmula y necesitamos su valor, llamamos a:
$valorCalculado = $celda->getCalculatedValue();
# Fila, que comienza en 1, luego 2 y así...
$fila = $celda->getRow();
# Columna, que es la A, B, C y así...
$columna = $celda->getColumn();
echo "En <strong>$columna$fila</strong> tenemos el valor <strong>$valorRaw</strong>. ";
echo "Formateado es: <strong>$valorFormateado</strong>. ";
echo "Calculado es: <strong>$valorCalculado</strong><br><br>";
}
}
}
Es como ver todo el contenido, pero usando PHP. La salida es la siguiente:
No se ve completa, pero en ella podemos observar que está obteniendo el valor formateado como dinero, así como el valor que resulta de calcular la fórmula.
El resultado es idéntico al de arriba, pero nos da la posibilidad de acceder a las celdas por sus índices, como si fuera un arreglo.
<?php
/**
* Demostrar lectura de hoja de cálculo o archivo
* de Excel con PHPSpreadSheet: leer todo el contenido
* de un archivo de Excel usando índices, no iteradores
*
* @author parzibyte
*/# Cargar librerias y cosas necesarias
require_once "vendor/autoload.php";
# Indicar que usaremos el IOFactory
use PhpOffice\PhpSpreadsheet\IOFactory;
# Recomiendo poner la ruta absoluta si no está junto al script
# Nota: no necesariamente tiene que tener la extensión XLSX
$rutaArchivo = "LibroParaLeerConPHP.xlsx";
$documento = IOFactory::load($rutaArchivo);
# Recuerda que un documento puede tener múltiples hojas
# obtener conteo e iterar
$totalDeHojas = $documento->getSheetCount();
# Iterar hoja por hoja
for ($indiceHoja = 0; $indiceHoja < $totalDeHojas; $indiceHoja++) {
# Obtener hoja en el índice que vaya del ciclo
$hojaActual = $documento->getSheet($indiceHoja);
echo "<h3>Vamos en la hoja con índice $indiceHoja</h3>";
# Calcular el máximo valor de la fila como entero, es decir, el
# límite de nuestro ciclo
$numeroMayorDeFila = $hojaActual->getHighestRow(); // Numérico
$letraMayorDeColumna = $hojaActual->getHighestColumn(); // Letra
# Convertir la letra al número de columna correspondiente
$numeroMayorDeColumna = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($letraMayorDeColumna);
# Iterar filas con ciclo for e índices
for ($indiceFila = 1; $indiceFila <= $numeroMayorDeFila; $indiceFila++) {
for ($indiceColumna = 1; $indiceColumna <= $numeroMayorDeColumna; $indiceColumna++) {
# Obtener celda por columna y fila
$celda = $hojaActual->getCellByColumnAndRow($indiceColumna, $indiceFila);
# Y ahora que tenemos una celda trabajamos con ella igual que antes
# El valor, así como está en el documento
$valorRaw = $celda->getValue();
# Formateado por ejemplo como dinero o con decimales
$valorFormateado = $celda->getFormattedValue();
# Si es una fórmula y necesitamos su valor, llamamos a:
$valorCalculado = $celda->getCalculatedValue();
# Fila, que comienza en 1, luego 2 y así...
$fila = $celda->getRow();
# Columna, que es la A, B, C y así...
$columna = $celda->getColumn();
echo "En <strong>$columna$fila</strong> tenemos el valor <strong>$valorRaw</strong>. ";
echo "Formateado es: <strong>$valorFormateado</strong>. ";
echo "Calculado es: <strong>$valorCalculado</strong><br><br>";
}
}
}
Gracias a esto tenemos dos maneras de iterar el documento y podemos aprovechar la que mejor nos convenga.
Sé que tardé un poco en escribir la continuación del post anterior, pero con este quedan explicadas las formas básicas de leer y escribir archivos de Excel con PHP.
Esta librería es muy completa, pero a la vez compleja, por eso es que los ejemplos son un poco confusos a veces.
Recuerda que puedes deleitarte leyendo toda la documentación aquí. Por otro lado, te invito a que leas más sobre PHP.
Igualmente te animo a seguirme en mis redes sociales para cuando publique más contenido de este tema.
No olvides que el índice de las hojas empieza en 0, y el de las filas y columnas en 1.
Hoy te voy a presentar un creador de credenciales que acabo de programar y que…
Ya te enseñé cómo convertir una aplicación web de Vue 3 en una PWA. Al…
En este artículo voy a documentar la arquitectura que yo utilizo al trabajar con WebAssembly…
En un artículo anterior te enseñé a crear un PWA. Al final, cualquier aplicación que…
Al usar Comlink para trabajar con los workers usando JavaScript me han aparecido algunos errores…
En este artículo te voy a enseñar cómo usar un "top level await" esperando a…
Esta web usa cookies.
Ver comentarios
Hola, espero que me puedas apoyar. Apenas comienzo a usar la librería y quiero saber, ¿es capaz de leer una tabla dinámica correctamente? Gracias de antemano por la respuesta. Saludos
Hola. Sería cuestión de probar y/o leer la documentación
Excelente explicación y muy claro el ejemplo! Gracias!!!
Gracias por sus comentarios. Saludos!
Hola tengo un problema, en mi documento Excel quiero obtener datos de manera dinámica me refiero a que una sección del documento puede contener 1 dato en la celda A1 o 100 datos de la celda A1:A100 entonces no siempre son la misma cantidad de datos y en el pie de la tabla hay mas datos que quiero recuperar pero es difil hacerlo sabiendo que se mueven de posición dependido que información tenga el documento, otro problema es el formato del Excel tiene filas y columnas vacías entonces me es muy complicado obtener datos que se mueven de posición dependiendo la cantidad de información que tenga el archivo como puedo solucionar ese problema.
Primero, muy bueno el tutorial. Super útil.
Segundo, te pido me ayudes con algo que no logré resolverlo. Mi archivo particular, "la tabla" con los datos no empieza en A1, pues el usuario tiene una personalizada. Digamos que empieza en B11.
Como puedo hacerlo.
Hola. Para ayuda personalizada siempre me puede encontrar en: https://parzibyte.me/#contacto
Saludos :)
Buenas tardes tengo instalado el paquete en otro subdirectorio, y no encuentra:
use PhpOffice\PhpSpreadsheet\IOFactory;
Comentas:
# Recomiendo poner la ruta absoluta si no está junto al script
No logro poner correctamente la ruta Absoulta, me podrías apoyar con algún ejemplo.
Gracias
Muchísimas gracias por tus ejemplo, me sirvieron de maravilla
Gracias por tus comentarios. Te invito a seguirme y compartir.
Saludos
¿Hay alguna manera de que cada que alguien entre o mi web se pase a la siguiente casilla?
Estoy desarrollando una web de códigos de descuentos, estos códigos van del 0001 hasta el 1550
necesito que cada que alguien entre me de un código en orden ascendente, sin que se repitan
Hola, sí, podrías guardar el último número en una base de datos, o de manera simple, en un archivo de texto
Saludos