LBlog Phát triển Phần mềm losLabGhi chú kỹ thuật dành cho các nhà phát triển phần mềm PDF, Delphi và tài liệu.

Bài viết kỹ thuật

Tiêu chí DOPER AutoFilter BIFF8 trong Delphi với HotXLS

HotXLS Delphi Component

HotXLS lưu mọi tiêu chí AutoFilter BIFF8 thành một record AUTOFILTER mang hai cấu trúc DOPER 10 byte, và kiểu DOPER quyết định cách Excel so sánh. Từ v2.384.45, TXLSWorksheet.ApplyAutoFilter ghi một phép so sánh như '>=100' thành DOPER số IEEE, nên Excel khớp các ô số thay vì so văn bản. Bug report khiến thay đổi này ra đời thì ngắn và phát điên: một export hằng đêm áp filter lên cột số tiền, file mở ra không phàn nàn gì, mũi tên dropdown vẫn hiện tiêu chí, mà filter khớp đúng 0 dòng. Chẳng gì hỏng cả. Các byte là BIFF8 hợp lệ, chỉ là loại hợp lệ sai, và đó chính là lớp lỗi mà bài này lần qua, cùng với hai lỗi cấp byte cũ hơn đã sửa trong v2.384.18

Một AutoFilter BIFF8 thực chất lưu những gì?

Một AutoFilter BIFF8 là một bộ ba loại record chứ không phải một, và chỉ record theo từng field mới giữ tiêu chí. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) ghi số cột mà vùng filter phủ. FILTERMODE ($009B) là marker không body mà HotXLS chỉ emit khi ít nhất một field có tiêu chí active. Sau đó mỗi field active có record AUTOFILTER riêng ($009E, §2.4.6): một chỉ mục field tính từ 0, một word grbit với hai bit thấp là wJoin, hai DOPER đúng 10 byte mỗi cái, và một phần đuôi tùy chọn giữ các ký tự của DOPER string. Chỉ mục field trên disk tính từ 0 dù ApplyAutoFilter đánh số field từ 1, và điều này có ý nghĩa ngay lần đầu bạn lùng một record trong hex dump. Byte đầu của mỗi DOPER, vt, cho biết kiểu operand theo sau:

  • $04 là một double IEEE 754 nằm trong 8 byte còn lại, cách Excel lưu một phép so sánh số
  • $06 là một string với độ dài nằm trong đúng một byte cch, còn các ký tự được đẩy vào phần đuôi record
  • $08 là một giá trị Bes, Boolean hay mã lỗi gói trong hai byte
  • $0C và $0E không mang operand nào, nghĩa là khớp mọi ô trống và khớp mọi ô không trống

Byte thứ hai, grbitSgn, giữ phép so sánh: 1 tới 6 ứng với <, =, <=, >, <> và >=. HotXLS giữ cả hai byte luôn tra cứu được sau sự kiện qua AutoFilterColumns, thứ expose các phần tử Criteria1 và Criteria2 dưới dạng object TXLSAutofilterDOPER với DataType, grbitSgn và Value, nên bạn có thể assert trên thứ sắp được ghi thay vì đoán mò

Giải phẫu record AUTOFILTER của HotXLS: chỉ mục field tính từ 0, word grbit với hai bit thấp giữ wJoin, và hai cấu trúc DOPER 10 byte trong đó byte vt chọn operand số IEEE, string, Boolean Bes, trống hay không trống, trong khi grbitSgn mã hóa toán tử so sánh mà Excel áp dụng
Mỗi record AUTOFILTER mang hai DOPER 10 byte, và byte vt quyết định Excel so tiêu chí như số, văn bản, Boolean hay phép thử ô trống — hãy đọc ngược cả hai qua AutoFilterColumns trước khi lưu

Vì sao filter '>=100' không khớp dòng nào trong Excel?

Filter không khớp gì cả vì operand được lưu dưới dạng văn bản, và Excel so một string DOPER với ô như văn bản. Trước v2.384.45, CreateFilterDoper trong lxFilter.pas cắt đúng phần tiền tố >= và đặt sign bằng 6, rồi luôn dựng một DOPER vtString giữ các ký tự 100. Một ô số mang 250 không bao giờ thỏa một phép so văn bản với "100", nên mọi dòng đều rơi ra. Không exception, không diagnostic, không prompt sửa chữa nào từ Excel. Luật từ v2.384.45 được cố ý thu hẹp: nếu tiêu chí bắt đầu bằng toán tử so sánh và phần còn lại parse được thành số theo luật invariant-culture, HotXLS ghi một DOPER vtIEEENumber với cùng sign. Giá trị trần không kèm toán tử giữ nguyên dạng string, vì đó chính là cách Excel lưu một mục chọn từ dropdown list

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 = cột thứ hai của A1:B100 (phía API đánh số từ 1)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (số IEEE), grbitSgn = 6 (>=)
  // Trước khi sửa: DataType = 6 (string), thứ không khớp gì cả
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;
HotXLS ghi cùng tiêu chí AutoFilter >=100 thành DOPER vtString không khớp ô số nào hay thành DOPER vtIEEENumber với grbitSgn 6 mà Excel đánh giá theo số với số tiền 250, chính là lỗi im lặng khớp 0 dòng mà CreateFilterDoper đã sửa trong v2.384.45
Cả hai lần các byte đều là BIFF8 hợp lệ — chỉ có byte kiểu operand thay đổi, đó là lý do Excel mở được file, hiện tiêu chí trong dropdown mà vẫn khớp đúng 0 dòng

Phần parse là nơi còn sót lại những cạnh sắc. Operand đi qua TryStrToFloat với dấu chấm làm dấu thập phân, nên '>=1.5' thành số còn '>=1,5' giữ nguyên là string DOPER và lặng lẽ không khớp gì thêm lần nữa, bất kể Windows locale nói gì. Ngày tháng là cái bẫy tương tự khoác bộ khác: '>=2026-01-01' không phải số, nên được ghi thành văn bản, trong khi Excel giữ ô ngày dưới dạng serial number. Với so bằng trên số, cả '=100' lẫn một Variant số như 100 đều cho ra DOPER IEEE với sign 2, còn string trần '100' cho ra phép khớp văn bản. Hãy dựng operand số ngay trong code thay vì format cho người đọc:

var
  Fmt: TFormatSettings;
  Since: TDateTime;
begin
  Fmt := TFormatSettings.Create;
  Fmt.DecimalSeparator := '.';

  // Ngưỡng có phần lẻ: luôn format với dấu chấm
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Ngày tháng: so với serial number mà Excel lưu trong ô.
  // TDateTime của Delphi bằng serial hệ 1900 với các ngày sau tháng 3/1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

AND và OR nối hai điều kiện với nhau thế nào?

Hai bit wJoin của grbit AUTOFILTER là 0 cho AND và 1 cho OR, và HotXLS đã đảo lộn hai hằng số đó cho tới v2.384.18. Một filter kiểu between như ít nhất 100 và dưới 500 bị lưu thành ít nhất 100 hoặc dưới 500, thứ trên thực tế khớp mọi số và trông như thể filter đơn giản không được áp dụng. Các hằng số toán tử công khai thêm một bẫy porting thứ hai. Trong HotXLS, xlAnd là 0 và xlOr là 1, trong khi Excel automation đánh số chúng 1 và 2. XlAutoFilterOperator là một Byte trơn, nên code dịch từ macro VBA với số literal compile ngon lành, và literal 1 vốn nghĩa là AND trong COM giờ nghĩa là OR. Dùng hằng số có tên thì vấn đề không thể phát sinh:

// Số tiền từ 100 (bao gồm) tới dưới 500 (không bao gồm)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 trên disk
  Assert(Criteria2.grbitSgn = 1);      // 1 = nhỏ hơn
end;
Bố cục bit wJoin của HotXLS cho tiêu chí AutoFilter trong đó 0 nối hai DOPER bằng AND và 1 bằng OR, một trục số cho thấy hằng số bị đảo trước v2.384.18 nới rộng filter between thành OR khớp mọi thứ, và sự xung đột đánh số xlAnd xlOr với Excel automation
Hằng số wJoin đảo ngược biến một filter between thành thứ khớp mọi số, và VBA dịch sang vẫn compile được vì XlAutoFilterOperator là một Byte trơn — literal 1 vốn nghĩa là AND dưới COM automation lại nghĩa là OR ở đây

Boolean, ô trống và trần 255 ký tự

Một tiêu chí Boolean được lưu dưới dạng giá trị Bes ([MS-XLS] §2.5.10), và Bes đặt byte giá trị bBoolErr trước, cờ fError sau. HotXLS ghi chúng theo thứ tự ngược lại trước v2.384.18, nên một filter cho TRUE đặt 1 vào cờ lỗi và Excel đọc tiêu chí thành mã lỗi. Writer và reader bị đảo cùng nhau, vì thế HotXLS round-trip file của chính mình không phàn nàn gì trong khi Excel phản đối, một lời nhắc rằng round-trip tự nhất quán chẳng chứng minh gì về sự tuân thủ spec. Ô trống chẳng cần operand gì cả: truyền '=' một mình cho ra DOPER khớp-mọi-ô-trống ($0C) và '<>' một mình cho ra DOPER khớp-mọi-ô-không-trống ($0E)

Tiêu chí string đụng trần cứng trong bố cục DOPER. Trường độ dài cch chỉ là một byte, nên operand string không thể vượt 255 ký tự, và CreateFilterDoper cắt cụt văn bản dài hơn sau khi strip toán tử thay vì để byte độ dài wrap và làm lệch phần đuôi record. Việc cắt diễn ra im lặng, và một filter trên cột mô tả dài có thể khớp khác với toàn bộ văn bản bạn truyền vào. Trong BIFF8 phần đuôi lưu mỗi string là một cờ một byte theo sau bởi các code unit UTF-16, và kích thước record khai báo phải đếm chính xác những byte đó, đúng kỷ luật sổ sách đã trình bày trong khai báo độ dài record BIFF trôi thế nào trong một XLS writer Delphi

Vì sao lời gọi ApplyAutoFilter thứ hai xóa sổ cái đầu tiên?

Mỗi lời gọi ApplyAutoFilter định nghĩa lại toàn bộ vùng filter, nên chỉ tiêu chí của lần gọi cuối sống sót. Bên trong nó gọi SetAutoFilter, thứ xóa mọi field trước khi dựng lại vùng, điều đúng với một cột và gây bất ngờ với hai cột. Để filter nhiều cột, hãy gọi ApplyAutoFilter một lần để thiết lập vùng và tiêu chí đầu tiên, rồi thêm các cái khác qua AutoFilterColumns.SetFieldCriteria, thứ không đụng tới vùng và các field còn lại. Cả hai đường đều bỏ qua số field ngoài vùng mà không raise, nên hãy xác minh bằng cách đọc ngược, tốt nhất là sau khi mở lại file đã lưu:

Sh.ApplyAutoFilter('A1:D500', 1, 'North');                     // vùng + field 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');
Assert(Wb.Sheets[1].AutoFilterColumns.Find(1).Active);
Assert(Wb.Sheets[1].AutoFilterColumns.Find(3).Criteria1.DataType = 4);

Hãy nhớ rằng record AUTOFILTER là một định nghĩa được lưu: HotXLS ghi các tiêu chí và không đánh giá chúng trên worksheet XLS cổ điển, nên một pipeline cần các dòng khớp phía server phải tự tính chúng ở đó, trong khi facade XLSX cung cấp đánh giá cấp dòng như trình bày trong data validation, AutoFilter và tables trong HotXLS với Delphi. Một khi Excel thật sự ẩn dòng, mọi tổng bên dưới vùng phụ thuộc vào cách SUBTOTAL và AGGREGATE xử lý dòng ẩn và dòng đã lọc, cũng là chỗ tiếp theo một filter số lặng lẽ khớp 0 dòng lộ diện thành một con số sai

HotXLS đọc và ghi workbook BIFF8 XLS và XLSX nguyên bản từ Delphi và C++Builder, bao gồm tiêu chí AutoFilter với DOPER số, Boolean và AND/OR mà Excel đánh giá đúng như ý. Xem HotXLS Delphi spreadsheet component để biết tính năng, các edition và tải bản dùng thử