HotXLS の 3 つの機能はワークシートを共有していますが、まったく異なるオブジェクトに対して動作します。それらが似たようなことをすると思い込んだ瞬間に問題が始まります。データの入力規則は範囲に紐づけられ、ユーザーがそこに入力できる内容を制約します。オートフィルタは領域に保存された条件定義を紐づけ、ビューアがどの行を表示するかを変更します。テーブルは範囲を、名前付きで型付けされ、帯状スタイルの適用された構造でラップします。1 つは入力を制約し、1 つはビューを記録し、1 つはスキーマを課します。どれも単独ではセルの値を 1 つも動かしません。特にオートフィルタは、その言葉が定義だけを保存するものであるにもかかわらず何らかの動作を示唆するため、人を惑わせます。どの呼び出しがどのオブジェクトに触れ、その効果がいつ実際に現れるのかを知っていることこそが、テストしたときと同じ振る舞いを Excel で示すワークブックと、静かに食い違っていくワークブックを分けます
オートフィルタは定義を保存するだけで、行を切り取るわけではない
保存されたファイル内のオートフィルタは条件レコードです。行の非表示化は後で、Excel がワークブックを開いて条件をデータに照らして評価するときに起こります。HotXLS はそのレコードを忠実に書き込むだけで、何も切り取りません。フィルタをかけた行はすべて、物理的にはファイル内にそのまま存在し続けます。却下された注文を除外するためにフィルタを適用し、その後ワークブックを読み戻すパイプラインは、却下されたものも含めてすべての行を目にすることになります。コードは API としては正しいのに、作者のメンタルモデルとしては間違っています。XLSX ワークシート側では、SetAutoFilter がフィルタ対象の領域を宣言し、AddAutoFilterColumn がその 1 列に条件を紐づけます。サーバー側コードが実際の結果を必要とする場合、たとえばサマリーの行数のためや、一致した行だけを転送するためには、ライブラリはファイルが変わったふりをするのではなく、条件をあなたの代わりに評価します
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R, Visible: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
// カラム id 3 = フィルタ範囲内の4番目の列（0 始まりのオフセット）
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
Visible := 0;
for R := 2 to 500 do
if Sheet.AutoFilterRowVisible(R) then
Inc(Visible);
// Visible はここで、ファイルを開いた後に Excel が表示する内容と一致する
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;AutoFilterRowVisible は行単位で答え、PreviewAutoFilterRows は一致した集合を 1 回のパスで必要とするときに、コールバックを通じて領域全体を走査します。どちらも正しい答えにならないケースが 1 つあります。要件が「除外された行はビューとしてではなく、ファイル内に一切存在してはならない」というプライバシー上の削除であるなら、行そのものを削除してください。フィルタはそこでは間違った道具です。受け取った人はワンクリックでそれを解除でき、隠したかったはずのデータが再び画面に現れてしまうからです
カラム id はオフセットであり、列番号ではない
上のスニペットのコメントは、この API で最もデバッグ時間を食う罠に印を付けています。AddAutoFilterColumn は、ワークシートの列ではなく、フィルタ範囲内での 0 始まりの位置によって対象を識別します。A1:E500 に対するフィルタでは、この 2 つの採番方式はたまたま 1 だけずれるため、ちょっとしたテストは通過してしまい、同僚が別の列でフィルタをかけた瞬間に壊れる、まさにその種のニアミスです。フィルタが列 C から始まる場合、id 0 は列 C を意味し、この不一致はすぐに明らかになります。フィルタ範囲を実行時に計算する場合は、その範囲文字列を組み立てたのと同じ変数からカラム id を導出してください。決してワークシートの列定数から導出してはいけません。各列は、2 つの演算子、2 つの条件、and/or の連結子を取るオーバーロードを通じて、2 つ目の条件を受け付けます。これは Excel のカスタムフィルタダイアログを鏡写しにしたものです。XLS ファサードは SetAutoFilter と ApplyAutoFilter で同じ領域をカバーしますが、その条件と演算子のパラメータは古い COM スタイルの慣習に従い、フィールドを 1 から採番します。ファサードを切り替えるということはインデックスの基点を切り替えるということなので、呼び出し箇所にはどちらを使っているかを示すコメントを残す価値があります
入力規則は、ユーザーがその下で編集する契約である
3 つの機能のうち、入力規則だけが未来の入力を積極的に制約するものであり、記入のために配布されて処理のために戻ってくるワークブックにおいて、最も多くの設計上の注意を必要とします。リスト型の亜種がその作業の大半を担います
var
Idx: Integer;
begin
Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
Sheet.DataValidations[Idx].SetPrompt('Status',
'Pick one of the listed states');
Sheet.DataValidations[Idx].SetError('Invalid status',
'Type or paste only listed values', xlsxDvErrStop);
Sheet.DataValidations[Idx].AllowBlank := False;
// 数量: 整数、0 以上
Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;リストと整数を超えて、同じファミリーは AddCustomValidation を通じて小数、日付、時刻、文字数、自由形式の数式もカバーし、汎用の AddDataValidation は設定駆動のルールビルダー向けに型と演算子の完全なマトリクスを公開します。エラーのスタイルはその名前が示唆する以上に重要です。xlsxDvErrStop は不正な入力をきっぱりと拒否します。警告スタイルと情報スタイルはワンクリックで値を通過させます。読み戻すコードがルール外の値を許容できるかどうかに基づいて、列ごとに選んでください。プロンプトのテキストか、ファイルとともに配布する README のいずれかに書いておくべき境界が 2 つあります。Excel の入力規則は入力をガードしますが、検証済みの範囲にブロックを貼り付けるとルールをすり抜けてしまうため、データを読み戻すコードは、セルを信頼するのではなく必ず再度検証しなければなりません。そして、ルールは渡された範囲そのものをカバーするため、最終的な行数がわかる前に入力規則を紐づけてしまうと、後から追加された末尾は保護されないまま残ります。まずデータを書き込み、その後で実際の広がりに合わせてルールのサイズを調整してください
レガシーファサードは、1 つの使い勝手の違いを除いて同じルールファミリーを提供します。XLS 側の作成関数、すなわち AddWholeNumberValidation、AddDecimalValidation、AddDateValidation、AddTimeValidation、AddTextLengthValidation、AddCustomValidation は、インデックスではなく TDataValidation オブジェクトを直接返すため、プロンプトとエラーの設定はルックアップではなく返されたリファレンスからチェーンできます。演算子の列挙型（xlsDvBetween、xlsDvGreaterThan、その他）は XLSX 側のセットを鏡写しにしているため、戻り値のスタイルの違いを除けば、ルール構築コードはファサード間で移植できます。プロンプトのテキスト自体も、ルールと同じくらい考え抜く価値があります。空のエラーボックスで入力を拒否するドロップダウンは、ユーザーに IT へメールを送ることを教えてしまいますが、正しい状態を名指しするものは、ユーザーにセルを直して先に進むことを教えます
ライブラリがあなたの代わりに吸収してくれる、ある極性の反転
OOXML の入力規則 XML を手で読んだことがある人なら、反転した showDropDown 属性に出会ったことがあるはずです。ISO/IEC 29500 では、true という値は「ドロップダウン矢印を抑制する」ことを意味し、名前から読み取れる意味とは正反対です。HotXLS はこれを内部で反転させるため、入力規則ルール上の ShowDropDown プロパティは文字どおりの意味を持ち、true でドロップダウンが表示されます。ここで火傷をする唯一の方法は、真実のレベルを混ぜてしまうことです。コードからプロパティを設定する一方で、同僚が保存済みの XML を監査し、彼らの目には逆に見える属性を「修正」してしまう、といったケースです。レビューツールにとってプロパティと生の XML のどちらが正であるかを決め、その反転をその決定が生きている場所に書き残しておいてください
テーブルは範囲にスキーマと名前を与える
ワークシートテーブル、Excel の用語で言う ListObject は、範囲を名前、型付きの列、帯状のスタイル、構造化参照のサポートでラップします。ユーザーが並べ替えたり拡張したりし始めた瞬間に、生成されたワークブックを完成品らしく感じさせるのはこの機能です。作成はファサード間で対称であり、AddTable は名前、範囲、列のリストを受け取ります
var
Cols: TStringList;
begin
Cols := TStringList.Create;
try
Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
Sheet.AddTable('Orders', 'A1:E500', Cols);
finally
Cols.Free;
end;
end;XLSX 側では、結果として得られるテーブルオブジェクトは StyleName(組み込みの TableStyleMedium2 ファミリーとその仲間たち)、縞模様の切り替え、合計行フラグを公開するため、社内標準のスタイルを適用することは手動の書式設定パスではなくプロパティの代入で済みます。レガシーの .xls ファイルでは、同じ呼び出しが BIFF8 のテーブルレコードを書き込み、ファサードは行フィールド、列フィールド、データフィールドから構築されるサマリービュー向けに AddPivotTable も提供します。これは、古いフォーマットにおける「テーブル」が OOXML の ListObject よりも広い範囲に及ぶことを思い出させてくれます。テーブルには、データベースのビューに名前を付けるのと同じように名前を付けてください。構造化参照で Orders[Amount] を読む下流のコードは、位置ベースのコードを壊してしまう列の並べ替えを生き延びます
後々の後始末を省いてくれる慣習が 2 つあります。Excel はテーブル名がワークブック全体で一意であることを要求するため、地域ごとにシートを 1 枚出力するジェネレーターは、Orders を使い回すのではなく Orders_EMEA のようなスキームを必要とします。重複は書き込み時には失敗しません。ユーザーがファイルを開いたときに修復ダイアログとして表面化し、それは発見するには最悪の場所です。もう 1 つの慣習は合計行に関するものです。有効になっている場合、それはデータ範囲のすぐ下に位置するため、後から「最後に使われた行プラス1」で追記するコードは、その後ろではなく合計帯の中に書き込んでしまいます。データの広がりをテーブルの広がりとは別に追跡すれば、追記は期待どおりの場所に着地します
この 3 つの機能は、データ入力用の成果物の中で自然に組み合わさります。テーブルが編集可能な領域を定義し、入力規則がユーザーが入力する列を制約し、あらかじめ設定されたフィルタが受け取った人の最初の数クリックを省いてくれます。ワークブックが重要な行にフォーカスした状態で開くよう、あらかじめフィルタを適用した状態で配布することには十分な理があります。ただし、除外された行が依然としてファイル内に存在し、好奇心のある受け取り手がそれを表示できることを忘れなければの話です。クエリ結果を効率よくシートに投入すること、このパイプラインの上流側については、Delphi からデータベース結果を Excel にエクスポートする記事で扱っており、数式が検証済みのデータを要約するワークブックは、安定したシート横断参照のための定義名から恩恵を受けます
入力規則、フィルタ、テーブルは、値のグリッドを出荷することと、小さなアプリケーションを出荷することの違いを生みます。ルール、フィルタ、テーブルの完全なリファレンスは HotXLS Delphi Component の製品ページにあります