はじめに
これまで何回かにわたり、グレープシティの「DioDocs」という製品を紹介してきました。
- .NET向けExcel・PDF操作ライブラリ「DioDocs」の衝撃！ その魅力に迫る
- .NET向けExcel・PDF操作ライブラリ「DioDocs」を使ったアプリケーション構築～究極に軽量な帳票生成環境を手に入れる～
- DioDocsとAzure Functionsで帳票生成サービスを作ろう！
2019年12月には新しいバージョンであるV3Jがリリースされ、非常に強力な「テンプレート構文」が新たに追加されました。
そこで今回は、このテンプレート構文を利用した、誰でも実施できるハンズオンテキストとして本稿を執筆しました。誰でもExcelのテンプレートファイルから、PDF帳票を簡単に生成することができます。
試用はユーザー登録なども必要なく、NuGetからインストールするだけで無料で利用できます。さぁ！ Visual Studioを開いて、記事を読みながらDioDocsを実際に体験してみましょう！
ハンズオンのゴール
データベースからデータを取得して、大量の帳票を一括生成することを可能とします。しかも、これ以上なく簡単に。
以下のようなテンプレートとなるExcelファイルを用意し
こんな簡単なコードを実行すると
using var command =
new SqlCommand(
File.ReadAllText("SelectInvoices.sql"),
connection);
using var dataTable = new DataTable();
dataTable.Load(command.ExecuteReader());
var workbook = new Workbook();
workbook.Open("Template.xlsx");
workbook.AddDataSource("Invoice", dataTable);
workbook.ProcessTemplate();
workbook.Save("Invoice.pdf", SaveFileFormat.Pdf);
以下の通り、複数ページのPDF帳票が簡単に生成できます。
DioDocsは、日々使い慣れているExcelのスキルに、ほんの少しDioDocsのエッセンスを身につけるだけで、PDF帳票が簡単に生成できるようになる魅力のライブラリです。
ハンズオン概略
対象者
本ハンズオンは以下の方を対象としています。
- 簡単なC#プログラムを書いたことがある
- 帳票生成に興味がある
- プログラムからExcelやPDFを操作すること興味がある
環境
本ハンズオンの環境は以下の通りです。
- Windows 10
- .NET Core 3.1
- Visual Studio 2019
- Docker Desktop
Dockerは帳票生成に利用するテストデータを取得するSQL Serverの実行に利用します。簡単なコマンドをいくつか叩くだけです。
誰でも利用可能な形で記載してあります。不慣れでもまったく問題ありません。
ハンズオンコンテンツ
ハンズオンの完成形はGitHub上で公開しています。
詰まった方は、適宜こちらを参考にしてください。
Hello, DioDocs!
ではハンズオンを開始します。まずはExcelからPDFを作成してみましょう。Visual Studioを起動して新しいプロジェクトを作成してください。
「コンソール」アプリケーションを検索し、その中から「コンソールアプリ（.NET Core）」でC#のプロジェクトテンプレートを選択し「次へ」を選択してください。
続いてプロジェクト名に「HelloDioDocs」と入力して、プロジェクトを「作成」します。この段階で「F5」キーを押してデバッグ実行し、次のように表示されることを確認しておきましょう。
動作しましたね？ ではDioDocsを試していきましょう！
まずはNuGetサーバーからDioDocsのライブラリをダウンロード・適用します。「HelloDioDocs」プロジェクトの「依存関係」を右クリックし、「NuGetパッケージの管理」を選択します。
続いて「参照」タブで「DioDocs」と入力して検索し、「Grapecity.DioDocs.Excel.ja」を選択したのち「インストール」します。バージョンはスクリーンショット上のものではなく、最新版を利用してください。
続いてテンプレートとなるExcelファイルを作成します。今回はベースとなるExcelファイルを用意してあります。以下からダウンロードしてください。
ダウンロードしたファイルをプロジェクト直下にコピーし、プロパティの「出力ディレクトリにコピー」を「新しい場合はコピーする」に変更してください。
では実際にDioDocsを使ってみましょう。以下がHelloDioDocsプログラムの全体です。
using GrapeCity.Documents.Excel;
namespace HelloDioDocs
{
class Program
{
static void Main(string[] args)
{
var workbook = new Workbook();
workbook.Open("Template.xlsx");
workbook.Worksheets[0].Range["B5"].Value = "Hello, DioDocs!";
workbook.Save("Invoice.pdf", SaveFileFormat.Pdf);
}
}
}
順に説明していきましょう。
まず先頭でDioDocsの名前空間に対してusingを宣言しています。
using GrapeCity.Documents.Excel;
続いてWorkbookオブジェクトを生成してExcelファイルを開きます。
var workbook = new Workbook();
workbook.Open("Template.xlsx");
そして開かれたワークブックの先頭のシートの「B5」セルに対して、文字列を設定します。DioDocsのオブジェクト構造はVBAやVSTOのオブジェクト構造と非常に近しくなっており、それらの経験がある方には理解しやすい設計となっていることが見て取れるかと思います。
workbook.Worksheets[0].Range["B5"].Value = "Hello, DioDocs!";
最後にInvoice.pdfという名称のPDFファイルで保存しています。
workbook.Save("Invoice.pdf", SaveFileFormat.Pdf);
以下がテンプレートとなったExcelファイルと、そこから生成されたPDFファイルです。
御中の左側、企業名の領域に「Hello, DioDocs!」という文字が設定された上でPDF化されていることが見て取れます。
C#とExcelの一般的な知識だけで、多くのことができそうだということを感じていただけたのではないでしょうか？
テンプレート構文
さて、先の例ではExcelに明示的に値を設定しに行きました。
これはこれで悪くはないのですが、ExcelのレイアウトとC#のプログラムが密結合してしまっており、セルを1つ下に移動したいといった、些細なレイアウト修正でもプログラムの改修が発生してしまいます。
こういったレイアウトとロジックを分離する手段として、一般的なユーザーインターフェイスを構築するためのアーキテクチャとしては、近年ではバインディングという手法がよく利用されます。.NETだとWPFなどで利用されていますよね。
DioDocs V3Jではデータ構造とレイアウトを分離するため、バインディングと類似した「テンプレート構文」が新たに導入されました。これは非常に強力な機能です。
過去の記事で、ExcelとC#コードを疎結合にするための実装例を紹介しましたが、今後は基本的にテンプレート構文を利用していくのが良いでしょう。
では早速テンプレート構文を利用して、ハンズオンを進めましょう。
データソース
では実際にテンプレート構文を利用していきましょう。そのためにはまず、「データソース」について理解する必要があります。
DioDocsのテンプレート構文ではWPFなどのデータバインディングのような機構が利用できます。その際にデータソースとしては次の3種類が利用できます。
- 変数
- カスタムオブジェクト
- データテーブル・データセット
変数
まずは「変数」から利用していきましょう。先ほどのコードを修正します。
直接セルに値を設定していた箇所をコメントアウトし、以下の通りデータソースを設定するように修正してください。
//workbook.Worksheets[0].Range["B5"].Value = "Hello, DioDocs!";
workbook.AddDataSource("CompanyName", "Hello, DioDocs!");
workbook.ProcessTemplate();
AddDataSourceの後に、ProcessTemplateメソッドを呼び出すことで、データソースの値をExcelファイルに適用します。
続いて、Excelファイルを次のように修正しましょう。
値を設定していた「B5」セルに、AddDataSourceで指定した名称を二重の波カッコで囲んで{{CompanyName}}のように記述します。
では実行してみましょう。修正前と同じPDFが得られたはずです。
このように物理的なレイアウト情報をExcel側で指定し、論理名を利用してマッピングすることで、ExcelのテンプレートファイルとC#のコードを疎結合に保てることがテンプレート構文の最大の利点になります。
カスタムオブジェクト
さて、変数を1つずつ指定して複雑な帳票を生成することも可能ですが、実際には必要なプロパティを保持した、帳票を抽象化したオブジェクトを扱えると便利ですよね。それが「カスタムオブジェクト」です。
今回は請求書を作成します。なので請求書クラスを作成して利用してみましょう。まずは次のようにInvoiceクラスを作成してください。
namespace HelloDioDocs
{
public class Invoice
{
public string CompanyName { get; set; }
}
}
これを利用して帳票を生成します。先ほどの値を設定していたコードを、以下の通り修正します。
//workbook.AddDataSource("CompanyName", "Hello, DioDocs!");
var invoice = new Invoice {CompanyName = "Hello, DioDocs!"};
workbook.AddDataSource("Invoice", invoice);
workbook.ProcessTemplate();
そしてExcelファイルも次のように修正します。
{{CompanyName}}を{{Invoice.CompanyName}}に修正します。データソースの名称とプロパティ名をピリオドでつないで記述します。
では実行して、先と同様なPDFが生成されることを確認してください。
このようにして複雑な構造のオブジェクトもテンプレート構文で扱うことが可能です。
データセット・データテーブル
データソースの最後は「データセット」と「データテーブル」です。データセットはデータテーブルを複数束ねて利用するだけなので本稿では割愛し、データテーブルのみを扱います。
データテーブルをプログラムから生成することも可能ですが、次の2つの理由からデータベースを利用してデータを取得します。
- データテーブルを利用する場合、ほとんどのユースケースでデータベースから値を取得する
- 後の大量データ生成のため
データベースのセットアップ
データベースはMicrosoftの提供するサンプルデータベース「AdventureWorks」を利用します。
データベースを利用するとは言え、Dockerさえ入っていれば何も煩わしいことはありません。いくつかのスクリプトを実行するだけです。ステップは次の通りです。
- コンテナイメージの取得
- コンテナの作成
- コンテナの起動
- 利用
- コンテナの停止
- コンテナの削除
- イメージの削除
1．コンテナイメージの取得
まずはコマンドプロンプトを開き、次の通りコンテナイメージをDockerHubから取得してください。
docker pull nuitsjp/adventureworks:latest
コンテナイメージとは、OSをインストールする際のisoファイルに置き換えて考えると理解しやすいでしょう。
2．コンテナの作成
取得したイメージからadventureworksという名称のコンテナを作成します。ここでは、saパスワードとSQL Serverを接続するポートをあわせて指定してコンテナを作成しています。
docker create --name adventureworks -e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssw0rd! -p 1433:1433 nuitsjp/adventureworks:latest
isoイメージを実際のPC（もしくは仮想PC）にインストールする感覚です。
3．コンテナの起動
続いてコンテナを起動します。
docker start adventureworks
4．利用
これでAdventureWorksが利用できるようになりました。SSMSがインストールされているのであれば、実際に試してみてもいいでしょう。
SSMSがインストールされていない場合は、特に何もしなくて構いません。すぐにコードから利用します。また、念のため停止や削除の方法も記載しておきましょう。
5．コンテナの停止
明示的に停止する場合は、次のようにコマンドを実行してください。またPCをシャットダウンしても停止し、起動時に自動的に開始されることはありません。
docker stop adventureworks
6．コンテナの削除
ローカルのリソースを削除するためにはコンテナとイメージの2つを削除する必要があります。まずはコンテナを削除しましょう。
docker rm adventureworks
7．イメージの削除
最後にイメージを削除すれば、すべて元通りです。
docker rmi nuitsjp/adventureworks:latest
Hello, DataTable!
では実際にコードからデータベースへ接続し、結果を利用してみましょう。
まずはSQL Serverへ接続するためのライブラリ「Microsoft.Data.SqlClient」をNuGetからインストールします。手順はDioDocsのライブラリをインストールしたときと同じです。そちらを参照してください。
.NET FrameworkではSQL Serverへ接続するためのライブラリは標準で含まれていました。しかし、.NET Coreではオプショナル的なパッケージは外部に切り出され、小さな単位で配備できるように分割されました。これは、たとえばスケーラブルなシステムを構築する際に、スタートアップ時間の削減などの効果が期待できるからです。
続いてSQLを用意しましょう。SQLはコードに直接記述すると読みにくいため、今回はファイルに記述します。プロジェクトを右クリックし、「追加」から「新しい項目」を選択しましょう。
検索条件に「テキスト」を入力し「テキスト ファイル」を選択してください。ファイル名に「SelectInvoices.sql」を指定して「追加」します。
追加したら「Template.xlsx」と同様に、「出力ディレクトリにコピー」プロパティの値を「新しい場合はコピーする」を選択してください。
続いてSQLファイルを開き、以下の通り記述してください。
use AdventureWorks; select Name as CompanyName from Sales.Store where BusinessEntityID = 1046
AdventureWorksデータベースのSalesスキーマのStoreテーブルから店舗を取得します。
ではこのSQLを実行してDioDocsへ渡すコードを記述しましょう。カスタムオブジェクトを設定していたコードをコメントアウトし、代わりに次のように記述しましょう。
//var invoice = new Invoice {CompanyName = "Hello, DioDocs!"};
//workbook.AddDataSource("Invoice", invoice);
var connectionStringBuilder =
new SqlConnectionStringBuilder
{
DataSource = "localhost",
UserID = "sa",
Password = "P@ssw0rd!"
};
using var connection = new SqlConnection(connectionStringBuilder.ToString());
connection.Open();
using var command =
new SqlCommand(
File.ReadAllText("SelectInvoices.sql"),
connection);
using var dataTable = new DataTable();
dataTable.Load(command.ExecuteReader());
workbook.AddDataSource("Invoice", dataTable);
順に説明していきましょう。
まずは、次のようにして接続文字列を生成してデータベースへのコネクションを開いています。
var connectionStringBuilder =
new SqlConnectionStringBuilder
{
DataSource = "localhost",
UserID = "sa",
Password = "P@ssw0rd!"
};
using var connection = new SqlConnection(connectionStringBuilder.ToString());
connection.Open();
続いてSQLをファイルから読み込んで実行し、実行結果をDataTableにロードしています。
using var command =
new SqlCommand(
File.ReadAllText("SelectInvoices.sql"),
connection);
using var dataTable = new DataTable();
dataTable.Load(command.ExecuteReader());
最後にDataTableをDioDocsのデータソースとして追加しています。
workbook.AddDataSource("Invoice", dataTable);
では実行してみましょう。先ほどと名称が変わっていますが、次のような似通ったPDFが生成されます。
カスタムオブジェクトvsデータセット
さて変数はさておき、実践的な帳票生成において機能的にカスタムオブジェクトとデータセット（もしくはデータテーブル）のいずれも選択できる場合、どれを利用するべきでしょうか？
結論から言うと、高度なUIを含むアプリケーションの内部に包含するのであれば「カスタムオブジェクト」を、単一種類の帳票をシンプルなルールで出力するような専用のバッチプログラムの場合は「データセット」を選ぶというのが、私の好みに合います。
もちろん前提として、実現できる帳票に差異がなく、性能に大幅に差がない場合に限ります。次の表は、簡単なベンチマークの実行結果です。
BenchmarkDotNet=v0.12.1, OS=Windows 10.0.18362.836 (1903/May2019Update/19H1) Intel Core i7-7700T CPU 2.90GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores .NET Core SDK=3.1.201 [Host] : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT [AttachedDebugger] DefaultJob : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT
| Method | Mean | Error | StdDev |
|---|---|---|---|
| DataSet | 12.66 ms | 0.482 ms | 1.384 ms |
| CustomObject | 10.73 ms | 0.461 ms | 1.338 ms |
どちらも非常に高速に動作していますね。素晴らしい。CustomObjectのほうがやや高速ですが、それでも2ミリ秒の差異のため、実用上の差異は気にならないケースがほとんどかと思います。
と言うわけで、先ほどの指針で問題ないように思えます。
カスタムオブジェクトを利用する場合、データセットを利用する場合と比較して、最低でもカスタムオブジェクトの設計・実装というコストがかかります。しかし逆に言うと、帳票の元データのデータストアとビジネスルール、Excelテンプレートの3つの結合度を低く保つことが可能となります。
データセットをダイレクトに利用する場合、データストアの具体的な実装に強く依存してしまいます。その分手軽で生産性は高くなりますが、高度なUIを含むアプリケーションの内部に埋め込んでしまうと、テスト容易性や変更容易性に大きな課題を残してしまいます。
高度なUIを含むアプリケーションの内部に包含するのであればカスタムオブジェクトを、単一種類の帳票をシンプルなルールで出力するような専用のバッチプログラムの場合はデータセットを選ぶのが、第一の判断基準として良いように思います。もちろん例外は存在するでしょう。
本格的な帳票の作成（1）
概要
さて、ここまでは非常に簡単な例を示してきましたが、ここからは本格的な帳票を作成していきましょう。
今回はデータソースにはデータテーブルを利用します。この帳票は請求書です。AdventureWorksでは請求書を作るにあたって、次のようなテーブルを利用することとします。
なおテーブルはすべてではありませんし、列も利用するもののみを記述しています。それぞれのテーブルの概要は以下の通りです。
| スキーマ | テーブル | 概要 |
|---|---|---|
| Sales | SalesOrderHeader | 販売情報の概要 |
| Sales | SalesOrderDetail | 販売情報の製品別明細 |
| Production | Product | 製品マスター |
| Sales | Customer | 販売先の顧客 |
| Sales | Store | 顧客の店舗 |
| Person | Person | 顧客の担当者 |
| Person | Address | 請求先住所 |
| Person | StateProvince | 請求先住所の「州」 |
このデータをもとに、次のような帳票を作成します。
赤字部分が動的に設定する項目です。各項目のテーブルとのマッピングは次の通りです。
| 項目 | セル | テーブルおよび列 |
|---|---|---|
| 郵便番号 | B2 | Address.PostalCode |
| 住所 | B3 |
StateProvince.Name + ' ' Address.City + ' ' + Address.AddressLine1 + ' ' + Address.AddressLine2 + ' ' + |
| 店舗名 | B4 | Store.Name |
| 担当者名 | B6 | Person.FirstName + ' ' + Person.LastName |
| 請求日 | F2 | 請求書作成日 |
| 支払期限 | F3 | 請求日＋30日 |
| No. | A13 | 明細の行番号 |
| 製品名 | B13 | Product.Name |
| 単価 | C13 | SalesOrderDetail.UnitPrice |
| 数量 | D13 | SalesOrderDetail.OrderQty |
| 割引率 | E13 | SalesOrderDetail.UnitPriceDiscount |
| 小計 | F13 | SalesOrderDetail.LineTotal |
| 税抜き価格 | F14 | SalesOrderHeader.SubTotal |
| 消費税 | F15 | SalesOrderHeader.TaxAmt |
| 送料 | F16 | SalesOrderHeader.Freight |
| 請求総額 | F18 | SalesOrderHeader.TotalDue |
なお住所が日本表記で、担当者名が英語表記となっています。日本語帳票なので住所は日本表記にしました。ただデータは英語です。アルファベットの氏名を姓名順で表示するのは気持ちが悪いので今回はこのようにしました。
上記項目のうち、Noから小計までは明細単位で行が増減します。
単一項目の設定
まずは明細以外の値を設定しましょう。
クエリの修正
まずはデータを取得するクエリを修正します。SelectInvoices.sqlを開き、以下の通り修正しましょう。
use AdventureWorks;
select
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
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
where
SalesOrderHeader.SalesOrderID = 44533
今回はSalesOrderHeader.SalesOrderIDに44533を固定で指定しています。
Excelテンプレートの修正
続いてExcelのテンプレートを修正しましょう。各項目について、次のようにセルに値を設定してください。
| 項目 | セル | テンプレート |
|---|---|---|
| 郵便番号 | B2 | {{Invoice.PostalCode}} |
| 住所 | B3 | {{Invoice.CustomerAddress}} |
| 店舗名 | B4 | {{Invoice.StoreName}} |
| 担当者名 | B6 | {{Invoice.CustomerPerson}} |
| 請求日 | F2 | {{Invoice.InvitationDay}} |
| 支払期限 | F3 | {{Invoice.DueDate}} |
| 税抜き価格 | F14 | {{Invoice.SubTotal}} |
| 消費税 | F15 | {{Invoice.TaxAmt}} |
| 送料 | F16 | {{Invoice.Freight}} |
| 請求総額 | F18 | {{Invoice.TotalDue}} |
では実行してみましょう。次のような結果が得られるはずです。
複数行項目の設定
さて、それでは商品ごとの明細行を設定しましょう。方法としては2つの選択肢があります。
- 明細用のDataTableを作って別名のデータソースとして登録する（もしくはDataSetに2つのDataTableを設定する）
- 明細を結合した複数行を返すクエリを利用する
今回は後ほど「ワークシートテンプレート」を利用したいため、後者で行います。
クエリの修正
では明細の取得をクエリに追加します。クエリを次のように修正しましょう。
use AdventureWorks;
select
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 = 44533
お気づきかもしれませんが、明細以外の行は重複して取得されるため効率として必ずしも良くありません。
単票であればクエリとDataTableを分けるのがやはり良いでしょうが、今回は後ほど複数PDFの一括生成をできるだけ簡単な実装で実現したいため、取得データの重複は諦めることにしています。
Excelテンプレートの修正
クエリに引き続き、テンプレートも修正しましょう。各項目について、次のようにセルに値を設定してください。
| 項目 | セル | テンプレート |
|---|---|---|
| 製品名 | B13 | {{Invoice.ProductName}} |
| 単価 | C13 | {{Invoice.UnitPrice}} |
| 数量 | D13 | {{Invoice.OrderQty}} |
| 割引率 | E13 | {{Invoice.UnitPriceDiscount}} |
| 小計 | F13 | {{Invoice.LineTotal}} |
では実行してみましょう。
ん？ ちょっとおかしいですね。先ほどのものと見比べてみましょう。
追加されたセルによって、もともとあったセルが上書きされてしまっていますね。
DioDocsでは複数行のデータ構造を作った場合、行単位で処理されているのではなく、セル単位で処理されているようです。したがって、デフォルトでは指定したセルの下に、値の数だけセルが上書きされます。
解決方法はいくつかありますが、今回は「グローバルオプション」の「挿入モード」を利用します。次のコードを追加してください。
workbook.AddDataSource("Invoice", dataTable);
//Init template global settings
workbook.Names.Add("TemplateOptions.InsertMode", "EntireRowColumn");
workbook.ProcessTemplate();
ProcessTemplateの前にグローバルセッティングを追加しています。では実行してみましょう。
これで下の行がつぶれず、スライドされるようになりましたね。とは言え、まだまだ粗があるので、細かな部分を修正していきましょう。
本格的な帳票の作成（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が生成されるはずです。
驚くほど簡単に、帳票の一括生成が実現できてしまいました。
グローバルオプションによるセルサイズの維持
さて、概ね「使える」レベルの帳票となりましたが、もう少し細かいところを詰めていきましょう。次の表をご覧ください。
よく見ると5～6行目や10～12行目の行の高さが他と異なります。これはExcelテンプレートの元の位置のセルサイズに依存しているからと思われます。下のExcelの「ご請求額」のある行をご覧ください。
恐らく、その行の高さが反映されているものと思われます。
そこで、新たに挿入されるセルの高さや幅を、元のサイズを維持するようグローバルオプションの「KeepLineSize」を利用して指定します。
C#のプログラムに以下の通り新しいグローバルオプションを追加してください。
//Init template global settings
workbook.Names.Add("TemplateOptions.InsertMode", "EntireRowColumn");
workbook.Names.Add("TemplateOptions.KeepLineSize", "true");
workbook.ProcessTemplate();
「TemplateOptions.KeepLineSize」にtrueを指定します。実行結果は以下の通りです。
すべての行が同じ高さになっているのが見て取れます。
なおこのオプションの利用は慎重に行う必要があります。たとえば製品名が長くなった場合、改行して表示したいといった場合には不都合なケースがあるかもしれません。オプションは現時点でグローバルに一括で適用され、個別に設定できるわけではないようなので注意してください。
条件付き書式の利用
さて、これが最後になります。
帳票を表示するにあたって、偶数行と奇数行で背景色を変えたいという要望は、よくあるかと思います。
実現方法はいくつかありますが、今回は条件付き書式を利用します。
では、Excelテンプレートを開いてください。
セル「A13～F13」を選択し、「条件付き書式」の「新しいルール」を選択してください。
続いて「新しい書式ルール」ウィンドウで、「数式を使用して、書式設定するセルを決定」を選択し、数式に以下を入力した上で「書式」を選択してください。
=MOD($A13,2)=1
開かれたウィンドウで、「塗りつぶし」タブを開き、適当な背景色を選択した上で、「OK」を押します。
ではExcelを保存して実行してみましょう。次のように行ごとに背景色の異なる表が出力されたはずです。
これで本ハンズオンの課題は、すべて終了となります。
まとめ
本ハンズオンでは以下の内容を取り扱いました。
- ExcelからPDFを生成する基本的な利用方法
-
3種類のデータソースの利用方法
- 変数
- カスタムオブジェクト
- データセット・データテーブル
-
代表的なテンプレート構文の利用方法
- データテンプレート
- 式テンプレート
- 関数テンプレート
- フォーミュラ
- ワークシートテンプレート
-
グローバルオプションの利用方法
- 挿入モード
- セルサイズの維持
本ハンズオンを通して、Excelをテンプレートに利用したPDF帳票の生成について、大筋を理解いただけたのではないでしょうか。帳票レイアウトの設計をExcelで利用できることから、恐らく .NETで利用できる帳票ライブラリのうち、もっとも扱いやすいライブラリの1つだと思います。
試用ライセンスであれば申請不要でいますぐ試すことができます。ぜひこの機会に触れてみてはいかがでしょうか？
