Excelwerte über Powershell auslesen und formatieren
Hallo zusammen,
ich sitze vor folgendem Problem:
Ich möchte in einer beliebigen Exceldatei(Auswahl muss durch Benutzer erfolgen können) nach Benutzerdefinierten Textfolgen(gesamt 3) suchen,
Problem an dieser Sache ist dass die Werte in folgendem Format vorliegen (Bezeichnung'Leerzeichen'Wert)
Wert1 12345
Wert2 xyz
Wert3 Musterstraße 123
beliebiger Text
beliebiger Text
beliebiger Text
Wert1 56789
Wert2 abc
Wert3 Musterweg 9
Die Werte (12345,xyz, Musterstraße 123) sollen in eine neue Exceldatei gespeichert werden, und zwar in einzelne Spalten
Wert1 Wert2 Wert 3
12345 Xyz Musterstraße 123
56789 abc Musterweg 9
Kann mir jemand hier weiterhelfen? Vielen Dank!
ich sitze vor folgendem Problem:
Ich möchte in einer beliebigen Exceldatei(Auswahl muss durch Benutzer erfolgen können) nach Benutzerdefinierten Textfolgen(gesamt 3) suchen,
Problem an dieser Sache ist dass die Werte in folgendem Format vorliegen (Bezeichnung'Leerzeichen'Wert)
Wert1 12345
Wert2 xyz
Wert3 Musterstraße 123
beliebiger Text
beliebiger Text
beliebiger Text
Wert1 56789
Wert2 abc
Wert3 Musterweg 9
Die Werte (12345,xyz, Musterstraße 123) sollen in eine neue Exceldatei gespeichert werden, und zwar in einzelne Spalten
Wert1 Wert2 Wert 3
12345 Xyz Musterstraße 123
56789 abc Musterweg 9
Kann mir jemand hier weiterhelfen? Vielen Dank!
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 298525
Url: https://administrator.de/forum/excelwerte-ueber-powershell-auslesen-und-formatieren-298525.html
Ausgedruckt am: 24.12.2024 um 12:12 Uhr
4 Kommentare
Neuester Kommentar
Hallo Snakebite2004,
ein paar Fragen dazu:
Wäre schön wenn du das ganze noch etwas präzisieren könntest. Du weist ja hoffentlich Programmierer benötigen es exakt damit Programme das machen was der User will !
Gruß jodel32
ein paar Fragen dazu:
- Meinst du mit Excel-Dateien :CSV-Dateien oder xlsx/xlsm etc. ?
nach Benutzerdefinierten Textfolgen(gesamt 3) suchen,
- Das ist mir zu lasch formuliert, mit drei Begriffen gleichzeitig suchen ? In beliebiger Reihenfolge oder nach welchem Schema ?
- Sind es immer 3 Zeilen die übernommen werden müssen (Gruppe) auch wenn nur ein Suchwort gefunden wird oder wie stellst du dir das vor ?
Wäre schön wenn du das ganze noch etwas präzisieren könntest. Du weist ja hoffentlich Programmierer benötigen es exakt damit Programme das machen was der User will !
Gruß jodel32
Obwohl ich das bei der Kombination von Excel und Powershell mit VBS machen würde (da sehr viel schneller... die Automatisierung von Excel ist in Powershell nämlich ziemlich lahm) hier die Powershell-Variante:
Die Anzeige der Excel-Fenster lässt sich im Code Zeile 29 ausschalten, ist nur für deine Debug-Zwecke eingeschaltet. Nicht ungeduldig werden wenn das Fenster etwas länger offen ist während gesucht wird - wie gesagt Excel-Automatisierung via COM ist mit Powershell ein graus!
Abgespeichert wird das Result-Sheet im selben Verzeichnis wie die vom User ausgewählte Datei, kannst du aber nach belieben in Zeile 20 ändern.
Viel Spaß damit Mister Schlangenbiss .
Weitere Anpassungen gibt's von mir nur gegen Cash.
Gruß jodel32
Die Anzeige der Excel-Fenster lässt sich im Code Zeile 29 ausschalten, ist nur für deine Debug-Zwecke eingeschaltet. Nicht ungeduldig werden wenn das Fenster etwas länger offen ist während gesucht wird - wie gesagt Excel-Automatisierung via COM ist mit Powershell ein graus!
Abgespeichert wird das Result-Sheet im selben Verzeichnis wie die vom User ausgewählte Datei, kannst du aber nach belieben in Zeile 20 ändern.
Add-Type -AssemblyName System.Windows.Forms
cls
# Suchwörter abfragen
$searchterms = @()
1..3 | %{
$searchterms += (Read-Host "Suchwert $_ eingeben").ToString()
}
# Datei-Auswahldialog ----
$dlg = New-Object System.Windows.Forms.OpenFileDialog
$dlg.Multiselect = $false
$dlg.Title = 'Datei die durchsucht werden soll auswählen'
$dlg.Filter = 'Excel Dateien | *.xlsx;*.xls;*.xlsm'
if($dlg.ShowDialog() -eq 'OK'){
$quelle = $dlg.FileName
}
# --------
# Speicherpfad für Ausgabedatei (im selben Ordner wie die Quelle)
$savepath = "$(Split-Path $quelle -Parent)\result.xlsx"
# Excel Objekt
$objExcel = New-Object -Com Excel.Application
# Dialogfelder ausschalten
$objExcel.DisplayAlerts = $false
# Mappe öffnen
$wb = $objExcel.Workbooks.Open($quelle)
# Excel anzeigen (wenn nicht gewünscht auf $false setzen)
$objExcel.Visible = $true
# Neues Workbook erstellen
$wbout = $objExcel.Workbooks.Add()
# Ausgabesheet im neuen Workbook
$wsout = $wbout.Sheets.Item(1)
# Überschriften der Spalten im Ausgabesheet setzen
$wsout.Range("A1:C1").Value() = $searchterms
# Suchbereich im Quellsheet festlegen
$rngSearch = $wb.Sheets.Item(1).Range("A:A")
$col = 1
# Für jedes Suchwort einen Suchvorgang starten
$searchterms | %{
$found = @()
$result = $rngSearch.Find("$_*",[System.Reflection.Missing]::Value,-4163)
# Wenn Wert gefunden wurde
if ($result -ne $null){
# erste Fundstelle speichern wegen Abbruchbedingung
$f = $result.Address()
Do{
# Fund mit Leerzeichen splitten und in Array speichern
$found += $result.Value().Split(" ",2)[1]
# nächste Fundstelle suchen
$result = $rngSearch.FindNext($result)
}while($result -ne $null -and $result.Address() -ne $f)
# 2-dim Array erstellen und gefundene Wert dort hinterlegen
$arrFound = New-Object 'object[,]' $found.Count,1
0..($found.count-1) | %{
$arrFound[$_,0] = $found[$_]
}
# Array in Spalte schreiben
$wsout.Cells.Item(2,$col).Resize($found.Count,1).Value() = $arrFound
}
$col++
}
$wbout.SaveAs($savepath)
$wbout.Close($true)
#$objExcel.DisplayAlerts = $true
# Dokument schließen
$wb.Close($false)
# Excel schließen
$objExcel.Quit()
# Ressourcen freigeben
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel)
Viel Spaß damit Mister Schlangenbiss .
Weitere Anpassungen gibt's von mir nur gegen Cash.
Gruß jodel32