Entladen Sie Daten in Excel. Zivilisiert

In der IT-Branche gibt es Aufgaben, die vor dem Hintergrund von Erfolgen bei Big Data , maschinellem Lernen , Blockchain und anderen Modetrends völlig unattraktiv erscheinen, aber seit Jahrzehnten für eine ganze Armee von Entwicklern nicht mehr relevant sind. Es wird sich um die alte Weltaufgabe handeln, Excel-Dokumente zu erstellen und hochzuladen, mit der jeder konfrontiert war, der jemals Bewerbungen für Unternehmen geschrieben hat.





Welche Möglichkeiten zum Erstellen von Excel-Dateien gibt es grundsätzlich?

1. VBA-Makros. Heutzutage ist die Idee, Makros zu verwenden, aus Sicherheitsgründen meistens nicht geeignet.
2. Excel-Automatisierung mit einem externen Programm über die API. Erfordert Excel auf demselben Computer wie das Programm, das Excel-Berichte generiert. Zu einer Zeit, als Clients dick waren und in Form von Desktop-Windows-Anwendungen geschrieben wurden, war diese Methode geeignet (obwohl sie sich nicht in Geschwindigkeit und Zuverlässigkeit unterschied). In der heutigen Realität ist dies kaum ein erreichbarer Fall.
3. Generieren Sie eine XML-Excel-Datei direkt. Wie Sie wissen, unterstützt Excel das XML-Format zum Speichern eines Dokuments, das möglicherweise mit allen Methoden zum Arbeiten mit XML generiert / geändert werden kann. Diese Datei kann mit der Erweiterung .xls gespeichert werden, und obwohl es sich streng genommen nicht um eine xls-Datei handelt, öffnet Excel sie gut. Dieser Ansatz ist sehr beliebt, aber die Nachteile umfassen die Tatsache, dass jede Lösung, die auf der direkten Bearbeitung des XML-Excel-Formats basiert, ein einmaliger „Hack“ ohne Allgemeingültigkeit ist.
4. Schließlich ist es möglich, Excel-Dateien mit Open-Source-Bibliotheken zu generieren, von denen der Apache-POI besonders bekannt ist. Apache POI-Entwickler haben eine titanische Arbeit an Reverse Engineering von binären MS Office-Dokumentformaten geleistet und pflegen und entwickeln diese Bibliothek im Laufe der Jahre weiter. Das Ergebnis dieses Reverse Engineering wird beispielsweise in Open Office verwendet, um die Aufbewahrung von Dokumenten in mit MS Office kompatiblen Formaten zu implementieren.

Meiner Meinung nach ist dies die letzte Methode, die jetzt zum Generieren von MS Office-kompatiblen Dokumenten bevorzugt wird. Zum einen muss keine proprietäre Software auf dem Server installiert werden, zum anderen wird eine umfangreiche API bereitgestellt, mit der Sie alle Funktionen von MS Office nutzen können.

Die direkte Verwendung von Apache POI hat jedoch seine Nachteile. Erstens ist dies eine Java-Bibliothek, und wenn Ihre Anwendung in mehr als einer der JVM-Sprachen geschrieben ist, können Sie sie kaum verwenden. Zweitens handelt es sich um eine Bibliothek auf niedriger Ebene, die mit Konzepten wie "Zelle", "Spalte", "Schriftart" arbeitet. Daher wird das "schriftliche Stirn" -Verfahren zum Generieren eines Dokuments schnell zu einer reichlichen "Nudel" von schwer lesbarem Code, bei der es keine Trennung in ein Datenmodell und eine Präsentation gibt, es schwierig ist, Änderungen vorzunehmen, und im Allgemeinen Schmerzen und Scham. Und eine hervorragende Gelegenheit, die Aufgabe an den unerfahrensten Programmierer zu delegieren - lassen Sie ihn auswählen.

Aber es könnte ganz anders sein. Das Xylophone- Projekt im Rahmen der LGPL, das auf der Grundlage des Apache POI erstellt wurde, basiert auf einer Idee mit einer geschätzten 15-jährigen Geschichte. In den Projekten, an denen ich teilgenommen habe, wurde es in Kombination mit einer Vielzahl von Plattformen und Sprachen verwendet - und aufgrund der Vielzahl von Formen, die mit seiner Hilfe in einer Vielzahl von Projekten erstellt wurden, gibt es wahrscheinlich bereits Tausende. Dies ist ein Java-Projekt, das sowohl als Befehlszeilenprogramm als auch als Bibliothek verwendet werden kann (wenn Sie Code in der JVM-Sprache haben, können Sie ihn als Maven-Abhängigkeit verbinden).

Xylophone implementiert das Prinzip, das Datenmodell von seiner Darstellung zu trennen. Beim Hochladen müssen Sie Daten im XML-Format generieren (ohne sich um Zellen, Schriftarten und Trennlinien kümmern zu müssen). Xylophone generiert mithilfe einer Excel-Vorlage und eines Deskriptors, der beschreibt, wie Ihre XML-Datei mit Daten gecrawlt wird, das Ergebnis wie in der Abbildung dargestellt:


Die Dokumentvorlage (xls / xlsx-Vorlage) sieht ungefähr so ​​aus:


Die Beschaffung einer solchen Vorlage erfolgt in der Regel durch den Kunden. Der betroffene Kunde beteiligt sich gerne an der Erstellung der Vorlage: Er beginnt mit der Auswahl des richtigen Formulars aus dem "Berater" oder erfindet eines von Grund auf neu und endet mit der Schriftgröße und -breite der Trennlinien. Der Vorteil der Vorlage besteht darin, dass kleinere Änderungen auch dann leicht vorgenommen werden können, wenn der Bericht vollständig entwickelt ist.

Wenn die "Design" -Arbeit erledigt ist, bleibt der Entwickler

1. Erstellen Sie eine Prozedur zum Herunterladen der erforderlichen Daten im XML-Format.
2. Erstellen Sie einen Deskriptor, der das Verfahren zum Durchlaufen von Elementen einer XML-Datei und zum Kopieren von Vorlagenfragmenten in den resultierenden Bericht beschreibt
3. Binden Sie Vorlagenzellen mithilfe von XPath- Ausdrücken an Elemente einer XML-Datei.

Beim Hochladen in XML ist alles mehr oder weniger klar: Wählen Sie einfach die entsprechende XML-Darstellung der Daten aus, die zum Ausfüllen des Formulars erforderlich sind. Was ist ein Deskriptor?

Wenn das von uns erstellte Formular keine doppelten Elemente mit unterschiedlichen Nummern enthält (z. B. Rechnungszeilen, die sich in verschiedenen Rechnungen unterscheiden), sieht der Deskriptor folgendermaßen aus:

<element name="root"> <output range="A1:Z100"/> </element> 

Hier ist root der Name des root-Elements unserer XML-Datendatei, und Bereich A1: Z100 ist der rechteckige Bereich von Zellen aus der Vorlage, die in das Ergebnis kopiert werden. Wie aus der vorherigen Abbildung ersichtlich, haben Platzhalterfelder, deren Werte durch Daten aus einer XML-Datei ersetzt werden, das Format ~{XPath-} (Tilde, geschweifte Klammer, XPath-Ausdruck relativ zum aktuellen XML-Element, wobei die geschweifte Klammer geschlossen wird).

Was ist, wenn wir Elemente im Bericht wiederholen müssen? Natürlich können sie als Elemente einer XML-Datendatei dargestellt werden, und ein Deskriptor hilft dabei, die Website richtig zu gestalten. Die Wiederholung von Elementen in einem Bericht kann sowohl eine vertikale Richtung (z. B. wenn wir Rechnungszeilen einfügen) als auch eine horizontale Richtung (wenn wir Spalten eines Analyseberichts einfügen) haben. Gleichzeitig können wir die Verschachtelung von XML-Elementen verwenden, um die willkürlich tiefe Verschachtelung sich wiederholender Berichtselemente widerzuspiegeln, wie in der Abbildung dargestellt:


Die roten Quadrate markieren die Zellen in der oberen linken Ecke des nächsten rechteckigen Fragments, an das der Berichtsgenerator andockt.

Es gibt eine weitere mögliche Option zum Wiederholen von Elementen: Blätter in einer Excel-Arbeitsmappe. Die Möglichkeit, eine solche Iteration zu organisieren, ist ebenfalls verfügbar.

Betrachten Sie ein etwas komplexeres Beispiel. Angenommen, wir benötigen einen zusammenfassenden Bericht wie den folgenden:


Lassen Sie den Benutzer den Bereich der Jahre zum Entladen auswählen, daher werden in diesem Bericht sowohl Zeilen als auch Spalten dynamisch erstellt. Die XML-Darstellung der Daten für einen solchen Bericht könnte folgendermaßen aussehen:


Es steht uns frei, die Namen der Tags nach Ihren Wünschen zu wählen. Die Struktur kann auch beliebig sein, jedoch im Hinblick auf die einfache Konvertierung in den Bericht. Zum Beispiel schreibe ich normalerweise die auf dem Blatt angezeigten Werte in Attribute, weil dies XPath-Ausdrücke vereinfacht (es ist praktisch, wenn sie wie @ aussehen).

Die Vorlage für einen solchen Bericht sieht folgendermaßen aus (vergleichen Sie XPath-Ausdrücke mit den Attributnamen der entsprechenden Tags):


Jetzt kommt der interessanteste Teil: das Erstellen eines Griffs. Da es sich um einen fast vollständig dynamisch zusammengestellten Bericht handelt, ist der Deskriptor in der Praxis ziemlich kompliziert (wenn wir nur eine „Kopfzeile“ des Dokuments, seiner Zeilen und seiner „Fußzeile“ haben), ist normalerweise alles viel einfacher. Hier ist der Deskriptor, der in diesem Fall benötigt wird:


Vollständig beschreibende Elemente sind in der Dokumentation beschrieben . Kurz gesagt bedeuten die Grundelemente eines Deskriptors Folgendes:

• Element - Übergang in den Lesemodus eines Elements einer XML-Datei. Es kann entweder das Stammelement des Deskriptors sein oder sich innerhalb der iteration . Mit dem Attribut name können beispielsweise verschiedene Filter für Elemente festgelegt werden
  • name="foo" - Elemente mit dem Tag-Namen foo
  • name="*" - alle Elemente
  • name="tagname[@attribute='value']" - Elemente mit einem bestimmten Namen und Attributwert
  • name="(before)" , name="(after)" - "virtuelle" Elemente, die der Iteration vorausgehen und die Iteration schließen.
• Iteration - Übergang in den Iterationsmodus. Es kann nur innerhalb des element . Verschiedene Parameter können eingestellt werden, z.
  • mode="horizontal" - mode="horizontal" Ausgabemodus (standardmäßig vertikal)
  • index=0 - beschränkt die Iteration auf das allererste Element
• Ausgang - In den Ausgangsmodus wechseln. Die Hauptattribute sind wie folgt:
  • sourcesheet - Das Vorlagenbuchblatt, aus dem der Ausgabebereich entnommen wird. Wenn nicht angegeben, wird das aktuelle (zuletzt verwendete) Blatt angewendet.
  • range - Der Bereich der Vorlage, die in das resultierende Dokument kopiert werden soll, z. B. "A1: M10" oder "5: 6" oder "C: C". (Die Verwendung von Zeilenbereichen vom Typ „5: 6“ im horizontalen Ausgabemodus und Spaltenbereichen vom Typ „C: C“ im vertikalen Ausgabemodus führt zu einem Fehler.)
  • worksheet - Wenn definiert, wird ein neues Blatt in der Ausgabedatei erstellt und die Ausgabeposition in die Zelle A1 dieses Blattes verschoben. Der Wert dieses Attributs, der einer Konstante oder einem XPath-Ausdruck entspricht, wird im Namen des neuen Blattes ersetzt.

In der Realität gibt es viel mehr Optionen im Deskriptor, siehe Dokumentation.

Nun ist es Zeit, Xylophone herunterzuladen und mit der Berichterstellung zu beginnen.
Nehmen Sie das Archiv von bintray oder Maven Central ( Hinweis : Zum Zeitpunkt des Lesens dieses Artikels sind neuere Versionen möglich). Im Ordner / bin befindet sich ein Shell-Skript. Wenn Sie es ohne Parameter ausführen, wird eine Eingabeaufforderung zu den Befehlszeilenparametern angezeigt. Um das Ergebnis zu erhalten, müssen wir alle zuvor zubereiteten Zutaten dem Xylophon „zuführen“:

 xylophone -data testdata.xml -template template.xlsx -descr descriptor.xml -out report.xlsx 

Öffnen Sie die Datei report.xlsx und stellen Sie sicher, dass wir genau das bekommen, was wir brauchen:


Da die Bibliothek ru.curs: xylophone in Maven Central unter der LGPL-Lizenz verfügbar ist , kann sie problemlos in Programmen in jeder JVM-Sprache verwendet werden. Das vielleicht kompakteste voll funktionsfähige Beispiel wird in Groovy erhalten, der Code benötigt keine Kommentare:

 @Grab('ru.curs:xylophone:6.1.3') import ru.curs.xylophone.XML2Spreadsheet baseDir = '.' new File(baseDir, 'testdata.xml').withInputStream { input -> new File(baseDir, 'report.xlsx').withOutputStream { output -> XML2Spreadsheet.process(input, new File(baseDir, 'descriptor.xml'), new File(baseDir, 'template.xlsx'), false, output) } } println 'Done.' 

Die XML2Spreadsheet Klasse verfügt über mehrere überladene Versionen der statischen process , die sich jedoch alle darauf beschränken, dieselben „Zutaten“ zu übertragen, die für die Erstellung des Berichts erforderlich sind.

Eine wichtige Option, die ich noch nicht erwähnt habe, ist die Möglichkeit, beim Parsen einer Datei mit XML-Daten zwischen DOM- und SAX-Parsern zu wählen. Wie Sie wissen, lädt der DOM-Parser die gesamte Datei in den Speicher, erstellt die Objektdarstellung und ermöglicht die willkürliche Umgehung des Inhalts (einschließlich der wiederholten Rückkehr zum selben Element). Der SAX-Parser speichert niemals die gesamte Datendatei im Speicher, sondern verarbeitet sie als "Stream" von Elementen und verhindert so, dass sie wieder zum Element zurückkehren.

Die Verwendung des SAX-Modus in Xylophone (über die -sax oder das Setzen des useSax Parameters der useSax Methode auf XML2Spreadsheet.process ) kann von entscheidender Bedeutung sein, wenn Sie sehr große Dateien generieren müssen. Aufgrund der Geschwindigkeit und Rentabilität der SAX-Parser-Ressourcen erhöht sich die Geschwindigkeit der Dateierzeugung um ein Vielfaches. Dies erfolgt auf Kosten einiger kleiner Einschränkungen des Deskriptors (in der Dokumentation beschrieben). In den meisten Fällen erfüllen Berichte diese Einschränkungen. Daher würde ich empfehlen, den SAX-Modus nach Möglichkeit zu verwenden.

Ich hoffe, dass Ihnen die Methode zum Hochladen über Xylophone in Excel gefallen hat und Sie viel Zeit und Nerven sparen werden - so wie Sie uns gespart haben.

Und zum Schluss nochmal Links:

• Quellen finden Sie hier: github.com/CourseOrchestra/xylophone
• Die Dokumentation finden Sie hier: courseorchestra.imtqy.com/xylophone
• Alle Codebeispiele aus diesem Artikel finden Sie hier: github.com/inponomarev/xylophone-example .