Powershell поиск в файле 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.
I am currently working on a fairly large powershell script. However, I got stuck at one part. The issue is the following.
I have various reports with the same file name, they just have a different time stamp at the end. Within the report, I have a field displaying the date from when to when the report is from.
---> 2/1/2015 5:00:00AM to 3/1/2015 5:00:00AM <--- This is what it looks like.
This field is randomly placed on the Excel Sheet. Pretty much in the range of A5 to Z16. What I would like the script to do is:
Read the file / Check the range of cells for the dates, if the date is found and it matches my search criteria, close the sheet and move it to a different folder / If date does not match, close and check next XLS file
This is what I got so far:
1 Answer 1
If you want it to search the entire column for A to Z you would specify the range:
Then you should be able to execute a $Range.Find($SearchText) and if the text is found it will spit back the first cell it finds it in, otherwise it returns nothing. So start Excel like you did, then do a ForEach loop, and inside that open a workbook, search for your text, if it is found close it, move it, stop the loop. If it is not found close the workbook, and move to the next file. The following worked just fine for me:
I even got ambitious enough to add a progress bar in there so you can see how many files it has to potentially look at, how many it's done, and what file it's looking at right then.
Now this does all assume that you know exactly what the string is going to be (at least a partial) in that cell. If you're wrong, then it doesn't work. Checking for ambiguous things takes much longer, since you can't use Excel's matching function and have to have PowerShell check each cell in the range one at a time.
The below script reads the sheet names of an Excel document.
How could I improve it so it could extract all the contents of column B (starting from row 5 - so row 1-4 are ignored) in each worksheet and create an object?
E.g. if column B in worksheet 1 (called London) has the following values:
and column C in worksheet 2 (called) Nottingham has the following values:
I'd want to create a object that from that looks like this:
This is my code so far:
5 Answers 5
This assumes that the content is in column B on each sheet (since it's not clear how you determine the column on each sheet.) and the last row of that column is also the last row of the sheet.
3,569 2 2 gold badges 24 24 silver badges 50 50 bronze badgesSorry I know this is an old one but still felt like helping out ^_^
Maybe it's the way I read this but assuming the excel sheet 1 is called "London" and has this information; B5="Marleybone" B6="Paddington" B7="Victoria" B8="Hammersmith". And the excel sheet 2 is called "Nottingham" and has this information; C5="Alverton" C6="Annesley" C7="Arnold" C8="Askham". Then I think this code below would work. ^_^
This should be the output (without the commas):
City, Area
---- ----
London, Marleybone
London, Paddington
London, Victoria
London, Hammersmith
Nottingham, Alverton
Nottingham, Annesley
Nottingham, Arnold
Nottingham, Askham
This was extremely helpful for me when trying to automate Cisco SIP phone configuration using an Excel spreadsheet as the source. My only issue was when I tried to make an array and populate it using $array | Add-Member . as I needed to use it later on to generate the config file. Just defining an array and making it the for loop allowed it to store correctly.
I used to have no issues adding information to an array with Add-Member but that was back in PSv2/3, and I've been away from it a while. Though the simple solution saved me manually configuring 100+ phones and extensions - which nobody wants to do.
395 1 1 gold badge 4 4 silver badges 9 9 bronze badgesThere is the possibility of making something really more cool!
You can use ImportExcel which uses OfficeOpenXml
Linked
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Get-ADUser это один из основных командлетов PowerShell, который можно использовать для получения различной информации о пользователях Active Directory и их атрибутах. С помощью командлета Get-ADUser можно получить значение любого атрибута учетной записи пользователя AD, вывести список пользователей в домене с нужными атрибутами и экспортировать их в CSV, и использовать различные критерии для выборки доменных пользователей.
Комадлет Get-ADUser доступен начиная с PowerShell 2.0 и входит в специальный модуль для работы с Active Directory — Active Directory Module for Windows PowerShell (представлен в Windows Server 2008 R2). Командлеты модуля RSAT-AD-PowerShell позволяют выполнять различные операции с объектами каталога AD.
Примечание. Ранее для получения информации об атрибутах учетных записей пользователей AD приходилось использовать различные инструменты: консоль ADUC (в том числе сохраненные запросы AD), vbs скрипты, утилиту dsquery и т.п. Все эти инструменты может с лёгкостью заменить командлет Get-ADUser.В этом примере мы покажем, как с помощью командлета PowerShell Get-ADUser получить информацию о времени последней смены пароля пользователя, когда истекает срок действия пароля и другие данные пользователей.
Как найти пользователя в AD и вывести его свойства с помощью Get-ADUser?
Для использования модуля RSAT-AD-PowerShell нужно запустить консоль PowerShell с правами администратора и импортировать модуль командой:
В Windows Server 2012 и выше модуль RSAT-AD-PowerShell устанавливается по-умолчанию при развертывании на сервере роли Active Directory Domain Services (AD DS). Для установки модуля на рядовом сервере домена нужно выполнить команду:
Install-WindowsFeature -Name "RSAT-AD-PowerShell" –IncludeAllSubFeature
В десктопных версия Windows (например, в Windows 10) для работы коммандера Get-AdUser нужно установить соответствующую версию RSAT и включить в Панели Управления компонент Active Directory Module for Windows PowerShell (Remote Server Administration Tools -> Role Administration Tools -> AD DS and AD LDS Tools -> AD DS Tools).
Вы можете установить модуль AD из PowerShell:
Add-WindowsCapability –online –Name “Rsat.ActiveDirectory.DS-LDS.Tools
Также есть способ использования модуля RSAT-AD-PowerShell без его RSAT установки на компьютере. Достаточно скопировать основные файлы и импортировать модуль в сессию PoSh:Import-Module "C:\PS\ADPoSh\Microsoft.ActiveDirectory.Management.dll"
Import-Module "C:\PS\ADPoSh\Microsoft.ActiveDirectory.Management.resources.dll"
Полный список всех аргументов командлета Get-ADUser можно получить так:
Для использования командлета Get-ADUser не обязательно использовать учетную запись с правами администратора домена. Любой авторизованный пользователь домена AD может выполнять команды PowerShell для получения значений большинства атрибутов объектов AD (кроме защищенных, см. пример с LAPS). Если нужно выполнить команду Get-ADUser из-под другой учетной записи, используйте параметр Credential.Чтобы вывести список всех учетных записей домена, выполните команду:
Важно. Не рекомендуется выполнять эту команду в доменах AD с большим количеством аккаунтов, т.к. возможно перегрузка контроллера домена, предоставляющего данные.
По-умолчанию командлет Get-ADUser возвращает только 10 основных атрибутов (из более чем 120 свойств учетных записей пользователей): DistinguishedName, SamAccountName, Name, SID, UserPrincipalName, ObjectClass, статус аккаунта (Enabled: True/False согласно атрибуту UserAccountControl), и т.д.
В выводе командлета отсутствует информация о времени последней смены пароля пользователя.
Чтобы выполнить запрос на конкретном контроллере домена используется параметр – Server:Get-ADUSer –Server DC01.winitpro.loc –Identity tstuser
Чтобы вывести полную информации обо всех доступных атрибутах пользователя tuser, выполните команду:
Get-ADUser -identity tuser -Properties *
Командлет Get-ADUser с параметром Properties * вывел список всех атрибутов пользователя AD и их значения.
Попробуем изменить параметры команды Get-ADUser, чтобы вывести только нужные нам атрибуты пользователя. Можно вывести сразу несколько атрибутов пользователя:
- PasswordExpired
- PasswordLastSet
- PasswordNeverExpires
- lastlogontimestamp
Get-ADUser tuser -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires, lastlogontimestamp
Теперь в информации о пользователе есть данные о статусе аккаунта (Expired:True/False), дате смены пароля и времени последнего входа в домен (lastlogontimestamp). Представим информацию в более удобном табличном виде и уберем все лишние атрибуты с помощью Select-Object –Property или Format-Table:
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Получение пользователей из нескольких OU с помощью Get-ADUser
Чтобы вывести пользователей только из определенного контейнера домена (OU), воспользуйтесь параметром SearchBase:
Get-ADUser -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’ -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Если вам нужно выбрать пользователей сразу из нескольких OU, используйте следующую конструкцию:
$OUs = "OU=Moscow,DC=winitpro,DC=local","OU=SPB,DC=winitpro,DC=loc"
$OUs | foreach
Получить Email адреса пользователей из AD
Email пользователя это один из атрибутов в Active Directory. Чтобы вывести список email адресов пользователей, вы должны добавить поле EmailAddress в выбираемые поля командлета Get-ADUser.
Get-ADUser -filter * -properties EmailAddress -SearchBase ‘OU=MSK,DC=winitpro,DC=loc’| select-object Name, EmailAddress
Вывести список активных учёток с почтовыми адресами:
Get-ADUser -Filter <(mail -ne "null") -and (Enabled -eq "true")>-Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Format-Table
Список пользователей, у которые нет email адреса:
Get-ADUser -Filter * -Properties EmailAddress | where -Property EmailAddress -eq $null
Следующий пример позволяет выгрузить адресную книгу email адресов компании в виде csv файла, который в дальнейшем можно импортировать в Outlook или Mozilla Thunderbird:
Get-ADUser -Filter <(mail -ne "null") -and (Enabled -eq "true")>-Properties Surname,GivenName,mail | Select-Object Name,Surname,GivenName,mail | Export-Csv -NoTypeInformation -Encoding utf8 -delimiter "," $env:temp\mail_list.csv
Get-ADUser: экспорт пользователей домена в текстовый или CSV/Excel файл
Полученный список пользователей домена с атрибутами можно выгрузить в текстовый файл:
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | ft Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires > C:\temp\users.txt
Или вы можете выгрузить пользователей AD в файл CSV, который в дальнейшем будет удобно экспортировать в Excel.
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires | select Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires | Export-csv -path c:\temp\user-password-expires-2019.csv -Append -Encoding UTF8
Get-ADUser –Filter: Сортировка и фильтрация списка пользователей AD
С помощью параметра –Filter вы можете фильтровать список пользователей по одному или нескольким атрибутам. В качестве аргументов этого параметра можно указать значения определённых атрибутов пользователей Active Directory. При использовании параметра –Filter командлет Get-ADUser выведет только пользователей, которые соответствуют критериям фильтра.
Например, выведем список активных (Enabled) учётных записей пользователей, чье имя содержит «Dmitry» (в примере ниже используется множественный фильтр, вы можете комбинировать условия с помощью стандартных логических операторов сравнения PowerShell):
Get-AdUser -Filter "(Name -like '*Dmitry*') -and (Enabled -eq 'True')" -Properties * |select name,enabled
Дополнительно с помощью sort-object вы можете отсортировать полученный список пользователей по определенному атрибуту. Кроме того, для выборки пользователей можно использовать командлет where. Здесь также можно использовать сразу несколько критериев фильтрации.
Get-ADUser -filter * -properties PasswordExpired, PasswordLastSet, PasswordNeverExpires -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’| where | sort-object PasswordLastSet | select-object Name, PasswordExpired, PasswordLastSet, PasswordNeverExpires
Таким образом, можно построить таблицу с любыми необходимыми атрибутами пользователей Active Directory.
Get-ADUser: разные примеры использования
Далее приведем еще несколько полезных вариантов запросов о пользователях Active Directory с помощью различных фильтров. Вы можете их комбинировать для получения необходимого списка пользователей домена:
Вывод пользователей AD, имя которых начинается с Roman:
Чтобы подсчитать общее количество всех аккаунтов в Active Directory:
Список всех активных (не заблокированных) учетных записей в AD:
Get-ADUser -Filter | Select-Object SamAccountName,Name,Surname,GivenName | Format-Table
get-aduser -Filter * -Properties Name, WhenCreated | Select name, whenCreated
Вывести всех пользователей, которые были созданы за последние 24 часа (пример отсюда):
$lastday = ((Get-Date).AddDays(-1))
Get-ADUser -filter
Список учетных записей с истекшим сроком действия пароля (срок действия пароля настраивается в доменной политике):
Get-ADUser -filter -properties name,passwordExpired | where |select name,passwordexpired
Можно использовать командлеты Get-AdUser и Add-ADGroupMember для создания динамических групп пользователей AD (в зависимости от города, должности или департамента).Задача: для списка учетных записей, которые хранятся в текстовом файле (по одной учетной записи в строке), нужно получить телефон пользователя из AD и выгрузить информацию в текстовый csv файл (для дальнейшего формирования отчета в Exсel).
Пользователи, которые не меняли свой пароль в течении последних 90 дней:
$90_Days = (Get-Date).adddays(-90)
Get-ADUser -filter
$user = Get-ADUser winadmin -Properties thumbnailPhoto
$user.thumbnailPhoto | Set-Content winadmin.jpg -Encoding byte
Список групп, в которых состоит учетная запись пользователя
Get-AdUser winadmin -Properties memberof | Select memberof -expandproperty memberof
Вывести список пользователей из OU, которые состоят в определенной группе безопасности:
Get-ADUser -SearchBase ‘OU=Moscow,DC=winitpro,DC=loc’ -Filter * -properties memberof | Where-Object
Вывести список компьютеров домена, на которые разрешено входить пользователю (ограничение через атрибут LogonWorkstations):
Get-ADUser AIvanov -Properties LogonWorkstations | Format-List Name, LogonWorkstations
Читайте также: