PowerShellでExcelの文字列の置換を行う

公開日:  

powershell excel


PowerShellを使ってExcelのセル内の文字列を置換します。
Excelの検索・置換機能をPowerShellから操作、実行するイメージです。

Excelの検索・置換機能

Excelの検索・置換機能

環境

  • Windows 10
  • PowerShell 5.1

文字列の置換

さっそくですが、以下がPowerShellスクリプトとなります。
ファイルパスは適宜置き換えてください。

try{
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    # xlsxファイルを開く
    $wb = $excel.Workbooks.Open("C:\path\to\Excel\file.xlsx")

    # シートを指定する
    $sheet = $wb.ActiveSheet

    # 置換処理(セル内容が完全一致するものを検索して置換する)
    [void]$sheet.Cells.Replace("Hello", "Hi", [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole)
    # 置換処理(セル内容が部分一致するものを検索して置換する)
    [void]$sheet.Range("A1", "C4").Replace("World", "Kenya", [Microsoft.Office.Interop.Excel.XlLookAt]::xlPart)

    # 保存
    $wb.Save()

    $excel.Quit()

}finally{
    $sheet, $wb, $excel | ForEach-Object {
        if ($_ -ne $null) {
            [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($_)
        }
    }
}

処理の流れを簡単に書くと以下のようになります。

  1. xlsxファイルを開く
    指定したエクセルファイルを開きます(Open)。

  2. シートを指定する
    アクティブなシートを変数に格納しておきます。
    このシートに対して処理を行うためです。

  3. 文字列の置換を行う
    セル範囲を表すオブジェクトを取得したら($sheet.Cells$sheet.Range("A1", "C4"))、検索文字列と置換文字列を指定して置換を行います(Replace)。
    また、今回はXlLookAtオプションを指定しています。検索文字列がセルに対して完全一致で検索されるか、部分一致で検索されるかを指定するオプションです。動作については以下のスクリーンショットも参考にしてください。
    ちなみにオプションは他にもあります。詳しくはこちら

  4. 保存する
    ファイルを上書き保存します(Save)。

以下に置換前と置換後のエクセルファイルのスクリーンショットを載せます。

置換前

置換前

置換後

置換後

今回のスクリプトでは"Hello"を"Hi"に置換していますが、完全一致で検索するオプション(XlLookAt::xlWhole)を指定しているため、上記画像のA4セル内の"Hello"は置換されていません。
それに対して、“World"はB2セル、A4セル内の両方とも"Kenya"に置換されています。

参考



関連記事