エクセルからのSqlite3のDB読み込み（ODBCなし）とサジェスト検索

はじめに

おそらくですが大企業では、会社の在庫数・在庫金額・出荷数量統計
回転日数等・粗利等々を瞬間に見れるような端末用ソフトを各営業が
使われているのではないかと思います。

私は中小企業勤務の為、そんな便利なシステムを導入してくれるわけでもなく、
基幹のオラクルのRDBMSよりクライアントソフトにて定期的に吐き出される各種の
CSVデータをソートや加工して、必要なデータを参照してました。

いちいち各種データをそれぞれ別々のCSVで閲覧・整理・加工しないといけないのも
非効率ですし、エクセルでシート毎に各種データのCSVを貼り付けてVlookで
参照するのも、データ量が増えてくるとメモリを無駄に使いますしエクセル関数が
動作するだけで数分待つような事が起こります。

※実際に私が使ってたエクセルファイルは単体で200mb超えており、
　開くだけで数分かかっていた状況でした。）

やりたい事

１：SQlite3のデータベースをエクセルで読み込む、ODBCなし

２：DQファイルからのリスト読み込みと、部分一致によるグーグルのようなサジェスト検索の実装

３：サジェスト検索を元に同じリストの他のカラムの値（価格や在庫数値など）を参照する。

　　例：商品名の一部など部分一致のキーワードで候補を出して、選択できるよう
　　　　にするまた参照元のリストはSQlite3のデータベースから読み込む

具体的な方法

１：参照元になるCSVデータをSQlite3形式のDBに変換する
　　※事務所用PCのような低スぺPCでも扱えるように
　　　何十MBあるようなVlook用の参照元データのシートを付けない

２：エクセルのADOやOBDCなどのアドオンを使わない
　　※PC知識がない事務員さんでも使えるように、アドオンは使わない

参考にしたもの

●参考：ExcelからODBCなしでSQLiteを操作する(設定編）
　　https://qiita.com/hisayuki/items/9b42624790ba74a2fb35

●参考：ExcelからODBCなしでSQLiteを操作する(実践編１）
　　https://qiita.com/hisayuki/items/cd1b6d7bd1a8293647c8

●参考：エクセルでグーグルサジェストっぽい入力をする
　　http://suugleblog.blogspot.com/2012/02/blog-post_4988.html

使うエクセル用ライブラリ（ライブラリというかマクロですが）

●SQLite For Excel Version 1.0
　　https://github.com/govert/SQLiteForExcel

●最新バージョン
　　https://github.com/govert/SQLiteForExcel/releases/tag/1.0

●上記ライセンス
　　https://github.com/govert/SQLiteForExcel/blob/master/License.txt

動作環境

現在の使用環境
　　WINDOWS10　64BitPro
　　Office2019　64Bit
　　Python3.8

準備

●ファイルツリー構造
　　必要なファイル構成は以下を参照

SQLiteForExcel
　│  　ChangeLog.txt
　│  　sqlite3.dll
　│  　SQLite3_StdCall.dll
　│  　SQLiteForExcel_64.xlsm
　│ 　
　├─DataBase　(SQlite3のDBファイルは自分はここに入れてます。)
　│　Sample.db
　│
　├─x64_64BitOS_ライブラリ(OSの種類に合わせて、階層上位の”sqlite3.dll”を入れ替える)
　│　sqlite3.dll
　│
　└─x86_32BitOS_ライブラリ(OSの種類に合わせて、階層上位の”sqlite3.dll”を入れ替える)
 　　　sqlite3.dll

データベース元ネタ

以下の2次配列データをSQlite3のDBへ変換
テスト商品マスタ.xlsx

●以下のコードで上記エクセルをSQlite3のデータベースに変換
　　Pythonのコードにて上記の元ネタのエクセルファイルをSQlite3のデータベースの
　　ファイルに変換します。
　　※先に、変換先のDBを置くパスに”Sample.db”という名前で、テキストファイルの
　　　拡張子を変えてデータフレーム格納先のDBファイルを作っておいてください。

Excel2SQlite3.py

import sqlite3
import pandas as pd

print("●エクセルファイルをSQLDBへ変換を開始します。")
dbpath = 'D:/Sample.db'
conn = sqlite3.connect(dbpath)

print("●商品マスタの変換開始")
df = pd.read_excel('D:/テスト商品マスタ.xlsx',encoding="cp932",dtype = 'object')


#検索キーを追加
df.insert(0, '検索キー', df['商品名'] + ':' + df['商品コード'])
print(df)

#テーブルを追加
df.to_sql("Master", conn, if_exists="replace")
del df

conn.close()

●Sample.db
検索キーが1列目のカラムにできました。
この検索キーの部分一致（JANコードか商品名の一部）したときに候補をサジェストする
ようなマクロをエクセルで組みます。また、ここで格納された”Sample.db”については、
上記のツリーのようにファイルを配置してください。

エクセルファイルの準備１：ツリー構造

エクセルファイルでマクロを作成して、以下のツリーになるようにしてください。
●シートの種類
　　Sheet1(検索)　　　＜検索キーを検索する為のプルダウンメニュー操作用のシート
　　Sheet2(リスト用)　＜検索一致した候補のリストを回す為の作業用シート
●標準モジュール
　　Sqlite3　＜これは上記のSQLite For ExcelV1.0を開いて、中身のエクセルファイルを
　　　　　　　　使ってください。

●実際のエクセルVBAのエクスプローラー表示のツリー

エクセルファイルの準備２：検索窓と名前の定義

エクセルファイルの準備３：マクロの記述

Sheet1(検索)に対して、以下のマクロを記述します。

Private Sub Worksheet_Change.vba

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Volatile  '値に変更があった場合のみ実行

    Dim testFile As String
    Dim RetVal As Long
    Dim myDbHandle As LongPtr
    Dim myStmtHandle As LongPtr
    Dim InitReturn As Long

'SQL実行分の入力用変数
    Dim SqlOrderSet As String

'SQL各指定用の変数
    Dim SqlTableOrder As String
    Dim SqlRecordOrder As String
    Dim SqlSearchKey As String

'検索候補リストのワークシート指定用
    Dim LWs As Worksheet

Select Case Target.Address
Case "$B$3"

'描画と計算をオフ
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


Range("B3").NumberFormatLocal = "@"

'特定の箇所が変更掛かったら、更新スタート

    If Intersect(Target, Range("B3")) Is Nothing Then
        Exit Sub
    Else

        Worksheets("リスト用").Cells.ClearContents

'DBのイニシャライズ
        InitReturn = SQLite3Initialize

        If InitReturn <> SQLITE_INIT_OK Then
            Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError
            Exit Sub
        End If

'パスの指定
        testFile = ActiveWorkbook.path & "\DataBase\" & "Sample.db"

'DBへの接続
        RetVal = SQLite3Open(testFile, myDbHandle)
        Debug.Print "SQLite3Open returned " & RetVal

'SQL構文のセット
        SqlTableOrder = "Master"
        SqlRecordOrder = "検索キー"
        SqlSearchKey = Range("B3")
        SqlOrderSet = ("SELECT * FROM " & SqlTableOrder & " WHERE " & SqlRecordOrder & " LIKE '%" & SqlSearchKey & "%'")

        RetVal = SQLite3PrepareV2(myDbHandle, SqlOrderSet, myStmtHandle)
        Debug.Print "SQLite3PrepareV2 returned " & RetVal

'SQL命令分の実行
        RetVal = SQLite3Step(myStmtHandle)
        Debug.Print "SQLite3Step returned " & RetVal

'選択された明細数分のデータコラム行を指定してを抜き出し
        r = 1
        Do While RetVal <> SQLITE_DONE

            Worksheets("リスト用").Cells(r, 1).Value = SQLite3ColumnText(myStmtHandle, 1)
            Worksheets("リスト用").Cells(r, 2).Value = CStr(SQLite3ColumnText(myStmtHandle, 3))

            RetVal = SQLite3Step(myStmtHandle)
            r = r + 1
        Loop

        RetVal = SQLite3Finalize(myStmtHandle)
        Debug.Print "SQLite3Finalize returned " & RetVal

        RetVal = SQLite3Close(myDbHandle)

'プルダウンメニュー用の名前の再定義
        Set LWs = Worksheets("リスト用")
        LWs.Range(LWs.Cells(1, 1), LWs.Cells(LWs.Cells(Rows.Count, 1).End(xlUp).row, 1)).Name = "リスト"

    End If

'描画と計算をオン
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

'リストをプルダウンさせる。
    Worksheets("検索").Range("B3").Select
    SendKeys "%{DOWN}"



End Select

End Sub

※検索窓や検索するファイルを増やしたい場合は、上記の”Case”を増やす事によって対応します。

使い方

検索窓にキーワードの一部を入れるとその条件と部分一致する検索キーを自動で拾ってきます。
以下のような感じです。