Willkommen zu meinem ersten Artikel. Heute möchte ich euch erklären, wie ihr mithilfe von PHP, ein wenig JavaScript und einer Excel-Tabelle eine PDF-Reihenbrief-Datei erstellen könnt.

Was genau machen wir heute?

Folgendes Problem: wir haben eine Excel-Tabelle mit zwei Tabellen-Blättern. Tabellenblatt 01 heißt „Essensangebot“ und Tabellenblatt 02 heißt „Bestellungen“. Aus diesen zwei Tabellenblättern sollen für jeden eingetragenen Nutzer eine Rechnung in Form einer PDF erstellt werden. Auf dieser Rechnung befinden sich dann das ausgewählte Menu, der Preis pro Tag und der Gesamtpreis. In Essensangebot befindet sich in Spalte A die Kennung des Tages mit jeweils drei verschiedenen Variationen (z.B. Mo1 oder Fr3). In Spalte B befinden sich das festgelegte Menu und in Spalte C findet sich der Preis für das entsprechende Menu.

Auf dem zweiten Tabellenblatt namens „Bestellungen“ befinden sich in Spalte A der Nachname und in Spalte B der Vorname. In Spalte C bis G befindet sich die jeweilige Bestellung für den Tag. Angegeben wird diese mit der Kennung aus Spalte A des ersten Tabellenblattes. Dabei ist bereits jetzt darauf zu achten, welche Probleme später auftreten könnten: Groß-/Kleinschreibung falsch? Leerzeichen innerhalb der Kennung? Unbekannte Kennungen? Alle diese potentiellen Fehlerquellen müssen wir später im Code abfangen.

Gut, jetzt wo wir einen Überblick über die uns vorliegenden Daten haben, was machen wir mit ihnen?

Jetzt gäbe es natürlich mehrere Lösungsansetze:

• Absolut verzweifeln und hoffen, dass jemand anderes die Aufgabe schon lösen wird
• Mithilfe von Formeln, die Aufgabe in Word und Excel lösen und dabei merken wie unglaublich teuer Office-Lizenzen sind
• oder natürlich der logischste Lösungsansatz: man programmiert sich seinen eigenen kleinen Parser

Gut, jetzt wo wir uns hoffentlich alle einig sind, dass es Option drei wird, was genau soll der Parser können und wie soll er funktionieren?

Der Parser

Der Parser sollte eine Bedienfläche haben, welche dem Nutzer ermöglicht, eine Excel-Datei auszuwählen. Diese Datei sollte danach vom Parser verarbeitet werden und am Ende sollte der Parser mithilfe von Hexerei und Magie ganz zufällig einen halbwegs guten Serien- bzw. Reihenbrief ausspucken. Bis hier hin klingt das ganze auch noch ganz simpel…

Also erstellt man als erstes eine kleine Ordnerstruktur mit folgenden Datein und Ordnern:

Hätten wir das also auch geklärt. Jetzt lasst uns ins Detail gehen. Wie macht der Parser aus ein paar Spalten in einer Tabelle nun einen Brief als PDF? Nachdem der Benutzer eine Datei ausgewählt hat, wird diese temporär auf dem Server hochgeladen und kurzzeitig gespeichert. Nach dem die Datei erfolgreich hochgeladen wird, versucht der PHP-Skript die verschiedenen Daten aus der Excel-Tabelle zu extrahieren und in eine MySQL-Datenbank zu übertragen. Hierbei ist wichtig, am besten direkt mögliche Fehleingaben, wie oben genannt, zu korrigieren indem beispielsweise Leerzeichen entfernt werden. Außerdem sollte es einen Fallback geben, wenn an einem Tag nichts bestellt wird oder eine ungültige Tageskennung angegeben wird. Aber warum werden die Daten überhaupt in eine MySQL-Datenbank extrahiert? Mit SQL lässt es sich in späteren Schritten einfacher arbeiten als mit der Excel-Datei. Allein die Excel-Datei einmalig zu parsen ist der reinste Horror… und das dann jedes Mal erneut zu machen? Mag man gar nicht dran denken!

Aus den Daten wird ein Brief

Perfekt! Die Daten wurden also aus der Excel-Tabelle extrahiert und liegen uns jetzt als MySQL-Datenbank vor. Damit sollte es jetzt einfacher sein zu arbeiten. Nun erstellen wir den Brief aus den gegeben Daten. In diesem Beispiel erstellen wir eine einzelne PDF, welche für jeden Nutzer eine eigene Seite hat. Nach dem gleichen Prinzip wäre es aber natürlich auch möglich, für jeden Benutzer eine eigene PDF zu erstellen und diese beispielsweise als ZIP-Datei herunterzuladen.

Wir erstellen also eine foreach-Schleife, welche über alle Bestellungen (also Tabellenblatt 2 „Bestellungen“) in der MySQL-Datenbank loopt und uns immer eine Seite erstellt.

$stmt = $db->prepare('SELECT * FROM bestellungen');
$stmt->execute();
$bestellungen = $stmt->fetchAll();

foreach ($bestellungen as $bestellung){
  // Erstelle eine Seite in der PDF

Als nächstes müssen wir ein paar Variablen festlegen, welche wir für den Brief benötigen. Dafür setzen wir als erstes die Brutto-Summe zurück auf 0 damit der Wert der Vorgängerseite nicht übernommen wird. Danach erstellen wir noch das aktuelle Datum für den Brief. Nicht zu vergessen ist, den Briefkopf zu definieren und mit den benötigten Informationen zu füllen.

$brutto = 0;
$date = date('d.m.Y');

$letter = '
            <style>
                table {
                    border-collapse: collapse;
                    width: 100%;
                }
                td, th {
                    border: 1px solid black;
                    padding: 5px;
                }
            </style>
            <h1 style="margin-bottom: 0px;">Rechnung (# ' . $bestellung['id'] . ')</h1>
            <i class="display: block;">Rechnungsdatum: ' . $date . '</i>
            <br>
            <table>
                <tr>
                    <td style="font-weight: bold;">Tag</td>
                    <td style="font-weight: bold;">Menu</td>
                    <td style="font-weight: bold;">Preis</td>
                </tr>
            ';

Jetzt geht’s an’s Eingemachte: Wir erstellen fünf if-Schleifen. Eine if-Schleife pro Tag. Diese sollen prüfen, ob die Bestellung für den entsprechenen Kunden an diesem Tag leer ist und diesen Tag dann überspringen. Innerhalb der if-Schleife soll dann in der Datenbank Essensangebot (also Tabellenblatt 1 „Essensangebot“) nachschauen, was das Menü für den entsprechenen Tag ist und den Preis des Menüs zum Brutto-Preis der Woche addieren.

// Bestellung für beispielsweise Freitag
if ($bestellung['freitag'] != 'none') {
    $stmt = $db->prepare('SELECT * FROM essensangebot WHERE tag = ?');
    $stmt->execute(array($bestellung['freitag']));
    $angebot = $stmt->fetch();

    $letter = $letter . '
                    <tr>
                        <td>Freitag</td>
                        <td>' . $angebot['menu'] . '</td>
                        <td>' . $angebot['preis'] . ' €</td>
                    </tr>
                ';

    $brutto = $brutto + $angebot['preis'];
}

Als nächstes müssen wir noch ein bisschen Mathe machen. Ich weiß, ist doof, müssen wir jetzt aber durch. Wir berechnen also noch die Netto-Summe (also die Summe ohne Steuern) indem wir die Brutto-Summe (dementsprechend die Summe mit Steuern) durch 1,19 teilen da unsere Mehrwertsteuer in diesem Fall bei 19 % liegt. In der Theorie könnten hier auch nur 7 % anfallen, da es sich um Lebensmittel handelt. Aber da fragt ihr lieber euren Steuerberater und nicht irgendwen aus dem Internet der auf seinem Blog versucht zu erklären, wie man Briefe aus Excel-Tabellen bastelt… Anyway, back to topic: am Schluss berechnen wir noch die eben genannte Mehrwertsteuer indem wir die Netto-Summe von der Brutto-Summe abziehen. Diese Differenz ist unsere gesuchte Mehrwertsteuer. Keine Sorge, viel mehr Mathe kommt nicht! Damit alles ein bisschen mehr ✨beautiful✨ aussieht, runden wir alle entstanden Summen nochmal auf zwei Nachkommastellen. Das hat den praktischen Nutzen, dass wir beim Bezahlen keine Cent-Stücke zersägen müssen.

$netto = $brutto / 1.19;
$mwst = $brutto - $netto;
$mwst = round($mwst, 2);
$brutto = round($brutto, 2);
$netto = round($netto, 2);

Am Schluss hängen wir dem Brief noch den Brief-Footer an. Dieser enthält die Netto- und Bruttosumme sowie den Mehrwertsteuerbetrag. Außerdem könnte man da noch so eine kleine Verabschiedung oder so reinbasteln. Da ist eure Kreativität bestimmt grenzenlos. Meine nicht. Danach schreiben wir den Seiteninhalt in die PDF und erstellen eine neue Seite innerhalb der Datei.

<?php
$letter = $letter .
    '
        </table>
        <br>
        <table>
            <tr>
                <td>Netto</td>
                <td>' . $netto . ' €</td>
            </tr>
            <tr>
                <td>Mwst (19 %)</td>
                <td>' . $mwst . ' €</td>
            </tr>
            <tr>
                <td>Fälliger Betrag</td>
                <td>' . $brutto . ' €</td>
            </tr>
        </table>
        <br><br>
        <p>
            Mit freundlichen Grüßen<br>
            <br>
            <b>Max Mustermann</b><br>
            <i>Essenslieferant</i>
        </p>
    ';

$mpdf->WriteHTML($letter);
$mpdf->AddPage();

Sobald die foreach-Schleife beendet ist, wird die PDF-Datei auf dem Server gespeichert und der Downloadlink dem Benutzer in der App bereitgestellt.

Sicherheit und Datenschutz

Da bei diesem Skript mit möglicherweise echt sensiblen Kundendaten gearbeitet wird, muss man hierbei sehr auf den Datenschutz und die Sicherheit der hochgeladenen Daten achten. Da mein Skript lediglich für Testzwecke dient, halten sich die Sicherheitsmechanismen in Grenzen. Ein paar habe ich jedoch implementiert und möchte diese gerne erklären.

Sobald ein neuer Benutzer die App öffnet, wird die komplette MySQL-Datenbank sowie der /tmp-Ordner gelöscht. Dadurch wird sichergestellt, dass folgende Benutzer keinen Zugang zu den Daten der vorherigen Nutzer haben.

Eine weitere Sicherheitfunktion wäre, dass jeder Download-Link nur einmal funktioniert. Sobald die PDF-Datei einmal heruntergeladen wurde, wird sie automatisch vom Server gelöscht und der Download-Link somit ungültig. Außerdem bestehen die Datei-Namen aus einem zufälligen 32-bytes langem Namen. Diese sollen es erschweren, die Dateinamen durch raten herauszufinden.

Fazit

Wahrscheinlich hätte es unzählige andere Wege gegeben diese Aufgabe zu lösen. Wahrscheinlich wären viele von diesen auch einfacher gewesen als meine Umsetzung. Allerdings war das, die für mich am effektivsten und nutzerfreundlichste Lösung. Die entstandene App ist für jeden leicht zu verwenden und Anwender müssen nicht extra Excel-Macros bauen oder so.

Ich hoffe mein kleiner Exkurs in die Welt der Excel-Tabellen hat euch gefallen und das Lesen war nicht allzu anstrengend. Das hier ist mein erster Blog-Artikel und ich freue mich gerne über Feedback von euch in den Kommentaren! 😀

Download

Natürlich könnt ihr den Source-Code inklusive einer Demo-Exceltabelle kostenlos herunterladen. Ausprobieren könnt ihr die entstandene App auch online unter essensbestellung.kurtiii.de.