 |
HowTo: |
Excel-Dokumente erzeugen per Powershell |
Wenn man in einem Powershell Script ein Excel-Sheet erzeugen will, so ist das relativ einfach.
Man verwendet das COM-Objekt von Excel namens Excel.Application. Die Fernsteuerung
per COM-Objekt nennt sich dann auch Excel Automation. Selbstverständlich klappt das nur, wenn
Excel auf dem Rechner installiert ist, auf dem das Sxript dann später läuft.
Innerhalb des Skriptes kann man dann alle Objekte, Collections, Methoden und Properties benutzen,
so als ob man innerhalb von Excel VBA programmieren würde. Der Clou: Man kann mit dem Makro-Recorder
Aktionen aufzeichnen und den erzeugten Code dann in einem VBScript verwenden.
Allerdings muß man im VBScript noch extra das Objekt angeben, desssen Methode man aufruft.
In Excel-VBA kann man z.B. folgende Zeile direkt aufrufen:
Range("A1").Font.Colorindex = 3
In einem Powershell Script hingegen müßte man zuerst das Objekt der Excel-Applikation erzeugen, dann ein Excel-Dokument (Workbook), dann ein Excel-Sheet (Tabellenblatt). Danach kann man die Methoden dieses Excel-Sheet-Objektes verwenden - in einer Syntax, die VBA stark ähnelt - nur eben nicht allein für sich, sondern ausgehend von dem Objekt.
$xlo = new-object -comobject excel.application
$xlw = $xlo.Workbooks.Add()
$xls = $xlo.Worksheets.Item(1)
$xls.Range("A1").Font.Colorindex = 3
Anmerkung: Im Gegensatz zu anderen Scriptsprachen muss bei der Verwendung der Excel-Objekte
in Powershell oft noch ein "Item"- und "Value"-Specifier dazwischengeschoben
werden. Wenn man unter VBScript z.B. sagen könnte ...Cells(1,1) = "Hallo", so
müßte es unter Powershell heißen ...Cells.Item(1,1).Value = "Hallo".
Anmerkung: Beim Umgang mit Excel muss man sich daran gewöhnen, dass alle Abzählungen
nicht bei 0 beginnen, sondern bei 1. Dies getrifft z.B. die Auswahl der Workbooks, der Worksheets,
aber auch die der Zeilen und Spalten!
Eine gute Beschreibung aller Objekte und Methoden findet man im hier:
MSDN Excel2007 Developer Reference
Das folgende Skript demonstriert einen kleinen Ausschnittl der Möglichkeiten, die einem die Excel-Automatisierung bietet:
#Enum XlAutoFilterOperator
$xlAnd = 1
$xlOr = 2
$xlTop10Items = 3
$xlBottom10Items = 4
$xlTop10Percent = 5
$xlBottom10Percent = 6
#Enum XlSortOrder
$xlAscending = 1
$xlDescending = 2
#Enum XlSortOrientation
$xlSortColumns = 1
$xlSortRows = 2
$xlSortNormal = 0
#Enum XlSortDataOption
$xlSortTextAsNumbers = 1
#Enum MsoScaleFrom
$msoScaleFromTopLeft = 0
#______________________________________________________________________________ Function xlsFill
#Füllt eine Zeile in einem Excel-Sheet mit Werten, die als String-Array übergeben werden
#vars xls: excel worksheet
#col: column number
#values: array of strings
#
function xlsFill($xls, $col, $values) {
For ($i=1; $i -le $values.Length; $i++) {
$xls.Cells.Item($col, $i).Value = $values[$i-1].ToString()
}
}
#______________________________________________________________________________ Function xlsRow
#Gibt zu einer gegebenen Spaltennummer ide Excelbezeichnung in Buchstaben zurück, z.B. "AE"
#Diese Angabe benötigt man bei Verwendung der Range-Eigenschaft
#
function xlRow([int] $i) {
If ($i -lt 27) {
[char]($i + 64)
} Else {
[char]([int](($i-1) / 26) + 64) + [char](((($i-1) % 26) + 1) + 64)
}
}
##
## MAIN SCRIPT
##
#_____________________________________________________________________________ Excel starten
$xlo = New-Object -comobject Excel.Application
$xlo.Visible = $true
#_____________________________________________________________________________ Umgang mit Excel Sheets
$xlw = $xlo.Workbooks.Add()
$xlo.Worksheets.Item(1).Name = "Summary"
$xlw.Worksheets.Item(2).Name = "Sheet1"
$xlw.Worksheets.Item(3).Delete()
#_____________________________________________________________________________ Arbeit in einem Sheet
$xls = $xlo.Worksheets.Item(1)
$xls.Cells.Item(5,1).Value2 = "TestTest"
xlsFill $xls 1 ("Dies","ist","die","Kopfzeile","Bla","Blubb","Dum","Dee","Dum")
xlsFill $xls 2 (1,2,3,4,5,6,7,8,9)
xlsFill $xls 3 (2,4,6,8,10,12,13,14,15)
#______________________________________________________________________________ AutoFill-Beispiel
$xls.Range("A2:I3").AutoFill($xls.Range("A2:I100")) >$null
#______________________________________________________________________________ Kommentar einfügen
$xls.Cells.Item(2,2).AddComment() > $null
$xls.Cells.Item(2,2).Comment.Text("Hallo" + [char]10 + "Dies ist ein Kommentar") > $null
$xls.Cells.Item(2,2).Comment.Shape.ScaleWidth(3, $false, $msoScaleFromTopLeft)
$xls.Cells.Item(2,2).Comment.Shape.ScaleHeight(3, $false, $msoScaleFromTopLeft)
$xls.Cells.Item(2,2).Comment.Shape.TextFrame.Characters(0, $xls.Cells.Item(2,2).Comment.Text.Length).font.name = "courier"
$xls.Cells.Item(2,2).Comment.Shape.TextFrame.Characters(0, $xls.Cells.Item(2,2).Comment.Text.Length).font.size = 12
#______________________________________________________________________________ Fenster Fixierung
$xls.Activate > $null
$xlo.ActiveWindow.SplitColumn = 2
$xlo.ActiveWindow.SplitRow = 1
$xlo.ActiveWindow.Split = $true
$xlo.ActiveWindow.FreezePanes = $true
#______________________________________________________________________________ AutFilter
#Alle Elemente > 100
$xls.Range("E:E").AutoFilter(1, ">100") >$null
#Die untersten 10 Elemente
$xls.Range("E:E").AutoFilter(1, "10", $xlBottom10Items) >$null
#______________________________________________________________________________ Sortierung
$xls.Range("A1:I100").Sort($xls.Range("B2"), $xlDescending) >$null
#______________________________________________________________________________ Speichern / Doc Schließen
$xlFileName = "test.xls"
$xlFilePath = [string]$pwd
If ($xlFilePath.SubString($xlFilePath.Length - 1, 1) -ne "\") {$xlFilePath += "\" }
$xlFilePath = $xlFilePath + $xlFileName
$xlw.SaveAs($xlFilePath)
$xlw.Close()
#______________________________________________________________________________ Bestehendes Doc Öffnen
$xlo.Workbooks.Open($xlFilePath) >$null
$xlw = $xlo.Workbooks.Item($xlFileName)
#______________________________________________________________________________ Sheet hinzufügen/plazieren
$xlw.Sheets.Add() >$null
$xls = $xlo.Worksheets.Item(1)
$xls.Name = "Sheet 2"
#Der "Before" - Parameter kann nicht genutzt werden,
#deswegen wird das Sheet mit einem Trick nach hinten geschoben
$xls.Move($xlo.Worksheets.Item($xlo.Worksheets.Count))
$xlo.Worksheets.Item($xlo.Worksheets.Count).Move($xls)
$xls.Select() >$null
$xls = $xlo.Worksheets.Item("Summary")
$xls.Select() >$null
#______________________________________________________________________________ Doc Sichern und schließen
$xlw.Save() >$null
$xlo.Quit()
Erhalten Sie unter Excel 2007 beim Aufruf der Methode ...Workbooks.Add() den Fehler Error: 0x80028018 (-2147647512) / Description: Old Format or Invalid Type Library erhalten, so liegt ein Mismatch zwischen der Ländereinstellung des Office-Paketes und Ihres Betriebssystems vor. Lesen Sie mehr dazu im Microsoft-KnowledgeBase-Artikel 320369.
 |
Download von ExceOutput.ps1 |