クエリの結果を Excel レポートに変換する作業は、1 着のコートを着た 3 つの問題である。Delphi の各フィールド型は正しい Excel の型としてセルに収まらなければならず、ヘッダー行はスキーマのダンプではなくレポートらしく読めなければならず、数値・日付・金額はやり取りの往復に耐える書式を保っていなければならない。このうちどれか 1 つでも省くと、ファイルは何事もなく開き、もっともらしく見え、そして経理担当者が列を選択して合計を待っても、何も表示されない瞬間に破綻する。値がテキストとして書き込まれていたため Excel はそれをラベルとして扱っただけであり、警告する例外は 1 つも発生しない
HotXLS は、Delphi と C++Builder から Excel の自動操作を一切使わずに直接 XLS と XLSX ファイルを書き出す、ネイティブの Object Pascal スプレッドシートライブラリである。TDataset からワークブックへ至る経路を 2 つ提供する。すぐに組み込める TDataToXLS コンポーネントと、ワークブック API に対して手書きしたループである。この 2 つは互換ではない。コンポーネントは XLS ファサードの上に構築された VCL の一員であり、正しい選択はコードがどこで動くか、消費者側がどのファイル形式を期待するかによって決まる。以下では、この両方の経路、コンポーネントが力不足になる境界線、そしてどちらを選んでもフィールド型を損なわずに保つ方法を扱う
フィールド型こそが本当のエクスポート契約である
API を呼び出す前に、Delphi の各フィールド型がどうセルに収まるかを決めておくこと。Delphi の文字列を受け取ったセルは文字列のままになる。HotXLS は '1,234.50' が本来数値であるはずだったと推測することはないし、そうすべきでもない。ロケールに依存した再解釈は、まさにドイツ語のカンマ区切り小数点が英語圏のサーバー上で桁区切りに化ける原因そのものだからである。信頼できるやり方は、型付きのアクセサを通じて代入することである。数値フィールドには AsFloat または AsCurrency、日付には AsDateTime を使い、書式化された文字列ではなく本物の Excel 日付シリアル値をセルに持たせる。AsString は本当にテキストであるフィールドにのみ使う
NULL の扱いは既定値任せにせず、明示的に決めておく価値がある。フィールド値を VarToStr で変換すると SQL の NULL は空文字列になり、これはテキストセルになる。一方、代入自体を省略すればセルは本当に空のままになり、これこそ AVERAGE、COUNT、ピボットテーブルの利用者が期待するものである。金額の列については、NULL がゼロを意味するのか不明を意味するのかを、ループを書く前に決めておくこと。誰かがその列を書式設定した瞬間、両者は見た目上区別がつかなくなるが、その違いは下流で計算されるすべての集計値を変えてしまう
コンポーネント経路: VCL アプリケーションにおける TDataToXLS
すでにデータモジュールにクエリが組み込まれている従来型の VCL アプリケーションでは、TDataToXLS が 1 回の呼び出しで済む経路になる。FireDAC、ADO、IBX、あるいは抽象的なデータセットインターフェースを実装するほかの何であれ、任意の TDataset の子孫をたどり、ヘッダーの見出し、フォント、罫線、任意のグループ小計、大きな結果セットに対する自動シート分割を備えたスタイル付きワークシートを生成する
var
Exporter: TDataToXLS;
begin
Exporter := TDataToXLS.Create(nil);
try
Exporter.Dataset := OrdersQuery; // 任意のTDatasetの子孫
Exporter.WorksheetName := 'Orders';
Exporter.HeaderSource := hsDisplayLabel; // 生の列名ではなくキャプション
Exporter.GroupFields.Add('CustomerID'); // 顧客ごとの小計ブロック
Exporter.RowsPerSheet := 50000; // BIFF8の行上限を下回るようにする
Exporter.VisibleFieldsOnly := True; // Field.Visibleを尊重する
Exporter.SaveDatasetAs('orders.xls');
finally
Exporter.Free;
end;
end;ここで実運用上の重みの大半を担うのは 2 つのプロパティである。HeaderSource := hsDisplayLabel は、生の SQL 列名ではなく各フィールドの DisplayLabel を書き込むため、ワークブックには CUST_NM ではなく「Customer Name」と表示される。RowsPerSheet が存在するのは、このコンポーネントが書き出す BIFF8 のグリッドが 65,536 行 × 256 列で止まってしまうためであり、これを 50,000 に設定しておけば、フォーマットの上限が結果セットを切り詰める前に、大きな結果セットを複数シートへ分割できる。見た目は HeaderFont、DetailFont、GroupColor、罫線スタイルの各プロパティで制御し、消費者側がプレーンなセルを求める場合は DisableFormat のセットで書式のカテゴリごとオフにできる。個別対応が必要な場合は、AfterCell と AfterRow のイベントが、書き込んだばかりの範囲を後処理のために渡してくれる
コンポーネントが力不足になる境界
TDataToXLS には 3 つの制約があらかじめ組み込まれており、これを最初に知っておけば、2 スプリント後になって気まずい設計のやり直しをせずに済む
- 正真正銘の VCL コンポーネントである。 そのユニットは
Forms、Controls、Dialogsを引き込むため、コンソールジョブや Windows サービスにリンクすると VCL 全体がバイナリに引きずり込まれる。ワークブックの中核ユニットにはそのような依存関係はなく、必要なのはWindows、Classes、SysUtils、Variantsだけである。サーバー側のコードで以下に示すループを使うべき理由はここにある - XLS ファサードの上に構築されている。 このコンポーネントは
IXLSWorkbookを組み立て、.xls（BIFF8）を書き出す。これを OOXML 出力へ切り替えるプロパティは存在しない - イベントは XLS の方言で話す。
AfterCellのCell: IXLSRangeパラメータは XLS のオブジェクトモデルに属するため、そこに書くセル単位のカスタマイズは、ファイルが後で .xlsx に変換されるとしても XLS 流のコードのままである
コンポーネントの出力から .xlsx を生成する
消費者側が .xlsx を求めているが、エクスポートのロジックがすでに TDataToXLS に載っている場合、lxXlsxExport ユニットのブリッジ関数が、組み立て済みのワークブックを 1 回の呼び出しで変換してくれる
uses lxXlsxExport;
Exporter.SaveDatasetAs('orders.xls');
// コンポーネントは、埋められたIXLSWorkbookを公開する
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');このブリッジは、完全な忠実度を持つコンバーターではなく、表形式データの運び手として扱うこと。値、数式、数値書式、塗りつぶしの色、フォント属性、列幅、表示設定はコピーする。罫線、結合されたセル範囲、コメント、グラフ、条件付き書式は意図的にコピーしない。ヘッダーと行だけからなるフラットな表であればそれで十分だが、スタイル付きのレポートでは不十分であり、正直な解決策は変換後のファイルを継ぎ接ぎすることではなく、XLSX を直接生成することである
サービスとバッチジョブ向けの手書きループ
サーバー側のコードは TXLSXWorkbook を直接対象にすべきである。サンプルをコピーする前に、この 2 つのファサードの間にあるライフタイムの違いに注意すること。XLS 側の TXLSWorkbook は参照カウント方式のインターフェースとして保持され、手動で解放してはならないのに対し、TXLSXWorkbook は try..finally Free を必要とする普通のクラスである。この 2 つの流儀を混同すれば、確実にリークか二重解放のどちらかを生み出す
procedure ExportOrders(Q: TDataSet; const FileName: string);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Orders');
Sheet.Cells[1, 1].Value := 'Order No';
Sheet.Cells[1, 2].Value := 'Customer';
Sheet.Cells[1, 3].Value := 'Ordered';
Sheet.Cells[1, 4].Value := 'Amount';
Row := 2;
Q.First;
while not Q.Eof do
begin
Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
if not Q.FieldByName('Ordered').IsNull then
Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
Inc(Row);
Q.Next;
end;
Book.StreamingWrite := True; // シートXMLをzipへ直接ストリーミングする
Book.SaveAs(FileName);
finally
Book.Free;
end;
end;重要なのは、型付きの代入と IsNull の防御である。日付は日付シリアル値として、金額は倍精度浮動小数点として届き、NULL の注文日は空文字列に変わることなく本当に空のままになる。StreamingWrite := True は保存経路だけを変える。ワークシートの XML は、まず 1 つの巨大な文字列として組み立てられるのではなく、zip コンテナへ直接ストリーミングされ、これは 6 桁行数の場合に SaveAs 時のメモリスパイクを平坦化する。どの保存メソッドにも TStream のオーバーロードが用意されているため、ワークブックはディスクに触れることなく HTTP レスポンスへ直接流し込むこともできる。ストリーミング書き込みとバッチジョブに関する記事がそのデプロイパターンを説明しており、大規模ワークブックの性能に関する記事が行数がさらに増えたときの対処を扱っている
このループは、スレッドをまたいで拡張できる経路でもある。両方のエンジンはネイティブの Object Pascal ライターであり、片方は BIFF8 のレコードストリーム、もう片方は OOXML の zip と XML であるため、エクスポートのどの部分も COM のオートメーションに触れず、サーバー上に Excel のライセンスも必要としない。これによって得られるのは、単一インスタンスのボトルネックを持たない並列性であり、その条件は各スレッドが自分自身のワークブックを構築することである。ワークブックオブジェクトは共有利用に対してスレッドセーフではないため、原則はエクスポートごとに 1 インスタンスであり、ロックで守った共有インスタンスを使うことは決してない
設計の前に知っておく価値のある制限が 1 つある。XLSX のグリッドは 1,048,576 行 × 16,384 列で止まるため、XLS 側で RowsPerSheet が担っているシート分割はここではほとんど必要ない。100 万行のワークブックは、人間の利用者が本当に求めているものでもまずない。結果セットが本当にそこまで大きい場合、区切り文字形式のファイルのほうが通常は良い契約になる。CSV と TSV エクスポートの記事が、区切り文字、BOM の挙動、そこに関わる数式評価に関する注意点を扱っている
出発点を選ぶ
エクスポートが VCL デスクトップツールに組み込まれ、.xls 出力で問題ない場合は、TDataToXLS とそのグループ化機能から始めること。これが最も少ないコードで済み、すでに述べた忠実度の限界を受け入れられるなら、後で誰かが .xlsx を求めてきたときに SaveXLSWorkbookAsXLSX によるブリッジがそこに用意されている。コードが無人で動く場合、あるいは消費者側が最初から .xlsx を要求する場合は、ループを書くこと。どちらの経路も動作するデモプロジェクトとともに提供されており、HotXLS Delphi Componentパッケージの一部である