При работе с файлами 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 файлов.
Спасибо, очень полезная инфа!