Cómo interactuar con Excel desde PHP

Un problema bastante común en el desarrollo de aplicaciones para empresas es la interacción con Excel (Levantar datos, generar planillas, etc…).

Un modo sencillo de evadir el problema (y que muchas veces funciona), es exportar la información hacia archivos de texto separados por comas (Los famosos CSV) y luego tratarlos desde PHP con funciones como fgetcsv.

Obviamente, esto servirá si se trata de una aplicación que sólo requiere leer datos… ¿Qué pasa si necesitamos generarlos? (O si la estructura de la planilla en cuestión es algo más compleja).

Como en la mayoría de los casos, existen diversas opciones de solución. Una particularmente buena es la librería PHPExcel PHPSpreadSheet.

Escribir un archivo Excel usando PHP

Un detalle importante de la librería PHPSpreadSheet es que permite interactuar con planillas de cálculo diversas (xls, xlsx, Gnumeric… ¡hasta CSV!), con lo cual, como podrás imaginar, al escribir (o leer) un archivo habrá que aclarar en qué formato se lo quiere.

Veamos un ejemplo:

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hola')
    ->setCellValue('B2', 'Mundo!')
    ;

$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('salida.xls');

En este caso estamos usando dos clases (PhpOffice\PhpSpreadsheet\Spreadsheet y PhpOffice\PhpSpreadsheet\IOFactory).

La primera es la planilla propiamente dicha, la segunda tiene el objetivo de crear un objeto capaz de escribir esa planilla al formato específico (En este caso, xls).

Una vez obtenido el objeto PhpOffice\PhpSpreadsheet\Writer\Xls (Resultado de la llamada a IOFactory::createWriter) podemos usar su método save para efectivamente generar la planilla.

Veamos otro ejemplo:

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hola')
    ->setCellValue('B2', 'Mundo!')
    ;

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="salida.xls"');

$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');

Aquí, en lugar de escribir el archivo salida.xlsen el servidor, lo estamos ofreciendo para que el cliente lo descargue (Algo muy útil si se trata de exportar un reporte desde nuestra aplicación).

Un tercer ejemplo:

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hola')
    ->setCellValue('B2', 'Mundo!')
    ;

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="salida.xlsx"');

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

Es muy sutil la diferencia, por si no lo notaste, el objeto writer que se creará será de tipo  PhpOffice\PhpSpreadsheet\Writer\Xlsx en lugar de  PhpOffice\PhpSpreadsheet\Writer\Xls.

¡Esa pequeña «x» hace una gran diferencia!

Los formatos xls cayeron en desuso a partir de la versión 2003 de Excel, el xlsx es un formato diferente (basado en XML y comprimido), con lo cual, será muy diferente el resultado obtenido usando uno u otro writer… ¡Ojito! 😉

Leer un archivo Excel usando PHP

La lectura de un archivo Excel es bastante similar:

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load('entrada.xls');
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);

En este caso, la clase PhpOffice\PhpSpreadsheet\IOFactoryintentará «adivinar» el tipo de planilla de la que se trata (Algo bastante útil cuando tienes que tratar con diferentes tipos de planilla).

¿Cómo funciona esta adivinación? es bastante complejo… el punto es que puede fallar, con lo cual, si sabés exactamente el tipo de planilla que vas a usar, más vale usar un Reader específico:

<?php

use PhpOffice\PhpSpreadsheet\Reader\Xls;

$reader = new Xls();
$spreadsheet = $reader->load('entrada.xls');

$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);

Conclusión

Como podrás ver en los ejemplos, es bastante sencillo realizar operaciones sobre una planilla Excel casi como si estuvieses escribiendo una macro.

Además de lo dicho hasta aquí, la documentación de PHPSpreadSheet es un lujo (Sólo que está en Inglés).

mchojrin

43 comentarios en “Cómo interactuar con Excel desde PHP

  • hola, tengo un problema al modificar una plantilla de Excel con esta librería, el problema es el siguiente: en la plantilla tengo dos hojas, en la hoja uno es donde estoy guardando los datos de mi consulta y en la hoja dos tengo una tabla dinámica que se alimenta de la hoja uno. el proceso de generar el archivo esta correcto pero al momento de descargar el archivo se borra la tabla dinámica de la hoja dos (y en esa hoja no estoy modificando nada)
    como podría solucionar esto?
    gracias

    • Hola Roy:

      ¿Cómo determinas que el proceso de generar el archivo está correcto? ¿Se almacena el archivo generado en algún lado?

      Publica algo de código por favor y lo vemos.

      Saludos!

  • Ramón Brito

    Hola como puedo crear un sistema con php que realice operaciones de excel, funciones como división, porcentaje etc en la nube pero como si estuviera trabajando en excel con sus tabla todo idéntico

    • Hola Ramón:

      No es sencillo lo que buscas… mi recomendación sería que no lo hagas, si quieres replicar la funcionalidad del Excel en la nube, ¿por qué no usar Google Spreasheet o algo así?

      Si decides que vale la pena lo que puedes usar es algún componenente para hacer el frontend (Por ejemplo http://handsontable.com/) y luego puedes replicar la funcionalidad desde el backend.

      Justamente ahora estoy trabajando en un proyecto de esas características y por eso la recomendación inicial 🙂

      Exitos!

  • Karla

    Hola, igual es un foro de hace un tiempo y ojala puedan ayudarme, tengo que hacer que una celda de mi excel vuelva obligatorio al seleccionar otra celda con un desplegable en especifico, pero todo desde PHP, si tienes alguna recomendacion me iria genial, muchas gracias

    • Hola Karla!

      Gracias por tu comentario 🙂 Pues no estoy seguro de que lo que quieres hacer sea posible usando PHP ya que lo que quieres es alterar el ingreso de información (Es decir, el comportamiento de la interface de Excel).

      Te sugiero que lo intentes desde el propio Excel (Tal vez usando VBA).

      Exitos!

  • Enrique Nuñez Carvajal

    Buenas noches
    Estoy necesitando saber como puedo extraer los datos de un archivo de excel y organizarlos con una estructura requerido y conocida para grabarlos en un archivo plano txt.
    En el archivo txt debo organizar los datos que extraigo de excel, y dichos datos deben respetar una cantidad de espacios

    Contenido excel
    Referencia Cantidad

    04631-T5R-A00ZZ 1

    txt: El caracter punto(.) representa espacio en este ejemplo para mayor claridad, la cantidad deberá ir siempre con el formato 0000

    ………………………………..4631-T5R-A00ZZ……………..0001…………………

    la extración ya la he logrado, pero como puedo hacer para respetar dichos espacios.

    • Hola!

      Pues diría que la parte más complicada la tienes resuelta. Lo que resta es rescatar los valores de cada fila del Excel y generar el correspondiente txt.

      Lo que puedes usar para generar los strings que necesitas es str_pad.

      Por ejemplo, para rellenar la cantidad podrías usar str_pad($cantidad, 4, '0', STR_PAD_LEFT);

    • Carlos Molina

      Enrique Nuñez Carvajal, yo tengo que hacer algo igual, extraer datos de una excel y mandarlos a un txt, me podrías decir como se hace, me mandaron en la empreaa hacer esto y no tengo la meno idea. Desde ya muchas gracias!!!

  • Richart

    Hola amigo que tal, has tenido que usar composer para lo de las librerias, y si no me podrias explicar como le has hecho? no logro hacer que funcionen las librerias por ende nada de lo otro funciona
    pd. De donde has sacado las lib(PHPSpreadshet)

    • mmm… que difícil… Una solución simple (aunque no la más recomendable) sería ampliar la cantidad de memoria disponible para PHP.

      Otra opción tal vez podría ser realizar el procesamiento en trozos pequeños que el servidor pueda manejar.

      ¿De cuántos datos estamos hablando?

  • AUS

    Hola, como puedo hacer para copiar la plantilla. Es decir, tengo una plantilla fija y cada vez que se da al boton ‘report’ tendria que hacer una copia de la plantilla con otro nombre y trabajar sobre la nueva copia

    Muchas gracias

  • Antony Estrada

    Hola amigo, eres mi única salvación, necesito una interfaz gráfica donde pueda cargar un excel (File) y realizar una limpieza de datos como por ejemplo en la Hoja1 a la columna A quitar espacios, columna B solo quedarme con números, columna C separar nombres y apellidos (si es necesario insertar columna para tener una de Nombres y otra de Apellidos), en la Hoja2 otros cambios por columna,Hoja3 de la misma forma, luego juntar todas las Hojas en una sola con toda la información una bajo otra… y al final que me descarge el excel ya con todos los cambios realizados… Todas las modificaciones y descarga de excel que este asociado a un boton (Ejecutar) de tal forma que yo solo adjunte el excel inicial y cuando pulse ejecutar me realice todos los cambios y me descarge el excel nuevo…

    Me ayudes o derrepente pido demasiado y no puedas hacerlo, igual te recomendare con mis compañeros de clase y les diré que se suscriban a tus redes.

    Gracias,

    Antony Estrada
    antony_virgo_93@hotmail.com

      • Antony Estrada

        Hola, gracias por auxiliarme, necesito hacer un ETL con php, te explico mejor, tengo un excel con 4 Worksheets y necesito hacerle varios cambios por columnas como quitarle espacios al inicio y al final, que solo tengan valor numérico, separa en una columna nombres y apellidos, cruzar este excel con otro etc… al final de todas las acciones consolidar todas las hojas en una sola y que al final me la descargue. Todos estos cambios debe estar en un botón que sea «Ejecutar» y antes de este debe haber una opción de carga para subir el excel inicial (yo usaba la función file).

        Si tienes un tutorial, link, manual, guía o un documento de apoyo donde pueda hacer cambios a un excel por columna como quitarle espacios, símbolos, quedarme solo con valores numéricos y todo tipo de cambios a un excel y todo desde PHP , por favor estaría muy agradecido.

  • Darwin Vivallo Contreras

    No me resulta por ninguna parte. Uso wordpress y vez que cargo el archivo en load(), no funciona mas.

      • Darwin Vivallo Contreras

        he usado las extensiones .xls y .xlsx. Guardo la dirección del archivo en una variable y la dejo en el load.
        Ejemplo:

        /* hasta aquí todo bien*/
        $archivo = «https://pagina/ruta/del/archivo.xls»;
        require_once dirname(FILE) . «/vendor/autoload.php»;
        use PhpOffice\PhpSpreadsheet\Spreadsheet;
        use PhpOffice\PhpSpreadsheet\Read\Xlsx;
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(«Xlsx»);

        /* aquí comienza el problema */
        $spreadsheet = $reader->load($archivo);

        Y después de eso no funciona mas.
        A todo esto, yo no instale phpspreadsheet con composer, solo descargue un paquete de Internet.
        Lo único que quiero es leer el archivo, no escribir.

        • danira

          Intentaste con phpExcel?
          phpExcel\Classes\PHPExcel.php
          phpExcel\Classes\IOFactory.php

          $objPHPExcel = PHPEXCEL_IOFactory::load($desti);
          $objPHPExcel -> setActiveSheetIndex(0);
          $numRows = $objPHPExcel -> setActiveSheetIndex(0)-> getHighestRow();

          $var = $objPHPExcel -> getActiveSheet()->getCell(‘F’.$i)->getCalculatedValue();
          echo ».$var.»;

          Así leo e imprimo en tabla

          • Darwin Vivallo Contreras

            No, lo deje de lado, ya que, encontré información que estaba descontinuado. Por lo mismo no me intereso mucho. Pero lo voy a probar.
            Por lo visto funciona aun, si no, no estarías recomendando. Voy a intentar con phpExcel.

  • gisell

    es posible mostrar la tabla de excel y a una columna y una celda especifica poner una lista desplegable y dependiendo de esta mostrar el resto de la tabla, me ayudaría demasiado gracias

  • gisell

    como puedo hacer operaciones con las celdas pero que no se exactamente cuales son como su numero y sacar promedio o sabiendo el numero sacar promedio operaciones etc.

    • Hola Gisell:

      Gracias por tu pregunta :).

      Para operar con las celdas de una planilla necesitas saber su letra y número (A1 por ejemplo).

      Si te refieres a que no sabes cuántas son las celdas, lo que puedes hacer es recorrer la fila o columna preguntando en cada momento si la celda está vacía y contando y sumando mientras no lo esté (Obviamente esto funcionará si no hay celdas vacías en el medio del rango sobre el que quieres operar).

      ¿Es esto lo que querías saber?

      • gisell

        el archivo tiene muchas celdas y no se cual es su numero «A1» pero tampoco tengo celdas vacias pero como podria hacer el recorrido me podrias dar un ejemplo serias tan amable , gracias

        • Ok… no estoy seguro de comprender correctamente tu situación, pero te dejo este ejemplo obtenido de la documentación de PHPSpreadsheet:

          $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
          $reader->setReadDataOnly(TRUE);
          $spreadsheet = $reader->load("test.xlsx");

          $worksheet = $spreadsheet->getActiveSheet();

          foreach ($worksheet->getRowIterator() as $row) {
          $cellIterator = $row->getCellIterator();
          $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
          // even if a cell value is not set.
          // By default, only cells that have a value
          // set will be iterated.
          foreach ($cellIterator as $cell) {
          echo $cell->getValue() . PHP_EOL;
          }
          }

          Aquí lo que se utiliza son dos objeto «auxiliares» (Un iterador de filas y un iterador de columnas).

          Es la forma más general que encontrarás de recorrer la planilla completa (Si necesitas leer más de una hoja dentro de la planilla tendrás que hacer una iteración más afuera, pero imagino que con esto andarás bien).

    • Hola Danira:

      ¿Cómo estás? Muchas gracias por tu pregunta.

      Para acceder a los comentarios de una celda tenés que usar el método getComment.

      Algo así:

      use PhpOffice\PhpSpreadsheet\Spreadsheet;

      $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile( $argv[1] );
      $reader->setReadDataOnly(true);
      $s = $reader->load( $argv[1] );

      $c = $s->getActiveSheet()->getComment( $argv[2] );

      var_dump( $c );

      Ten en cuenta que el comment es un objeto de clase PhpOffice\PhpSpreadsheet\Comment el cual tiene sus propias propiedades y métodos.

      Saludos!

¿Te quedó alguna duda? Publica aca tu pregunta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

A %d blogueros les gusta esto: