技術記事

HotXLSで書くBIFF8 AutoFilter DOPER条件

金額列に 250、730、1,040 が入っているとします。生成した .xls がその列に >=100 の AutoFilter を掛けると、Excel は修復プロンプトなしでファイルを開き、ヘッダーのドロップダウンはフィルタが設定されていると示すのに、データ行は 1 行も残りません。バイト列は正当な BIFF8 です。問題のすべては 1 個の型バイトにあります。オペランドの 100 が文字列として格納され、Excel は文字列オペランドをテキストとして比較するので、数値セルは "100" とのテキスト比較を 1 つも通らないのです。オペランドを IEEE の double として格納すれば、同じフィルタが 3 行すべてを残します

BIFF8 の AutoFilter はどこに条件を置くのか

BIFF8 の AutoFilter は 3 つのレコード型にまたがっており、条件を運ぶのはそのうち 1 つだけです。AUTOFILTERINFO($009D、[MS-XLS] §2.4.8)がフィルタ範囲が何列に及ぶかを記録します。FILTERMODE($009B)は本体のないマーカーで、HotXLS は少なくとも 1 つのフィールドに有効な条件があるときだけ出力します。そして有効なフィールドそれぞれが、自分専用の AUTOFILTER レコード($009E、§2.4.6)を得ます。中身は、フィールドインデックス、下位 2 ビットが wJoin である grbit ワード、ちょうど 10 バイトずつの 2 つの DOPER 構造、そして文字列オペランドがあればその文字を収めたオプションの末尾部です。ディスク上のフィールドインデックスは 0 始まりですが、TXLSWorksheet.ApplyAutoFilter はフィールドを 1 から数えるので、範囲の 3 列目は 16 進ダンプではレコード本体の先頭に 02 00 と現れます

各 DOPER の先頭バイト vt は、後続のオペランドの種類を告げます

  • $04 は残り 8 バイトに入る IEEE 754 double です。Excel が数値比較に使う形式です
  • $06 は文字列です。長さが 1 バイトの cch に収まり、文字はレコード末尾部へ押し出されます
  • $08 は Bes 値です。真偽値かエラーコードが 2 バイトに詰め込まれます
  • $0C と $0E はオペランドを一切持たず、すべての空白セルにマッチ、すべての非空白セルにマッチを意味します

2 バイト目の grbitSgn は比較演算です。1 から 6 が <、=、<=、>、<>、>= に対応します。HotXLS では、条件を設定した後も両バイトは検査可能です。TXLSWorksheet.AutoFilterColumns が TXLSAutofilter を返し、その Find メソッドが 1 始まりのフィールドの項目を返します。項目は Criteria1 と Criteria2 を TXLSAutofilterDOPER オブジェクトとして公開し、読み取り専用の DataType、grbitSgn、Value プロパティを持ちます。ブックの保存時に、バイト単位で書き込まれるのはこの内容です

HotXLSのAUTOFILTERレコードの構造図。0始まりのフィールドインデックス、下位2ビットにwJoinを保持するgrbitワード、そしてvtバイトがIEEE数値・文字列・Bes真偽値・空白・非空白のオペランドを選び、grbitSgnがExcelの適用する比較演算子を符号化する2つの10バイトDOPER構造を示します
各AUTOFILTERレコードは2つの10バイトDOPERを運び、vtバイトがExcelの条件比較を数値・テキスト・真偽値・空白テストのどれで行うかを決めます。保存前にAutoFilterColumnsで両方を読み戻しましょう

'>=100'のフィルタがExcelで1行もマッチしなかった理由

このフィルタが何もマッチさせないのは、テキストオペランドがテキスト比較を強制するからです。素直に最初のライターを書くと、>= プレフィックスは正しく剥がし、符号を 6 に設定し、それから文字 100 を持つ vtString DOPER を出力することになります。条件は Excel 独自のダイアログでも VBA の AutoFilter 呼び出しでも、いずれも文字列として届くからです。Excel はそのレコードを受け入れ、書かれたとおりに評価します。250 を持つ数値セルは、この比較でテキスト "100" に対して「以上」になることは決してありません。列は空になります

TXLSWorksheet.ApplyAutoFilter と TXLSAutofilter.SetFieldCriteria は、同じ方法で条件を変換します。6 つの演算子のいずれかで始まり、後に数値として解釈できるテキストが続く文字列は、その演算子を符号とする vtIEEENumber DOPER になります。数値の Variant(Integer、Int64、Double、Currency などの序数型と浮動小数点型)は、符号 2 の等値テストである数値 DOPER になります。真偽値の Variant は Bes DOPER になります。演算子のない素の文字列は文字列 DOPER のままです。Excel 自身がドロップダウンリストから選んだ項目をこの形で格納するからです

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
  Doper: TXLSAutofilterDOPER;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Cells[1, 1].Value := 'Region';
  Sh.Cells[1, 2].Value := 'Amount';
  Sh.Cells[2, 1].Value := 'North';
  Sh.Cells[2, 2].Value := 250;

  // Field 2 = A1:B100 の 2 列目(API ではフィールドは 1 始まり)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // 期待値:DataType = 4(IEEE 数値)、grbitSgn = 6(>=)
  // Assert は自分で足すチェックであり、ライブラリの一部ではない
  Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));

  Wb.SaveAs('orders.xls');
end;
HotXLSが同じ>=100のAutoFilter条件を、数値セルに1つも一致しないvtString DOPERとして書く場合と、grbitSgn 6のvtIEEENumber DOPERとして書いてExcelに金額250に対して数値評価させる場合の対比図。v2.384.45でCreateFilterDoperが修正した、無音で0行になる障害です
どちらの場合もバイト列は正当なBIFF8でした。変わったのはオペランド型バイトだけです。だからExcelはファイルを開き、ドロップダウンに条件を表示しながら、0行しかマッチしなかったのです

素の文字列の規則は、人が引っかかりやすいところです。Delphi では '100' と 100 は Variant 引数として互換がありそうに見えますが、前者はテキスト比較を、後者は数値の等値テストを生みます。推測でやるのはもっと危険です。00100 をテキストで抱える型番列に、100 の数値 DOPER を渡すと、自分のセルに 1 つもマッチしません。素の文字列をリテラルのまま渡すのは、Excel がリスト選択で書き込むものと同じ形です。数値のつもりなら、数値を渡すか '=100' と書きます。こちらは符号 2 の IEEE DOPER になります

小数点記号、日付、1904 日付システム

演算子の後の数値判定は、Windows の地域設定が何と言おうと、常にピリオドを小数点記号として使います。'>=1.5' は数値 DOPER になります。'>=1,5' は解釈できず文字列 DOPER のままなので、ドイツやフランスのマシンでも、アメリカのマシンと同じ信頼性で数値列を空にします。桁区切り記号も同じ道をたどるので、'>=1,000' はテキストです。演算子と数字の間の空白は取り除かれるので、'>= 100' は大丈夫です。記号の規約を 1 か所に固定しておくと、レポートの振る舞いがどのサーバーで生成したかで変わらなくなります。ファイル形式の既定としては、これが正しいのです

日付は同じ罠の別の衣装です。'>=2026-01-01' は数値ではないのでテキストとして書き込まれます。ところが Excel は日付セルをシリアル値として保持し、数値で比較します。渡すべきはシリアル値のほうです。Delphi の TDateTime は、1900 年 3 月 1 日以降のどの日付についても、Excel の 1900 システムのシリアル値と整数部分が同じです。1904 日付システムに切り替えたブックは 1904 年 1 月 1 日から数えるので、シリアル値は 1462 だけ小さく走ります。ファイルがどちらのシステムを使うかは IXLSWorkbook.Date1904 が教えてくれます。このオフセットの詳細は 1900 と 1904 の日付シリアルと表示形式の相互作用の記事にあります

var
  Fmt: TFormatSettings;
  Serial: Integer;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // 小数を含む閾値:ピリオドで書式化し、ロケールは決して使わない
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // 日付:セルに Excel が格納するシリアル値で比較する
  Serial := Trunc(EncodeDate(2026, 1, 1));
  if Wb.Date1904 then
    Dec(Serial, 1462);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
    xlAnd, Unassigned);
end;

文字列の連結より数値 Variant のほうが素直に見えますが、Variant の数値は常に等値を意味します。それ以外の比較は演算子とテキストの組で運ぶしかないので、書式化の工程は省略できません

AND と OR は 2 つの条件をどう結合するのか

AUTOFILTER の grbit における wJoin ビットは、AND が 0、OR が 1 です。これを入れ替えるのは簡単で、かつ高くつきます。入れ替わると、100 以上かつ 500 未満という範囲フィルタが、100 以上または 500 未満として保存されます。すべての数値にマッチし、フィルタをかけたようには見えない状態です。HotXLS は xlAnd に 0、xlOr に 1 を書き込み、等値条件が絡むときは wJoin の隣の fSimple1 と fSimple2 ビットも立てます。これらは符号 2 の DOPER を示すビットです

公開定数には、移植固有の危険がさらにあります。HotXLS では xlAnd が 0、xlOr が 1 ですが、Excel オートメーションでは 1 と 2 と番号が振られます。XlAutoFilterOperator は素の Byte として宣言されているので、リテラル数値のまま翻訳された VBA マクロは警告なしでコンパイルされます。COM では AND を意味していたリテラル 1 が、ここでは OR を意味します。OR を意味していたリテラル 2 は xlBottom10Items と衝突し、HotXLS は 2 つの DOPER を組み立てるとき xlOr 以外の演算子値をすべて AND として扱うので、その条件は AND で結合されます。2 つのリテラルが両方ひっくり返るのです。名前付き定数ならこの問題全体を回避できます

// 金額は 100(以上)から 500(未満)まで
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  // 自分で足すチェック:書き込まれる内容を確認する
  Assert(Operator = xlAnd);            // ディスク上では wJoin = 0
  Assert(Criteria2.DataType = 4);      // IEEE 数値
  Assert(Criteria2.grbitSgn = 1);      // 1 = 未満
end;
HotXLSのAutoFilter条件におけるwJoinビット配置の図。0は2つのDOPERをANDで結合し、1はORで結合します。v2.384.18より前の入れ替わった定数がbetweenフィルタをすべてにマッチするORへ広げた様子を数直線で示し、xlAndとxlOrの番号がExcelオートメーションと衝突する点も示します
逆になったwJoin定数は、betweenフィルタをすべての数値にマッチするフィルタへ変えました。XlAutoFilterOperatorはただのByteなので移植したVBAも問題なくコンパイルでき、COMオートメーションでANDを意味していたリテラルの1は、ここではORを意味します

真偽値、空白、255文字の上限

真偽値の条件は Bes 値([MS-XLS] §2.5.10)として格納されます。Bes は値バイト bBoolErr を先に、fError フラグを後に置きます。この順を逆に書くと、TRUE 用のフィルタがエラーフラグに 1 を入れ、Excel はその条件をエラーコードとして読みます。厄介なのは、同じ入れ替えをしたリーダーが、自分の書いたファイルとは完璧に往復できることです。自分の書いたものしか読まないライブラリは、ここでの適合性について何も証明しません。Excel でファイルを開いてフィルタダイアログを確認して初めて証明できます。HotXLS は bBoolErr を先に、fError を 0 で書き込むので、True を渡すと DataType が 8 で Value が 1 の DOPER が生まれます

空白のテストにオペランドは不要です。文字列 '=' だけだとすべての空白にマッチする DOPER($0C)になり、'<>' だけだとすべての非空白にマッチする DOPER($0E)になります。どちらも正式な条件なので、そのフィールドは有効と数えられ、FILTERMODE が書き込まれます

文字列オペランドには、DOPER のレイアウト上の物理上限があります。cch フィールドは 1 バイトなので、文字列オペランドは最大 255 文字です。Excel 独自のフィルタダイアログが課している上限と同じです。HotXLS はまず演算子を剥がし、それから 255 文字を超えるものを切り詰めます。長さバイトの周回とレコード末尾部のずれを防ぐためです。したがって、長い説明列へのフィルタは、渡した文字列の接頭辞でマッチすることがあります。大文字小文字には手を付けません。Excel はフィルタ適用時にフィルタテキストを大文字小文字の区別なくマッチさせるので、格納済みの条件を書き直すのはユーザーのデータを変えるだけです。BIFF8 では末尾部は各文字列を 1 バイトのフラグと UTF-16 コードユニットとして格納し、宣言するレコードサイズはそのバイト数を正確に数えなければなりません。この帳簿づけは Delphi XLS ライターで BIFF レコード長宣言がずれるしくみの記事で扱ったのと同じものです

複数列のフィルタと保存後のファイルの検証

ApplyAutoFilter の呼び出し 1 回ごとに、フィルタ範囲全体が定義し直されます。2 回呼ぶと、2 回目の条件だけが生き残ります。このメソッドは公開の TXLSWorksheet.SetAutoFilter を経由し、範囲を組み立て直す前に全フィールドをクリアします。1 列なら正しい動きで、2 列目のフィルタを初めて掛けたときには意外に感じます。複数列では、ApplyAutoFilter を 1 回だけ呼んで範囲と最初の条件を作り、残りは AutoFilterColumns.SetFieldCriteria で足します。こちらは 1 フィールドだけに触れ、範囲も他のフィールドもそのままにします。どちらの経路も、範囲外のフィールド番号は例外を投げずに無視するので、設定すべき検証は、開き直したファイルから条件を読み戻すことです

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                       // 範囲+フィールド 1
Sh.AutoFilterColumns.SetFieldCriteria(3, '>=100', xlAnd, Unassigned);
Sh.AutoFilterColumns.SetFieldCriteria(4, True, xlAnd, Unassigned);
Wb.SaveAs('orders.xls');

Wb := TXLSWorkbook.Create;
Wb.Open('orders.xls');
with Wb.Sheets[1].AutoFilterColumns do
begin
  // ファイルを開き直した後の、自分で足すチェック
  Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6));   // テキスト 'North'
  Assert(Find(3).Criteria1.DataType = 4);                          // 数値 100
  Assert(Find(4).Criteria1.DataType = 8);                          // 真偽値 TRUE
end;

AUTOFILTER レコードは保存された定義です。クラシックな XLS ワークシートでは、HotXLS はファイルを開いたとき Excel が評価するための条件を書き込み、サーバー側での行レベル評価は XLSX オブジェクトモデル側に住んでいます。それは HotXLS のデータ検証、AutoFilter、テーブルの記事のとおりです。Excel が実際に行を隠したあとは、範囲の下の合計がどうなるかは SUBTOTAL と AGGREGATE が隠し行とフィルタ行を扱うしかた次第で、テキストとして格納された数値フィルタはそこでは合計 0 として現れます

上で述べた条件の変換、Bes のレイアウト、結合ビットは、HotXLS が保存するすべての BIFF8 ブックに書き込まれるもので、Delphi と C++Builder 向けネイティブ XLSX 対応と並んで座っています。エディション、全機能リスト、トライアルダウンロードは HotXLS Delphi spreadsheet component のページにあります