Чтение данных из файла Excel с помощью PowerShell

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

Для начала немного теории.

Excel представляет из себя иерархическую структуру, состоящую из нескольких уровней  (layers). Первым, верхним уровнем является приложение (Application). В приложении содержится одна или несколько книг (WorkBooks). Каждая книга состоит из одного или нескольких листов (Worksheets), а внутри каждого листа находится диапазон ячеек (Range), в которых расположены данные.

 

Соответственно для доступа к данным необходимо точно знать расположение каждого уровня\элемента и его место в общей иерархии. На первый взгляд это выглядит сложно и запутанно, но на самом деле все довольно просто.

Перейдем к практике. Для примера возьмем типичный файл со списком сотрудников и с помощью PowerShell разберем его на уровни.

Application

PowerShell не умеет напрямую работать с файлом, поэтому первое, что нам надо сделать — это запустить само приложение Excel. Для запуска приложения создаем COM-объект и помещаем его в переменную:

$Excel = New-Object -ComObject Excel.Application

 

Примечание. Описание класса Microsoft.Office.Interop.Excel.ApplicationClass на MSDN.

Если посмотреть свойства полученного объекта, то можно найти много интересного. К примеру так мы можем узнать подробные данные о версии приложения:

$Excel | fl value, version, build, path

 

а так посмотреть свойства окна:

$Excel | fl visible, windowstate

 

Кстати, свойство Visible отвечает за видимость приложения. По умолчанию Excel открывается в фоне, но можно сделать его видимым с помощью команды:

$Excel.visible = $true

Вот собственно как выглядит уровень приложения. Чистый, ничем не замутненный Excel 🙂

WorkBook

Следующим шагом будет открытие книги (workbook), т.е. файла, с которой мы будем работать. Для этого выполним команду:

$WorkBook = $Excel.Workbooks.Open("D:\Excel\users.xlsx")

 

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

$WorkBook | fl Name, Path, Author

WorkSheet

Каждая книга состоит из одного или нескольких листов (worksheets). Вывести список имеющихся листов можно командой:

$WorkBook | fl Name, Index

 

Обращаться к листу можно как по имени, так и по индексу. Выберем лист с именем «Финансы»:

$WorkSheet = $WorkBook.Sheets.Item("Финансы")

 

Посмотреть, какой лист выбран, можно командой:

$WorkBook.ActiveSheet | fl Name, Index

 

Ну а в приложении это выглядит так.

Range

Вот мы и добрались до нижнего уровня, на котором находятся данные. Данные хранятся в ячейках, и для доступа к ним нам надо выбрать нужную ячейку и извлечь ее содержимое. Для примера возьмем ячейку с именем сотрудника.

 

Каждая ячейка имеет свои координаты, по которым ее можно найти. При этом для доступа есть несколько различных способов. Например если использовать диапазон (Range), то в качестве координат указываем имя столбца и номер строки:

$Worksheet.Range("A2").Text

Чтобы не попасть на пустую ячейку, можно ограничить диапазон только занятыми ячейками (UsedRange):

$Worksheet.UsedRange.Range("A2").text

Можно обращаться к самой ячейке (cell) по номеру строки и столбца, например так:

$Worksheet.Cells.Item(2,1).text

или так:

$Worksheet.Cells.Item(2,1).Value2

Можно отдельно указать столбец (column) и строку (row):

$Worksheet.Columns.Item(1).Rows.Item(2).Text

или наоборот:

$Worksheet.Rows.Item(2).Columns.Item(1).Text

 

Ну и немного о том, как обрабатывать полученные данные. Один из способов — это создать объект типа PSObject:

$user = New-Object -TypeName PSObject

и извлеченные из ячеек данные добавлять как свойства объекта:

$user | Add-Member -Name $WorkSheet.UsedRange.Cells(1,1).Text -Value $WorkSheet.UsedRange.Cells(2,1).Text -MemberType NoteProperty
$user | Add-Member -Name $WorkSheet.UsedRange.Cells(1,2).Text -Value $WorkSheet.UsedRange.Cells(2,2).Text -MemberType NoteProperty
$user | Add-Member -Name $WorkSheet.UsedRange.Cells(1,3).Text -Value $WorkSheet.UsedRange.Cells(2,3).Text -MemberType NoteProperty

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

$user.Сотрудник

или должность:

$user.Должность

 

Ну а если необходимо извлечь все данные из книги, то можно создать массив, состоящий из объектов. К примеру, считать данные из нашего файла можно с помощью такого скрипта:

 

$file = "D:\Excel\users.xlsx";
$SheetName = "Финансы";

$Excel = New-Object -ComObject Excel.Application;
$Workbook = $Excel.workbooks.open($file);
$Worksheets = $Workbooks.worksheets;
$WorkSheet = $WorkBook.sheets.item($SheetName);

$MaxRows = ($WorkSheet.UsedRange.Rows).count;
$MaxColumns = ($WorkSheet.UsedRange.Columns).count;
$users=@();

for ($row = 2; $row -le $MaxRows; $row++) {

$user = New-Object -TypeName PSObject;

for ($col = 1; $col -lt $MaxColumns; $col++) {

$user | Add-Member -Name $WorkSheet.UsedRange.Cells(1,$col).Text -Value $WorkSheet.UsedRange.Cells($row,$col).Text -MemberType NoteProperty;

}

$users+=$user

}

$users
$Excel.Quit();

 

Запускаем скрипт и получаем вот такую картину.

 

На этом все. В следующий раз рассмотрим способы создания и редактирования Excel файлов.