本格的な帳票の作成（2）
テンプレートセル
さてここまで、二重の波カッコで名称をつけることで値を設定してきました。これを正式には「テンプレートセル」と呼びます。
2020年5月現在、セルに対するテンプレートとして、次のものが用意されています。
- データテンプレート
- 式テンプレート
- フォーミュラ
- 関数テンプレート
- イメージテンプレート
- インラインテンプレート
- ワークシートテンプレート
ここからは寄り道をしつつ、いくつか実際に試しながら帳票を仕上げていきましょう。なお今回は、「イメージテンプレート」と「インラインテンプレート」については触れません。
式テンプレート
請求明細の小計のように、動的に増加する行に対して式を適用したい場合に利用します。
今回はDBから直接適用しているので本来は不要ですが、ハンズオンが目的なので式テンプレートでの実現方法も見てみましょう。
まずは式テンプレートです。セル「F13」に次のように入力して実行してください。ここで実行結果は載せませんが、先と同じ結果が得られるはずです。
{{=Invoice.UnitPrice*Invoice.OrderQty*(1-E13)}}
DioDocsでは二重の開始波カッコに続いて「=（イコール）」を指定して式をテンプレートとして扱うことができます。
次の通りに通常のExcel式を指定すると、No列と同じように行方向に拡張せず、1行だけ式が適用される結果となります。
=C13*D13*(1-E13)
以下がその実行結果です。
小計行の挙動が少し理解しにくいですが、挿入モードに「EntireRowColumn」が指定されているためでしょう。
1行目にだけExcel式の計算結果が適用され、そのすぐ下に「税抜き価格」や「消費税」などが表示されているのが見て取れます。そして拡張された行の先に、値が適用されていない「データテンプレート」が表示されています。恐らくセルテンプレートの配置をロードした後に値を設定しながら行の拡張が行われているため、このような挙動になるのでしょう。
このようにExcel式では複数行に拡張されるセルに対し対応することができません。
そこで式テンプレートを利用することで、式テンプレートから参照している値（または参照しているセルに設定されている値、上の例ではE13）に従い複数行に対応することができるようになります。
関数テンプレート
続いて「税抜き価格」もテンプレートを用いて解決してみましょう。「税抜き価格」のセル（F14）に次の値を設定して実行してください。正しい結果が得られるはずです。
{{=SUM(F13)}}
式テンプレートと同様に、通常のExcel関数を指定した場合、参照先であるF13の行方向への拡張に対応できません。関数テンプレートを利用することでDioDocsのセルの拡張に応じた関数の記述ができるようになります。
フォーミュラ
Excel関数を利用するもう1つの方法がフォーミュラです。式テンプレートや関数テンプレートとの最大の違いは、実行後のExcelに関数の実行結果が適用されるか、関数式そのものが適用されるかにあります。フォーミュラは後者です。
まずは実際に見てもらいましょう。「No」列のセル（A13）に次の値を設定して実行してください。
{{==(ROW()-12)(C=B13)}}
やっとNo列が正しく表示されましたね。これで帳票としても使えるものになりました。
さて、フォーミュラについて解説しましょう。フォーミュラは二重の開始波カッコに続いて、二重のイコール記号を記述します。
{{==(ROW()-12)(C=B13)}}
式テンプレートや関数テンプレートは関数の実行結果が適用されますが、フォーミュラの場合、関数式そのものが適用されると先ほど説明しました。では実際に見てみましょう。C#のコードを次のように修正し、PDF以外にExcelも出力してみましょう。
workbook.Save("Invoice.pdf", SaveFileFormat.Pdf);
workbook.Save("Invoice.xlsx", SaveFileFormat.Xlsx);
実行後に出力されたExcelを開き、A13とF13のセルの中身を確認してみてください。
左がフォーミュラで、右が式テンプレートの実行結果です。それぞれ、次のような違いがあります。
| セルテンプレート | 説明 |
|---|---|
| フォーミュラ | 式そのものがExcelに設定される |
| 式テンプレート | 式の実行結果が設定される |
| 関数テンプレート | 関数の実行結果が設定される |
式や関数はDioDocs上で実行した結果がExcelに設定されます。
今回はPDFを出力しているのであまり差がないように感じるかもしれませんが、Excelファイルを配布したい場合など、値ではなく式を設定したい際には、フォーミュラを利用する必要があります。
また、No列ではROW関数を利用していますが、2020年4月現在、関数テンプレートとしてROW関数がサポートされていません。式テンプレートや関数テンプレートで実行するためには、DioDocs側で同等の実装が必要になります。徐々に増えてはいくのでしょうが、DioDocsでサポートされていない関数などを利用したい場合も、フォーミュラを利用すると良いでしょう。
さて、フォーミュラの記述をもう一度見てみましょう。
{{==(ROW()-12)(C=B13)}}
後半に謎の記述「C=B13」がありますね。これは「コンテキスト」つまり文脈を指定しています。コンテキストを指定せず、以下の通り記述したとします。
{{==(ROW()-12)}}
すると実行結果は次のようになります。
先のフォーミュラでは、複数行を含む値が参照されていないので、行方向に拡張されません。しかしNo列には別にROW関数で取得する値以外は必要ありません。そこでコンテキストを利用します。
{{==(ROW()-12)(C=B13)}}
このように記述することで、「B13つまり製品名の文脈に従いなさい」と指示できます。製品名は行方向に拡張されるので、それに従ってNo列も同じ行数拡張されます。
なお文脈はこれ以外にグルーピングなどにも利用しますが、本ハンズオンでは省略いたします。公式のサンプルを参考にしてください。
ワークシートテンプレート
ここまで単票を生成してきましたが、ワークシートテンプレートを利用することで帳票を一括生成することが可能になります。
まずデータベースから値をロードしているクエリに、次の2つの変更を加えます。
- 複数のSalesOrderの取得
- SalesOrderID列の取得
複数の請求書を生成するため、その分のSalesOrderHeaderとそれに紐づくデータを取得する必要があります。
また、それらがどの請求のデータか判別するため、SalesOrderIDを取得します。以下の通り修正しましょう。
use AdventureWorks;
select
SalesORderHeader.SalesOrderID,
GETDATE() as InvitationDay,
DATEADD(DAY, 30, GETDATE()) as DueDate,
Address.PostalCode,
ISNULL(StateProvince.Name, '') + ' ' + ISNULL(Address.City, '') + ' ' + ISNULL(Address.AddressLine1, '') + ' ' + ISNULL(Address.AddressLine2, '') as CustomerAddress,
Store.Name as StoreName,
Person.FirstName + ' ' + Person.LastName as CustomerPerson,
SalesOrderHeader.SubTotal,
SalesOrderHeader.TaxAmt,
SalesOrderHeader.Freight,
SalesOrderHeader.TotalDue,
Product.Name as ProductName,
SalesOrderDetail.UnitPrice,
SalesOrderDetail.OrderQty,
SalesOrderDetail.UnitPriceDiscount,
SalesOrderDetail.LineTotal
from
Sales.SalesOrderHeader
inner join Person.Address
on SalesOrderHeader.BillToAddressID = Address.AddressID
inner join Person.StateProvince
on Address.StateProvinceID = StateProvince.StateProvinceID
inner join Sales.Customer
on Sales.SalesOrderHeader.CustomerID = Customer.CustomerID
inner join Sales.Store
on Customer.StoreID = Store.BusinessEntityID
inner join Person.Person
on Customer.PersonID = Person.BusinessEntityID
inner join Sales.SalesOrderDetail
on SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
inner join Production.Product
on SalesOrderDetail.ProductID = Product.ProductID
where
SalesOrderHeader.SalesOrderID <= 43661
SELECT句の先頭にSalesOrderIDが追加されているのと、WHERE句の条件がイコールから変更されているのが見て取れるかと思います。
続いてExcelのテンプレートを修正します。
複数の帳票を生成するため、ワークシートテンプレートを利用します。SalesOrder1つあたりに1ワークシートを割り当てるため、ワークシートのタブに次のように記述してください。
{{Invoice.SalesOrderID}}
それでは実行してみましょう。次のような複数ページのPDFが生成されるはずです。
驚くほど簡単に、帳票の一括生成が実現できてしまいました。
