Powershell создать файл 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.
Самый простой способ создать нового пользователя в домене Active Directory – воспользоваться графической mmc оснасткой ADUC (Active Directory Users and Computers). Но в том случае, если в домене нужно завести сразу несколько учетных записей, их создание в ручном режиме может стать довольно утомительной для администратора процедурой. В этой статье мы рассмотрим пример автоматизации создания учетных записей пользователей в AD с помощью командлета PowerShell New-ADUser.
Используем командлет New-ADUser для создания новой учетной записи AD
Командлет New-ADUser входит в состав модуля Active Directory для PowerShell. Для использования этого модуля, нужно установить на компьютере соответствующую ОС версию RSAT и включить компонент Active Directory Module for Windows PowerShell.
Для импорта модуля в сессию PowerShell выполните команду:
Полный синтаксис командлета New-ADUser можно получить с помощью команды
Get-Command New-ADUser –Syntax
В минимальной версии для создания новой учетной записи пользователя в AD достаточно указать только его имя.
New-ADUser testuser1
Как вы видите, новая учетная запись пользователя создалась в контейнере Users и является отключенной. Для использования данной учетной записи, ее нужно включить (командлет Enable-ADAccount), задать пароль (командлет Set-ADAccountPassword) и/или другие атрибуты (по необходимости).
Чтобы создать полноценную работоспособную учетную запись в определенном контейнере (OU) домена с паролем и сразу активировать ее, воспользуйтесь такой командой.
New-ADUser -Name "Test User2" -GivenName "Test" -Surname "User2" -SamAccountName "testuser2" -UserPrincipalName "[email protected]" -Path "OU=Users,OU=Accounts,OU=SPB,DC=winitpro,DC=loc" -AccountPassword(Read-Host -AsSecureString "Input Password") -Enabled $true
Команда предложит сразу указать пароль нового пользователя (в защищенном виде)
Примечание. Пароль пользователя должен соответствовать доменным политикам безопасности паролей по длине, сложности и т.д., в противном случае командлет вернет ошибку: New-ADUser : The password does not meet the length, complexity, or history requirement of the domain. Вы можете использовать готовый скрипт PowerShell для генерации уникального пароля для каждого пользователя .Информацию о созданном пользователе домена можно получить с помощью командлета Get-ADUser:
Массовое создание новых пользователей в AD из CSV скриптом PowerShell
В том случае, если в Active Directory нужно создать сразу большое количество пользователей, удобнее сохранить список пользователей в формате CSV (Excel) файла, а затем запустить специальный PowerShell скрипт. В данном файле нужно заполнить все значимые для вас атрибуты пользователей.
К примеру, мой Excel файл с пользователями состоит из 9 колонок и имеет следующий формат шапки:
Заполните данные пользователей и сохраните Exсel файл с в формате CSV c запятыми, в качестве разделителей. Кодировка файла должна быть обязательно UTF-8 (важно!). Кроме того, т.к. в значениях столбца OU есть запятые, нужно экранировать их, взяв в двойные кавычки.
Теперь можно импортировать данный CSV файл (new_ad_users2.csv) и создать в домене новых пользователей. Код готового PowerShell скрипта представлен ниже:
После выполнения скрипта, откройте консоль ADUC, разверните указанный контейнер и убедитесь, что в AD появились новые учетки пользователей (отследить создание учетных записей в AD можно так: Получение списка учетных записей AD, созданных за последние 24 часа.)
Создаваемые учетные записи можно сразу добавить в определенную группу AD с помощью командлета Add-AdGroupMember, для этого нужно немного модифицировать скрипт, добавив в цикле строку:
Add-AdGroupMember -Identity AllowPublicInet -Members $_.samAccountName
Или сразу установить фотографию пользователя в AD, чтобы она отображалась в Outlook и Lync:
Читайте также: