Данный материал является переводом оригинальной статьи "ATA Learning : Adam Bertram : PowerShell and Excel: Seize the Power!".
Microsoft Excel - один из тех вездесущих инструментов, от которых большинство из нас не может уйти, даже если хочет. Многие ИТ-специалисты используют Excel, как небольшую базу данных, в которой хранятся тонны данных в различных процедурах автоматизации. Каков наилучший сценарий автоматизации и Excel? Это, например, PowerShell!
Работу с таблицами Excel непросто автоматизировать. В отличие от менее функционального (и более простого) аналога файла CSV, книги Excel - это не просто текстовые файлы. Для работы со сложными книгами Excel потребует от PowerShell манипуляции с Component Object Model (COM), для чего раньше нужно было установить Excel. Однако, на самом деле, это вовсе не обязательно. Например, проницательный участник сообщества PowerShell, Doug Finke, создал модуль PowerShell, названный ImportExcel. Модуль устраняет сложность работы с Excel и позволяет легко работать с книгами Excel через PowerShell сценарии!
В этой статье рассмотрим пример того, что можно сделать в PowerShell и Excel с помощью модуля ImportExcel, а также рассмотрим несколько популярных вариантов использования.
Предварительные требования
При запуске модуля ImportExcel в системе Windows отдельные зависимости не требуются. Однако, если вы работаете с MacOS, вам необходимо установить пакет mono-libgdiplus, используя команду вида:
brew install mono-libgdiplus
Примечание: Все примеры в этой статье будут построены с использованием macOS, но все они должны работать и на других платформах. При использовании macOS, не забудьте перезапустить сеанс PowerShell, прежде чем продолжить.
Установка модуля ImportExcel
Начните с загрузки и установки модуля через PowerShell Gallery, запустив:
Install-Module ImportExcel -Scope CurrentUser
Через несколько секунд все будет в порядке.
Использование PowerShell для экспорта в рабочий лист Excel
Возможно, вы знакомы со стандартными командлетами PowerShell Export-Csv и Import-Csv. Эти командлеты позволяют читать и экспортировать объекты PowerShell в файлы CSV. К сожалению, в PowerShell нет таких же встроенных командлетов для Excel. Но, используя модуль ImportExcel, вы можете создать такой функционал!
Один из наиболее частых запросов системного администратора - это экспорт объектов PowerShell в рабочий лист Excel. С помощью командлета Export-Excel из модуля ImportExcel, вы можете легко сделать это. Командлет Export-Excel принимает любой объект точно так же, как делает Export-Csv. Этому командлету можно передать любой объект.
Например, возможно, вам нужно найти какие-то процессы, запущенные на вашем локальном компьютере, и поместить их в книгу Excel.
Чтобы найти процессы, запущенные в системе с помощью PowerShell, используйте командлет Get-Process, который возвращает каждый запущенный процесс и различную информацию о каждом процессе. Чтобы экспортировать эту информацию в Excel, используйте командлет Export-Excel, указывающий путь к создаваемой книге Excel. Вы можете увидеть пример команды и снимок экрана сгенерированного файла Excel ниже.
Get-Process | Export-Excel -Path './processes.xlsx
Поздравляем! Вы экспортировали всю информацию точно так же, как Export-Csv, но, в отличие от Export-Csv, мы можем сделать эти данные намного интереснее. Убедитесь, что имя рабочего листа называется "Proccesses", данные находятся в таблице, а размер строк устанавливается автоматически.
Добавим к командлету параметр -AutoSize для автоматического изменения размера всех строк, -TableName, чтобы указать имя таблицы, которая будет включать все данные, и имя параметра -WorksheetName для процессов, и сможем увидеть на снимке экрана ниже, что в итоге получится.
Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName 'Processes' -WorksheetName 'Proccesses'
Командлет Export-Excel имеет множество параметров, которые можно использовать для создания книг Excel всех видов. Для получения полной информации о возможностях Export-Excel, запустите:
Get-Help Export-Excel
Использование PowerShell для импорта в Excel
Итак, ранее вы экспортировали некоторую информацию в файл с именем process.xlsx. Возможно, теперь вам нужно переместить этот файл на другой компьютер и импортировать / прочитать эту информацию. Командлет Import-Excel к вашим услугам.
При простейшем использовании вам нужно только указать путь к документу / книге Excel с помощью параметра -Path, как показано ниже. Вы увидите, что он читает первый рабочий лист, в данном случае рабочий лист "Processes", и возвращает объекты PowerShell.
Import-Excel -Path './processes.xlsx'
Может быть, у вас есть несколько листов в книге Excel? Вы можете прочитать конкретный рабочий лист с помощью параметра -WorkSheetname.
Import-Excel -Path './processes.xlsx' -WorkSheetname 'SecondWorksheet'
Вам нужно читать только определенные столбцы из рабочего листа Excel? Используйте параметр -HeaderName, чтобы указать только те параметры, которые вы хотите прочитать.
Import-Excel -Path './processes.xlsx' –WorkSheetname 'Processes' -HeaderName 'CPU','Handle'
Командлет Import-Excel имеет другие параметры, которые можно использовать для чтения книг Excel всех типов. Чтобы получить полное изложение всего, что может делать Import-Excel, запустите:
Get-Help Import-Excel
Использование PowerShell для получения (и установки) значений ячеек Excel
Теперь вы знаете, как читать весь лист Excel с помощью PowerShell. Но что, если вам нужно только одно значение ячейки? Технически вы можете использовать Import-Excel и отфильтровать нужное значение с помощью Where-Object, но это будет не слишком эффективно.
Вместо этого, используя командлет Open-ExcelPackage, вы можете "преобразовать" книгу Excel в объект PowerShell, который затем можно будет читать и изменять. Этот командлет аналогичен использованию New-Object -ComObject 'Excel.Application', если работать напрямую с COM-объектами.
Чтобы найти значение ячейки, сначала откройте книгу Excel, чтобы занести его в память. Затем выберите лист внутри книги.
$excel = Open-ExcelPackage -Path './processes.xlsx'
$worksheet = $excel.Workbook.Worksheets['Processes']
Этот процесс похож на способ открытия книг с помощью COM-объекта 'Excel.Workbooks.Open'.
После того, как рабочий лист назначен переменной, вы можете перейти к отдельным строкам, столбцам и ячейкам. Возможно, вам нужно найти все значения ячеек в строке A1. Вам просто нужно сослаться на свойство 'Cells', указав индекс A1, как показано ниже.
$worksheet.Cells['A1'].Value
Вы также можете изменить значение ячеек на листе, присвоив другое значение, например:
$worksheet.Cells['A1'] = 'differentvalue'
Будучи хранимым в оперативной памяти, важно высвобождать пакет Excel с помощью командлета Close-ExcelPackage.
Close-ExcelPackage $excel
Конверсия Excel в файлы CSV с помощью PowerShell
Если у вас есть содержимое листа Excel, представленное с помощью объектов PowerShell, преобразование листов Excel в CSV просто требует отправки этих объектов в командлет Export-Csv.
Используя созданную ранее книгу processes.xlsx, прочтите первый рабочий лист, который получает все данные в объекты PowerShell, а затем экспортируйте эти объекты в CSV с помощью приведенной ниже команды.
Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation
Конверсия множества рабочих листов
Если у вас есть книга Excel с несколькими листами, вы также можете создать файл CSV для каждого листа. Для этого вы можете найти все листы в книге с помощью командлета Get-ExcelSheetInfo. Когда у вас есть имена рабочих листов, вы можете передать их в параметр -WorksheetName, а также использовать имя листа в качестве имени файла CSV.
Ниже вы можете найти необходимый пример кода.
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
foreach ($sheet in $sheets) {
Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}
Заключение
Используя модуль ImportExcel из библиотеки модулей PowerShell, вы можете импортировать, экспортировать и управлять данными в книгах Excel точно так же, как и в CSV, без установки Excel!
RSS - Записи
Добавить комментарий