Чтение и запись данных в 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 ячеек.

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

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

$ExcelObj = New-Object -comobject Excel.Application

Чтобы создать этот COM объект, Excel должен быть установлен на компьютере.

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

$ExcelObj.Visible = $true

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

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\ad_users.xlsx")

Вывести список листов в текущей книге Excel:

$ExcelWorkBook.Sheets | fl Name, Index

Откроем конкретный лист (по его имени или индексу):

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

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

$ExcelWorkBook.ActiveSheet | fl Name, Index

Теперь вы можете получить значения из ячеек документа Excel. Например, чтобы получить значение из ячейки B2:

$ExcelWorkSheet.Range("B2").Text

Либо через номер ячейки:

$ExcelWorkSheet.Cells.Item(2, 2).Text

Либо через столбец (Columns):

$ExcelWorkSheet.Columns.Item(2).Rows.Item(2).Text

Или строку (Rows):

$ExcelWorkSheet.Rows.Item(2).Columns.Item(2).Text

Вы можете изменить значение любой ячейки в книге 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()

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

Для этого используем командлет Get-ADUser из модуля Active Directory.

# Импорт модуля Active Directory в сессию PowerShell
import-module activedirectory

# Открываем книгу Excel
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("AD_User_List")

# Получаем количество заполненных строк в XLSX файле
$rowcount = $ExcelWorkSheet.UsedRange.Rows.Count

# Перебираем строки в первом столбце
for ($i = 2; $i -le $rowcount; $i++) {
$ADusername = $ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail | select name,telephoneNumber,department,mail

# Заполняем данные в Excel
$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
}

# Сохраняем изменения и закрываем Excel

$ExcelWorkBook.Save()
$ExcelWorkBook.Close($true)
$ExcelObj.Quit()

Этот скрипт получает данные о пользователях из 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++
}
# Сохранить отчет в новый XLSX файл:
$ExcelWorkBook.SaveAs('C:\ps\service-report.xlsx')
$ExcelWorkBook.close($true)

Если вы не можете установить Excel на компьютер, вы можете использовать кроссплатформенный PowerShell модуль ImportExcel для работы с Excel документами. Установите модуль:

Install-Module ImportExcel

Пример экспорта данных в XLSX файл:

Get-Process | Export-Excel -Path c:\ps\list_processes.xlsx -AutoSize -TableName 'WindowsProcesses' -WorksheetName 'Procs'

Получить данные из Excel файла (в параметре HeaderName можно указать значения каких столбцов нужно импортирововать):

$oldProcesses = Import-Excel -Path "C:\ps\ad_users.xlsx" -WorkSheetname 'AD_User_List' -HeaderName UserName, FullName

Чтобы изменить значение в ячейке Excel:

$excel = Open-ExcelPackage -Path "C:\ps\ad_users.xlsx"
$worksheet = $excel.Workbook.Worksheets['AD_User_List']

# Получаем значение
$worksheet.Cells['C3'].Value

# Изменяем значение
$worksheet.Cells['C3'].Value = 'Директор'

# Сохраняем изменения
Close-ExcelPackage $excel

С помощью PSObject можно добавить данные в таблицу Excel:

$FilePath = "C:\ps\ad_users.xlsx"
$ExcelData = Import-Excel -Path $FilePath -WorksheetName "AD_User_List"

$NewUser = [PSCustomObject]@{
"UserName" = "aivanov"
"FullName" = "Иванов Андрей"
"Job" = "Инженер"
}

$ExcelData += $NewUser
Export-Excel -Path $FilePath -WorksheetName "AD_User_List" -InputObject $ExcelData

Мы рассмотрели, как читать и записывать данные в Excel файлы с помощью PowerShell, что позволяет автоматизировать создание и обновление отчетов, а также работать с данными из Excel в бизнес-процессах.