Déchargez les données dans Excel. Civilisé

Il y a des tâches dans l'industrie informatique qui, dans le contexte des succès dans les mégadonnées , l'apprentissage automatique , la blockchain et d'autres tendances de la mode, semblent complètement peu attrayantes, mais depuis des décennies n'ont pas cessé d'être pertinentes pour toute une armée de développeurs. Il s'agira de l'ancien comme tâche mondiale de création et de téléchargement de documents Excel, qui a confronté tous ceux qui ont déjà écrit des applications pour les entreprises.





Quelles sont les possibilités de création de fichiers Excel en principe?

1. Macros VBA. De nos jours, pour des raisons de sécurité, l'idée d'utiliser des macros ne convient le plus souvent pas.
2. Excel Automation avec un programme externe via l'API. Nécessite Excel sur la même machine que le programme générant des rapports Excel. À une époque où les clients étaient gros et écrits sous la forme d'applications Windows de bureau, cette méthode était appropriée (bien qu'elle ne diffère pas en termes de vitesse et de fiabilité), dans les réalités d'aujourd'hui, ce n'est guère un cas réalisable.
3. Générez directement un fichier XML-Excel. Comme vous le savez, Excel prend en charge le format XML pour l'enregistrement d'un document, qui peut potentiellement être généré / modifié à l'aide de n'importe quel moyen de travail avec XML. Ce fichier peut être enregistré avec l'extension .xls, et bien qu'il ne s'agisse pas à proprement parler d'un fichier xls, Excel l'ouvre bien. Cette approche est assez populaire, mais les inconvénients incluent le fait que toute solution basée sur l'édition directe du format XML-Excel est un «hack» ponctuel dépourvu de généralité.
4. Enfin, il est possible de générer des fichiers Excel à l'aide de bibliothèques open source, dont Apache POI est particulièrement connu. Les développeurs d'Apache POI ont effectué un travail titanesque sur les formats de documents MS Office binaires de rétro-ingénierie, et continuent de maintenir et de développer cette bibliothèque au fil des ans. Le résultat de cette ingénierie inverse, par exemple, est utilisé dans Open Office pour implémenter la conservation des documents dans des formats compatibles avec MS Office.

À mon avis, c'est la dernière des méthodes qui est maintenant préférée pour générer des documents compatibles avec MS Office. D'une part, il ne nécessite l'installation d'aucun logiciel propriétaire sur le serveur, et d'autre part, il fournit une API riche qui vous permet d'utiliser toutes les fonctionnalités de MS Office.

Mais l'utilisation directe d'Apache POI a ses inconvénients. Tout d'abord, il s'agit d'une bibliothèque Java et si votre application est écrite dans plusieurs langages JVM, vous pouvez difficilement l'utiliser. Deuxièmement, c'est une bibliothèque de bas niveau qui fonctionne avec des concepts tels que «cellule», «colonne», «police». Par conséquent, «front», la procédure écrite pour générer un document se transforme rapidement en une «nouille» abondante de code difficile à lire, où il n'y a pas de séparation dans un modèle de données et une présentation, il est difficile d'apporter des modifications et, en général, de la douleur et de la honte. Et une excellente occasion de déléguer la tâche au programmeur le plus inexpérimenté - laissez-le choisir.

Mais cela pourrait être complètement différent. Le projet Xylophone sous la LGPL, construit sur la base d'Apache POI, est basé sur une idée qui a une histoire estimée à 15 ans. Dans les projets auxquels j'ai participé, il a été utilisé en combinaison avec une grande variété de plateformes et de langues - et, en raison de la variété des formulaires créés avec son aide dans une grande variété de projets, il y en a probablement déjà des milliers. Il s'agit d'un projet Java qui peut fonctionner à la fois comme un utilitaire de ligne de commande et comme une bibliothèque (si vous avez du code dans le langage JVM, vous pouvez le connecter en tant que dépendance Maven).

Xylophone met en œuvre le principe de séparation du modèle de données de sa présentation. Dans la procédure de téléchargement, vous devez générer des données au format XML (sans vous soucier des cellules, des polices et des lignes de séparation), et Xylophone, à l'aide d'un modèle Excel et d'un descripteur décrivant l'ordre d'analyse de votre fichier de données XML, générera le résultat, comme indiqué dans le diagramme:


Le modèle de document (modèle xls / xlsx) ressemble à ceci:


En règle générale, l'achat d'un tel modèle est fourni par le client. Le client impliqué est heureux de participer à la création du modèle: en commençant par choisir le bon formulaire à partir du "Consultant" ou en inventant un à partir de zéro, et en terminant par la taille de la police et la largeur des lignes de séparation. L'avantage du modèle est qu'il est facile d'y apporter des modifications mineures même lorsque le rapport est entièrement développé.

Lorsque le travail de «conception» est terminé, le développeur reste

1. Créez une procédure de téléchargement des données nécessaires au format XML.
2. Créez un descripteur qui décrit la procédure de traversée des éléments d'un fichier XML et de copie des fragments de modèle dans le rapport résultant
3. Liez les cellules du modèle aux éléments d'un fichier XML à l'aide d'expressions XPath .

Avec le téléchargement vers XML, tout est plus ou moins clair: il suffit de sélectionner la représentation XML appropriée des données nécessaires pour remplir le formulaire. Qu'est-ce qu'un descripteur?

Si le formulaire que nous créons ne contient pas d'éléments en double avec des numéros différents (tels que des lignes de facture, qui sont différentes dans différentes factures), le descripteur ressemblera à ceci:

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

Ici, root est le nom de l'élément racine de notre fichier de données XML, et la plage A1: Z100 est la plage rectangulaire de cellules du modèle qui sera copiée dans le résultat. De plus, comme le montre l'illustration précédente, les champs génériques dont les valeurs sont remplacées par des données d'un fichier XML ont le format ~{XPath-} (tilde, accolade, expression XPath par rapport à l'élément XML actuel, fermeture de l'accolade).

Et si nous avons besoin de répéter des éléments dans le rapport? Naturellement, ils peuvent être représentés comme des éléments d'un fichier de données XML, et un descripteur permet de l'aider de la bonne manière. La répétition d'éléments dans le rapport peut avoir à la fois une direction verticale (lorsque nous insérons des lignes de facture, par exemple) et horizontale (lorsque nous insérons des colonnes du rapport analytique). Dans le même temps, nous pouvons utiliser l'imbrication des éléments XML pour refléter l'imbrication arbitrairement profonde des éléments de rapport répétitifs, comme le montre le diagramme:


Les carrés rouges marquent les cellules qui seront le coin supérieur gauche du prochain fragment rectangulaire que le générateur de rapports ancrera.

Il existe une autre option possible pour répéter des éléments: les feuilles dans un classeur Excel. La possibilité d'organiser une telle itération est également disponible.

Prenons un exemple un peu plus complexe. Supposons que nous ayons besoin d'un rapport de synthèse comme celui-ci:


Laissez l'utilisateur sélectionner la plage d'années de déchargement. Par conséquent, les lignes et les colonnes sont créées dynamiquement dans ce rapport. La représentation XML des données d'un tel rapport pourrait ressembler à ceci:


Nous sommes libres de choisir les noms des tags à votre guise, la structure peut également être arbitraire, mais en gardant à l'esprit la facilité de conversion en rapport. Par exemple, j'écris généralement les valeurs affichées sur la feuille dans des attributs, car cela simplifie les expressions XPath (c'est pratique quand elles ressemblent à @ ).

Le modèle d'un tel rapport ressemblera à ceci (comparez les expressions XPath avec les noms d'attribut des balises correspondantes):


Vient maintenant la partie la plus intéressante: créer une poignée. Comme il s'agit d'un rapport presque entièrement assemblé dynamiquement, le descripteur est plutôt compliqué, en pratique (lorsque nous n'avons qu'un «en-tête» du document, ses lignes et son «pied de page»), tout est généralement beaucoup plus simple. Voici le descripteur nécessaire dans ce cas:


Les éléments entièrement descriptifs sont décrits dans la documentation . En bref, les éléments de base d'un descripteur signifient ce qui suit:

• élément - passage en mode lecture d'un élément d'un fichier XML. Il peut être soit l'élément racine du descripteur, soit être à l'intérieur de l' iteration . L'attribut name peut être utilisé pour définir divers filtres pour les éléments, par exemple
  • name="foo" - éléments avec la balise name foo
  • name="*" - tous les éléments
  • name="tagname[@attribute='value']" - éléments avec un nom et une valeur d'attribut spécifiques
  • name="(before)" , name="(after)" - éléments "virtuels" qui précèdent l'itération et clôturent l'itération.
• itération - transition vers le mode d'itération. Il ne peut s'agir que d'un element interne. Divers paramètres peuvent être réglés, par ex.
  • mode="horizontal" - mode de sortie mode="horizontal" (vertical par défaut)
  • index=0 - restreindre l'itération au tout premier élément rencontré
• sortie - passer en mode sortie. Les principaux attributs sont les suivants:
  • sourcesheet - La feuille de livre de modèle à partir de laquelle la plage de sortie est prise. S'il n'est pas spécifié, la feuille actuelle (dernière utilisée) est appliquée.
  • range - la plage du modèle à copier dans le document résultant, par exemple, «A1: M10», ou «5: 6» ou «C: C». (L'utilisation de plages de lignes de type «5: 6» en mode de sortie horizontale et de plages de colonnes de type «C: C» en mode de sortie vertical entraînera une erreur).
  • worksheet - si elle est définie, une nouvelle feuille est créée dans le fichier de sortie et la position de sortie est décalée vers la cellule A1 de cette feuille. La valeur de cet attribut, égale à une expression constante ou XPath, est substituée dans le nom de la nouvelle feuille.

En réalité, il y a beaucoup plus d'options dans le descripteur, voir la documentation.

Eh bien, il est maintenant temps de télécharger Xylophone et de commencer à créer des rapports.
Prenez l'archive depuis bintray ou Maven Central (NB: au moment de la lecture de cet article, des versions plus récentes sont possibles). Dans le dossier / bin, il y a un script shell, lorsque vous l'exécutez sans paramètres, vous verrez une invite concernant les paramètres de ligne de commande. Pour obtenir le résultat, nous devons «alimenter» au xylophone tous les ingrédients précédemment préparés:

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

Ouvrez le fichier report.xlsx et assurez-vous que nous obtenons exactement ce dont nous avons besoin:


Étant donné que la bibliothèque ru.curs: xylophone est disponible sur Maven Central sous la licence LGPL, elle peut être utilisée sans problème dans les programmes dans n'importe quel langage JVM. Peut-être que l'exemple le plus compact et pleinement fonctionnel est obtenu dans Groovy, le code n'a pas besoin de commentaires:

 @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.' 

La classe XML2Spreadsheet possède plusieurs versions surchargées de la méthode de process statique, mais elles se résument toutes à transférer les mêmes «ingrédients» nécessaires à la préparation du rapport.

Une option importante que je n'ai pas encore mentionnée est la possibilité de choisir entre les analyseurs DOM et SAX au stade de l'analyse d'un fichier avec des données XML. Comme vous le savez, l'analyseur DOM charge l'intégralité du fichier en mémoire, construit sa représentation d'objet et permet de contourner son contenu de manière arbitraire (y compris en revenant à plusieurs reprises sur le même élément). L'analyseur SAX ne met jamais l'intégralité du fichier de données en mémoire, il le traite plutôt comme un "flux" d'éléments, l'empêchant de revenir à l'élément.

L'utilisation du mode SAX dans Xylophone (via l' -sax ligne de commande -sax ou en définissant le paramètre useSax de la méthode useSax sur XML2Spreadsheet.process ) est extrêmement utile dans les cas où vous devez générer de très gros fichiers. En raison de la vitesse et de la rentabilité des ressources de l'analyseur SAX, la vitesse de génération de fichiers augmente plusieurs fois. Cela est donné au prix de quelques petites restrictions sur le descripteur (décrites dans la documentation), mais dans la plupart des cas, les rapports satisfont à ces restrictions, donc je recommanderais d'utiliser le mode SAX dans la mesure du possible.

J'espère que vous avez aimé la méthode de téléchargement vers Excel via Xylophone et économiserez beaucoup de temps et de nerfs - comme vous nous avez sauvés.

Et enfin, des liens à nouveau:

• les sources sont ici: github.com/CourseOrchestra/xylophone
• la documentation est ici: courseorchestra.imtqy.com/xylophone
• tous les exemples de code de cet article sont ici: github.com/inponomarev/xylophone-example .