Чтение и запись данных в Excel файл из PowerShell

Вы можете обращаться к данным в файлах Excel напрямую из PowerShell. Несмотря на то, что в PowerShell есть встроенные командлеты для импорта ( Import-CSV ) и экспорта ( Export-CSV ) табличных данных в CSV файлы, для конечных пользователей формат книг Excel более прост, понятен. С помощью автоматизации PowerShell и Excel вы можете инвентаризировать и строить красивые отформатированные отчеты по вашей инфраструктуре (компьютерам, серверам, пользователям, Active Directory и т.д.)

В этой статье мы покажем, как прочитать и записать данные в таблицу Excel с помощью PowerShell.

Сначала рассмотрим архитектуру объектной модели документа Excel, которая состоит из следующих уровней представлений:

• Уровень приложения (Application Layer) – запущенное приложение Excel;
• Уровень книги (WorkBook Layer) – одновременно могут быть открыты несколько книг (файлов/документов Excel);
• Уровень листа (WorkSheet Layer) – в каждом XLSX файле может быть несколько листов;
• Ячейки (Range Layer) – позволяет обратиться к данным в конкретной ячейке или диапазонe ячеек.

Доступ к данным в файле Excel из PowerShell

Рассмотрим на простом примере как получить доступ из PowerShell к данным в Excel файле со списком сотрудников.

Запустите приложение Excel (Application layer), создав COM объект:

$ExcelObj = New-Object -comobject Excel.Application

Эта команда запустите в фоновом режиме приложение Excel. Чтобы сделать окно Excel видимым, нужно изменить свойство Visible COM объекта:

$ExcelObj.visible=$true

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

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

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

$ExcelWorkBook.Sheets| fl Name, index

Теперь можно открыть конкретный лист (по его имени или индексу):<

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

Текущий (активный) лист Excel можно узнать командой:

$ExcelWorkBook.ActiveSheet | fl Name, Index

Для получения значения из ячейки Excel нужно указать ее номер. Теперь вы можете получить значения из ячеек документа 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.Columns.Item(2).Rows.Item(2).Text
$ExcelWorkSheet.Rows.Item(2).Columns.Item(2).Text

Записать значение в ячейку Excel из PowerShell

Вы можете изменить значение любой ячейки в книге Excel. Например, вы хотите изменить должность пользователя в файле.

Получить текущее значение ячейки:

$ExcelWorkSheet.cells.Item(2, 3).text

Присвойте новое значение ячейке:

$ExcelWorkSheet.cells.Item(2, 3) = 'Начальник отдела продаж'

Изменить размер шрифта и выделить новое значение жирным:

$ExcelWorkSheet.cells.Item(2, 3).Font.Bold = $true
$ExcelWorkSheet.cells.Item(2, 3).Font.size=14

Сохранить изменения и закрыть книгу Excel:

$ExcelWorkBook.Save
$ExcelWorkBook.close($true)

Закрыть приложение Excel:

$ExcelObj.Quit()

Откройте XLSX файл и проверьте, что данные и шрифт в указанной ячейки были изменены.

Если вам нужно создать новый лист в документе Excel:

$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Add()
$ExcelWorkSheet.Name = "NewSheet"

Удалить целиком столбец или строку:

$ExcelWorkSheet.cells.Item(5, 1).EntireRow.Delete()
$ExcelWorkSheet.cells.Item(2, 1).EntireColumn.Delete()

Как получить данные из Active Directory и сохранить их в книге Excel?

Рассмотрим практический пример как можно использовать PowerShell для получения данных из Excel. Например, вы хотите получить для каждого пользователя в 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
}
#Сохранить XLSX файл и закрыть Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)
$ExcelObj.Quit()


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

Рассмотрим еще один пример построения отчета с помощью 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++
}
# Сохранить отчет в новый XLSX файл:
$ExcelWorkBook.SaveAs('C:psservice-report.xlsx')
$ExcelWorkBook.close($true)


• Вы можете поручить сотруднику отдела кадров вести реестр пользователей в Excel, а затем с помощью PowerShellс скриптов создавать новых пользователей в AD ( New-ADUser ) или обновлять их данные ( Set-ADUser ). Можно делегировать этому пользователю права на изменение этих атрибутов.
• Пример скрипта PowerShell для рассылки писем из Outlook по списку пользователям из Excel файла.