Oft stellt sich die Anforderung, Excel-Dateien komfortabel aus Datenbanken befüllen zu können. Als Alternative zur Excel-Destination im SSIS-Paket wurde hierfür die C#-Klasse ExcelButler.cs „entwickelt. Sie enthält Routinen zum Erstellen, Auslesen, Beschreiben und Abspeichern von Excel-Dateien. Sie kann an beliebigen Stellen im Excel-Sheet Werte einfügen, Werte auslesen, Zellen formatieren, Zellen gruppieren und so weiter und bietet damit eine Fülle von Möglichkeiten für das Handling von Excel-Dateien.
In Abbildung 1 ist das Klassendiagramm des ExcelButlers dargestellt. Unter „Methods” sind die implementierten Methoden aufgelistet. Hier könnte man mit der Methode OpenWorkbook() ein bestehenden Excel-Dokument öffnen, mit der Methode InsertCellContent() beschrieben und mit der Methode SaveAndClose() wieder abspeichern.
Abbildung 1: Ansicht des Klassendiakgramms des ExcelButlers
Doch bevor das überhaupt möglich ist sind einige vorbereitende Schritte zu tun. Wenn man vor hat ein Excel-Dokument innherhalb eines SSS-Paketes mit dem ExcelButler dort zu befüllen, müsste eine C#-Konsolenapplikation im Visual Studio erstellt werden. Zu Beginn ist bei den Verweisen der Verweis zur Microsoft.Office.Interop.Excel .NET-Komponente hinzuzufügen. In Abbildung 2 ist das dargestellt. Anschliessend muss die Komponente mit der using-Direktive im Code eingebunden werden:
using Excel = Microsoft.Office.Interop.Excel;
Abbildung 2: Hinzufügen der .NET-Komponente
Nun kann sofort die ExcelButler-Klasse instanziiert werden:
ExcelButler butlerinstanz = new ExcelButler();
Nach Schaffung des C#-Objektes butlerinstanz muss mit ihrer Hilfe nun eine Excel-Instanz erzeugt werden:
butlerinstanz.CreateExcelInstance(true, out error);
Nach Ausführen des Befehls CreateExcelInstance mit dem ersten Parameter Visible = true öffnet sich sofort Excel, was der Entwickler am Bildschirm beobachten kann. Wenn nach Entwicklungsabschluss später die Excel-Dateien automatisiert innherhalb eines SSIS-Paketes erzeugt werden sollen, muss der Visible-Parameter auf false gesetzt werden, um eine fehlerlose Excel-Generierung im Hintergund zu bewerkstelligen.
Nun kann mit der Methode OpenWorkbook ein bestehendes Dokument geöffent und in die Excel-Instanz geladen werden. In Abbildung 3 ist diese Routine beispielhaft dargestellt.
Abbildung 3: Ansicht eines Code-Ausschnittes für die Routine OpenWorkbook()
Nun ist es möglich mit den zur Verfügung stehenden Routinen (zum Beispiel InsertCellContent) die Excel-Datei beliebig zu befüllen:
butlerinstanz.AddWorkbook(out error);
butlerinstanz.AddWorksheet(„testblatt“, out error);
butlerinstanz.InsertCellContent(1, 1, „testwert“, out error, „testblatt“);
butlerinstanz.InsertCellContent(1, 2, „testwert“, out error, „testblatt“);
Nun könnte eine Datenbankverbindung geöffnet warden eine Datenbankabfrage gemacht werden, die Ergebnisse dieser Abfrage in ein DataSet geschrieben werden und anschliessen das DataSet iteriert werden und mit InsertCellContent an beliebige Zellen im Excel-Dokument Werte geschrieben werden. Nach Beschreiben der Excel-Datei ist das Dokument mit der Routine SaveAndClose() abzuspeichern und zu schliessen.
Abbildung 4: Einbindung des ExcelButlers in ein SSIS-Paket
Schlussendlich kann man die komplette Erstellung der Excel-Datei nun in ein SSIS-Paket einbinden. Hierzu sind zu Beginn die im Excel-Dokument anzuzeigenden Daten bereitzustellen und anschliessend in einem Prozess-Ausführ-Task die Konsolenapplikation, bei Bedarf mit Parameterübergabe, auszuführen (siehe Abbildung 4).
Zusammenfassend sind also folgende Schritte zu tun:
1. in einem herkömmlichen SSIS die im Excel anzuzeigenden Daten bereitstellen (für vereinfachten späteren Lesezugriff idealerweise in einer extra für dieses Zweck geschaffenen Persistierungstabelle)
2. C#-Konsolenapplikations-Projekt im Visual-Studio erstellen, .NET-Komponente einbinden, ExcelButler-Instanz erzeugen und Excel mit Visible = false öffnen
3. Innerhalb der Konsolenapplikation Datenbankverbindung öffen und die in Punkt 1 beschriebene Persistierungstabelle abfragen und iterieren und in jedem Iterationsschritt Excel mit InsertCellContent() befüllen
4. Excel mit SaveAnClose() speichern und schliessen
5. Die C#-Main-Rountine mit dem Process-Exit-Code = 0 verlassen
Als Hinweis sei an dieser Stelle noch gesagt, dass empfindliche Code-Stelllen in der Konsolenapplikation, wie beispielsweise das Abfragen von Datenbanken, in Try-Catch-Blöcke gekapselt werden sollten und die Exceptions kontrolliert aufgefangen werden sollten. Bei eventuell auftretenden Fehlern sind dann enstprechend sinnvolle Exit-Codes zu vergeben (zum Beipiel: -3, Datenbankserver nicht verfügbar, oder: -4, keine Zugriffsberechtigung auf das Verzeichnis XY zum Abespeichern der Excel-Datei). Diese Codes sollte die Main-Rountine dann zurückliefern (Beispiel: return -7) und das SSIS-Paket wird diese Codes auswerten und so Fehler erkennen.
Das wars, so einfach und komfortabel kann das sein.