Créer un Add-in Automation pour Excel
Créer un Add-in Automation pour Excel en C#
Préambule
Ce tutoriel montre comment créer un Addin Automation pour Excel permettant de fournir des fonctions utilisables dans les formules des cellules.
Introduction
Depuis la version 2002 (XP) d'Office, Excel propose le concept d'Add-In Automation. Ceci est donc disponible dans Excel XP, Excel 2003 et Excel 2007.
Ce concept d'Add-In Automation permet d'accéder aux méthodes publiques des Dll COM en tant que Fonctions Définies par l'Utilisateur, c'est-à-dire de pouvoir les utiliser directement dans les formules des cellules.
Cet article décrit la manière de construire un Add-in Automation pour Excel et de le déployer. Je me suis inspiré fortement de "Create an Automation Add-In for Excel using .NET".
Version VB6
Vous pouvez tout à fait développer un tel Add-in avec Visual Basic 6 avec les options par défaut.
Pour cela, il vous suffit de :
- Créer un projet « Dll ActiveX »
- Ajouter un « module de classe »
- Déclarer et implémenter vos méthodes dans ce module de classe :
Option Explicit
Public Function TestAutomationAddInt(ByVal a As Integer, ByVal b As Integer) As Integer
TestAutomationAddInt = a + b
End Function
'Un test de macros prenant deux flottants et en faisant la somme
Public Function TestAutomationAddDouble(ByVal a As Double, ByVal b As Double) As Double
TestAutomationAddDouble = a + b
End Function
'un test avec une plage de cellules
Public Function TestAutomationAddRange(ByVal r As Range) As Double
'On peut très bien accéder aux valeurs des plages de cellules
'Par contre, il peut arriver que définir les propriétés
'des cellules génère une erreur 800A03EC,
'erreur qu'Excel renvoie dans 90% des cas où il y a une erreur
'dans la définition de la propriété
'Par exemple, je n'ai pas trouvé de solution pour définir
'les propriétés de Range.Interior...
'par contre, définir la couleur de la police se fait sans erreur
On Error GoTo erreur
Dim ret As Double
Dim i As Integer, j As Integer
ret = 0
'fait la somme des valeurs des cellules de la plage fournie
For i = 1 To r.Cells.Rows.Count
For j = 1 To r.Cells.Columns.Count
Dim d As Double
d = 0
Dim cell As Range
Set cell = r.Cells(i, j)
'test si la valeur est définie et si c'est un entier...
If Not IsEmpty(cell.Value) Then
d = d + val(cell.Value2)
End If
ret = ret + d
Next
Next
TestAutomationAddRange = ret
Exit Function
erreur:
MsgBox Err.Description
End Function
- Compiler
- Enregistrer votre dll avec « regsvr32 votre_dll.dll »
Pour déboguer, il suffit de faire Ctrl-F5 puis de choisir « Démarrer le programme » / « Start external program » (également accessible dans les propriétés du projet), de cliquer sur « ... » puis de choisir le fichier « excel.exe » dans le dossier d'installation d'Office. Une fois que vous avez ajouté votre DLL dans la liste des macros Automation comme indiqué dans la partie « Tester », si vous mettez des breakpoints dans votre code, vous pourrez déboguer directement votre code à l'exécution (recalcule) de la macro.
Version .Net
Voici la méthode générale :
Créer une « bibliothèque de classes » par exemple « TestAutomationAddin »
Dans les propriétés du projet, dans l'onglet « Compilation »/ « Build », vérifier que « Enregistrer pour Interopérabilité COM » / « Register for COM Interop » est coché.
Toujours dans les propriétés du projet, dans l'onglet « Application », cliquer sur « Information sur l'Assemblage » / « Assembly Information », puis vérifier que « Rendre l'assemblage visible de COM » / « Make assembly COM-Visible » est cochée.
Ajouter la référence COM (clic droit sur le projet, « Ajouter une référence » / « Add reference », onglet COM) : Microsoft Excel X.0 Object Library (où X peut être 10 (XP/2002), 11 (2003), 12 (2007)).
Renommer la classe existante « Class1 » en « TestAutomationAddinClass » par exemple.
Remplacer le code contenu dans cette classe par le code suivant :
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace TestAutomationAddin
{
/// <summary>
/// Interface définissant les "macros" qui seront disponibles
/// dans Excel
/// </summary>
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[Guid("5B016456-A7A0-4cf0-A738-DCC9B400251D")]
public interface ITestAutomationAddin
{
//un test avec des entiers
int TestAutomationAddInt(int a,int b);
//un test avec des floattants
double TestAutomationAddDouble(double a, double b);
//un test avec une plage de cellule
double TestAutomationAddRange(Range r);
}
/// <summary>
/// Classe implémentant les "macros" définies
/// dans l'interface précédente
/// </summary>
[ClassInterface(ClassInterfaceType.None),ComDefaultInterface(typeof(ITestAutomationAddin))]
public class TestAutomationAddin
: ITestAutomationAddin
{
#region ITestAutomationAddin Members
//Un test de macros prenant deux entiers et en faisant la somme
public int TestAutomationAddInt(int a, int b)
{
return a + b;
}
//Un test de macros prenant deux flottants et en faisant la somme
public double TestAutomationAddDouble(double a, double b)
{
return a + b;
}
//un test avec une plage de cellules
public double TestAutomationAddRange(Range r)
{
//On peut très bien accéder aux valeurs des plages de cellules
//Par contre, il peut arriver que définir les propriétés
//des cellules génère une erreur 800A03EC,
//erreur qu'Excel renvoie dans 90% des cas où il y a une erreur
//dans la définition de la propriété
//Par exemple, je n'ai pas trouvé de solution pour définir
//les propriétés de Range.Interior...
//par contre, définir la couleur de la police se fait
//sans erreur
double ret = 0;
try
{
//fait la somme des valeurs des cellules
//de la plage fournie
for (int i = 1; i <= r.Cells.Rows.Count; i++)
{
for (int j = 1; j <= r.Cells.Columns.Count; j++)
{
double val = 0;
Range cell = (Range)r[i, j];
//test si la valeur est définie et
//si c'est un entier...
if (cell.Value2 != null)
Double.TryParse(cell.Value2.ToString(),
out val);
ret += val;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return ret;
}
/// <summary>
/// Permet d'enregistrer nos macros de sorte
/// qu'Excel puisse les voir
/// </summary>
/// <param name="t"></param>
[ComRegisterFunctionAttribute]
public static void RegisterFunction(System.Type t)
{
Microsoft.Win32.RegistryKey key ;
//Excel recherche les composants COM "Programmable"
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\{" + t.GUID.ToString().ToUpper() + "}\Programmable");
//De plus, afin de ne pas avoir de message indiquant
//que "mscoree.dll" est introuvable
//On définit son chemin absolu (normalement
//dans C:WindowsSystem32mscoree.dll"
key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\{" + t.GUID.ToString().ToUpper() + "}\InprocServer32");
key.SetValue("", Environment.GetFolderPath(Environment.SpecialFolder.System) + @"mscoree.dll");
}
/// <summary>
/// Retire l'enregistrement de nos macros (nettoyage)
/// </summary>
/// <param name="t"></param>
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(System.Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\{" + t.GUID.ToString().ToUpper() + "}\Programmable");
}
#endregion
}
}
Compiler le projet pour produire bin\Debug\TestAutomationAddin.dll
Tester
Tester dans Excel 2002 (XP), Excel 2003
Dans le menu Outils, cliquer sur Compléments
Dans la boîte de dialogue Compléments, cliquer sur Automatisation.
Dans la liste des serveurs COM enregistrés, sélectionner votre Add-In Automation « TestAutomationAddin. TestAutomationAddinClass » et cliquer sur OK.
Votre Add-In Automation devrait apparaître dans la boîte de dialogue des Compléments.
Cliquer OK pour fermer la boîte de dialogue des Compléments.
Dans une cellule, ajouter =TestAutomationAddDouble(2,3 ;3,2)
Normalement, la cellule devrait afficher « 5,5 »
Dans une autre cellule, ajouter =TestAutomationAddRange(A3:B5)
Normalement, cela devrait faire la somme des valeurs que vous avez saisi dans la plage « A3:B5 »
Tester dans Excel 2007
Cliquer sur le Bouton Microsoft Office, puis sur le bouton Options Excel.
Cliquer sur Compléments.
Dans Gérer, choisir Compléments Excel, puis cliquer sur Atteindre.
Dans la boîte de dialogue Compléments, cliquer sur Automatisation.
Dans la liste des serveurs COM enregistrés, sélectionner votre Add-In Automation « TestAutomationAddin. TestAutomationAddinClass » et cliquer sur OK.
Votre Add-In Automation devrait apparaître dans la boîte de dialogue des Compléments.
Cliquer OK pour fermer la boîte de dialogue des Compléments.
Dans une cellule, ajouter =TestAutomationAddDouble(2,3 ;3,2)
Normalement, la cellule devrait afficher « 5,5 »
Dans une autre cellule, ajouter =TestAutomationAddRange(A3:B5)
Normalement, cela devrait faire la somme des valeurs que vous avez saisi dans la plage « A3:B5 »
Débogguer
Pour déboguer facilement, il suffit dans les propriétés de votre Bibliothèque de classes, dans l'onglet « Débogage » / « Debug » puis de choisir « Démarrer le programme externe » / « Start external program », de cliquer sur « ... » puis de choisir le fichier « excel.exe » dans le dossier d'installation d'Office. Une fois que vous avez ajouté votre DLL dans la liste des macros Automation comme indiqué dans la partie « Tester », si vous mettez des breakpoints dans votre code, vous pourrez déboguer directement votre code à l'exécution (recalcule) de la macro.
Déploiement .Net
Déploiement manuel
Pour enregistrer la dll après l'avoir déplacée dans un autre dossier, exécuter « regasm » avec l'option /codebase et le nom du fichier de la dll (regasm doit se trouver dans « C:\Windows\Microsoft .Net\Framework\vX.Y.Z\). Si vous obtenez une warning du fait que l'assembly n'est pas signé, ce n'est pas grave.
Déploiement par projet de déploiement
Pour que l'enregistrement se fasse correctement lors de l'installation par un projet de déploiement, il est nécessaire d'ajouter une « custom action ».
La procédure est la suivante :
Dans le projet de votre bibliothèque de classe, ajouter un « Installer » nommé « SelfRegisterInstaller » avec le code suivant à l'intérieur :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration.Install;
using System.Runtime.InteropServices;
namespace TestAutomationAddin
{
/// <summary>
/// Classe permettant d'enregistrer votre DLL pour Interop COM
/// avec l'option CodeBase et l'appel systématique
/// des méthodes ComRegisterFunction/ComUnregisterFunction
/// </summary>
[RunInstaller(true)]
public partial class SelfRegisterInstaller : Installer
{
public SelfRegisterInstaller()
{
InitializeComponent();
}
//enregistre l'assembly et appelle les méthodes marquées
//comme ComRegisterFunction
public override void Install(System.Collections.IDictionary stateSaver)
{
base.Install(stateSaver);
//appel le service d'enregistrement
//interop COM "Regasm /codebase"
RegistrationServices reg = new RegistrationServices();
reg.RegisterAssembly(this.GetType().Assembly, AssemblyRegistrationFlags.SetCodeBase);
}
//désenregistre l'assembly et appelle les méthodes marquées
//comme ComUnregisterFunction
public override void Uninstall(System.Collections.IDictionary savedState)
{
//désenregistre de l'interop COM
RegistrationServices reg = new RegistrationServices();
reg.UnregisterAssembly(this.GetType().Assembly);
base.Uninstall(savedState);
}
}
}
Dans le projet de Setup, dans l' « Editeur du système de fichiers », ajouter la « sortie principale » du projet de Bibliothèque de classes dans le dossier « Dossier de l'application » / « Application Folder »
dans l'« Editeur d'actions personnalisées » (clic droit sur le projet de Setup, « Vues » / « View »), ajouter une « action personnalisée » dans « Install » et « Uninstall » en choisissant « Sortie principale » / « Primary Output » (dans le dossier « Dossier de l'application » / « Application Folder »)
Tout ceci permet d'exécuter les méthodes ComRegisterFunction de la DLL Addin Automation à l'installation et à la désinstallation.
Points Importants
La bibliothèque de classes doit être enregistrée avec regasm.exe ou en cochant l'option « Enregistrer pour interopérabilité COM ».
Pour que Excel trouve notre Add-In Automation, il faut ajouter une sous clé « Programmable » dans l'enregistrement de la classe en tant que classe COM (dans HKCR\CLSID\{xxx}\.) . Ceci doit se faire dans deux méthodes static marquées avec les attributs ComRegisterFunction et ComUnregisterFunction.
Pour empêcher le message d'erreur "mscoree.dll est introuvable", il faut changer la valeur inscrite dans la sous clé "InProcServer32", pour mettre le chemin complet de « mscoree.dll ».
Il est nécessaire de marquer la classe qui implémente le complément Automation avec l'attribut ClassInterface. Vous avez ensuite deux solutions pour donner une valeur à cet attribut :
- ClassInterface.None : il faut alors définir une « interface par défaut » avec toutes les méthodes accessibles par Excel qu'implémente la classe marquée. Cela vous permet de définir explicitement ce qui est exposé. C'est la méthode recommandée pour les développements de production.
- ClassInterface.AutoDual : il suffit juste de définir et d'implémenter les méthodes directement et l'interface COM sera générée automatiquement. Cette méthode est non recommandée car les identifiants des interfaces peuvent changer lors que la classe change. On peut marquer « l'interface par défaut » avec l'attribut InterfaceType pour spécifier comment sont exposées ses méthodes :
- ComInterfaceType.InterfaceIsIDispatch : les méthodes sont résolues à l'exécution et ne sont donc pas incluses dans la typelib générée pour la bibliothèque de classe. On pourra les utiliser mais elles n'apparaîtront pas dans l'Intellisense ou dans la liste des macros disponibles.
- ComInterfaceType.InterfaceIsIUnknown : les méthodes sont résolues à la compilation et sont donc incluses dans la typelib générée pour la bibliothèque de classe. On pourra les utiliser et elles apparaîtront dans l'Intellisense et dans la liste des macros disponibles.
- ComInterfaceType.InterfaceIsDual : les méthodes peuvent être résolues à l'exécution ou à la compilation et sont donc incluses dans la typelib générée pour la bibliothèque de classe. On pourra les utiliser et elles apparaîtront dans l'Intellisense et dans la liste des macros disponibles.
On peut préciser explicitement « l'interface par défaut » de la classe de nos macros Automation avec l'attribut ComDefaultInterface.
Il est nécessaire de définir au moins le Guid de « l'interface par défaut » afin de ne pas avoir à réenregistrer la bibliothèque de classe à chaque fois que l'on ajoute/supprime une méthode à cet interface.
Pour faire l'enregistrement correctement, on utilise les RegistrationServices dans un Custom Installer (un composant qui dérivent de la classe Installer et qui est marquée avec l'attribut RunInstaller(true)) et qui sera appelé à notre demande par une Custom Action.
Problèmes courants
Quand vous développez des Addins Automation, vous pouvez vous trouver confronté à un certain nombre de problèmes :
- Problème d'installation : là, ca va il y a des solutions
- Problème « 0x800A03EC » lors de l'exécution des méthodes exposées...là par contre...
- ...
Problème d'enregistrement
Description
Quand vous enregistrez votre DLL .Net en tant qu'Add-in Automation dans Excel, vous pouvez recevoir le message d'erreur : « Le fichier que vous avez spécifié ne contient pas de nouveau serveur Automation, ou vous ne disposez pas des privilèges suffisants pour enregistrer le serveur Automation. » ou en anglais : « The file you selected does not contain a new Automation Server, or you do not have sufficient privileges to register the Automation Server. ».
Cause
Cela est dû au fait qu'une DLL .Net ne contient pas les points d'entrée COM. En fait, c'est la dll « mscoree.dll », constituant le coeur du framework .Net, qui expose n'importe quelle DLL .Net en tant que COM. C'est cette DLL qui sert de InprocServer et qui localise la DLL .Net qui implémente les méthodes demandées.
Solution
Si vous obtenez cette erreur, c'est que vous avez cliqué sur « Parcourir » et que vous avez choisi votre DLL dans son dossier. Mais cette méthode ne marche pas. Normalement, votre Add-In Automation devrait se trouver dans la liste qui se trouve à côté du bouton « Parcourir ». Il vous suffit de choisir votre DLL dans la liste, sous le nom « espace_de_nom.nom_classe ».
Problème de mscoree.dll
Description
Quand vous ajoutez (ou que vous modifiez) votre DLL à la liste des « macros complémentaires », un message d'erreur « mscoree.dll est introuvable, voulez-vous supprimer le complément ?» apparaît.
Cause
Cela vient du fait que pour une raison quelconque, Excel recherche « mscoree.dll » dans le dossier de votre DLL (ou du moins pas dans le dossier System32) et forcément ne la trouve pas...Cela vient du fait que « regasm » a enregistré dans la sous clé « InProcServer32 » de l'enregistrement classique des classes COM, « mscoree.dll » sans son chemin.
Solution
Normalement, le code présenté ci-dessus devrait avoir résolu le problème. Cependant la solution manuelle reste de localiser l'enregistrement de la classe et de modifier la sous clé « InProcServer32 » et d'ajouter le chemin complet de « mscoree.dll » à savoir « C:\Windows\System32\mscoree.dll ».
Problème de HRESULT 0x800A03EC
Alors là, c'est beaucoup plus compliqué. Tout d'abord, c'est une exception qui survient à l'exécution et qui peut donner l'affichage « #VALEUR » ou « #ERREUR » ou « #NOM » ou « #REF », ou alors la valeur que renvoie votre fonction au moment de l'exception suivant l'humeur d'Excel et des try/catch que vous avez mis dans votre code... En principe, cette erreur est renvoyée quand vous avez défini une valeur invalide pour une propriété d'un objet Excel quelque soit cette valeur invalide et quelque soit la propriété en question, c'est-à-dire dans 90% des cas.
J'ai eu le cas par exemple pour définir la couleur de fond d'une cellule (et je n'ai toujours pas trouvé de solution, d'autant que le même code marche parfaitement dans une macro du classeur). Par contre, définir la couleur de la police marche parfaitement.
Bibliographie
Voici quelques articles qui parlent d'Add-in Automation :
Q291392 - Excel COM add-ins and Automation add-ins
Q285337 - How To Create a Visual Basic Automation Add-in for Excel Worksheet Functions
Q278328 - XL2002: How to Mark an Automation Add-In Function as Volatile
How to: Use Managed Code as an Automation Add-In for Excel (C# Programming Guide)
Create an Automation Add-In for Excel using .NET
Un code source est également disponible: http://codes-sources.commentcamarche.net/source/44236-creer-un-addin-automation-pour-excel
- Comment faire un add
- Excel automation - Meilleures réponses
- Excel addin - Meilleures réponses
- Jbutton add actionlistener - Conseils pratiques -Java
- Jtable add row - Forum Java
- C# richtextbox add line - Forum VB.NET
- Add definition ✓ - Forum C# / .NET
- Listview add item - Forum C# / .NET