Philippe Sentenac
Développer des User Defined Functions pour Excel Services
Après une présentation des Excel Services, nous verrons comment étendre la librairie de fonctions déjà disponible avec Excel grâce aux User Defined Functions (UDF).
Par Philippe Sentenac publié le 16/03/2008 à 23:35, lu 3886 fois, 4 pages
commentaire(s) 0 commentaire(s) tag(s)Tags: Sharepoint, ExcelServices, User Defined Function
 3 | Les User Defined Functions
Les User Defined Functions
Il est possible de rajouter des fonctionnalités aux Excel Services via des librairies nommées « User Defined Functions » (UDF). Elles peuvent être exécutées depuis une feuille de calcul comme toute autre fonction Excel standard.
Comme le développement des UDF s'appuie sur le Framework .Net, les possibilités d'extension sont pratiquement illimitées. Côté technique, l'écriture d'UDF ne se distingue des développements classiques que par un attribut venant préfixer les méthodes exposées.
Les UDF représentent, entres autres, un moyen simple de centraliser des formules jusqu'alors disponibles sous forme de macros, sans en dévoiler les règles métier.
Par exemple, pour extraire le dernier mot d'une phrase dans une cellule, on pourrait écrire la formule :
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/OldFormula.jpg
 
Avec les UDF, on permet à l'utilisateur de simplement saisir le nom d'une nouvelle fonction :
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/NewFormula.jpg
 
Création d'une UDF
Pour créer une nouvelle User Defined Function, nous allons créer un nouveau projet de type « Bibliothèque de classe », auquel nous ajoutons une référence à Microsoft.Office.Excel.Server.Udf.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/projects.JPG
 
In fine, une UDF est une méthode préfixée de l'attribut [UdfMethod] d'une classe elle-même préfixée de l'attribut [UdfClass]. Cette classe doit être publique et non abstraite avec un constructeur sans paramètre. Son nom n'a aucune importance, car seule la signature de la méthode précédée de l'attribut [UdfMethod] permet d'identifier l'UDF en question.
Au final, voici le code de notre première UDF :

using System;

using Microsoft.Office.Excel.Server.Udf;

 

namespace myUdfs

{

    [UdfClass]

    public class Class1

    {

        [UdfMethod]

        public string Greetings()

        {

            return "Hello World";

        }

    }

}

Une fois compilée, on place notre DLL dans le GAC ou dans un répertoire précis sur notre serveur SharePoint.
Nous allons alors ajouter via l'interface d'administration du Shared Service Provider du serveur SharePoint l'assembly contenant notre UDF.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/EditUDFAssembl.JPG
 
Voilà, cela suffit pour créer une nouvelle UDF, et les créateurs de classeurs Excel pour publication sur le serveur SharePoint grâce aux Excel Services peuvent l'utiliser dans leurs formules.
Surcharges de méthodes et paramètres optionnels
Lors du chargement des UDF, si les Excel Services rencontrent la même signature pour plusieurs méthodes, seule la première est chargée en mémoire, empêchant ainsi les possibilités de surcharge de méthode.
Ainsi, nous ajoutons à notre classe la méthode suivante :

[UdfMethod]

public string Greetings(string name)

{

    string result = "Hello World";

    if (String.IsNullOrEmpty(name))

        return result;

    return String.Format("Hello {0}", name);

}

Cette méthode ne pourra pas être chargée et donc utilisée puisqu'elle porte le même nom que notre première méthode, qui elle ne prenait pas de paramètres.
Il existe toutefois une autre alternative pour passer des paramètres optionnels à une UDF : l'utilisation d'un tableau de paramètres. En réalité, le dernier paramètre d'une UDF est un tableau. Le compilateur s'occupe de récupérer les paramètres entrés en dernières positions et les rassemblent en un tableau passé en dernier paramètre de la méthode.

[UdfMethod]

public string Greetings2(params string[] args)

{

    string result = "Hello ";

 

    if (args.Length == 0)

        return result;

 

    foreach (string item in args)

    {

        if (!string.IsNullOrEmpty(item))

            result += " " + item;

    }

 

    return result;

}

Voici ce que donne alors l'appel aux différentes UDF créées dans un même classeur Excel publié avec les Excel Services.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/Greetings.jpg
 
La première UDF est bien chargée et exécutée. Comme nous l'avions prévu, la deuxième UDF n'a même pas été chargée et Excel nous retourne l'erreur « #VALEUR! ».
Pour notre dernière UDF, on voit qu'il est possible de l'appeler avec un ou plusieurs paramètres, grâce à la technique du tableau de paramètres.
Gestion du cache
Les Excel Services gèrent deux niveaux de cache lorsqu'ils exécutent des UDF. Le premier est relatif à un cache global et autorise le développeur à mettre en cache des informations indépendamment de la session utilisateur courante. A contrario, le deuxième est relatif à une session particulière.
Ainsi, le résultat d'une méthode est stocké en cache, et lorsque cette UDF est de nouveau appelée avec les mêmes arguments, c'est le résultat mis en cache qui sera retourné. De fait, ce cache peut poser problème pour une UDF qui, par exemple, retournerait la date et l'heure actuelle :

[UdfMethod]

public string DateTimeNow()

{

    return DateTime.Now.ToString();

}

Pour pouvoir passer outre la mise en cache par défaut, on peut alors marquer l'UDF en tant que volatile, ce qui signifie que même si elle est appelée avec les mêmes arguments, les Excel Services doivent réévaluer le résultat de la méthode.

[UdfMethod(IsVolatile = true)]

public string DateTimeNowReal()

{

    return DateTime.Now.ToString();

}

Parfois, une donnée retournée par une UDF peut être personnelle et ne doit pas être partagée entre les utilisateurs. Ainsi, les UDF peuvent être marquées pour retourner des informations personnelles. Ceci a pour effet de renseigner Thread.CurrentPrincipal.Identity.Name en y mettant le nom de l'utilisateur qui accède au classeur.

[UdfMethod(ReturnsPersonalInformation = true)]

public string GetUltraSecretData()

{

    string userName = Thread.CurrentPrincipal.Identity.Name;

 

    if (userName.ToLower().Contains("admin"))

        return "Votre Mot de Passe : $tr0ng P4$$w0rÐ (Strong Password)";

    else

        return "Vous n'êtes pas autorisé à voir cette donnée.";

}

 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/Caching.jpg
 
Quand on observe les résultats des appels à nos UDF, on voit qu'il y a une différence entre l'heure affichée avec la première UDF non volatile et la deuxième qui a été marquée comme volatile, et donc qui affiche le véritable résultat de la méthode.
Pour la troisième UDF, on remarque que le classeur a été affiché avec le compte administrateur. Si on l'affiche à nouveau avec un autre compte, le résultat en sera modifié.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/CachingNonAdmin.jpg
 
Pour finir sur la notion de cache, il est à noter que les classes marquées par l'attribut [UdfClass] sont instanciées une fois par session, c'est-à-dire tant que la page affichant la WebPart EWA est ouverte. De plus, les membres statiques de cette classe seront accessibles au niveau du cache global, ce qui implique une gestion de verrouillage des ressources pour éviter les accès concurrentiels.
Enfin, il n'existe pas de notion de cache Excel Services au niveau de la ferme de serveurs, mais il est néanmoins possible d'en implémenter une avec une solution personnalisée.
Types supportés et conversion
Les Excel Services, tout comme Excel, ne supportent que trois types de base et par extrapolation deux types supplémentaires dans les cellules des classeurs. Les trois types de base sont les chaines de caractères, les nombres à précision double et les erreurs de cellule. Les deux types supplémentaires sont les booléens et les dates.
Les UDF, comme elles sont basées sur le Framework .Net, supportent beaucoup plus de types. Il est alors important de comprendre quelles sont les conversions que feront les Excel Services avant de passer des valeurs en paramètres.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/types.JPG
 
Pour voir plus concrètement ces conversions, on peut écrire une simple UDF qui retourne le nom du type reçu en paramètre.

[UdfMethod]

public string GetTypeOfParameter(object[] param)

{

    return param[0].GetType().ToString();

}

On se retrouve alors avec le résultat suivant dans un classeur qui affiche tous ces types.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/TypesConversion.jpg
 
Exécution des UDF dans Office Excel 2007
Comme nous vous le disions dans la partie « Limitations des Excel Services »de cet article, Excel 2007 ne gère pas l'utilisation d'UDF et affichera #NAME dans la cellule où vous faites appel à votre formule. Il est néanmoins possible de modifier les UDF pour leur ajouter le support COM, et c'est ce que nous allons voir à présent.
Ainsi, si nous reprenons notre premier exemple de classeur et que nous l'ouvrons dans Excel, voici le résultat que nous obtenons :
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/ExcelCompatibility.jpg
 
Nous allons devoir modifier notre classe UDF pour définir nos méthodes en tant que composants COM.
Commençons par ajouter les namespaces System.Runtime.InteropServices et Microsoft.Win32 à notre fichier de définition de classe.

using System;

using Microsoft.Office.Excel.Server.Udf;

using System.Runtime.InteropServices;

using Microsoft.Win32;

 

namespace myUdfs

{

    [UdfClass]

    [ProgId("myUdfs.Class1")]

    [ClassInterface(ClassInterfaceType.AutoDual)]

    [Guid(Class1.Guid)]

    [ComVisible(true)]

    public class Class1

    {

        public const string Guid = "8ED508EB-0473-44be-974B-DFC607F8F230";

Comme vous pouvez le constater, nous avons également ajouté un certain nombre d'attributs à notre classe, pour définir son ProgId ainsi qu'un Guid unique.
Un objet COM a besoin d'une clé « Programmable » pour qu'Excel puisse l'enregistrer en tant que complément. Il va donc falloir implémenter deux méthodes pour l'enregistrement et le désenregistrement.

[ComRegisterFunction]

 public static void RegistrationMethod(Type type)

 {

    if (typeof(Class1) != type)

    {

        return;

    }

    RegistryKey key = Registry.ClassesRoot.CreateSubKey("CLSID\\{" + Guid + "}\\Programmable");

    key.Close();

 }

 

 [ComUnregisterFunction]

 public static void UnregisterationMethod(Type type)

 {

    if (typeof(Class1) != type)

    {

        return;

    }

    Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + Guid + "}\\Programmable");

 }

Après compilation, il nous faut enregistrer l'assembly généré en tant que librairie COM. Pour ce faire, le Framework .Net nous fournit un outil, RegAsm.exe, qui se trouve dans le dossier Windows. On exécute donc la commande suivante :
%windir%\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe /codebase UdfSamples.dll
Pour ajouter cette librairie COM en tant que complément actif dans Excel, nous nous rendons dans la fenêtre des options de l'application, à la rubrique « Compléments ».
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/ExcelOptions.jpg
 
En cliquant sur « Atteindre » tout en bas de la fenêtre, on arrive sur une fenêtre de liste des compléments disponibles et enregistrés grâce à l'outil RegAsm.
On ajoute alors notre librairie COM, et en recalculant les formules de notre classeur Excel, nous avons bien accès à nos UDF.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/ExcelAutomation.jpg
 
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/ExcelCompatibilityOK.jpg
 
Débugger une UDF
Il peut arriver que vos UDF ne fonctionnent pas comme vous le souhaitiez. En effet, vous pourriez vous retrouver avec ces valeurs dans les cellules de votre classeur Excel :
  • #NAME : cela signifie que la DLL n'a pas été chargée par les Excel Services.
  • #VALUE : cela signifie que la DLL a bien été chargée mais que la méthode UDF a jeté une exception ou que les paramètres de la méthode sont invalides.
Dans ces deux cas, il peut alors être intéressant de vérifier si la DLL est bien chargée dans le processus hôte des Excel Services, voire de débugger en pas à pas le code de la méthode.
Pour pouvoir débugger une UDF, il faut bien comprendre que les Excel Services l'exécutent dans un processus d'IIS nommé w3wp.exe. Ainsi, nous allons devoir nous attacher à ce processus avec le debugger de Visual Studio.
Sur un serveur SharePoint, il peut y avoir plusieurs processus w3wp.exe, et il va falloir identifier celui qui héberge les Excel Services. Pour ce faire, nous pouvons utiliser l'outil IISApp en lançant la ligne de commande suivante :
iisapp /a [Nom du Shared Service Provider]
Cette commande nous affiche le PID correspondant au bon processus w3wp.exe. Nous pouvons alors nous attacher à ce dernier grâce au menu « Debug / Attach to process » de Visual Studio.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/AttachToProcess.jpg
 
Une fois que nous sommes attaché au processus, nous pouvons vérifier que l'assembly est bien chargé par le processus en affichant la fenêtre des modules du debugger de Visual Studio.
 
/content/f4a19479-ff53-49da-b270-52815b65d1cd/modules.JPG
 
Si l'assembly de l'UDF n'est pas dans la liste, il se peut que les Excel Services aient rencontré un problème pour le charger. Vous pouvez alors consulter l'Event Viewer pour constater une éventuelle erreur.
Nous pouvons maintenant placer des points d'arrêt dans le code de notre UDF. Si le point d'arrêt n'est pas pris en compte, il se peut alors que la version de l'assembly que vous essayez de débugger ne soit pas la même que celle déployée et chargée par les Excel Services. Dans ce cas, il vous suffit de recompiler et de redéployer votre assembly.
 
» Démarrer une discussion