Erweiterungsbibliothek für Excel-Tabellenfunktionen in Managed Code schreiben


Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun, bislang kann man das nicht. Allerdings kann man mit etwas COM Interop das Ganze als Excel Add-In (Vorsicht, nicht COM Add-In) laufen lassen. Wie das geht, will ich im Folgenden mal beschreiben.


Als allererstes muß natürlich in Visual Studio eine einfache Klassenbibliothek angelegt und diese für COM sichtbar gemacht werden. Wichtig dabei ist, dass das COM Interface vom Typ AutoDual ist, sonst sieht Excel die Klasse nicht. Da bei der COM Registrierung mittel Regasm kein Programmable Key geschrieben wird, wir den aber brauchen, müssen wir uns selbst darum kümmern, dass der geschrieben wird. Dafür sind die beiden Methoden RegistrationMethod und UnregistrationMethod zuständig.



Nun ist es relativ einfach, Funktionen mit skalaren Parametern wie integer oder double zu implementieren:


Schwieriger wir es, wenn der Inhalt eines Arrays durchsucht werden soll. Normalerweise würden wir das so machen:


Das funktioniert aber nicht. Da ein Range Objekt in Excel als ein einziges COM Objekt (System.__ComObject) übergeben wird, müssen wir die Werte der einzelnen Zellen herausholen und ein Array erzeugen, welches am besten auch noch 0-basiert ist. Diese Konvertierung wird der eigentlichen Funktion vorgeschaltet:


Dazu kommt noch die Behandlung sog. flüchtiger Funktionen. Das sind solche, die bei gleichen Parameterwerten zu verschiedener Zeit unterschiedliche Ergebnisse liefern, wie z.B. now().


Sind diese im eigenen Code enthalten, müssen wir die Hostanwendung (also Excel) darauf aufmerksam machen (Volatile-Methode). Das führt dann dazu, dass nach Eingabe der Funktion bei jedem Neuberechnen der Tabelle auch diese Funktion berechnet wird. Anderenfalls denkt Excel - da sich die Eingabewerte ja nicht verändert haben - der Rückgabewert wäre konstant.

Wir benötigen dafür einen Zeiger auf die aktuelle Instanz von Excel, weswegen wir IDTExtensibility implementieren müssen und deshalb eine Referenz auf die Extensibility Library und - wegen der Typreferenz (Excel.Application) - die Excel PIA setzen müssen. In dessen OnConnection Event bekommt man diese Instanz übergeben. Dort speichern wir uns den Zeiger in einer lokalen Property. Alle anderen Events des IDTExtensibility Interfaces brauchen wir nicht zu befüllen, müssen diese aber implementieren und die Standard-Inhalte entfernen (Throw Exception, hier im Beispiel nicht angegeben)


Nach dem Kompilieren muß die Assembly nur noch registriert werden: regasm /codebase ExcelExtender.dll
(unter Vista die Console im Admin-Mode starten)

Und am Ende das Add-In Excel bekannt machen:


Danach können wir die Erweiterungsfunktionen auf der Maschine, auf der die Erweiterungsbibliothek registriert wurde verwenden


Weitere Hinweise finden Sie in den Teilen eins, zwei und drei einer 3-teiligen Serie von Shahar Prish.

Der Kollege ist Software Architect im Excel Services Team in Redmond und schreibt darin über User Defined Functions, die sowohl in Excel Services als auch auf dem Client verwendet werden können. Den Excel Services-Anteil kann man in unserem Kontext einfach ausblenden.

Comments (5)

  1. Andreas Adler says:

    Erst einmal Danke für den guten Artikel, er war sehr hilfreich.

    Trotzdem habe ich noch einige Probleme/Fragen zu diesem Thema.

    Ein Problem war erst einmal, dass ich den Code von C# nach Visual Basic übersetzen musste. Das hat im großen und ganzen auch geklappt. Allerdings bereitet mir die Bedingung "if (typeof(ExcelFunctions) != type)" Probleme.

    Ich hab das in meinem VB-Code folgendermaßen implementiert: "If Not TypeOf type Is ExcelFunctions Then ...". Allerdings erhalte ich sofort die Fehlermeldung "Ein Ausdruck vom Typ 'System.Type' kann nie vom Typ 'ExcelExtender.ExcelFunctions' sein." Nach meinem Ermessen müsste die Code-Übersetzung richtig sein. Irgendwas ist aber trotzdem falsch. Hoffentlich können Sie mir hierbei weiterhelfen.

    Außerdem gibt es noch ein anderes Problem. Wenn man das Add-In nun in Excel verwendet und über "Einfügen -> Funktion" seine ExcelFunctions auswählt, sind die Basis-Funktionen "Equals", "GetHashCode", "GetType" und "ToString" zu sehen, was recht unschön ist. Gibt es eine Möglichkeit, diese in Excel auszublenden? Ich habe bisher versucht, in meiner DLL die Funktionen per Overloads/Shadows und einer Private-Deklaration zu überschreiben und für Excel nicht sichtbar zu machen. Allerdings hat das nicht geklappt.

    Danke schonmal im Voraus für die Hilfe.

  2. jensha says:

    Andreas,

    in VB wäre das mit der GetType Funktion zu erledigen:

       <ComRegisterFunction()> _

       Public Shared Sub RegistrationMethod(ByVal t As Type)

         If GetType(ExcelFunctionsVB) IsNot t Then

           Return

         End If

         Dim key As RegistryKey = Registry.ClassesRoot.CreateSubKey("CLSID{" + ClsId + "}Programmable")

         key.Close()

       End Sub

       <ComUnregisterFunction()> _

       Public Shared Sub UnregisterationMethod(ByVal t As Type)

         If GetType(ExcelFunctionsVB) IsNot t Then

           Return

         End If

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

       End Sub

    Wegen der eigentlich im COM Interface überflüssigen Funktionen "Equals", "GetHashCode", "GetType" und "ToString" hatte ich mir noch keine Gedanken gemacht. Ich schaue mal, was ich herausfinde und poste dann hier.

    - Jens

  3. Andreas Adler says:

    Hallo Jens,

    danke für den Hinweis.

    Manchmal ist es von C# nach VB eben doch nicht so einfach. 😉

    Wegen der Object-Basismethoden bin ich so vorgegangen, dass ich die Methoden überschrieben habe (Deklaration als Public Overrides) und dann mit dem Attribut ComVisible(False) versehen hab.

    Hat auch geklappt, nur bei GetType nicht, da die Methode nicht als Overridable deklariert ist. Eine Deklaration mit Shadows brachte leider auch keinen Erfolg. Aber damit kann ich jetzt auch leben, das meiste konnte soweit umgesetzt werden.

    Nochmals Danke für die Mühen und die Hilfe.

    Gruß

    Andreas

  4. M.Binder says:

    Ist es eigentlich möglich, eine COM-DLL oder ein VSTO-Addin ohne Admin-Rechte auf den Zielrechnern zu installieren? Falls ja, wäre ich für einen Link/Tipp sehr dankbar!

    Viele Grüße

    M.Binder

  5. jensha says:

    Eine native COM DLL für Office kann man nicht ohne Admin-Rechten installieren. Für eine eigene Anwendung könnte man "registryless" COM verwenden. Geht, afaik ab XP. Aber ur, wenn man Schreibrechte im Exe-Ordner hat.

    Eine VSTO Assembly kann mittels ClickOnce für den angemeldeten Benutzer ohne Admin-Rechte installiert werden. Ab VSTO 3.0 und gegen Office 2007 und höher (vorher funktionierte die Security noch mit CAS (Code Access Security)) kein Problem. Kommt out of the Box (Publish Tab in den Projekteigenschaften).

    Oder mal meine WebCasts genauer ansehen.

Skip to main content