記事の概要
PowershellでExcelの翻訳データをチェックする関数を書いてみようという試みです。
チェックの目的は主に致命的なミス（数値や名詞の間違い、訳抜けなど）の洗い出しです。
PowerShell 6で動作確認済みです。
素人なもので、まだまだ問題点は多いかと思いますが何かの参考になれば幸いです。
下準備（翻訳データの整備）
翻訳データの入ったexcelを次のように整えます。
- データは1シート目のみにまとめる
- B列とC列に原文と訳文を入れる
- 同じ行の原文と訳文は対応させる（1行あたり1文が適量）
ここでの実行例に使うファイルは次のようになっています。
- A列: 連番
- B列: 原文（英語）
- C列: 訳文（日本語）
ダミーの行の中に実行例で検索対象とする原文-訳文を混ぜています。原文と訳文は1文あたり1セルに割り振られています。
関数の詳細（PowerShellのヘルプ風）
名前
Find-MatchText
概要
翻訳データの入ったexcelファイルのセル内のテキストを検索しマッチ部分のデータを出力します
構文
Find-MatchText \$dir(検索対象のディレクトリ) \$base(検索文字列) [ \$target(引き当て用検索文字列) ] [\$lang(検索対象の言語{ jp | (「jp」以外の文字列) }) ]
説明
Find-MatchText関数はexcelを指定した範囲のセルから文字列を検索し、マッチ部分のデータ（連番、ファイルパス、行数、セルのテキスト、対応するセルのテキスト）を出力します。
出力の際、セルのテキストは引数で指定した文字列のみ文字色を赤にします。
引き当て用検索文字列を設定した場合、セルのテキストに文字列がマッチした場合とマッチしない場合に分けて出力します。
この関数は次の2つの「したい」を可能にします。
1. 検索文字列にマッチしたセルの原文（訳文）とそれに対応する訳文（原文）を確認したい
2. 検索文字列にマッチしたセルの原文（訳文）に対して 引き当て用検索文字列が訳文（原文）で使用されているか確認したい
引数
\$dir : 既定値 None
\$base : 既定値 None
\$target : 既定値
\$lang : 既定値
\$baseが日本語の場合、\$langに「jp」を指定します。
ただし、\$targetに引き当て用検索文字列を指定しない場合は\$targetに「jp」を指定することができます。
注記
検索文字列に正規表現は使用できません。
また、英字の大文字と小文字の区別はつけずに検索しています。
例1: 「celsius」を含む原文とそれに対応する訳文を検索
例2: 「celsius」を含む原文とそれに対応する訳文を引き当て用検索文字列を「摂氏」にして検索
例3: 例2の結果で「100」の訳し方に不安を感じたので「100」を含む原文に対しての訳を「100」にして検索
主に使用したステートメント、Cmdlet、その他の小技
- Comオブジェクト（Excel.Application）の起動～excelの操作～終了
- if, whileステートメント
- Write-Host, ForEach-Objectコマンドレット
- 比較演算子各種、split演算子
- 配列
それでは、関数を1. 準備、2. ブックの操作、3. 出力、4. 終了の4段階に分けて確認していきます。
1 準備
- 引数から検索対象のディレクトリ、検索文字列、ベースとなる言語を決定
- 検索文字列を出力
- excel操作の開始（ComObjectの参照）
function Find-MatchText($dir,$base,$target="",$lang="")
{
#第3引数が「jp」の場合
#第3引数を空白にして「jp」を第4引数にする
if($target -eq "jp")
{
$target = ""
$lang = "jp"
}
#検索の対象となる言語を決定
#$langが「en」（デフォルト）→検索対象: B列（英語）
#それ以外→検索対象: C列（日本語）
if($lang -ne "jp")
{
$range_base = "B:B"
}
else
{
$range_base = "C:C"
}
#検索文字列を出力
Write-Host "Keyword(base) >> $base"
if($target -ne "")
{
Write-Host "Keyword(target) >> $target"
}
#マッチした行数のトータルをカウントする変数を定義
$counter = 0
#excelのアプリケーションのインスタンスを生成
$xlapp = New-Object -ComObject Excel.Application
2 ブックの操作
- 各ブックの1シート目に対して次の処理を実行
- 検索対象の列に対して文字列がマッチしたセルの情報を項目別に配列に格納
- マッチする文字列がなくなったらブックを閉じ次のブックに進む
#検索対象のディレクトリからexcelファイルのみ抽出
Get-ChildItem $dir -Include "*.xlsx",`
"*.xls","*.xlsm"`
-Recurse -Name |
#各ブック1シート目の検索、データの取得
ForEach-Object{
$ChildPath = $_
$wb=$xlapp.Workbooks.Open("$dir\$ChildPath")
$ws = $xlapp.Worksheets.Item(1)
#最初に開くブックの場合は下記の変数を配列と定義
if($counter -eq 0)
{
$text_base = $text_target =`
$write_line = $row = @()
}
#$range_baseに対して$baseがマッチしたセルの情報を
#$matchと$firstmatchに格納
$match = $firstmatch = $ws.range($range_base).Find($base)
#$matchが$nullでない限り、以下の3項目を配列の変数に格納
#$write_line: 連番、ファイルパス、行数
#$text_base: マッチしたセルのテキスト
#$text_target: マッチしたセルに対応する訳文（原文）のテキスト
While($match -ne $null)
{
$write_line += "[($($counter+1)) $childpath,$($match.row)行目]"
$text_base += $match.Text
#次にマッチするセルの情報を$matchに格納
$match = $ws.range($range_base).findnext($match)
if($range_base -eq "B:B")
{
$text_target+=$ws.Cells.Item($match.row,$match.column+1).Text
}
else
{
$text_target +=$ws.Cells.Item($match.row,$match.column-1).Text
}
#$matchが列内を一巡して最初の位置に戻ったらループを抜ける
if($match.Address() -eq $firstmatch.Address())
{
$counter++
break
}
$counter++
}
#ブックを閉じる
$wb.Close()
}
3 出力
・検索結果を出力するにあたり、以下の3点を作成
(1) 出力をカスタマイズするための関数（文字色変更）
(2) 原文と訳文の区切り線を格納した配列
(3) ランダムに取得した文字色を格納した変数
・(1)～(3)を使い「2.」の処理で生成された配列を要素ごとに出力
#引数に文字列とパターンを指定。
#パターンにマッチした部分の文字色を赤に変えて出力
function Colorise-Text($text,$pattern)
{
#パターンにマッチした文字列の前後に「#」を挿入して
#文字列全体を$splitに格納
[void]($text -match $pattern)
$split = $text -Replace $pattern,"#$($matches.values)#"
#「#」を区切り文字にして$splitを配列にする
#各要素に対してパターンにマッチするかどうかで文字色を変更
@($split -Split '#')|
ForEach-Object{
If($_ -match $pattern)
{
Write-Host $_ -ForegroundColor Red -NoNewline
}
else
{
Write-Host $_ -NoNewline
}
}
}
#訳文同士を区切る線を出力する数だけ複製、区切り文字で分割し配列$senにする
$sen = "--------------------------------------------------
`n"*$write_line.Length
$sen = $sen -split "`n"
$sen = $sen -match "-"
#PowerShellで出力できる文字色からランダムで1色取得し$colourに格納
$colour = [enum]::GetValues($host.UI.RawUI.ForegroundColor.GetType())| Get-Random
#マッチした行の総数を出力
Write-Host "`n$counter`行マッチしました"
#出力内容を格納した配列にアクセスするためのインデックスを定義
$element = 0
#引数に引き当て用文字列を設定した場合、一致しなかった行だけ別途出力する。
#その際の振り分け用の配列を定義
$notInclude = @()
#$write_lineの要素の数だけスクリプトブロックを実行
While($element -lt $write_line.Length)
{
#$targetが空白でなく、かつ$write_target[$element]に対して
#$targetがマッチしない場合$notIncludeにインデックス番号を追加
if(($target -ne "") -and ($text_target[$element] -notmatch $target))
{
$notInclude += $element
$element++
}
#そうでない場合次の項目を出力
#$write_line[$element]（文字色: シアン）
#$write_base[$element]（文字色: $base部分のみ赤）
#$sen[element]（文字色: ランダム）
else
{
"`n"
Write-Host $write_line[$element] -ForegroundColor Cyan
Colorise-Text $text_base[$element] $base
Write-Host "`n$($sen[$element])" -ForegroundColor $colour
#$targetが空白でない場合:
#$write_target[$element]（文字色: $targetのみ赤）
#$targetが空白の場合:
#$write_target[$element]（文字色: デフォルト）
if($target -ne "")
{
Colorise-Text $text_target[$element] $target
}
else
{
Write-Host $text_target[$element]
}
$element++
}
}
#$notIncludeの要素が1以上あった場合次の項目を出力
#一致しなかった行数および該当する行の
#$write_line（文字色:赤）、
#$write_base（文字色: $base部分のみ赤）、
#$sen（文字色: ランダム）
#$write_target（文字色: デフォルト）
if($notInclude.length -ge 1)
{
Write-Host "`n`n【対応する文字列にマッチしなかったのは$($notInclude.length)行です。】" -NoNewline
$notInclude|
ForEach-Object{
"`n"
Write-Host $write_line[$_] -ForegroundColor Yellow
Colorise-Text $text_base[$_] $base
Write-Host "`n$($sen[$_])" -ForegroundColor $colour
Write-Host $text_target[$_]
}
}
4 終了
#excelを終了し、ComObjectの参照を解放
$xlapp.Quit()
$ws,$wb,$xlapp|
ForEach-Object{[void][Runtime.InteropServices.Marshal]::ReleaseComObject($_)}
}
注意
splitメソッドの区切り文字に「#」を使用するためセル内のテキストに「#」が使用されている場合はスクリプトの変更が必要になります。
また、引き当て用検索文字列に「jp」を設定したい場合はスクリプトの変更が必要になります。
参考
PowerShellで複数のExcelファイルを一括検索する
https://qiita.com/nejiko96/items/b423e2dda90181ef524eAbout Comparison Operators (PowerShell Documentation)
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_comparison_operators?view=powershell-6Windows PowerShell Cookbook 3rd edition / Lee Holmes (O'reilly)