เกณฑ์ DOPER ของ AutoFilter ใน BIFF8 บน Delphi ด้วย HotXLS

HotXLS บันทึกเกณฑ์ AutoFilter แบบ BIFF8 ทุกตัวเป็น record AUTOFILTER ที่พกโครงสร้าง DOPER ขนาด 10 ไบต์สองชุด และชนิดของ DOPER คือตัวตัดสินว่า Excel จะเทียบแบบไหน ตั้งแต่ v2.384.45 TXLSWorksheet.ApplyAutoFilter เขียนการเทียบอย่าง '>=100' เป็น DOPER IEEE number เพื่อให้ Excel จับคู่กับ cell ตัวเลขแทนการเทียบแบบข้อความ รายงาน bug ที่กดให้เกิดการแก้ครั้งนี้สั้นจนน่าหงุดหงิด: export ประจำคืนใช้ filter กับคอลัมน์จำนวนเงิน ไฟล์เปิดได้โดยไม่มีปัญหา ลูกศร dropdown แสดงเกณฑ์อยู่ แต่ filter จับคู่ได้ศูนย์แถว ไม่มีอะไรพัง ไบต์ทั้งหมดเป็น BIFF8 ที่ถูกต้อง แค่ถูกแบบที่ผิด นั่นคือคลาสของความล้มเหลวที่บทความนี้จะพาไล่ดู พร้อมกับความผิดพลาดระดับไบต์รายเก่าอีกสองจุดที่แก้ใน v2.384.18

BIFF8 AutoFilter เก็บอะไรกันแน่

AutoFilter แบบ BIFF8 คือชุดของ record type สามตัว ไม่ใช่ตัวเดียว และมีแค่ record รายฟิลด์เท่านั้นที่เก็บเกณฑ์ AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) บอกจำนวนคอลัมน์ที่ช่วง filter ครอบคลุม FILTERMODE ($009B) เป็น marker ไร้เนื้อหาที่ HotXLS จะ emit ต่อเมื่อมีอย่างน้อยหนึ่งฟิลด์มีเกณฑ์ที่ทำงานอยู่ จากนั้นแต่ละฟิลด์ที่ active จะได้ record AUTOFILTER ของตัวเอง ($009E, §2.4.6): index ฟิลด์แบบ zero-based, word grbit ที่สองบิตล่างเป็น wJoin, DOPER สองตัวที่ใหญ่พอดี 10 ไบต์ และ tail เสริมที่เก็บตัวอักษรของ string DOPER ถ้ามี หมายเลขฟิลด์บนดิสก์เป็น zero-based แม้ ApplyAutoFilter จะนับฟิลด์จาก 1 ซึ่งสำคัญตอนที่คุณลงไปล่า record ใน hex dump ครั้งแรก ไบต์แรกของ DOPER แต่ละตัวคือ vt บอกว่า operand ที่ตามมาเป็นชนิดไหน:

$04 คือ IEEE 754 double ที่เก็บใน 8 ไบต์ที่เหลือ ซึ่งเป็นวิธีที่ Excel เก็บการเทียบแบบตัวเลข
$06 คือ string ที่ความยาวอยู่ในไบต์ cch เดียว ส่วนตัวอักษรถูกดันไปไว้ที่ tail ของ record
$08 คือค่า Bes ซึ่งเป็น Boolean หรือ error code ที่อัดลงสองไบต์
$0C กับ $0E ไม่มี operand ติดมา หมายถึงจับคู่ทุก cell ว่าง และจับคู่ทุก cell ที่ไม่ว่าง

ไบต์ที่สองคือ grbitSgn เก็บการเทียบ: 1 ถึง 6 เรียงไปตาม <, =, <=, >, <> และ >= HotXLS เก็บทั้งสองไบต์ไว้ให้ดูย้อนหลังได้ผ่าน AutoFilterColumns ซึ่ง item แต่ละตัวเปิดเผย Criteria1 กับ Criteria2 เป็น object TXLSAutofilterDOPER ที่มี DataType, grbitSgn และ Value คุณจึง assert กับสิ่งที่จะถูกเขียนออกไปได้ แทนการเดา

record AUTOFILTER แต่ละตัวพก DOPER 10 ไบต์สองตัว และไบต์ vt คือตัวตัดสินว่า Excel จะเทียบเกณฑ์เป็นตัวเลข ข้อความ Boolean หรือการทดสอบ cell ว่าง — อ่านทั้งสองค่าย้อนกลับผ่าน AutoFilterColumns ก่อนบันทึก

ทำไม filter '>=100' จึงไม่จับคู่แถวใดเลยใน Excel

filter จับคู่ไม่ได้อะไรเลยเพราะ operand ถูกเก็บเป็นข้อความ และ Excel เทียบ string DOPER กับ cell ในฐานะข้อความ ก่อน v2.384.45 CreateFilterDoper ใน lxFilter.pas ตัด prefix >= ออกได้ถูกต้อง ตั้ง sign เป็น 6 แล้วสร้าง DOPER vtString ที่เก็บตัวอักษร 100 เสมอ cell ตัวเลขที่เก็บ 250 ไม่มีทางผ่านการเทียบข้อความกับ "100" ทุกแถวจึงหลุดหมด ไม่มี exception ไม่มี diagnostic ไม่มี prompt ซ่อมไฟล์จาก Excel กฎตั้งแต่ v2.384.45 แคบอย่างมีเจตนา: ถ้าเกณฑ์ขึ้นต้นด้วยตัวดำเนินการเทียบและส่วนที่เหลือ parse เป็นตัวเลขได้ตามกฎ invariant-culture HotXLS จะเขียน DOPER vtIEEENumber ด้วย sign เดิม ส่วนค่าเปล่า ๆ ที่ไม่มีตัวดำเนินการยังอยู่ในรูป string เพราะนั่นคือวิธีที่ Excel เองเก็บ item ที่เลือกจากรายการ dropdown

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 (ฝั่ง API เป็น 1-based)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE number), grbitSgn = 6 (>=)
  // ก่อนแก้: DataType = 6 (string) ซึ่งไม่จับคู่อะไรเลย
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;

HotXLS เขียนเกณฑ์ AutoFilter >=100 เดิมได้ทั้งเป็น DOPER vtString ที่ไม่จับคู่ cell ตัวเลขใดเลย หรือเป็น DOPER vtIEEENumber ที่ grbitSgn 6 ซึ่ง Excel ประเมินเชิงตัวเลขกับจำนวน 250 นี่คือความล้มเหลวเงียบแบบศูนย์แถวที่ CreateFilterDoper แก้ใน v2.384.45 — ทั้งสองครั้งไบต์เป็น BIFF8 ที่ถูกต้อง — เปลี่ยนแค่ไบต์ชนิดของ operand Excel จึงเปิดไฟล์ได้ แสดงเกณฑ์ใน dropdown แต่ยังจับคู่ได้ศูนย์แถว

จุดที่ความคมที่เหลือซ่อนอยู่คือตอน parse operand จะผ่าน TryStrToFloat โดยใช้จุดเป็นตัวคั่นทศนิยม '>=1.5' จึงกลายเป็นตัวเลข แต่ '>=1,5' ยังเป็น string DOPER และจับคู่ไม่ได้อะไรอีกแบบเงียบ ๆ เหมือนเดิม ไม่ว่า Windows locale จะบอกอะไร วันที่เป็นกับดักเดียวกันในเครื่องแต่งตัวต่างออกไป: '>=2026-01-01' ไม่ใช่ตัวเลข มันจึงถูกเขียนเป็นข้อความ ขณะที่ Excel เก็บ cell วันที่เป็น serial number สำหรับการเทียบเท่ากับตัวเลข ทั้ง '=100' และ Variant ตัวเลขอย่าง 100 ให้ DOPER IEEE ที่ sign 2 เหมือนกัน แต่ string เปล่า ๆ อย่าง '100' ให้การจับคู่แบบข้อความ สร้าง operand ตัวเลขในโค้ดเถอะ อย่า format ไว้ให้คนอ่าน:

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

  // เกณฑ์ที่มีเศษทศนิยม: format ด้วยจุดเสมอ
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // วันที่: เทียบกับ serial number ที่ Excel เก็บไว้ใน cell
  // TDateTime ของ Delphi เท่ากับ serial ของระบบ 1900 สำหรับวันที่หลังมีนาคม 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

AND กับ OR ต่อเงื่อนไขสองตัวอย่างไร

บิต wJoin ของ grbit ใน AUTOFILTER เป็น 0 สำหรับ AND และ 1 สำหรับ OR ซึ่ง HotXLS มีสองค่าคงที่นี้กลับข้างกันมาจนถึง v2.384.18 filter แบบ between อย่างอย่างน้อย 100 และต่ำกว่า 500 ถูกบันทึกเป็นอย่างน้อย 100 หรือต่ำกว่า 500 ซึ่งในทางปฏิบัติจับคู่ทุกตัวเลข และดูเหมือน filter ไม่ทำงานเฉย ๆ ค่าคงที่ตัวดำเนินการสาธารณะยังเพิ่มอันตรายอีกชั้นตอนพอร์ตโค้ด ใน HotXLS xlAnd เป็น 0 และ xlOr เป็น 1 ขณะที่ Excel automation นับเป็น 1 กับ 2 XlAutoFilterOperator เป็น Byte ธรรมดา โค้ดที่แปลมาจาก macro VBA ที่ใช้ literal number จึง compile ผ่านสบาย และ literal 1 ที่เคยหมายถึง AND ใน COM ตอนนี้กลายเป็น OR ใช้ค่าคงที่แบบมีชื่อแล้วปัญหานี้จะไม่เกิด:

// จำนวนเงินระหว่าง 100 (รวม) กับ 500 (ไม่รวม)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');

with Sh.AutoFilterColumns.Find(3) do
begin
  Assert(Operator = xlAnd);            // wJoin = 0 บนดิสก์
  Assert(Criteria2.grbitSgn = 1);      // 1 = น้อยกว่า
end;

ผังบิต wJoin ของ HotXLS สำหรับเกณฑ์ AutoFilter ที่ 0 ต่อ DOPER สองตัวด้วย AND และ 1 ด้วย OR, เส้นตัวเลขที่แสดงว่าค่าคงที่ที่สลับกันก่อน v2.384.18 ขยาย filter between ให้กลายเป็น OR ที่จับคู่ทุกอย่างได้อย่างไร และการชนกันของลำดับเลข xlAnd xlOr กับ Excel automation — ค่าคงที่ wJoin ที่กลับข้างเปลี่ยน filter between ให้จับคู่ทุกตัวเลข และ VBA ที่แปลมายัง compile ผ่านเพราะ XlAutoFilterOperator เป็น Byte ธรรมดา — literal 1 ที่เคยหมายถึง AND ภายใต้ COM automation ที่นี่กลายเป็น OR

Boolean, cell ว่าง และเพดาน 255 ตัวอักษร

เกณฑ์ Boolean ถูกเก็บเป็นค่า Bes ([MS-XLS] §2.5.10) โดย Bes วางไบต์ค่า bBoolErr ไว้ก่อนและธง fError ไว้ทีหลัง HotXLS เขียนสลับลำดับกันมาก่อน v2.384.18 filter หา TRUE จึงเอา 1 ไปใส่ในธง error และ Excel อ่านเกณฑ์นั้นเป็น error code writer กับ reader สลับกันพร้อมกันทั้งคู่ HotXLS จึง round-trip ไฟล์ของตัวเองได้ลื่นโดยที่ Excel ไม่เห็นด้วย เป็นเครื่องเตือนใจว่า round trip ที่สอดคล้องกันเองไม่พิสูจน์อะไรเรื่องความตรงสเปกเลย ส่วน cell ว่างไม่ต้องมี operand เลย: ส่ง '=' ตัวเดียวจะได้ DOPER จับคู่ทุก cell ว่าง ($0C) และ '<>' ตัวเดียวได้ DOPER จับคู่ทุก cell ที่ไม่ว่าง ($0E)

เกณฑ์ string เจอขีดจำกัดจริง ๆ ในผังของ DOPER ฟิลด์ความยาว cch เป็นไบต์เดียว operand แบบ string จึงเกิน 255 ตัวอักษรไม่ได้ และ CreateFilterDoper จะตัดข้อความที่ยาวกว่านั้นทิ้งหลังตัดตัวดำเนินการออก แทนที่จะปล่อยให้ไบต์ความยาววนกลับและทำ tail ของ record เพี้ยน การตัดนี้เงียบสนิท filter บนคอลัมน์คำอธิบายยาว ๆ อาจจับคู่ไม่เหมือนกับข้อความเต็มที่คุณส่งเข้าไป ใน BIFF8 tail เก็บ string แต่ละตัวเป็นธงหนึ่งไบต์ตามด้วย UTF-16 code units และขนาด record ที่ประกาศไว้ต้องนับไบต์เหล่านี้ให้ตรงเป๊ะ วินัยการทำบัญชีแบบเดียวกับที่เล่าไว้ในเรื่องการเลื่อนของการประกาศความยาว record BIFF ในตัวเขียน XLS สำหรับ Delphi

ทำไมการเรียก ApplyAutoFilter ครั้งที่สองถึงลบครั้งแรกทิ้ง

การเรียก ApplyAutoFilter แต่ละครั้งนิยามช่วง filter ทั้งหมดใหม่ มีแค่เกณฑ์จากครั้งสุดท้ายที่รอด ภายในมันเรียก SetAutoFilter ซึ่งเคลียร์ทุกฟิลด์ก่อนสร้างช่วงใหม่ ถูกต้องสำหรับหนึ่งคอลัมน์ แต่เซอร์ไพรส์สำหรับสอง ถ้าจะ filter หลายคอลัมน์ ให้เรียก 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');
Assert(Wb.Sheets[1].AutoFilterColumns.Find(1).Active);
Assert(Wb.Sheets[1].AutoFilterColumns.Find(3).Criteria1.DataType = 4);

อย่าลืมว่า record AUTOFILTER เป็นนิยามที่เก็บไว้: HotXLS เขียนเกณฑ์แต่ไม่ประเมินมันบน worksheet XLS คลาสสิก pipeline ที่ต้องการแถวที่ตรงเงื่อนไขบนเซิร์ฟเวอร์จึงต้องคำนวณเองที่นั่น ขณะที่ facade XLSX มีการประเมินระดับแถวให้ ตามที่แสดงในdata validation, AutoFilter และตารางใน HotXLS บน Delphi พอ Excel ซ่อนแถวจริง ๆ ยอดรวมใต้ช่วงนั้นก็ขึ้นอยู่กับเรื่อง SUBTOTAL กับ AGGREGATE จัดการแถวที่ถูกซ่อนและกรองอย่างไร ซึ่งเป็นจุดต่อไปที่ filter ตัวเลขที่จับคู่ไม่ได้อะไรเงียบ ๆ จะโผล่มาเป็นตัวเลขผิด

HotXLS อ่านและเขียน workbook BIFF8 XLS กับ XLSX ได้จากตัวเองบน Delphi และ C++Builder รวมถึงเกณฑ์ AutoFilter ทั้งแบบตัวเลข Boolean และ DOPER AND/OR ที่ Excel ประเมินได้ตามตั้งใจ ดูรายละเอียด feature, edition และดาวน์โหลดรุ่นทดลองได้ที่HotXLS Delphi spreadsheet component