[PowerShell]ClosedXMLを使ってEXCELファイルからActiveDirectoryの登録を行ってみる
経緯
前回記事「[PowerShell]NuGetからパッケージをダウンロードする」の続き。
EXCELでADのユーザー情報を一括で更新する場合の手順なので、事前に前回記事「[PowerShell]NuGetからパッケージをダウンロードする」でClosedXMLとDocumentFormat.OpenXmlをNuGetから取得しておく必要があります。
※ Excel VBAではなく、PowerShellで取り込みます。
環境
Windows 10 Pro 1703
Windows PowerShell ISE
Windows Server 2012 R2（ActiveDirectoryのサーバ）
ClosedXML -Version 0.76.0
DocumentFormat.OpenXml -Version 2.5.0
Excelファイルの形式
| 列 | 項目名 | AD項目名 |
|---|---|---|
| A | SAM アカウント | sAMAccountName |
| B | ドメイン | UPNの@の部分 |
| C | 社員番号 | employeeID |
| D | 姓 | Surname |
| E | 名 | givenName |
| F | フリガナ(姓) | msDS-PhoneticLastName |
| G | フリガナ(名) | msDS-PhoneticFirstName |
| H | 部門コード | department |
| I | 事業所 | office |
| J | 役職 | title |
| K | 表示優先度 | employeeType |
| L | 説明 | description |
| T | メールアドレス | EmailAddress |
Powershellコマンド
-
forを2から始めている理由としては、ヘッダー部を対象外にするため。 - ClosedXMLは、 0始まりではなく1始まりの模様。
Clear-Host
Import-Module ActiveDirectory
[Reflection.Assembly]::LoadFile("C:\Program Files\PackageManagement\NuGet\Packages\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll")
[Reflection.Assembly]::LoadFile("C:\Program Files\PackageManagement\NuGet\Packages\ClosedXML.0.76.0\lib\net40-client\ClosedXML.dll")
Write-Host -ForegroundColor Green "******************************************************"
Write-Host -ForegroundColor Green "*"
Write-Host -ForegroundColor Green "* AcitveDirectory ユーザーの一括登録"
Write-Host -ForegroundColor Green "*"
Write-Host -ForegroundColor Green "******************************************************"
## 変数指定
$InPath = $TsukuiData + "C:\Temp\人事通達取込用.xlsx"
## ClosedXMLのEXCEL設定
$workBook = new-object ClosedXML.Excel.XLWorkbook($InPath);
$workSheet = $workBook.Worksheet("Sheet1")
$lastRow = $workSheet.LastRowUsed().RowNumber();
# 共通
$OU = "OU=UG_個人,OU=UG_ユーザ,DC=test,DC=local"
for ($DataCount = 2; $DataCount -lt $lastRow + 1; $DataCount++)
{
$sAMAccountName = $null;
$employeeID = $null;
$domain =$null;
$AccountPassword = ConvertTo-SecureString -AsPlainText $employeeID -Force
$sAMAccountName = $workSheet.Cell("A" + $DataCount).Value.ToString().Trim();
$domain = $workSheet.Cell("B" + $DataCount).Value.ToString().Trim();
$UserPrincipalName = $SamAccountName+$domain.ToString().Trim();
$employeeID = $workSheet.Cell("C" + $DataCount).Value.ToString().Trim();
$Surname = $workSheet.Cell("D" + $DataCount).Value.ToString().Trim();
$givenName = $workSheet.Cell("E" + $DataCount).Value.ToString().Trim();
$DisplayName = $Surname + " " + $givenName.ToString().Trim();
$PhoneticLastName = $workSheet.Cell("F" + $DataCount).Value.ToString().Trim();
$PhoneticFirstName = $workSheet.Cell("G" + $DataCount).Value.ToString().Trim();
$PhoneticName = $PhoneticLastName + " " + $PhoneticFirstName.ToString().Trim();
$department = $workSheet.Cell("H" + $DataCount).Value.ToString().Trim();
$office = $workSheet.Cell("I" + $DataCount).Value.ToString().Trim();
$title = $workSheet.Cell("J" + $DataCount).Value.ToString().Trim();
$employeeType = $workSheet.Cell("K" + $DataCount).Value.ToString().Trim();
$description = $workSheet.Cell("L" + $DataCount).Value.ToString().Trim();
$EmailAddress = $workSheet.Cell("T" + $DataCount).Value.ToString().Trim();
# NULL変換
if($employeeID -eq ""){$SetADUser.EmployeeID = $null;};
if($Surname -eq ""){$SetADUser.Surname = $null;};
if($givenName -ne ""){$SetADUser.GivenName = $null;};
if($DisplayName -ne ""){$SetADUser.DisplayName = $null;};
if($PhoneticLastName -ne ""){$SetADUser.'msDS-PhoneticLastName' = $null;};
if($PhoneticFirstName -ne ""){$SetADUser.'msDS-PhoneticFirstName' = $null;};
if($PhoneticName -ne ""){$SetADUser.'msDS-PhoneticDisplayName' = $null;};
if($department -ne ""){$SetADUser.Department = $null;};
if($office -ne ""){$SetADUser.Office = $null;};
if($title -ne ""){$SetADUser.Title = $null;};
if($employeeType -ne ""){$SetADUser.employeeType = $null;};
if($description -ne ""){$SetADUser.Description = $null;};
if($EmailAddress -ne ""){$SetADUser.EmailAddress = $null;};
if($UserPrincipalName -ne ""){$SetADUser.UserPrincipalName = $null;};
# ADの検索
$SetADUser = $null
$SetADUser = Get-ADUser -Identity $sAMAccountName
if( $SetADUser -eq $null ){
# 新規作成
New-ADUser -SamAccountName $SamAccountName -Name $DisplayName -Path $OU -UserPrincipalName $UserPrincipalName -DisplayName $DisplayName -Surname $Surname -GivenName $givenName -PasswordNeverExpires $false -ChangePasswordAtLogon $false -AccountPassword $AccountPassword -Enabled $true -EmailAddress $EmailAddress -Title $title -EmployeeID $employeeID -Department $department -Office $office -Description $Description
# AD更新
Set-ADUser -Identity $SamAccountName -Add @{"msDS-PhoneticDisplayName" = $PhoneticName; "msDS-PhoneticFirstName" = $PhoneticFirstName; "msDS-PhoneticLastName" = $PhoneticLastName;}
Get-ADUser -Identity $SamAccountName
} else {
# 更新
$SetADUser.EmployeeID = $employeeID;
$SetADUser.Surname = $Surname;
$SetADUser.GivenName = $givenName;
$SetADUser.DisplayName = $DisplayName;
$SetADUser.'msDS-PhoneticLastName' = $PhoneticLastName;
$SetADUser.'msDS-PhoneticFirstName' = $PhoneticFirstName;
$SetADUser.'msDS-PhoneticDisplayName' = $PhoneticName;
$SetADUser.Department = $department;
$SetADUser.Office = $office;
$SetADUser.Title = $title;
$SetADUser.employeeType= $employeeType;
$SetADUser.Description = $description;
$SetADUser.EmailAddress = $EmailAddress;
$SetADUser.UserPrincipalName = $UserPrincipalName;
# ADUser変更
Set-ADUser -Instance $SetADUser
}
# ADUser変更情報取得
Get-ADUser -Identity $SamAccountName
}
## 終了処理
$workBook.Dispose();
$Date = Get-Date -Format "yyyy/MM/dd HH:mm:ss "
Write-Host -ForegroundColor Yellow "******************************************************"
Write-Host -ForegroundColor Yellow $Date "完了"
Write-Host -ForegroundColor Yellow "******************************************************"
AcitveDirectory管理センターで確認
- RSATをインストールしていない場合は、要インストール
まとめ
CSVであれば、もっと簡単にできますが、今回はExcelファイルを使ってというところで、ClosedXMLを使用しました。
一括登録であれば、EXCELで一覧を作ることで一括で登録できるので、ある程度は大型組織改定等では楽になりそうです。