Вы можете обращаться к данным в файлах 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 объект:
Чтобы создать этот COM объект, Excel должен быть установлен на компьютере.
Эта команда запустите в фоновом режиме приложение Excel. Чтобы сделать окно Excel видимым, нужно изменить свойство Visible COM объекта:
Вывести все свойства объекта Excel:
Теперь можно открыть файл Excel (книгу, workbook):
В каждом файле Excel может быть несколько листов (worksheets). Вывести список листов в текущей книге Excel:
Теперь можно открыть конкретный лист (по его имени или индексу):<
Текущий (активный) лист Excel можно узнать командой:
Для получения значения из ячейки Excel нужно указать ее номер. Теперь вы можете получить значения из ячеек документа Excel. Можно использовать различные способы адресации ячеек в книге Excel: через диапазон (Range), ячейку (Cell), столбец (Columns) или строку (Rows). Ниже показаны примеры получения данных из одной и той же ячейки:
Записать значение в ячейку Excel из PowerShell
Вы можете изменить значение любой ячейки в книге Excel. Например, вы хотите изменить должность пользователя в файле.
Получить текущее значение ячейки:
Присвойте новое значение ячейке:
$ExcelWorkSheet.cells.Item(2, 3) = 'Начальник отдела продаж'
Изменить размер шрифта и выделить новое значение жирным:
$ExcelWorkSheet.cells.Item(2, 3).Font.Bold = $true
$ExcelWorkSheet.cells.Item(2, 3).Font.size=14
Сохранить изменения и закрыть книгу Excel:
Закрыть приложение Excel:
Откройте 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).
В результате в Excel файле для каждого пользователя были добавлены столбцы с информацией из AD.
Рассмотрим еще один пример построения отчета с помощью PowerShell и Excel. Допустим, вам нужно построить Excel отчет о состоянии службы Print Spooler на всех серверах домена.
- Вы можете поручить сотруднику отдела кадров вести реестр пользователей в Excel, а затем с помощью PowerShellс скриптов создавать новых пользователей в AD (New-ADUser) или обновлять их данные (Set-ADUser). Можно делегировать этому пользователю права на изменение этих атрибутов.
- Пример скрипта PowerShell для рассылки писем из Outlook по списку пользователям из Excel файла.
Получить данные их XLSX файла без установки Microsoft Office
Если вы хотите или не можете установить Excel на компьютере ( например у вас отсутствуют лицензии для активации Office или в случае с Windows Server Core), вы можете использовать кроссплатформенный PowerShell модуль ImportExcel для работы с Excel документами.
Установите модуль из PowerShell Gallery:
Рассмотрим типовые операции с книгами Excel, которые можно выполнить с помощью этого модуля.
Экспорт данных в XLSX файл:
Изменить значение в ячейке 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
Похожее:
«Изменение размера шрифта для устройств Nokia по сравнению с изменением общего масштаба шрифта приложения на Android с использованием Configuration.fontScale»? 
Что такое файл hosts, зачем он нужен и как его исправить? 
Защитить все листы excel от изменений 
Полезные заготовки вызова утилит командной строки