Excel mit PHP bearbeiten
- 13.09.2016
Mit diesem Artikel will ich einen kleinen Einblick in die Bearbeitung von Excel Dateien mittels PHP und dem Framework PHPExcel verschaffen.
Mit PHPExcel ist es möglich nicht nur den alten Excel Dateityp “xls”, sondern auch den Neuen “xlsx”, wie auch vom alternativen Anbieter OpenOffice das Dateiformat “ods” zu laden und zu bearbeiten. Wie Microsoft auf ihrer Homepage Codeplex für quelloffene Programme schreibt, basiert diese Bibliothek auf dem Microsoft’s OpenXML Standard und ist Projekt unter der GNU Library Public License, was soviel heißt das es ohne weiter Lizenzierung in ihren Projekten benutzt werden darf.
Abhängigkeiten (Dependency)
Wenn man die aktuelle Release Version 1.7.6 benutzt, muss man auf die Abhängigkeiten der Bibliothek achten und den Webserver gegebenenfalls anpassen!
- PHP Version 5.2.0 oder neuer
- PHP Extension php_xml
- PHP Extension php_gd2
- PHP Extension php_zip (jedoch nur benötigt wenn man Excel2007 oder OpenOffice lesen/bearbeiten möchte)
Einbinden
Um das Framework im eigenen Code benutzen zu können, müssen die Klassen und die Verzeichnisstruktur des Frameworks natürlich in das Projekt auf den Webserver kopiert werden und in das jeweilige Script / die jeweilige Klasse eingebunden werden.
Die benötigten Dateien findet man nach dem Download und dem Entpacken der zip-Datei im Ordner “Classes”. So kopiert man die Datei “PHPExcel.php” und den Order “PHPExcel” am besten in einen Order im Projekt den ich z.B. “excel” genannt habe.
Und schon kann man mit der Einbindung im Code beginnen. Wenn nun das zu verarbeitende Script im “root” des Projektes liegt lautet der Code folgendermaßen:
require_once "excel/PHPExcel.php"; |
Um die anderen Dateien muss man sich dank dem Autoloader von PHPExcel nicht kümmern, sie werden automatisch mit eingebunden.
Nun geht es los!!!
Ein kleiner Nachteil ist, dass man sich schon vor dem Programmieren des eigenen Codes im klaren sein muss, welchen Dateityp man bearbeiten will.
Dies erkennt PHPExcel nicht von allein, leider! Jedoch ist es natürlich möglich den Dateisuffix der zu bearbeitenden Datei ab zu fragen und an Hand dieser Information die jeweilige Klassen zu instanzieren.
Zum lesen der verschiedenen Dateiformate sind die folgenden Klassen notwendig:
- PHPExcel_Reader_Excel2007 -> xlsx
- PHPExcel_Reader_Excel5 -> xls
- PHPExcel_Reader_OOCalc -> ods
Ich werde auf Excel5 eingehen und anhand eines kleinen Beispiels zeigen, wie man die xls-Datei einließt und die enthaltenen Daten weiterverwenden kann.
Als ersten Schritt nun also die Instanzierung der Reader Class. Dazu habe ich mir 2 Klassenvariablen in meiner Klasse “ExcelExecuter” angelegt und eine Methode zur Initialisierung der Excel Objekte geschrieben. Der Parameter dieser Funktion beinhaltet schon den Pfad der zu bearbeitenden Datei.
ini_set("memory_limit", "256M"); require_once "excel/PHPExcel.php"; class ExcelExecuter { protected $_oPHPExcel = null; protected $_oExcelReader = null; . . . /** * initialize Excel Reader * loads the uploaded file into PHPExcel objects */ protected function _initializeExcelReader($filePath){ $this->_oPHPExcel = new PHPExcel(); $this->_oExcelReader = new PHPExcel_Reader_Excel5(); $this->_oExcelReader->setReadDataOnly(true); $this->_oPHPExcel = $this->_oExcelReader->load($filePath); } } |
Am Anfang der Datei habe ich die Funktion “ini_set” aufgerufen, diese reserviert jedes mal wenn die Klasse instanziert wird den angegeben Speicher.
Dies ist leider auch ein kleiner Nachteil des Frameworks, es benötigt unheimlich viel davon, da die gesamte Bearbeitung logischerweise In-Memory geschieht!
Nun lese ich den Inhalt der Datei aus und speichere ihn in einem mehrdimensionalen Array welches der Excel Datei gleichkommt.
Ob man dies wirklich im Live-Betrieb so machen sollte, ist fraglich, da ja PHPExcel schon recht viel Speicher benötigt. So würde ich nach einlesen einer Zeile diese auch gleich wieder weiter verarbeiten z.B. in einer Datenbank speichern.
... /** * reads the uploaded file and writes file content into data array */ protected function _readAndPersistXlsContent(){ $rowIterator = $this->_oPHPExcel->getActiveSheet()->getRowIterator(); foreach($rowIterator AS $row){ //$rowData = array(); $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); if(1 == $row->getRowIndex()) continue;//skip first row -> no headlines into data array $rowIndex = $row->getRowIndex(); $cellIndex = 0; foreach ($cellIterator as $cell) { if($cellIndex === 1){ //formated the cell from a number over 11 digits (long) into readable format $this->_oPHPExcel->getActiveSheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('0'); } $this->_uploadesData[$rowIndex][$cellIndex] = $cell->getFormattedValue(); //$rowData[$cellIndex] = $cell->getFormattedValue(); $cellIndex++; } //persist the xls data //$this->_insertNewDataInImportTable($rowData); //unset($rowData); } //echo print_r( $this->_uploadedData, true ); } ... |
Ein paar Erklärungen zum Code:
- Zum iterieren über die Reihen und Spalten, gibt es die Implementierung der Klassen RowIterator und CellIterator.
Diese Klasse beinhaltet Methoden um den Zeiger auf die einzelnen Reihen und Spalten zu ändern oder die aktuelle Zelle zu lesen/bearbeiten.
Hierbei bewegt man sich von den Reihen zu den Spalten!
Benutzt man wie im Codebeispiel die foreach fürs iterieren der Spalten, ist der Rückgabewert jeweils vom Typ PHPExcel_Cell. - PHPExcel_Cell ist wohl eine der wichtigsten Klassen für die Bearbeitung der Worksheets (Arbeitsblätter). So findet man hier mehrere Methoden um die Inhalte zu lesen und bearbeiten. Wobei es schon mehrere Möglichkeiten gibt wie aus einer Zelle gelesen wird, z.B. die Methode “getValue” und “getFormatedValue”. Wie die Namen schon vermuten lassen, gibt die Methode “getValue” den Wert der Zelle (wie ihn Excel interpretiert wieder), wobei die Funktion “getFormatedValue” den Wert mit dem zuvor gesetzten Format bereitstellt.
- Um ein Beispiel für die Zellenformatierung zu nennen, betrachte man was passiert wenn man eine Zahl in eine Zelle der Exceltabelle eingibt die länger als 11 Zeichen hat. Man bekommt eine von Excel formatierte (anscheinend) ganz andere Zahl zu sehen. Das liegt am Datentyp der Zelle. Eine Zahl mit mehr als 11 Stellen wird mit dem Datentyp “LONG” gespeichert/verarbeitet/dargestellt. Um nun in Excel die gewünschte Darstellung zu bekommen, formatiert man die Zelle als Zahl mit Dezimalstellen = 0. Nichts anderes passiert in der nachfolgenden Zeile Code:
- gib mir das aktive Worksheet
- gib mir die Klasse Style, Parameter: von der Koordinate auf die mein CellIterator gerade zeigt ($cell->getCoordinate())
- gib mir das Nummernformat
- setze das von mir gewünschte Format, in diesem Beispiel Zahl ohne Dezimalstellen
$this->_oPHPExcel->getActiveSheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('0');
Die möglichen Formate findet man auch im SourceCode der Format Klassen als Konstanten definiert.
Fazit
PHPExcel ist eine gut dokumentierte und wirklich verständliche Bibliothek um mit PHP Excel Dateien zu bearbeiten. Seit geraumer Zeit wurde Sie auch auf die Sprache Python protiert. Die Community ist sehr behilflich wenn es um knifflige Fragen geht, jedoch meist nur auf Englisch, doch welcher Entwickler ist heutzutage der englischen Sprache nicht mächtig? 😉
Das Framework scheint keine Probleme mit Umlauten, Sonderzeichen oder Dateiformatierungen zu haben.
Jedoch ist der enorme Speicherverbrauch doch etwas zu bemängeln!
Im Großen und Ganzen eine wirklich zu empfehlende Bibliothek, die das Handling mit Excle und PHP enorm vereinfacht, da kann das Pendant “PHP_ExcelReader” definitv nicht mithalten!
Hi, ich bin Matthias,
eingefleischter ITholiker und Mitgründer der ITholics GmbH.