Excelに画像貼り付け

ImportImagesToExcel.ps1

# ==============================================================================
# 設定
# ==============================================================================
$rootFolder = Get-Location
$excelPath = Join-Path $rootFolder "Result.xlsx"

Write-Host "処理開始..."
Write-Host "読み込み元: $rootFolder"
Write-Host "出力先: $excelPath"

# ==============================================================================
# ナチュラルソート関数
# ==============================================================================
function Get-NaturalKey($name) {
    return ([regex]::Replace($name, '\d+', {
        param($m)
        $m.Value.PadLeft(10, '0')
    }))
}

# ==============================================================================
# Excel起動
# ==============================================================================
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false

$workbook = $excel.Workbooks.Add()

# シート1枚化
while ($workbook.Sheets.Count -gt 1) {
    $workbook.Sheets.Item($workbook.Sheets.Count).Delete()
}

$isFirstSheet = $true

# ==============================================================================
# メイン処理
# ==============================================================================

$sheetFolders = Get-ChildItem $rootFolder |
    Where-Object { $_.PSIsContainer } |
    Sort-Object { Get-NaturalKey $_.Name }

foreach ($sheetFolder in $sheetFolders) {

    $sheetName = $sheetFolder.Name

    if ($isFirstSheet) {
        $sheet = $workbook.Sheets.Item(1)
        $sheet.Name = $sheetName
        $isFirstSheet = $false
    } else {
        # ✅ 末尾に追加（これが重要）
        $sheet = $workbook.Sheets.Add(
            [System.Reflection.Missing]::Value,
            $workbook.Sheets.Item($workbook.Sheets.Count)
        )
        $sheet.Name = $sheetName
    }

    Write-Host "シート作成: $sheetName"

    # 見出し
    $sheet.Cells.Item(1, 1).Value = "キャプション"
    $sheet.Cells.Item(1, 2).Value = "画像"
    $sheet.Rows.Item(1).Font.Bold = $true

    # 画像配置位置
    $currentTop = $sheet.Rows.Item(2).Top

    $captionFolders = Get-ChildItem $sheetFolder.FullName |
        Where-Object { $_.PSIsContainer } |
        Sort-Object { Get-NaturalKey $_.Name }

    foreach ($captionFolder in $captionFolders) {

        $images = Get-ChildItem $captionFolder.FullName |
            Where-Object { $_.Extension -match '(?i)\.(jpg|jpeg|png|bmp)' } |
            Sort-Object { Get-NaturalKey $_.Name }

        if ($images.Count -eq 0) { continue }

        # キャプション表示
        $textbox = $sheet.Shapes.AddTextbox(
            1,
            $sheet.Columns.Item(1).Left,
            $currentTop,
            200,
            20
        )
        $textbox.TextFrame.Characters().Text = $captionFolder.Name

        foreach ($image in $images) {
            try {
                $shape = $sheet.Shapes.AddPicture(
                    $image.FullName,
                    $false,
                    $true,
                    $sheet.Columns.Item(2).Left,
                    $currentTop,
                    -1,
                    -1
                )

                $shape.LockAspectRatio = $true

                $currentTop += $shape.Height + 10

            } catch {
                Write-Warning "挿入失敗: $($image.FullName)"
            }
        }

        $currentTop += 20
    }

    $sheet.Columns.Item(1).AutoFit()
}

# ==============================================================================
# 保存
# ==============================================================================
if (Test-Path $excelPath) {
    Remove-Item $excelPath
}

$workbook.SaveAs($excelPath)

# ==============================================================================
# 終了処理
# ==============================================================================
$workbook.Close()
$excel.ScreenUpdating = $true
$excel.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

Write-Host "完了しました！" -ForegroundColor Green