Чтение и запись данных в 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:\PS\ad_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


Записать значение в ячейку 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:\PS\ad_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:\ps\service-report.xlsx')
$ExcelWorkBook.close($true)


[/alert]

PowerShell: получить данные их XLSX файла без установки Microsoft Office

Если вы хотите или не можете установить Excel на компьютере ( например у вас отсутствуют лицензии для активации Office или в случае с Windows Server Core), вы можете использовать кроссплатформенный PowerShell модуль ImportExcel для работы с Excel документами.

Установите модуль из PowerShell Gallery:

Install-Module ImportExcel

Рассмотрим типовые операции с книгами Excel, которые можно выполнить с помощью этого модуля.

Экспорт данных в 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