MSDN Magazine > Accueil > Tous les numéros > 2007 > August >  Excel Services: Développez un moteur de ca...
Excel Services
Développez un moteur de calcul pour vos applications
Vishwas Lele and Pyush Kumar

Cet article aborde les sujets suivants:
  • Excel comme application basée sur serveur
  • Architecture et API Excel Services
  • Créer des fonctions gérées définies par l’utilisateur
  • Créer des solutions personnalisées avec Excel Services
Cet article utilise les technologies suivantes:
Excel Services
Télécharger le code de cet article: ExcelServices2007_08.exe (226 KB)
Parcourez le code en ligne
Les organisations utilisent Microsoft® Excel® pour effectuer des calculs complexes et visualiser des informations au moyen de graphiques, de tableaux croisés dynamiques et autres, et d’exécuter de nombreuses autres tâches personnalisées. Mais par le passé, si vous souhaitiez implémenter un moteur de calcul, vous deviez incorporer les services d’un développeur, qui utilisait les algorithmes fournis par vos analystes d’entreprise pour concevoir le code. Maintenant, avec la technologie Excel Services d’Office SharePoint® Server 2007, les analystes d’entreprise peuvent eux-mêmes implémenter les formules du moteur de calcul dont ils ont besoin, ce qui réduit le coût de mise en œuvre et facilite grandement la maintenance des algorithmes de calcul. De plus, avec Excel Services, les algorithmes personnalisés dans un classeur Excel peuvent s’exécuter sur un serveur Web, ce qui permet aux utilisateurs d’y accéder à distance. Comme vous pouvez l’imaginer, ceci veut dire que beaucoup plus d’utilisateurs peuvent profiter du logiciel à partir d’emplacements beaucoup plus nombreux.

Architecture Excel Services
Voyons comment l’architecture Excel Services offre une telle flexibilité. Excel Services se compose de trois niveaux : un site Web frontal, un serveur d’applications et une base de données (voir la figure 1). La base de données de contenu SharePoint forme le niveau de base de données. Pour activer le comportement Excel côté serveur, vous placez le classeur à un emplacement SharePoint approuvé ou sur un partage de fichier réseau. Certaines fonctionnalités (comme les composants de sécurité supplémentaires) ne sont disponibles que via SharePoint.
Figure 1 Architecture des Services Web Excel 
Le serveur d’applications se compose des Services de calcul Excel, qui ont pour rôle de charger un classeur donné et d’exécuter les calculs requis. Les instances de classeur peuvent être connectées aux sources de données externes.
Le site Web est responsable du rendu des portions pertinentes du classeur au format HTML à l’aide d’un composant WebPart de SharePoint. Il a également pour tâche d’exposer les points de terminaison de services Web qui autorisent l’accès distant aux Services de calcul Excel.
Un aspect important de l’architecture Excel Services est qu’il est intégré à SharePoint 2007. Comme indiqué précédemment, pour activer une partie du comportement côté serveur, le classeur doit être enregistré dans une base de données de contenu SharePoint. Ceci permet de profiter des fonctionnalités de gestion du contenu de SharePoint telles que la définition de versions, l’archivage/l’extraction et les rôles et autorisations de sécurité dans le contexte des classeurs Excel.
De la même manière, les Services de calcul Excel sont basés sur le modèle Fournisseur de services partagés de SharePoint. Le fournisseur de services partagés est un mécanisme d’empaquetage des fonctionnalités de SharePoint sous forme de services, qui facilite l’administration et l’utilisation sur différents sites. Par conséquent, il est possible de réutiliser une instance de Service de calcul Excel sur des sites SharePoint, de même que de la gérer à l’aide d’un site d’administration SharePoint.
Vous devez noter également que Excel Services impose certaines restrictions sur Excel. Les macros et les compléments basés sur code non gérés, tels que le code Visual Basic pour Applications (VBA), ne sont pas pris en charge par Excel Services. Au lieu de cela, Excel Services prend en charge les fonctions définies par l’utilisateur (UDF) côté serveur gérées, une interface qui permet d’appeler des calculs personnalisés depuis un classeur côté serveur.
Nous verrons plus loin un exemple de code UDF. La restriction liée aux compléments de code non gérés peut être surmontée en générant un code UDF géré pour emballer le code non géré. La restriction sur l’utilisation de VBA et des macros est difficile, mais peut être une alliée car elle empêche la logique de calcul côté serveur de devenir encombrante.

API Excel Services
Observons maintenant l’API Excel Services basé sur les services Web et utilisé pour interagir avec un classeur côté serveur, en utilisant les extraits de code dans la figure 2 comme base pour la discussion qui suit. Notez que le code a été élidé pour plus de clarté.
string sheetName = “Input”;
string targetWorkbookPath = “workbook.xlsx”;
Excel.Status[] outStatus;
            
// #1 Excel Service Namespace 
// using Excel = Microsoft.Office.Excel.Server.WebServices;

//#2 ExcelService 
Excel.ExcelService service = new Excel.ExcelService();

//#3 Open a session with the workbook
string sessionID = service.OpenWorkbook(targetWorkbookPath,”en-US”, 
    “en-US”, out outStatus);

// #4 Prepare the range 
Excel.RangeCoordinates rangeCoordinates = new Excel.RangeCoordinates();
rangeCoordinates.Column = 8;
rangeCoordinates.Row = 7;
rangeCoordinates.Height = 1;
rangeCoordinates.Width = 9;

object[] rangeValues = new object[rangeCoordinates.Width];
// Populate rangeValues
...

// #5 Set range values
service.SetRange(sessionID, sheetName, rangeCoordinates, 
    rangeValues, out outStatus);

// #6 Calculate the formulas in the workbook
service.CalculateWorkbook(sessionID, 
    Excel.CalculateType.CalculateFull, out outStatus);

// #7 Get the calculated values 
object[] rangeResult = service.GetRange(sessionID, 
    sheetName, rangeCoordinates, false, out outStatus);

Console.WriteLine(“Total Rows: “ + rangeResult.Length);

// #8 Close the session 
service.CloseWorkbook(sessionID, out outStatus);

Pour commencer, nous avons besoin de rendre le classeur accessible aux clients. Tout classeur enregistré à un emplacement sur le serveur est accessible par le biais de l’API Excel Service, une partie de Microsoft.Office.Excel.Server.WebServices. Le client Excel 2007 facilite la publication du classeur grâce à sa fonctionnalité de publication. L’avantage d’utiliser le mécanisme de publication est que vous pouvez contrôler quelles parties d’un classeur (feuilles, vues, tableaux croisés dynamiques, etc.) sont accessibles à l’aide de l’API Excel Services. La classe principale dans l’API est la classe ExcelService, illustrée à la figure 2. Cette classe représente une mémoire interne, une instance côté serveur d’un classeur. Pour permettre à plusieurs utilisateurs d’interagir simultanément avec un classeur, un modèle d’accès basé sur session a été implémenté. Chaque utilisateur ouvre une session séparée avec un classeur en utilisant la méthode OpenWorkbook de la classe ExcelService. La méthode OpenWorkbook renvoie un ID de session unique associé à la session ouverte. Cet identificateur de session doit être fourni lorsque vous appelez les méthodes suivantes pour interagir avec le classeur ouvert. Pour déterminer une plage nommée dans un classeur, vous pouvez utiliser la classe RangeCoordinates pour définir les limites de la plage nommée. SetRange prend RangeCoordinates et le tableau correspondant contenant les valeurs à transmettre comme paramètres. Une variation de la méthode SetRange est la méthode SetRangeA1, qui utilise la spécification de plage d’Excel « A1 » au lieu des coordonnées de plage utilisées par SetRange. Une fois toutes les valeurs de plage requises spécifiées, CalculateWorkbook peut être invoquée pour forcer le classeur à calculer les formules. Il est possible d’annuler la méthode CalculateWorkbook la plus récente en invoquant une méthode CancelRequest. Vous pouvez utiliser la méthode GetRange pour obtenir des valeurs calculées à partir d’une plage dans le classeur ouvert. Une fois que toutes les valeurs calculées ont été récupérées, vous fermez la session de classeur en utilisant la méthode CloseWorkbook.
Excel Services peut être étendu en ajoutant des UDF, accessibles sous forme de formules de cellules similaires aux fonctions Excel incorporées. Pour créer une UDF, vous devez créer un ensemble Microsoft® .NET Framework qui contient au moins une classe marquée avec UdfClassAttribute et au moins une méthode marquée avec UdfMethodAttribute. Veuillez vous reporter aux extraits de code ci-dessous. Nous définissons ici ConvertToUpper comme méthode UDF. Après avoir enregistré correctement l’UDF, la fonction ConvertToUpper peut se trouver dans une instance de classeur Excel Services :
using Microsoft.Office.Excel.Server.Udf;

[UdfClass]
public class Util
{
    [UdfMethod]
    public string ConvertToUpper(string name)
    {
        return name.ToUpper();
    }
}
Nous avons ici seulement traité une petite portion de l’API Excel Services. Pour plus de détails, consultez la documentation MSDN®.

Solution personnalisée avec Excel Services
Le principal objectif pour développer une solution personnalisée est de permettre aux analystes d’entreprise de rédiger des calculs (tels que des modèles financiers) directement sous forme de formules Excel. Jusqu’à présent, les analystes d’entreprise utilisaient surtout la documentation d’algorithmes comme pseudocode dans le texte. Le pseudocode était ensuite traduit en code par les développeurs. Grâce à Excel Services, nous avons pu surmonter certaines des restrictions inhérentes au processus de création de formules dans Excel et, par conséquent, éliminé le besoin pour les développeurs de convertir le pseudocode en vrai code.
Permettre aux non-développeurs de rédiger la logique de calcul posait le problème de trouver le juste milieu entre flexibilité et facilité de création, avec la capacité de mettre en place une structure robuste. Pour fournir la structure, nous avions besoin d’une méthode pour définir une « interface » d’entrée et de sortie qui représente le contrat de données d’un algorithme de calcul. Les analystes d’entreprise seraient limités aux plages nommées qui font partie du contrat de données pour faire circuler les données à l’intérieur et à l’extérieur de l’instance de calcul.
Le choix évident était de définir le contrat de données en utilisant des constructions de cellules ou de plages nommées dans Excel. Les plages nommées offrent non seulement le niveau requis de précision pour rédiger des calculs dans Excel, mais elles sont également la structure de données fondamentale servant de base aux méthodes d’API Excel Services telles que SetRange et GetRange. Cependant, le problème avec les plages nommées est qu’il n’y a pas de format ou de langage standard, comme la définition de schéma XML (XSD) ou WSDL (Web Services Description Language) pour définir l’interface. De plus, les plages nommées (et par conséquent les méthodes d’API Excel Services) sont en soi d’un type dangereux. Par exemple, il n’y a pas de moyen pour appliquer le contrôle de type de données sur une plage nommée donnée. Enfin, il n’y a pas de méthode intégrée pour appliquer le contrat sur les calculs (dans Excel) et le programme client Excel Services.
Pour éviter ces restrictions, nous avons développé une solution personnalisée en deux parties. La première partie est un pré-compileur Excel conçu pour produire des plages nommées basées sur une interface définie. La deuxième partie est un client de service Web Excel générique qui invoque le calcul dans un classeur, tout en adhérant à l’interface.

Pré-compileur Excel
La définition de schéma XML, avec toute sa richesse et sa simplicité sémantiques, semblait le choix idéal pour définir l’interface. Nous avons décidé d’utiliser les constructions de schéma XML pour définir les contrats d’entrée et de sortie. Ensuite, nous avions besoin d’une méthode pour traduire le schéma XML en plages nommées. Nous avons d’abord étudié la possibilité d’utiliser la fonctionnalité XMLMap introduite avec Excel 2003. XMLMap autorise le mappage des cellules dans Excel sur les éléments d’un schéma XML importé. Malheureusement, la capacité XMLMap n’est pas disponible pour Excel Services. Donc l’alternative était de créer des plages nommées dans un classeur Excel. Nous avons développé un composant de pré-compileur qui générait un classeur modèle avec les plages nommées requises en fonction du schéma. Le classeur modèle produit a trois feuilles : une pour l’entrée, une pour la sortie et une pour le calcul. La feuille d’entrée contient des plages nommées qui correspondent à l’entrée pour le calcul. De même, la feuille de sortie contient des plages nommées qui correspondent au résultat du calcul, et la feuille de calcul est là où les calculs sont placés (voir la figure 3).
Figure 3 Feuilles d’entrée, de calcul et de sortie de classeur (Cliquer sur l'image pour l'agrandir)
Comme nous l’avons indiqué précédemment, les constructions de programmation telles que les boucles ne sont pas disponibles pour Excel Services. Le pré-compileur compense ces restrictions en convertissant les champs XML d’entrée dans un format accessible sans nécessiter de constructions de programmation complexes. Par exemple, les collections d’éléments XSD peuvent être transformées dans les dimensions de la plage nommée. La figure 4 décrit un extrait de code XSD faisant partie du contrat de données d’entrée pour un moteur de calcul.
<?xml version=”1.0” encoding=”utf-8” ?>
<xs:schema id=”Input” ...>
  <xs:complexType name=”TypeA”>
    <xs:sequence>
      <xs:element name=”ValueA1” type=”xs:int”  minOccurs=”0” />
      <xs:element name=”ValueA2” type=”xs:int” minOccurs=”0” />
    </xs:sequence>
    <xs:attribute name=”RangeHeight” type=”xs:int” default=”1” />
    <xs:attribute name=”RangeWidth” type=”xs:int” default=”10” />

  </xs:complexType>
  <xs:complexType name=”InputType”>
    <xs:sequence>
      <xs:element name=”InputElementA” type=”TypeA”    />
      <xs:element name=”InputElementB” type=”TypeB”    />
    </xs:sequence>
  </xs:complexType>
  <xs:element name=”InputDataset” type=”InputType” msdata:IsDataSet=”true” />
</xs:schema>

Les éléments TypeA et TypeB font partie des entrées du calcul. Notez les attributs personnalisés RangeHeight et RangeWidth qui définissent les dimensions des plages nommées. Le pré-compileur utilise cette information pour produire les dimensions de plage nommées. Le pré-compileur peut également déréférencer les champs d’index en colonnes séparées, où chaque colonne représente une valeur d’index.
Un aspect notable du pré-compileur est sa capacité à conserver les calculs existants tout en régénérant les feuilles d’entrée et de sortie. Comme décrit à la figure 5, développer un algorithme de calcul est un processus itératif. Les analystes d’entreprise et les développeurs travaillent ensemble pour définir le contrat de données initial. Au cours du développement du classeur, les contrats d’entrée et de sortie peuvent avoir besoin d’être modifiés et le classeur doit être régénéré pour que ces modifications soient appliquées. Le pré-compileur prend en charge ce développement itératif en conservant la feuille de calculs pendant la régénération du classeur.
Figure 5 Génération d’un classeur en utilisant le pré-compileur 
La feuille d’entrée illustrée à la figure 3 a les plages nommées prégénérées. La feuille de calculs comporte des calculs qui font référence aux plages nommées définies sur la feuille d’entrée. La feuille de sortie, à son tour, fait référence aux calculs de la feuille de calcul.

Client de service Web Excel
Le rôle principal du client de service Web Excel est d’invoquer des calculs dans le classeur utilisant l’API Excel Services. Ce faisant, il interprète le contrat d’entrée de base XSD, en mappant les éléments de schéma dans les plages nommées appropriées. Une fois le calcul terminé, le client remappe les plages nommées de sortie dans un contrat de sortie de base XSD. La figure 6 décrit le rôle du client de service Web Excel. Un DataSet tapé (basé sur le contrat de schéma d’entrée) est transmis comme entrée. Les données contenues dans le DataSet sont mappées en plages nommées d’entrée. Une fois le calcul terminé, les plages nommées de sortie sont utilisées pour peupler le DataSet de sortie. Le client de service Web Excel a pour charge d’appliquer les règles définies pour les pré-compileurs. Il est également possible d’injecter des transformations de données personnalisées pour changer le mappage mentionné ci-dessus entre XSD et les plages nommées. Rappelez-vous notre discussion précédente sur le besoin de compenser le manque de constructions de programmation disponibles pour les auteurs du classeur. Les transformations de données personnalisées autorisent la modification du mappage pour que les analystes d’entreprise puissent facilement rédiger la logique de calcul.
Figure 6 Invoquer le moteur de calcul personnalisé 

Analyser le code
La solution que nous avons créée est composée de quatre projets. Le projet PreCompiler héberge tout le code afin de produire un classeur avec les plages nommées requises en fonction du schéma d’entrée et de sortie. Il tient compte des attributs personnalisés mentionnés ci-dessus tels que RangeHeight tout en produisant les plages nommées. Le projet PreCompiler à son tour se base sur SpreadsheetML (un dialecte basé sur XML utilisé pour représenter les informations dans une feuille de calcul) pour générer le classeur. Le projet SpreadsheetML contient des classes simples qui intègrent les composants SpreadsheetML tels que le Classeur, Feuille de travail, etc.
Le projet Client, comme son nom le suggère, est le code du client de service Web Excel. Il détermine les valeurs pour les plages nommées d’entrée, force le classeur à recalculer et récupère les valeurs pour les plages nommées de sortie. Souvenez-vous que nous avons parlé du besoin de transformer les données pour que les analystes d’entreprise puissent plus facilement développer les calculs. Pour autoriser la transformation de données afin de les personnaliser pour chaque moteur de calcul, nous avons externalisé la logique de transformation de données en définissant une interface IDataTransformer, comme illustré ici :
public interface IDataTransformer 
{
    object    getRangeData(string RangeName);
    object[]  getRangeData(string RangeName, int width);
    object[,] getRangeData(string RangeName, int width, int height);

    string getInputSchema();
    string getOutputSchema();

    string getInputSchemaPrefix();
    string getOutputSchemaPrefix();        
}
Le nom d’un assembly contenant une mise en œuvre de l’interface IDataTransformer est transmis comme entrée au programme client. Le programme client, à son tour, rappelle les méthodes appropriées sur la classe implémentant l’interface IDataTransformer. De cette façon, le programme client obtient les valeurs qui sont utilisées pour peupler les plages nommées. La logique de mise en œuvre dans les méthodes IDataTransformer assure la conversion des données résidant dans le DataSet d’entrée en valeurs de plages nommées appropriées. Par exemple, des lignes d’un DataTable doivent peut-être être filtrées avant de peupler les plages nommées appropriées. Ou les lignes dans le DataTable doivent peut-être être triées avant leur transmission au classeur. Tous ces besoins de transformation de données peuvent être satisfaits à l’aide de l’interface IDataTransformer.
Une autre classe importante devant être abordée ici est ExcelServiceFacade. Cette classe masque les détails de l’API Excel Services à l’appelant. L’autre fonction importante de cette classe consiste à combiner les appels SetRange individuels dans un appel SetRange regroupé. Ceci est crucial pour réduire la latence du réseau, car chaque appel SetRange provoque une boucle au serveur. En exposant un appel SetRange local qui est finalement transformé en un appel SetRange regroupé, ExcelServiceFacade peut améliorer les temps de réponse de façon spectaculaire. La figure 7 illustre le code ExcelServiceFacade pertinent. Un tampon interne est maintenu par la classe ExcelServiceFacade ajoutée à chaque invocation d’un appel SetRange « local ». Une fois que toutes les plages nommées d’entrée sont peuplées, le tampon interne est envoyé au serveur dans le cadre d’un appel unique. Un mécanisme similaire est utilisé lors de la récupération des valeurs nommées de sortie une fois le calcul terminé. Au lieu de récupérer les valeurs des plages nommées de sortie individuellement, nous récupérons simplement toutes les valeurs sur la feuille de sortie d’un seul coup.
public class ExcelServiceFacade
{
    ...
    
    public void OpenWorkbook()
    {
        if (this.SessionID.Equals(String.Empty))
        {
            Status[] status = null;
            this.Excel.Credentials = 
                System.Net.CredentialCache.DefaultCredentials;
            this.m_SessionID = this.Excel.OpenWorkbook(
                m_Url, CULTURE, CULTURE, out status);
        }        
    }

    public void CalculateWookbook()
    {
        RangeCoordinates coorInput = new RangeCoordinates();
        coorInput.Row = 0;
        coorInput.Column = 1;
        coorInput.Width = 25;
        coorInput.Height = m_RowIndex;

        // Set Range
        object[] inputValues = (object[])
            m_Input.ToArray(typeof(object[]));
        SetRange(“Input”, coorInput, inputValues);

        this.Excel.CalculateWorkbook(
            this.SessionID, CalculateType.CalculateFull);
    }
}


Performances et évolutivité
Nous avons découvert que l’exécution effective des calculs dans le classeur est très rapide. Dans un test peu scientifique effectué avec un classeur contenant une série importante de calculs impliquant près d’un millier de plages nommées, le temps de réponse était inférieur à une seconde ; une grande partie de ce temps ayant été utilisée par les boucles au serveur Excel. Comme la charge sur le système augmente en termes de complexité du calcul et du nombre d’exécutions simultanées, il est possible d’adapter la solution en tirant parti des diverses options de topologie offertes par Excel Services. Les différentes options de topologie vous permettent de déterminer le placement de chacune des couches Excel Services logiques (présentation, application et base de données). Pour les installations plus petites (principalement utilisées à des fins de tests), il est possible de déployer les trois couches sur un seul serveur. Pour une installation moyenne, les couches de présentation et d’application peuvent être installées sur un seul serveur, et la couche de base de données sur un serveur distinct. Pour les grandes installations, vous pouvez installer chacune des trois couches sur des serveurs distincts. De plus, vous pouvez augmenter la couche de présentation en ajoutant plus de serveurs avec un équilibrage de la charge réseau. La couche d’application comprenant les services de calcul Excel peut également être étendue en utilisant des schémas d’équilibrage de la charge pris en charge par l’infrastructure de fournisseur de services partagés. La figure 8 décrit une grande installation où chaque couche est installée sur un serveur distinct. De plus, les couches de présentation et d’application sont adaptées en utilisant des schémas d’équilibrage de la charge.
Figure 8 Installation Services Web Excel de grande ampleur 
Pour les sommes de travail lourdes en calculs, il est également possible de combiner Excel Services avec Compute Cluster Server pour distribuer en continu le travail de calcul des nœuds, comme à la figure 9.
Figure 9 Installation Excel Services hautes performances 
Comme vous pouvez le voir, une solution personnalisée visant à implémenter des composants de moteur de calcul en utilisant Excel Services est une aubaine pour la productivité. Elle vous permet de fournir à vos utilisateurs un accès depuis n’importe où aux fonctions de classeur personnalisées, évite au développeur de devoir implémenter la logique et vous permet d’augmenter votre solution selon vos besoins. Essayez-la. Nous sommes sûrs que vous apprécierez les gains en flexibilité et en productivité qu’elle offre. Pour plus d’informations, reportez-vous à l’encadré « Ressources ».

Vishwas Lele est directeur technique chez Applied Information Sciences (AIS) à Reston en Virginie. Il aide les organisations à prévoir, concevoir et implémenter des solutions d’entreprise qui sont basées sur les technologies Microsoft .NET. Vishwas est le Directeur régional de Microsoft pour la région de Washington DC. Il peut être contacté à l’adresse suivante : vlele@acm.org.

Pyush Kumar est Architecte responsable Systèmes pour Watson Wyatt Worldwide. Il a travaillé récemment sur le calcul de grille et la conception de logiciels à grande échelle pour .NET Framework. Vous pouvez le contacter à l’adresse suivante : pyush.kumar@watsonwyatt.com.

Page view tracker