Excelに写真を貼り付けて資料を作成する機会は結構あり、神エクセル撲滅協会理事（自称）の私としても不本意ながら世の流れには逆らえず、そのようなExcelファイルを多々生産してきました。
過去にはSurfaceで写真を撮影したあと、そのまま所定のExcelフォーマットにはめ込むシステムも作成しておりますので、興味があるかたはご覧ください。
今回は、Surface等の撮影機能を持ったWindows端末を導入するという、かなり高いハードルを超えなくてもいいように、写真の中に必要な情報を持たせたバーコード（QRコード）を写し込むことで、その情報をもとにExcelフォーマットにはめ込んでいくシステムを作成します。
システム概要
主に写真付き作業報告書の作成を簡略化することを想定しています。このような作業写真を
ボタンひとつで次のよう写真台帳に自動的に貼り付ける
というのが今回のシステムです。
システムの概要図は次のようになります。
Excelから必要な情報をバーコード（QRコード）にして埋め込んだ看板画像を出力し、それを印刷等で出力して写り込むように写真を撮ります。
撮った写真をPythonスクリプトで処理し、クロップ、リサイズ、デコードをおこないファイル名をデコードしたデータの値にして保存します。
Excel側から保存された画像のファイル名から、どの場所に埋め込めばいいか判定して所定の位置へ埋め込むというしくみです。
Excelには看板兼台帳のデータを登録するシートと
それに連動した写真台帳シートと
それに連動した看板シートがあります。
看板シートは画像としてファイルに書き出せます。写真を撮るときにその看板を入れます。
このように撮影した写真をフォルダにまとめて保存します。
写真を保存したフォルダを指定してExcelでマクロを実行すると、写真内のバーコードをPythonスクリプトが読み取り、その結果から台帳のどこへ貼り付ければいいかがわかるので、ボタンひとつで写真台帳が完成するという寸法です。
読み取り精度検証
概要で結果がわかってしまっていますが、看板に埋め込む認識用コードは何を使うのが良いのか検証しました。
まずは単純に件名、場所、日付、状況の情報をそのままQRコードにして埋め込む方法が考えられます。このように。
日本語はNGなのでURLエンコードで英数字記号に変換してからQRコードにしています。すべての情報を内包しているので、管理面では一番楽ですがURLエンコードにより超絶長い文字列になり、それを収めるためにドットがめちゃくちゃ細かくなります。
どの程度の大きさで写せば認識できるかをテストするため、認識できたら赤枠を付けるスクリプトで処理してみると
この写真は4032×2268pixで撮影していますが、大きい方から2つめまでしか認識できません。これだと少し離れて撮影しただけで使い物にならなくなりそうです。
次に情報量を減らせばQRコードのドットも減るので、Excel側にこのような変換表を用意して文字数を短縮する方法が考えられます。
変換表を参照しなければ何を意味するコードかわからないという管理面での手間が増えますが、URLエンコードが不要になりQRコードをかなり簡素化できます。
同じように認識で赤枠スクリプトにかけてみると
結構小さいやつまで読めています。
これでもいいとは思いますが、QRコードは主張が強いので、バーコードでもっとさりげなく隅っこに表示したいです。
そこで今回はCODE39という規格のバーコードを採用しました。
CODE39の仕様はGoogle先生に聞いていただくとして、JANのように数字限定ではなく、英数字と一部の記号を含めるので今回のような用途には適します。
QRコードを大きくするには面積を広くしなければなりませんが、バーコードは横に伸ばしていけばいいので配置の自由度は上がります。
解説
VBAのソースコードです。
Option Explicit
Const PY_SCRIPT_FILE_NAME As String = "picture_crop_bc.py"
Const PY_SCRIPT_DST_DIR_NAME As String = "trim"
Const WS_SETTING_START_ROW = 4 '設定シートデータ開始行
Const WS_ALBUM_START_ROW = 2 '写真台帳シート開始行
Const WS_ALBUM_UNIT_ROW_COUNT = 8 '写真台帳1ユニットの行数
'設定シート列
Enum setting
area = 1
status
code
fname
End Enum
'写真台帳シート列
Enum album
info = 1
photo
End Enum
Sub main()
Dim sws As Worksheet
Set sws = Worksheets("設定")
Dim pws As Worksheet
Set pws = Worksheets("写真台帳")
Dim dp As String
Dim w As String
Dim h As String
Dim rw As String
dp = pws.Range("e1").Value '写真フォルダパス
If dp = "" Then
dp = ThisWorkbook.Path
End If
w = CStr(sws.Range("m2").Value) 'アス比横
h = CStr(sws.Range("m3").Value) 'アス比縦
rw = CStr(sws.Range("m4").Value) 'リサイズPixel
Call executePy(dp, w, h, rw)
Dim i As Long
Dim j As Long
i = WS_SETTING_START_ROW
j = WS_ALBUM_START_ROW
Dim fp As String
Dim tgt As Range
Do Until sws.Cells(i, setting.code).Value = ""
fp = dp & "\" & PY_SCRIPT_DST_DIR_NAME & "\" & sws.Cells(i, setting.fname).Value
Set tgt = pws.Cells(j, album.photo)
Call insertPicture(tgt, fp)
i = i + 1
j = j + WS_ALBUM_UNIT_ROW_COUNT
Loop
End Sub
Sub insertPicture(tgt As Range, fp As String)
Dim img As Shape
Set img = tgt.Parent.Shapes.AddPicture(Filename:=fp, _
LinkToFile:=False, SaveWithDocument:=True, _
Left:=tgt.Left, Top:=tgt.Top, Width:=-1, Height:=-1)
With img
.Width = tgt.Width
.Left = tgt.Left
.Top = tgt.Top
End With
End Sub
Sub executePy(dp As String, w As String, h As String, rw As String)
Dim wss As Object
Set wss = CreateObject("WScript.Shell")
Dim cmd As String
cmd = "python " & ThisWorkbook.Path & "\" & PY_SCRIPT_FILE_NAME & " " & _
dp & " " & w & " " & h & " " & rw
wss.Run cmd, 0, True
End Sub
Sub exportPicture()
With Worksheets("看板")
Dim r As Range
Set r = .Range("a1:b6")
Dim title As String
Dim dt As String
Dim area As String
Dim status As String
title = .Range("b1").Value '件名
dt = Format(.Range("b3").Value, "yyyymmdd") '日付
area = .Range("b2").Value '場所
status = .Range("a4").Value '状況
r.CopyPicture xlScreen, xlPicture
Dim c As Chart
Set c = Worksheets("buf").ChartObjects.Add(0, 0, r.Width, r.Height).Chart
c.Parent.Select
c.Paste
c.Export ThisWorkbook.Path & "\" & _
title & "_" & dt & "_" & area & "_" & status & ".png"
End With
Dim co
For Each co In Worksheets("buf").ChartObjects
co.Delete
Next
End Sub
Sub folderPicker(cellAddress As String)
Dim fp As String
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
Range(cellAddress).Value = .SelectedItems(1)
End If
End With
End Sub
ｍainプロシージャでは、シートに入力された値を取得し、各プロシージャへ引き渡します。
insertPictureプロシージャは第一引数として挿入先のセル、第二引数としてPythonスクリプトで処理した画像ファイルのパスをとり、画像を写真台帳へ挿入します。特に変わったことはしていません。
executePyプロシージャは引数として受け取ったパラメーターをコマンドラインに組み立ててPythonスクリプトをWScript.Shellを使って実行します。これも特に変わったことはしていません。
exportPictureは看板シートをpng画像ファイルとして書き出すプロシージャです。Google先生に聞くとシートの範囲を画像として出力する方法を教えてくれましたので、基本その通りやっています。
具体的には、画像として切り出したい範囲をRangeオブジェクトとして取得します。RangeオブジェクトのCopyPictureメソッドで画像としてコピーできます。ただしこれを直で画像ファイルとして書き出すことはできないようで、いったん画像と同じサイズのChartオブジェクトをbufシートへ追加します。
Dim r As Range
Set r = Worksheets("看板").Range("a1:b6")
r.CopyPicture xlScreen, xlPicture
Dim c As Chart
Set c = Worksheets("buf").ChartObjects.Add(0, 0, r.Width, r.Height).Chart
そのChartオブジェクトのPasteメソッドでコピーされた画像をChartに貼り付け、Exportメソッドでめでたく画像ファイルとして書き出せる･･･はずなのですが、どうしても画像が真っ白になってしまいます。
いろいろ実験するとステップ実行ではちゃんと画像が出力されており、どうも更新が間に合っていない説があるみたいです。その方向で先生に聞いてみるとPasteの前にParent.Selectを入れればいいよという情報を入手し、やってみたら成功しました。なので、意味不明なParent.Selectが存在します。VBAってぽつぽつこういう事象が発生しますよね。
Exportメソッドの引数にファイルパスをわたすと、そこへ画像ファイルができます。
c.Parent.Select
c.Paste
c.Export ThisWorkbook.Path & "\" & _
title & "_" & dt & "_" & area & "_" & status & ".png"
用が済んだらChartは不要なので削除します。
folderPickerプロシージャはシートのダブルクリックイベントからコールされ、フォルダ選択ダイアログを表示して、選択されたフォルダのパスをセルに入力します。写真が保存されたフォルダを指定するのに使います。
続いてPythonのソースコードです。
import os
import sys
from PIL import Image, ImageOps
from pyzbar.pyzbar import decode
def main(argv):
for f in os.listdir(argv[1]):
tp = os.path.join(argv[1], f)
if not os.path.isdir(tp):
edit_image(tp, int(argv[2]), int(argv[3]), int(argv[4]))
def edit_image(fp, w_ratio, h_ratio, w_pixel):
try:
wh_ratio = w_ratio / h_ratio
img = Image.open(fp)
fn = decode_image(img)
img_ratio = img.width / img.height
if img_ratio > wh_ratio:
crop_pixel = (img.width - img.height * wh_ratio) / 2
left = int(crop_pixel)
right = img.width - int(crop_pixel)
upper = 0
lower = img.height
img = img.crop((left, upper, right, lower))
if img.width > w_pixel:
img = img.resize((w_pixel, int(w_pixel * img.height / img.width)))
dst_dir = os.path.join(os.path.dirname(fp), 'trim')
if not os.path.exists(dst_dir):
os.mkdir(dst_dir)
if fn is None:
img.save(os.path.join(dst_dir, os.path.basename(fp)))
else:
img.save(os.path.join(dst_dir, os.path.basename(fn + '.jpg')))
except:
pass
def decode_image(img):
result = None
decode_data = None
# そのままデコード
d = decode(img)
if len(d):
decode_data = d
else:
# 読めなかったら色反転後デコード
img_inv = ImageOps.invert(img)
di = decode(img_inv)
if len(di):
decode_data = di
if decode_data:
for dd in decode_data:
if dd.type == 'CODE39':
result = dd.data.decode('utf-8')
return result
if __name__ == '__main__':
main(sys.argv)
画像をクロップ、リサイズする流れは以前の記事で解説していますので、こちらを参照ください。
今回のために追加されたのがdecode_image関数で、pyzbarを使って画像ファイル内のバーコード、QRコードを読み取り、読み取った値を返します。
edit_image関数でdecode_imageから返ってきた値をファイル名にして画像を保存します。
main関数にはVBAからコマンドラインで引数として各パラメーターがわたってきます。
ここまでで使用したデータ入りのサンプルファイルを配布しますので、お試しください。
使用方法
PythonスクリプトはPyInstallerで実行ファイルにしたかったのですが、pyzbarの依存DLL関係の設定が面倒すぎてやめました。よってお手元にPython実行環境が必要です。
1.Pythonの外部モジュールpyzbarとpillowが必要です。まだインストールしていない場合はインストールします。
pip install pyzbar
pip install pillow
pyzbarは別途C++ランタイムが必要な場合があるので、スクリプトがうまくうごかない場合はpyzbar単独で動作するかご確認ください。エラーが出たらメッセージでググってインストールしてください。
2.このページのダウンロードボタンからzipファイルを入手します。
適当な場所へ展開してください。中身のExcelファイルとpyファイルは同じフォルダに置いてください。
3.Excelファイルの設定シートに必要事項を入力します。
場所、状況はコードと表示する文字列の対応を設定します。ファイル名禁則文字は使用しないでください。画像処理の設定値は写真のクロップ、リサイズに適用されます。
件名、日付、場所、状況を入力します。場所、状況はG列、I列で設定しているものと同一にします。code39列とfilename列は関数で自動生成されます。
4.看板シートの管理コードをリストから選択して画像出力ボタンを押します。
Excelファイルと同じフォルダに看板画像（png）が出力されます。施工者以外は設定シートから参照しています。施工者はここで書き換えてください。
すげー無駄なことをしているなと思われるでしょうが、当然最初は設定シートをループ処理してすべての看板を一発で画像出力するように書いたのですが、どう頑張ってもバーコード部分だけ変更が反映されません。DoEventsで割り込みしても、Sleepで止めても、前述のCopyPictureと同じ方法でもダメで、唯一ステップ実行のときだけ反映されます。ので、あきらめて1枚ごとに出力するようにしました。
5.看板画像を入れて写真を撮影します。
印刷せずにタブレット端末などに表示するのがスマートです。画像を表示するだけなので激安Android端末で何の問題もありません。何ならAmazonFireでもよいかと。
6.撮影した写真を保存したフォルダを写真台帳シートの写真フォルダセルで指定します。
セルをダブルクリックするとフォルダ選択ダイアログが出ます。
7.写真貼り付けボタンを押します。
設定シートに入力した内容にしたがって、写真台帳へ写真が貼り付けられます。写真はクロップ、リサイズされたものがtrimフォルダに保存されます。
ファイルを入手する
留意事項
Excelのバーコード・QRコードの作成機能（Microsoft BarCode Control）は手軽に導入できるのはいいのですが、その完成度はかなり低く、突然サイズが変わっていたり、真っ白で何も表示されなくなっていたりということが日常茶飯事です。あくまでおまけで付いている機能と割り切ってご利用ください。正確に大量に看板画像を出力したいならPython側ですべてやってしまうのが妥当かと思います。
おわり。
