[Powershell] Excel Range von mehreren einzelnen Zellen

[schumischumi]

Hallo zusammen,

ich stehe vor der Herausforderung in einem bestehenden Excel Dokument regelmäßig (alle 2 Wochen) bestehende Werte abzuändern und zu kennzeichnen (rot markieren und Kommentar mit altem Wert).

Mit Powershell und der COM Schnittstelle funktioniert das programmatisch einwandfrei, aber die Ausführungszeit ist ekelhaft lang. Für eine zu ändernde Zelle mehrere Sekunden. Bei 1500-2000 Änderungen zieht sich das und ich komme teilweise auf mehrere Stunden.
Klar die COM-Schnittstelle ist unglaublich langsam, aber evtl. kann man an der Logik noch was optimieren.

Und zwar wäre meine Idee:
- Eine Range definieren
- Alle Werte in diese Range eintragen
- Einmal die Range über die COM-Schnittstelle setzen.
D.h. statt 2000 Zellen mit je 3 Anpassungen, nur noch 2 Ranges (Wert und Farbe) anpassen und 2000 mal den Kommentar.

Prinzipiell funktioniert das auch, aber ich habe die Werte nicht nebeneinander (z.B. A5-A12) sondern jeweils einzeln (z.B. A5, B12, D1...).
Habt ihr eine Idee wie man diese Range abbilden könnte, ohne erst alle Werte (auch die sich nicht geändert haben) einzulesen und über die druchgänige Range zu befüllen?

Zusatzfrage: Das Nadelöhr scheint die CPU zu sein, da sie beim Excel-Prozess bei ~25% dicht macht. Was bei HT (Core i7) einem logischen Kern entspricht. Kann ich Excel oder die Schnittstelle zum Multithreading zwingen (Haken bei "Multithread-Verabreitung aktivieren" ist gesetzt) oder bring das deaktiveren von HT was?

Danke euch!


PS: Falls das Thema besser zu Office passt bitte einfach verschieben

Edit: Win8 64Bit, Excel 2013 32 Bit, Powershell 4 64Bit

 

[morcego]

HT zu deaktivieren würde nichts bringen. Und eine parallel-Programmierung in PS vermutlich auch nicht.
Das COM ist schnarchlahm, ja. Genau deshalb habe ich wieder Abstand davon genommen und exportiere Werte entweder als csv oder programmiere es direkt in Office - also VBA als kleinste Option.

 

[schumischumi]

Dann wäre es umso interessanter, ob man die Excel Range auch aus nicht zusammenhängenden Zellen bilden kann.

Aber nochmal kurz zu HT:
Ich weiss dass es bei vielen modernen Anwendungen im zusammenspiel mit einem halbwegs aktuellen OS, für singe-core Anwendungen egal ist, ob HT aktiv oder nicht aktiv ist.
Da diese Schnittstelle, aber alles andere als toll ist, und ich eben genau bei diesen 25% eine Deckelung habe, wäre zumindes meine Vermutung, dass ich ohne HT auf 50% Auslastung gehen könnte.
Warum würdest du es direkt ausschließen?

Nachtrag:
Hab gerade den Test gemacht. Mit HT aktiv und ca. 90 Zellen die angepasst werden müssen hat das Ganze 6:50 Minuten gedauert und die CPU ging nicht über 25% (eher 20%).
Mit deaktiviertem HT und den selben Daten ging das Prozedere in 3 Minuten über die Bühne und die CPU ging für den XLS Prozess auf ca. 80% und mit der Powershell zusammen gegen 95%, also Vollauslastung.
Da bei dem Ablauf nur 90 Zellen betroffen waren, war auch der iniziale Berechnungsaufwand von Powershell auf die Gesamtzeit gesehen relativ hoch. D.h. alleine für die Exceleditierung hab ich die nur noch ein drittel bis die Hälfte gebraucht. sehr schön.
Werde HT also erstmal deaktiviert lassen, da die CPU für alle anderen Anwendungen eh oversized ist.

Trotzdem wäre es interessant, ob sich bei den Ranges noch was machen lässt.

 

[morcego]

Von der Logik her geht die Auslastung ganz sicher von 25 auf 50% hoch, denn beim Deaktivieren von HT verschwinden auch die Hälfte der Kerne. 1/4 ist halt weniger als 1/2.
Den Zuwachs finde ich trotzdem ziemlich heftig, das klingt komisch. Deckt sich jetzt nicht mit meinen Erfahrungen, muss ja aber nix heißen.

Im Netz grad das gefunden
Union(Cells(1, "A"), Cells(4, "B")).Select
oder so ginge das andere
Range("A1, B2, C4").Select

Du müsstest das auch so im PS nutzen können.
Du wirst ja ein $ExcelApp = New-PSObject -com Excel.Application generiert haben
Danach vermutlich das Workbook, Worksheet pipapo. Normal sollten dann die Methoden klappen.
Wenn du das im ISE baust, klappt das IntelliSense ja leider erst nachdem es einmal ausgeführt wurde.

 

[schumischumi]

Bei dem HT war ich auch freudig überascht.

Aber zum Thema.
Bei Range habe ich das Problem, dass ich erstens nur zwei Argumente angeben kann und dann immer eine Spanne von bis gebildet wird. D.h. es wird immer die ganze Reihe und keine Einzelwerte geändert. Somit werden auch alle Werte dazwischen geändert, die eigentlich unangetastet bleiben sollten.

Die Union Methode bekomme ich nicht zum laufen. Da bekomme ich immer einen Overload analog zu diesem Beispiel https://sreerenjnair.wordpress.com/2010/03/11/error-cannot-find-an-overload-for-union-and-the-argument-count-2/
Aber unabhänig von 32 und 64 Bit, auch mit vollen 30 Argumenten.
Zudem finde ich keine anderen Einträge im Netz ausser mit VB...

Schade aber vielen Dank.

 

[DPXone]

Du darfst auch nicht die Union-Methode von Excel.Application nehmen, sondern musst die von Worksheet.Application nutzen:

$xl = New-Object -ComObject Excel.Application

$wb = $xl.Workbooks.Add()
$ws = $wb.worksheets(1)

$r1 = $ws.range("A1:B5")
$r2 = $ws.range("D1:D10")

$ws.Application.Union($r1,$r2).Value = "ABC"

$xl.Visible = $true

 

[schumischumi]

Hi,
hab genau deinen code eingegeben (nur statt $ws = $wb.worksheets(1) dann $ws = $wb.worksheets.item(1)) und ich bekomme den selben Fehler mit

Für "Union" und die folgende Argumenteanzahl kann keine Überladung gefunden werden: "2".
In Zeile:9 Zeichen:5
+     $ws.Application.Union($r1,$r2).Value = "ABC"
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

 

[Drexel]

Dann wäre es umso interessanter, ob man die Excel Range auch aus nicht zusammenhängenden Zellen bilden kann.

Ich hatte kürzlich ein ähnliches Problem in VBA. Ich hab eine neue Excel Instanz geöffnet und mir daraus immer wieder Daten besorgt, ich denke das geht dann auch über COM und es war saulahm. Wenn ich ich die Datei in der gleichen Excel Instanz geöffnet habe war es wesentlich schneller. Ich hatte mir auch überlegt, bei der weiteren Excel Instanz alle Daten auf einmal zu holen, die ich brauche, und in ein Array einzulesen, das müsste dann eigentlich die schnellste Methode sein, da so alle Daten die ich brauche im Speicher sind. Aber ausprobiert habe ich es noch nicht.

 

[DPXone]

Hi,
hab genau deinen code eingegeben (nur statt $ws = $wb.worksheets(1) dann $ws = $wb.worksheets.item(1)) und ich bekomme den selben Fehler mit

Für "Union" und die folgende Argumenteanzahl kann keine Überladung gefunden werden: "2".
In Zeile:9 Zeichen:5
+     $ws.Application.Union($r1,$r2).Value = "ABC"
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Hast du denn meinen Code schon mal ohne Änderung/Anpassung probiert auszuführen?

 

[schumischumi]

@DPXone
Bei deinem Code erhalte ich nach Copy&Paste

Fehler beim Aufrufen der Methode, da [System.__ComObject] keine Methode mit dem Namen "worksheets" enthält.
In Zeile:4 Zeichen:5
+     $ws = $wb.worksheets(1)
+     ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (worksheets:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In Zeile:6 Zeichen:5
+     $r1 = $ws.range("A1:B5")
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In Zeile:7 Zeichen:5
+     $r2 = $ws.range("D1:D10")
+     ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat.
In Zeile:9 Zeichen:5
+     $ws.Application.Union($r1,$r2).Value = "ABC"
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Also der Fehler bei $ws = $wb.worksheets(1) da Anscheinend das Sheet nicht korrekt referenziert bzw. als Funktion aufgerufen wird.


@Drexel
Wenn du VBA meinst, geh ich mal davon aus, dass es kein Macro war oder?
Ansonsten würd ich es auch nochmal testen, meine Daten (Objekt mit x-/y-koordinaten und wert) zuerst in ein neues temporäres Sheet zu übertragen und dann von dort in das Ziel-Sheet zu übernehmen.
Welche Funktion hast du dort genutzt um die Daten zu übertragen? Exce-Copy/Paste, Clipboard-Copy/Paste...

Bei dem letzten weg den du nicht getestet hast, verstehe ich es richtig:
Du bildest den Array in VBA und fügst ihn dann komplett in Excel ein oder? Wie würdest du hier die Daten nach Excel übergeben? Bei einer Range werden ja auch die Daten dazwischen überschrieben.

 

[Drexel]

Doch ein Makro. Temporäres Sheet wäre auch eine Möglichkeit, aber Arrays/Collections müsste doch eigentlich das schnellste sein, da die Sachen dann im RAM sind. Ich habe auf die Zellen mit Range.Value oder Cell.Value, das müsste ich nachschauen, zugegriffen.

Ich muss mir Daten zusammensetzen die über Keys in der Excel verteilt sind und somit immer wieder die gleichen Blätter komplett durchlaufen pro Key.

 

[schumischumi]

Sry wollte schon gestern zurückschreiben, aber hab vergessen auf antworten zu klicken...

Das Problem sind bei mir nicht die Sachen im RAM oder die Berechnung der Werte.
Im Prinzip vergleiche ich 3 Dokumente (rel. komplexes XLS und 2 CSV) und erhalte dann ein Objekt. Dank PS (und dem Modul ExcelImport) geht das auch recht fix.
Das Object sieht dann ca. so aus:

x   y   value   oldvalue
1   2   449     582
4   7   429     882
6   4   369     322
8   2   259     222

Das Objekt füge ich dann mit einer Schleife in das Excel ein und diese Aktion, sprich die 4 Excel-Calls, dauern "ewig" (~3-5 Sekunden).

    foreach($item in $object){
        $WorkSheet.Cells.Item($item.y,$item.x).Value2 = $item.value
        [void] $WorkSheet.Cells.Item($item.y,$item.x).ClearComments()
        [void] $WorkSheet.Cells.Item($item.y,$item.x).AddComment($item.oldvalue.ToString())
        $WorkSheet.Cells.Item($item.y,$item.x).Font.ColorIndex = 3
    }

Wenn ich hier eine Möglichkeit hätte die selben Daten mit weniger Calls abzuschicken hätte ich einen gewinn.

Via Makro scheint es allg. schneller zu laufen, aber die Generierung des Objekts macht da nicht wirklich Spass und lieber warte ich hier länger bevor ich die 500Zeilen in VBA-Code übersetze.^^

 

[umask007]

Eventuell könntest du auch einen anderen lösungsansatz ausprobieren.
Wenn du per COM auf Excel zugreifst, läuft im Hintergrund eine Excel Instanz
(selbst wenn diese nicht sichtbar ist).

Versuch doch mal die automatische Formelaktualisierung zu deaktivieren,
und zusätzlich noch die Bildschrimaktualisierung.

Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False
...
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Ergänzung (7. Juli 2017)

Falls das nicht funktioniert, könnte folgender VBA Code hilfreich sein:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim values() As Variant
Dim x() As Integer
Dim y() As Integer

ReDim values(0 To Target.count - 1)
ReDim x(0 To Target.count - 1)
ReDim y(0 To Target.count - 1)
Dim count As Integer


Dim cell As Range
count = 0
For Each cell In Target.Cells

x(count) = cell.Row
y(count) = cell.Column
values(count) = count

count = count + 1
Next

SetRange x, y, values


End Sub


Private Sub SetRange(x() As Integer, y() As Integer, values() As Variant)

Dim count As Integer

If Not (UBound(x) = UBound(y) And UBound(y) = UBound(values) And LBound(x) = 0 And LBound(y) = 0 And LBound(values) = 0) Then Return

For count = 0 To UBound(x)
 Dim cell As Range
 Set cell = ActiveSheet.Cells(x(count), y(count))
 cell.Value = values(count)
 Set cell = Nothing


Next count


End Sub

Die Funktion SetRange nimmt ein Arrays mit x,y Koordinaten und dazugehörigen Werten entgegen.
So hättest du weniger COM-Calls. Du musst es nur noch schaffen, die Funktion aus Powershell heraus
aufzurufen.
https://stackoverflow.com/questions/19536241/calling-excel-macros-from-powershell-with-arguments

 

[DPXone]

Edit: Win8 64Bit, Excel 2013 32 Bit, Powershell 4 64Bit

Daran könnte es noch liegen, dass es bei dir nicht funktioniert.
Ich verwende Win10 64bit, Office 2016 32bit und PowerShell 5.1

PowerShell 5.1 könntest dir ja leicht besorgen.
Einfach das Windows Management Framework 5.1 installieren (ist eh vorteilhaft, das es einiges neues bietet)
https://www.microsoft.com/en-us/download/details.aspx?id=54616

Weil mein Code funktioniert bei mir wunderbar ohne Fehler.

 

[schumischumi]

@umask007
Mit den Einstellungen für Excel wirds auf jeden Fall nochmal besser. Falls jemand noch die Powershell implementierung benötigt:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false 
$Excel.ScreenUpdating = $false
$Excel.EnableEvents = $false
$ExcelWordBook = $Excel.Workbooks.OpenXML($Path2Excel)
$ExcelWorkSheet = $Excel.WorkSheets.item("Tabell1")
#Wichtig: Calculation erst nach öffnen des Workbooks und Sheets setzten, sonst gibts nen Fehler
$xlCalculationManual = -4135
$Excel.Calculation = $xlCalculationManual
...
#Wichtig: CalculateBeforeSave erst direkt vorm speichern setzen
$Excel.CalculateBeforeSave = $true
$ExcelWordBook.SaveAs($Path2NewExel)

VBA Code möchte ich wie gesagt nach Möglichkeit vermeiden. Sonst habe ich zwei Baustellen die gepflegt werden müssen. Aber wäre definitiv auch ein guter Weg.


@DPXone:
Eine neue PS Version ist leider nicht ganz so leicht möglich, da zentrale Softwareverteilung. Und bei einer manuellen Installation, hätte ich ein Skript, dass nur ich ausführen kann. Mal gucken ob ich den Kollegen dazu bringe, eine neue Version zu verteilen.

Edit: Falls Union jetzt funktioniern würde, wie würde ich analog deinem Beispiel mehrere Werte setzen?
Im Beispielcode wird ja ein Wert für mehrere Ranges gesetzt.

Ergänzung (10. Juli 2017)

So mit PS 5.1 funktioniert Union jetzt. D.h. sowohl ClearComment als auch die Schriftfarbe kann ich darüber schon mal super setzen.
Bei dem Values habe ich aber noch ein Problem. Und zwar kann man die anscheinend (link) in VBA mit einem Multidimensionalen Array füllen.

Zitat:

    Dim aWrite(1 To 5, 1 To 1) As Long
   
    aWrite(1, 1) = 2
    aWrite(2, 1) = 2
    aWrite(5, 1) = 2
   
    Union(Range(“A1:A2”), Range(“A5”)).Value = aWrite

Wenn ich dies in PS probiere füllt er gleich mal garnix:

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.Add()
$ws = $wb.worksheets.item("Tabelle1")
$r1 = $ws.range("A1:A1")
$r2 = $ws.range("D1:D1")
$multi[1,1] = "Hello"
$multi[4,1] = "World"
$ws.Application.Union($r1,$r2).Value = $multi
$xl.Visible = $true

Der zweite Punkt und hier kann es gut sein dass ich auf dem Schlauch stehe, ist die Frage wie ich die max. 30 Parameter dynamisch befülle.
Aktuell fällt mir nur ein sehr dummer weg ein ala:

$r1 = $ws.range("A1")
$r2 = $ws.range("D1")
...
$r30 = $ws.range("D50")

$ws.Application.Union($r1,$r2,...,$r30).Value = $multi

Und darum ne Schleife. Geht das nicht schöner?


Edit: Ok.... PS5 behebt anscheinend auch das Problem mit der langsamen COM-Schnittstelle. Bin jetzt statt 2 Minuten auf 6 Sekunden... Denke ich werd hier nicht mehr viel optimieren....
Auch bei umfassenderen Änderungen (ca. 1200) bin ich mit dem reinen Excel Teil in 19 Sekunden durch...
kotzt mich bissl an dass ich soviel zeit/mühe reingesteckt hab.