PMPの流儀

我が家の流儀

我が家で気になったことを夫婦で取り上げていきます

MENU

【PowerShell】WebDriver Selenium 画像をExcelに貼り付け

PowerShell

WebDriver を使用してWEBサイトの画像をエクセルに自動貼り付けする方法を紹介します。前回紹介した、PowerShell + Microsoft Edge Webdriver + Selenium の環境を使います。

1. 概要

大事な人からの依頼で、とある製品の数百種類ある情報を一覧表にまとめることになり、1製品につき文書と画像をエクセルにちまちま貼り付けることを始めました。20個ほどやって作業にも慣れてきた頃…ソフト屋がこんな単純作業を手作業でやるなんて許されざる事と思い始めました。たとえ最終的に手作業のほうが速いこともあるかもしれないが自動化すべきと。一度習得しておけば他にも応用がきくからです。それに、PowerShell 推進者としても格好のネタではないか。

ブラウザのオートメーションは全く知識ゼロで、はじめてWebDriver なるものを知りました。前回の記事でブラウザのオートメーションはできるようになったので、Excel のオートメーションと連携させれば簡単にできそうです。 pmp-style.hatenablog.com

Excel のオートメーションというと VBA が筆頭です。Excel はかなり昔からオートメーションが実現されていて、COM(Component Object Model)というコンポーネント技術を使っています。.NET は COM の後継ということもありCOMを使える仕組みがあります。そのため PowerShell から Excel を使えるというわけです。

今回のポイントは2つです。
(1). WebDriver で抽出した画像をどうやって Excel の画像として挿入するか。
(2). COM オフジェクトの解放方法。うまく解放できないと Excel のプロセスが残ったままになります。

2. 詳細

2.1 画像の貼り付け方

試行錯誤して以下の方法で Excel に張り付けられるようになりました。

    # 1. WebDriver + Selenium で画像を検索。ここは適宜差し替えください。
    $pic = $driver.FindElement([By]::TagName("img"))

    # 2. 画像キャプチャ
    $scr = $pic.GetScreenshot()

    # 3. MemoryStream に格納
    $ms = New-Object System.IO.MemoryStream($scr.AsByteArray, 0, $scr.AsByteArray.Count)

    # 4. Bitmap に変換
    $bmp = New-Object System.Drawing.Bitmap($ms)

    # 5. Bitmapをクリップボードにコピー
    [System.Windows.Clipboard]::SetData([System.Windows.Forms.DataFormats]::Bitmap, $bmp)

    # 6. 少しウェイト
    Start-Sleep -Milliseconds 500

    # 7. クリップボードからシートにペースト
    $sheet.Paste()

本当はダイレクトに画像データを挿入したかったのですが、クリップボード以外だとファイル経由しか見つかりませんでした。Office Script というOffice オンライン用のスクリプトを使うと addimage()という関数があったのですが、クライアント側には存在していません。

手順6で500msのウェイトを入れてますが、少し間を開けないとペーストしても画像が抜ける現象がでました。非同期なのか ??

2.2 Excel COM Object の解放について

COM と .NET ではメモリ管理に関する考え方が大きく違います。COMはきっちり自分でメモリ管理が必要なのに対して、.NET ではシステムが自動的に未使用エリアを自動開放(ガベージコレクション)します。.NET のガベージコレクション機能を使ってCOMを解放させたいのですが、COMは自分を参照している数を保持しており、参照している変数があると解放できません。

そこで PowerShellの変数のスコープが重要になります。

PowerShell での変数スコープは、関数とスクリプトブロックのみ存在します。

スクリプトブロックは用途が違うため、C言語と同じようにロジックを関数で実行するようにしました。骨格は以下の流れです。

Function main
{
  # 起動処理
  # Excel COMオブジェクトを生成する。$excel は関数内のみのローカル変数
  $excel = New-Object -ComObject Excel.Application
  $excel.Workbooks.Add() 
  $excel.Visible = $true

 
  # 終了処理
  $excel.Quit()           # Excel アプリケーションを閉じる
  $excel = $null　　 # Excel COM オブジェクトへの参照を切る。　　
}

#------------------------------
# ここからスクリプトが始まる
main
[GC]::Collect()           # ガベージコレクション　COMを開放してもらう

ガベージコレクションでうまく解放できたかの確認ですが、タスクマネージャーでEXCEL.exe が消えていれば成功です。失敗すると残り続けるのでタスクマネージャー強制終了してください。

2.3 関数間のオブジェクト渡し

2.2 で紹介した方法は規模が小さいうちはこれでよいのですが、Excel の処理を分割して複数の関数で実装する場合も想定されます。例として、Excel のCOMオブジェクトの生成を別関数にしてみます。

Function excel_init($excel)
{
  $excel = New-Object -ComObject Excel.Application
  $excel.Workbooks.Add() 
  $excel.Visible = $true
}

Function main
{
  $excel = $null
  excel_init $excel
 
  $excel.Quit()
  $excel = $null
}

main
[GC]::Collect()

C#の感覚だとこれで動きますがこれは動きません。mainとexcel_init の $excel は別物です。PowerShellでは参照渡しをしてあげないと伝わりません。以前記事にしていますので解説はこちらを参照ください。

pmp-style.hatenablog.com

動くロジックは以下です。参照先では Value 経由じゃないと値がセットされません。

Function excel_init([ref][object]$excel) 
{
  $excel.Value = New-Object -ComObject Excel.Application
  $excel.Value.Workbooks.Add() 
  $excel.Value.Visible = $true
}

Function main
{
  $excel = $null
  excel_init ([ref]$excel) 
 
  $excel.Quit()
  $excel = $null
}

main
[GC]::Collect()

※ $excel をグローバル属性にしてしまう手もあります。いわゆるグローバル変数です。

3. 最終系

実際に動くプログラムを紹介します。

Yahoo! のトップページから img タグで最初に見つかった画像を Excel の B列2行目に貼り付けます。さらに、B列2行目のセルのサイズに画像を変形させ、セルのサイズ変更とともに画像サイズを追従させる設定にします。

# Set-ExecutionPolicy RemoteSigned -Scope Process
# Programed by ruruucky  2022.6.11

using namespace OpenQA.Selenium

Set-Location (Split-Path -Parent $MyInvocation.MyCommand.Path)

Function Create-EdgeDriver
{
    $ret = [Reflection.Assembly]::LoadFile((Join-Path (Get-Location) "WebDriver.dll"))
    Write-Host $ret
    
    $opt = New-Object Edge.EdgeOptions
    $opt.BinaryLocation = "C:\Program Files (x86)\Microsoft\Edge\Application\msedge.exe"

    $svc = [Edge.EdgeDriverService]::CreateDefaultService((Get-Location))

    $driver = New-Object Edge.EdgeDriver($svc, $opt)
    $driver.Manage().Timeouts().ImplicitWait = [System.TimeSpan]::FromSeconds(4) #暗黙的な待機4秒

    return $driver
}

# Excel の起動処理(分割する場合)
Function excel_init([ref][object]$excel)
{
    $excel.Value = New-Object -ComObject Excel.Application
    $excel.Value.Workbooks.Add() | Out-Null
    $excel.Value.Visible = $true
}

Function main
{
    $driver = Create-EdgeDriver
                                                                                                                                                                                                                                                                            
    # Yahoo! のトップページ
    $driver.Navigate().GoToUrl("https://www.yahoo.co.jp/")

    # 1. WebDriver + Selenium で画像を検索
    $pic = $driver.FindElement([By]::TagName("img"))

    # 2. 画像キャプチャ
    $scr = $pic.GetScreenshot()

    # 3. MemoryStream に格納
    $ms = New-Object System.IO.MemoryStream($scr.AsByteArray, 0, $scr.AsByteArray.Count)

    # 4. Bitmap に変換
    $bmp = New-Object System.Drawing.Bitmap($ms)

    # 5. Bitmapをクリップボードにコピー
    [System.Windows.Clipboard]::SetData([System.Windows.Forms.DataFormats]::Bitmap, $bmp)

    # 6. 少しウェイト
    Start-Sleep -Milliseconds 500

    #-------------------------------------------------------------------
    # ここから先は Excel の操作
    [object]$excel = $null

    excel_init ([ref]$excel)
    $book = $excel.ActiveWorkBook
    $sheet = $excel.ActiveSheet
    
    # 7. クリップボードからシートにペースト
    $sheet.Paste()

    # 8. 貼り付けた図を取得
    $sel = $excel.Selection

    # 9. 図形のアスペクトは無視
    $sheet.Shapes($sel.Name).LockAspectRatio = $false

    # 10. 図を埋め込むためのセル
    $cell = $sheet.Cells.Item(2, 2)

    # 11. セルに図の大きさを合わせる
    $sel.ShapeRange.Top = $cell.Top
    $sel.ShapeRange.Left = $cell.left
    $sel.ShapeRange.Width = $cell.Width
    $sel.ShapeRange.Height = $cell.Height

    # 12. 図をセルに追従するように設定
    $sel.Placement = 1

    $excel.Application.DisplayAlerts = $false  # 強制上書き
    $book.SaveAs("c:\temp\PicureCaptureTest.xlsx")          # ファイル名は適当に変えてください
    $excel.Quit()
    $excel = $null

    $bmp.Dispose()
    $bmp = $null
    $ms.Dispose()
    $ms = $null
    $driver.Close()
    $driver.Dispose()
    $driver = $null
}

main
[GC]::Collect()

後日談

この記事をかくきっかけになった300種類の製品の資料作りの件です

数日費やして完成し、感謝されると思って依頼主に話してみると・・・・

プログラムを書いてなんて言ってない、自分で頑張って作ってほしかった

と言われてしまったのでした。なにもせずに楽して完成してしてしまうことが気に入らなかったそうで。。。

え、、仕事よりも気合を入れて頑張ってプログラムを書いたのに !! なんでもかんでもプログラムにしても喜ばれるというわけではないという教訓でした…トホホ