PowerShell и Excel: Используем силу ImportExcel

Microsoft Excel является одним из тех всеобщих инструментов, от которого большинство из нас не может избавиться, даже если попытается. Многие IT-специалисты используют Excel в качестве небольшой базы данных, хранящей огромное количество данных в различных автоматизированных рутинах. Какой наилучший сценарий для автоматизации с помощью Excel? PowerShell и Excel!

Электронные таблицы Excel всегда славились своей сложностью скриптования и автоматизации. В отличие от его менее функционального (и простого) аналога – файла CSV, рабочие книги Excel не являются простыми текстовыми файлами. Рабочим книгам Excel требуется PowerShell для управления сложными объектами модели компонентов (COM) и, таким образом, необходимо установить Excel. Но больше нет.

К счастью, заботливый член сообщества PowerShell, Дуг Финк, создал модуль PowerShell под названием ImportExcel для нас, смертных. Модуль ImportExcel абстрагирует всю эту сложность. Он позволяет легко управлять рабочими книгами 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 для экспорта в лист Excel

Возможно, вы уже знакомы с стандартными командлетами PowerShell Export-Csv и Import-Csv. Эти командлеты позволяют считывать и экспортировать объекты PowerShell в файлы CSV. К сожалению, не существует командлетов Export-Excel и Import-Excel. Но с использованием модуля ImportExcel вы можете создать свою собственную функциональность.

Связанный материал: Управление файлами CSV в PowerShell с помощью Import-Csv

Одним из наиболее частых запросов системного администратора является экспорт объектов PowerShell в лист Excel. С использованием командлета Export-Excel в модуле ImportExcel это можно легко сделать.

Например, возможно вам нужно найти некоторые процессы, запущенные на вашем локальном компьютере и поместить их в книгу Excel.

Командлет Export-Excel принимает любой объект точно так же, как и командлет Export-Csv. Вы можете направлять любой тип объекта в этот командлет.

Чтобы найти процессы, работающие на системе с помощью PowerShell, используйте командлет Get-Process, который возвращает каждый запущенный процесс и различную информацию о каждом процессе. Чтобы экспортировать эту информацию в Excel, используйте командлет Export-Excel, указав путь к файлу Excel, который будет создан. Ниже приведен пример команды и скриншот файла Excel, сгенерированного этой командой.

Get-Process | Export-Excel -Path './processes.xlsx'

Поздравляю! Теперь вы экспортировали всю информацию, как и с помощью командлета Export-Csv, но, в отличие от командлета Export-Csv, мы можем сделать эти данные намного более интересными. Давайте убедимся, что имя листа рабочей книги называется Процессы, данные находятся в таблице, и строки автоматически подгоняются по размеру.

С помощью параметра-переключателя AutoSize для автоматического изменения размеров всех строк, TableName для указания имени таблицы, которая будет содержать все данные, и параметра имени WorksheetName Processes, вы можете увидеть на скриншоте ниже, что можно построить.

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses

У командлета Export-Excel есть множество параметров, которые можно использовать для создания рабочих книг Excel различного вида. Чтобы получить полную информацию о возможностях командлета Export-Excel, выполните команду Get-Help Export-Excel.

Использование PowerShell для импорта в Excel

Таким образом, вы экспортировали некоторую информацию в файл с именем processes.xlsx в предыдущем разделе. Возможно, теперь вам нужно переместить этот файл на другой компьютер и импортировать/прочитать эту информацию с помощью PowerShell и Excel. Нет проблем. У вас есть командлет 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

Теперь вы знаете, как прочитать всю рабочую книгу с помощью PowerShell и Excel, но что, если вам нужно только одно значение ячейки? Технически, вы могли бы использовать Import-Excel и отфильтровать значение, которое вам нужно, с помощью Where-Object, но это было бы не очень эффективно.

Вместо этого, используя командлет Open-ExcelPackage, вы можете “преобразовать” книгу Excel в объект PowerShell, который затем можно прочитать и изменить. Чтобы найти значение ячейки, сначала откройте книгу Excel для загрузки в память.

$excel = Open-ExcelPackage -Path './processes.xlsx'

Командлет Open-ExcelPackage аналогичен использованию New-Object -comobject excel.application, если вы работаете непосредственно с COM-объектами.

Затем выберите лист внутри книги.

$worksheet = $excel.Workbook.Worksheets['Processes']

Этот процесс аналогичен способу открытия рабочих книг с помощью COM-объекта excel.workbooks.open.

После того, как у вас есть лист, присвоенный переменной, теперь вы можете перейти к отдельным строкам, столбцам и ячейкам. Возможно, вам нужно найти все значения ячеек в строке A1. Вам просто нужно обратиться к свойству Cells, указав индекс A1, как показано ниже.

$worksheet.Cells['A1'].Value

Вы также можете изменить значение ячеек на рабочем листе, присвоив другое значение, например $worksheet.Cells['A1'] = 'другоезначение'

После загрузки в память важно закрыть пакет Excel, используя командлет Close-ExcelPackage.

Close-ExcelPackage $excel

Преобразование рабочих листов в файлы CSV с помощью PowerShell и Excel

После того, как содержимое рабочего листа Excel представлено с помощью объектов PowerShell, “преобразование” рабочих листов Excel в CSV требует просто отправки этих объектов в командлет Export-Csv.

Связано:Export-Csv: Преобразование объектов в файлы CSV

Используя ранее созданную книгу Excel processes.xlsx, прочитайте первый рабочий лист, который помещает все данные в объекты PowerShell, а затем экспортируйте эти объекты в CSV, используя следующую команду.

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

Если вы теперь откроете полученный файл CSV, вы увидите те же данные внутри рабочего листа Processes (в данном примере).

Преобразование нескольких рабочих листов

Если у вас есть книга Excel с несколькими рабочими листами, вы также можете создать файл CSV для каждого рабочего листа. Для этого вы можете найти все листы в книге, используя командлет Get-ExcelSheetInfo. После получения имен рабочих листов, вы можете передать эти имена в параметр WorksheetName и также использовать имя листа в качестве имени файла CSV.

Ниже приведен пример кода, необходимого для использования PowerShell и Excel.

## найти каждый лист в книге
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## прочитать каждый лист и создать файл CSV с тем же именем
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

Заключение

Используя PowerShell и Excel, вы можете импортировать, экспортировать и управлять данными в книгах Excel так же, как и в CSV-файлах, не устанавливая Excel!

В этой статье вы узнали основы чтения и записи данных в книге Excel, но это только верхушка айсберга. Используя PowerShell и модуль ImportExcel, вы можете создавать диаграммы, сводные таблицы и использовать другие мощные функции Excel!