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 Working with Excel Workbooks
    1.3 Working with Excel WorkSheets
          Example 1: Activate Sheets
    1.4 Beenden von Excel
          Beispiel 1: SaveAs() und Quit()

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

    2.2 Copy Tables 

Diagramme oder Charts
    Beispiel 1: Diagramm erstellen

Daten (ADO.Net und COM)
    Beispiel 1: Exportieren lokaler Gruppen und Benutzerkonten in eine Exceltabelle
    4.1 Tabellen sortieren
         Beispiel 1: Tabelle sortieren

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
    5.3 ExcelMakros aus Powershell aufrufen
          Beispiel 1: Aufruf eines  Makros


 

0 Einleitung

Ein Nachteil der Powershell oder generell von Skriptsprachen gegenüber ausgewachsenen Programmiersprachen ist die eingeschränkte Möglichkeit mit Daten grafisch umzugehen. Man kann zwar auch in der Powershell 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.
 

1 Basisfunktionen von Excel

Technet Magazine: Automatisierung von Excel

1.1 Starten und Beenden der Excel Applikation

Beispiel 1: Starten einer neuen Excelinstanz

Set-StrictMode -Version "2.0"
Clear-Host
 
$Excel = New-Object -ComObject Excel.Application # ComObjekt erstellen
$Excel.Visible = $True

Zugegeben, dieser Codeschnippsel ist noch ein recht magerer Einstieg

Hier wird das Wurzelobjekt ("Excel") erzeugt, von dem alle weiteren Obekte (Sheets, Cells, etc.) abgeleitet werden. 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)


Beispiel 2: Starten und Beenden einer Excel Instanz

Set-StrictMode -Version "2.0"
Clear-Host
 
$ExcelApplication = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApplication.Visible = $True

#......Arbeiten in Excel

 
$ExcelApplication.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null  #Freigabe des ComObjects

Technet: Getting Rid of a COM Object (Once and For All)

Beispiel 3: Beenden aller Excel Prozesse

Function Main{
  Kill-ExcelProcesses
}
 
Function Kill-ExcelProcesses{
   $Processes = @(Get-Process Excel)
   $Processes | ForEach {
     Stop-Process $_.Id
     #$_.Kill()
     #$_.CloseMainWindow()
   }
   Write-Host "$($Processes.Count) ExcelProzesse geschlossen"
}
Main

Zu Beginn eines Skriptes kann es beispielsweise während der Entwicklung sinnvoll sein alle Excel Prozesse zu schließen.
Die Methode "CloseMainWindow()" entspricht dem Mausklick auf das Schließen "X" rechts oben im Excel Fenster bei gedrückter Shift-Taste
MSDN: Process.CloseMainWindow Method

 

1.2 Working with Excel Workbooks

 

Beispiel 1a: Starten von Excel mit einer gespeicherten Arbeitsmappe

Wenn man sich beim Starten der Arbeitsmappe sicher ist, dass diese existiert oder kein Abfangen des Fehlers notwendig ist, genügen für den Start diese wenigen Zeilen.

Set-StrictMode -Version "2.0"
Clear-Host

$Filepath="C:\Powershell\Mappe1.xls"

$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True


$Workbook = $ExcelApp.Workbooks.Open($FilePath)

In der ersten Zeile wird das ComObjekt "$ExcelApp" 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 1b: Starten von Excel mit einer gespeicherten Arbeitsmappe

Soll das Skript nicht mit einem Fehler abbrechen, wenn es beim Öffnen der Mappe zu Problemen kommt, dann könnte man diese Funktion benutzen

Set-StrictMode -Version "2.0"
Clear-Host

Function Main{
   $FilePath = "C:\temp\mappe2.xlsx"
  
   $Return = Open-ExcelFile $FilePath
   If ($Return -eq 1){
      Write-Host "$FilePath wurde erfolgreich geöffnet"
      Write-Host "Wir können weitermachen"
   }Else{
      Write-Host "Error: $FilePath konnte nicht geöffnet werden" -ForegroundColor Red
       Write-Host "Bitte erst den Pfad oder die Excelmappe prüfen" -ForegroundColor Red
   }#If
}# End Function Main

Function Open-ExcelFile{
   Param($FilePath)

   $FileExists = ([System.IO.FileInfo]$FilePath).Exists
   If ($FileExists){
     $ExcelApp = New-Object -ComObject Excel.Application
     $ExcelApp.Visible = $True

     Try{
        $WorkBook = $ExcelApp.Workbooks.Open($FilePath)
        Return 1
     }Catch{
        Write-Host "Die Arbeitsmappe existiert, aber kann nicht geöffnet werden" -ForegroundColor Red
        Return 0
   }#Try/ Catch

  }Else{
     Write-Host "Der Pfad $FilePath existiert nicht" -ForegroundColor Red
  }# If/ Else 
}#End Function Open-ExcelFile

Main

#mögliche Ausgabe

Die Arbeitsmappe existiert, aber kann nicht geöffnet werden
Error: C:\temp\mappe2.xlsx konnte nicht geöffnet werden
Bitte den Pfad oder die Excelmappe prüfen


Beispiel 2: Starten von Excel mit einer neuen Arbeitsmappe

Set-StrictMode -Version "2.0"
Clear-Host

$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True

$Workbook = $ExcelApp.Workbooks.Add() #neue Arbeitsmappe incl. einer Tabelle
$Worksheet = $ExcelApp.Worksheets.Add() #zusätzliche Tabelle


Beispiel 3: Anzeige aller registrierten Com-Objekte

$ComObjects = Dir  REGISTRY::HKEY_CLASSES_ROOT -include PROGID -recurse | Foreach {$_.GetValue("")}
$ComObjects | Where{$_ -like "*Excel*"}

Für diejenigen, die es ganz genau wissen wollen :-)

 

1.3 Working with ExcelSheets


Example 1: Activate a Sheet

Function Main{
  $Filepath = "C:\Users\kaiusr\Google Drive\Excel\VBA\PSTest.xlsx"  
  $SheetName = "Sheet2"
 
  $Excel = New-Object -Comobject Excel.Application 
  $Excel.Visible = $True
 
  $Workbook = $Excel.Workbooks.Open($Filepath)
 
  #function call
  Activate-Sheet $SheetName
}#End Main
 
Function Activate-Sheet{
  Param($SheetName)
  
  #the Sheets object can be used for tablesheets or chartheets
  #the Worksheets or Charts objects are specific for tables or charts
  
  #$SourceSheetObj = $Workbook.Sheets.Item($SheetName)
  #$SourceSheetObj.Activate() 
 
  #$SourceSheetObj = $Workbook.Sheets($SheetName)
  #$SourceSheetObj.Activate() 
 
  $Workbook.Sheets($SheetName).Activate()
 
  #$Workbook.Charts($SheetName).Activate()
  #$Workbook.WorkSheets($SheetName).Activate() 
}#end Activate-Sheet
 
Main

 

Example 2: List all sheets of a workbook

Set-StrictMode -Version "2.0"
Clear-Host
 
Function Main{
  $Filepath = "C:\Users\kaiusr\Google Drive\Excel\VBA\PSTest.xlsx"  
  
  $Excel = New-Object -Comobject Excel.Application 
  $Excel.Visible = $True
 
  $Workbook = $Excel.Workbooks.Open($Filepath)
 
  #function call
  List-SheetNames $Workbook
}#End Main
 
Function List-SheetNames{
  ForEach ($Sheet in $Workbook.Sheets){
    $Sheet.Name
  }
  $Workbook.Sheets | Select Name
}# end List-SheetNames
 
Main
#possible output

Sheet2
Sheet1
 
Name  
----  
Sheet2
Sheet1

 

1.4 Speichern und Beenden von Excel

1.4.1 Der SaveAsDialog

 

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

Set-StrictMode -Version "2.0"
Clear-Host

$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True
$Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
$Worksheet = $ExcelApp.Worksheets.Add()    #zusätzliche Tabelle

###
# Hier kann etwas mit der ExcelMappe getan werden
Start-sleep 2 #Pause von 2 Sekunden
###

$FilePath =  "C:\Temp\mappe5.xlsx"
$ExcelApp.Application.DisplayAlerts = $False
$Workbook.SaveAs($FilePath)
$ExcelApp.Application.DisplayAlerts = $True

$ExcelApp.Quit()
$ExcelProcesses = Get-Process Excel
$ExcelProcesses | ForEach {Stop-Process ($_.Id)}

$Null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp)
  • 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)


Beispiel 2: Ausnutzen der Möglichkeiten von SaveAs
Sowohl in der ExcelGUI, wie auch beim Skripten mit der SaveAs-Methode stehen noch mehr Optionen zur Verfügung

Set-StrictMode -Version "2.0"
Clear-Host

Function Main{
  $FilePath = "C:\Temp\mappe5.xlsx" #Pfad wird nicht geprüft

  $ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
  $ExcelApp.Visible = $True
 
  $WorkBook = Open-ExcelBook $ExcelApp
  ###
  # Hier kann etwas mit der ExcelMappe getan werden
  # Modify ExcelFile
  ###

  SaveAs-File $FilePath $ExcelApp $WorkBook
  Delete-ExcelfromMemory $ExcelApp
}

Function Open-ExcelBook{
  Param($myApp)
  $myWorkbook = $myApp.Workbooks.Add() #neue Arbeitsmappe incl. einer Tabelle
  $myWorksheet = $myApp.Worksheets.Add() #zusätzliche Tabelle
  Return ,$myWorkbook
}#End Function Open-ExcelBook

Function SaveAs-File{
  Param($FilePath, $myApp, $myWorkBook)
 
  $FileFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
  $Password = "Holla123"
  $WriteResPassword = "Yippieh456"
  $ReadOnlyRecommended = $True
  $CreateBackup = $True
  $AccessMode = [Microsoft.Office.Interop.Excel.XlSaveAsAccessMode]::xlNoChange
  $ConflictResolution = [Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges
  $ExcelApp.DisplayAlerts = $False

  $myWorkbook.SaveAs($FilePath,$FileFormat,$Password,$WriteResPassword,$ReadOnlyRecommended, `
    $CreateBackup,$AccessMode,$ConflictResolution)
  $myApp.Application.DisplayAlerts = $True #.Application muss nicht sein
  $myApp.quit()
} #End Function SaveAs-File

Function Delete-ExcelfromMemory{
 Param($myApp)
 $ExcelProcesses = Get-Process Excel
 $ExcelProcesses | Foreach {Stop-Process ($_.id)}

 #Entfernen des ComObjekts aus dem Speicher
 $null= [System.Runtime.Interopservices.Marshal]::ReleaseComObject($myApp)

}#End Delete-ExcelfromMemory

Main


Zu den Bedeutungen der Parameter von SaveAs lest bitte in der MSDN nach: Developer Network: Workbook.SaveAs-Methode

Die übrigen Schritte habe ich in diesem Beispiel in Funktionen gekapselt

 

Beispiel: Die möglichen Werte einer Enumeration per Skript auslesen

[System.Enum]::GetValues([Microsoft.Office.Interop.Excel.XlSaveConflictResolution])
#Ausgabe Excel 2013

xlUserResolution
xlLocalSessionChanges
xlOtherSessionChanges

Developer Network: XlSaveConflictResolution Enumeration
Developer Network: XlSaveAsAccessMode Enumeration
 

2 Tabellen in Excel

2.1 Zellen

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)

 

2.1.1 Zellen beschreiben und formatieren

Im folgenden Skript zeige ich einige Beispiele, Zellen in Excel mit Werten und Formeln zu füllen, sowie das Formatieren von Zellen. 
In meiner Entwicklungsumgebung habe ich dieses Skript in die powershell_ise.exe geladen und auf einem zweiten Bildschirm wird die von dem Skript erzeugte Arbeitsmappe angezeigt. 
Beim ersten Aufruf des Skripts wird bei $excel.close ein ignorierbarer Fehler erscheinen, da noch kein $excel existiert. Die Erklärung steht unten nach dem Skript.
Das Skript ist auf deutschem Excel 2003(WinXP) und 2007(Win7) getestet


Beispiel 1: Zellen beschreiben und formatieren

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
  #Open Excel Workbook    
  $ExcelApp = New-Object -comobject excel.application # ComObjekt erstellen
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
  $Worksheet = $ExcelApp.Worksheets.Item(1)   

  #Function Calls
  Fill-Cells $Worksheet
  Format-Cells $Worksheet
 
  #Excel beenden
  $WhatToDo = Read-Host "Terminate Excel (T), Cancel (C)"  
  If ($WhatToDo -eq "T"){
    $ExcelApp.Quit()
    $ExcelProcesses = Get-Process Excel
    $ExcelProcesses | ForEach {Stop-Process ($_.Id)}
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp) | out-null
  }#If
}#End Function Main

Function Fill-Cells{
   Param($Sheet)
   #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)"
}# End Function-Cells

Function Format-Cells{
  Param($Sheet)

  #Zellen formatieren

  $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
}# End Funtion Format-Cells

Main

Das Ergebnis sollte dann ähnlich wie hier aussehen:

(Die untere Formelansicht kann man mit "STRG+#" ein- und ausschalten)

Function Fill-Cells

$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 ein- und aus geschaltet werden.


Function Format-Cells

$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
 

2.1.2 Zellen mit Skriptergebnissen befüllen

Beispiel 1: Prozesseigenschaften in Excel darstellen

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
  #Open Excel Workbook    
  $ExcelApp = New-Object -comobject excel.application # ComObjekt erstellen$ExcelApp = New-Object -ComObject Excel.Application
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
  $Worksheet = $ExcelApp.Worksheets.Item(1)   

  #Function Calls
  Write-Data2Excel $Worksheet
  SaveAs-File
  Close-Excel
   
}#End Function Main

Function Write-Data2Excel{
Param($Sheet)
  #TableHeader
  $Sheet.Cells.Item(1,2).Value2 = "Name"
  $Sheet.Cells.Item(1,3).Value2="Handles"
  $Sheet.Cells.Item(1,4).Value2="Workingset"
  $Sheet.Cells.Item(1,5).Value2="CPU(s)"
  $Sheet.range($Sheet.Cells.Item(1,2),$Sheet.Cells.Item(1,5)).Font.Bold = $true

  $Zeile = 2
  Get-Process | Sort Name -desc | Select -first 5 | ForEach {
    $Sheet.Cells.Item($Zeile,2).Value2="{0}" -f $_.name
    $Sheet.Cells.Item($Zeile,3).Value2="{0}" -f $_.handlecount
    $Sheet.Cells.Item($Zeile,4).Value2="{0}" -f $_.workingset
    $Sheet.Cells.Item($Zeile,5).Value2="{0:f}" -f $_.cpu
    $Zeile++
  }#end ForEach

  #Bereich erstellen und Zeilenanpassen
  $Range = $sheet.usedRange
  $Range.EntireColumn.AutoFit() | Out-Null  #autom. Anpassen der Spaltenbreiten

}#End Function Write-Data2Excel

Function SaveAs-File{
  # Wollt ihr die erstellte Datei speichern lassen, kopiert euch
  # die SaveAs-Function aus dem Beispiel in Kapitel 1.2.1 hierher
 } #end Function SaveAs-File

Function Close-Excel{
 #Excel beenden
  $WhatToDo = Read-Host "Terminate Excel (T), Cancel (C)"  
  If ($WhatToDo -eq "T"){
    $ExcelApp.Quit()
    $ExcelProcesses = Get-Process Excel
    $ExcelProcesses | ForEach {Stop-Process ($_.Id)} #stops all processes
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp) | out-null
    }#If
 } #End Function Excel

 Main


mögliche Ausgabe


[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | out-null
Entfernt altes Com-Objekt aus dem Speicher

 

2.2 Copy Tables

Sometimes it might be required to move or copy a table from one sheet to another. The process of copying a connected range of cells is going to be demonstrated in the next script. The used commands are nothing special, perhaps besides the "PasteSpecial"-method with its parameters. 
 

All you need is a Excel-file with two sheets ("Sheet1" and "Sheet2") and a little table in Sheet1. Cell "B4" should be one of the cells of the table.

Example 1: Copy a table

#This script copies a table from one sheet to another. 

Set-StrictMode -Version "2.0"
Clear-Host


Function Main{
  Try{

   #Close all probably open excel processes
   $ExcelProcesses = Get-Process Excel
   $ExcelProcesses | Foreach {Stop-Process ($_.id)}
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
  }Catch{}

 

  #parameters
  $Filepath = "C:\Users\kaiusr\Google Drive\Excel\VBA\PSTest.xlsx"  

  $SourceSheetName = "Sheet1"
  $DestinationSheetName = "Sheet2"
  $OneCell_Of_Range = "B4"
   
  #function call
  CopyAndPaste $Filepath $SourceSheetName $DestinationSheetName $OneCell_Of_Range
}#End of Main

 

Function CopyAndPaste{
  Param($FilePath,$SourceSheet,$DestinationSheet,$OneCell_Of_Range)

 

  #Open an existing ExcelFile
  $Excel = New-Object -Comobject Excel.Application 
  $Excel.Visible = $True
  $Workbook = $Excel.Workbooks.Open($Filepath)

 

  #Select and Activate the SourceWorksheet
  $SourceSheetObj = $Workbook.Sheets.Item($SourceSheetName)
  $SourceSheetObj.Activate() 

  #Select the whole range of the datatable
  $SourceSheetObj.Range($OneCell_Of_Range).CurrentRegion.Copy() | Out-Null
  
  #Activate the DestinationWorksheet
  $DestinationSheetObj = $Workbook.Sheets.Item($DestinationSheetName)
  $DestinationSheetObj.Activate() 
  $DestinationRange = $DestinationSheetObj.Range($OneCell_Of_Range)

 

  #Definition of the parameters of the PasteSpecial-method  
  $XLPasteType = [Microsoft.Office.Interop.Excel.XlPasteType]::xlPasteAll
  $XlPasteSpecialOperation = [Microsoft.Office.Interop.Excel.XlPasteSpecialOperation]::xlPasteSpecialOperationNone  #no calculation

  #copy process
  #see the msdn-links below the script for more information 

  $DestinationRange.Pastespecial($XLPasteType,$XlPasteSpecialOperation,$False,$False)
  
 #SaveAndClose-WorkBook
}#end of CopyAndPaste


Main

 

Further information regarding the  PasteSpecial method
#see:
   #https://msdn.microsoft.com/en-us/library/office/aa195818%28v=office.11%29.aspx 
   #https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.xmlmappedrange.pastespecial%28v=vs.120%29.aspx 
   #https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlpastetype.aspx 

It is a little bit confusing that "Paste" has actually the meaning of "copy" 

 

3 Diagramme - Charts

 

Beispiel 1: Anzeigen der möglichen Diagrammtypen in Excel
$ExcelApp = New-Object -comobject Excel.Application # ComObjekt erstellen

$xlChartType = "Microsoft.Office.Interop.Excel.xlChartType" -as [type]
[System.Enum]::GetValues( $xlChartType )
#Ausgabe unter Excel 2013

xlConeCol
xlPyramidColClustered
xlPyramidColStacked
xlPyramidColStacked100

Beispiel 2: Diagramm erstellen

Das folgende Skript zeigt, wie ein ExcelChart vielfältig von der Powershell aus ertellt und formatiert werden kann.

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
   #Open Excel Workbook    
   $ExcelApp = New-Object -comobject Excel.application # ComObjekt erstellen$ExcelApp = New-Object -ComObject Excel.Application
   $ExcelApp.Visible = $True
   $Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
   $Worksheet = $ExcelApp.Worksheets.Item(1)   

   #TablePosition in sheet
   $TableStartRow = 3
   $TableStartColumn = 4

   #Function Calls
   Write-Data2Excel $Worksheet
   Show-Chart $Worksheet
   # SaveAs-File $FilePath $ExcelApp $WorkBook  #see chapter 1.2 above
   Close-Excel
 }#End Function Main

Function Write-Data2Excel  {
   Param($Sheet)
     
    #$Sheet.Cells.Item($TableStartRow,$TableStartColumn).Value2="State"
    $Sheet.Cells.Item($TableStartRow,$TableStartColumn+1).Value2="2012"
    $Sheet.Cells.Item($TableStartRow,$TableStartColumn+2).Value2="2013"
    $Sheet.Cells.Item($TableStartRow,$TableStartColumn+3).Value2="2014"
    $Sheet.Range($Sheet.Cells.item($TableStartRow,$TableStartColumn),$Sheet.Cells.item($TableStartRow,$TableStartColumn+3)).Font.Bold = $true
    $Sheet.Range($Sheet.Cells.item($TableStartRow,$TableStartColumn),$Sheet.Cells.item($TableStartRow+3,$TableStartColumn)).Font.Bold = $true

   
    $Statistics = @( @{State = "Bayern";"2012" = 12;"2013" = 3;"2014" = 9},
                             @{State = "Hessen";"2012" = 18;"2013" = 7;"2014" = 10}
                             @{State = "Berlin";"2012" = 13;"2013" = 4;"2014" = 12}
          )

    $i = 1
    $statistics | foreach {
      $Sheet.Cells.Item($TableStartRow+$i,$TableStartColumn).Value2="{0}" -f $($_.State)
      $Sheet.Cells.Item($TableStartRow+$i,$TableStartColumn+1).Value2="{0}" -f $($_.'2012')
      $Sheet.Cells.Item($TableStartRow+$i,$TableStartColumn+2).Value2="{0}" -f $($_.'2013')
      $Sheet.Cells.Item($TableStartRow+$i,$TableStartColumn+3).Value2="{0}" -f $($_.'2014')
      $i++
    }
}#End Function

Function Show-Chart{
    Param($Sheet)
   
    #New Diagram (xlChrtType: Default)
    $Sheet.Cells.item($TableStartRow,$TableStartColumn).select()
    $ExcelApp.Selection.CurrentRegion.Select()
    $WorkBook.Charts.Add() | Out-Null
    $WorkBook.ActiveChart.Location(2,$($Sheet.Name)) | Out-Null #Chart in Tabellenblatt einbinden

   #select your charttype
   $xlChartType = "Microsoft.Office.Interop.Excel.xlChartType" -as [type]
   $Workbook.ActiveChart.ChartType = $xlChartType::xl3DColumnClustered

   #Linienstyle
   #$WorkBook.ActiveChart.seriescollection(1).border.LineStyle = -4118 #(Dashdot)
   #weitere LineStyles unter msdn.microsoft.com/en-us/library/bb241348(v=office.12).aspx
 
    #chartheader
    $WorkBook.ActiveChart.HasTitle = $True #titel erscheint oder nicht
    $WorkBook.ActiveChart.ChartTitle.Text = "my Title"

    #axes
    $WorkBook.ActiveChart.Axes(1).HasTitle = $True
    $WorkBook.ActiveChart.Axes(2).HasTitle = $True
    $WorkBook.ActiveChart.Axes(1).AxisTitle.Text = "Years"
    $WorkBook.ActiveChart.Axes(2).AxisTitle.Text = "Rate in %"

    $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
  
    #plotarea
   $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

 }#end function

Function Close-Excel{
 #Excel beenden
  $WhatToDo = Read-Host "Terminate Excel (T), Cancel (C)"  
  If ($WhatToDo -eq "T"){
    $ExcelApp.Quit()
    $ExcelProcesses = Get-Process Excel
    $ExcelProcesses | ForEach {Stop-Process ($_.Id)}
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp) | out-null
    }#If
} #End Function Excel


 Function SaveAs-File{
    #if needed, you could copy the saveas-function from chapter 1.2
 }
 
Main

 

Das Ergebnis sollte dann ähnlich wie hier aussehen:

weitere Charttypes unter  MSDN: XlChartType Enumeration
 

Erläuterungen zur Formatierung der Plotarea:

$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)"


Beispiel 2: Darstellen verschiedener DiagrammTypen
Das folgende Beispiel ist eine kleine Spielerei. Es zeigt einige oder alle der möglichen Diagrammtypen in Excel an, die unter

Developer Network: XlChartType enumeration (Excel2013)

MSDN: XlChartLocation Enumeration (Excel 2007 )

MSDN: Microsoft Excel Constants siehe XlChartLocation Excel (2003)

aufgelistet sind

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
  #Open Excel Workbook    
     $ExcelApp = New-Object -comobject excel.application # ComObjekt erstellen
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add() 
  $Worksheet = $ExcelApp.Worksheets.Item(1)   

  #Function Calls
  Write-DemoData2Excel $Worksheet
  Show-Charts $WorkSheet
     
}#End Function Main

Function Write-DemoData2Excel {
  Param($Sheet)

  #TableHeader
  $Sheet.Cells.Item(1,2).Value2 = "Name"
  $sheet.Cells.Item(1,3).value2 = "Cpu"
  $Sheet.Range($Sheet.Cells.Item(1,2),$Sheet.Cells.Item(1,3)).Font.Bold = $True

  #Create DemoData
  $Row=2
  Get-Process | where { $PSItem.Cpu -ne $null } | Sort Cpu -desc | Select -first 5 | ForEach {
     $Sheet.Cells.Item($Row,2).Value2 = "{0}" -f $_.Name
     $Sheet.Cells.Item($Row,3).Value2 = "{0}" -f $_.cpu
     $Row++
   }# ForEach
}#End Function Write-DemoData2Excel

Function Show-Charts{
   Param($Sheet)

   #get possible charttypes into an array
   $xlChartType = "Microsoft.Office.Interop.Excel.xlChartType" -as [type]
   $ChartTypes = [System.Enum]::GetValues( $xlChartType )

   $Sheet.Range("B1").Select()
   $ExcelApp.Selection.CurrentRegion.Select()

   #creating charts
   #For($Charttype = 1; $Charttype -lt $ChartTypes.count; $ChartType += 1) {
   For($Charttype = 1; $Charttype -le 5;$ChartType += 1) {
     $Null = $Sheet.Range("B1").Select()
     $Null = $ExcelApp.Selection.CurrentRegion.Select()
     $Null = $Workbook.Charts.Add()
     $Null = $Workbook.ActiveChart.Location(2,$($Sheet.Name))
      Try{
     $Workbook.ActiveChart.ChartType = $xlChartType::$($ChartTypes[$charttype])
     $Workbook.ActiveChart.ChartTitle.Text = $ChartTypes[$charttype]
     }Catch{
       $workbook.ActiveChart.ChartTitle.Text = "$($ChartTypes[$charttype]) nicht darstellbar"
     }#Try/ Catch
   
   #Move Chart Position      }
   $TopPosition = -130
   $LeftPosition = -50
   Foreach ($shape in $($sheet.shapes)) {
     $shape.IncrementTop($TopPosition)
     $shape.IncrementLeft($LeftPosition)
     $TopPosition += 60
     $LeftPosition += 75
    }#Foreach

  }#For

}#End Function Show Charts

Main

Das Ergebnis sollte dann ähnlich wie hier aussehen:

Aus Geschwindigkeitsgründen lasse ich nur die ersten 5 Diagrammtypen anzeigen. Aber tauscht einfach die beiden grünen Zeilen im Skript aus, um alle 73 Diagrammtypen (Excel 2013) zu sehen

 

4 Daten (ADO.Net und Com)

Man kann Daten, wie in diesem Kapitel gezeigt wird,.über die Com-Schnittstelle in eine Exceltabelle eingeben und verarbeiten. Hat man aber größere Datenmengen oder will diese mit Sql-Befehlen analysieren, so ist wahrscheinlich die ADO.Net Schnittstelle besser. Unter dem habe ich eine Reihe von Beispielen für die ADO-Schnittstelle erstellt:

Excel mit ADO.Net

Es kann auch sinnvoll sein, COM und ADO.Net Funktionen und Skripte zu kombinieren

Beispiel 1: Exportieren lokaler Gruppen und Benutzerkonten in eine Exceltabelle
Ruft die beiden folgenden Skripte einfach mal nacheinander auf. Passt bitte nur die Rot/ Gelb gefärbten Zeilen so an, dass sie in beiden Skripten consistent sind. 

"Beispiel 1a" erstellt mittels COM eine Arbeitsmappe, ein Tabellenblatt und Tabellenüberschriften.
"Beispiel 1b" liest die lokalen Gruppen und die darin enthaltenen Useraccounts aus und schreibt sie mit ADO.Net in das Tabellenblatt.

Wenn ihr die erstellte Datei speichern wollt, könnt ihr einfach die SaveAs-Function aus Kapitel 1 (siehe oben) am Ende des Skripts anhängen und den Aufruf in die Main-Function hier im Skript setzen

Beispiel 1a: Erstellen einer angepassten Arbeitsmappe mit COM
Clear-Host
Set-StrictMode -Version "2.0"

Function Main{
  $FilePath = "C:\Temp\localusers.xlsx"
  $Sheetname = "LocalGroups"
  New-ExcelFile $FilePath $Sheetname
  }

Function New-ExcelFile{
  Param ($FilePath)
  $ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add() #neue Arbeitsmappe incl. einer Tabelle
  $Worksheet = $ExcelApp.Worksheets.Add() #zusätzliche Tabelle
  $Worksheet.Name = $Sheetname

  #TableHeader
  $WorkSheet.Cells.Item(3,3).Value2 = "ComputerName"
  $WorkSheet.Cells.Item(3,4).Value2 = "GroupName"
  $WorkSheet.Cells.Item(3,5).Value2 ="MemberName"
  $WorkSheet.Cells.Item(3,6).Value2="CheckDate"
  $WorkSheet.Cells.Item(3,7).Value2="CheckTime"
  $WorkSheet.Range($WorkSheet.Cells.Item(3,3),$WorkSheet.Cells.Item(3,7)).Font.Bold = $true
  $Workbook.SaveAs($FilePath)
}

Main

Beispiel 1b: Lokale Benutzer mit ADO.Net in die erstellte Tabelle exportieren
Clear-Host
Set-StrictMode -Version "2.0"

Function Main{
  $ComputerNames = @("AcerNB","Computer2") #diese Computer werden abgefragt
  $FilePath = "C:\temp\Localusers.xlsx"
  $SheetName = "LocalGroups"

  ForEach($ComputerName in $ComputerNames){
   #FunctionCall
   New-AdoConnection $FilePath $SheetName $ComputerName
   }
}#End Function Main

Function New-AdoConnection{
   Param($FileName, $SheetName, $Computername)
 
   $Provider = "Provider=Microsoft.ACE.OLEDB.12.0"
   $DataSource = "Data Source ="+$FilePath
   $Extend = "Extended Properties=Excel 12.0"
   $Query = "Insert into [$SheetName$] (ComputerName,GroupName,Membername,CheckDate,CheckTime) Values (?,?,?,?,?)"
     
   $OleDbConnection = New-Object System.Data.OleDb.OleDbConnection("$Provider;$DataSource;$Extend")
   $OleDbCommand = New-Object System.Data.OleDb.OleDbCommand($Query)
   $OleDbCommand.Connection = $OleDbConnection

   $Field1 = $OleDbCommand.parameters.add("Computername","Varchar",50)
   $Field2 = $OleDbCommand.parameters.add("GroupName","Varchar",50)
   $Field3 = $OleDbCommand.parameters.add("MemberName","Varchar",50)
   $Field4 = $OleDbCommand.parameters.add("CheckDate","DateTime")
   $Field5 = $OleDbCommand.parameters.add("CheckTime","SmallDateTime")
   $OleDbConnection.open()

   $AdsiComputer = $ADSIGroup =[ADSI]("WinNT://$ComputerName")
   $Groups = $AdsiComputer.Children | Where-Object {$_.SchemaClassName -eq "Group"}

   $ADSIGroups = @()
   Foreach( $Group in $Groups){
     $ADSIGroups += [ADSI]($Group.Path)
   }

   ForEach ($ADSIGroup in $ADSIGroups){
     $Members = @($ADSIGroup.Invoke("members"))
     ForEach ($Member in $Members){
        $GroupName = $ADSIGroup.Name
        $MemberName = $Member.GetType().InvokeMember("Name", 'GetProperty', $null, $Member, $null)
            
        $Field1.Value = $ComputerName
        $Field2.Value = $GroupName.ToString()
        $Field3.Value = $MemberName
        $Field4.Value = (Get-Date).ToLongTimeString()
        $Field5.Value = (Get-Date).tostring("d")
        $returnValue = $OleDbCommand.ExecuteNonQuery()
      }#$Foreach Member
    }#Foreach ADSIGroup
 }#end Function

Main

Das Ergebnis sollte dann ähnlich wie hier aussehen: (getestet unter Win8 /Excel 2013)
Achtet auch darauf, dass Excel und Betriebssystem unter 64/64 oder 32/32Bit laufen! Sonst seht bitte hier nach.

 

4.1 Tabellen sortieren

Beispiel 1: Tabelle sortieren
Im diesem Beispiel sortiere ich unter Excel 2007 die Zeilen nach der ersten Spalte, berücksichtige den Header und Gross- und Kleinschreibung.


if($excel){
   $excel.Application.DisplayAlerts = $false
   $excel.quit()
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | out-null
}

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

$excel.visible = $true
$filepath="C:\powershell\excel\Daten\Arbeitsmappen\sortdata.xlsx"
$workbook=$excel.Workbooks.open($filepath)
$sheet = $workbook.Worksheets.Item(1)

#Abschnitt B)*****************************************************

$sheet.range("b2").select() | out-null  # optional
$excel.Selection.CurrentRegion.Select() | out-null # optional, da die Tabelle hier zusammenhängt
 
  $key1=$sheet.range("b1")
  $order1=1 #XLSortOrder
 
  $key2=$null
  $type=$null
  $order2=2  # egal ob 1 oder 2, aber nichts anderes
 
  $key3=$null
  $order3=1  # egal ob 1 oder 2, aber nichts anderes
 
  $Header=1 # Die Bedeutung findet man im erten MSDN-Link unter dem Skript
  $OrderCustom = 1 # dieser Parameter hat sich mir nicht erschlossen
  $MatchCase=$True #Gross- Kleinschreibung berücksichtigen

  $excel.selection.sort($key1,$order1,$sheet.$key2,$type,$order2,$key3,$order3,$Header,$OrderCustom,$MatchCase)  | out-null
  #Die Bedeutung der Parameter findet man im zweiten MSDN-Link unter dem Skript

 

# XlYesNoGuess Enumeration: msdn.microsoft.com/en-us/library/bb216447(office.12).aspx
# Sort Method Excel 2007: msdn.microsoft.com/en-us/library/bb238871(office.12).aspx

 

5 Sonstiges

5.1 Farben / Colors

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

In Powershell und .Net lassen sich mit der Klasse [Windows.Media.Colors] die enthaltenen Farben auslesen und darstellen (unabhängig von Excel oder Office)

Beispiel 1: Auflisten aller Farben mit Hex- und ARGP-Werten

Clear-Host
Set-StrictMode -Version "2.0"

$Colors = @()
$ColorNames = [Windows.Media.Colors] | Get-Member -static -Type Property | Select -Expand Name

ForEach ($ColorName in $ColorNames){
   $A = ([windows.media.colors]::$ColorName).A
   $R = ([windows.media.colors]::$ColorName).R
   $G = ([windows.media.colors]::$ColorName).G
   $B = ([windows.media.colors]::$ColorName).B
   
   $Color = New-Object -TypeName Psobject -Property @{
      Hex = "$([windows.media.colors]::$ColorName)"
      ARGB = $("$A,$R,$G,$B")
      ColorName = $ColorName
   }
   $Color  
}
#Ausgabe gekürzt

...
SteelBlue                    #FF4682B4             255,70,130,180
Tan                          #FFD2B48C             255,210,180,140   
Teal                         #FF008080             255,0,128,128
Thistle                      #FFD8BFD8             255,216,191,216 
...

In der Praxis googelt einfach nach "RGP Farbtabelle" oder seht in der MSDN nach

MSDN: Colors Class

 

5.1.1 Der Colorindex

Im vorigen Skript wurde die Eigenschaft .Interior.ColorIndex benutzt. Dieser ColorIndex kann gültige Werte zwischen 1 und 56 annehmen, wobei jede Zahl eine andere Farbe repräsentiert. Bis Excel 2003 lassen sich auch nicht mehr Farben auf einem Arbeitsblatt darstellen

 

Beispiel 1: Die 56 Farben des Colorindexes

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
  #Open Excel Workbook    
  $ExcelApp = New-Object -comobject Excel.Application
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
  $Worksheet = $ExcelApp.Worksheets.Item(1)   

  #Function Calls
  Write-Colors2Excel $Worksheet
   
}#End Function Main

Function Write-Colors2Excel{
  Param($Sheet)
  #Farben des Colorindexes
  For($Index = 1;$Index -lt 29; $Index++) {
   $Sheet.Cells.Item($Index,1).Value2="Farbe $Index"
   $Sheet.Cells.Item($Index,2).Interior.ColorIndex = $Index

   $Sheet.Cells.Item($Index,4).Value2="Farbe $($Index + 14)"
   $Sheet.Cells.Item($Index,5).Interior.ColorIndex = $Index + 14

   $Sheet.Cells.Item($Index,7).Value2="Farbe $($Index+28)"
   $Sheet.Cells.Item($Index,8).Interior.ColorIndex = $Index + 28
   }
}#End Function Write-Data2Excel

 Main


Ausgabe:

5.1.2 Farbdarstellung über RGB

Ein RGB (Red, Green, Blue) ist ein 4-Byte Wert, der üblicherweise im Hexadezimal Format 0x00RRGGBB angegeben wird. RRGGBB sind die Bytes für Red, Green, Blue (00 bis FF)

0x000000FF oder 255 bedeutet Rot
0x0000FF00 oder 65280 bedeutet Grün
0x00FF0000 oder 16711680 bedeutet Blau

Bis Excel 2003 kann Excel nur die 56 Farben des Colorindexes darstellen. Verarbeiten kann Excel Hex-Werte. Ab 2007 können mit Hex-Werten mehr als die 56 Farben dargestellt werden 

 

Beispiel 1: RGB-Farbpalette

Set-StrictMode -Version "2.0"
Clear-Host

Function Main {
  #Open Excel Workbook    
  $ExcelApp = New-Object -comobject Excel.Application
  $ExcelApp.Visible = $True
  $Workbook = $ExcelApp.Workbooks.Add()       #neue Arbeitsmappe incl. einer Tabelle
  $Worksheet = $ExcelApp.Worksheets.Item(1)   

  #Function Calls
  Write-Colors2Excel $Worksheet
   
}#End Function Main

Function Write-Colors2Excel{
  Param($Sheet)
  $Red = 0x000000FF
  $Green = 0x0000FF00
  $Blue = 0x00FF0000

  $Sheet.Cells.Item(1,1).Value2 = $Red
  $Sheet.Cells.Item(1,2).Value2 = "=DEZINHEX($Red,8)" #in Excel2003 das Add-In "Analyse Funktionen" installieren
  $Sheet.Cells.Item(1,3).Interior.Color = $Red

  $Sheet.Cells.Item(3,1).Value2 = $Green
  $Sheet.Cells.Item(3,2).Value2 = "=DEZINHEX($Green,8)"
  $Sheet.Cells.Item(3,3).Interior.Color = $Green

  $Sheet.Cells.Item(5,1).Value2 = $Blue
  $Sheet.Cells.Item(5,2).Value2 = "=DEZINHEX($Blue,8)"
  $Sheet.Cells.Item(5,3).Interior.Color = $Blue
}#End Function Write-Data2Excel

Main

Die Hex-Werte zu den Farben könnt ihr im Kapitel 5.1 weiter oben erfahren

Ausgabe:

 

5.2 Makroeditor und Objektkatalog

Natürlich geht der erste Weg zur Lösung einer Aufgabe meistens über die Suchmaschine meines Vertrauens. Da dieser Weg manchmal, aber bei weitem nicht immer zum Ziel führt, will ich hier die Möglichkeiten zeigen, die Excel und die anderen OfficeApplikationen nativ mitbringen.

 

5.2.1 Excel Entwicklertools anzeigen

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

 

5.2.1.1 Makroeditor

Der Makroeditor ist ideal, wenn man manuelle Schritte in Excel automatisieren möchte. Bei gestartetem Makroeditor werden alle Schritte, die man in Excel manuell ausführt, als VBA Code aufgezeichnet. Am Ende sollte man das Schließen des Makroeditors nicht vergessen. Danach findet man seinen Code durch Drücken der Schaltfläche Makros und Auswahl seines Makros zum Bearbeiten.
Der Makroeditor zeigt alle notwendige Schritte in Excel-VBA, die nun "nur" noch nach Powershell konvertiert werden müssen. So wird zum Beispiel aus ActiveWorkbook.Saveas Filename:="....." unter Powershell $workbook.SaveAs("..."). Diese Umsetzung ist oft mehr oder weniger knifflig.

 

5.2.1.2 Objektkatalog

Der Objektkatalog wird in der Excel-Entwicklungsumgebung (erreichbar über Alt +  F11 oder die oben beschriebenen Entwicklertools) mit F2 gestartet. Der Objektkatalog eignet sich besonders gut, um eine bekannte Klasse oder die Instanz einer Klasse auf dessen Methoden und Eigenschaften zu untersuchen.

Das folgende Bild zeigt die Eigenschaften "visible" und "workbooks" des Excel.Application Objekts. Geht man in der linken Hälfte des Objektkatalogs nach unten, so findet man auch noch eine Methode "workbooks". Durch einen Klick auf das Fragezeichen oben in der Symbolleiste erhält man weitere Informationen und meist ein Beispiel in Excel-VBA.
Die Kunst besteht nun weiter, diese VBA-Beispiele in Powershellsyntax zu konvertieren. Die richtigen Methoden und Eigenschaften zu kennen, ist aber oft mehr als die halbe Miete.

 

5.2.1.3 MSDN

Wie immer ist die MSDN eine sehr gute Informationsquelle:

 MSDN: Excel Object Model Reference 

(zu Excel 2007)

 Microsoft Office Excel 2003 Visual Basic Reference

 

5.3 ExcelMakros aus Powershell aufrufen

Technet: Scripting Guy! How Do I Run an Office Excel Macro on Multiple Workbooks?

Beispiel 1: Aufruf eines  Makros

$Excel = New-Object -Comobject Excel.Application 
$Excel.Visible = $true

$Filepath = "C:\temp\Powershell\mappe1.xlsm"
$Workbook = $Excel.Workbooks.Open($Filepath)

$Excel.Run("Mappe1.xlsm!Macro1")

#getestet unter Win7 und Office 2010

#getestet unter Win10 und Office 2013
#"Sub Macro1" muss in einem Module gespeichert sein

Das Makro1 muss in einer makroaktivierten (*.xlsm) Arbeitsmappe existieren.

Die Kombination aus Powershellskripten mit internen Excelmakros eröffnet riesige Möglichkeiten Daten einfach und fehlerfrei aufzubereiten.

 
 
 
 
 
Anhänge:
Diese Datei herunterladen (MMR_KeyReader.zip.txt)MMR_KeyReader.zip.txt[ ]9 kB