Obtener contenido de hoja de cálculo con formato XLSX en PHP

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:

1. Valor crudo
2. Valor formateado
3. Valor calculado

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).

Libro de Excel

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.

Lecturas recomendadas

Por favor mira el tutorial anterior, pues en el mismo se muestra cómo instalar la librería, sus requisitos y otras cosas.

Código fuente y descargas

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í.

Abrir archivo de Excel con PHP

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.

Las hojas del documento de Excel

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.

El tipo de dato celda o Cell

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).

Valores de una celda

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()

Iterar o recorrer filas y columnas de un documento

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.

Recorrer todas las filas y columnas del documento con índices

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.

Ejemplos de código

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.

Ejemplo 1: leer todas las hojas y obtener celda por coordenadas

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.

Ejemplo 2: leer todas las hojas y obtener celda por número columna y fila

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.

Ejemplo 3: recorrer todas las hojas, filas y columnas de un documento de Excel

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.

Ejemplo 4: leer todo el contenido del documento usando índices enteros y ciclo for

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.

Conclusiones

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.