Molti dei progetti web/app ai quali ho lavorato e sto attualmente lavorando, richiedono l’upload di fogli Excel e CSV per l’immissione di grandi quantità di dati nel database. Altre volte invece è necessario generare dei report o degli export in formato Excel o CSV, come download o allegati ad email automatiche.
Una libreria che consiglio, estremamente semplice da utilizzare, è PHPSpreadSheet:
https://github.com/PHPOffice/PhpSpreadsheet
La libreria è scritta interamente in PHP, linguaggio che ormai è presente in praticamente tutti gli host web (ad esempio, se avete WordPress, avete sicuramente PHP). Se PHP non è presente è sempre possibile aggiungerlo come servizio extra.
Installazione
Installare la libreria è semplice, basta creare una cartella vuota nello spazio web e copiare il build dell’ultima release. Compilare la libreria richiede composer, ma è sempre possibile utilizzare questa comoda scorciatoia
Scaricato lo zip del build, estraetelo nella cartella vuota che avete creato e voilà, la libreria è “installata”.
Esempio: creazione di file xlsx
Vediamo un semplicissimo esempio che tocca tutte le funzionalità base della libreria. Immaginiamo di avere la libreria installata nella cartella “test”.
require 'test/vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue("A1", "Ciao mondo!"); $writer = new Xlsx($spreadsheet); $writer->save('prova.xlsx');
Questo semplice programma crea un nuovo file Excel (prova.xlxs) nella cartella corrente (sul server ovviamente) e inserisce la scritta “Ciao mondo!” nella cella A1 del foglio. Tutto qui. setCellValue() è sostanzialmente l’unica API da utilizzare per inserire valori nelle varie celle. Tramite appositi loop potrete ad esempio leggere una riga dal database e creare la riga corrispondente nel foglio Excel e cosi via a creare una tabella completa dei dati.
Se indirizzare le celle con i nomi classici “A1”, “A2”, “C3” etc.. risultasse scomodo, è sempre possible indicizzare tramite due indici numerici separati, uno per la riga e uno per la colonna:
setCellValueByColumnAndRow(1, 5,"Ciao Mondo!");
E’ possibile inoltre impostare font, carattere e allargare/restringere le celle a piacimento, la documentazione contiene tutti gli esempi necessari.
Esempio: lettura di un file xlsx caricato in upload
In questo caso vogliamo leggere un file Excel caricato dall’utente e riversare i dati nel database (o fare delle elaborazioni).
require 'test/vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true); $spreadsheet = $reader->load("prova.xlsx");
In questo caso leggiamo un file (prova.xlsx) già presente nella directory corrente. Il file può essere un upload utente oppure un file generato precedentemente, non ha importanza.
Per leggere una cella, utilizziamo la funzione getCellByColumnAndRow():
$sheet = $spreadsheet->getActiveSheet(); $value = $sheet->getCellByColumnAndRow(2, 3);
che restituisce il contenuto della cella B3. Gli indici partono da 1, quindi A1 è (1,1).
Download diretto del file Excel
Magari preferiamo far partire immediatamente il download nel browser dell’utente non appena la libreria ha creato il file Excel, senza passare da un file temporaneo memorizzato sul server. Molto semplicemente, possiamo impostare gli header PHP per il download e scrivere il foglio Excel nel file speciale php://output. Questo file è un’area di memoria piuttosto che un file vero e proprio: la libreria creerà il foglio Excel in memoria che poi verrà passato in download al browser dell’utente.
$now = gmdate("D, d M Y H:i:s"); header("Expires: Tue, 03 Jul 2001 06:00:00 GMT"); header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate"); header("Last-Modified: {$now} GMT"); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="prova.xlsx"'); header("Content-Transfer-Encoding: binary"); $writer = new Xlsx($spreadsheet); $writer->save("php://output"); die();
Il nome del file in download è impostato nell’header content-dispositon filename (nome file: “prova.xlsx”).
Altre funzionalità
Quello che abbiamo visto è veramente il minimo sindacale per creare e leggere file Excel e CSV con PHP. La libreria mette a disposizione moltissime altre feature tra le quali:
- il supporto per file Excel 2003, CSV, file spreadsheet Mac
- accesso alle tab
- calcolo e creazione formule
Per una lista completa delle funzionalità vi rimando alla documentazione e alla pagina ufficiale del progetto:
https://github.com/PHPOffice/PhpSpreadsheet
https://phpspreadsheet.readthedocs.io/en/develop/
Complimenti per la spiegazione molto chiara ed esaustiva.
Volevo chiederti se esiste la possibilità, operando sullo stesso file .xlsx, di copiare il contenuto di tre fogli diversi (foglio1, foglio2, foglio3) nel primo foglio (foglio1) accodandoli al contenuto già esistente.
Grazie in anticipo.
Diego
Ciao Diego, credo di si. Basta creare un foglio vuoto e fare un while loop sulle righe dei tre fogli, man mano aggiungendo queste righe al foglio vuoto. Per ottenere il risultato che descrivi, occorre ciclare sui fogli in questa sequenza: foglio1, foglio1, foglio2, foglio3. Poi è chiaro, al termine dell’operazione dovrai rinominare il foglio risultato in “foglio1”
Ciao Gianluca e complimenti per questo tutorial così chiaro e utile.
Approfitto di averti “trovato” per porti un piccolo quesito. Ho scritto un programma che utilizza PHPSpreasheet e genera un file xlsx con un solo foglio partendo da un template xlsx e popolandolo con i dati letti da un database.
Tutto funziona molto bene, l’unico problema è che quando si apre il file risultante (con LibreOffice Calc) compare il messaggio che non tutti i dati sono stati caricati perché il numero di colonne supera il massimo consentito. Curiosando nel file xlsx che ho unzippato ho visto che in effetti il massimo è impostato a 1025 mentre credo che dovrebbe essere 1024, ma non trovo il modo di correggere il parametro da php. Puoi darmi una dritta per favore? Grazie e ancora complimenti.
Sergio
Innanzitutto scusa per la lunghezza del post, ho un bug al cervello che mi impedisce di essere sintetica. Spero avrai pazienza.
Sono al mio primo script PHP e, per adesso, invece di scrivere codice, mi limito a scopiazzarlo un po’ di qua e un po’ di là. Usando le tue indicazioni sono riuscita sia a leggere da un file Excel che a crearne uno e scriverci qualcosa, ma non riesco ad aggiungere nulla a un file già esistente. In realtà riesce a scrivere nel file, ma quando lo salva sovrascrive il vecchio file con un altro contenente solo i nuovi dati. Si può risolvere il problema? Se sì, come?
Ho anche altri due problemi, non gravi come quello appena esposto, ma abbastanza fastidiosi. Il primo problema è che la libreria non supporta i file con macro, in pratica quelli con estensione xlsm, anche se, da quello che ho capito leggendo la documentazione, questo problema è insormontabile.
Il secondo, invece, credo possa avere una soluzione. Quando vado a leggere una cella contenente una formula, non ottengo il valore della formula, ma la formula stessa, addirittura tradotta dall’italiano all’inglese. C’è un modo per leggere la formula ma riportare il valore da essa generato? Anche qui, se sì, come?
Ringraziando anticipatamente, ti saluto e ti faccio i complimenti per l’ottimo lavoro.
Ciao Rosa, probabilmente perchè riparti con un foglio vuoto invece di appendere i dati al foglio letto dal file originale. Per le macro (.xlsm) hai ragione, la libreria non le supporta. Puoi però convertire il file in .xlsx, a quel punto dovrebbe leggertelo. Per le formule invece, devi usare la funzione getCalculatedValue() invece di getValue(). La getValue() ritorna il contenuto della cella (valore o formula), invece con getCalculatedValue() recuperi in ogni caso un valore, proveniente o direttamente dalla cella oppure dalla formula codificata nella cella stessa
Grazie tante per la risposta, anche se ho già risolto i due problemi “risolvibili”: per il secondo ho già scoperto la funzione che mi suggerisci tu, mentre per il primo problema ho usato
$spreadsheet= \PhpOffice\PhpSpreadsheet\IOFactory::load(“MioFileExcel.xlsx”), e poi ho usato anche due diverse istanze del foglio di lavoro, una per la lettura e una per la scrittura, anche se non ho ancora capito bene cosa ha risolto il problema, ma l’importante è che vada.
Ancora grazie e buon proseguimento
Ho acquistato l’app. Clienti, nell’importare contatti in formato CSV non riesco ad importare la colonna della città, cap., provincia ed non trovo la colonna delle categorie quali bar, ristorante, pasticcerie etc.
Noi abbiamo un gestionale Danea che esporta in un file Excel che noi salviamo in CSV.
Aspetto vostre notizie e nell’occasione porgo i più distinti saluti
Claudio 335 6357939
Salve Claudio, nessun problema, le spiego come fare:
il file CSV non richiede alcun formato particolare, è nell’app che decide quali colonne importare. Metta il file clienti.csv nella memoria principale del dispositivo e poi vada su “importa da CSV”. A questo punto vedrà la schermata che le chiede di associare le colonne del suo file CSV ai campi cliente. Sono inclusi anche eventuali campi personalizzati che ha precedentemente creato. Le categorie al momento non sono supportate in import ma solo in export. Se mi manda il file CSV (anche parziale) potrei creare un aggiornamento dell’app che include la possibilità di importare le categorie da una colonna specifica. Per ulteriori chiarimenti mi scriva a info@clientiapp.com