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.
Generar un archivo Excel desde 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).
- Cómo agregar una página de error 500 en un proyecto PHP - 31/10/2024
- ¿Cuántos contenedoresnecesita tu php? - 28/10/2024
- Cuál es el mejor framework PHP para hacer APIs REST - 25/10/2024
Tengo el siguiente caso, es necesario escribir un excel con varias pestañas con información diferente cada una, pero no me deja ver lo escrito en la siguiente pestaña, pero si hace como un intento porque crea la otra pestaña
Hola Hildefonso:
¿A qué te refieres con que no te deja ver lo escrito? ¿Podrías pegar un poco de código?
me puedes indicar donde consigo ejemplos de codigo para crear en Excel grafico de barras desde PHPSpreadsheet
Como copiar una fila completa
Algo que podrías intentar es levantar los datos de la fila a un arreglo y luego usarlo para rellenar la fila de destino. Algo como:
$spreadsheet
->getSheetByName('Sheet1')
->fromArray(
$spreadsheet
->getSheetByName('Sheet1')
->rangeToArray('A1:H1'),
null,
'B1');
Saludos,
Hola gracias por compartir tus conocimientos. Resulta que estoy trabajando con php desde Drupal 8 y ya he creado desde php una plantilla de excel con datos que traigo de una base de datos, pero deseo que cuando se genere el excel, se cree con las pestañas que representan filtros, es decir las viñetas que activamos normalmente desde excel para que cada campo le brinde al usuario la posibilidad de filtrar por el campo y dato que desee. ¿Como puedo hacer que cuando se cree la plantilla, tambien se creen estas viñetas?, gracias por tu ayuda
Hola Pepe:
Supongo que te refieres a los auto-filtros de Excel. Nunca me tocó hacerlo pero, por lo que veo en la documentación, es posible realizarlo usando el método
setAutoFilter. Puedes ver más detalle en https://phpspreadsheet.readthedocs.io/en/latest/topics/autofilters/.Si quieres probarlo y volver a comentar cómo te fue será muy bienvenido tu aporte.
Saludos,
Mi pregunta es, tengo una hoja central en donde hago referencia a cálculos de otras hojas, por que no me funciona ???
es algo como esto
en la hoja10 en la celta A10 tengo un total hecho por una formula
en la hoja1 en la celda A1 ella es igual a =Hoja10!A10, al final cuando consulto la hoja1 celda A1 lo que me trae es = 0 cuando en realidad es otro valor
Hola Héctor:
¿Podrías mostrar algo de código? No llego a comprender bien tu problema.
Saludos,
Hola, me gustaría saber como puedo leer las hojas del archivo de excel dependiendo de la fecha actual, por ejemplo si hoy es 16 de Mayo que me lea la hoja 16 de mi archivo .xlsx
Hola Brisa:
La solución exacta dependerá de cómo esté estructurado tu archivo .xlsx pero dispones de los métodos
setActiveSheetIndexysetActiveSheetIndexByNamesegún si quieres cambiar la hoja activa a través del número o el nombre.Puedes leer más detalles en la documentación de PHPSpreadSheet.
Luego, para saber el número de día que es hoy puedes usar un objeto
DateTimeImmutable:<?php$d = new \DateTimeImmutable();
echo $d->format('d');
Saludos,
Hola, buenas tardes. Disculpe, me surge una duda, si quisiera que mi excel generara una vista previa de él en el navegador como normalmente se hace con un pdf, ¿Se puede? es que hasta ahora en mis pruebas que he hecho me lo descarga automáticamente.
Gracias!!
Hola Carl!
Depende un poco de cómo generarías esa «vista previa». Puedes hacer un script que lea el Excel y presente su contenido como HTML, tal vez usando algo como un DataTable para hacerlo más vistozo…
Por lo demás, depende del navegador si tiene la posibilidad de renderizar un Excel como lo hace con un pdf (No conozco ningún navegador que trabaje de esa forma).
Saludos!
Hola, antes que todo gracias por compartir tus conocimientos. Pero tengo una consulta
Tengo un archivo Excel que tiene formulas , filtros, tablas dinámicas y que constantemente estoy ingresando datos y quiero saber si existe una forma de mostrar ese Excel de la misma manera que lo veo con la aplicación nativa de office en una pagina web, cosa que otros puedan usar los filtros para buscar información dentro del Excel, pero que no puedan modificarlo.
Gracias de antemano por su tiempo y atención.
Saludos
Hola Raúl!
¿Cómo estás? Algo que podés usar es un DataTable, levantás la info del Excel y le podés configurar unas cuantas opciones para visualizarlo, exportarlo, etc…
Hay otras opciones también pero esta va bien.
Saludos!
Hola buen tutorial, tengo un pregunta:¿Si quiero ejecutar mi proyecto que usa PHPSpreadSheet en otra computadora, esa computadora tiene que tener instalado el composer para que se ejecute?
Hola Hernán!
Gracias por tu comentario 🙂
No necesariamente. Lo que sí necesitarás es tener la carpeta
vendorigual que como la tienes en tu entorno.Lo más cómodo para ello es tener composer en ambos lados y, en el sitio destino usar el comando
composer install.De eso se trata el archivo composer.lock, consulta Qué es composer y por qué deberías usarlo para ver más detalles y también Cómo instalar librerías de composer en un hosting compartido.
Saludos!
¿Hay alguna manera de tomar Excel como base de datos? Tengo un formulario que alimenta una base de datos, pero me gustaría que en lugar de guardarlos en la base datos, los guardara en un excel. Así mismo, otro formulario tomaria los datos para visualizarlos desde ese mismo archivo. ¿Es posible?. Quedo atenta,
Hola!
Técnicamente es posible usar Excel como una base de datos, aunque ciertamente no es recomendable… más aún, si ya tienes una base de datos implementada, lo mejor que podrías hacer es que el otro formulario tomara los datos de esa misma DB y los mostrara…
Eventualmente podrías exportar los datos de tu base hacia un Excel para llevarlos a algún otro sistema.
Salvo que haya alguna razón en particular para usar Excel que no me haya quedado clara (y si quieres comentarla la podré analizar :)).
En fin, si quieres usar Excel puedes hacerlo con la librería que se ve en este post, sólo tienes que definir cuál será el archivo que utilizrás (Para usar el mismo en ambos formularios) y tener cuidado con la concurrencia.
Exitos!
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!
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!
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!
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);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!!!
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)
Hola!
Siempre que puedo uso composer, si no lo conoces te recomiendo que lo investigues. La librería la puedes consultar en https://phpspreadsheet.readthedocs.io/en/latest/.
¿Qué problema tuviste con composer?
Hola, como hacer para exportar información de gran tamaño con esta librería, sin que se cuelgue de memoria el servidor?
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?
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
Para hacer una copia podés usar la función https://www.php.net/manual/es/function.copy.php, después abrís la copia y la trabajás sin problema.
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
Hola!
¿Cómo estás? ¿Cómo puedo ayudarte?
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.
Me temo que no tengo nada tan específico, ¿con lo que leíste aquí no te alcanzó? ¿Qué probaste? ¿Dónde te trabaste?
Si, esta muy bueno lo de esta página, logré avanzar un 60 % de mi proyecto, pero no me sale:
1. Como juntar todas las worksheets en una sola.
2. Hacer cruces con otros Excels.
Por el momento eso.
Para juntar todas las worksheets en una sola tenés que recorrer las diferentes worksheets e ir copiando el contenido de cada celda.
Fijate el método https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-a-spreadsheets-active-sheet.
Respecto de cruzar con otros Excels, no sé si se podrá hacer más eficientemente, pero en principio podés abrir tantos como necesites y levantar la data que quieras.
Exitos!
Solo se puede usar con PHP7
No me resulta por ninguna parte. Uso wordpress y vez que cargo el archivo en load(), no funciona mas.
No comprendo del todo tu problema… ¿Podrias elaborar un poco mas? ¿Usas Excel con WordPress?
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.
Cuando dices «no funciona más»… ¿qué es exactamente lo que sucede? ¿Ves algún error?
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
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.
si, uso wordpress y quiero cargar el archivo .xls para solo mostrar en la pagina.
Pues si usas WordPress lo mejor que puedes hacer es incorporar algún plugin que lo haga…
Lo he intentado pero no encuentro algo todavía. Gracias de todas maneras.
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
No creo que eso puedas hacerlo usando PHP… igualmente, veo más práctico hacerlo directamente desde Excel, ¿no te parece?
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?
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).
Me funciona perfecto, gracias 🙂
Excelente! Cualquier duda puedes dejarla por aquí o en mi twitter (@mchojrin)
Excelente muchas graciassss 🙂
No hay problema!
Gracias por el empuje 🙂
Cómo puedo obtener el comentario de una celda de excel con php?
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\Commentel cual tiene sus propias propiedades y métodos.Saludos!