Comment créer une sortie de rapports Excel - COM / Open XML / Modèles

Dans les consultations sur la sortie de rapports Excel, la formule « on veut sortir vers Excel » cache souvent, en réalité, plusieurs exigences distinctes mêlées les unes aux autres.

• Les utilisateurs veulent retoucher la sortie à la main par la suite
• Un .xlsm existant doit être conservé
• Les tableaux croisés dynamiques, graphiques et paramètres d’impression doivent être repris tels quels
• De gros volumes doivent être produits par un traitement de nuit
• Cela doit s’exécuter sans supervision sur un serveur
• Un PDF est également souhaité

Aucune approche unique ne résout proprement tout cela à la fois. La première chose à regarder n’est pas le nom de la bibliothèque, mais si l’on pilote l’application Excel ou si l’on construit un fichier Excel.

Se tromper sur ce point peut fonctionner au départ, mais rend la maintenance pénible par la suite. Dans cet article, en partant de la sortie de rapports Excel dans des applications Windows et des systèmes métier, nous organisons comment choisir entre automatisation COM / Open XML / injection de données par modèle / cohabitation avec du VBA existant.

1. D’abord les conclusions

Posons d’abord les conclusions.

• Si le rapport est destiné à être ouvert et modifié par les utilisateurs dans Excel par la suite, le premier candidat est un modèle associé à une génération directe en .xlsx / .xlsm.
• Si la génération est automatique sur un serveur / service / planificateur, il est plus sûr de ne pas fonder la conception sur l’automatisation Office.
• Si l’on souhaite tirer parti de fichiers .xlsm, VBA, graphiques, tableaux croisés dynamiques et paramètres d’impression existants, la conception est plus robuste en poussant la mise en page et les fonctionnalités propres à Excel dans le modèle, et en gardant le code focalisé uniquement sur l’injection de données.
• Ce n’est que lorsque l’on a réellement besoin du comportement propre de l’application Excel qu’il est naturel d’utiliser l’automatisation COM, et même dans ce cas, il faut la limiter à une exécution supervisée sur le poste de travail.
• Pour un simple export de liste, CSV / PDF / une page web correspond très souvent mieux aux besoins dès le départ.

En résumé, pour beaucoup de rapports métier, il est plus naturel « d’assembler un fichier Excel » que « d’opérer Excel ».

2. Ce qu’il faut décider en premier

Voici un tableau des points à décider en premier pour la sortie de rapports Excel.

3. Principales approches d’implémentation

3.1 Automatisation COM d’Excel

Cette approche lance Excel et manipule Workbook, Worksheet et Range via COM. Le plus simple est de la comprendre comme le fait de piloter le véritable Excel.

Sa force est de permettre d’utiliser tel quel le comportement propre à Excel. Elle s’accorde bien avec les classeurs existants, les graphiques, les tableaux croisés dynamiques, les paramètres d’impression, les macros et l’export PDF, et permet de travailler directement avec « la façon dont Excel présentera finalement les choses ».

Ses faiblesses, en revanche, sont tout aussi claires.

• Excel doit être installé
• On hérite de la durée de vie du processus, des verrous de fichiers, des boîtes de dialogue, du bitness et des dépendances au profil utilisateur
• L’Office Automation depuis un serveur ou un service non supervisé est quelque chose que Microsoft lui-même ne recommande ni ne prend en charge

3.2 Génération directe en .xlsx

Comme .xlsx est le format Open XML, on peut assembler les fichiers directement sans lancer Excel. Avec des outils comme le Open XML SDK, le programme peut manipuler classeurs, feuilles, cellules, styles et tableaux.

La force de cette approche est qu’elle est facile à exécuter dans des environnements sans Excel installé et qu’elle s’accorde bien avec les traitements par lot et les serveurs.

En revanche, les choses se compliquent quand on veut reproduire naturellement le comportement orienté UI qu’offre Excel lui-même. L’ajustement automatique de la largeur des colonnes, les sauts de page, les mises en forme complexes et l’édition profonde de classeurs existants tournent vite au corps-à-corps si l’on essaie de tout faire proprement rien qu’avec du code.

3.3 Injection de données par modèle

L’approche que nous recommandons le plus volontiers en pratique consiste à créer d’abord un modèle Excel et à concentrer le code uniquement sur l’injection de données.

L’apparence du rapport, les formules, la mise en forme conditionnelle, les zones d’impression, les en-têtes/pieds de page, les logos et les graphiques vivent dans le modèle. Le code copie le modèle et écrit les données dans des points d’entrée convenus : plages nommées, tableaux, plages de cellules, etc.

Cela sépare les modifications de mise en page des modifications de logique métier. Cela permet d’éviter en grande partie l’enfer du Cells[37, 9] = ... si courant dans les rapports Excel.

3.4 Conserver les ressources VBA existantes

Si des fichiers .xlsm ou du VBA existants sont bien vivants, il est souvent plus naturel de ne pas tout reconstruire d’un coup. Laisser l’UI du rapport et la mise en forme finale en VBA, tout en déplaçant les calculs lourds et la logique DB / HTTP / métier vers le côté C# / .NET, est une répartition très réaliste.

Ce qui compte ici, c’est de ne pas laisser les responsabilités floues.

• Le côté VBA possède le comportement à l’intérieur du classeur
• Le côté .NET possède la récupération des données et le traitement métier
• La frontière entre les deux est fixée via des plages nommées, des tableaux, des interfaces publiques, etc.

3.5 Les cas d’usage de Microsoft 365 / Graph

Si les fichiers Excel vivent dès le départ sur OneDrive / SharePoint et que l’on souhaite les partager depuis des applications web ou mobiles, l’API Excel de Microsoft Graph est aussi une option.

Ce n’est cependant pas une réponse générale pour produire en masse et sans précaution des fichiers arbitraires sur un poste local. Les autorisations, l’emplacement de stockage, les sessions et l’exploitation supposent tous M365 dès le départ.

3.6 Faut-il vraiment que ce soit Excel ?

Si l’exigence est « un tableau que des personnes retoucheront ensuite », choisir Excel est naturel. Mais pour des besoins comme ceux-ci, un autre format est souvent le choix le plus direct.

• Impression et archivage -> PDF
• Import dans un autre système -> CSV / TSV / JSON
• Simple besoin d’être consultable dans un navigateur -> HTML / page web
• Agrégation et visualisation comme objectif principal -> BI ou tableau de bord

4. Comparaison des approches

En mettant les différences côte à côte dans un seul tableau, cela donne ceci.

5. Choisir selon les besoins courants

5.1 Sortie sur le poste utilisateur, puis édition directe

Dans ce cas, modèle plus génération directe est un choix très solide. Les utilisateurs ouvrent ensuite la sortie dans Excel, donc l’édition finale peut tout simplement être laissée à Excel.

5.2 Génération en gros volume par un traitement de nuit ou un service

Si un traitement de nuit est en jeu, il est plus sûr de commencer par écarter l’automatisation COM. Déplacez la génération vers une création directe en .xlsx, et si besoin, laissez les utilisateurs ouvrir les fichiers dans Excel par la suite.

5.3 Tirer parti d’un .xlsm / VBA existant

Si des ressources existantes sont encore vivantes, la voie réaliste consiste à conserver le .xlsm comme modèle et à ne réaliser que l’injection de données depuis l’extérieur.

5.4 Gros volumes de lignes de détail

La limite d’une seule feuille Excel est de 1 048 576 lignes sur 16 384 colonnes. Quand les données de détail sont volumineuses, décidez d’abord ces points.

• Au-delà de combien de lignes découpe-t-on en plusieurs feuilles ?
• Au-delà de combien d’enregistrements découpe-t-on en plusieurs fichiers ?
• Le CSV ne serait-il pas, dès le départ, le choix le plus naturel ?

6. Une architecture facile à recommander en pratique

Ce qui se révèle robuste en pratique est une architecture répartie en quatre couches.

Ce qui est appréciable dans cette répartition, c’est que le code devient beaucoup moins susceptible d’être entraîné par l’apparence d’Excel.

7. Pièges à éviter

7.1 Ne pas transformer les adresses de cellules en règles métier

Dès que Cells[12, 7] se met à représenter une règle métier, un changement de mise en page devient un changement de spécification. Le code tient plus longtemps lorsqu’il touche le rapport via des plages nommées et des noms de tableaux.

7.2 Ne pas utiliser les cellules fusionnées comme points d’entrée de données

Les cellules fusionnées sont une fonctionnalité de présentation. Les utiliser comme cibles d’injection rend les accidents fréquents lors de l’ajout de lignes ou du calcul de plages.

7.3 Ne pas remplir les nombres et les dates avec des « chaînes préformatées »

Il est plus naturel de stocker les valeurs comme des valeurs et de reporter l’apparence sur le format de cellule.

7.4 Ne pas laisser les modifications de modèle sans gouvernance

Un modèle n’est pas du code, mais en pratique il est la spécification elle-même. L’approche sûre consiste à le traiter comme soumis au versionnage, à la comparaison de diff et à la revue.

7.5 En cas d’usage de COM, ne pas sous-estimer le bitness et la gestion de durée de vie

Avec l’automatisation COM et l’intégration VBA, les différences 32 bits / 64 bits, le nettoyage des processus Excel, les verrous de fichiers et les écarts entre environnements utilisateurs pèsent discrètement mais sûrement.

8. Résumé

La sortie de rapports Excel ressemble à un sujet qui tient en une ligne — « sortir vers Excel » — mais en réalité, plusieurs décisions doivent être prises en amont.

• Pilote-t-on l’application Excel ?
• Construit-on un fichier Excel ?
• L’exécution a-t-elle lieu sur le poste utilisateur, ou de façon non supervisée ?
• Le VBA ou le .xlsm existants seront-ils conservés ?
• Le livrable final est-il Excel, ou PDF / CSV ?

Comme premier candidat en pratique, modèle plus génération directe est très solide. Y ajouter, selon les besoins, la réutilisation du VBA existant ou un traitement Excel final sur le poste utilisateur tend à former un ensemble cohérent.

9. Références

• Considerations for server-side Automation of Office
• Considerations for unattended automation of Office in the Microsoft 365 for unattended RPA environment
• About the Open XML SDK for Office
• How to: Copy a worksheet with SAX (Simple API for XML)
• Overview of the Excel workbooks and charts API - Microsoft Graph
• Access OneDrive and SharePoint via Microsoft Graph API
• Excel specifications and limits

Articles associés

Articles récents partageant les mêmes étiquettes, pour approfondir des sujets proches.

Sujets associés

Ces pages replacent le sujet dans un contexte plus large de services et de décisions.

Services liés à ce sujet

Cet article est directement lié aux services suivants.

Questions fréquentes

Questions souvent posées lors d’une consultation sur le sujet de cet article.

Quelle est la meilleure façon de générer des rapports Excel depuis une application métier ?

Pour la plupart des rapports métier, le candidat le plus solide est un modèle associé à une génération directe en .xlsx/.xlsm : on conserve l'apparence, les formules, la mise en forme conditionnelle, les zones d'impression et les graphiques dans un modèle Excel, et le code se limite à injecter des données dans des plages nommées et des tableaux. Cela sépare les modifications de mise en page des modifications de logique métier, et évite le piège classique où des adresses de cellules comme Cells[37, 9] finissent par représenter des règles métier. La première question à se poser n'est pas quelle bibliothèque utiliser, mais si l'on pilote l'application Excel ou si l'on construit un fichier Excel.

Peut-on utiliser l'automatisation COM d'Excel sur un serveur ou dans un traitement par lot planifié ?

Il est plus prudent de l'éviter. Microsoft lui-même ne recommande ni ne prend en charge l'Office Automation depuis un serveur ou un service non supervisé, et l'automatisation COM entraîne en plus des problèmes de durée de vie de processus, de verrous de fichiers, de boîtes de dialogue, de bitness et de dépendance au profil utilisateur, sans compter qu'Excel doit être installé. Pour les traitements de nuit et la génération côté serveur, la création directe de .xlsx avec un outil comme le Open XML SDK fonctionne sans Excel installé et se prête bien aux volumes importants. Réservez l'automatisation COM à une exécution supervisée sur le poste de travail, uniquement lorsque le comportement propre d'Excel est réellement nécessaire.

Comment gérer les fichiers .xlsm et les macros VBA existants lors de l'ajout d'une sortie de rapports automatisée ?

Il n'est généralement pas nécessaire de tout reconstruire d'un coup. Une répartition réaliste consiste à conserver le .xlsm comme modèle, avec l'UI du rapport et la mise en forme finale en VBA, tout en déplaçant les calculs lourds et la logique DB/HTTP/métier vers le côté C#/.NET, qui se contente d'injecter les données depuis l'extérieur. L'important est de ne pas laisser les responsabilités floues : le VBA possède le comportement à l'intérieur du classeur, le .NET possède la récupération des données et le traitement métier, et la frontière entre les deux est fixée via des plages nommées, des tableaux et des interfaces publiques.

Quels sont les pièges courants dans le code de génération de rapports Excel ?

L'article en met cinq en avant : ne pas transformer les adresses de cellules en règles métier (utiliser plutôt des plages nommées et des noms de tableaux) ; ne pas utiliser les cellules fusionnées comme points d'entrée de données, car elles provoquent des accidents lors de l'ajout de lignes ; stocker les nombres et les dates comme de véritables valeurs et reporter l'apparence sur le format de cellule plutôt que sur des chaînes préformatées ; traiter les modèles comme des spécifications versionnées soumises à revue et à comparaison de diff ; et, en cas d'utilisation de COM, ne pas sous-estimer les différences 32 bits/64 bits, le nettoyage des processus Excel et les verrous de fichiers. N'oubliez pas non plus qu'une seule feuille est plafonnée à 1 048 576 lignes, il faut donc prévoir un découpage en feuilles ou en fichiers pour les données de détail volumineuses.

Profil de l’auteur

Page de présentation de l’auteur de l’article.

Go Komura

Représentant de KomuraSoft LLC

Spécialisé dans le développement de logiciels Windows, le conseil technique et l’analyse de pannes, notamment pour les systèmes existants et les incidents difficiles à reproduire.