Microsoft Excel - один из тех вездесущих инструментов, от которых большинство из нас не может убежать, даже если мы попытаемся. Многие ИТ-специалисты используют Excel как небольшую базу данных, в которой хранятся тонны данных в различных процедурах автоматизации. Каков наилучший сценарий автоматизации и Excel? PowerShell!
Таблицы Excel всегда было заведомо сложно создавать сценариями и автоматизировать. В отличие от менее функционального (и более простого) аналога файла CSV, книги Excel - это не просто текстовые файлы. Для работы со сложными книгами Excel требуется PowerShell Component Object Model (COM) objects , так как вам нужно было установить Excel. Уже нет.
К счастью, проницательный участник сообщества PowerShell, Doug Finke, создал модуль 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
Возможно, вы знакомы со стандартными командлетами PowerShell Export-Csv и Import-Csv. Эти командлеты позволяют читать и экспортировать объекты PowerShell в файлы CSV. К сожалению, нет командлетов Export-Excel и Import-Excel . Но, используя модуль ImportExcel, вы можете создать свой собственный функционал.
Один из наиболее частых запросов системного администратора - это экспорт объектов 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, мы можем сделать эти данные намного интереснее. Убедитесь, что имя рабочего листа называется Processes, данные находятся в таблице, а размер строк устанавливается автоматически.
Используя параметр переключателя 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, который затем можно будет читать и изменять. Чтобы найти значение ячейки, сначала откройте книгу 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'] = '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
Если вы сейчас откроете получившийся CSV-файл, вы увидите те же данные внутри рабочего листа «Processes» (в этом примере).
Конверсия множества рабочих листов
Если у вас есть книга Excel с несколькими листами, вы также можете создать файл CSV для каждого листа. Для этого вы можете найти все листы в книге с помощью командлета Get-ExcelSheetInfo. Когда у вас есть имена рабочих листов, вы можете передать их в параметр WorksheetName, а также использовать имя листа в качестве имени файла CSV.
Ниже вы можете найти необходимый пример кода.
## find each sheet in the workbook $sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name ## read each sheet and create a CSV file with the same name foreach ($sheet in $sheets) { Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation }
Заключение
Используя модуль ImportExcel PowerShell module, вы можете импортировать, экспортировать и управлять данными в книгах Excel точно так же, как и в CSV, без установки Excel!