PCのカメラでバーコードを読み取りExcelに取り込む

Excelシートでバーコードによる物品管理を企むの巻。

はじめにお断りしておきますと、VBAだけでは不可能です。たぶん。

そんな無謀なことをを考えてしまったのなら(私もですが)、まずはExcel至上主義からの改宗をオススメいたします。今ならPython万能教はいかがでしょうか?楽しい未来が待っています。

VBAには荷が重すぎるカメラ制御

VBAではバーコードの扱いは何とかなるにしても、カメラは絶望的です。VBAの主旨から完全に逸脱する領域なので、標準ライブラリでサポートされているはずもなく。

世界は広いのでVBAから使えるカメラ制御DLLがあるのかもしれませんが、あったとしても私の技量では扱える気がしないので早々にあきらめ、Pythonでやります(Python万能教へようこそ)。

Pythonでのカメラ制御はおそるべき簡単さです。マジでビビります。

作戦としては、Pythonでカメラ制御とバーコード(QRコード)のデコードをやらせて、結果をファイルへ出力、VBAからそのファイルを読み取りExcelシートへ反映します。

bc_vba_python2.png

PythonからExcelを直で操作する術もありますが、今回はExcelは管理表として主に見る目的で使う関係でPythonには触られたくないのでこうします。

Pythonには超絶余裕のカメラ制御

Pythonでのバーコード読み取り方法をインターネッツで探すとたくさんやり方が出てきますが、私が採用したのは次の方法です。

カメラ制御にopencv-pythonを使います。インストールします。

pip install opencv-python

バーコードのデコードにpyzbarを使います。インストールします。

pip install pyzbar

はい。

これでもうPythonがカメラを制御してバーコード(QRコード)を写すとデータを読み取れるようになっています!簡単すぎでしょ?どーなってんの?Python万能教へようこそ!

なんかエラーが出ちゃったかたはGoogle先生を頼ってください。私も複数環境に導入してみましたが一部の環境では最初うまくいかなくて先生に聞いて解決しました。

実際にテストするには、次のサイトにコピペですぐ使えるイカしたサンプルコードがあるので試してみてください。ここまででエラーがなければ
5.OpenCVを使った読み取り(カッコいい)
のコードをコピペしてきて実行しましょう。



カメラが起動するので、その辺にころがっているバーコードを写すと

bc_vba_python1.png

ちゃんと認識しています。カッコよく枠も出ます。Qキーを入力すれば終了できます。

蔵書管理システムを作る

このサンプルコードを元にして、今回は物品管理のプロトタイプとして私の本棚の本のバーコードを読むとExcelの蔵書テーブルの該当書籍にチェックマークがついていくシステムを構築します。

Excelの蔵書テーブルはこのようにしました。

bc_vba_python3.png

カメラ制御、バーコード読み取り、ファイル書き出しを担うPythonスクリプトは次のようにしました。

import cv2
import winsound
from pyzbar.pyzbar import decode

cap = cv2.VideoCapture(1)
font = cv2.FONT_HERSHEY_SIMPLEX
BUF_FILE_PATH = r'C:\Users\Ore\Desktop\buf.txt'
barcodes = []

while cap.isOpened():
    ret, frame = cap.read()

    if ret:
        d = decode(frame)

        if d:
            for barcode in d:
                barcodeData = barcode.data.decode('utf-8')

                if barcodeData not in barcodes:
                    barcodes.append(barcodeData)
                    winsound.Beep(2000, 50)
                    font_color = (0, 0, 255)
                    with open(BUF_FILE_PATH, mode='a') as buf:
                        buf.write(barcodeData + '\n')
                else:
                    font_color = (0, 154, 87)

                x, y, w, h = barcode.rect
                cv2.rectangle(frame, (x, y), (x + w, y + h), font_color, 2)
                frame = cv2.putText(frame, barcodeData, (x, y - 10), font, .5, font_color, 2, cv2.LINE_AA)

    cv2.imshow('BARCODE READER', frame)

    if cv2.waitKey(1) & 0xFF == ord('q'):
        break

cap.release()

Python-VBA版解説

先のサンプルコードが主ですが、要所を解説していきます。

カメラは0~番号が振られています。以下のコードでカメラ番号を指定して、そのカメラの制御を取得しています。

cap = cv2.VideoCapture(1)

だいたいノートだと0がインカメラで1がメインカメラになっていることが多いみたいです。私の環境では1がメインカメラなので1にしています。

それと、あらかじめbarcodesという空のリストを作っています。

barcodes = []

これは読み取ったバーコードデータをキャッシュしておくためで、リアルタイム読み取り値とこのリストを比較し、リストになければappendとファイル書き出し、あれば書き出さないという処理にしています。

以降はopencv-pythonの一般的なカメラ映像処理ロジックなので、リファレンスを読んでいただくとして、追加している処理は、前述のバーコードデータのキャッシュとの比較処理で

if barcodeData not in barcodes:
    barcodes.append(barcodeData)
    winsound.Beep(2000, 50)
    font_color = (0, 0, 255)
    with open(BUF_FILE_PATH, mode='a') as buf:
        buf.write(barcodeData + '\n')
else:
    font_color = (0, 154, 87)

読み取り値(barcodeData)をキャッシュリスト(barcodes)と比較して

リストになければ、リストに追加して読み取り時の処理でバーコードに付ける枠の色を赤に設定して、ビープ音をならし、テキストファイルとして読み取り値を追加書き込みで出力します。

リストにあれば、枠の色を緑に設定するだけであとは何もしません。

これにより、はじめて読み取ったバーコードは赤枠になりbuf.txtとしてファイルに書き込まれ、すでに読み取っているバーコードは緑枠で表示されます。


次にExcel側です。

と、その前にbuf.txtをPythonスクリプトで指定したパスへあらかじめ作成しておきます。1行目はタイトルとして扱うので入力しておきます。

bc-vba-python31.png

ここへ先のPythonスクリプトがバーコードデータを追記していくので、Excelで データ → データの取得と変換 → テキストまたはCSVから でこのテキストファイルをシートに取り込みます。

bc-vba-python37.png

bufという新規シートに、このようにテーブルとして取り込まれます。

bc-vba-python33.png

ためしにテキストファイルにデータを追加、保存して

bc-vba-python32.png

Excelで データ → クエリと接続 → すべて更新 を実行すると

bc-vba-python36.png

このように追加されていきます。

bc-vba-python34.png

この更新作業をVBAで定期実行させればテキストファイルを自動でシートへ同期できますね。

ということで、テーブルを更新する処理を一定間隔で実行させるプロシージャを作ります。VBAを一定間隔で実行させる方法はインターネッツに例がたくさんあるので、それをそのまま使います。

Public tm As Date

Sub startTableRefresh()
    Worksheets("buf").Range("a1").ListObject.QueryTable.Refresh
    tm = Now + TimeValue("00:00:05")
    Application.OnTime tm, "startTableRefresh"
End Sub

Sub stopTableRefresh()
    On Error Resume Next
    Application.OnTime tm, "startTableRefresh", Schedule:=False
End Sub

読んで字のごとくですが、startTableRefreshで自動更新が開始され、stopTableRefreshで自動更新を終了します。更新のインターバルは5秒にしています。

このままではbufシートにただデータが列記された状態なので、これを蔵書テーブルへ反映します。

蔵書テーブルのISBN列の値がbufシートのテーブルに存在するならOKに、存在しないならNGになるように関数をstock列に設定します。次のようにしました。

bc-vba-python35.png

テーブルになっていると関数内で列名を指定するだけでよく、しかも最初の行だけ関数を入力すれば、残りの行は勝手に空気読み反映されて大変便利です。

条件付き書式でセルの色も変わるようにしておきます。

自動更新開始と同時にPythonスクリプトを実行してカメラを起動したいので、VBAからPythonスクリプトを実行するプロシージャを作ります。

Sub executePy()
    Dim wss As Object
    Set wss = CreateObject("WScript.Shell")
    Dim cmd As String
    cmd = "python C:\Users\Ore\Desktop\bc_cam.py"
    wss.Run cmd, 0, False
End Sub

WScript.ShellのRunメソッドでPythonスクリプトファイルを実行しています。第三引数にFalseを指定するとコマンドはVBAとは非同期で実行されます。今回の用途ではTrueで同期実行だとカメラが起動したところで止まってしまい何もできなくなってしまうのでFalse一択です。

VBAを定期実行したままでExcelを終了すると良くないみたいなので、Workbook_BeforeCloseイベントで自動更新停止をさせます。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call stopTableRefresh
End Sub

メインプロシージャで自動更新開始とPythonスクリプト実行をコールするようにします。

Sub main()
    Call executePy
    Call startTableRefresh
End Sub

メインプロシージャをSTARTボタン、自動実行停止プロシージャをSTOPボタンに配置して完成です。

全体像は次のようになります。

Option Explicit
Public tm As Date

Sub startTableRefresh()
    Worksheets("buf").Range("a1").ListObject.QueryTable.Refresh
    tm = Now + TimeValue("00:00:05")
    Application.OnTime tm, "startTableRefresh"
End Sub

Sub stopTableRefresh()
    On Error Resume Next
    Application.OnTime tm, "startTableRefresh", Schedule:=False
End Sub

Sub executePy()
    Dim wss As Object
    Set wss = CreateObject("WScript.Shell")
    Dim cmd As String
    cmd = "python C:\Users\Ore\Desktop\bc_cam.py"
    wss.Run cmd, 0, False
End Sub

Sub main()
    Call executePy
    Call startTableRefresh
End Sub

VBA版デモ動画

実際にうごかしてみるとこんな感じです(動画・音がでます)。

5秒インターバルで更新のはずなのにシートへの反映が速すぎる気もして、Pythonと同時にファイルをつかんでいる疑惑は拭いきれませんが結果オーライです。

それ、VBAでやる必要なくね?

はい。ないです。ここまでの環境がそろっているならPythonだけでやった方がいいです。

悩みどころはVBA版ではExcelが全部やってくれていたUIをどうやるかですが、PythonにもGUIアプリを作成するモジュールがあり、一番有名どころを使ってみたのですが、Javaのそれを彷彿とさせる手続きだらけのコードでPythonらしさのかけらもなく「あ~、この分野はあまり力を入れられてないんだな」感がひしひしと伝わってきました。

ということで、今回はHTMLを使ってブラウザで表示させます。

システム概要は次のとおりです。

bc_vba_python5.png

WEBサーバーは標準モジュールのhttp.serverを、データベースはSQLiteを使用します。

データベースはこのような構造です。

bc_vba_python6.png

Pythonスクリプトはインターネッツから拾ったコードをちりばめてお送りします。

import cv2
import winsound
from pyzbar.pyzbar import decode
import sqlite3
import json
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
import threading


def cam_capture():
    cap = cv2.VideoCapture(1)
    font = cv2.FONT_HERSHEY_SIMPLEX
    barcodes = []
    global conn

    while cap.isOpened():
        ret, frame = cap.read()

        if ret:
            d = decode(frame)

            if d:
                for barcode in d:
                    barcodeData = barcode.data.decode('utf-8')

                    if barcodeData not in barcodes:
                        barcodes.append(barcodeData)
                        winsound.Beep(2000, 50)
                        font_color = (0, 0, 255)

                        cur = conn.cursor()
                        cur.execute('UPDATE bookshelf SET stock=True WHERE isbn = ?', (barcodeData,))
                        conn.commit()
                        cur.close()
                    else:
                        font_color = (0, 154, 87)

                    x, y, w, h = barcode.rect
                    cv2.rectangle(frame, (x, y), (x + w, y + h), font_color, 2)
                    frame = cv2.putText(frame, barcodeData, (x, y - 10), font, .5, font_color, 2, cv2.LINE_AA)

        cv2.imshow('BARCODE READER  press q -> exit', frame)

        if cv2.waitKey(1) & 0xFF == ord('q'):
            break

    cap.release()


class HttpHandler(BaseHTTPRequestHandler):

    def do_GET(self):
        with open('template.html', mode='r', encoding='utf-8') as html:
            response_body = html.read()
            self.send_response(200)
            self.send_header('Content-type', 'text/html; charset=utf-8')
            self.end_headers()
            self.wfile.write(response_body.encode('utf-8'))

    def do_POST(self):
        global conn
        cur = conn.cursor()
        cur.execute('SELECT * FROM bookshelf')
        rows = []

        for r in cur.fetchall():
            rows.append({'title': r['title'], 'isbn': r['isbn'], 'stock': r['stock']})

        cur.close()
        self.send_response(200)
        self.send_header('Content-type', 'application/json')
        self.end_headers()
        response_body = json.dumps(rows)
        self.wfile.write(response_body.encode('utf-8'))


def run_server():
    global server
    server.serve_forever()


server = ThreadingHTTPServer(('localhost', 8080), HttpHandler)
conn = sqlite3.connect(r'D:\bookshelf\book.sqlite', check_same_thread = False)
conn.row_factory = sqlite3.Row
httpd = threading.Thread(target=run_server)
capture = threading.Thread(target=cam_capture)
httpd.start()
capture.start()
capture.join()
conn.close()
server.shutdown()

我ながら美しくないコードですが、労力対効果の観点から妥協しました。

Python版解説

要点を解説しますと、まずWEBサーバーとカメラの2つのループが同時に走る必要があるので、threadingで同時進行させています。

run_server関数で起動したWEBサーバーへのリクエストはHttpHandlerインスタンスが捌きます。これはBaseHTTPRequestHandlerクラスを継承していて、GETリクエストにはdo_GET、POSTリクエストにはdo_POSTというように、それぞれのリクエストに対応したメソッドが実行されるようにできています。なのでこれらのメソッドをオーバーライドして自分の好きな処理をやらせることができます。

GETリクエストにはdo_GETメソッドで次のtemplate.htmlを読み込みレスポンスします。

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    BootstrapCDN読み込み(省略)
    <script>
        $(document).ready(function () {
            setInterval(function (){
                getJson()
            },1000)
        })

        function getJson() {
            $.ajax({
                url: 'http://localhost:8080',
                type: 'POST',
                dataType: 'json',
            }).then(
                function (data) {
                    let elem = '<tr><th>タイトル</th><th>ISBN</th><th>在庫</th></tr>'
                    $.each(data, function (key, item) {
                        let bc
                        let stock
                        if(item.stock === 1){
                            bc = 'bg-success'
                            stock = 'OK'
                        }else{
                            bc = 'bg-danger'
                            stock = 'NG'
                        }
                        elem += '<tr>'
                        elem += '<td>' + item.title + '</td>'
                        elem += '<td>' + item.isbn + '</td>'
                        elem += '<td class="' + bc + '">' + stock + '</td>'
                        elem += '</tr>'
                    })
                    $('#bs').html(elem)
                })
        }
    </script>
    <title>本棚</title>
</head>
<body>
<div class="container">
    <table class="table table-striped table-dark" id="bs">
    </table>
</div>
</body>
</html>

スクリプトを実行してhttp://localhost:8080へブラウザでアクセスすると次のようなページが表示されます。

bc-vba_python7.png


POSTリクエストにはdo_POSTメソッドでSQLiteデータベースへ問い合わせた結果をJSONに変換してレスポンスします。

template.htmlは仕込まれたJavaScriptによりAjaxで1秒インターバルでWEBサーバーへPOSTを送信し続けています。これによりデータベースのテーブルをSELECTした結果が1秒おきにHTMLのテーブルとして表示されます。BootstrapのCDNから読み込まれるjQueryはAjaxが使えないSlim版なので、そこだけ通常版のCDNに書き換える必要があります。

cam_capture関数はVBA版と同じうごきで、バーコードデータの出力先がテキストではなくデータベースになっただけです。新規データならデータベースをUPDATEしています。

なおsqlite3.connectの引数にcheck_same_thread = Falseを渡しておかないと、このような複数スレッドからcursorを取得しての操作ができないハマりポイントがあるのでご注意ください。

私がやってみた限りでは双方からの同時アクセスによる競合は発生しませんでしたが、SQLiteなので起こりえるのではないでしょうか。本気でやるならPostgreSQLとかにした方がいいでしょう。

WEBサーバーはserve_foreverメソッドで立ち上げたあと、shutdownメソッドを呼ばないと終了できないのですが、カメラと一緒に終わらせないといけないのであれこれ考えた結果、capture.start()でカメラのループを走らせたあとcapture.join()でcam_capture関数が終了した(=Qキーでループを終わらせた)ことを検知できるので、そのあとにserver.shutdown()を呼んでいます。これに関係してserverはグローバルなスコープに置いています。もっとスマートにできそうですけどね・・・。

わざわざshutdownメソッドを呼ばなくてもスクリプトを終了させればサーバーもシャットダウンされる説がありますが、それだとexit code -1が返ってきて気持ち悪いのでこうしています。

Python版デモ動画

実際にうごかしてみるとこんな感じです(動画・音がでます)。

すでにお気づきかと思いますが、どう見てもバーコードの数より多く認識して(ビープ音がなって)います。キャッシュ配列を覗いてみると、あさっての数値が記録されていました。カメラにもよるかもしれないですが、解析性能はそれほど高くないようです。読み取ったデータが正しいかを検証するステップはあったほうがいいですね。QRコードの方が速く正確に読み取れます。

【宣伝】QRコードをたくさん作りたい場合は、こちらをご利用ください。

まとめ

PythonのUIはHTMLでも問題ないですね。表示するだけの用途であればなおさらです。個人的な感想ですがGUIモジュールを使うよりもHTMLの方がまだ直感的に作れました。

後から思いついたんですが、読み取りの度にいちいちDBへアクセスしなくても、最初にDBからSELECTしたあとは配列などで保持しておいて終了時にDBへ書き戻すようにしても良さそうですね。

それとWeb-Socketあたりで通信するようにできればもっとエレガントになりそうです。気が向いたらやってみたいです。


そして、改良版ができました。

追記

最近(2021.10)今までカメラを使うときに引っ張り出していたノートPCを新調しVAIOの30万円のフラッグシップモデルにしました。今までもVAIOの2in1タイプだったんですけどね。

vaio.jpg

がっ、なんとカメラがクソ過ぎてバーコードが読めなくなりました。残念ながら貧弱インカメラではなかなか厳しいようです。このグレードのノートPCでダメなら大半はダメですね。今まで普通に読めてたのは2in1のメインカメラだからでした。

ということで、ノートPCのインカメラ程度のおもちゃだと今回のネタが使えない可能性があるのでご注意ください。WEBカメラやっすいし別で買ってもいいですね。

ところで、どうですこのVAIOのキーボード。

vaiokb.jpg

日本語配列かななし隠し刻印です。かっこよすぎ。実際にはこれよりもっと沈んだ色で少し離れると無刻印に見えます。これにしたくて別にそんな性能はいらないのに、最上位機種にして+5万くらい余分にかかりました。

ノートPCで日本語配列かななしキーボードが選べる時点で選択肢がほぼVAIO以外なくなるんですよね。かのデザイン料が価格の5割を占めているようなMacでさえキーボードはかなありです。どう考えてもかななしのほうがスタイリッシュでスタバでドヤる層に需要があると思うので、なんならデフォルトでかななしにしても誰も怒らないと思うのですが。

私はスタバに足を踏み入れる可能性はゼロですが、かななしキーボードのためだけにVAIOにしてます。

おわり。

related pages
ExcelシートからQRコードを大量生成する
シートの値からPythonで100個オーダーのQRコード生成。

ExcelでQRコードを作成することはできますが、標準の作成方法は使い勝手がイマイチです。PythonにQRコードを扱うモジュールがないわけがないと調べてみると、当然あるわけで、じゃあ、ExcelシートをPythonでQRコードにしちゃえば良くね?となり、今回のツールが完成しました。

Read More ...
VBAでファイルを開くのが遅すぎるのでPythonで無理矢理に高速化する
VBAとPythonを連携させてファイル読み込み高速化。

我々VBAerにとっては周知の事実ですが、Excel-VBAで時間がかかる処理と言えば、「ワークシートへのアクセス」と「ファイルを開く」です。Pythonでデータ取得してVBAでExcelへ取り込むことでファイルを開く処理を高速化します。

Read More ...
ExcelをPostgreSQLへ変換するPythonスクリプト
ExcelシートをPostgreSQLのテーブルへ変換。

ExcelシートをSQLiteへ変換するスクリプトを作りましたが、完全にアプリの中にデータベースを組み込めないのであれば、素直にサーバー型のデータベースにした方がいいですね。ということで私はPostgreSQL派なので、先のスクリプトをPostgreSQL用に書き換えました。

Read More ...
この記事の
作成日

2021-06-26

更新日

2021-10-09

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園