Excel Services, Configuration et Développement

Dans ce tutoriel, nous allons aborder les points suivants:

• Qu'est-ce que Excel Services et à quoi ça sert?
• Comment créer un fichier Excel et le publier vers Excel Services?
• Comment créer un fichier Excel contenant une connexion embarquée vers une base de données SQL Server et le publier vers Excel Services?
• Comment créer un fichier Excel contenant une connexion partagée vers une base de données SQL Server et le publier vers Excel Services?
• Comment exploiter les classeurs Excel via le service web proposé par Sharepoint
• Comment développer ses propres fonctions et les exploiter au travers de Excel Services ?

Excel Services est vaste, nous tenterons d'aborder les grands points génériques au travers de plusieurs exemples.

Excel Services est un service disponible dans MOSS 2007 Enterprise et travaille en combinaison avec certaines versions d'Excel 2007 pour la publication. Un des aspects de cette fonctionnalité est de permettre la publication de fichiers Excel vers un serveur Sharepoint et de les mettre à disposition des clients sans pour autant que ceux-ci disposent d'Excel sur leur machine. En gros, c'est le service qui se charge de proposer la puissance d'Excel de manière centralisée sur le serveur. Les clients doivent uniquement disposer d'un navigateur.

On va pouvoir partager au sein de Sharepoint des classeurs Excel pour effectuer du reporting, du calcul, des tables pivots etc. de manière contrôlée et centralisée.

D'autre part, on va pouvoir développer certaines fonctionnalités en se basant sur la puissance d'Excel.

Un bon moyen d'aborder Excel Services est d'utiliser le modèle de site "Report Center" délivré en standard avec MOSS. Celui-ci contient quelques exemples de Business Intelligence car il propose un Dashboard(1) avec des KPI(2) et un classeur Excel publié vers Excel Services. Notez que lorsque vous créez un centre de rapports, vous devez immédiatement "truster" la librairie de rapports pour ne pas avoir de problème. Reportez-vous aux sections suivantes pour savoir comment faire.

Pour éviter tout problème lors de la création d'un Report Center, veillez à activer l'Infrastructure de Publication Office SharePoint Server au sein de votre collection. En effet, le Report Center tente d'activer cette fonctionnalité dès sa création. Etant donné qu'il y a une dépendance au niveau de cette fonctionnalité, vous devez préalablement l'activer au niveau de votre collection.

Vous bénéficierez ensuite d'exemples tels qu'illustrés ci-dessous. Je vous engage à explorer ce modèle afin de comprendre l'utilité d'Excel Services

Pour pouvoir bénéficier de l'infrastructure d'Excel Services, la première chose à vérifier est que le service soit bien démarré. Pour cela, allez dans la centrale d'administration

Démarrez le service s'il est arrêté. Si vous ne voyez pas ce service, vérifiez que vous avez bien activé la license Enterprise

Allez dans votre Shared Service Provider pour accéder aux autres interfaces de configuration d'Excel Services. Lorsque vous y êtes, vous devriez voir une section dédicacée à Excel Services.

Voici maintenant une explication de chaque catégorie

Nous reviendrons en détail sur chacune de ces catégories dans la section suivante.

Dans cet exemple, nous allons créer une mini (vraiment mini) calculatrice, le but étant de voir comment publier un document Excel vers Excel Services et comment spécifier des paramètres que l'utilisateur final pourra exploiter.

Veuillez disposer d'une bibliothèque de documents avant de passer à la suite.

Ouvrez Excel 2007 et créez un nouveau document que vous nommez Calculatrice. Utilisez ensuite la première ligne.
La cellule A1 contient simplement le mot Addition, les cellules B1 et C1 seront des paramètres fournis par l'utilisateur et la cellule D1 sera le résultat.

Positionnez le curseur sur B1 et ensuite cliquez sur Define Name (entouré en rouge dans l'image ci-dessus). Nommez la cellule Addition1 comme illustré ci-dessous

Positionnez le curseur sur C1 et nommez-le Addition2. Positionnez ensuite le curseur sur D1 et tapez la formule suivante =Addition1+Addition2. Ni plus ni moins que la somme de nos deux paramètres.

III-A-1. Publication vers Excel Services▲

Maintenant que l'on a créé le document, il reste à le publier. Cliquez sur le bouton Office et procédez comme illustré ci-dessous

Dans la zone filename, saisissez l'URL de la bibliothèque de documents vers laquelle vous souhaitez publier le document et donnez un nom à celui-ci. Cliquez ensuite sur Excel Services Options

Allez dans l'onglet Parameters, ensuite Add et cochez les deux paramètres comme ci-dessous

Cliquez ensuite sur Save. Le document doit s'ouvrir en mode Excel Services et là, normalement, vous aurez une belle erreur

Ceci est dû au fait que nous n'avons pas référencé cette bibliothèque comme trusted. Nous allons tout de suite le faire. Allez dans Trusted file locations comme expliqué en section II-B. Cliquez ensuite sur Add Trusted File Location et procédez comme illustré

Saisissez bien sûr l'URL de votre bibliothèque. L'option Children Trusted spécifie si on fait aussi confiance aux éléments enfants, on peut également référencer des sites comme trusted location. Restons-en là pour l'instant, nous parlerons des connexions dans un instant.

Maintenant que vous avez "trusté" la bibliothèque, ouvrez celle-ci et cliquez sur le document Excel.

Cela devrait à présent fonctionner et vous devez obtenir ceci

Soit une partie centrale avec les données et la partie de droite permettant à l'utilisateur d'entrer les différents paramètres.

Note: lorsque vous publiez un document vers Excel Services, vous pouvez décider de ne publier qu'une feuille ou un élément spécifique du classeur. Ceci permet d'avoir une partie "publique" et une partie "privée" par exemple.

Dans cet exemple nous allons créer un fichier Excel avec une connexion vers AdventureWorks(3). Il faut donc disposer de SQL Server et de AdventureWorks.

Que ce soit pour une connexion embarquée ou une connexion partagée, il faut autoriser l'utilisation des connexions au niveau de la Trusted Location correspondante. Je vais donc montrer comment autoriser les connexions à la fois embarquées et partagées

Allez dans la section Excel Services de votre SSP. Vous avez déjà dû définir la Trusted Location dans l'exemple précédent. Cliquez dessus pour l'éditer. Lorsque vous êtes en mode édition, localisez la section External Data comme illustrée ci-dessous

Cochez l'option Trusted data connection libraries and embedded. Ceci autorise l'ouverture de classeurs ayant des connexions embarquées et/ou partagées (issues d'une bibliothèque de connexions)

Les autres paramètres permettent de définir un intervalle de raffraîchissement automatique, de spécifier si on souhaite avertir l'utilisation qu'un raffraîchissement de données va se faire, de définir le temps de mise en cache etc...

III-B-1. Création et Publication du classeur▲

Ouvrez Excel 2007 et créez un nouveau document, on l'appellera ConnexionEmbarquee. Cliquez sur l'onglet Data puis sur From Other Sources et enfin From SQL Server

Dans la zone Server Name, saisissez ".". Le point représente le serveur courant. Etant donné que nos exemples sont tous créés sur un stand-alone, le . convient très bien. Adaptez bien sûr en fonction de votre environnement.

Ensuite, cliquez sur Next et choisissez la base AdventureWorks et ensuite la vue vEmployee

Terminez enfin en donnant un nom à votre fichier et un friendly name

Choisissez Pivot Table Report (tableau croisé dynamique) et cliquez sur Ok. Ensuite choisissez n'importe quel colonne dans la liste des champs à droite.

A ce stade, vous devez obtenir un listing des données.

Publiez ensuite vers Excel Services comme pour l'exemple 1 de ce tutoriel sans aller dans les options car ce n'est pas nécessaire. Le classeur devrait ensuite s'ouvrir en mode Excel Services.

Pour être certain que votre connexion fonctionne correctement, effectuez un raffraîchissement

Note: si cela ne fonctionne pas, ceci peut-être dû à :

• Vous avez oublié d'autoriser la trusted location à exécuter des connexions embarquées
• Vous ne vous êtes pas authentifié au niveau Sharepoint, vous êtes donc en mode anonyme
• Vous n'avez pas trusté la bibliothèque dans laquelle vous avez publié le classeur

III-C-1. Avantage des connexions partagées▲

L'avantage d'utiliser des connexions partagées se résume en ces quelques points

• Réduction de la maintenance. En effet, si une connexion partagée par 5 classeurs doit être modifiée, on effectuera une seule modification plutôt que de modifier les 5 classeurs les uns après les autres
• Possibilité d'utiliser les droits (ACL) sur les connexions puisqu'elles sont des éléments d'une bibliothèque
• Centralisation des connexions. Tout est sous contrôle et même versionné au besoin.

Comme pour l'exemple précédent, il faut avoir autorisé les connexions externes pour la trusted location correspondante. Il faut par contre créer une bibliothèque de connexions et créer la connexion partagée que l'on utilisera dans notre classeur. Pour créer une bibliothèque de connexions, vous devez afficher tout le contenu de votre site et ensuite, cliquez sur Créer puis sur Bibliothèque de connexion (connection library). En somme, ce type de bibliothèque se crée comme n'importe quelle autre bibliothèque.

Lorsque la bibliothèque est créée, vous devez la référencer comme sûre, soit Trusted. Allez dans les paramètres Excel Services de votre SSP, cliquez sur Trusted data connection libraries et ensuite sur Add Trusted Data Connection Library. Saisissez ensuite l'URL de votre bibliothèque de connexions comme illustré ci-dessous.

Il reste maintenant à créer le classeur et à le publier

III-C-2. Création et Publication du classeur▲

Pour simplifier, nous allons réutiliser la connexion créée dans l'exemple 2. Excel 2007 stocke par défaut les connexions (ODC => Office Data Connection) dans le répertoire My Data Sources

Lorsque vous avez localisé votre fichier de connexion, retournez dans la bibliothèque de connexions que vous avez créée. Ajoutez-y un nouveau fichier .ODC

Cliquez sur Browse (Parcourir) et allez rechercher le fichier .odc stocké sur votre disque dur. Cliquez sur Ok pour valider l'upload. Vous devez ensuite approuver le fichier

Cette connexion est maintenant utilisable dans les classeurs Excel.

Créez un nouveau fichier Excel 2007 que vous nommerez ConnexionPartagee, cliquez sur l'onglet Data et ensuite sur Connections

Ensuite cliquez sur Add puis sur Browse for more et saisissez l'URL de votre bibliothèque dans la zone File name

Cliquez sur Open. La connexion est à présent référencée par votre classeur. Toujours dans l'onglet Data, cliquez sur Existing Connections et sélectionnez la connexion fraîchement ajoutée. Ensuite, créez une Pivot Table Report (tableau croisé dynamique) comme dans l'exemple 2.

Publiez le classeur de la même manière que pour l'exemple 2. Le classeur s'ouvre en mode Excel Services.

En fonction de votre configuration, les connexions externes se feront sous l'identité de l'utilisateur ayant ouvert le classeur ou non. Si vous travaillez sur plusieurs serveurs (cas de figure fréquent), vous devrez éventuellement utiliser le protocole Kerberos comme mode d'authentification pour vos applications et/ou implémenter le single sign on.

Tous les exemples démontrés dans ce tutoriel sont réalisés sur un stand-alone, c'est à dire une seule machine avec tous les composants installés dessus (SQL, Sharepoint etc...). Vous ne retrouverez probablement jamais ce type de configuration dans le monde réel.

III-D-1. Utilisation du webpart Excel Web Access▲

Si vous souhaitez afficher un classeur Excel au sein d'une page, vous pouvez utiliser le webpart prévu pour cela. Il fait partie des webparts appartenant au module Business Intelligence de MOSS. Il faut donc que cette fonctionnalité soit activée.

En éditant une page, et en cliquant sur Add WebPart dans une zone, vous devriez pouvoir localiser le webpart Excel Web Access

Voici par exemple comment intégrer notre classeur Calculatrice.xlsx dans ce webpart. Choissisez le bon webpart dans la galerie

Vous obtenez le webpart dans votre page, allez dans ses propriétés

Cliquez sur le bouton (entouré en rouge) ou tapez directement l'URL

Quoi qu'il en soit, au final vous devez lier le webpart à votre classeur.

Et voici le résulat au sein de votre page, votre classeur est encapsulé dans celle-ci

III-D-2. En forcant la bibliothèque à ouvrir les classeurs en mode Excel Services▲

Par défaut, si vous disposez d'Excel sur votre machine (client ou serveur), un classeur même publié vers Excel Services s'ouvrira dans Excel. Vous pouvez cependant modifier ce comportement en forcant Sharepoint à ouvrir le classeur en tant que page Web.

En allant dans les paramètres de la bibliothèque hébergeant vos classeurs Excel, vous pouvez forcer l'ouverture d'un document en tant que page web.

Ceci est également le cas pour les formulaires Infopath déployés vers Forms Services

Nous allons créer un projet permettant de calculer les indemnités kilométriques pour les employés. Pour faire simple, nous aurons uniquement le montant d'une indemnité kilométrique stocké dans une liste Sharepoint. Le système demandera à l'utilisateur d'entrer le nombre de KM et ira rechercher le taux applicable pour fournir le résultat final, à savoir le montant total devant être remboursé à l'employé.

Créez donc une liste personnelle que vous appelerez ParametresGlobaux avec une colonne Taux de type numérique. Créez ensuite un élément de liste et entrez par exemple la valeur 0.2 qui correspondra à l' en Euro par kilomètre.

Pour bien comprendre ce qui suit, vous devez être familiarisé avec dotnet et visual studio

• Créez un projet de type Class Library
• Ajoutez les références vers les DLL Microsoft.Office.Excel.Server.Udf et Microsoft.SharePoint
• Ajoutez les directives using Microsoft.SharePoint; et using Microsoft.Office.Excel.Server.Udf;
• Renommez class1.cs en Calcul.cs

Pour simplifier, je code "en dur" tous les paramètres qui devraient dans un contexte réel être bien sûr issus de fichiers de configuration et je n'ajoute pas de gestion d'erreur.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
using Microsoft.SharePoint;

namespace Indemnite
{
    [UdfClass]
    public class Calcul
    {
        [UdfMethod]
        public Double ValeurIndemniteKm(double d)
        {
            double Taux = 0;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite Site = new SPSite("http://sey-pc/sites/dev/"))
                {
                    using (SPWeb Web = Site.OpenWeb())
                    {
                        SPListItem Itm = Web.Lists["ParametresGlobaux"].GetItemById(1);
                        Taux = Convert.ToDouble(Itm["Taux"].ToString());
                    }
                }
            });


            return d * Taux;
        }
    }
}

Que faut-il retenir de ce code?

• La classe est marquée par l'attribut UdfClass. En effet, nous pourrions avoir plusieurs classes au sein de cet assemblage. Seules les classes marquées de cet attribut sont connues par Excel Services
• La méthode est marquée par l'attribut UdfMethod pour la même raison que précédemment.
• L'accès aux données contenues dans la liste Sharepoint se fait via un délégué impersonated.

L'attribut UdfMethod peut prendre optionnellement deux paramètres, IsVolatile qui détermine si la méthode est volatile ou non et ReturnsPersonalInformation qui spécifie si oui ou non l'identité de l'utilisateur ayant ouvert le classeur doit être cachée ou non.

Le déploiement peut se faire de 3 manières différentes

• Via une solution : certainement la meilleure manière de procéder. Une solution déploiera la DLL sur tous les frontaux automatiquement. Pour savoir comment déployer une UDF via une solution, vous pouvez consulter ce lien
• Via un assemblage signé placé en GAC et référencé dans la configuration Excel Services
• Via un simple référencement vers le chemin physique de la DLL sur le serveur.

Bien que la première option soit la meilleure, je vais plutôt explorer la troisième.

Allez dans la configuration Excel Services comme indiqué en section II-B et cliquez sur User-defined function assemblies. Vous devriez obtenir ceci

Cliquez ensuite sur Add User-defined Function Assembly et saisissez les informations comme illustré sur l'image ci-dessous

Soit le chemin physique de l'emplacement de votre DLL. Spécifiez qu'il s'agit d'un File path et qu'il est activé via la case à cocher.

Si vous choisissez Global Assembly Cache, vous devrez renseigner la signature de l'assemblage dans la zone Assembly.

Enfin, quelle que soit la technique choisie, il faudra que les DLL soient présentes sur tous vos frontaux si vous en avez plus d'un. Si vous optez pour la spécification d'un File path, vous pouvez envisager une ressource partagée sur le réseau comme illustré ci-dessous

Lorsque la DLL sera "enregistrée" dans Sharepoint, les fonctions utilisées dans les classeurs seront automatiquement reconnues. Vous devrez cependant être certain d'avoir coché User-defined functions allowed au niveau de la trusted location dans laquelle se trouve le(s) classeur(s).

Lorsque vous aurez procédé à toutes les étapes décrites précédemment, vous pourrez ouvrir votre fichier publié et vous devriez obtenir ceci

Comme vous pouvez le constater dans cet exemple, le calcul est bien de (100 * 0.2) + 100+50+25 => 295