金額欄裡放著 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 物件。活頁簿存檔時逐位元組寫出的,就是這些內容
為什麼 '>=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;
裸字串規則是最容易坑人的那條。在 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;
布林、空白與 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 頁面