PowerShell: Importarea datelor dintr-un fișier XLSX folosind obiectul COM al Excel

De exemplu, pentru un fișier simplu cu rol de exemplu: inventar.xlsx, din care doresc să extrag date și să le import într-un script PowerShell. Fișierul XLSX conține o listă de utilizatori fictivi în următorul format: Nume, Adresă, E-mail.

PowerShell nu poate accesa direct datele din fișierele xlsx, așa că trebuie mai întâi să creați un obiect COM cu ajutorul aplicației Excel:

$Excel = New-Object -ComObject Excel.Application

Procesul Excel rulează în fundal în mod implicit și nu este afișat pe desktop. Pentru a afișa fereastra aplicației, executați comanda:

$Excel.visible = $true

Acum, deschideți fișierul XLSX țintă:

$WorkBook = $Excel.Workbooks.Open(“C:\Inventar.xlsx”)

Importă toate valorile din fișierul Excel în PowerShell

Folosiți următoarea comandă pentru a importa rapid toate valorile dintr-un fișier Excel în PowerShell:

$myUsers = Import-Excel “C:\Inventar.xlsx”

$myUsers

Importarea datelor doar dintr-un tab, daca nu este necesar tot fisierul

$excel = Open-ExcelPackage -Path “C:\Inventar.xlsx”

$excel.Workbook.Worksheets|select Name, Index

Importarea datelor dintr-un tab specific (după nume):

Import-Excel -Path “C:\Inventar.xlsx”-WorksheetName UserInfo

Utilizați parametrii StartRow, EndRow, StartColumn și EndColumn pentru a specifica intervalul de celule din foaia de calcul pe care doriți să îl importați în PowerShell:

Import-Excel -Path “C:\Inventar.xlsx” -StartRow 1 -EndRow 4 -StartColumn 2 -EndColumn 3

Ar trebui să luați în considerare încadrarea codului de automatizare Excel într-un bloc try { } finally { } pentru a vă asigura că Excelul este închis și că obiectul COM Excel este eliberat chiar și în cazul în care apare o eroare.

Nota Bene: Dacă registrul de lucru Excel este protejat cu parolă, trebuie să introduceți parola atunci când îl deschideți cu ajutorul obiectului COM Excel. De exemplu:

$Workbook = $Excel.Workbooks.Open(
“C:\Inventar.xlsx”,
$null,
$true,
$null,
“ParolaMea”
)

Modulul ImportExcel oferă suport limitat pentru registrele de lucru protejate cu parolă. Dacă registrul de lucru este criptat, s-ar putea să fie necesar să eliminați mai întâi parola sau să utilizați în schimb automatizarea COM din Excel.

Un registru de lucru Excel poate conține mai multe foi de calcul. Pentru a afișa lista tuturor foilor de calcul dintr-un fișier, executați:

$WorkBook.Sheets | fl Name, Index

Un tab anume poate fi selectat după nume folosind această comandă:

$WorkSheet = $WorkBook.Sheets.Item(“UserInfo”)

Puteti obține valoarea unei celule anume dintr-un tab Excel:

$Worksheet.Range(“A2”).Text

Script care creează un obiect PSCustomObject pentru fiecare rând

Însă, de cele mai multe ori, doriți să citiți întregul conținut al unui registru de lucru și să importați toate datele într-un obiect PowerShell. Scriptul următor citește celulele și creează un obiect PSCustomObject pentru fiecare rând.

# Preia nr de coloane
$UsedRange = $Worksheet.UsedRange

# Preia nr de randuri din workbook
$RowCount = $UsedRange.Rows.Count

$users = @()
for ($row = 2; $row -le $RowCount; $row++) {
$name = $Worksheet.Cells.Item($row, 1).Text
$address = $Worksheet.Cells.Item($row, 2).Text
$email = $Worksheet.Cells.Item($row, 3).Text
$users += [PSCustomObject]@{
Name = $name
Address = $address
Email = $email
}
}

Astfel, prin script, importam toate rândurile din fișierul Excel în PSObject. folosind doar o singura invocare:

$users

Accesarea valorii unui element individual dintr-un obiect

Puteți accesa valoarea unui element individual din acest obiect folosind această sintaxă.

Afișați doar numele de utilizator:

$users.Name

Afișează numele celui de-al doilea utilizator din listă:

$users.Name[1]

După importarea datelor din Excel în PowerShell, puteți închide registrul de lucru și ieși din aplicație:

$Workbook.Close($false)

$Excel.Quit()

Nota Bene: Atunci când lucrați cu obiecte COM din Excel, apelarea metodei Quit() nu este întotdeauna suficientă pentru a închide complet procesul Excel. PowerShell poate păstra în continuare referințe la obiectele COM, ceea ce face ca Excel.exe să rămână în execuție în fundal. Pentru a evita apariția proceselor Excel orfane, trebuie să eliberați în mod explicit obiectele COM după închiderea registrului de lucru:

$Workbook.Close($false)
$Excel.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Worksheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null

Remove-Variable Worksheet, Workbook, Excel

[GC]::Collect()
[GC]::WaitForPendingFinalizers()

Utilizarea datelor importate din Excel într-o buclă(loop)

Odată ce datele din Excel au fost importate într-un obiect PowerShell, puteți parcurge fiecare rând și efectua sarcini administrative. De exemplu, pentru a afișa numele fiecărui utilizator, utilizați următoarea comandă:

foreach ($user in $users) {
Write-Host $user.Name
}

În practica de zi cu zi, datele importate sunt adesea utilizate pentru a efectua operațiuni în bloc. De exemplu, puteți căuta conturile AD listate în fișierul Excel:

foreach ($user in $users) {
Get-ADUser -Identity $user.Name
}

De asemenea, puteți utiliza valorile importate pentru a crea utilizatori, a actualiza atributele, a atribui apartenența la grupuri sau a efectua alte sarcini administrative automatizate.

Alte exemple practice de administrare

Puteți utiliza datele importate din Excel pentru numeroase sarcini obișnuite de administrare a AD și Microsoft 365. De exemplu, puteți actualiza atributul „Departament” pentru mai mulți utilizatori:

foreach ($user in $users) {
Set-ADUser -Identity $user.Name -Department $user.Department
}

Pentru a adăuga utilizatori într-un grup de securitate AD, executați următoarea comandă:

foreach ($user in $users) {
Add-ADGroupMember -Identity “VPN Users” -Members $user.Name
}

De asemenea, puteți utiliza datele importate pentru a automatiza sarcinile de administrare din Microsoft 365 (cum ar fi alocarea licențelor, actualizarea setărilor căsuțelor sau modificarea apartenenței la grupuri, prin combinarea obiectelor importate cu cmdlet-urile corespunzătoare din Microsoft Graph/Exchange Online PowerShell).

Lucrul cu seturi mari de date

Atunci când importați fișiere Excel de dimensiuni mari (zeci de mii de rânduri), este posibil să doriți să filtrați sau să sintetizați datele înainte de a le prelucra în continuare. De exemplu, puteți număra numărul de înregistrări importate:

$users | Measure-Object

Puteți filtra datele importate folosind comanda:

$users |
Where-Object Email -like “*.startechteam.ro”

Pentru a exporta datele importate într-un fișier CSV în vederea prelucrării ulterioare, puteti folosi comanda:

$users | Export-Csv “C:\Utilizatori.csv” -NoTypeInformation

În cazul seturilor de date foarte mari, ar trebui să luați în considerare prelucrarea numai tab-urilor și a coloanelor necesare, pentru a reduce consumul de memorie și a îmbunătăți performanța.

Pentru a elibera memoria ocupată de un obiect Excel, utilizati următoarea comandă:

Close-ExcelPackage $excel