PowerShell

PowerShellで複数のExcelファイルを一括検索する

More than 3 years have passed since last update.

500本あるExcelの設計書の中から「ほげほげ」が書いてあるところを全部探さないといけなくなった。。。

そんな時に!

いろいろと応用も効くと思います!


SrchExcel.ps1

Param(${dir}="", ${word}="")

if (${dir} -eq "") {
${dir} = Split-Path $myInvocation.MyCommand.Path -Parent
}

if (${word} -eq "") {
echo "キーワードが指定されていません。"
return
}

echo "Dir : ${dir}"
echo "Keyword : ${word}"

${excel} = New-Object -ComObject Excel.Application

Get-ChildItem "${dir}" -Include "*.xlsx","*.xls","*.xlt" -Recurse -Name | % {
${childPath} = $_
${wb} = ${excel}.Workbooks.Open("${dir}\${childPath}")
${wb}.Worksheets | % {
${ws} = $_
${wsName} = ${ws}.Name
${first} = ${result} = ${ws}.Cells.Find(${word})
while (${result} -ne $null) {
echo "${childPath}[${wsName}][$(${result}.Row), $(${result}.Column)] : $(${result}.Text)"
${result} = ${ws}.Cells.FindNext(${result})
if (${result}.Address() -eq ${first}.Address()) {
break
}
}
}
${wb}.Close(0)
}

${excel}.Quit()

${ws} = $null
${wb} = $null
${excel} = $null
[System.GC]::Collect([System.GC]::MaxGeneration)