powershellで画像をExcelに挿入

import-excel

環境

windows 10
powershell 5
excel 2016

概要

SE屋さんのエビデンス作成の一部をpowershellで自動化したい。

(powershellの勉強を含んでいます。冗長でも許してね)

できること

スクリーンショットを納めているディレクトリを指定 ＞ excelに整形しながら貼り付け。

コード

途中のeditにはあまり意味はない。

Param(
# ------------- edit ----------------
# Mandatory : 引数の入力を強制
    [Parameter(Mandatory=$true)]
    [String[]]
    $winshots,
# ------------- edit ----------------
    [Parameter(Mandatory=$true)]
    [String[]]
  $exceldir
)

begin
{
# --------------- edit ----------------
# Excelファイルを起動
    $xl = New-Object -ComObject Excel.Application -Property @{
     Visible = $false
     DisplayAlerts = $false
    }
    $wb = $xl.WorkBooks.Add()
}

process
{

# --------------- edit -----------------
# $winshotsが存在するのか確認
# 存在しない場合は、処理を終える。
    if(-not (test-path  $winshots) ) 
    {
        $exitCmt = "there is not " + $winshots + "."
        echo $exitCmt
        echo "exit."
        exit
    }
# --------------- edit -----------------
# $exceldirが存在するのか確認
    if(-not (test-path  $exceldir) ) 
    {
        $exitCmt = "there is not " + $exceldir + "."
        echo $exitCmt
        echo "exit."
        exit
    }
# --------------- edit -----------------
# $winshots配下のイメージのフルパスを配列に保存
# 複数階層にするとき、避難ディレクトリとして"."で始まる名前のディレクトリ以下にあるイメージは調べない。
    $subDirPaths = ( get-childitem $winshots | ?{$_.name -match "^[^\.]+" -and $_.mode -like "d*"} )
# ---------------  edit -----------------
# Excelファイルの名前を準備
  $exportFileName = ((split-path $winshots -leaf) + (get-date -format MMddhhmm))
    $excelName = join-path (convert-path $exceldir) $exportFileName
# この変数はもう使いませんよ、と言いたいだけ。
    $exportFileName = $null



# --------------- edit ----------------
# MsoTriState
    Set-Variable msoFalse 0 -Option Constant -ErrorAction SilentlyContinue
    Set-Variable msoTrue 1 -Option Constant -ErrorAction SilentlyContinue
# Excelファイル一般の高さ・横幅を指定
# 現状は、間違った値。デフォルトの行幅・列幅を変える方法が判明すれば、この値にする。
    Set-Variable cellWidth 2 -Option Constant -ErrorAction SilentlyContinue
    Set-Variable cellHeight 15 -Option Constant -ErrorAction SilentlyContinue
# AddPictureの仕様に合わせて名前を変更?
# linktofile: 画像を元ファイルとリンクさせるか
    $LinkToFile = $msoFalse
# savewithdocument: 画像をExcelの中で保存するか
    $SaveWithDocument = $msoTrue
    $Top = $cellHeight * 5
    $Left = $cellWidth * 3
    $Height = $cellHeight * 20 #デフォルトセルの幅変更の方法が判明すれば修正する。
    $Width = $cellWidth * 300 #デフォルトセルの幅変更の方法が判明すれば修正する。

# --------------- edit ----------------
    for($p=1; $p -le $subDirPaths.length; $p++){
# --------------- edit ----------------
# 最初の高さを上に戻す
    $Top = $cellHeight * 5
    $Left = $cellWidth * 3
# --------------- edit ----------------
# ワークブックの追加
        if(-not ($p -eq 1)){$wb.worksheets.add() >$null}
        $wb.Sheets(1).name = $subDirPaths[$p-1].name
        $sh = $wb.Sheets.Item($subDirPaths[$p-1].name)
# ファイル名.name : ファイル名を表示
# ファイル名.fullname : フルパスを表示
        $imagePaths = ($subDirPaths[$p-1] | get-childitem  | ?{$_.name -match ".*\.(jpg|png)"} ).fullname
# --------------- edit ----------------
# arguments to insert the image through the Shapes.AddPicture Method
        for($i=1; $i -le $imagePaths.length; $i++){
# addpictureの第二変数以降は、ドキュメントの引数名と同じ。
            $img = $sh.Shapes.AddPicture($imagePaths[$i-1], $LinkToFile, $SaveWithDocument, $Left, $Top, $Width, $Height)
            $Top = $Top + $Height + $cellHeight * 5
        }
    }
}


end
{
# --------------- edit ------------------
# Excelの終了
#
    $xl.ActiveWorkbook.SaveAS($excelName)
# その他変数を閉じる。
#ブックのクローズ
    $wb.Close($true)
#Excelのクローズ
    $xl.Quit()
#ガベージコレクタを動作させる
    [GC]::Collect()
}

解説

各コードの意味を後から思い出せように備忘録を書いておく。

引数

-winshots :スクリーンショットを納めているディレクトリパス。間に一つディレクトリを挟まなければならない。そのディレクトリ名がシートの名前となる。(名前は言わずと知れたフリーソフトから。感謝を込めて)
-exceldir :エクセルファイルを出力するディレクトリパス。(ファイル名は自動でwindhotsで指定したディレクトリ名+MMddhhmmが入力される。)

課題

時間を掛ければ・・・

• 間にディレクトリを挟む必要をなくしたい。(自動判別)
• コピペ部分が多いので、すべて自分で書き直したい。(自衛のため・学習のため)
• 同名のエクセルファイルがあった場合、警告する。

学習すればできるはず・・・

• 画像の縦横比を維持したまま大きさを調整したい。(どこかで見た気はするんだ。。。)
• 各画像を囲むような罫線を描きたい。(セルの一方向だけ罫線を引くにはどうすれば。。。)
• 変数の名前を分かりやすくしたい。
• セルのデフォルト幅を最初に変更させたい。

できるのか？

• コードの量を少なくしたい。(手でコピペ楽しいなぁ。。。)
• パイプで繋げることができるようにしたい。(PSObjectを受け取る？)
• get-help ファイル名 でヘルプを出す。

参考url(コードに反映されているとは言っていない)

powershellに興味を持った一番の要因。もっと広まれPowerShellの輪。
PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい - Qiita
powershellで画像リサイズ。もうこの人だけでもいいじゃないかな。
PowerShell で画像の回転、リサイズを行う - Qiita
powershell開発環境の構築。これがなければ、powershellを諦めていただろう。多謝。
VimとPowerShell - Qiita
本筋とは関係ないが、知っとくと便利なpowershellのTips
PowerShell コンソールにおける入力履歴管理について
ファイルパスからクリップボードにコピーする(.NET)。これで行けると思ってた時期がありました。
クリップボードの画像を表示する、クリップボードに画像をコピーする
クリップボードにアクセル(文字列)
クリップボードアクセス完結編
クリッぷボードに画像をコピー。二個目のサイトと合わせれば、指定ファイルに存在する画像をクリップボードにコピーは完成。
インターネット上の画像をクリップボードにコピーするコード(PowerShell版)
System.なんちゃらって何さって時に行き当たったサイト。
++C++; // 未確認飛行 C
リンク先のタイトルは、サイトタイトルかページタイトルかで迷う。
06.値渡しと参照渡し
英語。Excelに対するTips集。
By Example – PowerShell commands for Excel
英語。パワーシェルを用いたクリップボードの操作。
Som's Powershell Tips
英語。.NETプログラミングレファレンスの一部。powershellを使う上で避けては通れない道？
3-7 列挙型 (Enum)
英語。いつもお世話になっております。
Use PowerShell to Work with Pictures in Microsoft Excel | Hey, Scripting Guy! Blog
参照。
画像ファイルを挿入する：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug
引数の取り方備忘録
Trivial Contents -正規表現-
WindowsのPowerShellでファイルのフルパスを取得する | 俺的備忘録 〜なんかいろいろ〜
Excel Macros (VBA) tips for beginners: Change the worksheet name
Tech TIPS：WindowsのPowerShellでパス文字列を操作する - ＠IT

後書き

これでプライベートPCに依存している箇所があったらしぬる。
(さあ早く"次へを押下"を挿入する作業に戻るんだ。。。)