Как импортировать Excel в PowerShell

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

Хотя мы можем импортировать данные Excel с помощью Import-CSV, преимущество модуля ImportExcel заключается в том, что вам не нужно конвертировать файл Excel, и у вас есть больше опций при выборе необходимых данных.

В этой статье мы рассмотрим, как импортировать файл Excel с помощью PowerShell. Мы изучим различные функции модулей, и я приведу вам несколько примеров.

Импортировать Excel в PowerShell

Чтобы импортировать файл Excel в PowerShell, сначала нам нужно установить модуль ImportExcel. Модуль доступен в PowerShell Gallery, поэтому мы можем просто установить его с помощью команды Install-Module.

Используйте команду ниже для установки модуля, имейте в виду, что вам нужно запустить PowerShell в режиме повышенных прав (администратор), чтобы установить модули, или вы можете использовать параметр scope, чтобы установить его только для текущего пользователя.

Install the module for all users
Install-Module -Name ImportExcel

Install the module for the current user only
Install-Module -Name ImportExcel -Scope CurrentUser

После того как вы установили модуль, вы можете загрузить модуль с помощью команды Import-Module. Если вы часто используете этот модуль, вы также можете добавить его в свой профиль PowerShell, чтобы он всегда загружался при открытии PowerShell.

Import-Module ImportExcel

Импортирование файла Excel

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

Прежде чем мы начнем рассматривать, как использовать модуль ImportExcel, давайте сначала ознакомимся с параметрами, которые мы можем использовать для импорта файла Excel:

Путь – Обязательный, местоположение файла Excel
ExcelPackage – Позволяет предоставить объект пакета Excel вместо пути
ИмяЛиста – Имя листа Excel, который вы хотите импортировать.
Название заголовка – Укажите пользовательские имена заголовков вместо тех, что в файле Excel
Без заголовка – Импортирует файл Excel, не используя первую строку в качестве заголовка.
ImportColumns – Укажите, какие столбцы импортировать
StartRow – Определяет строку, с которой начинается импорт, все строки выше будут проигнорированы
EndRow – Импорт останавливается на указанной строке
Стартовый столбец – Определяет, с какого столбца начинать импортирование
EndColumn – Импорт начинается с указанного столбца
Только данные – Импортировать только строки и столбцы, которые содержат данные. Пустые будут проигнорированы.
AsText – Импортирует указанные столбцы как текст
AsDate – Преобразует указанные столбцы в объект даты
Пароль - Пароль для открытия защищенного файла Excel

Когда вы импортируете файл Excel в PowerShell, по умолчанию импортируется только первый лист. Все заголовки, строки и столбцы импортируются, но при необходимости мы можем изменить это. Таким образом, чтобы просто импортировать файл Excel, вам нужно только указать путь к файлу Excel:

import-Excel -Path "C:\temp\employeeData.xlsx" | ft

Results
EmployeeID Name          Department   Salary HireDate
---------- ----          ----------   ------ --------
    101,00 John Doe      HR         60000,00 42064,00
    102,00 Jane Smith    IT         75000,00 42931,00
    103,00 Emily Jones   Finance    72000,00 43636,00
    104,00 Michael Brown Marketing  68000,00 43416,00
    105,00 Jessica White IT         79000,00 43840,00

Исправление полей дат

Как видно из результатов выше, у нас есть проблема с колонкой HireDate. Все данные импортируются как текст, что делает поле даты бесполезным. Вы можете решить эту проблему, пометив колонку даты как поле даты с параметром -AsDate:

Import-Excel -Path "C:\temp\employeeData.xlsx" -AsDate "HireDate" | ft

Results
EmployeeID Name          Department   Salary HireDate
---------- ----          ----------   ------ --------
    101,00 John Doe      HR         60000,00 1-3-2015 00:00:00
    102,00 Jane Smith    IT         75000,00 15-7-2017 00:00:00
    103,00 Emily Jones   Finance    72000,00 20-6-2019 00:00:00
    104,00 Michael Brown Marketing  68000,00 12-11-2018 00:00:00
    105,00 Jessica White IT         79000,00 10-1-2020 00:00:00

Импортировать конкретный лист

Если ваша книга Excel содержит несколько листов и вы хотите импортировать данные с другого листа вместо первого, то мы можем указать имя листа с помощью параметра -WorksheetName.

Чтобы импортировать данные о продажах из нашего примерного рабочего листа, мы можем использовать команду ниже:

Import-Excel -Path "C:\temp\employeeData.xlsx" -WorksheetName "salesdata" | ft

В этом примере мы знаем имя рабочего листа, который мы хотим импортировать. Но как узнать из командной строки, содержит ли ваш файл Excel несколько рабочих листов? И каковы имена рабочих листов? Чтобы это выяснить, мы можем использовать командлет Get-ExcelSheetInfo, который также является частью модуля ImportExcel.

Этот командлет требует только путь к файлу Excel и выведет все листы, их названия и индексные номера, а также информацию о том, видим ли лист или скрыт:

Get-ExcelSheetInfo -Path C:\temp\employeeData.xlsx

Result
Name         Index  Hidden Path
----         -----  ------ ----
EmployeeData     1 Visible C:\temp\employeeData.xlsx
SalesData        2 Visible C:\temp\employeeData.xlsx
Summary          3 Visible C:\temp\employeeData.xlsx

В этом примере каждый лист имеет разную структуру, поэтому мы не можем легко импортировать их все в один объект. Но если вы хотите импортировать каждый лист из файла Excel, вы можете просто использовать цикл ForEach, чтобы импортировать их все:

$excelSheets = Get-ExcelSheetInfo -Path "C:\temp\employeeData.xlsx"

$excelSheets | ForEach {
    Import-Excel -Path "C:\temp\employeeData.xlsx" -WorksheetName $_.Name | ft
}

Импортировать определенные строки и столбцы

Иногда вам нужна только часть данных из файла Excel. С помощью модуля ImportExcel вы можете указать, что хотите импортировать, а что нет.

Для этого мы можем использовать команды StartRow, EndRow и StartColumn, EndColumn. Таким образом, если мы хотим импортировать только имя и отдел из нашего Excel-файла и только первых трех пользователей, мы можем сделать следующее:

Import-Excel -Path "C:\temp\employeeData.xlsx" -StartRow 1 -EndRow 4 -StartColumn 2 -EndColumn 3

Results
Name        Department
----        ----------
John Doe    HR
Jane Smith  IT
Emily Jones Finance

Есть несколько вещей, на которые стоит обратить внимание. При программировании мы обычно начинаем считать с 0, но в этом случае мы начинаем с 1 для первой строки или столбца. Конечная строка или столбец, которые вы указываете, включены в результаты.

Но, как вы можете видеть, мы указали строку 4 как конечную строку. Это связано с тем, что наш файл Excel содержит заголовки, которые мы также включили.

Если вы не хотите включать заголовки, тогда нам также нужно будет использовать параметр -NoHeaders и начать с 2-й строки в этом примере.

Когда вам нужно импортировать только определенные столбцы, вы можете использовать параметр -ImportColumns и указать номера индексов (начиная с 1). Например, чтобы импортировать только первые два столбца, вы можете сделать так:

import-Excel -Path "C:\temp\employeeData.xlsx" -ImportColumns 1,2

Расширенные параметры импорта

Когда вам нужно прочитать только определенные ячейки из файла Excel, вы можете использовать командлет Open-ExcelPackage, который также является частью модуля ImportExcel. Этот командлет на самом деле не импортирует файл Excel, а просто открывает его в фоновом режиме.

Это не только позволяет вам читать определенные ячейки в вашем файле, но и позволяет вам модифицировать, например. Важно отметить, что вам необходимо закрыть файл Excel, когда вы закончите.

Итак, чтобы прочитать значение конкретной ячейки, мы сначала можем открыть файл Excel:

$excel = Open-ExcelPackage -Path "C:\temp\employeeData.xlsx"

Чтобы получить значение конкретной ячейки, нам нужно использовать имя листа и ячейки. Например, чтобы получить значение ячейки B4 из нашего примера файла:

$excel.EmployeeData.Cells["B4"].Value

Returns
Emily Jones

Когда закончите, убедитесь, что вы закрыли файл Excel с помощью команды Close-ExcelPackage.

Завершение

Модуль ImportExcel является действительно мощным модулем. В этой статье мы рассмотрели только его возможности импорта, но истинная сила на самом деле заключается в экспорте в Excel. Это то, что мы рассмотрим в совершенно другой статье, так что убедитесь, что вы подписались на рассылку или следите за мной в Facebook.

Надеюсь, вам понравилась эта статья. Если у вас есть какие-либо вопросы, просто оставьте комментарий ниже!