Nov 06, 2014, updated at Nov 11, 2014 (UTC)

5702

Powershell Excel mehrere Tabellenspalten finden

Hallo,

ich habe eine Funktion geschrieben die mir die Zeile und Spalte einer Seriennummer findet.
Zusätzlich sollen aber noch weitere Zeilen und Spaltennummern in Variablen gespeichert werden. (Also z.B. von "IP-Adresse", "MAC-Adresse")
Mit einer anderen Funktion werden dann die Werte jeweils aktualisiert. Wie bekomme ich es hin, dass ich alles abspeichere und später verwenden kann ohne das es gleich wieder überschrieben wird?

Function WhoAmI{
$Global:Range = $Worksheet.Range("A1:ZZ50000").EntireColumn
$Global:Search = $Range.find($Seriennummer)
$Global:Zeile=$Search.row
$Global:Spalte=$Search.Column

if($Seriennummer -ne $null)
{
if($Zeile -ne $null)
{
if($Spalte -ne $null)
{
Write-Host "Suche : $Seriennummer"
Write-Host "Zeile : $Zeile"
Write-Host "Spalte: $Spalte"
UpdateZelle
}
else{
Write-Host "Spalte: $Spalte , ist NULL."
}
}
else{
Write-Host "Zeile: $Zeile , ist NULL."
}
}
else{
Write-Host "Seriennummer: $Seriennummer , ist NULL."
}

}

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

Content-Key: 254024

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

Printed on: April 19, 2024 at 22:04 o'clock

7 Comments

Latest comment

Hallo Marabunta,
mach mal etwas deutlicher was du willst. Ich habe das mal so interpretiert:

function WhoAmi(){
    #Seriennummer suchen
    $result = $Worksheet.UsedRange.Find($seriennummer)
    # wurde sie gefunden
    if ($result -ne $null){
        # füge die Informationen als Objekt an das Script-Array $script:data an
        $script:data += New-Object PSObject -Property @{"Seriennummer"=$seriennummer;"Row"=$result.Row;"Column"=$result.Column}  
    }
}

# erstelle ein Array in dem die Infos gespeichert werden
$script:data = @()
# rufe die Funktion auf
WhoAmi
#gebe testweise das Objekt aus
$script:data

Zu Variablen-Scopes lese auch diese Seite:
http://technet.microsoft.com/de-de/library/hh847849.aspx

Grüße Uwe

Alles in einem Array funktioniert nicht wie ich es mir vorstelle(meine ich).

Bsp.:
Seriennummer=A222

Tabelle:
A1 = "Seriennummer"
B1 = "IP-Adresse"
C1 = "MAC-Adresse"

Seriennummern

Alles in einem Array funktioniert nicht wie ich es mir vorstelle(meine ich).

wieso nicht ? geht alles wenn man weis wie

ganz einfach, in meinem Beispiel enthält $result den "Range" der gefundenen Zelle der Seriennummer. Jetzt kannst du mit $result.Offset(0,1) die jeweilige Zelle rechts daneben ansprechen (IP) oder mit $result.Offset(0,2) noch eine Zelle weiter für die MAC.

Ich würde schon gleich beim Suchen nach der Seriennummer die dazugehörigen Werte in die Tabelle schreiben, so entfällt das zwischenspeichern und du sparst wertvolle Scriptzeit, da ja die Powershell-Automation von Office Performancetechnisch sowieso nicht gerade berauschend ist.

Performancetechnisch noch besser wäre es den kompletten Range in einem Rutsch in ein Array zu laden, die Daten im Array zu suchen und zu verändern und dann das Array wieder in einem Rutsch zurückzuschreiben.
Wenn du möchtest, mache ich dir dazu morgen mal ein Beispiel.

Ich empfehle dir auch die Excel VBA-Referenz die Methoden und Eigenschaften sind dort nämlich fast exakt gleich anzuwenden wie in Powershell, da es sich ja um das selbe Objektmodell handelt. Hilft ungemein

Grüße Uwe

Ich hab mit dem Com-Objekt von Excel jetzt das erste Mal zu tun.
Wenn du mir ein Beispiel zur Orientierung machen kannst, bringt mich bestimmt ein gutes Stück in Richtung Ziel.
Alles im Array zu speichern und darin zu verändert klingt spannend, habe ich bisher nie so gemacht bzw. kann ich bis dato nicht.

Zitat von @Marabunta:
Ich hab mit dem Com-Objekt von Excel jetzt das erste Mal zu tun.
Wenn du mir ein Beispiel zur Orientierung machen kannst, bringt mich bestimmt ein gutes Stück in Richtung Ziel.
Alles im Array zu speichern und darin zu verändert klingt spannend, habe ich bisher nie so gemacht bzw. kann ich bis dato
nicht.

OK, ich gehe jetzt einfach mal von folgendem Aufbau der Excel-Datei aus

	A	B	C
1	Seriennummer	IP	MAC
2	123
3	456
4	789

Beide Codebeispiele machen das selbe, aber auf zwei unterschiedliche Arten. Kommentare findest du im Code.

Beispiel 1: Herkömmliche Art mit Range.Find

# Excel COM-Ojekt erzeugen
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
# Workbook öffnen
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx")  
# Tabelle 1 wählen
$ws = $wb.Worksheets.Item(1)
# Range in dem nach der Seriennummer gesucht wird
$rngSearch = $ws.Range("A:A")  

# Objekt mit Beispieldaten erzeugen
$data = @()
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"}  
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"}  
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"}  
# -------

# für jeden Datensatz
foreach($set in $data){
    # suche Seriennummer
    $result = $rngSearch.Find($set.SN)
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben
    if ($result -ne $null){
        $result.Offset(0,1).Value2 = $set.IP
        $result.Offset(0,2).Value2 = $set.MAC
    }
}

Beispiel 2: Zuordnung der Daten in einem Array in Powershell und zurückschreiben in die Tabelle als Array

# Excel COM-Ojekt erzeugen
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
# Workbook öffnen
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx")  
# Tabelle 1 wählen
$ws = $wb.Worksheets.Item(1)

# Alle Daten des Sheets in ein 2-Dimensionales Array laden
$array = $ws.UsedRange.Value2

# Objekt mit Beispieldaten erzeugen
$data = @()
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"}  
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"}  
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"}  
# -------

# für jeden Datensatz in den Beispieldaten ...
foreach($set in $data){
    # filtere Array nach Seriennummer und schreibe zugehörige IP-Adresse und MAC Adresse an der richtigen Stelle in das Array
    2..($array.GetUpperBound(0)) | ?{$array[$_,1] -eq $set.SN} | %{$array[$_,2] = $set.IP; $array[$_,3] = $set.MAC }
}

#Schreibe geändertes Array wieder als ganzes zurück in die Tabelle
$ws.UsedRange.Value2 = $array

Hier noch einige Erläuterungen für den Code im zweiten Beispiel.

`2..($array.GetUpperBound(0))`	Das macht nichts anderes als eine for-Schleife für alle Zeilen zu erzeugen. Die Überschriften lassen wir dabei aus
`?{$array[$_,1] -eq $set.SN`}	Das ist die kurze Variante des Where-Object(?{}) und filtert die Zeile welche die Seriennummer in Spalte 1 enthält. Das $_ enthält die Zeilennummer.
`%{$array[$_,2] = $set.IP; $array[$_,3] = $set.MAC`}	Zum Schluss wird mit der Abkürzung des Foreach-Objektes (%{}) in Spalte 2 die IP und Spalte 3 des Arrays die MAC Adresse eingetragen.

So, hoffe das gibt dir etwas Auftrieb in deinem Vorhaben.

Grüße Uwe

beide Varianten scheitern an der selben Stelle wie mein Skript.
Die Spalte wird nicht richtig identifiziert sondern "nur" hochgezählt.
Wenn ich MAC in eine Spalte daneben setze wird es in die "FalscheSpalte" eingetragen.

Seriennummer ## IP ######### ######## FalscheSpalte ################MAC
123 ########### 192.168.1.20 ######## 44:44:44:44:44:44
456 ########### 192.168.1.100 ######## 55:55:55:55:55:55
789 ########### 192.168.1.200 ######## 66:66:66:66:66:66

Irgendwie muss es funktionieren die Spalte zum Inhalt zu identifizieren. =/

Zitat von @Marabunta:

beide Varianten scheitern an der selben Stelle wie mein Skript.

also, hier laufen beide einwandfrei, oder wir reden hier aneinander vorbei. Sind die Spalten für IP und MAC nicht immer an der selben stelle sondern immer in anderen Spalten zu finden, d.h. variabel ? Wäre von Vorteil für uns wenn du uns deinen genauen Tabellenaufbau mal näher bringst. Merci.
Dann musst du diese Spalten erst in den Überschriften suchen, nach dem gleichen Schema:

$rngMac = $ws.Range("1:1").Find('MAC')  
$rngIP = $ws.Range("1:1").Find('IP')  
$colMAC = $rngMac.Column
$colIP = $rngIP.Column

Somit hast du die richtigen Spalten für deine Daten ermittelt.

D.h das erste Script sähe dann so aus:

# Excel COM-Ojekt erzeugen
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
# Workbook öffnen
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx")  
# Tabelle 1 wählen
$ws = $wb.Worksheets.Item(1)
# Range in dem nach der Seriennummer gesucht wird
$rngSearch = $ws.Range("A:A")  

# Spaltennamen in der erste Zeile identifizieren
$rngMac = $ws.Range("1:1").Find('MAC')  
$rngIP = $ws.Range("1:1").Find('IP')  
$colMAC = $rngMac.Column
$colIP = $rngIP.Column

# Objekt mit Beispieldaten erzeugen
$data = @()
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"}  
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"}  
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"}  
# -------

# für jeden Datensatz
foreach($set in $data){
    # suche Seriennummer
    $result = $rngSearch.Find($set.SN)
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben
    if ($result -ne $null){
        $result.Offset(0,$colIP - 1).Value2 = $set.IP
        $result.Offset(0,$colMac -1).Value2 = $set.MAC
    }
}

German solved Question Batch, Shell Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 3 Comments

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments

WIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 3 Comments

End of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment