
Warum braucht mein Excel-Export meinen ganzen Arbeitsspeicher auf?
Warum du PHPSpreadsheet nicht benutzen solltest
Allowed memory size of 2147483648 bytes exhausted (tried to allocate 1326419838 bytes)
Nur in seltensten Fällen erreicht das Resultat unseres Excel-Exports die 1-MB-Grenze, und trotzdem wurde hier das Speicherlimit von 2 Gigabyte überschritten. Wie kommt das?
Bei uns war ein Teilproblem fehlende Pagination - in dem Sinne, dass wir auf einmal alle zu exportierenden Daten aus der Datenbank ziehen wollten, was bei mehreren tausend Einträgen die Speicherlimits sprengen kann. In vielen Fällen wird eine entsprechende Umstellung, bei der Daten stückweise nachgeladen werden, schon ausreichend Speicherplatz freimachen.
Doch das ist nicht das ganze Problem. Auch mit Pagination wären wir bei den großen Exports, die unsere Kunden teilweise brauchen, auf Speicherprobleme gestoßen: PhpSpreadsheet, das Excel-Backend, das wir benutzen, ist nicht sehr speichereffizient. PhpSpreadsheet selbst empfiehlt einen externen Cache (bspw. in der In-Memory-Datenbank Redis) zu benutzen. Weil dies aber den ohnehin schon lange andauernden Export noch weiter verlangsamen würde, fiel die Entscheidung, stattdessen ein separates Excel-Backend einzubinden.
Da wir PhpSpreadsheet über das yii2-Export-Widget einbinden, haben wir das umgesetzt, indem wir yii2-Export entsprechend angepasst haben. Damit schlagen wir zwei Fliegen mit einer Klappe: Wir stellen sicher, dass wir selbst mit PhpSpreadsheet nicht in Speicherprobleme laufen und tragen zu offener Software bei, damit auch andere dieses Problem in Zukunft weniger haben.
Wechsel ja, aber wohin?
Viel Auswahl für PHP Excel-Backends gibt es nicht, schlussendlich haben wir aber zwei Kandidaten gefunden:
PHP_XLSXWriter funktioniert zwar grundsätzlich, ist aber schlecht dokumentiert und entbehrt vieler Features, wie bspw. Excels AutoFilter-Funktionalität.
Openspout, ein Fork des inzwischen nicht mehr gewarteten Spout, ist umfangreicher, besser dokumentiert (auch wenn wir etwas nachhelfen mussten) und somit ein besserer Kandidat für unseren Anwendungsfall.
Unterschiede zwischen PhpSpreadsheet und Openspout
Openspout hält sein Versprechen von stark begrenztem Speicherverbrauch dadurch ein, dass die Werte einzelner Zellen nicht lange im Arbeitsspeicher behalten, sondern durchgängig in eine temporäre Datei geschrieben werden. Dies bringt den Nachteil mit sich, dass man Werte und Stile auch nur für die aktuelle Zeile bearbeiten kann. Es ist also nicht möglich, wie bei PhpSpreadsheet zwischen Zeilen hin- und herzuspringen.
Dadurch kann das Bearbeiten von zellenübergreifenden Stilen etwas umständlich werden: Möchte man mit PhpSpreadsheet eine Gruppe an Zellen wählen und dieser innerhalb der Gruppe gestrichelte, aber an der Grenze der Gruppe eine durchgezogene Border geben, geht das recht einfach:
$sheet->getStyle([1, 1, 10, 10])->applyFromArray([
'borders' => [
'outline' => [
'borderStyle' => Border::BORDER_MEDIUM,
'color' => ['argb' => Color::COLOR_BLACK],
],
'inside' => [
'borderStyle' => Border::BORDER_DOTTED,
'color' => ['argb' => Color::COLOR_BLACK],
],
],
]);
Openspout unterstützt outline und inside-Border nicht, da nur eine Zelle gleichzeitig bearbeitet werden kann. Das Openspout-Äquivalent dazu benötigt also eine Helferfunktion, die für jede Zelle einzeln bestimmt, ob sie am Rand der Gruppe liegt und somit eine andere Border bekommt.
Grundsätzlich sollte für neue Projekte die Entscheidung, ob PhpSpreadsheet oder Openspout verwendet werden soll, hauptsächlich davon abhängen, ob die benötigten Stileinstellungen mit Openspout allzu umständlich sind und ob der Export theoretisch wachsen kann. Wenn alle Excel-Exports ein fixes Format haben, anstatt beliebig viele Zeilen haben zu können, fällt der Unterschied zwischen Openspout und PhpSpreadsheet nicht ins Gewicht.
Migration von PhpSpreadsheet zu Openspout
Da wir an anderen Orten keine gute Dokumentation zum Wechsel von PhpSpreadsheet zu Openspout finden konnten, dokumentieren wir hier einmal den Prozess in 6 Schritten:
1. Anderen Writer verwenden
Anstelle eines PhpSpreadsheet Spreadsheet verwenden wir nun Openspouts Writer-Klasse. Dafür muss auch im Gegensatz zu PhpSpreadsheet schon im Voraus das Format des Exports entschieden werden. Unsere Initialisierungslogik in yii2-Export sieht so aus:
public function initOpenspout()
{
if ($this->_exportType === self::FORMAT_EXCEL_X) {
$this->_objOpenspoutOptions = new \OpenSpout\Writer\XLSX\Options();
$this->_objOpenspoutWriter = new \OpenSpout\Writer\XLSX\Writer($this->_objOpenspoutOptions);
} elseif ($this->_exportType === self::FORMAT_ODS) {
$this->_objOpenspoutOptions = new \OpenSpout\Writer\ODS\Options();
$this->_objOpenspoutWriter = new \OpenSpout\Writer\Ods\Writer($this->_objOpenspoutOptions);
} else {
$this->_objOpenspoutOptions = new \OpenSpout\Writer\CSV\Options();
$this->_objOpenspoutOptions->FIELD_DELIMITER = $this->getSetting('delimiter', "\t");
$this->_objOpenspoutWriter = new \OpenSpout\Writer\CSV\Writer($this->_objOpenspoutOptions);
}
}
Es wird also immer ein dem Export-Typ zugehöriges Options-Objekt erzeugt, das (falls notwendig) mit entsprechenden Werten befüllt und dann in den jeweiligen Writer gereicht wird. Hierbei empfiehlt es sich weiter eine Referenz zum Options-Objekt zu behalten (hier in $this->_objOpenspoutOption), da teilweise auf dieses direkt zugegriffen werden muss.
2. Writer für eine Datei öffnen
In PhpSpreadsheet muss die Datei erst zum Ende des Schreibprozesses angegeben werden ($writer->save('/path/to/target/file')). Openspout öffnet seinen Writer dagegen direkt nach Initialisierung und muss diesen zum Ende hin schließen:
$this->initOpenspout();
$this->_objOpenspoutWriter->openToFile('/path/to/target/file');
// hier werden Daten geschrieben
$this->_objOpenspoutWriter->close();
3. SheetView-Einstellungen vorziehen
Einige Einstellungen müssen schon vor dem Schreiben von Zeilen getroffen werden, während sie bei PhpSpreadsheet auch später hinzugefügt werden können (siehe Dokumentation zu SheetView). Diese müssen also auch in eine Initialisierungsfunktion direkt nach dem Öffnen des Writers rutschen.
4. Schreiben der Zeilen umstellen
Zeilen müssen für Openspout in der Reihenfolge, in der sie auch im Dokument vorkommen, geschrieben werden. Wenn also im existierenden Code Zeilen später befüllt werden, müssen die entsprechenden Aufrufe vorgezogen werden.
Außerdem können Daten nicht mehr zellenweise geschrieben werden, es wird immer eine Reihe auf einmal geschrieben. Für yii2-Export haben wir dafür an vielen Stellen die folgende Umstellung gemacht:
// mit PhpSpreadsheet
foreach ($columns as $column) {
// stile und $value bestimmen
$cell = $sheet->getCell([1, $column])->setValue($value);
}
// mit Openspout
$openspoutCells = [];
foreach ($columns as $column) {
// stile und $value bestimmen
$openspoutCells[] = \OpenSpout\Common\Entity\Cell::fromValue($value);
}
$this->_objOpenspoutWriter->addRow(new \OpenSpout\Common\Entity\Row($openspoutCells));
Findigen Programmierern wird dabei auffallen, dass sich $openspoutCols effizienter per array_map befüllen lässt - in diesem Fall haben wir das nicht gemacht, da yii2-Export weiter auch PhpSpreadsheet unterstützt und die Schleife somit trotzdem notwendig ist.
Je nach Daten kann stattdessen auch \OpenSpout\Common\Entity\Row::fromValues benutzt werden. Dies ist vor allem dann der Fall, wenn die Zellen alle die gleichen Stilinformationen haben.
5. Stilinformationen umstellen
PhpSpreadsheet-Code zum Setzen von Stilen sieht in der Regel wie folgt aus:
$sheet->getStyle($rangeOfColumnsToStyle)->applyFromArray($styleOptions);
Aufgrund des Aufbaus von Openspout funktioniert dies natürlich nicht. Stattdessen müssen Stile entweder einer Zelle oder der ganzen Zeile übergeben werden. Dafür muss ein \OpenSpout\Common\Entity\Style\Style-Objekt erstellt und befüllt werden (siehe die Dokumentation). Ein Äquivalent zu PhpSpreadsheets applyFromArray existiert dabei nicht. Da yii2-Export sowieso Kompatibilität mit PhpSpreadsheets Array-Format behalten sollte, haben wir also eine Funktion erstellt, um aus einem Array in PhpSpreadsheets Format ein Openspout-Style-Objekt zu erstellen. Diese ist zu groß, um sie hier einzufügen, ist aber im OpenspoutHelper von yii2-Export zu finden.
Nach Erstellung eines Style kann dieser dann entsprechend an die Zelle oder die Reihe übergeben werden.
Zu beachten sind hierbei besonders inside- und outline-Border. Diese können wie oben bereits besprochen nicht direkt übernommen werden, hier muss also stattdessen für jede Zelle einzeln der Border-Stil bestimmt werden.
6. Andere Informationen aktualisieren
Optionen wie mergeCells zum Kombinieren mehrerer Zellen oder Excels AutoFilter müssen natürlich auch übernommen werden. Hier ist es am einfachsten, den eigenen Code nach bisher nicht angepassten Stellen zu durchsuchen und in der Openspout-Dokumentation nach einem Äquivalent zu suchen.
Dabei ist zu beachten, dass das Indexing sich zwischen Openspout und PhpSpreadsheet unterscheidet: In PhpSpreadsheet sind sowohl Zeilen als auch Spalten 1-indiziert, d.h. die Zelle A1 hat die Koordinaten [1, 1]. In Openspout sind Spalten 0-indiziert, Zeilen aber 1-indiziert, A1 hat also die Koordinaten [0, 1].
Außerdem muss bei verschiedenen Export-Formaten auch geprüft werden, ob das aktuelle Format die Funktion unterstützt. Das Zusammenführen von Zellen unterstützt beispielsweise nur Excel:
// mergeCells is only supported for Excel!
if ($this->_objOpenspoutOptions instanceof \OpenSpout\Writer\Excel\Options) {
$this->_objOpenspoutOptions->mergeCells(/* parameter */);
}
Dieser Check und der zugehörige Fehler ließe sich vermeiden, wenn alle Options- bzw. Writer-Klassen alle Methoden hätten und diese bei Funktionalitäten, die ihr Dateiformat nicht unterstützt, einfach nichts tun. Diesen Vorschlag haben wir bereits an das Openspout-Team kommuniziert, wir werden also auch diesen Artikel aktualisieren, wenn sich hier etwas tut und dieser Check unnötig wird.
Zusammenfassung
In diesem Artikel haben wir gezeigt, dass PhpSpreadsheet Probleme mit seinem Speicherverbrauch hat. Als Alternative haben wir Openspout vorgestellt und mit PhpSpreadsheet verglichen, danach haben wir noch demonstriert, wie die Migration von PhpSpreadsheet zu Openspout aussieht.
Den Fortschritt der Übernahme von Openspout in yii2-Export kannst du hier einsehen.
Christina Reichel
04.02.2025
