Меню Закрыть

Powershell работа с excel

Для работы с Excel в среде powershell нужно использовать COM объект.

При выполнении данного командлета произойдет запуск приложения Excel в скрытом состоянии, т.е. у нас будет висеть процесс Excel, но при этом самого приложения как такового мы не увидем. Для того чтобы увидеть работу приложения нужно обратиться к его свойству Visible и установить его в TRUE т.к. по умолчанию стоит FALSE

Далее после того как приложение открылось нужно создать книгу воспользовавшись свойством Workbooks и методом Add().

После создания книги нужно выбрать лист с которым будем работать в данной книге. Для этого воспользуемся свойством Worksheet и методом Item()передав ему номер листа с которым будем работать.

Зададим имя нашему листу для этого используем свойство Name и присвоим ему значение нового имени листа.

Так как Excel это таблица и мы работаем с ее ячейками то используем свойство Cells и метод Item() для указания с какими ячейками данного листа мы будем работать. Отсчет начинается с 1.

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

После того как книга заполнена нужными данными ее необходимо сохранить. Для этого используем метод Saveas() и передаем ему путь и имя файла для сохранения.

После этого закрываем саму книгу воспользовавшить методом Close().

После того как закрыли книгу можно завершать работу приложения Excel использую метод Quit(). Тем самым освободив память в системе.

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

Посетителей: 8408 | Просмотров: 13578 (сегодня 2) Шрифт:

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

Дома в Америке 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.

Читайте также:  Диск на котором установлен windows заблокирован

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Установка и настройка phpmyadmin

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Устройство зарядного шнура для айфона

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

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

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

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

Using PowerShell I would like to capture user input, compare the input to data in an Excel spreadsheet and write the data in corresponding cells to a variable. I am fairly new to PowerShell and can’t seem to figure this out. Example would be: A user is prompted for a Store Number, they enter "123". The input is then compared to the data in Column A. The data in the corresponding cells is captured and written to a variable, say $GoLiveDate.

Any help would be greatly appreciated.

Создан 17 май. 13 2013-05-17 18:08:40 squishy79

3 ответа

User input can be read like this:

Excel can be handled like this:

Looking up a value in one column and assigning the corresponding value from another column to a variable could be done like this:

Don’t forget to clean up after you’re done:

Создан 17 май. 13 2013-05-17 20:36:00 Ansgar Wiechers

Cannot set the Value property for PSMemberInfo object of type "System.Management.Automation.PSParameterizedProperty". At line:7 char:30 + if ( $ws.Cells.Item($i, 1). – squishy79 19 май. 13 2013-05-19 03:30:16

My mistake. The comparison operator in PowerShell is ‘-eq’, not ‘=’. Fixed. – Ansgar Wiechers 19 май. 13 2013-05-19 09:20:50

This works! I have found that reading excel files is notoriously slow. After doing some research it is evidently faster to read CSV using the Import-CSV cmdlet. I know how to call the CSV file with the referenced cmdlet. How would I take user input and pull the corresponding data from a CSV file? – squishy79 21 май. 13 2013-05-21 15:28:49

@squishy79 You should make that a new question. – Ansgar Wiechers 21 май. 13 2013-05-21 16:35:10

Kudos to @squishy79, the Import-CSV is super helpful – Daniel Flippance 21 авг. 13 2013-08-21 22:11:16

.Value gets the cell’s object, to read an actual value of the cell use: $ws.Cells.Item.Invoke($row,$col).Value2 – tsap 26 июл. 16 2016-07-26 15:44:02

in case you stumble upon @squishy79’s exception in the first comment and you are trying to modify the spreadsheet, what has worked for me has been ‘$sheet.Cells.Item($firstrow, $colCount + 1).Value2 = $newval’. And do not forget to issue a ‘$workbookObject.Save()’ command once done editing. – kellogs 17 янв. 17 2017-01-17 16:57:13

I find it preferable to use an OleDB connection to interact with Excel. It’s faster than COM interop and less error prone than import-csv. You can prepare a collection of psobjects (one psobject is one row, each property corresponding to a column) to match your desired target grid and insert it into the Excel file. Similarly, you can insert a DataTable instead of a PSObject collection, but unless you start by retrieving data from some data source, PSObject collection way is usually easier.

Here’s a function i use for writing a psobject collection to Excel:

Создан 08 янв. 14 2014-01-08 18:06:00 Yevgeniy

I found this, and Yevgeniy’s answer. I had to do a few minor changes to the above function in order for it to work. Most notably the handeling of NULL or empty valued values in the input array. Here is Yevgeniy’s code with a few minor changes:

Создан 19 окт. 17 2017-10-19 05:31:26 Ole Martin Graae

Рекомендуем к прочтению

Добавить комментарий

Ваш адрес email не будет опубликован.