Edited at

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)
' 1件読み込んで表示
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のちょっとよくわからない機能