Excel
PowerShell
エクセル
一覧

【PowerShell】エクセル拡張子のファイルとプロパティ情報を一覧化してcsvに出力する

機能

指定したフォルダ内のエクセルファイルについて、そのファイル名とプロパティ情報(読み取り推奨となっている/パスワードがかかっている等)を一覧にします。


注意点

  • 使用する際はご使用者の責任でお願いします。特に今回のコードには怪しい箇所があります。
  • 初投稿です。書き方・構成等に至らない部分があるかと思います。ご指摘いただけると幸いです。
  • PowerShellのバージョンが古い場合、Import-Csvに-EncodingパラメタがないのでGet-Content CSVファイル | CovertFrom-CSVを使う(Import-CSVコマンドレットには、-Encodingパラメタがなく、文字化けする)。

環境

  • PSVersion 5.1.16299.251
  • Windows 10 Home

手順

  1. 指定フォルダ内のexcel拡張子ファイルのフルパス(FullName)をリスト化してcsvに出力
  2. 出力したcsvを元に、各ファイルをexcelで開き、excel内の各種プロパティ情報(今回はReadOnlyRecommended,HasPassword,Author)をcsvに出力する

コード

export_filepath_list_of_excel_extention_to_csv
#$path_of_target_folderに一覧化したいフォルダを指定する
$path_of_target_folder = "C:\Users\~~~~~\Desktop\test"

#$name_of_file1に出力するcsvのファイル名を指定する
$name_of_file1 = "excel_filelist.csv"

$path_of_file1 = Join-Path $path_of_target_folder $name_of_file1

Get-ChildItem $path_of_target_folder -Recurse |
?{($_.Name -like "*.xlsx")-Or($_.Name -like "*.xlsm")-Or($_.Name -like "*.xls")} | 
Select-Object FullName |
Export-Csv $path_of_file1 -Encoding Default -NoTypeInformation
  • ここではxlsx,xlsm,xlsに絞っていますが、エクセルの拡張子は他にもあります(Excel でサポートしているファイル形式)。必要に応じて変更してください。ただそもそも、もっとスマートなエクセル拡張子指定方法があるような気もします。

  • FullNameでファイルのフルパスのみを取得しています。他の情報も合わせて取得したい場合は"FullName"の部分を"FullName,IsReadOnly"などと","で区切って入力することで取得可能です。取得可能な情報の一覧は以下をPowerShellで叩くと出てきます。

Get-ChildItemで取得可能なSystem.IO.FileInfoのプロパティ一覧
Get-ChildItem | Get-Member -MemberType Property

続いて、先に作成したリストファイルをエクセルで開き、エクセルファイルの各プロパティ情報を取得してcsvに出力します。

Export_list_of_excel_property_to_csv
#$path_of_target_folderに一覧化したいフォルダを指定する
$path_of_target_folder = "C:\Users\~~~~~\Desktop\test"

#$name_of_file1に先ほど出力したcsvのファイル名を指定する
$name_of_file1 = "excel_filelist.csv"

#$name_of_file2に先ほど出力したcsvのファイル名を指定する
$name_of_file2 = "excel_propertylist.csv"

$path_of_file1 = Join-Path $path_of_target_folder $name_of_file1
$path_of_file2 = Join-Path $path_of_target_folder $name_of_file2
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true

Import-Csv $path_of_file1 -Encoding Default| % {    
    $excel.DisplayAlerts = $false #「読み取り専用で開きますか」のポップアップを表示しないため
    #パスワードがかかっているファイルに対して、そのパスワードがpassword以外の場合開かずErrorとなるのでcatchする。
    $targetName = $_.FullName #catchの中では$_がエラー内容を表してしまうので、先にFullNameを固定しておく。

    try{
        $wb = $excel.Workbooks.Open($targetName,$false,$false,5,"password","password",$false)
        #optionalのparameterの一部にでも、$nullまたは""を指定すると正常に動作しない(Microsoft未修整バグ?)。
        #Passwordのかかっていないファイルにパスワードを入れても問題無く開くようだ(自分の環境下では開いた)が、問題ないという旨の文章を見たわけではなく、やや不安が残る。
        #式 . Open(1=FileName, 2=UpdateLinks, 3=ReadOnly, 4=Format[5:なし] , 5=Password , 6=WriteResPassword, 7=IgnoreReadOnlyRecommended,... )
        #IgnoreReadOnlyRecommendedを$Trueにしないのは、$Trueにして開けた場合に、そのファイルがReadOnlyRecommendedかどうかが分からなくなるという現象が確認されたため。
        New-Object PSObject -Property @{
            FullName = $targetName;
            ReadOnlyRecommended = $wb._ReadOnlyRecommended;
            HasPassword = $wb.HasPassword;
            Author = $wb.Author;
        } | Export-Csv $path_of_file2 -Encoding Default -Append
        $wb.Close()
    }catch{
        New-Object PSObject -Property @{
            FullName = $targetName;
            ReadOnlyRecommended = "不明";
            HasPassword = "MaybeTrue";
            #Maybeとなっているのは、パスワードが設定されている以外の理由で開かないことがある(ファイルが壊れているなど)ためである。
            Author = "不明";
        } | Export-Csv $path_of_file2 -Encoding Default -Append
    }finally{
    $excel.DisplayAlerts = $true
    }
}

$excel.Quit()
# プロセスを解放する
$excel = $null


工夫点

  • 対象ファイルの数が多い場合に備えて、一度エクセルファイルのリストを出力し、その後でそのリストをもとに1件ずつcsvにappendして最終結果を取得するという方法を取ることで、途中でexcelが固まった場合に復帰しやすくしています。

課題・問題点

  • Workbooks.Openのオプションの指定について、保存せずに閉じているので問題は少ないかと思いますが、より良い方法を調べるべきでしょう。
  • 未調査ですが、($_.Name -like "*.xlsx")-Or(\$_.Name -like "*.xlsm")-Or(\$_.Name -like "*.xls")といった書き方ではなく、エクセルの拡張子全体をスマートに指定する方法がありそうな気がします。
  • 変数の命名規則や、保存するcsvの名前をどうすべきかについては未だ試行錯誤しています。
  • 各ファイルでDisplayalertをOffにして、finallyの箇所でOnにしているのは無駄で、try-catch-finallyの外に書いた方が一度で済むので良いと思います。(finallyを削除するのが寂しかったので変えていません)

おわりに

  • コメント大歓迎です。

他の参考文献・参考サイト

  • 特にありません。