Exportieren von MySQL Tabellen in Open Document Spreadsheet (ODS) Dateien mit PHP

Vor kurzem stand ich vor der Aufgabe in einer meiner Softwarelösungen die Tabellen-Exportfunktion zu überarbeiten. Bis dahin verwendete ich einen einfachen pseudo-XLS Export der die Daten als formatierte Textdatei ausgibt die eine .xls Endung bekam. Microsoft Excel ließt solche Dateien problemlos ein. Allerdings war die Handhabung nicht gerade optimal, da keine Formatierungsdaten in der Tabelle liegen. Lange Zahlen wie EAN-Nummern oder Preise wurden durch die Autoformatierung nicht wie gewollt dargestellt und man musste manuell nacharbeiten.
Ich forschte dann nach alternativen Dateiformaten und die Wahl viel dann schnell auf Open Document. Großer Vorteil hierbei ist das es ein offenes Format ist und somit gut dokumentiert. Zudem ist es mit den gängigen Office Anwendungen kompatibel, wie z.B. Microsoft Excel ab Version 2007 oder Oracle Open Office.

Benötigte Software:
Zuerst solle geprüft werden ob das Zip PHP Modul aktiviert ist. Hier hilft ein Blick in phpinfo().

Für die Arbeit am eigenen Rechner benötigt man, außer der Office Umgebung, keine weitere Software. Hier reichen die Hausmittel des eigenen Betriebssystems.
Ich selbst verwende Open Office für die Tabellenkalkulation und Notepad++ für PHP und alles andere was in Textform ist.

Der Aha-Effekt
Wer sich noch nie mit Open Document befasst hat steht natürlich erst einmal vor der Fragestellung wie die Datei aufgebaut ist. Man möchte ja schließlich seine Daten dort unterbringen.
Hierzu empfehle ich einfach eine Tabelle in der Office Umgebung zu erstellen und als *.ods zu speichern. Danach geht man in das Verzeichnis in dem die Datei liegt und ändert die Datei-Endung auf “zip”. Anschließend entpackt man die Datei und schon offenbart sich das Geheimnis: XML-Dateien.

Es geht also nur darum die Daten aus der MySQL Abfrage in XML Dateien zu schreiben und das Ganze anschließend mit ZIP zu verpacken.

Zur Sache
Von den ganzen Dateien welche die Tabellenkalkulation in die *.ods Datei legt bleiben in unserem Fall nur drei übrig:

  • mimetype – in der nur steht das es sich hierbei um eine ODS Datei handelt,
  • manifest.xml – die festlegt in welcher Datei innerhalb der ODS was liegt, und die
  • content.xml – in der sich die Daten befinden.

Die Funktions-Deklaration in PHP sieht wie folgt aus.

function exportODF($data, $fields, $name)

$data ist das MySQL Abfrageergebnis, also der Rückgabewert der Funktion mysql_query().
$fields ist ein Array mit den Feldnamen (Spalten) der MySQL Tabelle die exportiert werden sollen sowie deren sichtbare Bezeichnung in der exportierten Tabelle. Ein Beispiel wäre:

$fields = array('fname' => 'Vorname', ' lname' => 'Nachname', 'street' => 'Straße', 'city' => 'Ort');

Hierbei werden die Datenbankfelder `fname`, `lname`, `street` und `city` aus $data exportiert und in der ODS Tabelle mit den Bezeichnungen “Vorname”, “Nachname”, “Starße” und “Ort” angezeigt.
$name wäre dann noch der Name der zu erzeugenden Datei.

Nachdem wir geprüft haben ob die übergebenden Variablen auch Daten enthalten können wir damit beginnen die Datei zu erzeugen. Dafür holen wir uns am besten erst einmal einen Dateinamen:

if(!($file = tempnam("/tmp", "zip")))
{
return FALSE;
}

Danach holen wir das Handle für die ZIP-Datei und versuchen die Datei zu erzeugen:

$zip = new ZipArchive;
if(!$zip->open($file, ZipArchive::CREATE|ZipArchive::OVERWRITE))
{
return FALSE;
}

Jetzt haben wir eine temporäre ZIP Datei mit zufälligem Namen ($file) im Server-Verzeichnis /tmp erzeugt und können Daten in das Archiv legen.
Fangen wir jetzt mit den einfachen Sachen an. Wie oben beschreiben müssen drei Dateien in das Archiv, von denen zwei immer den gleichen Inhalt haben. Es bietet sich also an das gleich zu erledigen:

if(!$zip->addEmptyDir("META-INF"))
{
return FALSE;
}
if(!$zip->addFromString("META-INF/manifest.xml", '<?xml version="1.0" encoding="UTF-8"?><manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0"><manifest:file-entry manifest:media-type="application/vnd.oasis.opendocument.spreadsheet" manifest:version="1.2" manifest:full-path="/"/><manifest:file-entry manifest:media-type="application/vnd.sun.xml.ui.configuration" manifest:full-path="Configurations2/"/><manifest:file-entry manifest:media-type="text/xml" manifest:full-path="content.xml"/></manifest:manifest>'))
{
return FALSE;
}
if(!$zip->addFromString("mimetype", "application/vnd.oasis.opendocument.spreadsheet"))
{
return FALSE;
}

Was passiert hier:
Zuerst möchte ich die Datei manifest.xml erstellen. Da diese aber in dem Unterverzeichnis “META-INF” liegt muss ich dieses zuerst mithilfe von ZipArchive::addEmptyDir() anlegen. Danach wird die Datei mit addFromString(“META-INF/manifest.xml”, [Inhalt]) in diesem Unterverzeichnis erstellt.
Die Funktion ZipArchive::addFromString() bietet mir dabei den Vorteil die Datei nicht vorher extra anlegen zu müssen um sie danach dem ZIP Archiv hinzu zu fügen. Ich kann den Dateiinhalt direkt als Zeichenkette der Funktion übergeben.
Das Gleiche passiert dann auch bei der mimetype Datei, die aber wieder direkt im ZIP Archiv liegt. (ohne Unterverzeichnis)

Zum Schluss die eigentliche Arbeit, die content.xml. Zunächst der statische Kopf:

$buffer = '<?xml version="1.0" encoding="UTF-8"?>'
.'<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:presentation="urn:oasis:names:tc:opendocument:xmlns:presentation:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:grddl="http://www.w3.org/2003/g/data-view#" xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0" office:version="1.2" grddl:transformation="http://docs.oasis-open.org/office/1.2/xslt/odf2rdf.xsl">'
.'<office:scripts/>'
.'<office:font-face-decls>'
.'<style:font-face style:name="Arial" svg:font-family="Arial" style:font-family-generic="swiss" style:font-pitch="variable"/>'
.'<style:font-face style:name="Lucida Sans Unicode" svg:font-family="&apos;Lucida Sans Unicode&apos;" style:font-family-generic="system" style:font-pitch="variable"/>'
.'<style:font-face style:name="Mangal" svg:font-family="Mangal" style:font-family-generic="system" style:font-pitch="variable"/>'
.'<style:font-face style:name="Tahoma" svg:font-family="Tahoma" style:font-family-generic="system" style:font-pitch="variable"/>'
.'</office:font-face-decls>'
.'<office:automatic-styles>'
.'<style:style style:name="co1" style:family="table-column">'
.'<style:table-column-properties fo:break-before="auto" style:column-width="2.9cm"/>'
.'</style:style>'
.'<style:style style:name="ro1" style:family="table-row">'
.'<style:table-row-properties style:row-height="0.453cm" fo:break-before="auto" style:use-optimal-row-height="true"/>'
.'</style:style>'
.'<style:style style:name="ta1" style:family="table" style:master-page-name="Default">'
.'<style:table-properties table:display="true" style:writing-mode="lr-tb"/>'
.'</style:style>'
.'</office:automatic-styles>'
.'<office:body>'
.'<office:spreadsheet>'
.'<table:table table:name="Tabelle1" table:style-name="ta1" table:print="false">'
.'<office:forms form:automatic-focus="false" form:apply-design-mode="false"/>';

Im Kopf wird der allgemeine Aufbau der Tabelle bekannt gegeben, wie z.B. Zeilenhöhe und Spaltenbreite. Bei dieser Funktion werden alle Zellen gleich groß sein. Danach werden die Spalten angelegt, das $fields Array bestimmt die Anzahl:

foreach($fields AS $value)
{
$buffer .= '<table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>';
}

Weiter geht es mit der ersten Zeile der Tabelle in der die Spalten-Überschriften stehen. (die Werte aus dem $fields Array)

foreach($fields AS $value)
{
$buffer .= '<table:table-cell office:value-type="string">'
.'<text:p>'.utf8_encode($value).'</text:p>'
.'</table:table-cell>';
}
$buffer .= '</table:table-row>';

Da XML mit der UTF-8 Textkodierung arbeitet muss der Inhalt von Variablen die eingefügt werden sollen auch in UTF-8 kodiert werden. Es klappt zwar meist auch ohne, doch spätestens wenn mal ein Umlaut enthalten ist kann man die ODS Datei nicht mehr öffnen. Viel Spaß bei der folgenden Fehlersuche.

Was nun folgt ist die Hauptschleife welche die Daten aus der MySQL Abfrage in die ODS Tabelle schreibt:

while($row = mysql_fetch_assoc($data))
{
$buffer .= '<table:table-row table:style-name="ro1">';
foreach($fields AS $key => $value)
{
if(preg_match("/^[0-9]+$/", $row[$key]))
{
$buffer .= '<table:table-cell office:value-type="float" office:value="'.$row[$key].'">'
.'<text:p>'.$row[$key].'</text:p>'
.'</table:table-cell>';
}else if(preg_match("/^[0-9]+\.[0-9]+/", $row[$key])){
$buffer .= '<table:table-cell office:value-type="float" office:value="'.sprintf("%01.2f", $row[$key]).'">'
.'<text:p>'.str_replace(".", ",", sprintf("%01.2f", $row[$key])).'</text:p>'
.'</table:table-cell>';
}else{
$buffer .= '<table:table-cell office:value-type="string">'
.'<text:p>'.htmlspecialchars(utf8_encode(stripslashes($row[$key]))).'</text:p>'
.'</table:table-cell>';
}
}
$buffer .= '</table:table-row>';
}

Die while Schleife geht dabei Zeile für Zeile durch das MySQL Abfrageergebnis, mit foreach werden die Werte aus den Zellen gezogen, indem die Schlüssel des $fields Array als Referenz benutzt werden.
htmlspecialchars() wird dabei verwendet um XML Schlüsselzeichen wie “&”, “<” oder “>” aus den Variablen unkenntlich zu machen, da sonst wieder Fehler beim Lesen der Datei auftreten.
Außerdem unterscheide ich noch zwischen ganzen Zahlen, Fließkomma-Zahlen sowie Strings und gebe das Format dieser Werte der ODS Tabelle bekannt. Wer das nicht möchte kann auch nur den Code aus dem else{} Block verwenden und alles als String einfügen.

Jetzt muss die XML Datei nur noch geschlossen,

$buffer .= '</table:table>'
.'</office:spreadsheet>'
.'</office:body>'
.'</office:document-content>';

der gesamte Text als content.xml Datei in das ZIP Archiv geschrieben

if(!$zip->addFromString("content.xml", $buffer))
{
return FALSE;
}

und das Archiv geschlossen werden:

$zip->close();

Damit ist die Arbeit für die ZipArchive Klasse erledigt und wir können auf die Datei mit hilfe der $file Variable zugreifen.
In meinem Fall schicke ich sie als direkten Download an den User.

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$name.".ods");
header("Pragma: no-cache");
header("Expires: 0");
readfile($file);
unlink($file);
return TRUE;

Das war es schon … fast
Die Funktion ist natürlich auf die Softwarelösung zugeschnitten in der sie implementiert wurde. Wer andere Anforderungen hat sollte dennoch eine gute Basis haben. So ließen sich z.B. mit den Funktionen mysql_num_fields() und mysql_field_name() Schleifen bauen die das gesamte MySQL Ergebnis in der ganzen Breite ausgeben. Wer sich weiter mit den XML Eigenschaften des ODS Formates befasst kann auch dynamisch Bilder einfügen, welche dann in ein Unterverzeichnis gelegt und in der manifest.xml bekannt gemacht werden müssen. Kurzum man hat hier die volle Kontrolle über die Ausgabe.

This entry was posted in PHP and tagged , , , , , , , . Bookmark the permalink.

One Response to Exportieren von MySQL Tabellen in Open Document Spreadsheet (ODS) Dateien mit PHP

  1. asdf says:

    Kleiner Fehler in deiner hervorragenden Erklärung ist mir aufgefallen.

    Vor

    foreach($fields AS $value)
    {
    $buffer .= ”;
    }

    muss noch ein (zumindest bei mir):
    $buffer .= ”;

    Sonst kommt ein Fehler da er die Tabelle anscheinend schliesst, da du nach der FOR Schleife die table-row zumachst.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s