Excel-Funktionen unlimited - Der Entwickler - Das Magazin für professionelle IT-Lösungen

Dazu müssen einige Hindernisse überwunden werden, die vorerst durch einfache Beispiele demonstriert werden sollen. Das erste davon ist der Umstand, dass alle Funktionen in Excel, egal ob eingebaut oder benutzerdefiniert, nur jenen Bereich beschreiben können, der zum Zeitpunkt ihres Aufrufes ausgewählt ist [1]. Das folgende Beispiel soll dies verdeutlichen:

Public Function writeToanotherCell() As Variant

On Error GoTo myerr

Application.ActiveCell.Offset(1, 0).Value = 100

writeToanotherCell = 0

Exit Function

myerr:

MsgBox Err.Number & " " & Err.Description

End Function

In dieser Funktion wird versucht, die unterhalb der aktuellen Zelle liegende Zelle zu beschreiben. Der Aufruf aus dem Code-Editor oder aus einer anderen Funktion funktioniert reibungslos. Wird die Funktion aber als benutzerdefinierte Funktion durch Eingabe von =writeToanotherCell () aus einer beliebigen Zelle aufgerufen, tritt ein Fehler auf (Abb. 1).

Abb. 1: Der Versuch, aus einer Funktion eine andere Zelle zu beschreiben, schlägt fehl

Zudem muss eine Funktion, die Daten aus einer Datenbankabfrage zurückliefern soll, mehr als nur eine Zelle beschreiben können, und das Ergebnis muss mit der Funktion und ihren Parametern verknüpft bleiben, um auf Änderungen des Abfrageergebnisses entweder durch Änderung der Daten innerhalb der Datenbank oder durch Änderungen der Funktionsparameter reagieren zu können. Beide Anforderungen können auf den ersten Blick durch Excel Matrixformeln erfüllt werden:

Public Function myMatrixformula(base As Integer) As Variant

Dim retval(4, 4) As Variant

For i = 0 To 4

For j = 0 To 4

retval(i, j) = base ^ i

Next j

Next i

myMatrixformula = retval

End Function

Diese Funktion befüllt eine 5 x 5 Felder große Tabelle mit Werten, die vom übergebenen Parameter base abhängen. Damit die Funktion eine Tabelle statt eines Einzelwertes zurückgibt, muss der erforderliche Bereich markiert und die Funktionseingabe durch gleichzeitiges Drücken von <STRG><ALT><ENTER> abgeschlossen werden (Abb.2).

Abb. 2: Mit einer Matrixfunktion lassen sich auch ganze Zellbereiche befüllen, allerdings nur jener Bereich, der beim Aufruf der Funktion selektiert war
Aus dem Beispiel wird ersichtlich, dass auch Matrixfunktionen nicht zur Rückgabe von tabellarischen Daten unbekannter Größe geeignet sind, da der benötigte Bereich zum Zeitpunkt der Funktionseingabe nicht bekannt ist und daher auch nicht ausgewählt werden kann. Bei Auswahl eines zu großen Bereiches werden überschüssige Zellen mit #N/V-Werten ausgefüllt. Bei einer zu kleinen Auswahl hingegen verschluckt Excel einfach die Werte, die im ausgewählten Bereich nicht untergebracht werden können. Das zur Funktionseingabe erforderliche gleichzeitige Drücken von <STRG><ALT><ENTER> ist eine weitere unangenehme Eigenschaft von Matrixfunktionen.
Funktionen zum komfortablen Einfügen von tabellarischen Daten sollten also folgende Eigenschaften aufweisen.

Die Eingabe muss ohne vorherige Auswahl des zu befüllenden Bereiches möglich sein.
Verändert sich die Größe der zurückgegeben Datenmenge z.B. durch Einfügungen in der Datenbank oder durch Änderung der Funktionsparameter, muss die Größe der Tabelle automatisch angepasst werden.
Bei der Eingabe muss nicht <STRG><ALT><ENTER> gedrückt werden.

Das beiliegende Excel Add-in, mit den beiden Funktionen getCustomers und getOrderItems zeigt die Arbeitsweise von Funktionen, die diese Anforderungen erfüllen (siehe Kasten Installation).

Beschreibung der Funktionen
Die Funktion getCustomers greift auf die Datenbanktabelle Customers zu und liefert alle Datensätze. Mit den Parametern fieldlist können die anzuzeigenden Spalten ausgewählt werden, der Parameter SortingCriteria dient der Eingabe von einem oder mehreren Sortierkriterien inklusive Sortierreihenfolge ASC bzw. DESC und der Parameter Header bestimmt, ob die Daten mit oder ohne Spaltenüberschriften ausgegeben werden. Alle Parameter sind optional, sodass bei Eingabe von =getCustomers() alle Datensätze der Tabelle mit allen Feldern ohne Sortierung und mit Spaltenüberschriften angezeigt werden.
Die Funktion getOrderItems greift auf den View OrderItems zu, der eine Liste von bestellten Artikel mit Preisen und Orderstatus ausgibt. Diese Funktion erlaubt neben der Auswahl der anzuzeigenden Spalten auch die Eingrenzung der Daten auf den in den Parametern startdate bzw. enddate angegebenen Datumsbereich. Auch bei dieser Funktion sind alle Parameter optional, sodass man bei Eingabe von =getOrderItems() alle Zeilen des zugrundeliegenden Views erhält.
Die Eingabe der Funktionen und ihrer Parameter kann in gewohnter Weise entweder direkt durch Eintippen des Funktionsnamens und der Parameter oder durch Verwendung des Excel Funktion einfügen-Dialogs erfolgen. Nach Abschluss der Eingabe füllen die Funktionen exakt den benötigten Zellbereich mit den Rückgabedaten aus (Abb. 3). Für den Anwender präsentieren sich die Daten wie das Ergebnis einer Matrixformel d.h. als ob er den Bereich vor der Eingabe in seiner korrekten Größe ausgewählt und die Funktionseingabe mit <STRG> <ALT><ENTER> abgeschlossen hätte. Eine nachträgliche Änderung der Parameter kann in jeder beliebigen Zelle der Ergebnistabelle erfolgen, auch wenn diese Änderung zu einer abweichenden Größe der zurückgegebenen Datenmenge führt, muss nun aber wie bei Matrixformeln üblich durch gleichzeitiges Drücken von <STRG><ALT><ENTER> abgeschlossen werden.

Abb. 3: Die Funktionen und die von ihnen eingefügten Tabellen
Dieses Verhalten wird folgendermaßen erreicht:

Während der ersten Ausführung werden der Ort des Aufrufs und die Funktionsparameter gespeichert und der Wert #NV (Wert nicht vorhanden) zurückgegeben.
Excel löst nun automatisch ein Calculate-Event aus. Dieses Event wird im Add-in abgefangen. Da der eigentliche Funktionsaufruf bereits abgeschlossen ist, gelten während der Abarbeitung dieses Ereignisses keinerlei Beschränkungen mehr, d.h. es ist möglich, beliebige Bereiche zu beschreiben. Mithilfe der vorher abgespeicherten Funktionsparameter und der nach der Datenbankabfrage bekannten Größe der Rückgabetabelle, kann der benötigte Bereich selektiert und mit der ursprünglich eingegebenen Formel aufgefüllt werden.
Dadurch erfolgt sofort ein erneuter Aufruf der ursprünglichen Funktion, die nun die Daten in den korrekt dimensionierten Bereich einfügen kann.

Für den Anwender ist dieser doppelte Funktionsaufruf nicht erkennbar. Die Funktion verhält sich aus seiner Sicht wie eine gewöhnliche Excel-Funktion, kann aber tabellarische Daten als Rückgabewert verarbeiten.
Das Beispiel-Add-in wurde mit dem Ziel einer möglichst einfachen Erweiterbarkeit entworfen, sodass es als Basis für die Implementierung eigener Funktionen dienen kann. Im weiteren Verlauf werden die dafür erforderlichen Schritte beschrieben und im Zuge dessen auf die Funktionsweise näher eingegangen. Die Anwendung besteht aus folgenden Komponenten:

Einer Microsoft Excel-Add-in-Datei (excelmvf.xla, excelmvf steht für Excel Multivalue Formula)
und einer in C++ verfassten dynamic link library (dll) excelmvf.dll.

Excelmvf.xla ist schnell erklärt. Es enthält im Wesentlichen nur die Funktionsdeklarationen der von der dll exportierten Funktionen (private deklariert!) sowie die Deklarationen der Funktionen, wie sie in Excel sichtbar sind (public Deklarationen). Der Grund dafür, warum nicht gleich die exportierten Funktionen der dll public deklariert werden, ist im letzten Parameter der dll-Funktionen zu suchen. Hier wird der dll ein Pointer auf die aufrufende Excel-Zelle übermittelt.
Eine wichtige Rolle spielt die Funktion setExcelApp, die automatisch beim Laden des Excel-Add-ins, also in der Regel beim Start von Excel, aufgerufen wird und der dynamic link library einen Pointer auf das Excel-Applikations-Objekt übergibt, der in der dll zum Abfangen der von Excel ausgelösten Ereignisse benötigt wird.
Die dynamic link library excelmvf.dll bildet die Hauptkomponente des Add-ins und ist, nachdem die Funktionsaufrufe durch excelmvf.xla weitergeleitet wurden, für die gesamte weitere Abwicklung verantwortlich.
Zunächst wird in der DllEntryPoint-Funktion die Datenbankverbindung hergestellt. Durch die Verwendung von ADO (ActiveX Data Objects) ist die einfache Austauschbarkeit der verwendeten Datenbankumgebung gewährleistet. In unserem Beispiel wird für die Verbindung zur Access-basierten Datenbank ein ConnectionString aus dem Pfad der dll-Datei und dem Datenbanknamen zusammengesetzt. Soll auf andere Datenbankumgebungen zugegriffen werden, muss lediglich der ConnectionString angepasst werden. Für die Beispielanwendung wurde dies für die Microsoft OLE-DB Bridge für ODBC-Treiber und eine dahinterliegende Oracle-Datenbank erfolgreich durchgespielt. Der ConnectionString sähe dann z.B. folgendermaßen aus:

Provider=MSDASQL.1;Password=mypassword;Persist Security Info=True;User ID=myuser;Data Source=mydatasource

Im dllmain-Modul sind die von Excel aufgerufenen Funktionen getCustomers_dll und getOrderItems_dll implementiert. Da diese Funktionen die Schnittstelle zu Excel darstellen, sind sie mit den Linker-Direktiven extern "C" __export __stdcall deklariert. Wie bereits erwähnt, übergibt Excel neben den vom User eingegebenen Parametern einen Pointer auf das aufrufende Range-Objekt, mit dessen Hilfe auf die Methoden und Eigenschaften dieses Objekts zugegriffen werden kann. Die dazu verwendete Funktion AutoWrap der Klasse TCOMHelper wurde mit geringen Modifikationen für das Error Handling von [3] übernommen.
Die Klasse TExcelEventDispatcher ist für das Abfangen der von Excel nach jedem Funktionsaufruf ausgelösten Calculate-Ereignisse zuständig. Sie ist von der VCL-Klasse TEventDispatcher abgeleitet und benötigt für die Verbindung mit Excel den von der Funktion setExcelApp übergebenen Pointer auf das Excel-Applikations-Objekt sowie den GUID (Global Unique Identifier) des Excel Application Events-Interfaces [4, 5].
In der Calculate-Methode dieser Klasse werden die entsprechenden Excel-Ereignisse verarbeitet. Die Aufgabe der Funktion ist es, den Zellbereich in Excel so zu verändern, dass er die richtige Größe für die Aufnahme der aus der Datenbank geholten Datentabelle hat. Eine direkte Ausgabe der Ergebnisse innerhalb dieser Funktion ist aber nicht erwünscht, da sich dann in den Zellen nur mehr die rohen Daten befinden würden und somit die Verbindung zu den Daten in der Datenbank verloren gehen würde. Genau diese bleibende Verbindung zur Datenquelle und die damit einhergehende automatische Aktualisierung der Ergebnisse bei einer Neuberechnung in Excel ist aber eine wichtige Aufgabe der Funktionen. Statt den Daten wird daher die ursprüngliche Formel in Form einer Matrix-Funktion eingefügt und die Rückgabe der Daten der eigentlichen Funktion überlassen.
Im dadurch ausgelösten zweiten Funktionsaufruf erkennt die Funktion aufgrund des richtig dimensionierten Bereiches in Excel, dass nun die Daten zurückgegeben werden können. Dies geschieht in Form eines Variant-Wertes vom Typ SAFEARRAY, der in der Methode getResultArray der Klasse TDataset zusammengestellt wird.
Die Klassen TDataset und TRecord repräsentieren die Ergebnisse einer SQL-Abfrage bzw. eine Zeile der Ergebnismenge und stellen die Basisklassen dar, von denen für jede zu implementierende Funktion entsprechende Klassen abgeleitet werden müssen. Zur Implementierung der Funktion getCustomerData wurden z.B. die Klassen TCustomerRecord als Repräsentation einer Zeile in der Tabelle Customers bzw. TCustomerDataset als Repräsentation der Tabelle selbst abgeleitet. Die von TRecord abgeleitete Klasse muss als Template-Parameter für die Ableitung der TDataset-Klasse verwendet werden. Dadurch kann bereits die Basisklasse Methoden implementieren, für deren Ausführung Informationen über den entsprechenden Recordset bekannt sein müssen und hält den Aufwand für die Anpassung der abgeleiteten TDataset-Klasse in engen Grenzen.
Eine unabdingbare Voraussetzung zur Abfrage der Daten ist das Vorhandensein einer SQL-Abfrage. Sie muss daher in einem ersten Schritt für jede Ableitung von TDataset durch Einfügen einer statischen Variablen SQL festgelegt werden. Für die TOrderItemDataset-Klasse enthält die Abfrage zwei Parameter und sieht daher folgendermaßen aus:

std::string TOrderItemDataset::SQL="Select * from orderitems where orderdate between :startdate and :enddate";

Die für die Ausführung der SQL-Abfrage zuständige Methode retrieve() ist in der Basisklasse als virtuelle Methode deklariert und muss für jede abgeleitete Klasse implementiert werden. Am Beispiel der Implementierung in der Klasse TOrderItemDataset (Listing 1) wird ersichtlich, welche Anpassungen erforderlich sind, um die Methode an den jeweilig zu verarbeitenden TRecord-Typ anzupassen.

Listing 1

int TOrderItemDataset::retrieve(void)

{

   TADOQuery* query = TSgADOQuery::getInstance();

   query->SQL->Clear();

   query->Parameters->Clear();

   query->SQL->Add(SQL.c_str());

   query->Prepared;

   query->Parameters->ParamByName("startdate")->Value = startDate;

   query->Parameters->ParamByName("enddate")->Value = endDate;



   query->Open();



   query->Sort = verifySortingCriterias(sortCriterias);



   recordContainer.clear();

   int recordcounter=0;

   setTimestamp(); //unvalid values are stored in the container too, so timestamp has to be set independent from success of fetch

   while (!query->Eof){

      TOrderItemRecord orderItemRecord;

      orderItemRecord.orderID =               query->Fields->Fields[0]->AsVariant;

      orderItemRecord.customername =          query->Fields->Fields[1]->AsVariant;

      orderItemRecord.orderdate =             query->Fields->Fields[2]->AsVariant;

      orderItemRecord.status =                query->Fields->Fields[3]->AsVariant;

      orderItemRecord.deliverydate =          query->Fields->Fields[4]->AsVariant;

      orderItemRecord.description =           query->Fields->Fields[5]->AsVariant;

      orderItemRecord.price =                 query->Fields->Fields[6]->AsVariant;

      orderItemRecord.amount =                query->Fields->Fields[7]->AsVariant;

      orderItemRecord.totalprice =            query->Fields->Fields[8]->AsVariant;

      recordContainer.push_back(orderItemRecord);

      recordcounter++;

      query->Next();

   }

   setTimestamp();

   setvalid();

   return recordcounter;

}

Die erste Anpassung ist nur für parametrisierte SQL-Abfragen nötig. Im Fall der TOrderItemDataset-Klasse handelt es sich um zwei Datumswerte, die als private Member-Variablen der Klasse TOrderItemDataset hinzugefügt wurden und beim Konstruktoraufruf befüllt werden. Für jeden dieser Parameter muss ein Eintrag wie in Listing1, Zeile 8-9 eingefügt werden. Für nicht parametrisierte Abfragen entfallen diese Einträge.
Die zweite Anpassung ist innerhalb des while-Loops, in dem die zurückgegebenen Werte in eine Record-Klasse übertragen und dann im recordContainer eingetragen werden, nötig. Dabei handelt es sich nur um Änderungen bzw. Ergänzungen der jeweiligen Feldnamen je nach verwendeter TRecord-Ableitung.
Eine auf den ersten Blick weniger einleuchtende Notwendigkeit ist die erforderliche Implementierung des <-Operators für jede von TDataset abgeleitete Klasse. Er wird benötigt, um das Abspeichern und schnelle Wiederauffinden in STL::set-Containern zu ermöglichen. Ein solcher Container ist für jede von TDataset abgeleitete Klasse in der Klasse TRequestController enthalten und dient ausschließlich dazu, um die Performance der Funktionen dadurch zu verbessern, dass die aus der Datenbank geholten Werte für eine kurze Zeitspanne zwischengespeichert werden. Da ja sowohl in den eigentlichen Funktionen als auch im Calculate-Event die Größe der von der Datenbank erhaltenen Datenmenge ermittelt werden muss (mithilfe der Methode TDataset::getRangeSize), wäre ohne diese Zwischenspeicherung eine zweimalige Datenbankabfrage innerhalb eines sehr kurzen Zeitraumes erforderlich. Der Zeitraum, für den die in den Containern enthaltenen Daten gültig bleiben sollen, kann mittels der statischen TRequestController-Eigenschaft cachingperiod bestimmt werden.
Die von TRecord abgeleiteten Klassen beschreiben eine Datenzeile der Ergebnismenge. Für jedes Attribut der zugrundeliegenden SQL-Abfrage muss eine Variable des entsprechenden Datentyps eingefügt werden. Zudem ist die Definition einer Übersetzungstabelle für die Spaltenbezeichnungen erforderlich. Dies geschieht durch die Befüllung eines Arrays mit TFieldDesciptor-Werten. Jedes Datenbankfeld ist mit einem oder mehreren TFieldDescriptor-Werten, der aus Ordinalnummer, dem Spaltennamen in der Datenbank und einem Spalten-Alias besteht, beschrieben. Der Zugriff auf die Daten erfolgt durch die Ordinalnummer, weshalb auch der []-Operator als rein virtuelle Methode implementiert werden muss. Vom Anwender eingegeben Spaltenname (z.B. im Parameter fieldList) werden in die zugehörige Ordinalnummer übersetzt. Diese Vorgehensweise erlaubt auch die Identifikation von Spalten durch mehrere Bezeichnungen, wie z.B. folgender Auszug aus dem TFieldDescriptor-Array der TCustomerRecord-Klasse zeigt:

{"5","POSTALCODE",5},{"POSTALCODE","POSTALCODE",5},

{"POSTALCODE","POSTALCODE",5},{"ZIP","POSTALCODE",5},

{"POSTLEITZAHL","POSTALCODE",5},{"PLZ","POSTALCODE",5}

Soll das Postleitzahlenfeld in der Ergebnistabelle enthalten sein, kann der Anwender daher zwischen der Eingabe von postalcode, postal code, zip, Postleitzahl oder PLZ wählen. (Groß/Kleinschreibung ist egal).
Zu guter Letzt soll noch kurz auf die bereits erwähnte Klasse TRequestController eingegangen werden. Sie bildet das Bindeglied zwischen den Funktionen und dem dahinterliegenden Klassen, indem sie für jede Funktion eine entsprechende Abfragefunktion definiert (TRequestController::getCustomers und TRequestController::getOrderItems). Vor der Anlage eines entsprechenden TDataSet-Objekts wird der lokale Cache abgefragt, ob das entsprechende Objekt bereits vorhanden ist. Neben der Implementierung des Cache-Speichers enthält die Klasse auch eine statische Variable vom Typ TFuncparams, die für die Zwischenspeicherung der Funktionsparameter verantwortlich ist. Zusammengefasst umfasst eine Änderung/Erweiterung des Add-Ins folgende Schritte:

Anpassung der Datenquelle durch Deklaration des entsprechenden ADO-Connect-Strings.
Beschreibung der Daten mittels Ableitung einer neuen Klasse von TRecord.
Anpassung der Interaktion mit der Datenquelle durch Ableitung einer Klasse von TDataset.
Einfügen eventueller zusätzlicher oder anderer Parameter in die Klasse TFuncparams.
Erweiterung der Klasse TRequestController durch Einfügen einer neuen Methode.
Anpassung der Calculate-Methode in der Klasse TExcelEventDispatcher.
Einfügen der neuen Funktion in der dllmain-Datei (in diesem Projekt ist dies excelmvf.dll)
Einfügen der private-Deklaration dieser Funktion im zugehörigen Excel-Add-in (.xla-Datei) sowie der zugehörigen public-Deklarationen (mit Übergabe IDispatch-Pointers der aktuellen Zelle)

Da nicht alle Details beschrieben werden können, wurde der Vorgang der Erweiterung des Add-ins um eine weitere Funktion zum Zugriff auf Daten der Tabelle Articles durchgespielt und alle erforderlichen Schritte bis ins Detail protokolliert. Die Erweiterung um die Funktion getArticles.doc liegt dem Quellcode bei.

Fazit
Ein auf den hier beschriebenen Mechanismen aufbauendes Add-in ist bereits erfolgreich im Einsatz und wird von den Anwendern sehr positiv aufgenommen. Die Stabilität ist bislang sehr zufriedenstellend und es treten auch keinerlei Wechselwirkungen mit anderen Applikationsteilen oder Add-ins auf.
Wer immer diesen Artikel zum Anlass nimmt, ähnliche Funktionen zu implementieren und dabei z.B. Verbesserungen am Design, eine Portierung auf .NET oder Ähnliches vornimmt, ist herzlich dazu eingeladen, seinen Beitrag unter [6] auch für andere Anwender öffentlich zu machen, wo das Projekt unter der GNU public license registriert ist.

Links & Literatur

	Der Entwickler \| dot.net magazin \| Eclipse Magazin \| Entwickler Magazin \| Java Magazin \| Linux Enterprise \| PHP Magazin \| CREATE OR DIE \| Business Technology