Aug 09, 2017

16104

Excel mit Powershell formatieren

Hallo,

ist es möglich eine Excel-Datei mit Powershell zu formatieren?

- Bestimmte Zellen bekommen eine bestimmte Größe
- Bestimmte Zellen bekommen eine bestimmte Schriftart
- Bestimmte Zellen bekommen einen bestimmten Rahmen
- Bestimmte Zellen bekommen eine bestimmte Uhrzeitformatierung und werden zentriert
- Bestimmte Zellen bekommen eine bestimmte Farbe

Wenn ja, wie und gibt es dazu Beispiele?

Please also mark the comments that contributed to the solution of the article

Content-Key: 345827

Url: https://administrator.de/contentid/345827

Printed on: April 26, 2024 at 18:04 o'clock

13 Comments

Latest comment

https://www.google.de/search?q=powershell+excel+format+cell

Und welche Paramter sind für Datumsformat, Uhrzeitformat, Zeilenumbruch usw. ?
Oder z.B. einen Satz am Ende des schon geschriebenen setzen?
Ich finde leider keine Tabelle dazu.

Servus @Tastuser,
zu deinen Fragen hier die Antworten:

# Excel Objekt
$objExcel = New-Object -Com Excel.Application
# Mappe öffnen
$wb = $objExcel.Workbooks.Add()
# Excel anzeigen
$objExcel.Visible = $true
# Excelsheet zuweisen
$ws = $wb.Worksheets.Item(1)
# Inhalt der zelle festlegen
$ws.Range("A1").Value2 = "09.08.2017"  
# Zellgröße festlegen
$ws.Range("A1").RowHeight = 100  
$ws.Range("A1").ColumnWidth = 50  
# Farbe festlegen
$ws.Range("A1").Interior.Color = [System.Drawing.Color]::Red  
# Font festlegen
$ws.Range("A1").Font.Name = "Arial"  
$ws.Range("A1").Font.Size = 20  
$ws.Range("A1").Font.Color = [System.Drawing.Color]::White  
$ws.Range("A1").Font.Bold = $true  
# Rahmen mit  Eigenschaften festlegen
$n = [System.Reflection.Missing]::Value
$ws.Range("A1").BorderAround(1,-4138,$n,[System.Drawing.Color]::Blue)  
# Text zentrieren
$ws.Range("A1").HorizontalAlignment = -4108 # Horizontal  
$ws.Range("A1").VerticalAlignment = -4108 # Vertikal  
# Zelle mit Datumsformat formatieren
$ws.Range("A1").NumberformatLocal = "TT.MM.JJJJ"  

Die nötigen Parameter und Konstanten etc. pp kannst du dir aus der VBA-Doku raus suchen, die sind die gleichen, müssen aber natürlich auf die Syntax von Powershell übersetzt werden! Hilfereich dabei ist immer ein geöffneter VBA-Editor mit aktivem Direktfenster indem du dir die Werte von Konstanten ausgeben lassen kannst und der Makrorekorder wenn du VBA-Anfänger bist.

Office 2013 Developer References

Grüße Uwe

Du wolltest ein Beispiel haben.
Den Rest findest Du über den Object Browser und das Web.
z.B. hier:
https://msdn.microsoft.com/en-us/library/wss56bz7.aspx
http://www.excel-spreadsheet.com/vba/objectbrowser.htm
usw.

Vielen Dank Uwe!

Ich weiß nur nicht wie ich etwas bestimmtest finden soll.
Wie z.B. einen Zeilenumbruch bei bestimmten Zellen oder dass nur beschriebene Zellen einen Rahmen bekommen?

Zitat von @Tastuser:

Vielen Dank Uwe!

Ich weiß nur nicht wie ich etwas bestimmtest finden soll.
Wie z.B. einen Zeilenumbruch bei bestimmten Zellen oder dass nur beschriebene Zellen einen Rahmen bekommen?

Schmeiß im Excel den Makrorekorder an und formatiere eine Zelle, dann stoppst du die Aufzeichnung und schaust dir den generierten Code im Modul an, schon hast du die gewünschten daran beteiligten Funktionen!
Ob eine Zelle Inhalt hat fragst du mit einer IF-Abfrage und der Eigenschaft Value der Zelle ab, oder du machst es gleich mit bedingter Formatierung.

Hier mal ein Beispiel für eine Bedingte Formatierung für den Bereich A1:A10 in dem alle Zellen mit Inhalt formatiert werden:
(Nutze der Abkürzung wegen hier die Objekte von obigem Code, die Definition von $wb muss also dazu!)

$ws = $wb.Worksheets.Item(1) 
# Defintion eines leeren Parameters
$n = [System.Reflection.Missing]::Value
# Bereich A1:A10 als Variable setzen
$rngCells = $ws.Range("A1:A10")  
# Bedingte Formatierung hinzufügen
$rngCells.FormatConditions.Add(2,$n,'=NICHT($A1="")')  
# Füllfarbe festlegen
$rngCells.FormatConditions.Item(1).Interior.Color = [System.Drawing.Color]::Green
# Alle Ränder einfärben
$rngCells.FormatConditions.Item(1).Borders | %{$_.Color = [System.Drawing.Color]::Red}

Du kannst stattdessen natürlich auch mit einer For-Schleife über die Zellen arbeiten wenn du nur einmalig formatieren möchtest:

$ws.Range("A1:A10") | %{  
   if ($_.Value() -ne ""){  
     # hier Zelle formatieren
     $_.Interior.Color =  [System.Drawing.Color]::Green
  }
}

Okay danke
Ich habe mal versucht zu vergleichen.
Zellen zentriert und das kam aus der Aufnahme:

Sub text_zentrieren()
'  
' text_zentrieren Makro  
'  

'  
    Range("D6:E9").Select  
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
End Sub

Nun wollte ich die Werte die du fürs Zentrieren angegeben hast mit den ausgelesenen Werten ersetzen:

$Worksheet.Range("A1").HorizontalAlignment = "xlCenter" # Horizontal   
$Worksheet.Range("A1").VerticalAlignment = "xlBottom" # Vertikal   

Jedoch funktioniert das nicht
Habe ich einen Denkfehler?

Habe ich einen Denkfehler?

Manchmal frage ich warum man meine Antworten nicht ganz durchliest

wenn ich genau das dort reinschreibe...
Ich hatte oben geschrieben das du die Werte der Konstanten wie xlCenter etc. ermitteln musst da diese in Powershell nicht existieren. Deswegen der Hinweis zum Direktfenster im VBA Editor in dem du diese einfach anzeigen lassen kannst mit
debug.print xlCenter

Ergebnis: -4108

xlBottom entsprechend: -4107

Und diesen Wert setzt du stattdessen in Powershell ein.
Diese Werte stehen übrigens auch in der Doku wenn du sie lieber nachschlagen willst:
Constants enumeration

Okay, tut mir leid! Ich werde es gründlicher lesen!
Dann müsste aus

Columns("A:A").Select  
    With Selection
        **.HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom**
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

das werden:

$Worksheet.Range("A1:A3").HorizontalAlignment = 1  # Horizontal   
$Worksheet.Range("A1:A3").VerticalAlignment = -4107 # Vertikal   

Es wird ausgeführt, passiert aber nichts

Zitat von @Tastuser:
Dann müsste aus

Columns("A:A").Select  
>     With Selection
>         **.HorizontalAlignment = xlGeneral
>         .VerticalAlignment = xlBottom**
>         .WrapText = True
>         .Orientation = 0
>         .AddIndent = False
>         .IndentLevel = 0
>         .ShrinkToFit = False
>         .ReadingOrder = xlContext
>         .MergeCells = False
>     End With
> End Sub

das werden:

$Worksheet.Range("A1:A3").HorizontalAlignment = 1  # Horizontal   
> $Worksheet.Range("A1:A3").VerticalAlignment = -4107 # Vertikal   

Es wird ausgeführt, passiert aber nichts

Nein, erstens wird oben der Range A:A formatiert und bei dir A1:A3 und zweitens hast du nirgends die Variable $worksheet mit der Referenz auf das Sheet definiert.
Das es funktioniert siehst du an meinem ersten Skript, das läuft hier einwandfrei.

Beschäftige dich erstmal eingehend mit VBA, und danach gehst du zu Powershell über, denn du steigst zwei Stufen zu Hoch ein für den Anfang, denn bei der Arbeit mit Office COM-Automation über die Powershell gibt es einige Fallen die dich noch Kopfkratzen lassen werden.

Okay
Eine Frage hätte ich dennoch.
Zelle A1 und A4 bis A10 sind mit Inhalt.
Wie setze ich eine Formatierung für A11, wenn ich nicht weiß dass es A11 ist, da sich die Länge des Dokuments ändert und immer anders ist?
Also wie setze ich z.B. ein Feld für die Summe aller Zellen genau unter die letzte beschriebene Zelle?
Z.B.
A4 bis A10 sind mit Zahlen gefüllt
A11 Soll die Summe ausrechnen aus A4-A10.
An sich nicht so schwer da man weiß dass dies in A11 gehört.
Was ist jedoch wenn die Tabelle variabel ist und die Länge jedes mal anders ist?

Du könntest mit Bereiche atbeiten und dann jedes Mal das untere Ende des Bereichs auslesen + 1 Zeile.
Oder Du fragst die Zeilen der Reihe nach ab und nimmst die erste leere Zeile (oder anderes Kriterium)

Kein Problem
(Worksheet Variablen wie oben)

$last = $ws.Cells.Item($ws.Rows.Count,1).End(-4162)
$last.Offset(1,0).Value2 = "=Summe(A4:$($last.Address())"  

Wie gesagt, die Standardmethoden die man auch in VBA nutzt, deswegen ist es absolut hilfreich sich damit erst mal zu beschäftigen.

German Question Batch, Shell Development