Docurainを使えばExcelファイルをテンプレートとして、多彩な帳票やレポートを出力できます。報告書やレポートなどでは、グラフが使われることが多いです。Excelでレポートを作成したことがあれば、そのデータを使ってグラフを埋め込んだことがある方も多いでしょう。
今回はDocurainを使って動的に値を変更したグラフを作成します。グラフを使うことでビジュアル的にも、訴求力が向上したレポートを作成できるでしょう。
今回のテンプレート
今回はExcelで提供されているテンプレート「個人予算表」をベースにしました。
テーブルを範囲に変換
このExcelファイルでは、テーブルが利用されています。テーブルは記事執筆現在（2021年9月）、Docurainでは対応していません。このファイルをそのまま使おうとすると、次のようなエラーが返ってきます。
{ "type":"TEMPLATE_BROKEN_ERROR", "location":{ "col":-1, "row":-1, "sheet":"unknown" }, "message":"sheet 現在の月 has table - Excel table is not supported" }
そこで、テーブルの部分（今回のExcelファイルでは、収入/支出/預金の表部分）をそれぞれ選択して 範囲に変換 を選択します。そうすることでテーブルがなくなり、テンプレートとして利用できるようになります。
テンプレートの埋め込み
1行目およびA列に、空の行および列を追加します。そして、A1に次のように書きます。
#set($e=$ROOT)
こうすることで ${e.value} のようにして値を書き込めるようになります。今回は次のような内容としています。
API呼び出し時に送信するJSONの構造を説明すると、次のようになります。
{ // 収入の欄 "income": [ // 数字の配列 ], // 支出の欄 "expense": { // 支出のカテゴリ "home": { "date": "2021-10-31", // 日付 "value": 100000 // 金額 }, // 車関係が3種類あったので、さらにグルーピング "car": { // 車関係の支出のカテゴリ "loan": { "date": "2021-9-30", // 日付 "value": 25000 // 金額 }, // 省略 }, // 省略 }, // 預金の欄 "saving": [ // 日付と金額の配列 { "date": "2021-09-02", // 日付 "value": 10000 // 金額 }, // 省略 ] }
収入の欄の例
たとえば、収入の欄であれば1行目は次のように記述します。
| 項目 | 金額 |
|---|---|
| 収入源 1 | $!{e.income[0]} |
ここで $! としているのは、もしJSONデータに該当する項目がなかった場合には、テンプレート文字列を消すためです。
支出の欄の例
支出の欄は次のようになっています。各項目ごとにhome、mobileといったキーを付け、その中に date（日付）とvalue（金額）があります。
| 項目 | 期日 | 金額 |
|---|---|---|
| 家賃/住宅ローン | $!{e.expense.home.date} | $!{e.expense.home.value} |
こちらも $! として、値が存在しなかった場合には表示を消すようにしています。
預金の欄の例
預金の欄は次のようになっています。支出と似ていますが、配列になっているのが違うポイントです。
| 日付 | 金額 |
|---|---|
| $!{e.saving[0].date} | $!{e.saving[0].value} |
集計部分について
レポートの上部では、各金額の合計値が表示されています。この部分はJSONからデータを送るのではなく、Excel上に出力した値を集計して表示させます。
範囲への名前付け
この時 =SUM(D19:D31) のようにすると、Excel上は正しいのですが、Docurainで変換すると1行ずれてしまいます。これは、1行目がレポート変換時に使われないので、その分がずれるためです。
そこで、収入/支出/預金のそれぞれの金額部分を選択して、範囲に名前を付けます。今回はそれぞれ Income 、 Expense 、 Saving と名前を付けています。
集計行の計算
名前付けを行ったら、それぞれ次のように計算処理を書きます。なお、この集計行の項目にはあらかじめ名前が付いています。
| 項目 | 計算式 | 名前 |
|---|---|---|
| 1ヶ月の収入合計 | =SUM(Income) | TotalMonthlyIncome |
| 1ヶ月の支出合計 | =SUM(Expense) | TotalMonthlyExpenses |
| 1ヶ月の貯蓄合計 | =SUM(Saving) | TotalMonthlySavings |
現金残高の項目は、この名前を使って次のように計算されています。
=TotalMonthlyIncome-TotalMonthlyExpenses-TotalMonthlySavings
グラフの修正
今回、グラフは2つあります。円グラフと棒グラフがあるのですが、円グラフの方は修正不要です。それに対して、棒グラフはうまく出力されないのが分かります。この修正方法を紹介します。
値を別シートに出力する
円グラフが正しく出せているのは、表示されているシートとは別に、グラフデータ用のシートの値を使っているからです。シートを右クリックして「シートの再表示」を選択すると、グラフデータというシートが隠れているのが分かります。このシートを表示してみましょう。
このシートは次のようになっています。
各項目は値が入っていないのでエラーになっていますが、計算式は次のようになっています。
| A | B | |
|---|---|---|
| 2 | グラフデータ | |
| 3 | ||
| 4 | =MIN(1-B5,1) | |
| 5 | =MIN(TotalMonthlyExpenses/TotalMonthlyIncome,1) | |
| 6 | =(TotalMonthlyExpenses/TotalMonthlyIncome)>1 |
そして、円グラフはこの値を使って作成されています。
そこで、棒グラフについても同じように、このグラフシートに値を書き出して利用します。今回はC4/C5セルに、次のように入力します。
| A | B | C | |
|---|---|---|---|
| 2 | グラフデータ | ||
| 3 | |||
| 4 | =MIN(1-B5,1) | =TotalMonthlyIncome | |
| 5 | =MIN(TotalMonthlyExpenses/TotalMonthlyIncome,1) | =TotalMonthlyExpenses | |
| 6 | =(TotalMonthlyExpenses/TotalMonthlyIncome)>1 |
そして、棒グラフの データの選択 にて、次のように範囲を変更します。
='グラフ データ'!$C$4:$C$5
最後にグラフデータシートを再度非表示にして、テンプレートは完成です。
レポートを作成する
では実際にこのテンプレートを使ってレポートを作成してみます。今回はRubyを利用しましたので、ここからはRubyがインストールされているものとして進めていきます。また、APIはインスタントAPIを使っています。
利用するライブラリのインストール
HTTP用のライブラリを含め、次のライブラリを使っています。
- faraday
- faraday_middleware
- ruby-filemagic
適当なディレクトリでGemfileを作成します。
$ bundle init Writing new Gemfile to /path/to/dir/Gemfile
このファイルを編集します。今回は次のように変更しています。
# frozen_string_literal: true
source "https://rubygems.org"
git_source(:github) {|repo_name| "https://github.com/#{repo_name}" }
# gem "rails"
gem "faraday"
gem "faraday_middleware"
gem "ruby-filemagic"
そしてライブラリをインストールします。
$ bundle install
スクリプトの作成
適当なファイル（今回はupload.rbとしています）を作成し、ライブラリを読み込みます。
require 'faraday' require 'faraday_middleware' require 'ruby-filemagic'
DocurainのAPIを実行する処理
まず、Docurainの実行部分を作成します。ここからは api_call 関数の内容です。
# 帳票レンダリング（インスタント）APIコール def api_call # この中に書きます end
まず必要な変数を準備します。
token = 'YOUR_API_TOKEN' # Docurain API トークン out_type = 'pdf' # 出力形式 template_path = './template.xlsx' # テンプレートファイルパス entity_json = open('./entity.json').read # テンプレート置き換え文字列のJSON path = "/api/instant/#{out_type}" # APIのパス
次にテンプレートファイルのコンテンツタイプを取得します。
# テンプレートファイルのContent-Typeを取得 template_content_type = FileMagic.new(FileMagic::MAGIC_MIME).file(template_path, true)
これらの情報を使ってリクエストボディを作ります。
# リクエストボディの作成 params = { template: Faraday::FilePart.new(template_path, template_content_type), entity: Faraday::ParamPart.new(entity_json, 'application/json') }
後はFaraday（HTTPクライアント）を使って、APIを実行します。
# APIを実行する conn = Faraday.new(:url => 'https://api.docurain.jp') do |builder| builder.request :multipart builder.adapter :net_http end # 認証ヘッダーの設定 conn.headers['Authorization'] = "token #{token}" # 実行結果（レスポンス）を返す conn.post(path, params)
これでAPI呼び出し処理の完成です。関数全体の内容は次のようになります。
require 'faraday' require 'faraday_middleware' require 'ruby-filemagic' # 帳票レンダリング（インスタント）APIコール def api_call token = 'YOUR_API_TOKEN' # Docurain API トークン out_type = 'xlsx' # 出力形式 template_path = './template.xlsx' # テンプレートファイルパス entity_json = open('./entity.json').read # テンプレート置き換え文字列のJSON path = "/api/instant/#{out_type}" # APIのパス # テンプレートファイルのContent-Typeを取得 template_content_type = FileMagic.new(FileMagic::MAGIC_MIME).file(template_path, true) # リクエストボディの作成 params = { template: Faraday::FilePart.new(template_path, template_content_type), entity: Faraday::ParamPart.new(entity_json, 'application/json') } # APIを実行する conn = Faraday.new(:url => 'https://api.docurain.jp') do |builder| builder.request :multipart builder.adapter :net_http end # 認証ヘッダーの設定 conn.headers['Authorization'] = "token #{token}" # 実行結果（レスポンス）を返す conn.post(path, params) end
レスポンスを判定する
次にレスポンスの内容を判定して、レポートをファイル出力します。ここからは res_handle 関数の内容です。
# レスポンスハンドリング（適宜必要なハンドリングを行ってください） def res_handle(res) # この中に書きます end
まずHTTPステータスが200（正常終了）以外の場合はエラーメッセージを出して終了します。
if res.status != 200 # エラー判定 puts res.body # エラーメッセージを出力して終了 return end
次にレスポンスのContent-Typeから、出力するファイルの拡張子を指定します。
# 正常時、カレントディレクトリにファイル保存 # content-typeから拡張子に変換 extensions = { 'application/pdf' => 'pdf', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' => 'xlsx', 'application/vnd.ms-excel' => 'xls', 'image/svg+xml' => 'svg', 'image/png' => 'png', 'image/jpeg' => 'jpg', 'image/gif' => 'gif', } content_type = res.headers['content-type'] ext = extensions[content_type]
後はファイル名（今回は test に固定）を決めて、ファイル書き出します。
# ファイル名を決めて書き出し file_name = "test.#{ext}" filePath = "#{Dir.pwd}/#{file_name}"; File.binwrite(filePath, res.body) # メッセージを出力 puts "saved : #{filePath}"
この関数の全体像は次のようになります。
# レスポンスハンドリング（適宜必要なハンドリングを行ってください） def res_handle(res) if res.status != 200 # エラー判定 puts res.body # エラーメッセージを出力して終了 return end # 正常時、カレントディレクトリにファイル保存 # content-typeから拡張子に変換 extensions = { 'application/pdf' => 'pdf', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' => 'xlsx', 'application/vnd.ms-excel' => 'xls', 'image/svg+xml' => 'svg', 'image/png' => 'png', 'image/jpeg' => 'jpg', 'image/gif' => 'gif', } content_type = res.headers['content-type'] ext = extensions[content_type] # ファイル名を決めて書き出し file_name = "test.#{ext}" filePath = "#{Dir.pwd}/#{file_name}"; File.binwrite(filePath, res.body) # メッセージを出力 puts "saved : #{filePath}" end
実行する
ではこの2つの関数をつないで実行します。
res = api_call # APIコール res_handle(res) # レスポンスハンドリング
実行すると、次のように結果が出力されます。
$ ruby upload.rb saved : /path/to/test.xlsx
実際に開いてみると、値が入力されて、グラフにも反映されているのが分かります。
PDFでも同様です。
まとめ
グラフを使うことで、レポートがより信頼性の高い、可読性の高いものになります。クライアント向けに定期的に発行するレポートの中で利用したり、システムの管理画面でグラフ付きレポートを出力したりするのに使えるでしょう。
DocurainはWeb APIを介して利用するので、プログラミング言語を問わず、品質の高いレポート出力が可能になります。ぜひお試しください。
