Экспорт данных из оболочки Windows PowerShell в Excel

Весь последний месяц ваш сценарист жил и работал в Австралии, где постоянно восхищался творческими способностями и здравомыслием австралийцев. Несколько дней назад один австралийский друг пригласил нас вместе с женой на ужин. Он сказал, что ресторан находится так близко к гостинице, что до него можно дойти пешком.

Через сорок минут мы наконец-то добрались до красивого ресторана с видом на гавань Сиднея и на здание оперного театра, блестящее, как огромная морская раковина, выброшенная волной на разноцветный пляж. Мост через гавань, самый длинный в мире безопорный пролет, скрепляет два полуострова вместе подобно тому, как массивная скоба держит две изогнутые доски.

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

Одна из причин, по которым вашему сценаристу так понравилась эта поездка, — это совсем другой взгляд на жизнь, который я увидел у моих австралийских коллег. Их уникальный взгляд на жизнь часто противоречил моим заранее сложившимся представлениям.

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

Рассмотрим, например, задачу мониторинга. Оболочка Windows PowerShell позволяет легко получить моментальный снимок процессов, запущенных на вашем компьютере. С помощью командлета Get-Process получается удобный отсортированный набор выходных данных, как показано на рис. 1.

Увеличить

Рис. 1 Список процессов, запущенных на компьютере, полученный с помощью командлета Get-Process

С помощью командлета Get-Process во многих случаях можно получить полезные результаты: количество открытых дескрипторов, пару разных представлений расхода памяти и картину использования центрального процессора. Когда же выйдет версия оболочки Windows PowerShell 2.0, командлету Get-Process можно будет передавать параметр –computername, что позволит получать такую же информацию с удаленного компьютера. Кому со всей этой информацией может понадобиться исследовать что-то еще?

Проблема заключается в том, что эти длинные столбцы данных часто маскируют все действительно важные детали. И хотя в будущей версии Windows PowerShell 2.0 будет поддерживать параметр –computername, в настоящее время сетевому администратору не предоставлено никаких дополнительных возможностей. В результате для того, чтобы наблюдать за состоянием удаленных систем и представлять информацию в пригодном для использования виде, приходится использовать инструментарий WMI, в частности, класс Win32_Process. Если вам казался достаточным объем данных, получаемых с помощью команды Get-Process, то посмотрите на выходные данных класса Win32_Process, которые показаны на рис. 2.

Увеличить

Рис. 2 Просмотр информации о процессах с помощью инструментария WMI

Что же делать бедному сетевому администратору, если все, чего он хочет — это получить удобный для понимания отчет об использовании памяти? Прежде всего — отказаться от стереотипов мышления, выйти из своей оболочки и овладеть мастерством автоматизации Excel. Почти наверняка на вашем компьютере установлен Microsoft Office Excel. Вы, как и мы, не являетесь экспертом по работе в нем, но Excel — это часть системы Microsoft Office, которую можно использовать для своих задач.

Насколько тяжело автоматизировать Excel? Как выясняется, довольно просто: корпорация Майкрософт создала модель автоматизации специально для работы с Excel. Код программы — Excel.Application, который является COM-объектом. Когда создается экземпляр объекта Excel.Application, то запускается Excel, который по умолчанию остается невидимым; тем не менее, его можно сделать видимым при помощи свойства visible.

Приведенный ниже код создает объект Excel.Application, выводит на экран значение свойства visible, а затем присваивает ему значение $true:

PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true

Проделав это, на экране появится приложение Excel довольно странного вида, напоминающее скорее пустую оболочку вместо привычного вида Excel (см. рис. 3). В нем нет ни рабочих книг, ни листов электронной таблицы — ничего, только голый Excel.

Увеличить

Рис. 3 Голый Excel — без рабочих книг и электронных таблиц

К запущенному приложению нужно добавить рабочую книгу. Воспользуемся методом Add объекта «Рабочая книга». К объекту «Рабочая книга» можно обратиться из объекта Excel.Application object, и, как видно из приведенного кода, полученный объект сохраняется в переменную $workbook:

$workbook = $excel.Workbooks.add()

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

$sheet = $workbook.worksheets.Item(1)

Теперь электронную таблицу можно заполнять данными. В электронных таблицах Excel информация хранится в ячейках. Ячейки находятся в таблице, следовательно, для доступа к необходимой ячейке нужно воспользоваться объектом, сохраненным в переменной $sheet. Для доступа к ячейкам вместо ссылок на столбец и строку применяются индексы. В приложении Excel строки обозначаются числами, а столбцы — буквами. В модели автоматизации числами обозначаются и столбцы, и строки. Первое число указывает на строку, второе — на столбец. Записать данные в ячейку можно простым присваиванием значения нужной ячейке:

$sheet.cells.item(1,1) = "Test"

После того как к Excel.Application была добавлена рабочая книга, а в ячейку помещены данные, приложение Excel выглядит так: рис. 4.

Увеличить

Рис. 4 Присвоение значения ячейке

Теперь с помощью всех этих знаний давайте попробуем сделать что-нибудь полезное. Соберем информацию о работающих процессах посредством инструментария WMI, запишем названия процессов и занимаемую ими память в электронную таблицу Excel, а затем построим диаграмму, чтобы наглядно показать использование памяти. Именно это делает скрипт WriteProcessInformationToExcel.ps1. Полный текст скрипта находится на веб-сайте журнала TechNet.

Наш скрипт начинается вызовом командлета Get-WmiObject, с помощью которого собирается информация о процессах. Для доступа к ней используется класс инструментария WMI Win32_Process, а полученные данные сохраняются в переменную $processes:

$processes = Get-WmiObject -class Win32_Process

Затем создается экземпляр объекта Excel.Application, ссылка на него сохраняется в переменной $excel, приложение Excel становится видимым и к нему добавляется рабочая книга. Обычно эти шаги выполняются при любой автоматизации Excel. Вот соответствующий код:

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()

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

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

Теперь переименуем оставшуюся таблицу. Дело в том, что если вы решите использовать объекты данных ActiveX (ADO) для запросов к таблицам Excel, то название таблицы нужно будет передавать в строке подключения. Чтобы облегчить создание интуитивно понятного и надежного кода, необходимо выбрать логически обоснованное название для электронной таблицы. Чтобы переименовать нужную таблицу, достаточно присвоить новое значение свойству name. Переименуем первую электронную таблицу в «Processes», как показано ниже:

$workbook.WorkSheets.item(1).Name = "Processes"

Теперь нужно получить ссылку на переименованную таблицу. Для этого используется метод Item объекта worksheets. В качестве параметра передается название таблицы:

$sheet = $workbook.WorkSheets.Item("Processes")

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

$x = 2

Следующие четыре строчки кода определяют четыре типа перечисления. Типы перечислений нужны для того, чтобы задать множество величин, допустимых для определенных настроек программы Excel. Например, при помощи типа перечислений xlLineStyle задается тип выводимой линии: двойная, пунктирная и так далее. Значения типов перечислений описаны в MSDN.

Чтобы облегчить чтение кода, можно создать псевдонимы для каждого из четырех используемых типов перечислений. Для этого нужно преобразовать строку, представляющую собой название типа перечисления, в тип ([type]). Вообще-то это довольно хитрый прием:

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Теперь нужно отформатировать первую строку. Зададим жирный шрифт, присвоим типу линии значение xlDashDot, воспользуемся автоматическим выбором цвета и установим среднюю ширину рамки:

For($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

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

$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

Теперь нужно поместить информацию о процессах, полученную запросом инструментария WMI и сохраненную в переменной $processes, в соответствующие ячейки таблицы. Организуем цикл foreach для обхода коллекции сведений о процессах. В переменной цикла $process будет храниться текущий элемент коллекции; из него мы поместим в первый столбец название процесса, а во второй — значение свойства workingSetSize.

И здесь нам понадобится переменная $x. Начиная со второй строки мы будем увеличивать значение переменной $x по мере продвижения по коллекции. Таким образом, в этой переменной всегда будет находиться индекс текущей строки таблицы, куда переносятся данные из коллекции. Таким образом будет обработан весь набор данных о процессах, хранящийся в переменной $processes:

Foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} #end foreach

Заполнив электронную таблицу, можно отрегулировать размер столбцов в соответствии с размером находящихся в ячейках данных. Для этого можно было бы создать диапазон, указав координаты используемых столбцов; а можно просто воспользоваться свойством электронной таблицы usedRange. Когда объект типа диапазон создан, свойство EntireColumn и метод AutoFit помогут изменить размер столбцов. Поскольку этот метод всегда возвращает какие-то данные, перенаправим его вывод в командлет Out-Null. В результате ненужная информация не будет выводиться в окно командной строки. Вот этот код:

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

Здесь можно было бы остановиться: у нас уже есть удобная электронная таблица с названиями процессов и количеством потребляемой ими памяти. Но мы сделаем еще и диаграмму. Это просто. Воспользуемся методом Add объекта диаграмма, чтобы добавить ее к рабочей книге. Поскольку этот метод тоже возвращает ненужную информацию, пропустим результат через командлет Out-Null, как показано здесь:

$workbook.charts.add() | out-null

Приведенная выше команда добавляет график. Чтобы получить диаграмму нужного типа, нужно задать значение типа перечисления, определяющего тип диаграммы. Выберем одно из значений типа перечисления microsoft.office.interop.excel.xlChartType, скажем, xl3DPieExploded. Выбор этого типа диаграммы создает трехмерную разрезанную круговую диаграмму. Это значение типа перечисления нужно присвоить свойству chartType объекта ActiveChart. В качестве источника данных для диаграммы будет установлен диапазон, определенный в переменной $range. В результате вы увидите, как появившийся график меняется на разорванную трехмерную круговую диаграмму. Вот код:

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

А теперь давайте покрутим круговую диаграмму. Свойство rotation объекта ActiveChart позволяет нам сделать это. Счетчик увеличивается в цикле до 360 с шагом 15. В окружности 360 градусов; диаграмма поворачивается на 15 секунд. Выглядит неплохо. Вот код, с помощью которого это было сделано:

For($i = 1 ; $i -le 360 ; $i +=15)
{
 $workbook.ActiveChart.rotation = $i
}

Наконец электронную таблицу нужно сохранить. Проверим наличие таблицы с помощью командлета Test-Path. Если да, то удалим старый файл через командлет Remove-Item, а потом сохраним текущую рабочую книгу туда, куда указывает переменная $strPath. Использовались объект ActiveWorkbook объекта Excel.Application и метод SaveAs рабочей книги. Если сохраненной раньше копии электронной таблицы нет, то метод SaveAs объекта ActiveWorkbook производит сохранение таблицы:

IF(Test-Path $strPath)
  {
   Remove-Item $strPath
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }
ELSE
  {
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }

После работы скрипта на экране появляется разорванная круговая диаграмма, как показано на рис. 5.

Увеличить

Рис. 5 Разорванная круговая диаграмма процессов

Сама таблица находится на закладке Processes. На рис. 6 показаны заголовки столбцов, стиль линии, который был выбран для заголовков, и сами заголовки, выделенные жирным. Выводятся данные из двух столбцов: размер рабочей области памяти и название процесса.

Увеличить

Рис. 6 Законченная электронная таблица

Можно отметить, что модель автоматизации Excel.Application предоставляет средства, которые нужны для обработки данных с ваших серверов так, чтобы воспользоваться преимуществами средств анализа и построения диаграмм.