#_____________________________________________________________________________ Excel Konstanten
#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).Value2 = $values[$i-1]
	}
}


#______________________________________________________________________________ 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
##
			   			

#_____________________________________________________________________________ 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()