В это статье мы покажем, как получить доступ к данным в файлах Excel напрямую из PowerShell. Возможности прямого обращения к данным Excel из PowerShell открывает широкие возможности по инвентаризации и построению различных отчетов по компьютерам, серверам, инфраструктуре, Active Directory и т.д.
Прежде, чем показать, как обратиться к данным в ячейке файла Excel, необходимо рассмотреть архитектуру уровней представления в документе Excel. На следующем рисунке показаны 4 вложенных уровня в объектной модели 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
$ExcelObj| fl
Теперь можно открыть файл (книгу, workbook) Excel:
$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: через диапазон (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
Как получить данные из 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:\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
}
#Сохраните xls файл и закройте Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)
В результате в 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++
}
# сохраните полученный отчет и закройте Excel:
$ExcelWorkBook.SaveAs('C:\ps\service-report.xlsx')
$ExcelWorkBook.close($true)
Область применения возможностей доступа из PowerShell в Excel очень широка. Начиная от простого построения отчетов, например, из Active Directory, и заканчивая возможностью создания PowerShell скриптов для актуализации данных в AD из Excel.
Есть PS модуль который позволяет читать и создавать эксель файлы без установленного экселя.
https://github.com/dfinke/ImportExcel
не написано как закрыть Excel в Powershell, чтобы он не остался болтаться в процессах.
Я пользуюсь так:
$workbook.Close()
$excel.Quit()
Remove-Variable sheet
Remove-Variable workbook
Remove-Variable excel
Этого достаточно?
Да, все верно. Нужно завершать процесс Excel командой:
$ExcelObj.Quit()
Добрый день. Скажите пожалуйста, как можно сделать так, чтобы результат выполнения скрипта всегда выводился на новую страницу одной книги excel?
Вместо выбора существующего листа Excel можно создать новый:
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Add()
$ExcelWorkSheet.Name = "NewSheet"
Добрый день!
1. Подскажите пожалуйста, а как закрыть файл не сохраняя изменение? Нужно только чтение, а потом выходит диалоговое окно с вопросом надоли сохранять.
2. excel висит в процессах даже после этих команд. Как его закрыть?
$workbook.Close()
$excel.Quit()
Remove-Variable sheet
Remove-Variable workbook
Remove-Variable excel
2. Stop-Process -Name EXCEL
Приветствую!
Не буду вдаваться в детали, но тема такая…
Я открываю файл, открываю лист, читаю из него данные и записываю информацию в этот же лист.
Когда я его сохраняю, он ругается на то что он уже существует и предлагает выбрать действие (Сохранить, не сохранять и отмена).
Как мне сохранять без запроса? Чтобы сохранялось принудительно.
грубо говоря, команды такие:
$file = «C:\Users\user1\Downloads\Книга1.xlsx»
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.visible=$true
$WorkBook = $ExcelObj.Workbooks.Open($file)
$AddressBook = $WorkBook.Worksheets.Item(2)
…
тут некий код
…
$AddressBook.Rows.Item(1).Font.Bold = $true
$UsedRange = $AddressBook.UsedRange
$UsedRange.EntireColumn.AutoFit() | Out-Null
$AddressBook.SaveAs($file) # как мне здесь сохранять без запроса?
$ExcelObj.Quit()
разобрался.
Надо добавить строку
$ExcelObj.DisplayAlerts = $False
И после сохранения сделать паузу в 1-2 секунды, иначе система не успеет понять что файл сохранен
sleep 2
А затем уже закрывать.
Добрый день!
Подскажите, возможно ли обращаться к уже открытой книге? Чтобы автоматически её сохранять каждые 5 минут. Книга всегда будет открыта на сервере.
Попробуйте включить многопользовательский доступ в книге excel.
Get-ChildItem -path «D:\*.txt»,»*.xlsx»,»*.docx» -Recurse |? { $_.FullName -notmatch ‘Text papka’ }| Select-String -Pattern «qazwsxedc» | Select-Object -ExpandProperty Path почему-то этот скрипт видит txt файл, но не может проверить файлы xlsx и docx, что не так, заранее спасибо
Как сделать чтобы в цикле foreach массового создания УЗ, добавлялась строка в столбец CSV Файла