Top-Themen

Aktuelle Themen (A bis Z)

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, 1770 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 ..
Ähnliche Inhalte
Batch & Shell
Powershell Skript Ändern - Mehrere Hotfix IDs finden
Frage von pixel0815Batch & Shell1 Kommentar

Moin Moin lässt sich folgendes Skript so erweitern das man mehrere Patch IDs suchen kann? Habe das Skript im ...

Batch & Shell
Powershell excel
gelöst Frage von Crank69Batch & Shell6 Kommentare

Hallo Leute, ich habe in meinem Script leider 3 Probleme und hoffe das ihr mir helfen könnt. würde ich ...

Batch & Shell
Powershell für excel
gelöst Frage von Crank69Batch & Shell9 Kommentare

Hallo ich benötige hilfe bei Powershell leider bin ich noch neu im Programmieren und würde mich freuen wenn ihr ...

Batch & Shell
PowerShell Wert in Array finden
gelöst Frage von derhoeppiBatch & Shell7 Kommentare

Hallo, in meinem Projekt lese ich einen Eintrag aus einer Combobox aus. Dieser wird in eine Variable / Objekt ...

Neue Wissensbeiträge
Erkennung und -Abwehr

Necur-Botnet soll Erpressungstrojaner Scarab massenhaft verbreiten

Information von BassFishFox vor 10 StundenErkennung und -Abwehr

12,5 Millionen Spam-Mails aus einem Bot-Netz mit 6 Millionen Computern? Eigentlich eine schwache Leistung. Die Erpresser setzen dabei auf ...

Microsoft

Nadeldrucker-Problem unter Windows - Microsoft liefert Updates

Information von BassFishFox vor 10 StundenMicrosoft

Hat ja nicht lange gedauert. Nachdem die November-Updates für Windows 7, 8.1 und 10 zahlreiche Nadeldrucker lahmgelegt hatten, stellt ...

Linux

Limux-Ende in München: Wie ein Linux Projekt unter Ausschluss der Öffentlichkeit zerstört wurde

Information von Frank vor 18 StundenLinux14 Kommentare

Mein persönlicher Kommentar zum Thema "Limux-Ende". Die SPD-Politikerin Anne Hübner hat die Richtung von München ganz klar definiert: "Wir ...

Batch & Shell

Open Object Rexx: Eine mittlerweile fast vergessene Skriptsprache aus dem Mainframebereich

Information von Penny.Cilin vor 1 TagBatch & Shell9 Kommentare

Ich kann mich noch sehr gut an diese Skriptsprache erinnern und nutze diese auch heute ab und an noch. ...

Heiß diskutierte Inhalte
Windows Server
Kann man im KMS nachschauen , wieviele Clients den Key in Anspruch genommen haben
gelöst Frage von rainergugusWindows Server15 Kommentare

Hallo, wir haben einen KMS Windows 10 Key. Dieser ist ja W7 kompatibel. Aber unser Windows 7 Pool registriert ...

Linux
Limux-Ende in München: Wie ein Linux Projekt unter Ausschluss der Öffentlichkeit zerstört wurde
Information von FrankLinux14 Kommentare

Mein persönlicher Kommentar zum Thema "Limux-Ende". Die SPD-Politikerin Anne Hübner hat die Richtung von München ganz klar definiert: "Wir ...

Router & Routing
Zwei Netzwerke erstellen
Frage von bunteblumeRouter & Routing14 Kommentare

Hallo Zusammen, Ich möchte gerne ein backup von einem bestimmten Folder welcher auf dem Server regelmässig synchronisiert wird auf ...

Off Topic
Fachkräftemangel in Deutschland? - Talentschmiede schreibt alle 2 Tage die gleichen Stellen aus
Frage von Penny.CilinOff Topic12 Kommentare

Hallo, haben wir in Deutschland Fachkräftemangel? Die Talentschmiede schreibt gefühlt alle zwei Tage dieselben Stellen aus. Und das schon ...