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.
Об инвентаризации не писал, наверное, только ленивый. Вот и я, чтобы не казаться ленивым, тоже решил взяться за это дело. Поводом для написания стало появление нескольких статей на эту тему. Меня даже заинтересовала не сама инвентаризация (что там инвентаризировать – дёргай нужные объекты, смотри их свойства), а работа с Excel’ем, так как всё руки не доходили попробовать. С Word’ом сталкиваться уже приходилось, а вот с Excel’ем ещё нет. Можно, конечно, не заморачиваться, и вывести всё в CSV-файл, но повторюсь – меня интересовала именно работа с Excel: заполнение и форматирование ячеек, раскраска, диаграммы и т.д. Но обо всём по порядку 🙂
Итак, прежде всего нужно создать объект Excel и сделать его видимым, чтоб видеть всю дальнейшую магию 🙂
Это равносильно запуску Excel. Далее необходимо создать файл (в терминологии Excel рабочую книгу):
Далее переименовываем лист (чтобы было не Лист1, Лист2 и т.д., а “человеческие” названия) и заполняем шапку таблицы:
Как (наверное) понятно здесь мы пишем в каждую ячейку по очереди, первая цифра в скобках – номер строки, вторая – номер столбца.
Уже можно наслаждаться первыми результатами работы 🙂
Главное окно Excel
Пока смотрится криво из-за того, что надписи не влазят в ячейки, и хочется растянуть ячейки, но ничего страшного, мы это потом поправим.
Переходим на следующую строку, возвращаемся в первый столбец и в цикле заполняем таблицу данными по логическим дискам, после каждого диска переводим курсор (или как правильно назвать текущую ячейку?) на следующую строку и возвращаемся в первый столбец:
Размеры дисков переводятся в гигабайты, и чтобы много цифр не сбивали с толку, округляются до двух знаков после запятой.
Логические диски в Excel
мдя… многовато дисков, надо-бы их немножко пообъединять, создавались когда-то временно для тестовых целей, но как известно нет ничего более постоянного чем временное 🙂
Диски с нулевыми размерами это два DVD-привода и один виртуальный.
Осталось немного приукрасить внешний вид – выделим шапку таблицы (первая строка) жирным, и отрегулируем ширину ячеек по ширине текста (до этого момента я даже не подозревал, что Excel такое умеет:)):
Переменная $UsedRange содержит все занятые ячейки (эквивалентно однократному нажатию Ctrl+A)
Смотрим, что получилось:
Готовая таблица
Красота да и только 🙂
С логическими дисками разобрались, переходим к физическим.
Создадим для них отдельный лист:
Тут есть один нюанс, заключающийся в том, что листы добавляются в обратном порядке, т.е. только что добавленный лист будет иметь номер 1, а предыдущий станет номером 2. Поэтому выделяем только что созданный лист, и делаем всё то же самое, только с физическими дисками:
Смотрим, что получилось:
Логические диски
Осталось сохранить полученный отчёт и выйти из Excel:
На сегодня всё :). В следующих частях мы научимся объединять и раскрашивать ячейки, а также строить диаграммы.
Ваша оценка:
Понравилось это:
Похожее
Я использую следующий код:
Я предпочитаю собирать эксель-файлы через формирование папок с xml-данными. Ком-объекты дорогие по ресурсам, кроме того, связь с ком-объектом может упасть, если данных для передачи много.
Через PS открываю новую книгу из шаблона, и вношу в данные ячейки произвольные данные:
Application : System.__ComObject
Creator : 1480803660
Parent : System.__ComObject
_Default : =Source_Data!$B$2
Index : 1
Category :
CategoryLocal :
MacroType : -4142
Name : UterusHeight
RefersTo : =Source_Data!$B$2
ShortcutKey :
Value : =Source_Data!$B$2
Visible : True
NameLocal : UterusHeight
RefersToLocal : =Source_Data!$B$2
RefersToR1C1 : =Source_Data!R2C2
RefersToR1C1Local : =Source_Data!R2C2
RefersToRange : System.__ComObject
Comment :
WorkbookParameter : False
ValidWorkbookParameter : True
Это необходимо чтобы вносить данные из txt файла не по позициям, а по именам ячеек.
По поводу вашего вопроса попробуйте так:
P.S. спасибо за статью. 🙂
Бо Ви вставляєте не в лист (не в $Worksheet), а в певне місце ($Range)
$excel = New-Object -ComObject Excel.Application
if ( $EWS.Cells.Item($i, 3) -eq $num )
треба змінити на
if ( $EWS.Cells.Item($i, 3).text -eq $num )
Спасибо большое за решение, мне очень пригодилось, и все понятно. Мне было важно заполнять данные по столбцам сверху в низ, а через экспорт csv не получалось.
Всем привет. Написал из разных частей скрипт на создание базы данных и передачи информации в неё из файла эксель. Может кому пригодится.
Осталось пара нюансов. Может кто подскажет как в файле Excel удалить первую строку? То есть у меня шапка файла начинается со второй строки.
Продолжаем работать в Excel через Powershell. Напомню, что в предыдущей части мы создали небольшую таблицу и заполнили её данными. Также напомню, что это не моё “изобретение”, а очень вольный перевод вот этих трёх статей.
Для начала подготовим плацдарм для работы: создадим объект Excel и заполним его начальными данными. Я не буду на этом останавливаться, так как это было подробно рассмотрено в прошлой статье.
На данном этапе у нас будет одна текстовая строка, размещённая в диапазоне ячеек с A1 по G2, т.е. в двух строках и семи столбцах, что по умолчанию выглядит не очень презентабельно, так как текст выравнивается по нижнему краю:
Объединённые ячейки (неформатированные)
Чтобы текст в объединённых ячейках выглядел красивее его можно выровнять по вертикали по центру.
Все варианты вертикального выравнивания можно посмотреть в MSDN. А значения, которые нужно при этом использовать можно узнать выполнив команду:
В результате мы увидим следующую таблицу:
| Name | value__ |
| xlVAlignTop | -4160 |
| xlVAlignJustify | -4130 |
| xlVAlignDistributed | -4117 |
| xlVAlignCenter | -4108 |
| xlVAlignBottom | -4107 |
Из таблицы видно, что для выравнивания по середине нужно использовать значение
После выравнивания наш текст будет выглядеть уже красивее:
Объединённые ячейки (отформатированные)
Переходим к заполнению таблицы данными.
Для начала переходим на следующую строку. Так как в последствии вокруг таблицы мы нарисуем рамку, нам понадобится номер начальной строки, поэтому сохраним его в отдельной переменной.
Начинаем с шапки таблицы:
Сама таблица заполняется в цикле по логическим дискам:
Фильтрация нужна для того, чтобы исключить из рассмотрения CD/DVD диски (которые чаще всего пустые, и следовательно их размер будет равен нулю).
В цикле выводим в таблицу, интересующую нас информацию:
А также раскрашиваем строки в зависимости от процента свободного места на диске. Для простоты я раскрашиваю строку, относящуюся к конкретному диску в жёлтый цвет, если свободного места на нём меньше 5 ГБ, и в красный цвет, если свободного места меньше 1 ГБ:
Смотрим, что получилось:
Результаты работы (неформатированные)
Как видно на дисках C и D свободного места меньше 5 ГБ, поэтому соответствующие строки подсвечены жёлтым цветом. А на Диске F свободного места вообще меньше 1 ГБ, поэтому он подсвечен красным цветом. Кстати, для быстрого забивания диска мне пришёл на помощь скрипт, генерирующий файл заданного размера.
В целом работа выполнена. Осталось привести таблицу к боле красивому виду. Для этого мы выровняем ширину столбцов в таблице и нарисуем рамку вокруг таблицы.
Сейчас курсор стоит уже на следующей строке, так как в цикле мы его перевели находясь ещё в цикле. А так как нам нужны только строки таблицы возвращаемся на одну строку назад и выделяем таблицу:
Напомню, что $InitialRow – это номер начальный строки таблицы, который мы заранее сохранили.
Переходим к “рисованию” рамки – границы диапазона ячеек.
Чтобы узнать все возможные варианты границ диапазона ячеек можно выполнить команду:
В результате получим таблицу:
| Name | value__ |
| xlDiagonalDown | 5 |
| xlDiagonalUp | 6 |
| xlEdgeLeft | 7 |
| xlEdgeTop | 8 |
| xlEdgeBottom | 9 |
| xlEdgeRight | 10 |
| xlInsideVertical | 11 |
| xlInsideHorizontal | 12 |
Как видно из таблицы для рамки подходят значения с 7 по 12.
Подгоняем ширину столбцов:
В результате получается вот так:
Результаты работы (отформатированные)
Осталось сохранить полученный результат и выйти из Excel:
Ваша оценка:
Понравилось это:
Похожее
И еще вопрос, вот такая связка не выдает мне результат, почему? Разве это не объект?
$sheet.Rows.Item($row) | Get- Member
переменная row содержит номер строки
Затрудняюсь сказать, у меня замечательно копирует, а вторая команда выводит кучу свойств и методов.
А Excel и конкретно этот файл открыт во время выполнения проблемных команд?
Вот что ему может не нравится? Может у тебя есть мысли, ну или опыт. Да, команду удаление строки ввел потому что при вырезании он не удаляет эту строчку, а просто оставляет пустой.
В это статье мы покажем, как получить доступ к данным в файлах Excel напрямую из PowerShell. Возможности прямого обращения к данным Excel из PowerShell открывает широкие возможности по инвентаризации и построению различных отчетов по компьютерам, серверам, инфраструктуре, Active Directory и т.д.
Обращение к Excel из PowerShell выполняется через отдельный Component Object Model (COM) объект. Это требует наличие установленного Excel на компьютере.Прежде, чем показать, как обратиться к данным в ячейке файла Excel, необходимо рассмотреть архитектуру уровней представления в документе Excel. На следующем рисунке показаны 4 вложенных уровня в объектной модели Excel:
- Уровень приложения (Application Layer) – запущенное приложение Excel;
- Уровень книги (WorkBook Layer) – одновременно могут быть открыты несколько книг (документов Excel);
- Уровень листа (WorkSheet Layer) – в каждом xlsx файле может быть несколько листов;
- Ячейки (Range Layer) – здесь можно получить доступ к данным в конкретной ячейке или диапазонe ячеек.
Доступ к данным в Excel из консоли PowerShell
Рассмотрим на простом примере как получить доступ из PowerShell к данным в Excel файле со списком сотрудников.
Сначала нужно запустить на компьютере приложение Excel (application layer) через COM объект:
После выполнения этой команды на компьютере запускается в фоновом режиме приложение Excel. Чтобы сделать окно Excel видимым, нужно изменить свойство Visible COM объекта:
Все свойства объекта Excel можно вывести так: $ExcelObj| flТеперь можно открыть файл (книгу, workbook) Excel:
В каждом файле Excel может быть несколько листов (worksheets). Выведем список листов в текущей книге Excel:
$ExcelWorkBook.Sheets| fl Name, index
Теперь можно открыть конкретный лист (по имени или по индексу):
Текущий (активный) лист Excel можно узнать командой:
$ExcelWorkBook.ActiveSheet | fl Name, Index
Теперь вы можете получить значения из ячеек документа Excel. Можно использовать различные способы адресации ячеек в книге Excel: через диапазон (Range), ячейку (Cell), столбец (Columns) или строку(Rows). Ниже я привел разные примеры получения данных из одной и той же ячейки:
$ExcelWorkSheet.Range("B2").Text
$ExcelWorkSheet.Range("B2:B2").Text
$ExcelWorkSheet.Range("B2","B2").Text
$ExcelWorkSheet.cells.Item(2, 2).text
$ExcelWorkSheet.cells.Item(2, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(2).Text
$ExcelWorkSheet.Rows.Item(2).Columns.Item(2).Text
Как получить данные из Active Directory и сохранить их в книге Excel?
Рассмотрим практический пример использования доступа к данным Excel из PowerShell. Например, нам нужно для каждого пользователя в Excel файле получить информацию из Active Directory. Например, его телефон (атрибут telephoneNumber), отдел (department) и email адрес (mail).
Для получения информации об атрибутах пользователя в AD мы будем использовать командлет Get-ADUser из модуля AD PowerShell.В результате в Excel файле для каждого пользователя были добавлены столбцы с информацией из AD.
Рассмотрим еще один пример построения отчета с помощью PowerShell и Excel. Допустим, вам нужно построить Excel отчет о состоянии службы Print Spooler на всех серверах домена.
Для получения списка серверов в AD используется командлет Get-ADComputer, а для удаленной проверки статуса службы на серверах командлет WinRM Invoke-Command.Область применения возможностей доступа из PowerShell в Excel очень широка. Начиная от простого построения отчетов, например, из Active Directory, и заканчивая возможностью создания PowerShell скриптов для актуализации данных в AD из Excel.
Читайте также: