kintoneのデータをエクセル帳票で表示させる仕組み

今回はkintoneのデータをエクセル帳票で表示させる方法について解説してみます。

何故それをしようと思ったのか?

お客様でkintoneを使われている組織がいくつかありますが、よく課題として出るのは「帳票」でした。

もちろん、プリントクリエイターやレポトンなどのプラグインは検討するものの、レコードの要件が合わなかったり、ユーザがレイアウト調整をしたりするのが難しかったりするため、プラグインの利用を見送るケースがしばしばありました。

そこで、普段使い慣れたエクセル帳票に、ダイレクトにkintoneのデータを表示することで、その課題を解決しました。

エクセルを使ってkintoneにアクセスする方法

kintoneからのデータ取得にはエクセルVBAからkintoneのAPIを利用します。

大まかな流れとしては図のようになります。

仕組みとしては以上になりますが、ユーザ目線で言えば、コードは完全に隠蔽されており、「データ表示用シート」で、kintoneアプリ上のデータを一意に特定する主キーを入力すれば、自動的にデータが表示される形になります(Worksheet_Calculate イベントで①の処理を走らせます)。

データ表示用シートは通常のエクセルファイルとして自由に編集できるので、ユーザが要件に合わせて柔軟にレイアウトを組むことが出来ます。

コードの一部を公開します

一部になりますが、具体的なコードを公開します。データ構造等が分かってしまうので、ところどころ端折っているので(定数・変数宣言など)、参考として読んで下さい。コードハイライトがVBに対応していないため読みにくいです。

なお、JSONのパースについては、vba-jsonというクラスライブラリを拝借しております。

Public Sub getAllData()

    Dim ret As Boolean
    Dim strKey As String
    
    '--------------------------------------------------------------------
    '  変数定義
    '--------------------------------------------------------------------
    Dim objHttpReq As MSXML2.XMLHTTP60 ' XMLHTTP オブジェクト
    Dim strJSON As String              ' レスポンスで受け取るJSONデータ
    Dim strURL As String               ' アクセス先URL
    Dim strQuery As String             ' 検索文字列
    Dim strSubDomain As String         ' サブドメイン
    Dim strAppId As String             ' アプリID
    Dim strLogin As String             ' ログイン情報
    Dim objJSON         As Object      ' JSONオブジェクト
    Dim wsMe As Worksheet              ' 自ワークシートオブジェクト
    
    Set wsMe = Worksheets("〇〇〇データ")
    '〇〇番号で検索
    strKey = wsMe.Cells(1, 2).value

    '--------------------------------------------------------------------
    '  接続情報
    '--------------------------------------------------------------------
    strSubDomain = Worksheets("システム設定").Cells(2, 2).value
    strAppId = APP_CONTRACT '
    strLogin = Worksheets("システム設定").Cells(5, 2).value
    
    '------------------------------------------------------------------
    ' 検索文字付きURLを作成する
    '------------------------------------------------------------------
    strQuery = "KEY=""" & strKey & """"
    strQuery = URL_Encode(strQuery)
    strURL = "https://" & strSubDomain & ".cybozu.com/k/v1/records.json?app=" & strAppId & "&query=" & strQuery
       
    '------------------------------------------------------------------
    ' XMLHTTP オブジェクトを生成する
    '------------------------------------------------------------------
    Set objHttpReq = CreateObject("MSXML2.XMLHTTP")
    objHttpReq.Open "GET", strURL, False
    '------------------------------------------------------------------
    ' XMLHTTP のリクエストヘッダーを指定する
    '------------------------------------------------------------------
    ' ログイン認証
    objHttpReq.setRequestHeader "X-Cybozu-Authorization", strLogin
    ' Basic 認証
    'objHttpReq.setRequestHeader "Authorization", "Basic " & <ベーシック認証情報>
    'キャッシュ対策(常にレスポンスが取得できる状態にする)
    objHttpReq.setRequestHeader "If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT"
    
    '------------------------------------------------------------------
    ' リクエストを送信する
    '------------------------------------------------------------------
    objHttpReq.send (Null)
    
    '------------------------------------------------------------------
    ' レスポンスを取得する
    '------------------------------------------------------------------
    'レスポンス情報を変数に格納する
    strJSON = objHttpReq.responseText
    
    Set objJSON = parseJSON(strJSON)


    '取得したレコードからフィールドの値をシートに書き込み

    Dim record As Variant
   
    For Each record In objJSON("records") '主キー指定なので、1行or0行の結果となる。
   
        With wsMe
            .Cells(lngRow, COL_○○○○).value = record.Item("○○○○").Item("value")
            .Cells(lngRow, COL_××××).value = record.Item("××××").Item("value")           
            
        End With
        lngRow = lngRow + 1
        i = i + 1

    Next record

    If i = 0 Then
        MsgBox "該当データがありません。KEY:" & strKey
        Exit Sub
    End If

End Sub

このコードは対応するアプリごとに作成する必要があるため、導入時は若干煩雑ですが、一度作ってしまえば再利用可能です。

参考サイト

cybozu developer network
 
第2回 Excelとkintoneを連携させよう
https://developer.cybozu.io/hc/ja/articles/201932564-%E7%AC%AC2%E5%9B%9E-Excel%E3%81%A8kintone%E3%82%92%E9%80%A3%E6%90%BA%E3%81%95%E3%81%9B%E3%82%88%E3%81%86
(著者:株式会社ジョイゾー 四宮靖隆)第1回ではExcelからkintoneに接続し、レコードデータを取得するところまでを紹介しました。 今回は、取得したレコードデータの取り扱い方法を紹介したいと思います。 前回も書きましたが、kintoneから取得したデータはJSON形...

残念ながらセキュリティホールもあるため、利用時には注意が必要です

エクセル利用者がいちいちkintoneのIDとパスワードを入力しなくても済むように、このケースではエクセルブックの中にアクセス用のトークン(文字列)を埋め込んでいますが、このトークンはIDとパスワードをBASE64エンコードしているだけなので、簡単にデコード出来てしまいます。

万が一、エクセルファイルが外部に流出してファイルを解析されてしまうと、kintoneに不正アクセスが出来てしまいます。

今のところこのリスクを回避するには、運用で気を付けるしかないのが悩ましいところです。

この辺は公開鍵方式で暗号化するなどして、仮に流出したとしても簡単にデコード出来ない仕組みの実装をしてほしいところであります。

まとめ

普段使い慣れたエクセルを使ってkintoneのデータにアクセス出来るのは使い勝手の向上につながります。

例えば、軽微なレイアウト変更などがあったとしても、普通にエクセルを使える人であれば簡単に対応することも可能です。

kintoneユーザで、エクセル帳票を使ってみたいという方はご相談ください。個別に対応させていただきます。

ITと経営に関するお悩みはありませんか?

ITに関連する経営課題についてどんな内容でもアドバイス可能です。
お一人で悩まれずに、些細なことだと思われることであっても、お気軽にご相談ください。
もちろん、お問い合わせメールへは無料で対応いたします。