PowerShell Array in Excel Datei schreiben

Hallo zusammen,

mein Skript liest aus einer bestimmten Spalte einer .xlsx Datei Benutzernamen aus und speichert diese in dem Array $User. Der Array wird anschließend an mein AD übergeben, fragt die im AD stehenden Vorgesetzten zu den Benutzern ab und speichert diese in dem Array $Manager.
Die beiden Arrays, $User und $Manager, sollen nun in jeweils eine bestimmte Spalte einer bereits vorhandenen .xlsx Datei geschrieben werden. Und genau da liegt mein Problem.

$Pfad = 'C:\Users\blumme22l\Desktop\AM\Projekt\Test\Test.xlsx' <#Pfad der lesenden Datei#>  
$Pfad2 = 'C:\Users\blumme22l\Desktop\AM\Projekt\Test\Test2.xlsx' <#Pfad der schreibenden Datei#>  

$Zeile = 2 <#Zeile welche ausgelesen werden soll#>
$Spalte = 2 <#Spalte welche ausgelesen werden soll#>
$Zeile2 = 2 <#Zeile in welche geschrieben werden soll#>
$Spalte2 = 1 <#Spalte in welche geschrieben werden soll#>
$User = @() <# Array für die User#>
$Manager = @() <# Array für die Manager der User #>
$Zaehler = 0

$Excel = New-Object -ComObject Excel.Application <#startet Excel#> 
$Excel.Visible = $false <# Excel sichtbar öffnen = Nein#>
$Workbook = $Excel.Workbooks.Open($Pfad) <#Excel Datei auswählen#>
$Worksheet = $Workbook.Worksheets.item(1) <#Tabellenblatt 1 auswählen#>

do {<#Schleife zum Auslesen der ganzen Spalte ab definierter zeile bis keine Werte mehr vorhanden sind#>                       
        
        $User += $Worksheet.Cells.Item($Zeile,$Spalte).Text <#Werte aus oben definierter Zeile und Spalte auslesen#>
        $Zeile++ <# Zeile hochzählen#>
}

while($Worksheet.Cells.Item($Zeile,$Spalte).Text.Length -gt 0)


$Excel.Quit() <#Excel beenden#>
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

$Manager = $User | Get-ADUser -Properties * -ErrorAction SilentlyContinue | select manager <# Von den Usern aus dem Array $User wird der Vorgesetzte aus dem AD abgefragt und in $Manager gespeichert. Fehler werden ignoriert.#>

<#Funktioniert bis hier#>

do {

$Excel = New-Object -ComObject Excel.Application <#startet Excel#> 
$Excel.Visible = $false <# Excel sichtbar öffnen = Nein#>
$Workbook = $Excel.Workbooks.Open($Pfad2) <#Excel Datei auswählen#>
$Worksheet = $Workbook.Worksheets.item(1) <#Tabellenblatt 1 auswählen#>

$Worksheet.Cells.Item($Zeile2,$Spalte2)=$User | ForEach-Object {$Zaehler += $_}

$Zeile++


}
while($User.Count -isnot '0')  

$Excel.Quit() <#Excel beenden#>
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Folgenden Output und Fehlermeldungen bekomme ich beim Ausführen des Skripts:

0

Der Wert "User" kann nicht in den Typ "System.Int32" konvertiert werden. Fehler: "Die Eingabezeichenfolge hat das falsche Format."  
In Zeile:40 Zeichen:65
+ ... .Cells.Item($Zeile2,$Spalte2)=$User | ForEach-Object {$Zaehler += $_}
+                                                           ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) , RuntimeException
    + FullyQualifiedErrorId : InvalidCastFromStringToInteger

Für alle in dem Array vorhandenen User

Der Wert "0" vom Typ "System.String" kann nicht in den Typ "System.Type" konvertiert werden.  
In Zeile:42 Zeichen:1
+ $Zeile++
+ ~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) , RuntimeException
    + FullyQualifiedErrorId : RuntimeException

Ich vermute also, mein Problem hat mit dem Datentyp des Arrays und mit der 0, welche zu igrend einem Zeitpunkt generiert und ausgegeben wird zu tun. Aber egal wie ich es dreh und wende, ich komm nicht auf die Lösung. Vielleicht kann mir ja jemand weiterhelfen.

VG
Lele

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 600845

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

Ausgedruckt am: 22.11.2024 um 10:11 Uhr

7 Kommentare

Neuester Kommentar

Hi,
wenn das überhaupt so geht, dass man da einfach ein Array in eine Zelle zu kippen versucht, dann muss man da trotzdem einen Datentyp haben, welchen die Zelle versteht. Also Text, Zahl, Datum oder sowas.
Versuche mal

$Worksheet.Cells.Item($Zeile2,$Spalte2)=$User.Name | ForEach-Object {$Zaehler += $_}

(beachte das ".Name")

E.

Moin Lele,

Zitat von @lele679:

Der Wert "User" kann nicht in den Typ "System.Int32" konvertiert werden. Fehler: "Die Eingabezeichenfolge hat das falsche Format."  
> In Zeile:40 Zeichen:65
> + ... .Cells.Item($Zeile2,$Spalte2)=$User | ForEach-Object {$Zaehler += $_}
> +                                                           ~~~~~~~~~~~~~~

Du definierst in Zeile 10 deines Skripts

$Zaehler = 0

Damit ist die Variable $Zaehler vom Typ Integer.
Anschließend versuchst du diese mit $Zaehler += $_ zu erweitern - geht nicht, wenn $_ keine Zahl ist.

Darüber hinaus:

$Worksheet.Cells.Item($Zeile2,$Spalte2)=$User | ForEach-Object {$Zaehler += $_}

Du setzt die Zelle B2 mit einem Array (was schon nicht geht). Da beim setzen einer Zelle keine Ausgabe erfolgt, würde niemals etwas in der dahinter folgenden Pipeline landen.

Was du tun möchtest wäre folgendes:

$User | ForEach-Object {$Zeile=2}{
  $Worksheet.Cells( $Zeile , 2 ) = $_
  $Zeile++
  }

oder alternativ

For ( $Zeile=2 ; $Zeile -lt $User.count+2 ; $Zeile++ ) { $Worksheet.Cells( $Zeile , 2 ) = $User[($Zeile-2)] } 

Die Do-While-Schleife ist zudem kompletter nonsens.

while($User.Count -isnot '0')  

1. Muss es wenn dann $User.Count -ne 0 lauten. Mit -is bzw. -isnot überprüft man nur, ob es sich um einen bestimmten Datentyp handelt.
2. Wäre die Count-Eigenschaft nie 0, wenn du die einzelnen User nicht aus dem Array entfernst - die Schleife würde also endlos laufen.
3. Öffnest du in jedem Durchlauf eine neue Excel-Instanz.

Gruß Thomas

Hi Emeriks,

mit deinem Vorschlag hat es leider auch nicht funktioniert. Ich habe die Lösung für mein Problem aber gefunden, poste ich gleich als extra Kommentar. Trotzdem vielen Dank für deine Hilfe.

VG
Lele

Hallo Thomas,

du hast Recht, meine Do-While Schleife war wirklich kompletter nonsens und das mit dem $Zaehler hätte auch nie funktioniert. Ich habe deine beiden Lösungsvorschläge bisher nicht getestet, habe mein Problem dennoch lösen können. Ich poste meine Lösung in einem extra Kommentar. Trotzdem vielen Dank für die Hilfe und deine Bemühung.

VG
Lele

Das Problem war zum einen meine DoWhile Schleife und zum anderen die Art wie ich vorhatte den Array zu durchlaufen. Mit der foreach Schleife hat es nun geklappt und ich kann jedes Objekt aus meinem Array in jeweils eine bestimmte Zelle der Tabelle schreiben.

$Pfad = 'C:\Users\blumme22l\Desktop\AM\Projekt\Test\Test.xlsx' <#Pfad der lesenden Datei#>  
$Pfad2 = 'C:\Users\blumme22l\Desktop\AM\Projekt\Test\Test2.xlsx' <#Pfad der schreibenden Datei#>  

$Zeile = 2 <#Zeile welche ausgelesen werden soll#>
$Spalte = 2 <#Spalte welche ausgelesen werden soll#>
$Zeile2 = 2 <#Zeile in welche geschrieben werden soll#>
$Spalte2 = 1 <#Spalte in welche geschrieben werden soll#>
$User = @() <# Array für die User#>
$Manager = @() <# Array für die Manager der User #>


$Excel = New-Object -ComObject Excel.Application <#startet Excel#> 
$Excel.Visible = $false <# Excel sichtbar öffnen = Nein#>
$Workbook = $Excel.Workbooks.Open($Pfad) <#Excel Datei auswählen#>
$Worksheet = $Workbook.Worksheets.item(1) <#Tabellenblatt 1 auswählen#>

do {<#Schleife zum Auslesen der ganzen Spalte ab definierter zeile bis keine Werte mehr vorhanden sind#>                       
        
        $User += $Worksheet.Cells.Item($Zeile,$Spalte).Text <#Werte aus oben definierter Zeile und Spalte auslesen#>
        $Zeile++ <# Zeile hochzählen#>
}

while($Worksheet.Cells.Item($Zeile,$Spalte).Text.Length -gt 0)


$Excel.Quit() <#Excel beenden#>
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

$Manager = $User | Get-ADUser -Properties * -ErrorAction SilentlyContinue | select manager <# Von den Usern aus dem Array $User wird der Vorgesetzte aus dem AD abgefragt und in $Manager gespeichert. Fehler werden ignoriert.#>

<#Funktioniert bis hier#>



$Excel = New-Object -ComObject Excel.Application <#startet Excel#> 
$Excel.Visible = $false <# Excel sichtbar öffnen = Nein#>
$Workbook = $Excel.Workbooks.Open($Pfad2) <#Excel Datei auswählen#>
$Worksheet = $Workbook.Worksheets.item(1) <#Tabellenblatt 1 auswählen#>



foreach ($n in $User)
{
$Worksheet.Cells.Item($Zeile2,$Spalte2)= $n
$Zeile2++
}

$workbook.Save()
$Excel.Quit() <#Excel beenden#>
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Noch eine Frage am Rande: Wieso beendest du Excel erst, um dann wieder eine neue Instanz zu öffnen?

Schließe doch einfach stattdessen die geöffnete Arbeitsmappe und arbeite mit der einen Excelinstanz weiter.

Hab ich heute beim weiterarbeiten an dem Skript auch gemerkt. Ich hab sogar nach einem Befehl die Instanz beendet um genau die gleiche nach dem nächsten Befehl wieder zu öffnen xD

gelöst Frage Entwicklung Batch, Shell

Mehr von lele679

Powershell Get-ADUser aus Arraylele679 - 8 Kommentare

Heiß diskutiert