【Powershell】複数のExcelファイルから特定文字列を含むものを一括検索する 

スポンサーリンク
Excel操作
スポンサーリンク

複数のExcelファイルのなかで、特定キーワード(文字列)を含むものを一括検索して、リスト化したい。

仕事でIPアドレスなど、特定のキーワードを含むファイル/セルを一括検索したかったので作った。

搭載機能

  • フォルダパスと検索キーワードをテキストボックスから指定できるようにする。
  • 指定されたフォルダ(サブフォルダ配下含む)に存在するxlsx、xls、xlsm、xltから、指定された検索キーワードを含むファイルを列記して出力する。 列記する内容は「ファイルパス、対象ワークシート、対象セル、対象セルの記載(テキスト)とする。
  • 結果はCSVで出力する。ファイル名はSearchExcel_yyyymmdd_時分秒.csvとする
  • 後からエラーなどを追えるように実行ログを別ファイルとして吐き出す。(別途関数にしたほうが良かった気がする。要改善)

コード

絶対改善余地があるけどこんな感じ。

$dir=""
$word=""

# CSVテーブルの用意
$CSVdatas = @()

# 日付の取得
$date = Get-Date -Format "yyyyMMdd";

# 時間の取得
$time = Get-Date -Format "HHmmss";

# 調査結果を出力するフォルダ
$scriptPath = $MyInvocation.MyCommand.Path
$scriptPath_split = Split-Path -Parent $scriptPath
$ResultFolder = ($scriptPath_split+"\結果")

# 調査結果出力結果フォルダがなかったら作成
if( -not (Test-Path $ResultFolder) ) {
    New-Item $ResultFolder -Type Directory
}

# 実行結果出力ファイル名
$ExecuteLogFile = ( "\ExecuteLog_"+$date+"_"+$time+".log" )

#実行結果出力フォルダとファイル名のマージ
$ExecuteLog = Join-Path $ResultFolder $ExecuteLogFile

# アセンブリ読み込み
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

# フォーム作成
$Form = New-Object System.Windows.Forms.Form 
$Form.Size = New-Object System.Drawing.Size(400,200) 
$Form.Text = "Excel内文字検索ツール"

# ラベル作成(フォルダパス)
$LabelFolderPath = New-Object System.Windows.Forms.Label
$LabelFolderPath.Location = New-Object System.Drawing.Point(20,10)
$LabelFolderPath.Size = New-Object System.Drawing.Size(350,20)
$LabelFolderPath.Text = "検索したいExcelファイル郡が格納されているフォルダを指定してください"
$Form.Controls.Add($LabelFolderPath)

# 入力用テキストボックス(フォルダパス)
$TextBoxFolderPath = New-Object System.Windows.Forms.TextBox
$TextBoxFolderPath.Location = New-Object System.Drawing.Point(20,30)
$TextBoxFolderPath.Size = New-Object System.Drawing.Size(300,20)
$Form.Controls.Add($TextBoxFolderPath)


# ラベル作成(検索ワード)
$LabelSearchWORD = New-Object System.Windows.Forms.Label
$LabelSearchWORD.Location = New-Object System.Drawing.Point(20,70)
$LabelSearchWORD.Size = New-Object System.Drawing.Size(300,20)
$LabelSearchWORD.Text = "検索したいワードを入力してください"
$Form.Controls.Add($LabelSearchWORD)

# 入力用テキストボックス(検索ワード)
$TextBoxSearchWORD = New-Object System.Windows.Forms.TextBox
$TextBoxSearchWORD.Location = New-Object System.Drawing.Point(20,90)
$TextBoxSearchWORD.Size = New-Object System.Drawing.Size(300,20)
$Form.Controls.Add($TextBoxSearchWORD)

# 参照ボタン
$ButtonFolderPath = New-Object System.Windows.Forms.Button
$ButtonFolderPath.Location = New-Object System.Drawing.Point(320,30)
$ButtonFolderPath.Size = New-Object System.Drawing.Size(40,20)
$ButtonFolderPath.Text = "参照"
$Form.Controls.Add($ButtonFolderPath)

# OKボタン
$ButtonOK = New-Object System.Windows.Forms.Button
$ButtonOK.Location =  New-Object System.Drawing.Point(230,120)
$ButtonOK.Size = New-Object System.Drawing.Size(60,20)
$ButtonOK.Text = "OK"
$Form.Controls.Add($ButtonOK)

# Cancelボタン
$ButtonCancel = New-Object System.Windows.Forms.Button
$ButtonCancel.Location =  New-Object System.Drawing.Point(300,120)
$ButtonCancel.Size = New-Object System.Drawing.Size(60,20)
$ButtonCancel.Text = "キャンセル"
$ButtonCancel.DialogResult = "Cancel"
$Form.Controls.Add($ButtonCancel)

# 参照ボタンをクリック時の動作
$ButtonFolderPath.add_click{

    #ダイアログを表示しフォルダを選択する
    $Dialog = New-Object System.Windows.Forms.FolderBrowserDialog
    if($Dialog.ShowDialog() -eq "OK"){
        $TextBoxFolderPath.Text = $Dialog.SelectedPath
    }
}

# OKボタンをクリック時の動作
$ButtonOK.add_click{

    #フォルダパスが入力されていないときは背景を黄色にする
    if($TextBoxFolderPath.text -eq ""){
        $TextBoxFolderPath.BackColor = "yellow"
    }else{
        $Form.DialogResult = "OK"
    }

    #検索ワードが入力されていないときは背景を黄色にする
    if($TextBoxSearchWORD.Text -eq ""){
        $TextBoxSearchWORD.BackColor = "yellow"
    }else{
        $Form.DialogResult = "OK"
    }
}

Start-Transcript $ExecuteLog


$FormResult = $Form.ShowDialog()
if($FormResult -eq "OK"){

$dir =  $TextBoxFolderPath.Text
$word = $TextBoxSearchWORD.Text

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

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

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

# 調査結果出力ファイル名
$ResultFile = ( "\SearchExcel"+"_"+$date+"_"+$time+".csv" )

# 調査結果出力フォルダとファイル名のマージ
$Result = Join-Path $ResultFolder $ResultFile

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

Get-ChildItem "$dir" -Include "*.xlsx","*.xls","*.xlt","*.xlsm" -Recurse -Name | % {
    ${childPath} = $_
    ${wb} = ${excel}.Workbooks.Open("$dir\${childPath}")
    ${wb}.Worksheets | % {
        ${ws} = $_
        ${wsName} = ${ws}.Name
        ${first} = ${target} = ${ws}.Cells.Find($word)
        while (${target} -ne $null) {
            
            $CSVdata = New-Object PSObject | Select-Object Filepath,wsName,Cell,Text
            $CSVdata.Filepath = ($dir+"\"+${childPath})
            $CSVdata.wsName = ${wsName}
            $CSVdata.Cell = "$(${target}.Row), $(${target}.Column)"
            $CSVdata.Text =  $(${target}.Text)
            $CSVdatas += $CSVdata

            ${target} = ${ws}.Cells.FindNext(${target})
            if (${target}.Address() -eq ${first}.Address()) {
                break
            }
        }
    }
    ${wb}.Close(0)
}

${excel}.Quit()

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

$CSVdatas | Export-CSV $Result -Encoding Default -NoTypeInformation

# 調査結果ファイルを開く
Invoke-item $Result

}else {

echo "操作はキャンセルされました"

}

Stop-Transcript

実行イメージ

注意点と補足

・PowerShellが実行可能な環境である必要があります。
・共有フォルダなどで利用する場合は認証済みである必要があります。

改版履歴

2021/01/28 初版公開

コメント

タイトルとURLをコピーしました