たくさんあるexcelファイルのどれに必要な情報が含まれているのかわからないときに、WindowsのPowerShellとExcelアプリを連携させて各セルの中身を検索するスクリプトを作成した。
単純な処理にした場合、excelが保護されている(パスワードあり)ものがあると、スクリプト上でそのexcelファイルを開こうとしたところで、パスワード入力が出てそこで止まってしまう。
そのため、保護されているexcelファイルを効率よく除外する手法はないものか、というのを探したのだが、どうやらPowerShell/Excel連携の機能には、該当するファイルに保護がかかっているかどうかを判定する機能はない模様。
stackoverflowの「Powershell Test for Excel Password Protection」にて解決方法を発見
保護されているexcelファイルというのは、暗号化されたzipファイルで、ヘッダが特定文字列で始まるので、その文字列があれば暗号化されている、と判断して処理を飛ばす、ということが書かれていた。
$sig = [Byte[]] (0x50,0x4b,0x03,0x04)
$bytes = get-content $_.fullname -encoding byte -total 4
if (@(compare-object $sig $bytes -sync 0).length -eq 0) {
# process unencrypted file
}
これを搭載することで、パスワード入力要求を飛ばすことができた。
で、検索した結果をCSVファイルに保存する処理を付けたものがこちら
CSV出力周りの処理がめんどくさくなってるの「PowerShellで巨大なファイルをGet-Contentし、Export-Csvするのを省メモリで行う」によるもの。これをやらないと速度がだいぶ変わるはず
$excel= New-Object -ComObject Excel.Application
$excel.visible= $false
# 検索対象ディレクトリ
$directory="C:\Users\osakanataro\Documents"
# 検索結果のcsv保存先
$outputfile="c:\tmp\output-tmp.csv"
# 検索キーワード
$keywords="ワード1","ワード2","ワード3"
# CSV出力向けの処理
$results=@()
$linecount=0
#
Get-ChildItem -Recurse $directory -Include *.xlsx,*.xls | ForEach-Object {
$filename=$_.FullName
Write-Host "file:", $filename
# 暗号化チェック簡易版用
$sig=[Byte[]] (0x50,0x4b,0x03,0x04)
$bytes= Get-Content -Path $filename -Encoding Byte -TotalCount 4
if( @(Compare-Object $sig $bytes -Sync 0).Length -eq 0){
# 暗号化されていないファイルの処理 start
$workbook=$excel.workbooks.open($filename)
#Write-Host "ファイル:",$workbook.Name
$workbook.Sheets|ForEach-Object {
$worksheet = $_
#write-host "タブ:", $worksheet.name
$keywords | ForEach-Object {
$keyword=$_
$result1=$worksheet.Cells.Find($keyword)
while($result1 -ne $null){
Write-Host "タブ:",$worksheet.Name," 単語:",$keyword," 場所:",$result1.Column, $result1.row, $result1.text
# CSV向け処理 start
$output = New-Object -TypeName PSObject
$output | Add-Member -MemberType NoteProperty -Name "ファイル名" -Value $filename
$output | Add-Member -MemberType NoteProperty -Name "タブ" -Value $worksheet.Name
$output | Add-Member -MemberType NoteProperty -Name "単語" -Value $keyword
$output | Add-Member -MemberType NoteProperty -Name "Column" -Value $result1.Column
$output | Add-Member -MemberType NoteProperty -Name "Row" -Value $result1.row
$output | Add-Member -MemberType NoteProperty -Name "文面" -Value $result1.text
$results+=$output
$linecount++
# CSV向け処理 end
$result2=$result1
$result1=$worksheet.Cells.FindNext($result2)
if( $result1.row -le $result2.row ) { $result1=$null }
}
}
}
# 暗号化されていないファイルの処理 end
}else{
# 暗号化されているファイルの処理 start
Write-Host " 暗号化されている"
$output = New-Object -TypeName PSObject
$output | Add-Member -MemberType NoteProperty -Name "ファイル名" -Value $filename
$output | Add-Member -MemberType NoteProperty -Name "タブ" -Value "パスワード保護につき確認できず"
$output | Add-Member -MemberType NoteProperty -Name "単語" -Value ""
$output | Add-Member -MemberType NoteProperty -Name "Column" -Value ""
$output | Add-Member -MemberType NoteProperty -Name "Row" -Value ""
$output | Add-Member -MemberType NoteProperty -Name "文面" -Value ""
$results+=$output
$linecount++
# 暗号化されているファイルの処理 end
}
if(($linecount % 1000) -eq 0 ){
$results | Export-Csv $outputfile -Encoding UTF8 -NoTypeInformation -Append -NoClobber
$results = @()
}
}
$results | Export-Csv $outputfile -Encoding UTF8 -NoTypeInformation -Append -NoClobber
