技術文章

HotXLS 與 Delphi 的 BIFF8 AutoFilter DOPER 條件

金額欄裡放著 250、730 與 1,040。產生的 .xls 對該欄套用 >=100 的 AutoFilter,Excel 開檔沒有修復提示,表頭下拉顯示已設篩選,然後每一列資料都消失了。位元組是合法的 BIFF8。整個問題出在一個型別位元組:運算元 100 被存成字串,Excel 對字串運算元做文字比較,而沒有任何數值儲存格能在與 "100" 的文字比較中過關。把運算元存成 IEEE 倍精準數,同一個篩選就會留下全部三列

BIFF8 AutoFilter 把條件放在哪裡

BIFF8 的 AutoFilter 橫跨三種記錄型別,只有其中一種放條件。AUTOFILTERINFO($009D,[MS-XLS] §2.4.8)記錄篩選範圍涵蓋幾欄。FILTERMODE($009B)是個沒有內容的標記,HotXLS 只有在至少一個欄位有生效條件時才寫出它。之後每個生效的欄位都有自己的一條 AUTOFILTER 記錄($009E,§2.4.6):一個欄位索引、一個低兩位是 wJoin 的 grbit 字組、剛好各 10 位元組的兩個 DOPER 結構,外加一段選填尾段,放字串運算元的字元。磁碟上的欄位索引從零起算,而 TXLSWorksheet.ApplyAutoFilter 的欄位從 1 數起,所以在十六進位傾印裡,範圍的第三欄會以 02 00 出現在記錄內容開頭

每個 DOPER 的第一個位元組 vt,說明後面跟的是哪種運算元:

  • $04 是 IEEE 754 倍精準數,放在其餘 8 個位元組裡,Excel 做數值比較用的就是這種形式
  • $06 是字串,長度放在單一 cch 位元組裡,字元本體推進記錄尾段
  • $08 是 Bes 值,布林或錯誤碼裝在兩個位元組裡
  • $0C 與 $0E 完全不帶運算元,分別代表符合全部空白與符合全部非空白

第二個位元組 grbitSgn 是比較運算子:1 到 6 對應 <、=、<=、>、<> 與 >=。在 HotXLS 裡設定條件之後,這兩個位元組仍然看得到。TXLSWorksheet.AutoFilterColumns 回傳一個 TXLSAutofilter,它的 Find 方法以 1 起算的欄位編號取回項目,項目再以唯讀的 DataType、grbitSgn 與 Value 屬性暴露 Criteria1 與 Criteria2 兩個 TXLSAutofilterDOPER 物件。活頁簿存檔時逐位元組寫出的,就是這些內容

HotXLS AUTOFILTER 記錄解剖圖:從零起算的欄位索引、低兩位放 wJoin 的 grbit 字組,以及兩個 10 位元組的 DOPER 結構,其 vt 位元組挑選 IEEE 數字、字串、Bes 布林、空白或非空白運算元,grbitSgn 則編碼 Excel 套用的比較運算子
每條 AUTOFILTER 記錄帶兩個 10 位元組 DOPER,vt 位元組決定 Excel 把條件當數字、文字、布林還是空白測試來比——存檔前先透過 AutoFilterColumns 把兩個都讀回來檢查

為什麼 '>=100' 篩選在 Excel 中比不到任何一列?

篩選比不到東西,因為文字運算元強制文字比較。最直覺的寫入端會正確剝掉 >= 前綴、把符號設成 6,然後造出裝著字元 100 的 vtString DOPER——反正條件都是以字串進來的,Excel 自己的對話框和 VBA 的 AutoFilter 呼叫都一樣。Excel 接受那條記錄,並照字面精確求值。在那個比較裡,放著 250 的數值儲存格永遠不會「大於等於」文字 "100",於是整欄清空

TXLSWorksheet.ApplyAutoFilter 與 TXLSAutofilter.SetFieldCriteria 用同一套規則轉換條件。以六個運算子之一開頭、後接可解析成數字的文字的字串,會變成以該運算子為符號的 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;

  // 欄位 2 = A1:B100 的第二欄(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 條件存成比不到任何數值儲存格的 vtString DOPER,或存成 grbitSgn 為 6、Excel 對金額 250 做數值運算的 vtIEEENumber DOPER
兩個方向的位元組都是合法 BIFF8——差別只在運算元型別位元組,這就是 Excel 開得了字串版、下拉也顯示條件、卻照樣比出零列的原因

裸字串規則是最容易坑人的那條。在 Delphi 裡,'100' 和 100 當 Variant 參數看起來可以互換,但前者產生文字比對,後者產生數值等於。用猜的更糟:一個以文字存放 00100 的料號欄,會拿到給 100 的數字 DOPER,結果連自己的儲存格都比不到。裸字串照實鏡射 Excel 對清單選取的寫法;當您要的是數字,就傳數字或寫 '=100',後者給出符號 2 的 IEEE DOPER

小數點分隔符、日期與 1904 日期系統

運算子之後的數字測試一律用句點當小數點分隔符,Windows 地區設定說什麼都不影響。'>=1.5' 變成數字 DOPER;'>=1,5' 解析不了,留在字串 DOPER,在德國或法國機器上照樣清空數值欄,跟在美國機器上同樣可靠。千位分隔符也一樣失敗,所以 '>=1,000' 是文字。運算子與數字之間的空白會修剪,所以 '>= 100' 沒問題。把分隔符固定在一處,報表才不會因為產生的伺服器不同而行為不同——對檔案格式來說,這是正確的預設

日期是同一個陷阱換了戲服。'>=2026-01-01' 不是數字,所以被寫成文字,而 Excel 把日期儲存格存成序號並做數值比較。請改傳序號。對 1900 年 3 月 1 日起的任何日期,Delphi TDateTime 的整數部分與 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 怎麼合併兩個條件?

AUTOFILTER grbit 的 wJoin 位元,0 代表 AND、1 代表 OR,而把它們弄反很容易,代價也很大。弄反之後,「至少 100 且低於 500」這種範圍篩選會被存成「至少 100 或低於 500」,結果每個數字都符合,看起來就像篩選根本沒套上。HotXLS 給 xlAnd 寫 0、給 xlOr 寫 1;條件涉及等於時,還會在 wJoin 旁邊設上 fSimple1 與 fSimple2 位元,用它們標記符號為 2 的 DOPER

公開常數自己帶著一個移植陷阱。HotXLS 的 xlAnd 是 0、xlOr 是 1,Excel automation 卻編號成 1 和 2。XlAutoFilterOperator 宣告成普通 Byte,所以帶著字面數字翻譯過來的 VBA 巨集編譯時一個警告也沒有。COM 裡代表 AND 的字面 1,到這裡變成 OR;代表 OR 的字面 2 則撞上 xlBottom10Items,而 HotXLS 在建兩個 DOPER 時,把 xlOr 以外的所有運算子值都當 AND,於是那個條件被用 AND 連接。兩個字面值都翻了。用具名常數可以完全避開:

// 金額從 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 用 AND 連接兩個 DOPER、1 用 OR;數線圖示常數寫反如何把 between 篩選放大成什麼都符合的 OR;以及 xlAnd xlOr 與 Excel automation 的編號衝突
寫反的 wJoin 常數把 between 篩選變成什麼數字都符合,而翻譯過來的 VBA 照樣編譯通過,因為 XlAutoFilterOperator 只是普通 Byte——COM automation 下代表 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 欄位只有一個位元組,所以字串運算元至多 255 個字元,這正是 Excel 自己的篩選對話框強制的同一個上限。HotXLS 先剝掉運算子,再把更長的內容截到 255 字元,避免長度位元組迴繞、把記錄尾段弄到失步。對長描述欄做的篩選,因此可能只比中您傳入內容的前綴。大小寫保持原樣:Excel 套用篩選時本來就不分大小寫地比對篩選文字,改寫存好的條件只會動到使用者的資料。BIFF8 的尾段把每個字串存成一個位元組的旗標加 UTF-16 code unit,宣告的記錄大小必須把這些位元組算得分毫不差,這正是Delphi XLS 寫入器裡 BIFF 記錄長度宣告的漂移一文談過的同一套帳目紀律

篩選多欄與檢查存好的檔案

每次呼叫 ApplyAutoFilter 都會重新定義整個篩選範圍,所以兩次呼叫之後,只有第二次的條件留得下來。這個方法走公開的 TXLSWorksheet.SetAutoFilter,它在重建範圍前先清掉每個欄位。對單欄正確,第一次篩兩欄時卻很意外。要篩好幾欄,先呼叫一次 ApplyAutoFilter 建立範圍與第一個條件,再用 AutoFilterColumns.SetFieldCriteria 加入其餘條件,它只動一個欄位,範圍與其他欄位原封不動。兩條路徑對範圍外的欄位編號都直接忽略、不丟例外,所以值得做的檢查,是從重新開啟的檔案把條件讀回來

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 怎麼對待隱藏列與篩選列,而存成文字的數值篩選,在那裡現形的方式就是合計歸零

上述的條件轉換、Bes 版面與連接位元,就是 HotXLS 存每本 BIFF8 活頁簿時寫進去的內容,同時也為 Delphi 與 C++Builder 原生支援 XLSX。版本、完整功能清單與試用下載在 HotXLS Delphi spreadsheet component 頁面