Pull to refresh

Comments 11

Знаете ли вы, что Excel на ура открывает csv файлы как «родные», если разделителем проставлен правильный (указанный в региональных настройках: разделитель списков) символ?
И все игрища с COM-объектом становятся совершенно ненужными: точно таким же образом excel откроет правильно сформированный CSV файл,
Большое спасибо за замечание. Не знал.
Ну только не совсем на ура. Никаких проблем не будет, если разделен только текст и желательно целые числа. Если же пойдут вперемешку цифры, даты, какие-нибудь телефоны через запятую в одном столбце, то без указания конкретного типа колонки через COM OpenText, у экселя начинает немножечко ехать крыша. При этом еще для дробных чисел могут начаться пляски с бубном, а какая же у вас комбинация локали винды и локали экселя. И в итоге всё заканчивается OpenXml. Но это уже совсем другая история…
есть небольшой хак, которым пользовались: указывать при экспорте encoding: UTF8
В таком случае, в csv каждая ячейка берется в кавычки и Excel дальше более-менее нормально это воспринимает.( именно что более-менее, потому что случаются исключения)
Однако же, делая выгрузку, фактически, каждый раз нужно думать о том, на каком компьютере этот файл будет открываться — и да, в этом неудобство: иногда региональные настройки все таки разные :D и это приводит к вышеупомянутой вами дополнительной обработке или «совсем другой истории»
Более того, можно даже с export-csv не заморачиваться. В простых случаях (в каком нибудь цикле) хватает вывода в файл со знаком табуляции:
Out-File -InputObject ($var1 + "`t" + $var2) -FilePath C:\test.csv -Append

Файл откроется в MS Excel, LibreOffice, да и в powersell потом его можно обработать через import-csv
В связи с тем, что согласно нашего технического задания данные у нас очень хорошо структурированы мы можем к ним обращаться, используя стандартные запросы, одним из таких примеров может послужить справочник сотрудников, который используется на каждом предприятии.
Пффф.
Полная выгрузка учеток пользователей из леса доменов в CSV
#############################
# by Sergey S. Kovalev
# 2016-08-16
#############################
cls

# Генерируем имя файла для отчета, НУЖНО ЗАДАТЬ ПУТЬ!
$csvreport = «D:\Reporting\complete-ad-users\»+$LogDate+"_"+$domainname+".csv"

# Подключение модуля для работы с ActiveDirectory
Import-Module ActiveDirectory

# Получение списка всех доменов в лесу
$forestdomains = get-adforest

# Получаем актуальную дату
$LogDate = get-date -f yyyyMMdd

# Запускаем цикл выгрузки информации по каждому домену в лесу
foreach ($domainname in $forestdomains.domains) {

# Выводим имя домена
$domainname

# Проверяем существование файла,
$Temp = Test-Path $csvreport
If ($Temp -eq $True) {Remove-Item $csvreport} # Если существует — удаляем

$DomainDC = Get-ADDomainController -DomainName $domainname -Discover # Получаем имя первого контроллера в домене

$SearchBase = «DC=»+$domainname.Split(".")[0]+",DC="+$domainname.Split(".")[1] # Определяем корень домена для поиска объектов
$Attribute = «msds-user-account-control-computed» # Определяем статус учетой записи
$ADServer = $DomainDC.ToString()+"."+$domainname.ToString() # Опеределяем FQDN имя первого контроллера домена

# Получаем список всех пользователей домена
$AllADUsers = Get-ADUser -server $ADServer `
-searchbase $SearchBase `
-Filter * -Properties * #| Where-Object {$_.info -NE 'Migrated' -and $_.Enabled -eq 'Enabled'} # Примеры фильтра поиска

# Выводим количество пользователей в домене
$AllADUsers.count

# Для всех пользователей в домене получаем значения объектов
$AllADUsers |
Select-Object @{Label = «Display Name»;Expression = {$_.DisplayName}},
@{Label = «GivenName»;Expression = {$_.GivenName}},
@{Label = "_FirstNameOnly";Expression = {($_.GivenName).split("")[0]}},
@{Label = "_MiddleNameOnly";Expression = {($_.GivenName).split("")[1]}},
@{Label = «Surname»;Expression = {$_.Surname}},
@{Label = «initials»;Expression = {$_.initials}},
@{Label = «sAMAccountName»;Expression = {$_.sAMAccountName}},
@{Label = «Email»;Expression = {$_.Mail}},
@{Label = «msExchVersion»;Expression = {($_.msExchVersion) -replace «44220983382016»,«Exchange 2010»}},

@{Label = «Company»;Expression = {$_.Company}},
@{Label = «Department»;Expression = {$_.Department}},
@{Label = «Job Title»;Expression = {$_.Title}},
@{Label = «Description»;Expression = {$_.Description}},
@{Label = «telephoneNumber»;Expression = {$_.telephoneNumber}},

@{Label = «Mobile»;Expression = {(($_.mobile).toString())}},

@{Label = «ipPhone»;Expression = {$_.ipPhone}},
@{Label = «otherTelephone1»;Expression = {$_.otherTelephone[0]}},
@{Label = «otherTelephone2»;Expression = {$_.otherTelephone[1]}},
@{Label = «userPrincipalName»;Expression = {$_.userPrincipalName}},
@{Label = "_userDomain";Expression = {$_.userPrincipalName.replace($_.sAMAccountName+"@","")}},
@{Label = «distinguishedName»;Expression = {$_.distinguishedName}},
@{Label = "_accountPath";Expression = {$_.distinguishedName.replace(«CN=»+($_.DisplayName)+",","")}},
@{Label = «logonCount»;Expression = {$_.logonCount}},
@{Label = «employeeID»;Expression = {$_.employeeID}},

@{Label = «Account Status»;Expression = {if (($_.Enabled -eq 'TRUE') ) {'Enabled'} Else {'Disabled'}}},
@{Label = «Account Expires»;Expression = {([datetime]::FromFileTime($_.accountExpires).ToString(«u»)) -replace «1601-01-01 07:00:00Z»,""}},
@{Label = «is-PasswordNeverExpires»;Expression = {$_.passwordneverexpires}},
@{Label = «msDS-User-Account-Control-Computed»;Expression = {($_.$Attribute) -replace «8388608»,«Password expired» -replace «0»,«Normal account»}},

@{Label = «whenCreated»;Expression = {($_.whenCreated).ToString(«u»)}},
@{Label = «pwdLastSet»;Expression = {[datetime]::FromFileTime($_.pwdLastSet).ToString(«u»)}},
@{Label = «badPasswordTime»;Expression = {[datetime]::FromFileTime($_.badPasswordTime).ToString(«u»)}},
@{Label = «lastLogonTimestamp»;Expression = {[datetime]::FromFileTime($_.lastLogonTimestamp).ToString(«u»)}},
@{Label = «lastLogon»;Expression = {[datetime]::FromFileTime($_.lastLogon).ToString(«u»)}},
@{Label = «Last LogOn Date»;Expression = {($_.lastlogondate).ToString(«u»)}} |

# Пишем полученные данные в CSV файл
Export-Csv -NoClobber -Encoding utf8 -NoTypeInformation -Path $csvreport -Delimiter ";"

}
А вот кто то забыл вставить тег кода.

Листинг с тегом Code
#############################
# by Sergey S. Kovalev
# 2016-08-16
#############################
cls

# Генерируем имя файла для отчета
$csvreport = "D:\Reporting\complete-ad-users\"+$LogDate+"_"+$domainname+".csv"

# Подключение модуля для работы с ActiveDirectory
Import-Module ActiveDirectory

# Получение списка всех доменов в лесу
$forestdomains = get-adforest

# Получаем актуальную дату
$LogDate = get-date -f yyyyMMdd

# Запускаем цикл выгрузки информации по каждому домену в лесу
foreach ($domainname in $forestdomains.domains) {

# Выводим имя домена
$domainname

# Проверяем существование файла,
$Temp = Test-Path $csvreport
If ($Temp -eq $True) {Remove-Item $csvreport} # Если существует - удаляем

$DomainDC = Get-ADDomainController -DomainName $domainname -Discover # Получаем имя первого контроллера в домене

$SearchBase = "DC="+$domainname.Split(".")[0]+",DC="+$domainname.Split(".")[1] # Определяем корень домена для поиска объектов
$Attribute = "msds-user-account-control-computed" # Определяем статус учетой записи
$ADServer = $DomainDC.ToString()+"."+$domainname.ToString() # Опеределяем FQDN имя первого контроллера домена

# Получаем список всех пользователей домена
$AllADUsers = Get-ADUser -server $ADServer `
-searchbase $SearchBase `
-Filter * -Properties * #| Where-Object {$_.info -NE 'Migrated' -and $_.Enabled -eq 'Enabled'} # Примеры фильтра поиска

# Выводим количество пользователей в домене
$AllADUsers.count

# Для всех пользователей в домене получаем значения объектов
$AllADUsers |
Select-Object @{Label = "Display Name";Expression = {$_.DisplayName}},
@{Label = "GivenName";Expression = {$_.GivenName}},
@{Label = "_FirstNameOnly";Expression = {($_.GivenName).split("")[0]}},
@{Label = "_MiddleNameOnly";Expression = {($_.GivenName).split("")[1]}},
@{Label = "Surname";Expression = {$_.Surname}},
@{Label = "initials";Expression = {$_.initials}},
@{Label = "sAMAccountName";Expression = {$_.sAMAccountName}},
@{Label = "Email";Expression = {$_.Mail}},
@{Label = "msExchVersion";Expression = {($_.msExchVersion) -replace "44220983382016","Exchange 2010"}},

@{Label = "Company";Expression = {$_.Company}},
@{Label = "Department";Expression = {$_.Department}},
@{Label = "Job Title";Expression = {$_.Title}},
@{Label = "Description";Expression = {$_.Description}},
@{Label = "telephoneNumber";Expression = {$_.telephoneNumber}},

@{Label = "Mobile";Expression = {(($_.mobile).toString())}},

@{Label = "ipPhone";Expression = {$_.ipPhone}},
@{Label = "otherTelephone1";Expression = {$_.otherTelephone[0]}},
@{Label = "otherTelephone2";Expression = {$_.otherTelephone[1]}},
@{Label = "userPrincipalName";Expression = {$_.userPrincipalName}},
@{Label = "_userDomain";Expression = {$_.userPrincipalName.replace($_.sAMAccountName+"@","")}},
@{Label = "distinguishedName";Expression = {$_.distinguishedName}},
@{Label = "_accountPath";Expression = {$_.distinguishedName.replace("CN="+($_.DisplayName)+",","")}},
@{Label = "logonCount";Expression = {$_.logonCount}},
@{Label = "employeeID";Expression = {$_.employeeID}},

@{Label = "Account Status";Expression = {if (($_.Enabled -eq 'TRUE') ) {'Enabled'} Else {'Disabled'}}}, # the 'if statement# replaces $_.Enabled
@{Label = "Account Expires";Expression = {([datetime]::FromFileTime($_.accountExpires).ToString("u")) -replace "1601-01-01 07:00:00Z",""}},
@{Label = "is-PasswordNeverExpires";Expression = {$_.passwordneverexpires}},
@{Label = "msDS-User-Account-Control-Computed";Expression = {($_.$Attribute) -replace "8388608","Password expired" -replace "0","Normal account"}},

@{Label = "whenCreated";Expression = {($_.whenCreated).ToString("u")}},
@{Label = "pwdLastSet";Expression = {[datetime]::FromFileTime($_.pwdLastSet).ToString("u")}},
@{Label = "badPasswordTime";Expression = {[datetime]::FromFileTime($_.badPasswordTime).ToString("u")}},
@{Label = "lastLogonTimestamp";Expression = {[datetime]::FromFileTime($_.lastLogonTimestamp).ToString("u")}},
@{Label = "lastLogon";Expression = {[datetime]::FromFileTime($_.lastLogon).ToString("u")}},
@{Label = "Last LogOn Date";Expression = {($_.lastlogondate).ToString("u")}} |

# Пишем полученные данные в CSV файл
Export-Csv -NoClobber -Encoding utf8 -NoTypeInformation -Path $csvreport -Delimiter ";"

}

Имхо для вывода данных в Excel и работы с файлами Excel удобнее использовать модули ImportExcel или PSExcel (оба сделаны на основе .NET библиотеки EPPlus), которые можно поставить через Install-Module или найти в powershellgallery. При этом работа идет напрямую с файлом, без ComObject Excel.Application и даже парсинг или выгрузка огромных таблиц происходит за считанные секунды.
Ваш Users, к примеру выгружался бы в одну строку:
$Users | Export-XLSX -Path C:\temp\Demo.xlsx
Если хотите что бы ваши данные еще быстрее попадали в лист Excel, ознакомьтесь с вот этой статьей
Очень сильно помогает ускорить вывод «линейных» данных.
Окну редактора PowerShell теперь можно изменять размеры, сомнительное достижение, но теперь окно можно развернуть на весь экран;

Это единственное, что появилось именно в Windows10/WindowsServer2016

Все остальные нововведения относятся не к Windows а к самому PowerShell5, который доступен для установки на все версии Windows старше Windows7/WindowsServer2008R2.
Подсветка синтаксиса — заслуга модуля PSReadline, который можно установить отдельно.
У меня на WindowsServer2008R2 в Powershell5 подсветка присутствует.

Самая большая заслуга PowerShell5 — это PSGallery, репозиторий, позволяющий скачивать и выкладывать собственные скрипты и модули.

Знаете ли вы, что Excel на ура открывает csv файлы как «родные»

Для облегчения жизни при работе с excel на русской локализации windows, из Powershell в excel сохраняется так:

Export-Csv -Delimiter ';' -Encoding utf8 -NoTypeInformation -Path $home\test.csv


А CSV, сохраненный из excel открывается так:

Import-Csv -Delimiter ';' -Encoding Default -Path $home\test.csv
Очень много комментариев про саму возможность экспорта в csv и дальнейшую работу с ним в Excel.
Но речь в статье не про это, а именно про работу с COM объектами, которая действительно очень хромала.

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

Если по сценарию вы хотите сделать некий инструмент для конечного пользователя, опять таки — для HR подразделения, который по нажатию кнопки будет генерировать красивый, удобный и отформатированный excel файл в корпоративном стиле, то ускорение работы с COM объектами очень упрощает жизнь. В моем случае, пришлось отказаться от exсel и делать выгрузку в html.
Sign up to leave a comment.

Articles