Automatisierte Dokumentenerstellung mit PHPExcel

Teil 3: Erstklassig
Kommentare

Ergebnis pro Jahr: Nachdem die einzelnen Datensätze pro Monat aus der Datenbank in das Dokument geschrieben wurden, wird mittels $objPHPExcel->getActiveSheet()->getRowDimension(’14‘)->setRowHeight(2)eine Zeile in der Höhe angepasst und danach eingefärbt. Das ist unsere Trennlinie für die verdichtete Ansicht in den nächsten zwei Zeilen.

Für die Darstellung der Umsatzzahlen pro Jahr soll eine Abgrenzung nach abgelaufenen Monaten durchgeführt werden. Also ermitteln wir mit $lastMonth= date(„m“, strtotime(„last month“))+1 den vorigen Monat, was zum Zeitpunkt der Erstellung dieses Artikels der Februar war. Da wir die Ausgabe in der zweiten Zeile des Excel-Dokuments begonnen haben, wird der Wert von $lastMonthnach der Ermittlung des Monats um eins erhöht (Listing 3).
// Completed total per last month (without actual month)
$lastMonth= date("m", strtotime("last month"))+1;

$objPHPExcel->getActiveSheet()->setCellValue('C15', 'Summe per Vormonat');
$objPHPExcel->getActiveSheet()->setCellValue('D15', '=SUM(D2:D'.$lastMonth.')');

// Total including actual month
$objPHPExcel->getActiveSheet()->setCellValue('D16', '=SUM(D2:D14)');

// Percentage values
$objPHPExcel->getActiveSheet()->setCellValue('F16', '=(D16/E16)*100');
Mit der Anweisung setCellValue(‚D15‘, ‚=SUM(D2:D‘. $lastMonth.‘)‘) wird nun, anstatt fester Werte wie bei den Datenbanksätzen vorher, eine Excel-Formel in die Zelle D15 eingetragen. Die nächste Formelzeile kommt ohne Variable aus, sie bildet einfach mit setCellValue(‚D16‘, ‚=SUM(D2:D14)‘) die Summe über alle Zeilen. Der beliebte prozentuale Vergleich des aktuellen Jahres mit dem Vorjahr wird über setCellValue(‚F16‘, ‚=(D16/E16)*100‘) aufgebaut. Zum Abschluss wird die Summenzeile mit der gleichen Hintergrundfarbe wie die Datensätze hervorgehoben.

Hübsch machen

Nachdem alle Datensätze eingefügt, die Summenzeilen aufgebaut und die Farben für den Hintergrund definiert wurden, wird vor der Ausgabe noch der letzte Feinschliff durchgeführt. Es beginnt in Listing 4 mit der Angabe der Spaltenbreite. Mittels $objPHPExcel->getActiveSheet()->getColumnDimension(‚C‘)->setWidth(19.27); wird jeder Spalte eine feste Breite vorgegeben. Nach der mitgelieferten Dokumentation orientiert sich die Einstellung an der Breite in Zeicheneinheiten. Eine Zeichenbreite ist dabei die Weite von 0 (Null) in der Standardschrift. Für Anwender mit „deutschem“ MS Excel hat sich nun folgender Ansatz bewährt. Man erstellt sich ein Beispiel in Excel und stellt die Spaltenbreite nach Wunsch ein. Bei dem Verändern wird immer der aktuelle Wert angezeigt, für unsere Spalte C sind das 18,57 Einheiten. Für PHPExcel werden 0,7 Einheiten dazu addiert, es ergibt sich der oben aufgeführte Wert von 19,27 Einheiten. Zahlen müssen hier zwingend mit Punkt und nicht mit Komma angegeben werden. Denn PHPExcel akzeptiert auch Werte mit einem Komma, ohne eine Fehlermeldung auszugeben Das gewünschte Ergebnis wird aber nie eintreten und die Verzweiflung enorm steigen.
// Define the coumn width
// Use dots not comma
// add 0.7 to the value in Excel, e.g. 14.7 becomes 14
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(7);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13.7);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(19.27);
Bei der Betrachtung von größeren Zahlen ist eine Darstellung mit Tausendertrennzeichen gewünscht. Das ist auch schnell gemacht:
$objPHPExcel->getActiveSheet()->getStyle('A2:I16')->getNumberFormat()->setFormatCode('#,##0');
Mit der Anweisung  getStyle(‚D15:I16‘)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); werden alle Zahlen rechtsbündig dargestellt, damit auch unterschiedlich große Zahlen einfacher zu lesen und zu erfassen sind. Es folgen noch weitere Formatierungen für die Kopfzeile wie Zeilenumbruch, die vertikale Zentrierung, Hintergrundfarbe und ein Rahmen. Als Letztes wird in diesem Schritt das aktuelle Register auf „CRM“ umbenannt und als aktiv markiert. Hierdurch wird beim Öffnen mit MS Excel direkt dieses Register angezeigt.

Ergebnis aushändigen

Nun gilt es, dem Anwender das Resultat im gewünschten Format zu präsentieren. Im Rahmen unserer „Fernsteuerung“ sind verschiedene Ausgaben eingeplant worden:
  • Excel 2007 mit der Dateiendung .xlsx ist das Standardformat in PHPExcel.
  • Excel 5 ist das „alte“ Dateiformat xls und basiert auf einer modifizierten Version des PEAR Spreadsheet_Excel_Writer mit den gleichen Funktionen und Grenzen; Excel 5 wird nicht weiter entwickelt und ergänzt nur die Ausgabeformate in PHPExcel.
  • PDF rundet als verbreitete Dokumentart das Beispiel der Downloads ab. Die Ausgabe im PDF ist nicht optimal, es werden in jeder Zelle mit Inhalt schwarze Bereiche angezeigt.
  • HTML ermöglicht die direkte Ausgabe des Excel-Dokuments im Browser.
In der eigenen Applikation könnten die Anwender in einer Listen- oder Detailansicht von Kunden mit den entsprechenden Icons das gewünschte Format auswählen und sich anzeigen lassen. Anstelle der direkten Downloads ist es auch möglich, eine Datei auf dem Server zu erstellen. Die Vorgehensweise wird in den mitgelieferten Beispielen demonstriert. Diese Datei kann dann in andere Anwendungen wie ein CRM oder DMS importiert werden. Für die Handhabung all dieser verschiedenen Ausgaben wird die PHPExcel_IOFactory angeboten. In Listing 5 wird in der Switch-Abfrage nach dem gewünschten Format unterschieden, zudem werden Variablen für die Ausgabe vorbelegt.
switch ($outputFormat) {
case 'HTML':
// no variables need to be defined
break;
case 'Excel2007':
$contentType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
$fileExtension='xlsx';
break;
case 'Excel5':
$contentType='application/vnd.ms-excel';
$fileExtension='xls';
break;
default:
$outputFormat='PDF';
$contentType='application/pdf';
$fileExtension='pdf';
}

// Direct output to the browser
if($outputFormat=='HTML'){
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
echo $objWriter->generateHTMLHeader();

// Individual style setting
echo '
generateStyles(false); // do not repeat  & 
// close style
echo '-->';
echo $objWriter->generateSheetData();
echo $objWriter->generateHTMLFooter();
}
// Redirect output to a client's web browser using the predefined outputFormat
else{
header('Content-Type: ' . $contentType);
header('Content-Disposition: attachment;filename="Umsatzauswertung.' . $fileExtension . '"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFormat);
$objWriter->save('php://output');
}
Bei der Vorauswahl HTML wird direkt das Ergebnis im Browser ausgegeben. Für eine vergleichbare Formatierung wie in Excel können allgemeine Style-Angaben im CSS-Format benutzt werden. Diese werden von PHPExcel durch den Methodenaufruf $objWriter->generateStyles(false) um die vorher definierten Spezifikationen unseres Excel-Dokuments ergänzt. Die eigenen Einstellungen können auch entfallen, dann ist $objWriter->generateStyles(true) zu nutzen. Durch die Umstellung des Parameters auf true werden die notwendigen Style-Tags für HTML direkt mit ausgegeben. Da sowohl HTML als auch PDF die Excel-Formeln nicht interpretieren können, berechnet PHPExcel die Ergebnisse für die Ausgabe in diesen Medien vor. Das ist praxisorientiert, da es dem Entwickler erspart, Sonderimplementierungen für diese Formate umsetzen zu müssen.
Abb. 2: Ohne Mehraufwand werden auch im Browser die Formatierungen wie in MS Excel angezeigt

Abb. 2: Ohne Mehraufwand werden auch im Browser die Formatierungen wie in MS Excel angezeigt

  Für die Ausgaben als Excel 2007, Excel 5 und PDF wird der Browser mit den Header-Informationen wie Inhaltstyp und Dateiname auf die kommende Datei vorbereitet:
header('Content-Type: ' . $contentType);
header('Content-Disposition: attachment;filename="Umsatzauswertung.' . $fileExtension . '"');
header('Cache-Control: max-age=0');
Die finale Ausgabe erfolgt über die PHPExcel_IOFactory:
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFormat);
$objWriter->save('php://output');
Das war’s! Die Erstellung eines kundenspezifischen Dokuments mit Datenbankinhalten in den gewünschten Formatierungen und verschiedenen Ausgabeformaten ist fertig und kann durch den Anwender genutzt werden.
Abb. 3: Musterschüler: neben den Farben werden auch Spaltenbreiten und Formeln nach Vorgabe umgesetzt

Abb. 3: Musterschüler: neben den Farben werden auch Spaltenbreiten und Formeln nach Vorgabe umgesetzt

  Das erzeugte PDF ist durch die Darstellung der schwarzen Flächen nicht optimal, dafür kann es mit dem weit verbreiten Acrobat Reader, Foxit Reader u. a. direkt ohne installiertes MS Excel geöffnet werden. Aktuell setzt PHPExcel noch auf die Bibliothek tcPDF, die sich neben der verfälschten Darstellung auch nicht gerade durch Geschwindigkeit oder geringen Speicherverbrauch auszeichnet. Aber es gibt Überlegungen der Entwickler, in Zukunft auf andere Komponenten wie DomPDF oder mPDF zu setzen. Mehr dazu finden Sie unter http://phpexcel.codeplex.com/discussions/247901.

Fazit

PHPExcel demonstriert als erste der drei Klassen für die automatisierte Dokumentenerstellung ihre enorme Leistungsfähigkeit. Strukturiert und übersichtlich können MS-Excel-Dokumente in zahllosen Ausprägungen erstellt werden. Unser Beispiel hat einige Möglichkeiten für die Metadaten, Druckoptionen, Schriftformatierungen und verschiedene Ausgabeformate aufgezeigt. Es sind aber noch viel mehr Optionen in dieser flexiblen Komponente möglich. An dieser Stelle sei noch einmal auf die gute Beispielsammlung hingewiesen, die beim Download von PHPExcel mitgeliefert wird. Der beachtliche Funktionsumfang in Kombination mit der großen Flexibilität bei der Integration macht PHPExcel wohl auch so beliebt, für zahlreiche Systeme wie Joomla, CakePHP oder Typo3 existieren bereits Implementierungen. PHPExcel macht Lust auf mehr. Gespannt schauen wir deshalb auf die Geschwister PHPWord und PHPPowerPoint, die wir in den nächsten Ausgaben besprechen werden. Alle Teile: Teil 1, Teil 2, Teil 3
Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -