#requires -version 5.1
<#
.SYNOPSIS
    Active Directory のユーザーアカウント一覧を Excel (.xlsx) でデスクトップに出力します。
.DESCRIPTION
    Excel / ImportExcel モジュール / AD PowerShell モジュールは不要です。
    Windows PowerShell 5.1 と、ドメインを照会できる Windows 環境で動作します。
    C列は「姓名（姓 + 半角スペース + 名）」または「表示名」を選べます。
.EXAMPLE
    .\Export-ADUsersToExcel.ps1
.EXAMPLE
    .\Export-ADUsersToExcel.ps1 -NameMode Seimei
.EXAMPLE
    .\Export-ADUsersToExcel.ps1 -NameMode DisplayName -Server dc02.example.local
#>
[CmdletBinding()]
param(
    [ValidateSet('Seimei', 'DisplayName')]
    [string] $NameMode,

    # 接続先のドメインコントローラー。省略時は現在のドメインを利用。
    [string] $Server
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

if ([string]::IsNullOrWhiteSpace($NameMode)) {
    Write-Host ''
    Write-Host 'C列に出力する項目を選んでください。'
    Write-Host '  1：姓名（姓 + 半角スペース + 名）'
    Write-Host '  2：表示名'
    do {
        $choice = (Read-Host '番号を入力（1 または 2）').Trim()
    } until ($choice -eq '1' -or $choice -eq '2')
    $NameMode = if ($choice -eq '1') { 'Seimei' } else { 'DisplayName' }
}

# 必要な機能は Windows / .NET の標準機能のみ。
Add-Type -AssemblyName System.DirectoryServices
Add-Type -AssemblyName System.IO.Compression

function Get-LdapValue {
    param(
        [System.DirectoryServices.ResultPropertyCollection] $Properties,
        [string] $Key
    )
    $valueCollection = $Properties[$Key]
    if ($null -ne $valueCollection -and $valueCollection.Count -gt 0) {
        return [string] $valueCollection[0]
    }
    return ''
}

# ExcelのセルはXMLの文字列として格納するため、無効な制御文字だけ置換。
function Convert-ToSafeXmlText {
    param([AllowNull()][string] $Value)
    if ($null -eq $Value) { return '' }
    return [regex]::Replace($Value, '[\x00-\x08\x0B\x0C\x0E-\x1F]', ' ')
}

function Add-ZipText {
    param(
        [System.IO.Compression.ZipArchive] $Zip,
        [string] $Path,
        [string] $Content
    )
    $entry = $Zip.CreateEntry($Path, [System.IO.Compression.CompressionLevel]::Optimal)
    $stream = $entry.Open()
    $writer = [System.IO.StreamWriter]::new(
        $stream,
        [System.Text.UTF8Encoding]::new($false)
    )
    try { $writer.Write($Content) }
    finally { $writer.Dispose() }
}

function Write-TextCell {
    param(
        [System.Xml.XmlWriter] $Writer,
        [string] $SheetNamespace,
        [string] $CellAddress,
        [AllowNull()][string] $Value,
        [int] $StyleId = 0
    )
    $Writer.WriteStartElement('c', $SheetNamespace)
    $Writer.WriteAttributeString('r', $CellAddress)
    $Writer.WriteAttributeString('t', 'inlineStr')
    if ($StyleId -ne 0) {
        $Writer.WriteAttributeString('s', [string] $StyleId)
    }
    $Writer.WriteStartElement('is', $SheetNamespace)
    $Writer.WriteStartElement('t', $SheetNamespace)
    # 文字列が数式と解釈されることはありません。
    $Writer.WriteString((Convert-ToSafeXmlText $Value))
    $Writer.WriteEndElement() # t
    $Writer.WriteEndElement() # is
    $Writer.WriteEndElement() # c
}

# ドメインのユーザーを全件取得（無効アカウントも対象）。
$rootDsePath = if ($Server) { "LDAP://$Server/RootDSE" } else { 'LDAP://RootDSE' }
$rootDse = [System.DirectoryServices.DirectoryEntry]::new($rootDsePath)
$searchRoot = $null
$searcher = $null
$results = $null

try {
    $domainDn = [string] $rootDse.Properties['defaultNamingContext'].Value
    if ([string]::IsNullOrWhiteSpace($domainDn)) {
        throw 'ドメインの情報を取得できません。ADに接続できる環境で実行してください。'
    }

    $searchRootPath = if ($Server) { "LDAP://$Server/$domainDn" } else { "LDAP://$domainDn" }
    $searchRoot = [System.DirectoryServices.DirectoryEntry]::new($searchRootPath)
    $searcher = [System.DirectoryServices.DirectorySearcher]::new($searchRoot)
    $searcher.Filter = '(&(objectCategory=person)(objectClass=user)(sAMAccountName=*))'
    $searcher.SearchScope = [System.DirectoryServices.SearchScope]::Subtree
    $searcher.PageSize = 1000  # 1000件を超えるドメインにも対応
    foreach ($property in @('sAMAccountName', 'sn', 'givenName', 'displayName', 'description')) {
        [void] $searcher.PropertiesToLoad.Add($property)
    }

    Write-Host 'AD のアカウント情報を取得しています...'
    $results = $searcher.FindAll()
    $users = @(
        foreach ($result in $results) {
            $props = $result.Properties
            [pscustomobject]@{
                AccountName = Get-LdapValue $props 'samaccountname'
                Surname     = Get-LdapValue $props 'sn'
                GivenName   = Get-LdapValue $props 'givenname'
                DisplayName = Get-LdapValue $props 'displayname'
                Description = Get-LdapValue $props 'description'
            }
        }
    )
}
finally {
    if ($null -ne $results) { $results.Dispose() }
    if ($null -ne $searcher) { $searcher.Dispose() }
    if ($null -ne $searchRoot) { $searchRoot.Dispose() }
    $rootDse.Dispose()
}

$users = @($users | Sort-Object -Property AccountName)
if ($users.Count -gt 1048575) {
    throw 'Excelの1シートあたりの上限（見出しを除き1,048,575件）を超えました。'
}

$desktop = [Environment]::GetFolderPath([Environment+SpecialFolder]::DesktopDirectory)
if ([string]::IsNullOrWhiteSpace($desktop)) {
    $desktop = Join-Path $env:USERPROFILE 'Desktop'
}
if (-not (Test-Path -LiteralPath $desktop -PathType Container)) {
    [void] (New-Item -ItemType Directory -Path $desktop -Force)
}
$stamp = Get-Date -Format 'yyyyMMdd_HHmmss'
$outputPath = Join-Path $desktop "AD_Users_$stamp.xlsx"

# 追加モジュールを使わず、Office Open XML形式の正規の.xlsxを作成。
$contentTypes = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/></Types>
'@
$rootRelationships = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>
'@
$workbookXml = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="ADユーザー一覧" sheetId="1" r:id="rId1"/></sheets></workbook>
'@
$workbookRelationships = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/></Relationships>
'@
$stylesXml = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11"/><name val="Yu Gothic"/></font><font><b/><color rgb="FFFFFFFF"/><sz val="11"/><name val="Yu Gothic"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF234866"/><bgColor indexed="64"/></patternFill></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles></styleSheet>
'@

$archive = $null
$fileStream = $null
$sheetWriter = $null
$finished = $false
$sheetNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
try {
    $fileStream = [System.IO.FileStream]::new(
        $outputPath, [System.IO.FileMode]::CreateNew, [System.IO.FileAccess]::Write
    )
    $archive = [System.IO.Compression.ZipArchive]::new(
        $fileStream, [System.IO.Compression.ZipArchiveMode]::Create, $false
    )

    Add-ZipText $archive '[Content_Types].xml' $contentTypes
    Add-ZipText $archive '_rels/.rels' $rootRelationships
    Add-ZipText $archive 'xl/workbook.xml' $workbookXml
    Add-ZipText $archive 'xl/_rels/workbook.xml.rels' $workbookRelationships
    Add-ZipText $archive 'xl/styles.xml' $stylesXml

    $sheetEntry = $archive.CreateEntry(
        'xl/worksheets/sheet1.xml', [System.IO.Compression.CompressionLevel]::Optimal
    )
    $settings = [System.Xml.XmlWriterSettings]::new()
    $settings.Encoding = [System.Text.UTF8Encoding]::new($false)
    $settings.CloseOutput = $true
    $sheetWriter = [System.Xml.XmlWriter]::Create($sheetEntry.Open(), $settings)

    $sheetWriter.WriteStartDocument()
    $sheetWriter.WriteStartElement('worksheet', $sheetNs)

    # 先頭行を固定。
    $sheetWriter.WriteStartElement('sheetViews', $sheetNs)
    $sheetWriter.WriteStartElement('sheetView', $sheetNs)
    $sheetWriter.WriteAttributeString('workbookViewId', '0')
    $sheetWriter.WriteStartElement('pane', $sheetNs)
    $sheetWriter.WriteAttributeString('ySplit', '1')
    $sheetWriter.WriteAttributeString('topLeftCell', 'A2')
    $sheetWriter.WriteAttributeString('activePane', 'bottomLeft')
    $sheetWriter.WriteAttributeString('state', 'frozen')
    $sheetWriter.WriteEndElement() # pane
    $sheetWriter.WriteEndElement() # sheetView
    $sheetWriter.WriteEndElement() # sheetViews

    # 列幅: A=No. / B=アカウント / C=姓名or表示名 / D=説明。
    $sheetWriter.WriteStartElement('cols', $sheetNs)
    $colWidths = @(9, 24, 30, 65)
    for ($idx = 0; $idx -lt 4; $idx++) {
        $sheetWriter.WriteStartElement('col', $sheetNs)
        $sheetWriter.WriteAttributeString('min', [string] ($idx + 1))
        $sheetWriter.WriteAttributeString('max', [string] ($idx + 1))
        $sheetWriter.WriteAttributeString('width', [string] $colWidths[$idx])
        $sheetWriter.WriteAttributeString('customWidth', '1')
        $sheetWriter.WriteEndElement()
    }
    $sheetWriter.WriteEndElement() # cols

    $sheetWriter.WriteStartElement('sheetData', $sheetNs)
    $sheetWriter.WriteStartElement('row', $sheetNs)
    $sheetWriter.WriteAttributeString('r', '1')
    $colCHeader = if ($NameMode -eq 'Seimei') { '姓名' } else { '表示名' }
    Write-TextCell $sheetWriter $sheetNs 'A1' 'No.' 1
    Write-TextCell $sheetWriter $sheetNs 'B1' 'アカウント名' 1
    Write-TextCell $sheetWriter $sheetNs 'C1' $colCHeader 1
    Write-TextCell $sheetWriter $sheetNs 'D1' '説明' 1
    $sheetWriter.WriteEndElement() # row

    $number = 0
    foreach ($user in $users) {
        $number++
        $rowNum = $number + 1
        if ($NameMode -eq 'Seimei') {
            $parts = @()
            if (-not [string]::IsNullOrWhiteSpace($user.Surname)) {
                $parts += $user.Surname.Trim()
            }
            if (-not [string]::IsNullOrWhiteSpace($user.GivenName)) {
                $parts += $user.GivenName.Trim()
            }
            $fullName = $parts -join ' '  # 半角スペース（U+0020）
        }
        else {
            $fullName = $user.DisplayName
        }

        $sheetWriter.WriteStartElement('row', $sheetNs)
        $sheetWriter.WriteAttributeString('r', [string] $rowNum)

        # A列は連番の数値として記録。
        $sheetWriter.WriteStartElement('c', $sheetNs)
        $sheetWriter.WriteAttributeString('r', "A$rowNum")
        $sheetWriter.WriteStartElement('v', $sheetNs)
        $sheetWriter.WriteString([string] $number)
        $sheetWriter.WriteEndElement() # v
        $sheetWriter.WriteEndElement() # c

        Write-TextCell $sheetWriter $sheetNs "B$rowNum" $user.AccountName
        Write-TextCell $sheetWriter $sheetNs "C$rowNum" $fullName
        Write-TextCell $sheetWriter $sheetNs "D$rowNum" $user.Description
        $sheetWriter.WriteEndElement() # row
    }
    $sheetWriter.WriteEndElement() # sheetData

    # フィルターを追加。
    $sheetWriter.WriteStartElement('autoFilter', $sheetNs)
    $sheetWriter.WriteAttributeString('ref', "A1:D$($users.Count + 1)")
    $sheetWriter.WriteEndElement() # autoFilter
    $sheetWriter.WriteEndElement() # worksheet
    $sheetWriter.WriteEndDocument()
    $sheetWriter.Dispose()
    $sheetWriter = $null

    $finished = $true
}
finally {
    if ($null -ne $sheetWriter) { $sheetWriter.Dispose() }
    if ($null -ne $archive) { $archive.Dispose() }
    if ($null -ne $fileStream) { $fileStream.Dispose() }
    if (-not $finished -and (Test-Path -LiteralPath $outputPath)) {
        Remove-Item -LiteralPath $outputPath -Force -ErrorAction SilentlyContinue
    }
}

Write-Host ''
Write-Host "完了: $($users.Count) 件" -ForegroundColor Green
Write-Host "保存先: $outputPath" -ForegroundColor Green
Write-Host "C列: $(if ($NameMode -eq 'Seimei') { '姓名（姓 名）' } else { '表示名' })"