lele679
Aug 31, 2020, updated at 13:30:01 (UTC)
view5021
view7
0
Goto Top

PowerShell Array in Excel Datei schreiben

GermansolvedQuestionBatch, ShellDevelopment
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
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 600845

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

Printed on: May 4, 2024 at 03:05 o'clock

7 Comments
Latest comment
Goto Top
Member: emeriks
emeriks Aug 31, 2020 updated at 14:04:20 (UTC)
Goto Top
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.
Member: TK1987
TK1987 Sep 01, 2020 updated at 08:35:44 (UTC)
Goto Top
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
Member: lele679
lele679 Sep 01, 2020 at 13:46:46 (UTC)
Goto Top
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
Member: lele679
lele679 Sep 01, 2020 at 13:53:41 (UTC)
Goto Top
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
Member: lele679
lele679 Sep 01, 2020 updated at 14:26:09 (UTC)
Goto Top
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)
Member: TK1987
TK1987 Sep 01, 2020 updated at 14:41:06 (UTC)
Goto Top
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.
Member: lele679
lele679 Oct 06, 2020 at 08:56:38 (UTC)
Goto Top
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
GermansolvedQuestionBatch, ShellDevelopment
Hotly discussed
DaniEnd of availability for classic Teams clientDani - 2 CommentsFrankUbuntu 24.04 LTS Noble Numbat availableFrankadmtechDeveloper diary: Release 6.1admtech