behandelte Themen

     0. Einleitung

1. Basisfunktionen von Excel
   1.1 Starten von Excel
         Beispiel 1: Starten von Excel mit einer existierenden Arbeitsmappe
         Beispiel 2: Starten von Excel mit einer neuen Arbeitsmappe
         Beispiel 3: Anzeige aller registrierten Com-Objekte
   1.2 Beenden von Excel
         Beispiel 1: SaveAs() und Quit()
   
   
2. Tabellen in Excel
   2.1 Zellen
        2.1.1 Zellen beschreiben und formatieren
                 Beispiel 1: Zellen beschreiben und formatieren
        2.1.2 Zellen mit Skriptergebnisse beschreiben
                 Beispiel 1: Prozesseigenschaften in Excel darstellen
   
   
3. Diagramme oder Charts
   Beispiel 1: Diagramm erstellen
   
   
4. Daten
   4.1 Tabellen sortieren
        Beispiel 1: Tabelle sortieren
   
   
5. Sonstiges
   5.1 Farben / Colors
         5.1.1 Farbauswahl über den ColorIndex
                  Beispiel 1: Farben des Colorindexes auflisten
         5.1.2 Farbdarstellung über RGB
   5.2 Erforschen von Excelklassen und Objekten
         5.2.1 Excel Entwicklertool anzeigen
                  5.2.1.1 Makroeditor
                  5.2.1.2 Objektkatalog
                  5.2.1.3 MSDN

******************************************************************************************************

Ein Nachteil der Powershell oder generell von Skriptsprachen gegenüber ausgewachsenen Programmiersprachen ist die eingeschränkte Möglichkeit mit Daten grafisch umzugehen. Man kann die Klasse Windows.Forms verwenden, bestes Beispiel ist der WMI-Explorer von thepowershellguy.com, komfortabel zum Programmieren ist das allerdings nicht.

Bei der Eingabe verzichten Administratoren gerne auf Masken mit Eingabefeldern zugunsten der Commandline mit einfachen Textdateien, während bei der Ausgabe eine grafische Darstellung von Daten zur Analyse manchmal durchaus hilfreich ist. Dafür bietet sich eine Aufbereitung solcher von Powershell erzeugter Daten mit Microsoft Excel an.

Excel lässt sich weitgehend von außen über die Powershell steuern, so dass man für die Eingabe der Daten, die Formatierung von Zellen, das Sortieren von Tabellen, die Erstellung von Diagrammen und vieles mehr innerhalb von Excel selbst keine Aktionen durchführen braucht. Das verhindert möglicherweise den einen oder anderen Bedienungsfehler.

Beispiel 1: Starten von Excel mit einer existierenden Arbeitsmappe

$excel = new-object -comobject excel.application # ComObjekt erstellen
$excel.visible = $true

$filepath="C:\Powershell\mappe1.xls"
$workbook=$excel.Workbooks.open($filepath)

Beispiel 2: Starten von Excel mit einer neuen Arbeitsmappe

$excel = new-object -comobject excel.application # ComObjekt erstellen
$excel.visible = $true

$workbook=$excel.Workbooks.add()
$worksheet=$excel.worksheets.add()

In jeweils der ersten Zeile der beiden Beispiele wird das ComObjekt "$excel" erstellt, mit dem wir alle weiteren Funktionen ausführen werden.
Die visible-Eigenschaft gibt an, ob die Ausführung des Skripts sichtbar oder unsichtbar ablaufen soll.

Beispiel 3: Anzeige aller registrierten Com-Objekte

$a = dir  REGISTRY::HKEY_CLASSES_ROOT -include PROGID -recurse | foreach {$_.GetValue("")}
$a|Where{$_ -like "*Excel*"}

Beispiel 1: Speichern mit SaveAs() und Beenden mit Quit()

$excel = new-object -comobject excel.application # ComObjekt erstellen
$excel.visible = $true
$workbook=$excel.Workbooks.add()
$worksheet=$excel.worksheets.add()

start-sleep 2 #Pause von 2 Sekunden

$filepath="C:\Powershell\mappe2.xls" #Pfad muss vorhanden sein
$workbook.SaveAs($filepath)
$excel.Application.DisplayAlerts = $False #.Application muss nicht sein
$excel.quit()
$ExcelProcess=get-process excel
$ExcelProcess | foreach {stop-process ($_.id)}
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) #Entfernen des ComObjekts aus dem Speicher

- Der erste Teil des Skripts öffnet Excel wie unter 1.1 schon beschrieben.

- die Methode SaveAs speichert die Arbeitsmappe auf den angegebenen Pfad

- Die Eigenschaft DisplayAlerts steuert, ob Hinweise, wie nach dem Speichern einer Arbeitsmappe, angezeigt werden sollen.

- Die quit()-Methode beendet das Programm

- $ExcelProcess | foreach {stop-process ($_.id)} stoppt alle Excelprozesse

- [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel): Ohne Aufruf dieses Befehls bleibt nach Beenden von Excel das ComObjekt $excel im Arbeitsspeicher.

 Technet: Windows PowerShell Tip of the Week Getting Rid of a COM Object (Once and For All)

Die spannende Frage ist nun, wie man die eingesetzten Methoden .add(), saveAs(), quit() oder die Eigenschaft .displayalerts() erhält. Zwei mögliche Antworten gibt der nächste Abschnitt

Um auf Zellen in Excel zugreifen zu können, muss man den Excelobjektbaum von der Excelapplikation bis zur Zelle durchlaufen

Application (=Com-Objekt) -> Workbook (=Arbeitsmappe) -> Worksheet (=Arbeitsblatt) -> Cells (Zellen) oder Range (Bereich)

Erläuterungen zu Beispiel 1

Abschnitt A)

#Abschnitt A)*****************************************************

if($excel){
   $excel.Application.DisplayAlerts = $false
   $excel.quit()
   $ExcelProcess=get-process excel
   $ExcelProcess | foreach {stop-process ($_.id)}

   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | out-null
}

$excel = new-object -comobject excel.application # ComObjekt erstellen

$excel.visible = $true
$workbook=$excel.Workbooks.add()
$sheet = $workbook.Worksheets.Item(1)

if($excel){
Dieser Abschnitt schliesst automatisch ein vorhandenes Excel-Objekt $excel. Beim ersten Aufruf dieses Skripts wird dieser Befehl umgangen, da $excel noch nicht exisitert und die if-Abfrage $false liefert. Dadurch bekommt man nicht nach jedem Testlauft eine zusätzliche, offene Excelmappe auf den Desktop.

$excel.Application.DisplayAlerts = $false
Verhindert, dass beim Beenden von Excel nachgefragt wird, ob die Datei gespeichert werden soll

$excel.quit()
Schliessen des Com-Objektes

$ExcelProcess=get-process excel
$ExcelProcess | foreach {stop-process ($_.id)}
Beendet alle Excel Processe

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | out-null
Entfernt ein altes Com-Objekt aus dem Speicher. (siehe unter 1.2 Beenden von Excel)

$excel = new-object -comobject excel.application
Hier wird das Wurzelobjekt erzeugt, von dem wir alle weiteren Obekte ableiten können. Sollte hier ein Fehler auftauchen "Altes Format oder ungültige Typbibliothek" dann liegt das an Schwierigkeiten zwischen der Betriebssystem- und der Officesprache (siehe cultureinfo-Object)

$excel.visible = $true
wir wollen schliesslich etwas sehen

$workbook=$excel.Workbooks.add()
Hinzufügen einer neuen Arbeitsmappe

$sheet = $workbook.Worksheets.Item(1)
Das erste Tabellenblatt wird als Objekt $sheet angelegt. Statt Item(1) funktioniert auch die Angabe des Tabellennamens: $sheet = $workbook.Worksheets.Item("Tabelle1")

Möchte man mit einer bestehenden Arbeitsmappe arbeiten, so kann man die beiden letzten Zeilen in Abschnitt A) ersetzen durch
$filepath="C:\Powershell\PPraxis\Arbeitsmappen\mappe1.xls"
$workbook=$excel.Workbooks.open($filepath)
$sheet = $workbook.Worksheets.Item(1)

wobei dann die übrigen Abschnitte keinen Sinn mehr ergeben.

Abschnitt B)

#Zellen mit Werten füllen

$sheet.Cells.Item(1,2).value2="Hallo Powershell" 
$sheet.Range("B3").value2=$sheet.Range("B1").text

#Zellen mit Formeln füllen
$sheet.Range("D2:F2").FormulaR1C1="3"
$sheet.Range("D3:F3").FormulaR1C1="4"
$sheet.Range("D4:F4").FormulaR1C1="5"

$sheet.Range("D5").Formula = "=Summe(D2:D4)"
$sheet.Cells.Item(5,5).Value2 = "=Mittelwert(`$E`$2:E4)"
$sheet.Range("F5").FormulaR1C1 = "=Produkt(Z2S6:Z4S6)"

$sheet.Cells.Item(1,2).value2="Hallo Powershell"
Warum die Eigenschaft Value2 lautet, kann ich nicht sagen. Führt man an dieser Stelle ein "$sheet.Cells.Item(1,2) | get-member aus, so taucht in den Properties nur Value2 und kein Value auf. Die Fähigkeiten von get-member zum Erforschen von Eigenschaften und Methoden sind hier näher beschrieben

$sheet.Range("B3").value2=$sheet.Range("B1").text
Kopieren des Inhalts von B1 nach B3. Die Eigenschaft .text ist readonly
Die Zelle wird mit dem A1-Bezugssystem angesprochen

$sheet.Range("D2:F2").FormulaR1C1="3"
Befüllen des Ranges D2:F2 mit dem Wert 3

$sheet.Cells.Item(5,5).Value2 = "=Mittelwert(`$E`$2:E4)"
Die Zelle E5 wird mit ihrem Spalten- und Zeilenindex (5,5) angesprochen. Diese Schreibweise ist am Besten für die Verwendung von Schleifen geeignet.
Der Inhalt von E5 ist die ExcelFormel Mittelwert, die mit absoluten Zellbezügen $E$2 arbeitet. Der Backtick verhindert, dass Powershell das $-Zeichen als den Beginn einer Variablen ansieht.

$sheet.Range("F5").FormulaR1C1 = "=Produkt(Z2S6:Z4S6)" #Z1S1 - Schreibweise
die Z1S1-Notation ist veraltet. In den ExcelOptionen kann diese Notation generell eingeschaltet werden.

Abschnitt C)

#Abschnitt C)*****************************************************
#Zellen formtiern

$green=10 #Colorindex
$red=0x000000FF #RGB in Hex

$sheet.Range("D2:F2").Numberformat="00,00"
$sheet.Range("D5").Interior.ColorIndex = $green
$sheet.Range("E5").Interior.Color = $red
$sheet.Range("F5").Font.Size = 15
$sheet.Range("F5").Font.Bold = $true
# $sheet.Range("F5").Font.Fontstyle = "Fett" #alternativ zur .Bold-Property
$sheet.Range("F5").Font.Strikethrough = $true
$sheet.Range("F5").Orientation=45

#$range = $sheet.usedRange
#$range.EntireColumn.AutoFit() | out-null #autom. Anpassen der Spaltenbreiten

Hier wird gezeigt, wie man Zellen formatieren kann. Die Eigenschaften habe ich aus einem aufgezeichneten Makro erhalten

$green=10 #Colorindex
$red=0x000000FF #RGB in Hex
Auf die Farben gehe ich in Kapitel 5.1 genauer ein

$sheet.Range("D2:F2").Numberformat="00,00"
"00,00" bedeutet 2 Stellen vor und 2 Stellen nach dem Komma
Auf die Formatierungsmöglichkeiten gehe ich im Kapitel Strings hierauf näher ein.  Natürlich kann man den Makroeditor einschalten, die Zelle wie gewünscht formatieren und sich das Formatergebnis im Makrocode ansehen.

#$range = $sheet.usedRange
#$range.EntireColumn.AutoFit() | out-null #autom. Anpassen der Spaltenbreiten
Damit kann man die Spaltenbreiten des benutzten Bereichs auf dem Tabellenblatt anpassen

Erläuterungen zu Beispiel 1

#Abschnitt C)*****************************************************
#Chart erstellen und formatieren

#BasisDiagrmm erstellen
   $sheet.range("b1").select()
   $excel.Selection.CurrentRegion.Select()

   $workbook.charts.add() | out-null
   $workbook.ActiveChart.Location(2,$($sheet.name)) | out-null #Chart in Tabellenblatt einbinden

$sheet.range("b1").select()
$range=$excel.Selection.CurrentRegion.Select()
die beiden Zeilen haben denselben Effekt, wie ein "STRG+*". Die gesamte Tabelle um "B1" herum, wird damit ausgewählt.

$workbook.charts.add() | out-null
Ein neues Diagramm wird der Arbeitsmappe hinzugefügt, zunächst als eigenes Blatt

$workbook.ActiveChart.Location(2,$($sheet.name)) | out-null #Chart in Tabellenblatt einbinden
# Alternativ: $workbook.ActiveChart.Location(2,"Tabelle1") | out-null
Mit diesem Befehl und dem Wert "2" der XlChartLocation wird das Diagramm als Objekt in die Exceltabelle eingebettet.

die Auflistung der Enumerations für XLChartLocation finden sich hier

#Excel 2007  MSDN: XlChartLocation Enumeration
#Excel 2003  MSDN: Microsoft Excel Constants siehe XlChartLocation

#Abschnitt D)*****************************************************
#ChartType
   $workbook.ActiveChart.chartType = 65 #xlLineMarkers
 
#Linienstyle
   $workbook.ActiveChart.seriescollection(1).border.LineStyle = -4118 #(Dashdot)  

#ChartType
     $workbook.ActiveChart.chartType = 65

Die 65 steht für xlLineMarkers oder "Line with Markers"

weitere Charttypes unter  MSDN: XlChartType Enumeration

Alternativ kann man auch mit Enumerations arbeiten, wie in diesem Beispiel der ScriptingGuys
 Technet Magazine: Automatisierung von Excel

$xlChartType = "microsoft.office.interop.excel.xlChartType" -as [type]
$workbook.ActiveChart.chartType = $xlChartType::xlLineMarkers

Bei mir funktionieren die Enumerations nur unter Excel 2003

# Linienstyle
   $workbook.ActiveChart.seriescollection(1).border.LineStyle = -4118 #(Dashdot)
#weitere LineStyles unter msdn.microsoft.com/en-us/library/bb241348(v=office.12).aspx

Abschnitt E)*****************************************************
#Beschriftungen
  #Überschrift
    $workbook.ActiveChart.hasTitle=$true #titel erscheint oder nicht
    $workbook.ActiveChart.ChartTitle.Text = "die Überschrift"

  #Achsenbeschriften
    $workbook.ActiveChart.Axes(1).HasTitle = $True
    $workbook.ActiveChart.Axes(2).HasTitle = $True

    $workbook.ActiveChart.Axes(1).AxisTitle.Text = "die x-Achse"
    $workbook.ActiveChart.Axes(2).AxisTitle.Text = "die y-Achse"

    $workbook.ActiveChart.Axes(1).AxisTitle.font.ColorIndex=46 #siehe Abschnitt Farben
    $workbook.ActiveChart.Axes(1).AxisTitle.Font.Bold=$true
    $workbook.ActiveChart.Axes(1).AxisTitle.Font.Size=15

#Abschnitt F)*****************************************************
#Zeichnungsfläche formatieren  
    $workbook.ActiveChart.PlotArea.Fill.TwoColorGradient(1,4) #zweifarbiger Fülleffekt
    $workbook.ActiveChart.PlotArea.Fill.ForeColor.SchemeColor=45
    $workbook.ActiveChart.PlotArea.Fill.BackColor.SchemeColor=8

 $workbook.ActiveChart.PlotArea.Fill.TwoColorGradient(1,4) #mit zweifarbigem Fülleffekt
 $workbook.ActiveChart.PlotArea.Fill.ForeColor.SchemeColor=45
 $workbook.ActiveChart.PlotArea.Fill.BackColor.SchemeColor=8

Die Plotarea wird mit mit einem Fülleffekt aus hellblau (Schemecolor=8) und orange (Schemecolor=45) gebildet. Die gewünschte Art des Fülleffektes (Schattierungsart, Variante) erzeugt man sich am besten wieder über den Makroeditor, der beispielsweise diesen VBA-Code zurückliefert

Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=4
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 45
        .Fill.BackColor.SchemeColor = 8

Unter  MSDN: MsoGradientStyle Enumeration erfahren wir, dass ein HorizontalerGradient den Wert 1 besitzt. Aus diesen Informationen und ein bischen Erfahrung lässt sich der PowershellCode oben entwickeln.

Alternativ der Code für einen einfarbigen Fülleffekt:
    $workbook.ActiveChart.PlotArea.Interior.ColorIndex=28 #einfarbiger Hintergrund
    $workbook.ActiveChart.PlotArea.Fill.OneColorGradient(7,1,0.3) #mit einfarbigem Fülleffekt
    $workbook.ActiveChart.PlotArea.Fill.ForeColor.SchemeColor=46
 
Im Vergleich zum TwoColorGradient hat der OneColorGradient einen weiteren Paramater. Der dritte Parameter gibt die Stärke des Gradienten an.
 MSDN: OneColorGradient Method
"Degree  Required Single. The gradient degree. Can be a value from 0.0 (dark) through 1.0 (light)"

Anmerkung: Die Farbverläufe unterscheiden sich etwas zwischen Excel 2003 und Excel 2007.

Farben können in Excel entweder mit dem Colorindex oder als RGB-Wert ausgewählt werden

Ohne eine kleine Anpassung zeigt Excel seine Entwicklertools, nämlich den Makroeditor und seine VBA-Umgebung nicht an. In der Schaltfläche "Office Optionen" muss man in den ExcelOptionen die Entwcklerregisterkarte aktivieren, wie im den folgenden Bildern gezeigt wird

Wie immer ist die MSDN eine sehr gute Informationsquelle:

 MSDN: Excel Object Model Reference

(zu Excel 2007)

 Microsoft Office Excel 2003 Visual Basic Reference