Artikel

November 2005 | Artikel

Die Weltformel

(Link zum Artikel: http://www.it-republik.de/dotnet/artikel/0764)

Excel um .NET-Funktionen erweitern

Text: von Martin Szugat
Die Anwendungen der Microsoft-Office-Suite, allen voran Excel und Word, bieten zahlreiche Erweiterungsmöglichkeiten: Angefangen bei den einfachen VBA-Makros bis hin zu komplexen SmartDocuments. Eine Form der Erweiterung wird oftmals übersehen: Das Einbinden externer Funktionen in Excel-Formeln mittels Automation-Add-ins. Diese lassen sich auch mit .NET implementieren und erleichtern so beispielsweise die Integration von Web Services in Office.

Automation-Add-ins sind ein Überbleibsel aus den Zeiten, in denen COM die bevorzugte Komponententechnologie und von .NET noch keine Rede war. Sie unterscheiden sich von den reinen COM-Objekten durch eine zusätzliche Automatisierungsschnittstelle - namentlich IDispatch. Diese Schnittstelle erlaubt es einem COM-Client - wie eben Excel - zur Laufzeit die verfügbaren Methoden aufzulisten und eine Methode über einen eindeutigen Bezeichner aufzurufen. Im Unterschied zum Aufruf über Funktionszeiger wird dies als Late-Binding bezeichnet und dürfte insbesondere Visual-Basic-Entwicklern der alten Schule noch bestens bekannt sein.

Zunehmende Automatisierung
Excel nutzt diese Automatisierungsschnittstelle, um die Methoden beliebiger COM-Objekte als Erweiterungsfunktionen innerhalb von Excel-Formeln zur Verfügung zu stellen. Um ein COM-Objekt mit einer Automatisierungsschnittstelle als Automation-Add-in zu registrieren, muss in Excel der Menüpunkt Extras/Add-Ins gewählt und im daraufhin erscheinenden Dialog die Schaltfläche Automatisierung betätigt werden. Anschließend wird ein Objekt aus der Liste der verfügbaren Automatisierungsserver ausgewählt. Die Methoden dieses Objekts stehen schließlich als Funktionen innerhalb einer Formel zur Verfügung. Hierfür wird über den Dialog Einfügen/Funktion zuerst eine Kategorie, sprich ein Automation-Add-in, und dann die jeweils gewünschte Funktion beziehungsweise Methode ausgewählt (Abb. 1).

Ein COM-Objekt mit einer IDispatch-Schnittstelle kann in jeder COM-tauglichen Programmiersprache wie zum Beispiel Visual Basic 6.0 implementiert werden. Aber auch das .NET Framework bietet die Möglichkeit über das COM Interop COM-Objekte zu erstellen. Die Vorteile dieser Variante sind:
  • Verwendung von vorhandenem .NET-Code
  • Verfügbarkeit sowohl für .NET- als auch COM-Clients
  • Einfache Integration von Web Services

Insbesondere der erst und letzt genannte Punkt wird von einem in der Praxis eingesetzten Projekt namens ProTag demonstriert, welches im Rahmen eines Forschungsprojektes entwickelt wurde und unter [1] zur Verfügung steht.
Excel.NET schon jetzt
Im Folgenden wird die Realisierung eines Automation-Add-in anhand eines einfacheren Beispiels namens „Excel.NET“ demonstriert. Dieses mit Visual Basic .NET entwickelte Automation-Add-in bietet zwei einfache Funktionen an: Add und Substract, um zwei Zahlen zu addieren beziehungsweise zu subtrahieren. Der Quellcode (Listing 1) ist erwartungsgemäß kurz und simpel. Einzig und allein das ClassInterface- und das ProgId-Attribut für die Deklaration der Klasse Functions stechen hervor. Diese Attribute entstammen dem Namensraum System.Runtime.InteropServices und bestimmen zum einen die verfügbaren COM-Schnittstellen und zum anderen die ProgID der betreffenden Klasse. Der Wert ClassInterfaceType.AutoDual sorgt dafür, dass die Klasse Functions zwei COM-Schnittstellen implementiert: Eine reguläre COM-Schnittstelle, welche die Methoden der Klasse widerspiegelt, und eine IDispatch-Schnittstelle für die eingangs beschriebene Automatisierung.
Das ProgID-Attribut steht für den so genannten Programmatic Identifier einer COM-Klasse. Dieser identifiziert eine COM-Klasse, dient zu deren Instantiierung und ist mit dem Klassennamen einer .NET-Klasse vergleichbar. Per Konvention setzt sich die ProgID aus dem Namen der Bibliothek und dem Klassennamen sowie eventuell einer Versionsnummer zusammen. Zusätzlich erhält jede COM-Klasse eine eindeutige CLSID (Abkürzung für „Class Identifier“), die automatisch generiert wird und einem GUID (Global Unique Identifier) entspricht. Ein GUID besteht aus einer Folge von 32 hexadezimalen Zeichen und wird meist wie folgt angegeben: {098f2470-bae0-11cd-b579-08002b30bfeb}. Durch die Verwendung des ClassInterface-Attributs werden grundsätzlich sämtliche öffentlichen Methoden der betreffenden Klasse als COM-Methoden einem Client zur Verfügung gestellt. Hiervon betroffen sind auch die Methoden, die direkt oder indirekt von der allgemeinen Basisklasse Object geerbt werden - namentlich die Methoden GetType, Equals, GetHashCode und ToString. In den meisten Fällen ist es jedoch nicht gewünscht, dass diese Methoden als Funktionen in einer Excel-Formel auftauchen. Um daher ihre Sichtbarkeit einzuschränken, müssen sie in der Functions-Klasse überschrieben und mit dem Attribute ComVisible versehen werden, wobei für den Parameter visibility der Wert False einzusetzen ist. Einzig die GetType-Methode lässt sich nicht überschreiben, da sie nicht als Overridable markiert ist.

Listing 1
Die Functions-Klasse
  1. Imports System.Runtime.InteropServices
  2. Imports Microsoft.Win32
  3. Imports System.Environment
  5. <ClassInterface(ClassInterfaceType.AutoDual), _
  6. ProgId("ExcelNET.Functions")> _
  7. Public Class Functions
  9. Public Function Add(ByVal n1 As Double, ByVal n2 As Double) As Double
  10. Return n1 + n2
  11. End Function
  13. Public Function Subtract(ByVal n1 As Double, ByVal n2 As Double) As Double
  14. Return n1 - n2
  15. End Function
  17. #Region "Nicht sichtbare Methoden"
  19. <ComVisible(False)> _
  20. Public Overrides Function ToString() As String
  21. Return "Excel.NET Functions"
  22. End Function
  24. <ComVisible(False)> _
  25. Public Overrides Function GetHashCode() As Integer
  26. Return MyBase.GetHashCode
  27. End Function
  29. <ComVisible(False)> _
  30. Public Overloads Overrides Function Equals(ByVal obj As Object) As Boolean
  31. Return MyBase.Equals(obj)
  32. End Function
  34. #End Region
  36. #Region "COM Registrierung"
  38. <ComRegisterFunction()> _
  39. Public Shared Sub RegisterClass(ByVal t As Type)
  40. Dim Key As RegistryKey = _
  41. Registry.ClassesRoot.CreateSubKey("CLSID\{" & _
  42. t.GUID.ToString().ToUpper() & "}")
  43. Key.CreateSubKey("Programmable")
  44. Key.SetValue("", "Excel.NET Functions")
  45. Key.CreateSubKey("InprocServer32\").SetValue("", _
  46. GetFolderPath(SpecialFolder.System) & _
  47. "\mscoree.dll")
  48. Key = Registry.ClassesRoot.CreateSubKey( _
  49. "ExcelNET.Functions")
  50. Key.SetValue("", "Excel.NET Functions")
  51. End Sub
  53. #End Region
  55. End Class
Unterschied zwischen Theorie und Praxis
Die Entwicklung von Automations-Servern mit .NET ist wie hier gezeigt äußerst einfach, doch wie so oft tauchen die eigentlichen Probleme erst später auf: in diesem Fall bei der Registrierung der COM-Klasse. Wie jede herkömmliche COM-Klasse muss auch eine in .NET programmierte COM-Klasse in der Registrierung vermerkt werden, damit sie den COM-Clients zur Verfügung steht. Diese Aufgabe übernimmt wiederum das COM Interop des .NET Framework beziehungsweise Visual Studio .NET. Hierfür ist in den Projekteigenschaften unter Konfigurationseigenschaften/Erstellen die Option Für COM-Interop registrieren zu aktivieren. Damit ist allerdings erst die halbe Arbeit getan. Um die Klasse auch für die Automatisierung bereitzustellen, muss in der Registrierung für die CLSID der Klasse ein spezieller Schlüssel namens Programmable hinterlegt werden. Dies erledigt COM Interop allerdings nicht, sodass die Klasse dies selbst übernehmen muss. Hierfür definiert sie eine statische (als Shared deklarierte) Methode namens RegisterClass, die mit dem Attribut
ComRegisterFunction versehen ist. Die Bedeutung dieses Attributs wird im Folgenden noch ersichtlich werden. Wichtig ist bei der Verwendung dieses Attributs nur, dass die Parametersignatur der zugehörigen Methode stimmt. Der Methodenname spielt keine Rolle und kann daher frei gewählt werden.
Der einzige Parameter wird beim Aufruf der Methode mit dem Type-Objekt für die Functions-Klasse belegt. Über die GUID-Eigenschaft des Type-Objekts kann die CLSID der Functions-Klasse in Erfahrung gebracht werden und mit Hilfe der RegistryKey-Klasse aus dem Namensraum Microsoft.Win32 wird der dazu passende Registrierungsschlüssel ermittelt. Hierfür werden die hexadezimalen Zeichen der CLSID in Großbuchstaben überführt. Unterhalb des CLSID-Schlüssels wird ein weiterer Schlüssel namens Programmable angelegt, sodass die Functions-Klasse fortan als Automations-Server fungiert. Zudem wird der Standardwert des CLSID-Schlüssels mit einer benutzerfreundlichen Beschreibung versehen. Diese wird bei der Auswahl des Automatisierungs-Server innerhalb von Excel angezeigt. Schließlich wird noch eine kleine Korrektur an den von COM Interop getätigten Einstellungen vorgenommen: Da eine .NET-DLL für ihre Ausführung die CLR-Runtime benötigt, ist als so genannter „Inprocess Server“ nicht die eigentliche DLL, sondern die Datei Mscoree.dll eingetragen, welche die Laufzeitumgebung von .NET beherbergt. Da jedoch kein absoluter Pfad für die Datei genannt wird, kann es unter bestimmten Bedingungen passieren, dass Excel die DLL nicht findet und folglich das Add-in nicht lädt. Um dies zu vermeiden, wird der absolute Pfad gesetzt. Hierbei ist die Environment-Klasse behilflich, welche über ihre GetFolderPath-Methode und die Konstante SpecialFolder.System den Pfad zum System32-Verzeichnis von Windows liefert - dem Aufenthaltsort der Datei mscoree.dll.
Auch an dem Registrierungsschlüssel für die ProgID der Functions-Klasse wird eine Änderung durchgeführt: Sein Standardwert wird ebenfalls mit einer benutzerfreundlichen Beschreibung versehen. Unter diesem Wert wird die Klasse im Add-ins-Dialog von Excel aufgeführt. Für die Darstellung im Dialog Funktion einfügen wird übrigens als Kategorienname die ProgID verwendet - also drei unterschiedliche Bezeichnungen für ein und dasselbe Objekt. Es ist daher zu empfehlen sowohl für den CLSID- als auch den ProgID-Schlüssel denselben Standardwert zu setzen und eine aussagekräftige ProgID zu wählen.
Showtime
Um die beschriebenen Änderungen an der Registrierung durchzuführen, muss die DLL, die die Functions-Klasse zur Verfügung stellt, registriert werden. Bei gewöhnlichen COM-DLLs wird hierzu das Programm RegSvr32.exe ausgeführt. Bei .NET-DLLs mit COM-Klassen muss dagegen das Programm RegAsm.exe wie folgt aufgerufen werden:
  1. regasm.exe Excel.NET.Functions.dll

Dieses Programm ist im Ordner C:\WINDOWS\Microsoft.NET\Framework\v1.x.yyyy zu finden. Es lädt eine .NET-DLL, registriert die .NET-Klassen als COM-Klassen und sucht anschließend nach einer Klasse mit einer statischen Methode, welche die Signatur der eben beschriebenen RegisterClass-Methode aufweist und welche das ComRegisterFunction-Attribut besitzt. Diese Methode wird dann für jede Klasse innerhalb des Assemblies aufgerufen und das jeweils passende Type-Objekt übergeben. Ist eine Klasse auf diese Weise registriert worden, kann sie in Excel als Automation-Add-in verwendet werden. Selbst ein Debugging innerhalb von Visual Studio .NET ist möglich. Hierfür wird in einer der beiden Methoden Add und Substract ein Haltepunkt gesetzt und in den Konfigurationseigenschaften des zugehörigen Projektes wird unter Debuggen als externes Programm die Datei Excel.exe eingetragen. Wird das Projekt nun zur Ausführung gebracht, wird Excel gestartet. In Excel muss das Add-in Excel.NET Functions über den bekannten Add-Ins-Dialog geladen werden, um anschließend eine beliebige Zelle mit beispielsweise folgender Formel zu füllen: =Add(1;2). Nach betätigen der Enter-Taste unterbricht Visual Studio .NET die Ausführung und springt zu dem Haltepunkt in der Add-Methode.
Willkommen in der Wirklichkeit
Der Weg über das Programm RegAsm.exe mag für das Debugging ausreichend sein, doch für die Distribution der Anwendung wird ein Setupprogramm benötigt, welches die notwendigen Registrierungsschritte durchführt. Bedauerlicherweise bietet das mit Visual Studio .NET ausgelieferte Setup-Projekt von Haus aus nicht die erforderlichen Funktionalitäten, um die hier vorgestellte DLL-Datei Excel.NET.Functions.dll einwandfrei zu registrieren. Zwar kann in einem Setupprojekt eine DLL (über Ansicht/Dateisystem) als „selbstregistrierend“ (Eigenschaft Register auf vsdrpCOMSelfReg setzen) gekennzeichnet werden, dies führt allerdings nur dazu, dass die DLL mittels RegSvr32.exe zum Zeitpunkt der Setup-Erstellung registriert wird und die so generierten Registrierungsschlüssel in das fertige Setup übernommen werden. Die Methode RegisterClass wird hierbei nicht aufgerufen. Deshalb kann die Eigenschaft Register auch auf vsdrpDoNotRegister gesetzt werden. Die Registrierung muss dann jedoch an anderer Stelle erfolgen.
Derartige Aktionen können über die Ansicht Benutzerdefinierte Aktionen festgelegt werden. Eine benutzerdefinierte Aktion kann die Ausführung einer EXE-Datei oder eines Skripts sein und sollte für alle Phasen eines Setups (Installieren, Commit, Rollback und Deinstallieren) gleichermaßen bereitgestellt werden. Das in der Praxis am häufigsten eingesetzte „ausführende“ Organ ist eine DLL-Datei, die entweder eine API-Funktion exportiert oder eine Installer-Klasse beinhaltet. Im letzt genannten Fall muss die InstallerClass-Eigenschaft der jeweiligen Aktion auf True gesetzt werden. Über die Eigenschaft CustomActionData können der Installer-Klasse zusätzliche Informationen übermittelt werden. Für das hier beschriebene Setup für das Excel.NET-Add-in wird die CustomActionData-Eigenschaft beispielsweise auf /AssemblyFile=Excel.NET.dll gesetzt und als DLL-Datei wird Register.dll festgelegt.
Nachbesserungen
Dieses Assembly wird durch ein weiteres Projekt namens „Register“ erzeugt, welches eine einzige Klasse beinhaltet - ebenfalls mit dem Namen Register. Die Register-Klasse erbt von der Klasse System.Configuration.Install.Installer und besitzt zudem das Attribut RunInstaller(True), wodurch es als Installer-Klasse gekennzeichnet ist. Für Installer-Klassen hält Visual Studio .NET eine spezielle Vorlage bereit, die bereits einen Teil des notwendigen Codes zur Verfügung stellt. In Listing 2 ist daher nur der für diese Lösung spezifische Code zu sehen. Für jede Phase der Installation gibt es in der Installer-Basisklasse eine gleichnamige Methode, die in der abgeleiteten Klasse überschrieben werden kann. Die Register-Klasse überschreibt unter anderem die Commit-Methode, leitet den Aufruf zuerst an die Basisklasse weiter und versucht dann die DLL-Datei, welche in der CustomActionData-Eigenschaft festgelegt wurde, mithilfe einer Instanz der RegistrationServices-Klasse zu registrieren. Deren RegisterAssembly-Methode erfüllt denselben Zweck wie ein Aufruf von RegAsm.exe. Zusätzlich zu der DLL-Datei, welche als Assembly-Objekt übergeben werden muss, kann im zweiten Parameter die Art und Weise der Registrierung festgelegt werden. Durch das Flag SetCodeBase wird die Methode angewiesen, den Speicherort der DLL-Datei in der Registrierung zu vermerken. Da die Commit-Methode erst dann aufgerufen wird, wenn sämtliche Dateien bereits in das Installationsverzeichnis kopiert wurden, ist dieses Vorgehen möglich. Der umgekehrte Vorgang, das heißt die Deregistrierung einer DLL-Datei mittels der Methode UnregisterAssembly, erfolgt in den Installer-Methoden Rollback und Uninstall. Um aus dem Namen der DLL-Datei ein Assembly-Objekt zu erhalten, wird in allen drei Fällen die Hilfsmethode GetAssembly aufgerufen. Sie erfragt als erstes den Wert des Parameters AssemblyFile von dem Context-Objekt der Installer-Klasse beziehungsweise von der zugehörigen Parameters-Auflistung, ermittelt anschließend den Speicherort der Datei Register.dll (das Installationsverzeichnis) und lädt schließlich das gewünschte Assembly.

Listing 2
Die Register-Klasse
  1. Imports System.ComponentModel
  2. Imports System.Configuration.Install
  3. Imports System.Runtime.InteropServices
  4. Imports System.Reflection
  5. Imports System.IO
  7. <RunInstaller(True)> Public Class SmartTagRegister
  8. Inherits System.Configuration.Install.Installer
  10. #Region " Vom Component Designer generierter Code " ...
  12. Public Const ParamNameAssemblyFile = "AssemblyFile"
  14. Private Function GetAssembly()
  15. Dim AssemblyFile As String = _
  16. Me.Context.Parameters(ParamNameAssemblyFile)
  17. If AssemblyFile Is Nothing Then
  18. Throw New InstallException(ParamNameAssemblyFile & _
  19. " is not set.")
  20. End If
  21. Dim f As New FileInfo( _
  22. [Assembly].GetExecutingAssembly.Location)
  23. Return [Assembly].LoadFrom( _
  24. f.Directory.FullName() & "\" & AssemblyFile)
  25. End Function
  27. Public Overrides Sub Commit( _
  28. ByVal savedState As System.Collections.IDictionary)
  29. MyBase.Commit(savedState)
  30. Dim rs As New RegistrationServices
  31. rs.RegisterAssembly(GetAssembly(), _
  32. AssemblyRegistrationFlags.SetCodeBase)
  33. End Sub
  35. Public Overrides Sub Rollback( _
  36. ByVal savedState As System.Collections.IDictionary)
  37. MyBase.Rollback(savedState)
  38. Dim rs As New RegistrationServices
  39. rs.UnregisterAssembly(GetAssembly)
  40. End Sub
  42. Public Overrides Sub Uninstall( _
  43. ByVal savedState As System.Collections.IDictionary)
  44. MyBase.Uninstall(savedState)
  45. Dim rs As New RegistrationServices
  46. rs.UnregisterAssembly(GetAssembly)
  47. End Sub
  49. End Class
It's not a bug, it's a feature!
Zudem registriert das Setup-Programm das Add-in bei Excel, indem es in der Registrierung unter den folgenden Schlüsseln (für die unterschiedlichen Versionen von Excel) jeweils einen Eintrag mit der ProgID der Functions-Klasse als Name ablegt:
  1. HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager
  2. HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager

Der Wert der Einträge ist hierbei unwesentlich. Die ProgID des Add-ins reicht Excel aus, um das Add-in in dem Add-ins-Dialog aufzulisten. Aktiviert ist es hierdurch allerdings noch nicht. Dies muss manuell durch den Benutzer erfolgen. Alternativ ließe sich auch im benachbarten Schlüssel Options ein Eintrag mit dem folgenden Inhalt anlegen:
  1. /A "ExcelNET.Functions"

Der Name des Eintrags muss dabei mit der Zeichenfolge „OPEN“ beginnen, welcher eine eindeutige Nummer folgt. Um diese zu festzusetzen, müsste ein Setup-Programm ermitteln, welche Nummern bereits vergeben sind und welche Nummer noch frei ist. Hierauf wurde in dem hier vorgestellten Setup verzichtet. Zwei ungelöste Probleme bleiben abschließend noch zu erwähnen: Erstens muss der Installationsvorgang von einem Benutzer mit Administratorrechten ausgeführt werden, da der (schreibende) Zugriff auf die Registrierungsdaten von COM spezielle Rechte verlangt. Folglich sollte ein Administrator das Setup ausführen. Während des Installationsvorganges bietet das Setup-Programm dem Administrator die Möglichkeit, das Programm für alle Benutzer zu installieren. So verlockend diese Option auch klingen mag, so wenig hilfreich ist sie. Denn die einzigen benutzerspezifischen Einstellungen sind die eben beschriebenen Registrierungseinträge für Excel und die werden, da sie unterhalb von HKEY_CURRENT_USER stehen, nur für den aktuellen Benutzer angelegt. Die anderen Benutzer sind demnach gezwungen das Add-in nachträglich über den eingangs erwähnten Dialog zur Auswahl eines Automations-Server hinzuzufügen. Zweitens muss in den Sicherheitsoptionen von Excel (Extras/Makro/Sicherheit) auf der Seite Vertrauenswürdige Herausgeber die Option Allen installierten Add-ins und Vorlagen vertrauen aktiviert sein. Andernfalls verweigert Excel die Nutzung des Add-ins. Eine Signierung des zugehörigen .NET-Assemblies bringt übrigens nicht die erhoffte Lösung, da nicht das Assembly selbst, sondern die DLL-Datei Mscoree.dll von Excel geladen wird. Stattdessen müsste eine so genannte „Shim-Dll“, also eine stellvertretende und signierte COM-DLL implementiert werden, welche das eigentliche Assembly bei Bedarf lädt und die eingehenden Aufrufe an dieses Assembly weiterleitet. Für die Generierung solcher Shim-DLLs stellt Microsoft unter [2] einen speziellen Assistenten zur Verfügung. Der allerdings unterstützt von Haus aus keine Automationsserver, sodass hier noch eine manuelle Anpassung (von C++-Code) notwendig ist.
Fazit
Die Programmierung von Erweiterungsfunktionen ist dank .NET zwar ein leichtes Unterfangen. Die Erweiterungen unter Verwendung der von Visual Studio .NET vorgegebenen Mittel beim Kunden zu installieren bereitet jedoch leichte Kopfschmerzen. Mit der hier vorgestellten Lösung basierend auf der allgemein verwendbaren Register-Klasse lassen sich diese zumindest mindern.
Martin Szugat ist seit vielen Jahren als freischaffender Fachautor im Bereich der Software-Entwicklung tätig. Im Rahmen eines Forschungsprojektes am Bioinformatik-Lehrstuhl der Universität München (LMU) beschäftigte er sich unter anderem mit der Einbindung von Web Services in Office-Anwendungen (siehe [1]). Bei Fragen erreichen Sie ihn unter Martin.Szugat@GMX.net.
Links & Literatur

Anzeige

Kommentare

+ Neuen Kommentar verfassen
zurück zum Seitenanfang