LibreOffice Calcでデータベースのデータを取得する関数を作る

LibreOffice AdventCalendar 2018の3日目
前回は榎さん　LibreOfficeを使うときに知っていると便利な7つのこと。

弊社の商品cdから商品名と価格取ってきてよ。

弊社、商品数、10万件超えます。

1. 商品データを全てLibreOffice Calcのシートに取得
2. VLOOKUPで取り出す

出来なくはない。
10-20MBの巨大Calcファイル。
辛い。

下記のような関数を作れないだろうか？

• JDBC Driverでデータベースにアクセス
• 商品cdに該当するカラムのデータを取得

##あった！
そんな時に
LibreOffice(calc)マクロでMariaDB にアクセスするを見つけた。

実際にinformixでもやってみた。

環境

• Informix Database
• Informix JDBC Driver 4.10 JC12
• Java 11
• LibreOffice Calc 6.10
• Windows10 Pro x64

JDBC Driverの取得

1. 下記のサイトのProductsと書かれた項目の中に、Informix Java Database Connectivity Driverというリンクがある。
2. Informix Java Database Connectivity DriverをクリックするとDownloadsリンクが現れる。

DownloadsをクリックするとMyIBMでLoginする。
MyIBMは無料で取得可能。

IBM -Products - Informix Java Database Connectivity Driver

JDBC Driverのインストール

1. ダウンロードしたものを解凍します。
   • tarファイルはWindows標準では解凍できません。
   • 7zipを使うといいでしょう。
2. 解凍すると setup.jar が出てきます。
   • Windowsの場合はsetup.jarをダブルクリックするとJDBCのインストーラーが起動します。
   • LinuxやMacでCUIの場合は下記の通りに行います。
   • インストールフォルダ: /opt/IBM/Informix_JDBC_Driver

java -jar setup.jar -i console

LibreOfficeにJDBC Driverを登録

1. LibreOffice起動
2. ツール > [オプション] > [詳細]
3. Javaオプションの[クラスパス]ボタンを押す
4. クラスパスで[アーカイブを追加]を押す
5. C:\opt\IBM\Informix_JDBC_Driver\lib の中のjarファイルを全て追加

これでJDBC Driverが使えるようになりました。

関数を書く

今回はVBAで書きます。
そのCalcファイルだけ動くユーザー定義関数にします。

1. LibreOffice Calcを起動
2. ツール > マクロ > マクロの編集
3. ファイル名.ods > Standard の中にマクロを書きます。

Function GetItem(ID As Long, Column As String)

    Dim DatabaseContext as Object
    Dim DataSource as Object
    Dim Connection as Object
    Dim Statement as Object
    Dim sSQL as String
    Dim oResultSet as Object
    Dim nDlgResult As Integer
    Dim sURL as String
    Dim oProps(4) as new com.sun.star.beans.PropertyValue
    
    ' DB コネクションのオープン
    DatabaseContext=createUnoService("com.sun.star.sdbc.DriverManager") 
    sURL = "jdbc:informix-sqli://192.168.11.1:1526/dbname:INFORMIXSERVER=servername;"
    oProps(0).Name = "user"
    oProps(0).value = "username"
    oProps(1).Name = "password"
    oProps(1).value = "password"
    oProps(2).name = "JavaDriverClass"
    oProps(2).value = "com.informix.jdbc.IfxDriver"
    oProps(3).name = "DB_locale"
    oProps(3).value = "ja_JP.932"
    oProps(4).name = "NEWCODESET"
    oProps(4).value = "MS932,sjis-s,932"
    Connection = DatabaseContext.getConnectionWithInfo(sURL, oProps())

    ' SQL select 実行
    Statement = Connection.createStatement()
    oResultSet = Statement.executeQuery("SELECT "+Column+" FROM 商品m where 商品cd="+ID)
    ' １件読み込んで表示
    oResultSet.Next
    GetItem=oResultSet.getString(1)
    'Msgbox(oResultSet.getString(1))

    ' DB コネクションのクローズ
    Statement.Close()
    Connection.Close()
    Connection.Dispose()

End Function

これを保存します。

使ってみる

該当の商品cdと出力したいカラムを入力すると該当するデータが出てくる関数を作りました。
使ってみます。

Calcのセルでこう入力してみましょう。

=GetItem(1,"商品名1")

セルにこんな風に表示されます

素敵な商品

感想

1,2件ぐらいならいいが、1000件取得とかになると非常に重い。(´;ω;｀)
大量の商品リストを作るのは向かない。

やっぱり、REST JSONじゃないとダメかな…。

参考文献

LibreOffice(calc)マクロでMariaDB にアクセスする

次回、12月4日は野方さんLibreOfficeのちょっとよくわからない機能。