Заметки о Windows и других программных продуктах Microsoft...

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

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

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

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

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

уровни Excel

 

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

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

исходный файл

Application

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

$Excel = New-Object -ComObject Excel.Application

запуск приложения excel

 

Примечание. Описание класса 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 файлов.

 
 
Комментарии

Пока нет комментариев.