Hey, Scripting Guy!Automatisierung von Excel

Die Scripting Guys von Microsoft

Dieser Scripting Guy, der in den letzten Wochen in Australien lebte und arbeitete, ist immer wieder überrascht von der Kreativität und Widerstandsfähigkeit seiner australischen Kollegen. Vor ein paar Tagen wurden ich und meine Frau von einem Freund zum Abendessen ausgeführt. Der Freund meinte, das Restaurant sei einen kurzen Fußweg von unserem Hotel entfernt.

Vierzig Minuten später erreichten wir ein wunderschönes Restaurant mit Blick auf den Hafen von Sydney. Das Opernhaus lag vor uns glitzernd wie eine Muschel, angeschwemmt an einem farbenprächtigen Strand. Die Harbour Bridge, die weltweit breiteste Brücke mit langer Spannweite, hält die beiden Halbinseln zusammen wie eine riesige Klammer, die zwei verformte Bretter bändigt.

Hier zu Hause gilt ein Fußweg von 40 Minuten nicht als kurzer Spaziergang zum Abendessen. Eher als tägliches Fitnesstraining, dem 15 Minuten Aufwärm- und Dehnungsübungen vorausgehen.

Ein Grund, aus dem dieser Scripting Guy die Reise nach Australien so genossen hat, ist die andere Sichtweise der Kollegen hier. Ihnen ist eine ganz besondere Perspektive auf die Dinge eigen, von der meine vorgefassten Ansichten zum richtigen Umgang mit bestimmten Problemen oft in Frage gestellt werden.

Beim Skripting kann man sehr leicht in einen Trott geraten, wenn man versucht, die Sache mit erprobten Methoden durchzuziehen. Oftmals geht es genau darum, mit dem gleichen Ansatz die gleichen Ergebnisse zu erzielen.

Nehmen wir z. B. das Thema Überwachung. Mithilfe von Windows PowerShell erhalten problemlos einen hübschen Snapshot der Prozessauslastung auf dem lokalen Computer. Wenn Sie das Cmdlet „Get-Process“ verwenden, ist das Ergebnis eine ordentliche Ausgabe wie in Abbildung 1.

Abbildung 1 Ansicht lokaler Prozesse mithilfe von Get-Process

Die Ergebnisse des Cmdlets „Get-Process“ sind in vielerlei Hinsicht nützlich. Sie zeigen die Anzahl offener Handles, einige verschiedene Ansichten der Arbeitsspeichernutzung sowie einen Snapshot der CPU-Auslastung. Wenn Windows PowerShell 2.0 ins Spiel kommt, können Sie Get-Process sogar mit einem -computername-Parameter verwenden, um diese Übersicht von einem Remotecomputer abzurufen. Warum würde sich angesichts all dieser Vorteile jemand die Mühe machen, weitere Untersuchungen anzustellen?

Das Problem besteht darin, dass sich in den langen Spalten mit Daten jede Menge Details verbergen. Alle diese Daten neigen dazu, die wichtigeren Details zu verdecken. Zwar ist es wunderbar, dass der -computername-Parameter vom künftigen Windows Power­Shell 2.0 unterstützt wird, aber im Moment nützt das einem Netzwerkadministrator recht wenig. Daher müssen wir auf die Windows-Verwaltungsinstrumentation (Windows Management Instrumentation, WMI) und die WMI-Klasse „Win32_Process“ zurückgreifen, um Remotesysteme zu überwachen und die Informationen in einer Art und Weise zu präsentieren, die wirklich nützlich ist. Wenn Sie denken, die Ausgabe von Get-Process war üppig, werfen Sie einen Blick auf die Ausgabe von Win32_Process (siehe Abbildung 2).

Abbildung 2 Verwenden von WMI zum Anzeigen von Prozessen

Was raten wir also einem armen alten Netzwerkadministrator, der nichts weiter will als einen einfach zu lesenden Bericht über die Menge des genutzten Speichers? Es ist an der Zeit, über den Tellerrand hinauszusehen, die Fesseln abzustreifen und über die Automatisierung mit Excel nachzudenken. Höchstwahrscheinlich ist Microsoft Office Excel auf Ihrem Computer installiert. Vielleicht haben Sie damit kaum Erfahrung, aber da Excel Teil von Microsoft Office System ist, können Sie es auch verwenden.

Wie schwierig ist es, Excel zu automatisieren? Eigentlich ziemlich einfach, da Microsoft ein Automatisierungsmodell speziell für die Arbeit mit Excel erstellt hat. Die Programm-ID ist „Excel.Application“, und es handelt sich um ein COM-Objekt. Wenn Sie eine Instanz des Objekts „Excel.Application“ erstellen, wird Excel standardmäßig gestartet und ausgeführt, ist aber nicht sichtbar. Mithilfe der visible-Eigenschaft können Sie Excel jedoch sichtbar machen.

So erstellen Sie das Excel.Application-Objekt, fragen den Status der visible-Eigenschaft ab und legen dann für die visible-Eigenschaft den Wert „true“ fest:

PS C:\> $excel = New-Object -ComObject Excel.Application PS C:\> $excel.Visible False PS C:\> $excel.Visible = $true

Anschließend werden Sie mit einer ziemlich ungewöhnlichen Ansicht von Excel konfrontiert, die einfach wie eine Shell der Excel-Anwendung aussieht (siehe Abbildung 3). Es gibt weder Arbeitsmappen noch Tabellen, nur das reine Excel.

Abbildung 3 Reines Excel ohne Arbeitsmappen oder Tabellen

Der Anwendung muss eine Arbeitsmappe hinzugefügt werden. Hierzu wird die add-Methode aus dem Arbeitsmappenobjekt verwendet. Der Zugriff auf das Arbeitsmappenobjekt erfolgt über das Hauptobjekt „Excel.Application“. Wie Sie sehen, speichern wir das Arbeitsmappenobjekt in einer Variablen namens „$workbook“:

$workbook = $excel.Workbooks.add()

Nun müssen wir eine Verbindung zu einer bestimmten Tabelle herstellen. Wenn Excel eine Arbeitsmappe hinzugefügt wird, werden der Arbeitsmappe standardmäßig drei Tabellen hinzugefügt. Diese Tabellen können mit Nummern versehen werden. In dieser Codezeile stellen wir eine Verbindung zur ersten Tabelle her und speichern das zurückgegebene Tabellenobjekt in einer Variablen namens „$sheet“:

$sheet = $workbook.worksheets.Item(1)

Jetzt können wir Daten in diese Tabelle schreiben. Informationen in Excel-Tabellen werden in Zellen gespeichert. Da sich Zellen in Tabellen befinden, verwenden Sie das Tabellenobjekt, das in der $sheet-Variablen gespeichert ist, um Zugriff auf eine bestimmte Zelle zu erhalten. Hierzu verwenden Sie Zahlen, die sich auf Zeilen und Spalten in der Tabelle beziehen. Ein wenig verwirrend ist, dass Zeilen in Excel-Tabellen Zahlen zugeordnet sind, während Spalten mit Buchstaben versehen werden. Bei Verwendung des Automatisierungsmodells werden sowohl Zeilen als auch Spalten Zahlen zugewiesen. Die erste Zahl bezeichnet die Zeile und die zweite Zahl die Spalte. Sie können in die Zelle schreiben, indem Sie dieser bestimmten Zelle einfach einen Wert zuweisen:

$sheet.cells.item(1,1) = "Test"

Nach dem Hinzufügen einer Arbeitsmappe zum Excel.Application-Objekt und der Daten zu einer Zelle in der Tabelle sieht die Excel-Arbeitsmappe so aus wie in Abbildung 4.

Abbildung 4 Hinzufügen eines Werts zu einer Zelle

Nutzen wir nun alle diese Erkenntnisse, um etwas Sinnvolles zu schaffen. Die Aufgabe besteht darin, eine Auflistung von Prozessinformationen aus WMI abzurufen, den Namen und den Speicherverbrauch jedes Prozesses in eine Excel-Tabelle zu schreiben und dann ein Diagramm zu erstellen, in dem der Speicherverbrauch veranschaulicht wird. Genau diese Aufgabe erfüllt WriteProcessInformation­ToExcel.ps1. Das vollständige Skript finden Sie auf der TechNet Magazin-Website.

Zu Beginn des Skripts rufen wir mithilfe des Cmdlets „Get-WmiObject“ eine Auflistung von Informationen zu Prozessen ab. Wir verwenden die WMI-Klasse „Win32_Process“, um diese Informationen abzurufen, und speichern sie in der $processes-Variablen:

$processes = Get-WmiObject -class Win32_Process

Nun erstellen wir eine Instanz des Excel.Application-Objekts und speichern sie in der $excel-Variablen. Dann machen wir die Anwendung sichtbar und fügen eine Arbeitsmappe hinzu. Diese Schritte müssen normalerweise für jede gewünschte Excel-Automatisierung ausgeführt werden. Hier sind die Codezeilen:

$excel = new-object -comobject excel.application $excel.visible = $true $workbook = $excel.workbooks.add()

Eines der Ärgernisse bei Excel besteht darin, dass von einer Arbeitsmappe immer drei Tabellen erstellt werden. Wir halten dies für Verschwendung, weil wir kaum eine einzige Tabelle nutzen, von dreien ganz zu schweigen. Dank der Automatisierung können wir die zusätzlichen Tabellen glücklicherweise einfach löschen. Hierzu stellen wir über die Arbeitsblattauflistung eine Verbindung zur dritten Tabelle her und rufen die delete-Methode auf. Auf dieselbe Weise kann die zweite Tabelle gelöscht werden:

$workbook.workSheets.item(3).delete() $workbook.WorkSheets.item(2).delete()

Als Nächstes benennen wir die übrig gebliebene Tabelle um. Das ist wichtig, denn wenn Sie irgendwann beschließen, die Excel-Tabelle mithilfe von ActiveX Data Objects (ADO) abzufragen, verwenden Sie den Tabellennamen in der Verbindungszeichenfolge. Daher sollten Sie der Tabelle einen logischen Namen geben, um den Code lesbar und intuitiv zu machen. Zum Umbenennen der Tabelle weisen Sie einfach der name-Eigenschaft der konkreten Tabelle einen neuen Wert zu. In diesem Fall benennen wir die erste Tabelle in „Processes“ um:

$workbook.WorkSheets.item(1).Name = "Processes"

Nun müssen wir eine Verbindung zu der umbenannten Tabelle herstellen. Verwenden Sie die Item-Methode aus dem Arbeitsblattobjekt, und geben Sie ihm den Namen der Tabelle:

$sheet = $workbook.WorkSheets.Item("Processes")

Die erste Zeile der Tabelle enthält Headerinformationen. Wir zeichnen einen Rahmen und formatieren die Eigenschaftsnamen fett. Infolgedessen beginnen die Daten in Zeile 2. Also weisen wir der Indikatorvariablen $X den Wert 2 zu:

$x = 2

Als Nächstes werden von vier Zeilen Code vier Enumerationstypen erstellt. Mit Enumerationstypen wird Excel mitgeteilt, welche Werte für bestimmte Arten von Optionen zulässig sind. Die xlLineStyle-Enumeration wird beispielsweise verwendet, um die Art der zu zeichnenden Linie zu bestimmen: doppelt, gestrichelt und so weiter. Diese Enumerationswerte sind auf MSDN dokumentiert.

Um den Code einfacher lesbar zu machen, erstellen wir Verknüpfungsaliase für jeden der vier Enumerationstypen, die wir verwenden. Im Grunde wandeln wir eine Zeichenfolge, die den Namen der Enumeration repräsentiert, in ein [type] um. Dieses Verfahren ist tatsächlich ziemlich raffiniert:

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type] $colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type] $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type] $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Jetzt müssen wir die erste Zeile formatieren. Wir formatieren die Schriftart fett, definieren die Linie als „xlDashDot“, ermöglichen das automatische Festlegen der Farbe und stellen für die Rahmenstärke einen mittleren Wert ein:

For($b = 1 ; $b -le 2 ; $b++) { $sheet.cells.item(1,$b).font.bold = $true $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium }

Anschließend weisen wir der ersten Zeile Werte zu, indem wir die Zelle mit der item-Methode auswählen und die Koordinaten der Zeile und Spalte angeben. Als Nächstes folgt eine direkte Wertzuweisung zum Schreiben der Spaltenüberschriften:

$sheet.cells.item(1,1) = "Name of Process" $sheet.cells.item(1,2) = "Working Set Size"

Jetzt müssen wir die Prozessinformationen, die in der $processes-Variablen gespeichert sind, die im Ergebnis unserer WMI-Abfrage erstellt wurde, in den entsprechenden Zellen platzieren. Mithilfe der foreach-Anweisung durchlaufen wir die Auflistung der Prozessinformationen. Wir definieren die $process-Variable als Enumerator (Platzhalter) für die Auflistung und wählen die name- und workingSetSize-Eigenschaften zum Schreiben in die erste bzw. zweite Spalte aus.

An dieser Stelle kommt die $x-Variable ins Spiel. Wir beginnen mit der zweiten Zeile. Während wir die Auflistung der Prozesse durchlaufen, inkrementieren wir den Wert der $x-Variablen, sodass sie immer auf die aktuelle Zeile in der Auflistung verweist. Auf diese Weise sind wir in der Lage, alle Daten, die in der $processes-Auflistung der Prozessinformationen gespeichert sind, durchzugehen:

Foreach($process in $processes) { $sheet.cells.item($x, 1) = $process.name $sheet.cells.item($x,2) = $process.workingSetSize $x++ } #end foreach

Nach dem Auffüllen der Excel-Tabelle müssen wir die Größe der Spalten anpassen, damit die Zellen die gleiche Größe haben wie die Daten, die darin gespeichert sind. Hierzu könnten wir einen Bereich erstellen, indem wir die zu verwendenden Spaltenkoordinaten angeben. Wir können jedoch auch einfach die usedRange-Eigenschaft aus der Tabelle verwenden. Nach dem Erstellen des Bereichsobjekts wählen wir die EntireColumn-Eigenschaft aus und verwenden die AutoFit-Methode, um die Größe der Spalten zu ändern. Weil von dieser Methode immer Daten zurückgegeben werden, leiten wir die Ergebnisse zum Cmdlet „Out-Null“ weiter. Damit verhindern wir, dass sich in der Konsole nutzlose Informationen anhäufen. Folgender Code wird verwendet:

$range = $sheet.usedRange $range.EntireColumn.AutoFit() | out-null

An diesem Punkt könnten wir die Arbeit beenden: Wir haben eine ordentliche Tabelle mit dem Namen und dem Speicherarbeitssatz aller Prozesse. Wir wollen jedoch einen Schritt weitergehen und ein Diagramm erstellen. Das ist einfach. Wir verwenden die add-Methode des charts-Objekts aus der Arbeitsmappe. Weil diese Methode auch Informationen zurückgibt, die wir nicht sehen möchten, leiten wir die Ergebnisse zum Cmdlet „Out-Null“ weiter, wie im Folgenden gezeigt:

$workbook.charts.add() | out-null

Durch den obigen Befehl wird ein Liniendiagramm hinzugefügt. Wenn Sie ein Diagramm anderer Art definieren möchten, müssen Sie einen der Typenumerationswerte des Diagramms verwenden. Hierzu können Sie einen der Enumerationswerte aus microsoft.office.interop.excel.xlChartType verwenden, z. B. den Typ „xl3DPieExploded“. Der Typ „xl3DPieExploded“ erstellt ein dreidimensionales explodiertes Kreisdiagramm. Wir weisen der chartType-Eigenschaft des ActiveChart-Objekts diesen Enumerationstyp zu. Dann weisen wir die Datenquelle für das Diagramm so zu, dass sie den Bereich umfasst, den wir in der $range-Variablen definiert haben. Im Ergebnis wird kurz das Liniendiagramm angezeigt, woraufhin das 3D-Kreisdiagramm auf dem Bildschirm explodiert. Hier ist der Code:

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded $workbook.ActiveChart.SetSourceData($range)

Da wir schon einmal am Ausprobieren sind, wollen wir das Kreisdiagramm drehen. Hierzu können wir die rotation-Eigenschaft des ActiveChart-Objekts verwenden. Wir verwenden eine for-Anweisung zum Inkrementieren um den Wert 15 bis zum Wert 360. 360 Grad beschreiben einen Kreis. Das Diagramm wird in Schritten von 15 Grad ein Mal im Kreis gedreht. Das sieht eigentlich ziemlich beeindruckend aus. Hier ist der Code, mit dem Sie diese Aufgabe vollbringen:

For($i = 1 ; $i -le 360 ; $i +=15) { $workbook.ActiveChart.rotation = $i }

Als Letztes müssen wir die Tabelle speichern. Hierzu verwenden wir das Cmdlet „Test-Path“, um zu sehen, ob die Tabelle bereits existiert. In diesem Fall löschen wir die alte Tabelle mithilfe des Cmdlets „Remove-Item“ und speichern dann die aktuelle Arbeitsmappe an dem Ort, der in der $strPath-Variablen gespeichert ist. Wir verwenden das ActiveWorkbook-Objekt aus dem Excel.Application-Objekt sowie die SaveAs-Methode, um die Arbeitsmappe zu speichern. Wenn nicht bereits eine Kopie der Tabelle gespeichert ist, verwenden wir die SaveAs-Methode aus dem ActiveWorkbook-Objekt zum direkten Speichern:

IF(Test-Path $strPath) { Remove-Item $strPath $Excel.ActiveWorkbook.SaveAs($strPath) } ELSE { $Excel.ActiveWorkbook.SaveAs($strPath) }

Wenn das Skript ausgeführt wird, sehen Sie ein Diagramm wie das in Abbildung 5.

Abbildung 5 Explodiertes Kreisdiagramm „Processes“

Die Tabelle selbst befindet sich auf der Registerkarte „Processes“. Abbildung 6 zeigt die Spaltenheader, den Punkt-/Strich-Linienstil, den wir für den Rahmen ausgewählt haben, und die fett formatierten Spaltenüberschriften. Die beiden Datenspalten, die angezeigt werden, enthalten die Prozessnamen und die Arbeitssatzgrößen-Eigenschaften.

Abbildung 6 Die fertige Tabelle

Das Excel.Application-Automatisierungsmodell bietet Ihnen die Tools, die Sie benötigen, um Prozessdaten der Server so zu verarbeiten, dass Sie die Analyse- und Diagrammerstellungstools dieser funktionsreichen und leistungsfähigen Anwendung nutzen können.

Ed Wilson ist leitender Berater bei Microsoft und ein bekannter Skriptexperte. Er ist ein Microsoft-zertifizierter Schulungsleiter, der für Microsoft Premier-Kunden weltweit einen beliebten Workshop zu Windows PowerShell anbietet. Ed Wilson hat acht Bücher verfasst, einschließlich mehrerer Titel zum Thema Windows-Skripterstellung, und hat an fast einem Dutzend weiterer Bücher mitgearbeitet. Er besitzt mehr als 20 Branchenzertifizierungen.

Craig Liebendorfer ist Schriftsteller und langjähriger Microsoft-Webredakteur. Er kann immer noch nicht glauben, dass es einen Job gibt, bei dem er dafür bezahlt wird, täglich mit Wörtern zu arbeiten. Respektloser Humor steht bei Craig Liebendorfer ganz oben auf der Liste, und daher ist er hier gut aufgehoben. Als größte Leistung in seinem Leben betrachtet er seine wunderbare Tochter.