none
Excel Automation via Powershell RRS feed

  • Frage

  • Hallo an die Profis,

    Inhalte zum Titel muss man sich oft mühevoll im Netz zusammensuchen. Machmal jedoch auch ohne Erfolg. Mir geht es um die bedingte Formatierung. Ich kann ja in Excel die bedingte Formatierung für einen ganzen (markierten) Bereich festlegen.

    Hat jemand Erfahrung, wie ich diese Aufgabe mit Hilfe von Excel COM mit der Powershell lösen kann?

    Mir ist es bisher auch noch nicht gelungen, den VBA-Code nach Powershell zu konvertieren.

    Gruß

    dd.albert

    Donnerstag, 2. Februar 2012 17:39

Antworten

  • So ! Jetzt bin ich dazu gekommen mich damit in Ruhe auseinander zu setzen.
    Wenn du XL-Konstanten benutzt musst du sie natürlich vorher definieren (sieh Code).
    Welchen Wert die Konstanten haben steht leider nicht in der sehr guten Dokumentation.
    http://msdn.microsoft.com/de-de/library/ms262200%28v=office.11%29.aspx
    Aber du kannst in Excel VBA eine Modul kreieren und die Konstanten darin abfragen.
    z.B. so:

    Sub Konstanten()
      MsgBox ("xlLineStyleNone =" & xlLineStyleNone)
    End Sub

    Eine große Falle ist, das PowerShell alles was in einem String steht und mit $ anfängt als Variable Parst !!!!!!!!
    Das geht nicht ! "=$a$7" !!!!
    Hier müssen die Dollarzeichenmit dem Backtick  „escaped“ werden oder du schreibst es in einfache Anführungszeichen!
    Das geht : '=$a$7'
    Die Zeile: #$Formatcondition1.Borders.Weight = $xlThick Habe ich auskommentiert.
    Warum das nicht geht überlasse ich dir ;-))

    # get a new instance of Excel as COM-Object
    $excel = new-object -comobject excel.application
    $excel.visible = $true
    
    # Adding a new Workbook to Excel Object
    $workbook=$excel.Workbooks.add()
    # adding a new worksheet to the Worbook
    $worksheet = $workbook.Worksheets.Item(1)
    
    # filling some cells with use of Range Object
    $worksheet.Range("A7").Value2 = 2
    $worksheet.Range("A1").Value2 = 1
    $worksheet.Range("A2").Value2 = 2
    $worksheet.Range("A3").Value2 = 3
    $worksheet.Range("A4").Value2 = 4
    
    # Create the constants we need to do
    # Formatcondition stuff
    $xlCellValue = 1
    $xlExpression = 2
    
    $xlBetween = 1
    $xlNotBetween =	2
    $xlEqual = 3
    $xlNotEqual = 4
    $xlGreater = 5
    $xlLess = 6
    $xlGreaterEqual = 7
    $xlLessEqual = 8
    
    # adding (create) a Formatcondition to the Range A1:A5 wich gets the number 1 in the Formatcondition Collection
    # 3 Formatconditions are Possible
    $Formatcondition1 = $Worksheet.Range("A1:A5").FormatConditions.Add($xlCellValue, $xlGreater, '=$a$7')
    
    
    # Excel Borders Constants (xlBorderIndex)
    $xlEdgeRight = 10
    $xlEdgeLeft = 7
    $xlEdgeTop = 8
    $xlEdgeBottom = 9
    $xlDiagonalDown = 5
    $xlDiagonalUp = 6
    $xlThin = 2
    $xlContinuous = 1
    
    # Excel Borders Constants (xlBorderWeight) 
    $xlHairline = 1
    $xlThin = 2
    $xlMedium = -4138
    $xlThick = 3
    
    # Excel Borders Constants (xlLineStyle)
    $xlContinuous = 1
    $xlDash = -4115
    $xlDashDot = 4
    $xlDashDotDot = 5
    $xlDot = - 4118
    $xlDouble = -4119
    $xlSlantDashDot = 13
    $xlLineStyleNone = -4142
    
    
    # using the Formatcondition
    $Formatcondition1.Font.ColorIndex = 1
    $Formatcondition1.Interior.ColorIndex = 4
    $Formatcondition1.Font.Bold = 1
    $Formatcondition1.Borders.LineStyle = $xlContinuous
    #$Formatcondition1.Borders.Weight = $xlThick
    



    Please click “Mark as Answer” if my post answers your question and click Vote as Help if my Post helps you.
    Bitte markiere hilfreiche Beiträge von mir als Hilfreich und Beiträge die deine Frage ganz oder teilweise beantwortet haben als Antwort.
    My PowerShell Blog http://www.admin-source.info
    [string](0..21|%{[char][int]([int]("{0:d}" -f 0x28)+('755964655967-86965747271757624-8796158066061').substring(($_*2),2))})-replace" "

    • Bearbeitet Peter Kriegel Samstag, 4. Februar 2012 12:24
    • Als Antwort markiert dd.albert Samstag, 4. Februar 2012 15:40
    Samstag, 4. Februar 2012 12:22

Alle Antworten

  • Machs uns nicht so schwer! ;-)
    Wo ist den der VBA code?

    Mit PowerShell:
    http://social.msdn.microsoft.com/Forums/pl-PL/exceldev/thread/116fdea9-41f2-4caa-acb2-3375f7b699f9

    Hier habe ich was gefunden (VB):
    http://www.foxite.com/archives/conditional-format-0000244274.htm

    Documentation FormatConditions:
    http://msdn.microsoft.com/de-de/library/ms262200%28v=office.11%29.aspx

    Dokumentation Excel Objecte:
    http://msdn.microsoft.com/de-de/library/aa288626%28v=vs.71%29.aspx

    Beste Suchergebnisse mit beispiel code:
    http://social.msdn.microsoft.com/search/de-de?query=.FormatConditions&x=0&y=0


    Please click “Mark as Answer” if my post answers your question and click "Vote as Help" if my Post helps you. Bitte markiere hilfreiche Beiträge von mir als "Hilfreich" und Beiträge die deine Frage ganz oder teilweise beantwortet haben als "Antwort". Das wäre Nett :-)) My PowerShell Blog http://www.admin-source.info

    Freitag, 3. Februar 2012 06:16
  • Hallo Peter,

    Danke erstmal für die Antwort. Ich sehe die Seiten mal durch, vielleicht finde ich die Lösung. Melde mich dann wieder.

    Hier noch der VBA-Code:

     

    ' Bedungte Formatierung für einen Bereich

    Range("A1:J21").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G1=""Privat"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Gruß Dolf-D. Albert

    Samstag, 4. Februar 2012 07:33
  • Also, bis auf eine habe ich die Seiten schon zigmal durchgearbeitet. Weil es sich aber um einen Bereich handelt, komme ich da eben nicht weiter.

    Hier mal der Code. Im unteren Bereich scheint was zu fehlen. Trotz Brille, ich sehe es nicht.

    $excel = new-object -comobject excel.application
    $excel.visible = $true
    $workbook=$excel.Workbooks.add()
    $worksheet = $workbook.Worksheets.Item(1)

    $worksheet.Range("A7").Value2 = 2
    $worksheet.Range("A1").Value2 = 1
    $worksheet.Range("A2").Value2 = 2
    $worksheet.Range("A3").Value2 = 3
    $worksheet.Range("A4").Value2 = 4

    $worksheet.Range("A1:A5").FormatConditions.Add($xlCellValue, $xlGreater, "=$a$7")
    $worksheet.Range("A1:A5").FormatConditions.Borders.LineStyle = $xlContinuous
    $worksheet.Range("A1:A5").FormatConditions.Borders.Weight = $xlThin
    $worksheet.Range("A1:A5").FormatConditions.Borders.ColorIndex = 6

    Gruß D.-D. Albert

    Samstag, 4. Februar 2012 07:49
  • So ! Jetzt bin ich dazu gekommen mich damit in Ruhe auseinander zu setzen.
    Wenn du XL-Konstanten benutzt musst du sie natürlich vorher definieren (sieh Code).
    Welchen Wert die Konstanten haben steht leider nicht in der sehr guten Dokumentation.
    http://msdn.microsoft.com/de-de/library/ms262200%28v=office.11%29.aspx
    Aber du kannst in Excel VBA eine Modul kreieren und die Konstanten darin abfragen.
    z.B. so:

    Sub Konstanten()
      MsgBox ("xlLineStyleNone =" & xlLineStyleNone)
    End Sub

    Eine große Falle ist, das PowerShell alles was in einem String steht und mit $ anfängt als Variable Parst !!!!!!!!
    Das geht nicht ! "=$a$7" !!!!
    Hier müssen die Dollarzeichenmit dem Backtick  „escaped“ werden oder du schreibst es in einfache Anführungszeichen!
    Das geht : '=$a$7'
    Die Zeile: #$Formatcondition1.Borders.Weight = $xlThick Habe ich auskommentiert.
    Warum das nicht geht überlasse ich dir ;-))

    # get a new instance of Excel as COM-Object
    $excel = new-object -comobject excel.application
    $excel.visible = $true
    
    # Adding a new Workbook to Excel Object
    $workbook=$excel.Workbooks.add()
    # adding a new worksheet to the Worbook
    $worksheet = $workbook.Worksheets.Item(1)
    
    # filling some cells with use of Range Object
    $worksheet.Range("A7").Value2 = 2
    $worksheet.Range("A1").Value2 = 1
    $worksheet.Range("A2").Value2 = 2
    $worksheet.Range("A3").Value2 = 3
    $worksheet.Range("A4").Value2 = 4
    
    # Create the constants we need to do
    # Formatcondition stuff
    $xlCellValue = 1
    $xlExpression = 2
    
    $xlBetween = 1
    $xlNotBetween =	2
    $xlEqual = 3
    $xlNotEqual = 4
    $xlGreater = 5
    $xlLess = 6
    $xlGreaterEqual = 7
    $xlLessEqual = 8
    
    # adding (create) a Formatcondition to the Range A1:A5 wich gets the number 1 in the Formatcondition Collection
    # 3 Formatconditions are Possible
    $Formatcondition1 = $Worksheet.Range("A1:A5").FormatConditions.Add($xlCellValue, $xlGreater, '=$a$7')
    
    
    # Excel Borders Constants (xlBorderIndex)
    $xlEdgeRight = 10
    $xlEdgeLeft = 7
    $xlEdgeTop = 8
    $xlEdgeBottom = 9
    $xlDiagonalDown = 5
    $xlDiagonalUp = 6
    $xlThin = 2
    $xlContinuous = 1
    
    # Excel Borders Constants (xlBorderWeight) 
    $xlHairline = 1
    $xlThin = 2
    $xlMedium = -4138
    $xlThick = 3
    
    # Excel Borders Constants (xlLineStyle)
    $xlContinuous = 1
    $xlDash = -4115
    $xlDashDot = 4
    $xlDashDotDot = 5
    $xlDot = - 4118
    $xlDouble = -4119
    $xlSlantDashDot = 13
    $xlLineStyleNone = -4142
    
    
    # using the Formatcondition
    $Formatcondition1.Font.ColorIndex = 1
    $Formatcondition1.Interior.ColorIndex = 4
    $Formatcondition1.Font.Bold = 1
    $Formatcondition1.Borders.LineStyle = $xlContinuous
    #$Formatcondition1.Borders.Weight = $xlThick
    



    Please click “Mark as Answer” if my post answers your question and click Vote as Help if my Post helps you.
    Bitte markiere hilfreiche Beiträge von mir als Hilfreich und Beiträge die deine Frage ganz oder teilweise beantwortet haben als Antwort.
    My PowerShell Blog http://www.admin-source.info
    [string](0..21|%{[char][int]([int]("{0:d}" -f 0x28)+('755964655967-86965747271757624-8796158066061').substring(($_*2),2))})-replace" "

    • Bearbeitet Peter Kriegel Samstag, 4. Februar 2012 12:24
    • Als Antwort markiert dd.albert Samstag, 4. Februar 2012 15:40
    Samstag, 4. Februar 2012 12:22
  • Danke für das Zeitopfer und den Script.
    Ich bin nach langem Probieren auf die Anführungszeichen gekommen.
    Meine drei einfachen Zeilen waren:

    $worksheet.Range("A1:E10").Select()
    $excel.Selection.FormatConditions.Add($xlExpression, 0, '=ZÄHLENWENN($E1;"Privat")')
    $excel.Selection.FormatConditions.Item(1).Interior.ColorIndex = 6

    Dein Beispiel beantwortet gleich noch ein paar Fragen mehr.

    Powershell-Excel-Automation ist trotz meiner '60' noch ein bisschen Neuland. Spezielle Sachen findet man nur schwer im Netz und VBA oder überhaupt Excel brauchte ich bisher nicht. Ich schwärme seit vielen Jahren für das Datenbanksystem FileMaker, dass seine Daten auch als Excel-Datei ausgeben kann, aber eben nicht so gut formatiert. Mit Hilfe der Automation kann man eben schneller fertig ausdruckbare Arbeitsmappen weiter geben.

    Danke nochmals für die Mühen, freue mich darüber.

    Gruß Dolf-D.

    Hier habe ich eine Zusammenfassung der Enumerationen kopiert: 

    http://www.datapigtechnologies.com/downloads/Excel_Enumerations.txt

    Samstag, 4. Februar 2012 15:55