マクロを使って帳票のメンテナンス性を高めましょう！
帳票では細かなカスタマイズが不定期に求められるので、メンテナンス性高い状態に維持するのが大事なポイントになります。そのためにできることは幾つかありますが、一つはコピー&ペーストで場当たり的な対応をしないことです。
例えば自社の情報やヘッダー情報、フッターに記載した連絡先に関する情報などを全ての帳票に直書きしていると、いざその情報が変わった時に変更漏れが発生したり、幾つも修正する手間が発生します。かといって、それをシステム側から出力するのも面倒でしょう。
そこで使って欲しいのがDocurainのマクロ機能です。共通処理を部品化し、再利用性を高めます。
続きを読む帳票はヘッダーとフッター、そして明細行の組み合わせで作成されます。それぞれ、次のような区別があるでしょう。
この時、多くの帳票では明細行に記載された金額を集計して、ヘッダーやフッターに表示します。明細行は帳票によって行数が異なるので、増減に合わせて計算範囲が変わります。さらに言えば、帳票が単ページ、または複数ページによっても変わります。
今回はDocurainにおける明細行の集計方法をいくつかのパターンに分けて紹介します。
今回は分かりやすくするためにヘッダー、フッターに数字以外の情報は載せていません。
データは次のようなJSONです。明細ごとに単価、数量が記載されています。項目数は可変です。
{ "items" : [ { "note": "商品1", "unit": 100, "amount": 5 }, { "note": "商品2", "unit": 500, "amount": 3 }, { "note": "商品3", "unit": 1500, "amount": 10 }, : ] }
帳票が必ず1ページの場合 #EASY_SHIFT_FORMULA を指定するのが最も簡単です。A1セルで次のように記述します。
#EASY_SHIFT_FORMULA #set($e = $ROOT) ## 変数用
#EASY_SHIFT_FORMULA を記述しておけば、数式の簡易自動シフトモードになり、自動で計算範囲を調整してくれるようになります。そして、明細部分を次のように記述します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 10 | 詳細 | 単価 | 数量 | 金額 | |
| 11 | #foreach($line in $e.items) | ||||
| 12 | ${line.note} | ${line.unit} | ${line.amount} | =D12*E12 | |
| 13 | #end |
さらにフッター部分の集計では次のように記述します。10%はExcelのパーセント表示を使っていますので、実際には0.1になります。
| A | B | C | |
|---|---|---|---|
| 14 | 小計 | =SUM(E12:E12) | |
| 15 | 税率 | 10% | |
| 16 | 集計 | =C14 + C14*F15 |
この状態で帳票を作成すると、明細部分では行ごとにD12（またはE）がD13やD14に自動的にシフトします。これが簡易自動シフトモードです。フッターについても =SUM(F12:F12) が =SUM(F10:F12) へ自動的に変更されます（明細が3つの場合）。もちろん、最後の計算についても =F13+F13*F14 となり、正しい計算結果が得られます。
複数ページの帳票（改ページがある帳票）を作成する場合には数式の簡易自動シフトモードは利用できません。範囲がページをまたぐので、どの範囲をシフトすれば良いのか自動では定義できないからです。そこで利用するのがスコープになります。
スコープはまず #scope("全て") 〜 #end の形で定義します。"全て" は自分で定義したものを利用できます。このスコープで区切られた範囲について、数式がシフトしたり、集計を行えるようになります。スコープは複数用いてネストさせられます。
では帳票を作ってみましょう。今回は1ページあたり10明細とします。そこでデータを10明細ごとに分割します。
#set($e = $ROOT) #set($pages = $e.items.chunk(10))
例えば items が5行分のデータを持っていたとして、chunk(3) と指定すると、 $pages は次のようなデータになります。
[ { items: [ {"note": "商品1", "unit": 100, "amount": 5}, {"note": "商品2", "unit": 200, "amount": 3}, {"note": "商品3", "unit": 300, "amount": 1} ] }, { items: [ {"note": "商品4", "unit": 400, "amount": 5}, {"note": "商品5", "unit": 500, "amount": 3} ] } ]
そして、この $pages を繰り返し処理しますが、この時、全体に対してscopeを定義します。
#scope("全て")
#foreach($page in $pages)
: 省略
#end
#end
そして、明細行を記述します。単ページの場合は $e.items でしたが、今度は $pages ごとに処理します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 14 | 詳細 | 単価 | 数量 | 金額 | |
| 15 | #foreach($line in $page) | ||||
| 16 | ${line.note} | ${line.unit} | ${line.amount} | =D16*E16 | |
| 17 | #end |
フッター行では DR.SHIFT_FORMULA を使います。これはDocurain独自の仕組みで、スコープに合わせた計算式のシフトを行うための記述です。この時のスコープとして、先ほど定義した名前を指定します。
| A | B | C | |
|---|---|---|---|
| 18 | 小計 | =DR.SHIFT_FORMULA(SUM(E16),"scope=全て, target=all") | |
| 19 | 税率 | 10% | |
| 20 | 集計 | =C19+C19*C20 |
target は all または nearest を指定します。 nearest はスコープ内の最近傍範囲になります。デフォルトは nearest です。
明細は単ページの時と同じく計算されます。そして、フッターでは次のように変換が行われます（一例です）。
=SUM(F10:F19,F28:F33)
ページごとの計算対象範囲を自動的に判別し、SUM関数に複数の範囲を指定してくれます。これによって正しい集計結果が得られます。
帳票全体の集計もありつつ、ページ単位でも小計を出したい場合はスコープを複数定義します。スコープ名はページごとにユニークなものになっていなければならないので、#scope("ページ-$foreach.count") のように変数を使って定義します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 13 | 詳細 | 単価 | 数量 | 金額 | |
| 14 | #scope("ページ-$foreach.count") | ||||
| 15 | #foreach($line in $page) | ||||
| 16 | ${line.note} | ${line.unit} | ${line.amount} | =D16*E16 | |
| 17 | #end | ||||
| 18 | #end |
そしてページ計を下に記述します。この時もスコープの範囲について、変数を用いて指定します。
| A | B | C | |
|---|---|---|---|
| 19 | ページ計 | =DR.SHIFT_FORMULA(SUM(E16),"scope=ページ-$foreach.count, target=all") |
このようにスコープを2つ使うことで、ページ内での集計と帳票全体での集計を同時に行えます。
単ページの場合は数式の簡易自動シフトモードで解決できるでしょう。複数ページの場合にはスコープとDR.SHIFT_FORMULAを利用してください。スコープを使いこなせば、複雑な帳票も作成できるようになるでしょう。
Docurainでは帳票を生成する際にApache Velocity（以下単にVelocity）というライブラリを利用しています。VelocityはJavaで開発されたテンプレートエンジンになります。テンプレートエンジンというのはテンプレートファイルと、データを組み合わせて別なファイルを生成する仕組みになります。つまりDocurainの場合は次のようになります。
今回はこのVelocityについて、Docurainで利用されている機能について解説します。Velocityを知ることで、Docurainの動作原理を知り、よりテンプレート作成がはかどることでしょう。
続きを読む業務システムをはじめとして、データを蓄積するシステムは最終的に何らかのレポートや帳票を出力します。特に業務システムにおいては、多くの帳票が作成されます。たとえば納品書、発注書、請求書などは有名な帳票ですが、各企業のワークフローに合わせてもっと多数の帳票が作成されるでしょう。
システム開発時において、帳票作成は若干独特なポジションになります。その多くがPDFやExcelファイルなどバイナリファイルとして出力されるため、一般的なGUIアプリケーションやWebシステムへの出力とは作法が異なるからです。その結果、次のような課題が生まれます。
昨今ではエンジニア不足が話題に上がることが増えています。自社開発のシステムで帳票出力を行う場合はもちろん、SIerにおいても専門知識を持った帳票デザインを行える人材を確保するのに苦労しているようです。PDFなどで出力される帳票はHTMLやJSONを出力するのに比べると複雑であり、専門的な知識が必要です。反面、常時帳票開発に関わるのは難しく、帳票に関する開発に関わる度に過去の経験であったり、新しい技術へのキャッチアップが必要になります。
PDFやExcelファイルなどに出力を行うシステムを開発する際には、一般的なHTMLやJSONで出力する場合と比べて煩雑になりがちです。専用のライブラリを使ったとしても、位置調整や細かな設定をプログラミングで行う必要があり、コーディング量が増えてしまいます。帳票のパターンは多いため、一つ一つの帳票に対して固有のコーディングが必要になります。そのため仕様変更に伴う修正が難しく、工数も増えやすくなります。
帳票出力に関わる開発はシステム開発の中でも中盤〜終盤になりやすいでしょう。そのごく一部の期間（全体の工数の1/10程度と言われます）だけ学んでコーディングを行っても、次の機会にはすでに忘れ去ってしまいます。その上、過去に記述した長大なコードだけが残されていると、それを思い出すのにも時間がかかります。技術も進歩しており、ライブラリがバージョンアップしていれば、ほぼ学び直しに等しい工数がかかる可能性があります。
一般的にこうした帳票システムの開発に携わった経験は、特定の要員に限られてしまいます。知識を共有するのが難しかったり、不定期に発生する作業では、引き継ぎも難しいでしょう。そのため、修正が必要になった時には特定の人しか作業できなかったり、その担当者が転職や転籍してしまうと知識が途絶えてしまいます。その結果、場当たり的な対応になったり、変更に大きな工数がかかるようになります。
そもそもDocurainは、開発者たちが過去に基幹系業務システム開発時に感じていた「帳票開発は退屈」であるという課題を解決するために生まれたという背景があります。帳票開発はきめ細かな配慮が必要な反面、技術的な面白みは少ないとされています。開発者によっては苦痛すら感じるでしょう。しかし、誰かがやらねばなりません。
前述の通り、一つの業務システム開発プロジェクトにおいて、その工数の1/10程度が帳票開発に使われると言われています。そこには帳票のデザイン、生成プロセスの選定、コーディング、テストなどが含まれます。帳票では商品名の長さや数字などに表示上の制限がある中で、あらゆるパターンに対応しなければなりません。99.999%のデータは問題なくとも、たまたま発生したイレギュラーなデータによって帳票が崩れてしまった経験を持つ方も多いでしょう。そうした問題にあらかじめ対応する必要があるため、帳票の開発フェーズは工数が増えがちです。
とはいえ、大多数なシステム開発において帳票開発はメイントピックではありません。開発リソースを集中させるべきは、バックエンドシステムやフロントエンドの開発になるでしょう。帳票は最終的なアウトプットになるので、開発工程の最後部に回されやすく、限られたリソースで対応されることが多いようです。
私たちDocurainでは帳票システムをSaaSとして提供する中で、数多くのお客さま、そして開発現場からご意見を伺ってきました。ここで挙げたような課題は、多くのお客さまから聞いています。そこで私たちはサービス提供からさらに一歩踏み込み、帳票作成に関わるプロセスをまるごとアウトソースいただける体制を整えました。
Docurainを用いた帳票作成について、テンプレートの作成を含めてお任せいただけます。システム開発プロセスにおける帳票作成フェーズをすべてお任せいただくイメージです。実際のDocurainの呼び出し、データ生成に関するシステム開発はお客さまになります。
ここでDocurainチームを頼っていただくメリットを紹介します。
私たちのサービスを利用することで、システム開発における帳票システムの設計、帳票デザインの部分が丸々アウトソースできます。後はあらかじめ規定したフォーマットに合わせて、バックエンドシステムからDocurainのAPIを呼び出すだけで、PDFやExcel、画像といった様々なフォーマットで帳票を取得できます。
システム開発において帳票設計、出力に関する工数を削減できます。空いた工数をメインのシステム開発に回すことができます。現代のシステム開発ではクラウドを利用することが増えていますが、これはハードウェアの管理業務からエンジニアを解放し、そのリソースをさらに有益な面に当てることを可能にしました。1社でシステム開発の全フェーズに対応するのは前時代的とさえ言えるでしょう。帳票に関する開発はDocurainに任せてしまいましょう。
私たちは日々Docurainの開発を通じて、ドッグフーディングを続けています。不定期に発生する帳票のメンテナンス、新しい帳票の追加と言ったタスクに対して適切に対応可能です。教育や、Docurainの使い方を思い出すような作業は不要なので、スムーズな帳票更新が実現できます。
当たり前ですが、私たちはDocurainの専門家です。Docurainの仕様を知り尽くし、できることとできないことはもちろん、その使い方も熟知しています。Docurainは高い帳票開発効率を提供しますが、慣れるまでにある程度の工数がかかるのも事実です。そうした教育にかかる工数もなく、すぐに実用的な帳票設計に入ることが可能です。
不定期に発生する帳票の修正にも対応できますので、帳票開発の担当者が辞めてしまった、部署異動になってしまったといったリスクもありません。
新規のシステム開発はもちろん、既存の帳票ソリューションからの載せ替えでも問題ありません。帳票の専門家として、皆様のシステム開発における帳票システムの課題を解決いたします。ぜひお問い合わせください。
帳票では一部を目立たせたい時に、色を利用します。たとえば赤字やマイナスといった目を配るべきデータがあれば、赤文字の出力にするでしょう。同じように明細行で1行ずつ色を変えることで、見間違いを防いだりできます。
Docurainでこのように帳票の中での色付けを行いたい時には、条件付き書式を使うと便利です。今回は条件付き書式を使って、ガントチャート風の表示を実現してみました。
まずは完成版から紹介します。
仕様は次のようになっています。
実際のJSONデータは次のようになります。colorキーでr/g/b/yを使って色指定しています。startDateキーがタスクの開始日、endDateがタスクの終了日になります。
{ "baseDate": "2021/11/26", "tasks": [ {"title": "DB設計", "startDate": "2021/11/26", "endDate": "2021/11/30", "color": "r"}, {"title": "クラウド選定", "startDate": "2021/11/26", "endDate": "2021/12/02", "color": "b"}, {"title": "リポジトリ作成", "startDate": "2021/11/28", "endDate": "2021/11/28", "color": "y"}, {"title": "概要設計", "startDate": "2021/11/26", "endDate": "2021/12/03", "color": "g"}, {"title": "フレームワーク選定", "startDate": "2021/11/27", "endDate": "2021/11/29", "color": "r"} ] }
ではここからテンプレート側の記述について解説します。まず一番左の日付はJSONデータのbaseDateをそのまま出力しています。そして、そのセルに BASEDATE という名前を付けています。
${e.baseDate}
そしてその右側のセルから、順番に =BASEDATE + 1 、 =BASEDATE + 2 … といった具合に式を定義していきます。表示上は14日分で足りるのですが、計算処理の都合上、最後は =BASEDATE + 15 とします。
A1セルではJSONデータを扱いやすくするために $e を定義します。
#set($e = $ENTITY)
さらに数式の自動シフト（簡易）を利用するために #EASY_SHIFT_FORMULA も定義します。
#EASY_SHIFT_FORMULA
チャート部分は繰り返し処理になりますので、foreach を使います。
#foreach($task in $e.tasks) // この間の行でチャート部分を作成します #end
チャート部分はまず単純な出力を行います。色情報も出力していますが、画面の表示上は不要なので、白文字で出力しています。
| B | C | D | E |
|---|---|---|---|
| ${task.title} | ${task.startDate} | ${task.endDate} | ${task.color} |
チャート部分では、次のような計算式を定義しています。
実際の記述は次のようになります。 $ を使うことで縦方向（または横方向）コピーした際に計算式が別なセルを参照するのを防いでいます。
=IF(AND($C6<=F4,$D6>=F4),$E6,"")
こうすると、データを適用した結果が次のようになります。
| 11月26日 | 11月27日 | 11月28日 | 11月29日 | 11月30日 | 12月1日 | 12月2日 | ||||
|---|---|---|---|---|---|---|---|---|---|---|
| DB設計 | 2021/11/26 | 2021/11/30 | r | r | r | r | r | r | ||
| クラウド選定 | 2021/11/26 | 2021/12/2 | b | b | b | b | b | b | b | b |
| リポジトリ作成 | 2021/11/28 | 2021/11/28 | b | b | ||||||
| フレームワーク選定 | 2021/11/27 | 2021/11/29 | r | r | r | r |
ではいよいよ条件付き書式を設定します。条件付き書式のルールの管理を選択します。
そして、次のように設定をします。
| 項目 | 内容 |
|---|---|
| スタイル | クラシック |
| 対象 | 指定の値を含むセルだけを書式設定 |
| 特定の文字列 | |
| 次の値を含む | |
| y |
この時の書式設定をユーザ設定の書式とします。上記例のようなyであれば黄色で出力したいので、塗りつぶしとフォントカラーを同じ黄色に指定します。r（赤）/b（青）/g（緑）も同じように定義します。
書式設定の適用先として $F$5:$T$7 のようにしてforeachループの含まれる範囲を指定します。そうすると、先ほどのr/b/g/yといった文字列が入ったセルが、それぞれの色に置き換わって表示されます。
この条件付き書式はExcelだけでなく、PDFにも適用されます。PDFで出力すると、設定した通りにガントチャートが出力されるはずです。
条件付き書式を設定することで、配色の伴うレポートでも自在に出力できるようになります。なお、Excel上で細かく計算処理を行うと複雑度が増す可能性があるのであまりお勧めしません。出力するか否かを含めてデータ側で指定できるようにすると、テンプレートをシンプルに維持できるでしょう。
DocurainはExcelファイルをテンプレートとし、さらに描画するデータを与えて帳票やレポートを作成します。与えるデータフォーマットはJSONが一般的なのですが、CSVやTSVも利用可能です。
今回はCSVファイルを使った請求書作成について解説します。
今回利用するCSVファイルは次のようになっています（抜粋です）。企業名、個人名、住所、郵便番号はダミーで生成したものになります。
#no,date,companyname,accountname,address,zipcode,tel,note,item,price,total,tax,totalPrice 1,2021/11/30,有限会社 渚,吉田 浩,岐阜県藤本市斉藤町鈴木3-9-7,215-9066,080-8054-8454,"いつもお世話になります。 2021年11月分の請求書になります。",製品1,4000,14500,1450,15950 1,2021/11/30,有限会社 渚,吉田 浩,岐阜県藤本市斉藤町鈴木3-9-7,215-9066,080-8054-8454,"いつもお世話になります。 2021年11月分の請求書になります。",製品2,6000,14500,1450,15950 : 3,2021/11/30,有限会社 木村,青田 香織,山形県加藤市吉田町加藤9-6-7,167-2711,090-2125-2506,お世話になります。よろしくお願いいたします。,アイテム1,1300,9000,900,9900 3,2021/11/30,有限会社 木村,青田 香織,山形県加藤市吉田町加藤9-6-7,167-2711,090-2125-2506,お世話になります。よろしくお願いいたします。,アイテム2,1000,9000,900,9900 3,2021/11/30,有限会社 木村,青田 香織,山形県加藤市吉田町加藤9-6-7,167-2711,090-2125-2506,お世話になります。よろしくお願いいたします。,アイテム3,6700,9000,900,9900ｂ
ここで注意して欲しい点は以下になります。
# を最初に記述する# を最初に記述するたとえば次のようなCSVファイルがあったとします（#がないパターンです）。
no,date,name 1,2021-04-01,テスト 太郎 2,2021-05-01,テスト 花子 3,2021-06-01,テスト 次郎
これをDocurainのデータファイルとして適用した場合 $ENTITIES は次のように送られてきます。
[ ["no", "date", "name"], ["1", "2021-04-01", "テスト 太郎"], ["2", "2021-05-01", "テスト 花子"], ["3", "2021-06-01", "テスト 次郎"] ]
それに対して、最初に # を付けた場合の $ENTITIES は次のようになります。
[ { "no": 1, "date": "2021-04-01", "name": "テスト 太郎", }, { "no": 2, "date": "2021-05-01", "name": "テスト 花子", }, { "no": 3, "date": "2021-06-01", "name": "テスト 次郎", }, ]
後者のヘッダーを設定した場合、 $ENTITIES[0].no のようにアクセスできます。CSVにはヘッダーのような概念がないので、注意してください。
この後のテンプレート側の処理で紹介しますが、どこからどこまでが同じ帳票であるか区別できる必要があります。今回は no という列を持たせて、そこで次の帳票になった時に数字を変更しています。
CSVは多段構造は表現できません。そのため帳票ヘッダー、明細のように出力する際には、どちらの情報も一行の中に入れてしまうのがお勧めです。明細行の中に、ヘッダー情報も繰り返し入れてしまうことで、一部を取り出してヘッダー行情報として利用できます。つまり次のような形です。
#set($header = $ENTITIES[0])
今回はMicrosoft Officeで提供されている請求書テンプレートを利用します。
まずA1セルにて、テンプレートで利用するデータの変数を定義し、さらに帳票ごとにグルーピングします。
#set($e = $ENTITIES)
#set($pages = $e.chunk('no'))
$e.chunk('no') を実行すると、ある特定のカラム（今回はno）が同じデータは同じ配列グループになります。元データは $e は次のようになっていると考えてください。
[ { "no": 1, "date": "2021-04-01", "name": "テスト 太郎", }, { "no": 1, "date": "2021-05-01", "name": "テスト 太郎", }, { "no": 2, "date": "2021-06-01", "name": "テスト 花子", }, { "no": 2, "date": "2021-07-01", "name": "テスト 花子", }, { "no": 2, "date": "2021-08-01", "name": "テスト 花子", }, { "no": 3, "date": "2021-09-01", "name": "テスト 次郎", }, { "no": 3, "date": "2021-10-01", "name": "テスト 次郎", }, ]
このデータに対して $e.chunk('no') を実行すると、次のように変換されます。
[ [ { "no": 1, "date": "2021-04-01", "name": "テスト 太郎", }, { "no": 1, "date": "2021-05-01", "name": "テスト 太郎", } ], [ { "no": 2, "date": "2021-06-01", "name": "テスト 花子", }, { "no": 2, "date": "2021-07-01", "name": "テスト 花子", }, { "no": 2, "date": "2021-08-01", "name": "テスト 花子", } ], [ { "no": 3, "date": "2021-09-01", "name": "テスト 次郎", }, { "no": 3, "date": "2021-10-01", "name": "テスト 次郎", } ] ]
このようにグルーピングされれば、後はこのグループ毎に帳票を作成するだけです。
グルーピングできたら、そのグループ毎に処理を行います。つまり foreach を使います。$pageにはグルーピングされたCSV行が入ります。
#foreach($page in $pages) // この中で帳票の処理 #end
つまり最初の $page は以下のようになっていると考えてください。
[ { "no": 1, "date": "2021-04-01", "name": "テスト 太郎", }, { "no": 1, "date": "2021-05-01", "name": "テスト 太郎", } ]
帳票のヘッダーとして利用するデータを抽出します。1行目は必ず存在しますので、添え字として [0] を利用します。
#set($h = $page[0])
ヘッダー行に関するデータ、たとえば会社名などは ${h.companyname} で出力できます。
明細行も繰り返しになりますので、foreachを使います。
#foreach($item in $page) // この中で明細処理 #end
これで帳票のできあがりです。今回はデータの出力のみで、集計計算などは入れていません（CSVデータに元々入れています）。
CSVはJSONとはデータ構造が異なるので、その扱いに多少の工夫がいります。しかし、JSONよりも使い慣れている人が多いので、業務システムとの連携ではCSVの方が活躍するかも知れません。
皆さんの帳票作りの参考にしてください。
