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'D:\bookshelf\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-VBA側です。まず定数の定義とPythonが出力したファイルを読みに行く処理を作ります。

Enum col
    isbn = 1
    bookTitle
    stock
End Enum

Const BUF_FILE_PATH As String = "D:\bookshelf\buf.txt"
Public tm As Date

Sub importText()
    Dim buf As String
    Dim barcodes() As Double
    
    If Not Dir(BUF_FILE_PATH) = "" Then
        Open BUF_FILE_PATH For Input As #1
            Dim i As Long
            i = 0
            
            Do Until EOF(1)
                Line Input #1, buf
                ReDim Preserve barcodes(i)
                barcodes(i) = buf
                i = i + 1
            Loop
        Close #1
        
        Call markStock(barcodes)
        Kill BUF_FILE_PATH
    End If
    
    tm = Now + TimeValue("00:00:05")
    Application.OnTime tm, "importText"
End Sub

Pythonが出力したファイルを読み取り、barcodes配列に格納していきます。このbarcodes配列は後ほど作成する、シートへの展開処理markStockへ渡します。

読み取りが終わったファイルはただちに削除します。こうすることで、ファイルを作成するのはPython、削除するのはVBAとなり、もしファイルが存在したならば、それは新しいデータがあるのと同義になります。いちいちファイルを開いて新しいデータが存在するか否かを判定する手間がなくなります。

このimportTextプロシージャは一定間隔で自動実行したいので、インターネッツで拾ってきたコードを仕込み5秒ごとに実行されるようにしています。

そして自動実行を止めるためのプロシージャも用意します。

Sub stopImportText()
    Application.OnTime tm, "importText", Schedule:=False
End Sub

続いてシートへの展開処理を作ります。

Sub markStock(bc() As Double)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    Dim lr As Long
    lr = ws.Range("a1").CurrentRegion.Rows.Count
    
    Dim c
    
    For Each c In bc
        Dim r As Long
        
        On Error Resume Next
        r = WorksheetFunction.Match(c, Range(ws.Cells(1, col.isbn), ws.Cells(lr, col.isbn)), False)
        
        If Error.Number = 0 Then
            ws.Cells(r, col.stock).Value = "○"
        End If
        
        On Error GoTo 0
    Next
End Sub

バーコードデータはimportTextプロシージャからDoubleの配列でやってくるので、それをFor Eachで取り出し、WorksheetFunction.MatchでシートのテーブルのISBN列と比較して、テーブルに存在すれば行番号がわかるので、その行のstock列に○を書き込みます。

WorksheetFunction.Matchは範囲に存在しない検索値を渡すとコケるので、エラーハンドラは必須です。

次にPythonスクリプトをVBAから実行するプロシージャを作成します。

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

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

最後にメインのプロシージャです。

Sub main()
    Call executePy
    Call importText
End Sub

メインプロシージャを呼ぶstartボタンと自動実行停止プロシージャを呼ぶstopボタンをシートに配置して完成です。

bc_vba_python4.png

全体像は次の通りです。

Option Explicit

Enum col
    isbn = 1
    bookTitle
    stock
End Enum

Const BUF_FILE_PATH As String = "D:\bookshelf\buf.txt"
Public tm As Date

Sub main()
    Call executePy
    Call importText
End Sub

Sub importText()
    Dim buf As String
    Dim barcodes() As Double
    
    If Not Dir(BUF_FILE_PATH) = "" Then
        Open BUF_FILE_PATH For Input As #1
            Dim i As Long
            i = 0
            
            Do Until EOF(1)
                Line Input #1, buf
                ReDim Preserve barcodes(i)
                barcodes(i) = buf
                i = i + 1
            Loop
        Close #1
        
        Call markStock(barcodes)
        Kill BUF_FILE_PATH
    End If
    
    tm = Now + TimeValue("00:00:05")
    Application.OnTime tm, "importText"
End Sub

Sub stopImportText()
    Application.OnTime tm, "importText", Schedule:=False
End Sub

Sub markStock(bc() As Double)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    Dim lr As Long
    lr = ws.Range("a1").CurrentRegion.Rows.Count
    
    Dim c
    
    For Each c In bc
        Dim r As Long
        
        On Error Resume Next
        r = WorksheetFunction.Match(c, Range(ws.Cells(1, col.isbn), ws.Cells(lr, col.isbn)), False)
        
        If Error.Number = 0 Then
            ws.Cells(r, col.stock).Value = "○"
        End If
        
        On Error GoTo 0
    Next
End Sub

Sub executePy()
    Dim wss As Object
    Set wss = CreateObject("WScript.Shell")
    Dim cmd As String
    cmd = "python D:\bookshelf\bc_cam.py"
    wss.Run cmd, 0, False
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あたりで通信するようにできればもっとエレガントになりそうです。気が向いたらやってみたいです。


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

おわり。

related pages
コーディングを加速するキーボード
PCに付属してきたキーボードは窓から投げ捨てろ!

昨今はスマホのフリック入力の方が速いなんて人も多いと思いますが、オフィスワーカーであればキーボードを全くさわらないというわけにはいかないと思います。事務所のキーボードはPC本体にくっついてきたものをそのまま使っているケースが大多数でしょう。ノートPCであれば、もうキーボードを外付けしてまで使うなんてことは皆無だと思います。

Read More ...
結合セルで集計できない表を何とかする
人間にしか読めない表をPCで使えるように正規化します。

セルの結合は使ったその瞬間から「データとしての価値」がなくなります。Excelは結合されたセルが同じ値をグループ化したものだとは認識しません。集計などの計算をしたり、データベースとして使用したりするためには表が正規化されている必要があるのです。

Read More ...
Excelファイルから文字列を取り出す
神エクセルから文字列だけを頂戴します。

Excelから文字列だけを取り出したい!という需要はあまりないと思いますが、以前、画像を取り出すツールを作りましたので、どうせならと作ってみました。原理は画像取り出しツールと基本同じですが、文字列はXMLファイルに埋め込まれており単純にファイルをごそっと抜き取るだけでは使い物になりません。

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

2021-06-26

更新日

2021-07-04

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