В цей статті ми покажемо, як отримати доступ до даних в файлах Excel безпосередньо з PowerShell. Можливості прямого доступу до даних Excel з PowerShell відкриває широкі можливості по інвентаризації та побудови різних звітів по комп’ютерам, серверів, інфраструктурі, Active Directory і т.д.

  • Доступ до даних в Excel з консолі PowerShell
  • Як отримати дані з Active Directory і зберегти їх в книзі Excel?

Звернення до Excel з PowerShell виконується через окремий Component Object Model (COM) об’єкт. Це вимагає наявність встановленого Excel на комп’ютері.

Перш, ніж показати, як звернутися до даних в осередку файлу Excel, необхідно розглянути архітектуру рівнів подання в документі Excel. На наступному малюнку показані 4 вкладених рівня в об’єктної моделі Excel:

  • Рівень додатки (Application Layer) – запущене застосування Excel;
  • Рівень книги (WorkBook Layer) – одночасно можуть бути відкриті кілька книг (документів Excel);
  • Рівень листа (WorkSheet Layer) – в кожному xlsx файлі може бути декілька листів;
  • Осередки (Range Layer) – тут можна отримати доступ до даних в конкретній комірці або діапазонe осередків.

com модель документа excel

Доступ до даних в Excel з консолі PowerShell

Розглянемо на простому прикладі як отримати доступ з PowerShell до даних в Excel файлі зі списком співробітників.

excel файл з даними, як прочитати з powershell скрипта

Спочатку потрібно запустити на комп’ютері програму Excel (application layer) через COM об’єкт:
$ExcelObj = New-Object -comobject Excel.Application

Після виконання цієї команди на комп’ютері запускається у фоновому режимі додаток Excel. Щоб зробити вікно Excel видимим, потрібно змінити властивість Видно COM об’єкта:

$ExcelObj.visible=$true

Всі властивості об’єкта Excel можна вивести так: $ExcelObj| fl

Тепер можна відкрити файл (книгу, workbook) Excel:

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:PSad_users.xlsx")

відрити excel файл за допомогою powershell

В кожному файлі Excel може бути кілька листів (worksheets). Виведемо список листів в поточній книзі Excel:

$ExcelWorkBook.Sheets| fl Name, index

Тепер можна відкрити конкретний лист (на ім’я або по індексу):

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("AD_User_List")

Поточний (активний) лист Excel можна дізнатися командою:

$ExcelWorkBook.ActiveSheet | fl Name, Index

вибрати активну книгу excel за допомогою powershell

Тепер ви можете отримати значення з осередків документа Excel. Можна використовувати різні способи адресації осередків в книзі Excel: через діапазон (Range), осередок (Cell), стовпець (Columns) або рядок (Rows). Нижче я навів різні приклади отримання даних з однієї і тієї ж осередки:

$ExcelWorkSheet.Range("B2").Text
$ExcelWorkSheet.Range("B2:B2").Text
$ExcelWorkSheet.Range("B2","B2").Text
$ExcelWorkSheet.cells.Item(2, 2).text
$ExcelWorkSheet.cells.Item(2, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(2).Text
$ExcelWorkSheet.Rows.Item(2).Columns.Item(2).Text

powershell - отримати значення осередки excel

Як отримати дані з Active Directory і зберегти їх в книзі Excel?

Розглянемо практичний приклад використання доступу до даних Excel з PowerShell. Наприклад, нам потрібно для кожного користувача в Excel файлі отримати інформацію з Active Directory. Наприклад, його телефон (атрибут telephoneNumber), відділ (department) і email адреса (mail).

# Импорт модуля Active Directory в сессию PowerShell
import-module activedirectory
# Сначала откройте книгу Excel:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:PSad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("AD_User_List")
# Получаем количество заполненных строк в xlsx файле
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Перебираем все строки в столбце 1, начиная со второй строки (в этих ячейках указано доменное имя пользователя)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Получаем значение атрибутов пользователя в AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
#Заполняем ячейки данными из AD
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
#Сохраните xls файл и закройте Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

В результаті в Excel файлі для кожного користувача були додані стовпці з інформацією з AD.

powershell скрипт для отримання даних користувачів з Active Directory і збереження в Excel

Розглянемо ще один приклад побудови звіту за допомогою PowerShell і Excel. Припустимо, вам потрібно побудувати Excel звіт про стан служби Print Spooler на всіх серверах домена.

# Создать объект Excel
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Добавить рабочую книгу
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Переименовывать лист
$ExcelWorkSheet.Name="Статус сервиса spooler"
# Заполняем шапку таблицы
$ExcelWorkSheet.Cells.Item(1,1) = 'Имя сервера'
$ExcelWorkSheet.Cells.Item(1,2) = 'Имя службы'
$ExcelWorkSheet.Cells.Item(1,3) = 'Статус службы'
# Выделить шапку таблицы жирным. задать размер шрифта и ширину столбцов
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=14
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=25
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=25
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=25
# получим список всех Windows Server в домене
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# подключается к каждому компьютеру и получаем статус службы
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
#Заполняем ячейки Excel данными с сервера
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# сохраните полученный отчет и закройте Excel:
$ExcelWorkBook.SaveAs('C:psservice-report.xlsx')
$ExcelWorkBook.close($true)

Область застосування можливостей доступу з PowerShell в Excel дуже широка. Починаючи від простого побудови звітів, наприклад, з Active Directory, і закінчуючи можливістю створення PowerShell скриптів для актуалізації даних в AD з Excel.

Наприклад, ви можете доручити співробітникові відділу кадрів вести реєстр користувачів в Excel. Потім за допомогою PowerShell скрипта через Set-ADUser співробітник може автоматично оновлювати дані користувачів в AD (досить делегувати користувачеві права на зміну цих атрибутів користувачів AD і показати як запускати PS скрипт). Таким чином можна вести актуальну адресну книгу з актуальними номерами телефонами і посадами.

Leave a Reply

Your email address will not be published. Required fields are marked *