LlosLab Software Development BlogИнженерни бележки за разработчици на PDF, Delphi и софтуер за документи.

Техническа статия

BIFF8 AutoFilter DOPER критерии в Delphi с HotXLS

HotXLS Delphi Component

HotXLS записва всеки BIFF8 AutoFilter критерий като AUTOFILTER record, който носи две 10-байтови DOPER структури, а типът на DOPER-а решава как Excel сравнява. От v2.384.45 нататък TXLSWorksheet.ApplyAutoFilter записва сравнение от рода на '>=100' като IEEE number DOPER, така че Excel съпоставя числови клетки, вместо да сравнява текст. Бъг репортът, който е причина за промяната, беше кратък и вбесяващ: nightly export прилага филтър върху колона с суми, файлът се отваря без оплаквания, стрелката на dropdown-а показва критерия, а филтърът съвпада с нула реда. Нищо не е повредено. Байтовете са валиден BIFF8, просто грешният вид валидност, и точно този клас провали проследява статията, заедно с две по-стари грешки на байтово ниво, оправени в v2.384.18

Какво всъщност съхранява BIFF8 AutoFilter?

BIFF8 AutoFilter е набор от три типа record-и, не един, и само per-field record-ът носи критерии. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) записва от колко колони се състои диапазонът на филтъра. FILTERMODE ($009B) е маркер без тяло, който HotXLS излъчва само когато поне едно поле има активен критерий. После всяко активно поле получава свой AUTOFILTER record ($009E, §2.4.6): zero-based индекс на полето, grbit дума, чиито два най-ниски бита са wJoin, два DOPER-а с по точно 10 байта и опционална опашка, която пази символите на всеки string DOPER. Индексът на полето е zero-based на диска, макар ApplyAutoFilter да брои полетата от 1, което има значение при първото издирване на record в hex dump. Първият байт на всеки DOPER, vt, казва какъв операнд следва:

  • $04 е IEEE 754 double в останалите 8 байта — така Excel съхранява числово сравнение
  • $06 е string, чиято дължина стои в един-единствен cch байт, а самите символи са изместени в опашката на record-а
  • $08 е Bes стойност, Boolean или error code, опакован в два байта
  • $0C и $0E не носят операнд и значат „съвпада с всички празни“ и „съвпада с всички непразни“

Вторият байт, grbitSgn, пази сравнението: 1 до 6 отговарят на <, =, <=, >, <> и >=. HotXLS държи двата байта видими и след факта чрез AutoFilterColumns, чиито елементи излагат Criteria1 и Criteria2 като TXLSAutofilterDOPER обекти с DataType, grbitSgn и Value, така че можете да проверите с assert какво ще бъде записано, вместо да гадаете

Анатомия на AUTOFILTER record-а на HotXLS: zero-based индекс на полето, grbit дума, чиито два ниски бита пазят wJoin, и две 10-байтови DOPER структури, чийто vt байт избира операнд IEEE number, string, Bes Boolean, blank или non-blank, докато grbitSgn кодира оператора за сравнение, който Excel прилага
Всеки AUTOFILTER record носи два 10-байтови DOPER-а, а vt байтът решава дали Excel ще сравнява критерия като число, текст, Boolean или blank тест — прочетете и двата обратно чрез AutoFilterColumns, преди да запишете

Защо филтър '>=100' не съвпада с нито един ред в Excel?

Филтърът не съвпадна с нищо, защото операндът е записан като текст, а Excel сравнява string DOPER с клетката като текст. Преди v2.384.45 CreateFilterDoper в lxFilter.pas правилно отстраняваше префикса >= и задаваше sign 6, но после винаги строеше vtString DOPER със символите 100. Числова клетка със 250 никога не минава текстово сравнение срещу "100", така че изпадаше всеки ред. Без exception, без diagnostics, без repair prompt от Excel. Правилото от v2.384.45 нататък е нарочно тясно: ако критерият започва с оператор за сравнение и остатъкът се парсва като число по invariant-culture правила, HotXLS записва vtIEEENumber DOPER със същия sign. Голата стойност без оператор остава в string форма, защото точно така Excel сам съхранява елемент, избран от 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 (1-based от страната на API-я)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE number), grbitSgn = 6 (>=)
  // Преди fix-а: DataType = 6 (string), което не съвпадаше с нищо
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;
HotXLS записва същия >=100 AutoFilter критерий като vtString DOPER, който не съвпада с нито една числова клетка, или като vtIEEENumber DOPER с grbitSgn 6, който Excel оценява числово срещу сумата 250 — тихият zero-row провал, който CreateFilterDoper оправи в v2.384.45
И двата пъти байтовете са валиден BIFF8 — сменен е само байтът за типа на операнда, затова Excel отвори файла, показа критерия в dropdown-а и все пак съвпадна с нула реда

Парсването е мястото, където живеят останалите остри ръбове. Операндът минава през TryStrToFloat с точка като десетичен разделител, така че '>=1.5' става число, а '>=1,5' си остава string DOPER и отново тихо не съвпада с нищо, каквото и да е Windows локалът. Датите са същият капан в друг костюм: '>=2026-01-01' не е число, затова се записва като текст, докато Excel държи клетките с дати като serial numbers. За равенство по число и '=100', и числов Variant като 100 дават IEEE DOPER със sign 2, докато голият string '100' дава текстово съвпадение. Стройте числовите операнди в кода, а не като форматиране за хора:

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

  // Праг с дробна част: винаги форматирайте с точка
  Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));

  // Дати: сравнявайте със serial number-а, който Excel пази в клетката.
  // Delphi TDateTime съответства на serial от 1900-системата за дати след март 1900
  Since := EncodeDate(2026, 1, 1);
  Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Trunc(Since)),
    xlAnd, Unassigned);
end;

Как AND и OR съединяват две условия?

Битовете wJoin на AUTOFILTER grbit са 0 за AND и 1 за OR, а HotXLS е държал тези две константи разменени до v2.384.18. Филтър от between тип като поне 100 и под 500 се е записвал като поне 100 или под 500, което на практика съвпада с всяко число и изглежда така, сякаш филтърът просто не е приложен. Публичните операторски константи добавят втори porting риск. В HotXLS xlAnd е 0, а xlOr е 1, докато Excel automation ги номерира 1 и 2. XlAutoFilterOperator е обикновен Byte, така че код, преведен от VBA макрос с литерални числа, се компилира чисто, а литералът 1, който е значел AND в COM, тука значи OR. Ползвайте именуваните константи и проблемът не може да възникне:

// Amount между 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 при AutoFilter критериите в HotXLS, където 0 съединява два DOPER-а с AND, а 1 с OR, числова ос, показваща как разменените константи преди v2.384.18 разширяват between филтър до съвпадащ с всичко OR, и сблъсъкът в номерирането на xlAnd и xlOr с Excel automation
Разменените wJoin константи превръщат between филтър в такъв, който съвпада с всяко число, а преведеният VBA пак се компилира, защото XlAutoFilterOperator е обикновен Byte — литерал 1, който е значел AND при COM automation, тука значи OR

Booleans, blanks и таванът от 255 символа

Boolean критерий се съхранява като Bes стойност ([MS-XLS] §2.5.10), а Bes слага първо value байта bBoolErr и чак тогава флага fError. HotXLS ги е записвал в обратен ред до v2.384.18, така че филтър за TRUE е слагал 1 в error флага и Excel е четял критерия като error code. Writer и reader са били разменени заедно, затова HotXLS е минавал през собствените си файлове без оплаквания, докато Excel е възразявал — напомняне, че самосъгласуван round trip не доказва нищо за съответствие със спецификацията. Празните клетки изобщо не се нуждаят от операнд: самостоятелен '=' дава match-all-blanks DOPER ($0C), а самостоятелен '<>' — match-all-non-blanks DOPER ($0E)

String критериите удрят твърда граница в DOPER подредбата. Полето за дължина cch е един байт, така че string операнд не може да надхвърли 255 символа, а CreateFilterDoper отрязва по-дългия текст след отстраняването на оператора, вместо да допусне length байтът да превърти и да разсинхронизира опашката на record-а. Отрязването е тихо и филтър върху дълга колона с описания може да съвпада различно от пълния текст, който сте подали. В BIFF8 опашката пази всеки string като еднобайтов flag, следван от UTF-16 code units, а обявеният размер на record-а трябва да брои тези байтове точно — същата счетоводна дисциплина, за която пише как декларациите за дължина на BIFF record се разминават в Delphi XLS writer

Защо второ извикване на ApplyAutoFilter заличава първото?

Всяко извикване на ApplyAutoFilter предефинира целия диапазон на филтъра, така че оцелява само критерият от последното извикване. Вътрешно то вика SetAutoFilter, който изчиства всяко поле, преди да rebuild-не диапазона, и това е коректно за една колона и изненадващо за две. За да филтрирате няколко колони, извикайте ApplyAutoFilter веднъж, за да установите диапазона и първия критерий, а другите добавете през AutoFilterColumns.SetFieldCriteria, който не пипа диапазона и останалите полета. И двата пътя игнорират номер на поле извън диапазона без да вдигат exception, така че проверявайте с четене обратно, в идеалния случай след повторно отваряне на записания файл:

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);

Имайте предвид, че AUTOFILTER record-ът е съхранена дефиниция: HotXLS записва критериите и не ги оценява върху класическия XLS worksheet, така че pipeline, който има нужда от съвпадащите редове на сървъра, трябва сам да ги смета там, докато XLSX фасадата предлага оценка на ниво ред, както е показано в HotXLS data validation, AutoFilter и таблици в Delphi. Щом Excel все пак скрие редовете, всички суми под диапазона зависят от как SUBTOTAL и AGGREGATE третират скритите и филтрираните редове — следващото място, където числов филтър, който тихо не съвпада с нищо, се показва като грешно число

HotXLS чете и записва BIFF8 XLS и XLSX работни книги нативно от Delphi и C++Builder, включително AutoFilter критерии с numeric, Boolean и AND/OR DOPER-и, които Excel оценява както е предвидено. Вижте HotXLS Delphi spreadsheet компонента за възможности, издания и trial изтегляне