Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Powershell Excel mehrere Tabellenspalten finden

Frage Entwicklung Batch & Shell

Mitglied: Marabunta

Marabunta (Level 1) - Jetzt verbinden

06.11.2014, aktualisiert 11.11.2014, 1480 Aufrufe, 7 Kommentare

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."
}

}
Mitglied: colinardo
06.11.2014, aktualisiert um 10:35 Uhr
Hallo Marabunta,
mach mal etwas deutlicher was du willst. Ich habe das mal so interpretiert:
01.
function WhoAmi(){ 
02.
    #Seriennummer suchen 
03.
    $result = $Worksheet.UsedRange.Find($seriennummer) 
04.
    # wurde sie gefunden 
05.
    if ($result -ne $null){ 
06.
        # füge die Informationen als Objekt an das Script-Array $script:data an 
07.
        $script:data += New-Object PSObject -Property @{"Seriennummer"=$seriennummer;"Row"=$result.Row;"Column"=$result.Column} 
08.
09.
10.
 
11.
# erstelle ein Array in dem die Infos gespeichert werden 
12.
$script:data = @() 
13.
# rufe die Funktion auf 
14.
WhoAmi 
15.
#gebe testweise das Objekt aus 
16.
$script:data
Zu Variablen-Scopes lese auch diese Seite:
http://technet.microsoft.com/de-de/library/hh847849.aspx

Grüße Uwe
Bitte warten ..
Mitglied: Marabunta
06.11.2014 um 16:32 Uhr
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"

    1. Seriennummern
    A2 = "C422"
    A3 = "A222"

    Das Skript muss jetzt die Spalte Seriennummer finden:
    "A"
    dann die Zeile seiner Seriennummer:
    "3"

    dann Spalte IP-Adresse:
    "B"
    und in seine Zeile dann die IP eintragen:
    "B3"

    dasselbe dann für die MAC-Adresse etc.

    Was ich bisher hinbekommen habe ist Seriennummer finden und "Spalte + 1" rechnen
    und nacheinander IP-Adresse und MAC-Adresse stupide eintragen.

    Wie bekomme ich also die Verbindungen hin die Spalte und Zeile in der ein Wert soll richtig zu bestimmen?
Bitte warten ..
Mitglied: colinardo
06.11.2014, aktualisiert um 17:05 Uhr
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
Bitte warten ..
Mitglied: Marabunta
06.11.2014 um 18:36 Uhr
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.
Bitte warten ..
Mitglied: colinardo
06.11.2014, aktualisiert 07.11.2014
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

ABC
1SeriennummerIPMAC
2123
3456
4789

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

Beispiel 1: Herkömmliche Art mit Range.Find
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
# Range in dem nach der Seriennummer gesucht wird 
09.
$rngSearch = $ws.Range("A:A") 
10.
 
11.
# Objekt mit Beispieldaten erzeugen 
12.
$data = @() 
13.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
14.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
15.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
16.
# ------- 
17.
 
18.
# für jeden Datensatz 
19.
foreach($set in $data){ 
20.
    # suche Seriennummer 
21.
    $result = $rngSearch.Find($set.SN) 
22.
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben 
23.
    if ($result -ne $null){ 
24.
        $result.Offset(0,1).Value2 = $set.IP 
25.
        $result.Offset(0,2).Value2 = $set.MAC 
26.
27.
}
Beispiel 2: Zuordnung der Daten in einem Array in Powershell und zurückschreiben in die Tabelle als Array
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
 
09.
# Alle Daten des Sheets in ein 2-Dimensionales Array laden 
10.
$array = $ws.UsedRange.Value2 
11.
 
12.
# Objekt mit Beispieldaten erzeugen 
13.
$data = @() 
14.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
15.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
16.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
17.
# ------- 
18.
 
19.
# für jeden Datensatz in den Beispieldaten ... 
20.
foreach($set in $data){ 
21.
    # filtere Array nach Seriennummer und schreibe zugehörige IP-Adresse und MAC Adresse an der richtigen Stelle in das Array 
22.
    2..($array.GetUpperBound(0)) | ?{$array[$_,1] -eq $set.SN} | %{$array[$_,2] = $set.IP; $array[$_,3] = $set.MAC } 
23.
24.
 
25.
#Schreibe geändertes Array wieder als ganzes zurück in die Tabelle 
26.
$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
Bitte warten ..
Mitglied: Marabunta
07.11.2014 um 15:21 Uhr
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. =/
Bitte warten ..
Mitglied: colinardo
LÖSUNG 07.11.2014, aktualisiert 11.11.2014
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:
01.
$rngMac = $ws.Range("1:1").Find('MAC') 
02.
$rngIP = $ws.Range("1:1").Find('IP') 
03.
$colMAC = $rngMac.Column 
04.
$colIP = $rngIP.Column
Somit hast du die richtigen Spalten für deine Daten ermittelt.

D.h das erste Script sähe dann so aus:
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
# Range in dem nach der Seriennummer gesucht wird 
09.
$rngSearch = $ws.Range("A:A") 
10.
 
11.
# Spaltennamen in der erste Zeile identifizieren 
12.
$rngMac = $ws.Range("1:1").Find('MAC') 
13.
$rngIP = $ws.Range("1:1").Find('IP') 
14.
$colMAC = $rngMac.Column 
15.
$colIP = $rngIP.Column 
16.
 
17.
# Objekt mit Beispieldaten erzeugen 
18.
$data = @() 
19.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
20.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
21.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
22.
# ------- 
23.
 
24.
# für jeden Datensatz 
25.
foreach($set in $data){ 
26.
    # suche Seriennummer 
27.
    $result = $rngSearch.Find($set.SN) 
28.
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben 
29.
    if ($result -ne $null){ 
30.
        $result.Offset(0,$colIP - 1).Value2 = $set.IP 
31.
        $result.Offset(0,$colMac -1).Value2 = $set.MAC 
32.
33.
}
Bitte warten ..
Neuester Wissensbeitrag
Humor (lol)

Linkliste für Adventskalender

(3)

Information von nikoatit zum Thema Humor (lol) ...

Ähnliche Inhalte
Batch & Shell
gelöst Powershell excel (6)

Frage von Crank69 zum Thema Batch & Shell ...

VB for Applications
gelöst Excel VBA Eine oder mehrere Zellen Verschieben (2)

Frage von batchnewbie zum Thema VB for Applications ...

Batch & Shell
Powershell multiple Datei-Inhaltssuche und mehrere Unterordner (3)

Frage von Jenny82 zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
Router & Routing
gelöst Ipv4 mieten (22)

Frage von homermg zum Thema Router & Routing ...

Exchange Server
gelöst Exchange 2010 Berechtigungen wiederherstellen (20)

Frage von semperf1delis zum Thema Exchange Server ...

Windows Server
DHCP Server switchen (20)

Frage von M.Marz zum Thema Windows Server ...

Hardware
gelöst Negative Erfahrungen LAN-Karten (19)

Frage von MegaGiga zum Thema Hardware ...