misc.log

日常茶飯事とお仕事と

Excel VBAからPHPのスクリプトを呼ぶ

MSOffice VBA 業務日誌 業務日誌.php Test プログラミング

仕事でデータの引っ越し処理を行う必要があり、Excelに貼り付けてデータ加工し、数式でInsert文を作るというものを作って大量データを一気に引っ越そうとしていたのですが、問題が発生。なんと、カタカナ等を半角変換しなければならないと……。いまどき半角か？とも思いましたが要件としてあるので仕方ない。また、変換もPHPの関数と同等の変換をしてほしいとのこと。
テストで正当性を確認するのも面倒なので、いっそ「ExcelからVBAでPHP呼んでPHP側で処理したものを戻せばいいんじゃね？」というわけで、下記の記事を参考にして試作してみました。

qiita.com

紹介されていた方法で試作

上記の例では、PHP側で標準入力で文字を受け取り、処理して標準出力に出すスクリプトを組んで1セルずつ処理する方式を取っています。これもまずはコピーさせてもらってカスタマイズして試行。できました。

<?php
	// 全角英数カナ記号の半角変換
	$sourceStr = trim(fgets(STDIN));
	$result = mb_convert_kana($sourceStr,'ask', 'SJIS');

	echo $result;
?>

上記のコードをファイルにして、PHPが動く環境で「php.exe ファイル名」とすると文字の待ち受け状態に。ここで全角カタカナを入力すると、半角になってでてきます。それをVBAから以下のようなコードで呼び出せば、Excelの関数として使えるようになります。ただ、関数が呼ばれるたびにコマンドプロンプトがたちあがりますけどね（笑）。

' PHPのカナ変換を用いた半角カナ変換
Public Function halfKanaConv(ByVal source) As String

    'PHPコマンド実行パス
    Dim path As String
    path = "php.exe " & ThisWorkbook.path & "\kanaconv.php"
    
    'WSH生成
    Dim wsh As Object
    Set wsh = CreateObject("WScript.shell")
    
    'コマンド実行
    Dim cmd As Object
    Set cmd = wsh.exec("%ComSpec% /c " & path)
    cmd.StdIn.WriteLine source
    Do While cmd.Status = 0
        DoEvents
    Loop
    
    '結果抽出
    result = cmd.StdOut.ReadAll
    
    halfKanaConv = result

End Function

ただ、これだと上記のようにセル単位でしか使えませんし、数万件のセルを処理すると、実際やってみましたがとても遅い上、コマンドプロンプトが出ては消えるというのを繰り返してその間はパソコンが使えなくなります。これでは実用には耐えません。ということで以下の方針で組み替えてみることにしました。

  • Excelからは対象セル全てを改行区切りの文字列としてテキストファイルに吐き出す（SJISで吐き出す）。
  • 上記の仕組みを応用してPHPの処理を呼ぶ。
  • PHP側では所定のファイルをよみこみ、各行について半角変換を掛けて、同じファイルに書き戻す処理を組んでおく。
  • ExcelはPHP処理終了を検知してファイルを再読み込みし、元のセルに貼り直す。

ExcelVBAを実務で使い倒す技術

ExcelVBAを実務で使い倒す技術

大量処理版:Excel側の処理

Excel側は、シートと対象列を渡されたら、その列の有効データを全てテキストファイル「export_rows.txt」に書き出し、PHP処理を呼んでから、ファイルを読み直してセルに戻す下記の処理を用意します。GetEffectiveDataCount関数は特定列のデータが入っている行数を返す自作の関数です。ここは何とかしてください（とりあえずなら固定数にしてしまって動作確認は可能です）。

Public Sub kanaConv(ByVal sheetName As String, ByVal colName As String)

    Dim idx As Long
    Dim startRow As Long
    Dim rows As Long
    
    '処理開始行の1行前の行番号を用意しておく（ループの起点に利用）
    startRow = 10 - 1
    '最大行数を取得する
    rows = GetEffectiveDataCount(sheetName, colName)
     
    '書込み用にファイルオープン
    Dim Fso As New FileSystemObject
    Dim TStream As TextStream
    Set TStream = Fso.CreateTextFile(ThisWorkbook.path & "\export_rows.txt", True, False)
    'セル内容を書き出す
    For idx = 1 To rows
        TStream.WriteLine (Worksheets(sheetName).Cells(startRow + idx, colName))
    Next
    TStream.Close
    Set TStream = Nothing
    Set Fso = Nothing

    'PHPに処理をわたす
    fileKanaConv
    
    '読込用にファイルオープン
    Set TStream = Fso.OpenTextFile(ThisWorkbook.path & "\export_rows.txt", ForReading, False, TristateUseDefault)
    'セルに書き戻す
    For idx = 1 To rows
        Worksheets(sheetName).Cells(startRow + idx, colName) = TStream.ReadLine
        'Worksheets(sheetName).Cells(startRow + idx, "A").value = TStream.ReadLine  'デバッグ用に結果をA列に書きだす
    Next
    TStream.Close
    Set TStream = Nothing
    Set Fso = Nothing

End Sub

次に、PHPを呼ぶ部分。ここはこんな感じで。PHP側には引数として作業場所のフォルダ名だけ渡しておきます（でないと、読み込み、書き戻しのファイルの場所がよくわからなくなる）。

' PHPのカナ変換を用いた変換処理呼び出し。
' PHP側は固定のテキストファイル名 export_rows.txt を読み込んで変換、書き戻す
Public Sub fileKanaConv()

    'PHPコマンド実行パス生成
    Dim path As String
    path = "php.exe " & ThisWorkbook.path & "\kanaconv.php"
    
    'WSH生成
    Dim wsh As Object
    Set wsh = CreateObject("WScript.shell")
    
    'コマンド実行。引数は作業ディレクトリパス
    '処理終了を検知するまで待つ。
    Dim cmd As Object
    Set cmd = wsh.exec("%ComSpec% /c " & path & " " & ThisWorkbook.path)
    Do While cmd.Status = 0
        DoEvents
    Loop

End Sub

大量処理版:PHP側

PHP側は、作業場所のフォルダ名をうけて、そこにある固定名称のファイルを開いて配列に格納。配列をループさせながら変換をかけ、同じファイルに書き戻します。ループの部分、おそらくFor Eachでも行けるのでしょうけれど、元ネタがExcelセルなので行の並びが変わったらいやだな、ってことでFor文による原始的なループにしています。

<?php
    // ファイル読み込み
    $lines = file($argv[1] . '\export_rows.txt');

    // 変換
    $counts = count($lines);
    for ($idx = 0; $idx < $counts; $idx++){
	$lines[$idx] = mb_convert_kana($lines[$idx],'ask','sjis-win');
    }

    // 書き戻し
    file_put_contents($argv[1] . '\export_rows.txt', $lines);
?>

Excel側改善

Excelの処理ですが、最後に結果を貼り戻す際に数式等が大量にあると、再計算がセル数だけ発生してしまい大変なことになります。ですので、処理開始と終了の時点で下記のコードを入れた方が無難です。

処理開始前にはこちらを。

    '自動計算等停止
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

処理後にはこれで設定を戻します。

    '自動計算等再設定
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With