ExcelシートからQRコードを大量生成する

シートの値からPythonで100個オーダーのQRコード生成。

Excel(MS-Office)には2013あたりから標準でバーコードを作成するライブラリがあります。このライブラリでシートに入力された値をQRコードにできます。さわったことがあるかたはご存じかと思いますが、このバーコード作成ライブラリ(Microsoft bar code control)はイマイチ使い勝手がよろしくないのです。

msbcctrl.png


VBAには荷が重いQRコード作成

少量を作る場合なら、このライブラリでも問題にはなりません。が、大量に必要な場合間違いなく心が折れます。VBAでループを使って生成することも可能かと思われますが、何しろ出力がシート上のコントロールというトンデモ仕様なので、取りまわしがすこぶる悪いです。何よりShapeをはじめとした、セル外に存在する物体が私は大嫌いです。

そもそも大量にQRコードが必要な場面というのが、ニッチな気もしないでもないですが、CordovaなどでQRコードを読み取るスマホアプリを簡単に作成できるご時世ですので、例えば職場の備品管理で、備品に貼ったQRコードをスマホでバンバン読んでいくだけで、どれがあってどれがないというのが一覧になる、なんてのはそんなに難しいことをしなくても実現できます。PCに外付けでリーダーを取り付ければExcel-VBAで処理することも容易です。

実際、私も似たようなことをやろうとして「ExcelでQRコード作成問題」にぶち当たったわけです。

Pythonには余裕のQRコード作成

さて、どうしようかということでPythonにQRコードを扱うモジュールがないわけがないと調べてみると、当然あるわけで、じゃあ、ExcelシートをPythonでQRコードにしちゃえば良くね?となり、今回のツールが完成しました。

QRコードを作成するWEBサービスは多々ありますが、大量生成は想定されていません。本ツールはExcelをベースにすることで、管理番号などを連番で大量にQRコードにしたい場合、オートフィル、コピペ、関数といったExcel上の機能を総動員して簡単に作成できます。すでに管理台帳がExcelで整備されていれば、そのデータを丸ごと流用できます。

Python標準モジュール以外に、Excelファイルの扱いは神ツールOpenPyXLで、QRコード生成にはqrcodeモジュール、画像処理はpillowモジュールを使っています。

使用方法

このツールはAPI方式です。当方のサーバー上でデータを処理しますので、業務でご利用の場合は、所属組織のセキュリティポリシーをよくご確認の上ご利用ください。まぁ、表向きはExcelだけで作成したことにしておけば良いのです。わかりゃしません。

1.指定フォーマットのExcelファイルを作成します。

  • xlsx形式のファイル
  • シートは1枚だけ

A列にはQRコードに変換する文字列、B列には名称が入ります。C列は名称を表示するか否かのフラグとなります。
1行目にヘッダーを設定します。以下の画像を参考にしてください。ヘッダーはQRコードへ変換時に無視されます。わかりやすいようにご自身で適当な項目名としてください。

説明で使用しているファイル(saitama_city.xlsx)をリンクから入手できます。これを書き換えてお使いいただくのが簡単です。

qr-excel0.png



2.データを入力します。

  • A列にはQRコードに変換する文字列(重複不可、必須)
  • B列には名称(30文字以下、省略化)

以下の画像を参考にしてください。データ数の上限は1000です(ヘッダーを含め1001行目まで)。ファイルサイズは2MBまでです。

qr-excel1.png

完成したQRコードとの対応はこのようになります。

qr-excel3.png


名称(青枠部)はデフォルトでは表示しません。表示する場合はC2セルに「y」でも「1」でもなんでもいいので値を入れてください。値が存在することが名称表示のフラグとなります。

qr-excel2.png


3.このページ下部のアップロードフォームからファイルをアップロードしてください。

xlsx形式のみ受け付けます。データ数により、応答に少し時間がかかることがあります。


4.アップロードしたファイルをサーバーのスクリプトが処理した結果がZIPファイルとしてダウンロードされます。

展開すると中身はシートへ入力したデータをQRコード画像へ変換したPNGファイルになっています。

qr-excel4.png


画像は用途に応じてご自由にお使いください。
※ファイル名はQRコードの文字列ですが、禁則文字が含まれている場合は_(アンダーバー)に置き換わっています。

サーバーサイドスクリプトは次の通りです。HTTP周りを書き換えればローカルのPython環境でうごかすこともできます。APIが使えない方はお試しください。

このサイトでは1,000枚で数秒、リミッターを外してローカル環境で10,000枚を作成したところ30秒程度かかりました。画像処理があるのでちょっと重くなりますね。

import io
import re
import qrcode
import zipfile
import openpyxl as excel
from PIL import ImageFont, ImageDraw
from django.http import HttpResponse
from django.shortcuts import render


def create_qr_code(request):
    if 'xlsx' in request.FILES:
        try:
            file = request.FILES['xlsx']
            if file.size > 2000000:
                raise ValueError
            wb = excel.load_workbook(file, data_only=True)
            ws = wb.get_active_sheet()
            name_visible = ws['c2'].value is not None
            zip_stream = io.BytesIO()
            img_zip_file = zipfile.ZipFile(zip_stream, 'w')
            font_size = 14
            font_name = '/fonts/ipa-gothic/ipag.ttf'
            font = ImageFont.truetype(font_name, font_size)

            for cnt, row in enumerate(ws.rows):
                if cnt > 0:
                    if cnt > 1000:
                        break
                    code_str = str(row[0].value)
                    name = row[1].value
                    img = qrcode.make(code_str)
                    draw = ImageDraw.Draw(img)
                    if name_visible and name is not None:
                        draw.text((10, 10), str(name)[:30], font=font)
                    img_stream = io.BytesIO()
                    img.save(img_stream, 'PNG')
                    img_zip_file.writestr(
                        re.sub(r'[\\/:*?"<>|]+', '_', code_str)
                        + '.png', img_stream.getvalue())
                    img_stream.close()

            img_zip_file.close()
            response = HttpResponse(
                zip_stream.getvalue(),
                content_type='application/zip'
            )
            response['Content-Disposition']\
                = 'attachment; filename="qr_code_images.zip"'
            zip_stream.close()
            return response
        except:
            pass

    return render(request, 'api_err.html')

ローカル版のご案内

どこの馬の骨ともわからないサーバーにファイルをアップしたくないという方にはPythonソースコードから実行できるセットをご用意しています。基本的な使用方法はAPI版に準じます。

アップロード

利用上のご注意
  • このAPIを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • 送信されたデータはサーバーへ保存されることはありません。
  • 送信されたデータは成果物生成のためだけに利用され、他の用途に利用されることはありません。
ファイルを選択してください。(.xlsx)

アップロードボタンが押下された時点で注意事項に同意したものとみなします。

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

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

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

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

Read More ...
NCMBにPythonでファイルをアップする
NCMBのREST-APIをPythonから利用します。

NCMBはmBaaSです。スマホアプリのバックエンドとしての機能は当然そろっていますが、REST-APIが用意されており、スマホからに限らずHTTPでアクセスできればデータを出し入れできます。すなわちPythonからだろうとVBAからだろうと利用できます。

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

2019-09-29

更新日

2020-01-31

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