Excel
PowerShell
ActiveDirectory
ClosedXML

[PowerShell]ClosedXMLを使ってEXCELファイルからActiveDirectoryの登録を行ってみる

[PowerShell]ClosedXMLを使ってEXCELファイルからActiveDirectoryの登録を行ってみる

経緯

前回記事「[PowerShell]NuGetからパッケージをダウンロードする」の続き。
EXCELでADのユーザー情報を一括で更新する場合の手順なので、事前に前回記事「[PowerShell]NuGetからパッケージをダウンロードする」でClosedXMLDocumentFormat.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で一覧を作ることで一括で登録できるので、ある程度は大型組織改定等では楽になりそうです。