Donc, je m'appelle Mikhail et je suis directeur technique chez Exerica. L'un des problèmes que nous résolvons est de faciliter le travail des analystes financiers avec des données numériques. Ils travaillent généralement à la fois avec les documents originaux de rapports financiers et statistiques et avec une sorte d'outil pour créer et maintenir des modèles analytiques. Il se trouve que 99% des analystes travaillent sous Microsoft Excel et y font des choses assez complexes. Par conséquent, les transférer d'Excel vers d'autres solutions n'est pas efficace et pratiquement impossible. Objectivement, les services «cloud» de tableurs n'atteignent pas encore les fonctionnalités d'Excel. Mais dans le monde moderne, les outils doivent être pratiques et répondre aux attentes des utilisateurs: ouvrir par clic de souris, effectuer une recherche pratique. Et la mise en œuvre sous la forme de diverses applications indépendantes sera assez éloignée des attentes de l'utilisateur.
Ce avec quoi l'analyste travaille ressemble à ceci: Les principales données ici sont des «indicateurs financiers» numériques, par exemple le revenu du 1er trimestre 2020. Par souci de simplicité, je les appellerai simplement des «nombres». Comme vous pouvez le voir, il n'y a presque aucun lien entre les nombres dans le document et dans le modèle analytique, tout n'est que dans la tête de l'analyste. Et le travail de remplissage et de maintenance du modèle prend des heures de recherche et d'interruption des nombres du document dans les tableaux, puis à la recherche d'erreurs de saisie. Dans le même temps, nous souhaitons offrir à l'utilisateur des outils familiers: glisser-déposer, insertion dans le presse-papiers, etc., ainsi qu'une vue rapide des données sources.
Ce que nous avions déjà
Au moment où nous avons commencé à implémenter une interaction interactive avec Excel sous la forme décrite dans cet article, nous avions déjà une base de données dans MongoDB, un backend sous la forme d'une API REST dans .NET Core, un SPA frontal dans Angular et quelques autres services. À ce stade, nous avons déjà essayé différentes options pour l'intégration dans des applications de feuille de calcul, y compris Excel, et elles ne sont pas allées au-delà de MVP, mais c'est un sujet pour un article séparé.
Lier des données
Il existe deux outils courants dans Excel qui peuvent être utilisés pour résoudre le problème de la liaison de données dans une table avec des données dans le système: RTD (RealTimeData) et UDF (Fonctions définies par l'utilisateur). Pure RTD est moins convivial en termes de syntaxe et limite la flexibilité de la solution. À l'aide des UDF, vous pouvez créer une fonction personnalisée qui fonctionnera d'une manière familière à un utilisateur Excel. Il peut être utilisé dans d'autres fonctions, il comprend des références comme A1 ou R1C1 et se comporte généralement comme il se doit. En même temps, personne ne se soucie d'utiliser le mécanisme RTD pour mettre à jour la valeur de la fonction (ce que nous avons fait). Nous avons développé UDF sous la forme d'un complément Excel en utilisant C # et .NET Framework auxquels nous sommes habitués. Nous avons utilisé la bibliothèque Excel DNA pour accélérer le développement .
En plus de UDF, notre complément implémente un ruban (barre d'outils) avec des paramètres et des fonctions utiles pour travailler avec des données.
Ajouter de l'interactivité
Pour transférer des données vers Excel et pour établir l'interactivité, nous avons développé un service distinct qui fournit une connexion Websocket à l'aide de la bibliothèque SignalR et est en fait un courtier pour les messages sur les événements qui devraient être échangés par les parties frontales du système en temps réel. Nous l'appelons Service de notification.
Insérer des données dans Excel
Dans notre SPA, nous mettons en évidence tous les numéros que le système a détectés. L'utilisateur peut les sélectionner, les parcourir, etc. Pour l'insertion de données, nous avons implémenté 3 mécanismes pour fermer divers cas d'utilisation:
- Glisser déposer
- Insertion automatique au clic dans SPA
- Copier et coller via le presse-papiers
Lorsque l'utilisateur lance le drag'n'drop d'un certain nombre depuis SPA, un lien avec l'identifiant de ce numéro depuis notre système (
.../unifiedId/005F5549CDD04F8000010405FF06009EB57C0D985CD001) est formé pour le glisser . Lors du collage dans Excel, notre addin intercepte l'événement d'insertion et analyse le texte inséré avec une expression régulière. Lorsqu'un lien valide est trouvé à la volée, il le remplace par la formule appropriée =ExrcP(...).
Lorsque vous cliquez sur un numéro dans le SPA via le service de notification, un message est envoyé à l'addin, contenant toutes les données nécessaires pour insérer la formule. Ensuite, la formule est simplement insérée dans la cellule actuellement sélectionnée.
Ces méthodes sont bonnes lorsque l'utilisateur a besoin d'insérer un numéro dans son modèle, mais s'il a besoin de transférer la table entière ou une partie de celle-ci, un autre mécanisme est nécessaire. La copie via un presse-papiers semble être la plus familière aux utilisateurs. Cependant, cette méthode s'est avérée plus compliquée que les deux premières. Le fait est que, pour plus de commodité, les données insérées doivent être présentées au format Excel natif - Feuille de calcul OpenXML. Ceci est plus facilement implémenté à l'aide du modèle d'objet Excel, c'est-à-dire à partir de l'addin. Par conséquent, le processus de formation d'un presse-papiers ressemble à ceci:
- L'utilisateur sélectionne la zone avec des numéros dans SPA
- Un tableau de numéros alloués est transmis au service de notification
- Le service de notification le transmet au complément
- Addin génère OpenXML et l'insère dans le presse-papiers
- L'utilisateur peut coller des données du presse-papiers n'importe où dans n'importe quelle feuille de calcul Excel.
Malgré le fait que les données vont très loin, grâce à SignalR et RTD, cela se produit assez rapidement et de manière abstraite de l'utilisateur.
Nous diffusons des données
Une fois que l'utilisateur a sélectionné les données initiales pour son modèle, il doit "propager" toutes les périodes (années, semestres et trimestres) qui l'intéressent. A ces fins, l'un des paramètres de notre FDU est la date (période) de cette date (rappelez-vous: «revenus du 1er trimestre 2020»). Excel dispose d'un mécanisme de «diffusion» de formule native qui vous permet de remplir les cellules avec la même formule, en tenant compte des références spécifiées dans les paramètres. Autrement dit, au lieu d'une date spécifique, un lien vers celle-ci est inséré dans la formule, puis l'utilisateur «l'étend» à d'autres périodes, tandis que les «mêmes» numéros d'autres périodes sont automatiquement chargés dans le tableau.
Et quel est ce nombre là-bas?
L'utilisateur dispose désormais d'un modèle avec plusieurs centaines de lignes et plusieurs dizaines de colonnes. Et il a peut-être une question, qu'est-ce qu'il y a pour le numéro dans la cellule L123? Pour obtenir une réponse, il suffit de cliquer sur cette cellule et dans notre SPA le même rapport s'ouvrira, sur la même page où le numéro cliqué est écrit, et le numéro dans le rapport sera mis en évidence. Comme ceci:
Et s'il ne s'agit pas simplement d'un nombre du rapport, mais du résultat de certains calculs sur les nombres tirés du rapport, nous mettrons en évidence tous les nombres inclus dans l'expression calculée dans Excel. Cela ne télécharge pas l'ensemble de l'application et télécharge toutes les données nécessaires, comme dans le cas de suivre un lien.
Comme conclusion
Il s'agit, à mon avis, d'une implémentation non standard de l'interaction entre Excel et une application Web, qui s'est avérée assez conviviale. Grâce à l'utilisation d'Excel, le seuil d'entrée pour les utilisateurs du public cible est assez bas. Dans le même temps, nous bénéficions également de toute la puissance d'Excel pour travailler avec des données numériques. Les données elles-mêmes restent toujours associées à la source, ainsi que associées dans le temps. Pour les clients cibles, il n'est pas nécessaire d'intégrer des systèmes externes entièrement nouveaux dans le processus métier de travail avec les données. Notre solution est intégrée comme un «outil» supplémentaire dans Excel, qui est le standard de facto pour les fournisseurs de données financières.
Une approche architecturale similaire pour l'intégration d'applications Web avec Microsoft Excel peut être appliquée à d'autres tâches qui nécessitent de l'interactivité et des interfaces utilisateur complexes lorsque vous travaillez avec des données numériques et tabulaires.