LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

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

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

HotXLS Delphi Component

HotXLS сохраняет каждый критерий AutoFilter BIFF8 в записи AUTOFILTER, несущей две 10-байтовые структуры DOPER, и именно тип DOPER решает, как будет сравнивать Excel. Начиная с v2.384.45, TXLSWorksheet.ApplyAutoFilter пишет сравнение вроде '>=100' как DOPER с IEEE-числом, так что Excel сопоставляет числовые ячейки, а не сравнивает текст. Баг-репорт, из-за которого появилось это изменение, был коротким и доводящим до белого каления: ночная выгрузка вешала фильтр на колонку с суммами, файл открывался без единой жалобы, стрелка выпадающего списка показывала критерий, а фильтр совпадал с нулём строк. Ничего не было повреждено. Байты были валидным BIFF8 — просто валидным не того рода, и именно этот класс сбоев разбирается в статье вместе с двумя более старыми байтовыми ошибками, исправленными в v2.384.18

Что на самом деле хранит AutoFilter BIFF8?

AutoFilter BIFF8 — это набор из трёх типов записей, а не одна, и критерии хранит только запись отдельного поля. AUTOFILTERINFO ($009D, [MS-XLS] §2.4.8) записывает, сколько колонок покрывает диапазон фильтра. FILTERMODE ($009B) — маркер без тела, который HotXLS выпускает, только когда хотя бы у одного поля есть активный критерий. Затем каждое активное поле получает собственную запись AUTOFILTER ($009E, §2.4.6): индекс поля с нумерацией от нуля, слово grbit, чьи младшие два бита — это wJoin, два DOPER ровно по 10 байтов каждый и опциональный хвост, в котором лежат символы строкового DOPER. Индекс поля на диске нумеруется от нуля, хотя ApplyAutoFilter нумерует поля с единицы, — и это всплывает при первой же охоте за записью в hex-дампе. Первый байт каждого DOPER, vt, говорит, какого рода операнд следует дальше:

  • $04 — это IEEE 754 double в оставшихся 8 байтах, так Excel хранит числовое сравнение
  • $06 — строка, чья длина живёт в одном байте cch, а сами символы выталкиваются в хвост записи
  • $08 — значение Bes, логическое значение или код ошибки, упакованные в два байта
  • $0C и $0E не несут операнда и означают «совпадать со всеми пустыми» и «совпадать со всеми непустыми»

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

Анатомия записи AUTOFILTER в HotXLS: индекс поля с нумерацией от нуля, слово grbit, чьи младшие два бита держат wJoin, и две 10-байтовые структуры DOPER, чей байт vt выбирает операнд IEEE-число, строку, логическое Bes, пустую или непустую ячейку, тогда как grbitSgn кодирует оператор сравнения, который применит Excel
Каждая запись AUTOFILTER несёт два 10-байтовых DOPER, и байт vt решает, будет ли Excel сравнивать критерий как число, текст, логическое значение или тест на пустоту — прочитайте оба обратно через AutoFilterColumns до сохранения

Почему фильтр '>=100' не совпал ни с одной строкой в Excel?

Фильтр не совпал ни с чем, потому что операнд был сохранён как текст, а строковый DOPER Excel сравнивает с ячейкой как текст. До v2.384.45 CreateFilterDoper в lxFilter.pas корректно срезал префикс >= и ставил знак 6, а затем всегда строил DOPER vtString с символами 100. Числовая ячейка с 250 никогда не удовлетворит текстовое сравнение с "100", поэтому выпадали все строки. Ни исключения, ни диагностики, ни предложения починки от Excel. Правило с v2.384.45 нарочно узкое: если критерий начинается с оператора сравнения, а остаток парсится как число по правилам инвариантной культуры, HotXLS пишет DOPER vtIEEENumber с тем же знаком. Голое значение без оператора сохраняет строковую форму — именно так сам 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 нумерация с единицы)
  Sh.ApplyAutoFilter('A1:B100', 2, '>=100');

  Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
  // v2.384.45+: DataType = 4 (IEEE-число), grbitSgn = 6 (>=)
  // До исправления: DataType = 6 (строка), что не совпадало ни с чем
  Assert(Doper.DataType = 4);

  Wb.SaveAs('orders.xls');
end;
HotXLS пишет один и тот же критерий AutoFilter >=100 либо как DOPER vtString, не совпадающий ни с одной числовой ячейкой, либо как DOPER vtIEEENumber с grbitSgn 6, который Excel вычисляет численно против суммы 250, — та самая тихая ошибка нуля строк, которую CreateFilterDoper исправил в v2.384.45
Оба раза байты были валидным BIFF8 — сменился только байт типа операнда, потому Excel и открывал файл, и показывал критерий в выпадающем списке, и всё же совпадал с нулём строк

Острые углы прячутся в разборе. Операнд прогоняется через TryStrToFloat с точкой в роли десятичного разделителя, так что '>=1.5' становится числом, а '>=1,5' остаётся строковым DOPER и снова молча не совпадает ни с чем — что бы ни говорил локал Windows. Даты — та же ловушка в другом костюме: '>=2026-01-01' не число, поэтому пишется текстом, тогда как Excel держит ячейки дат как серийные номера. Для равенства на числе и '=100', и числовой Variant вроде 100 дают IEEE DOPER со знаком 2, а голая строка '100' даёт текстовое совпадение. Стройте числовые операнды в коде, а не форматируйте их для человека:

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

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

  // Даты: сравнивайте с серийным номером, который Excel хранит в ячейке.
  // Delphi TDateTime равен серийному номеру системы 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, и до v2.384.18 HotXLS держал эти две константы перепутанными. Фильтр в духе «between», скажем «не меньше 100 и меньше 500», сохранялся как «не меньше 100 или меньше 500», что на практике совпадает с любым числом и выглядит так, будто фильтр просто не применился. Публичные константы операторов добавляют вторую опасность при переносе кода. В HotXLS xlAnd — это 0, а xlOr — 1, тогда как Excel automation нумерует их 1 и 2. XlAutoFilterOperator — простой Byte, так что код, переведённый из VBA-макроса с литеральными числами, компилируется чисто, а литерал 1, означавший в COM AND, здесь означает 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 константы расширяли between-фильтр до OR, совпадающего со всем, и конфликт нумерации xlAnd и xlOr с Excel automation
Перепутанные константы wJoin превращали between-фильтр в совпадающий с любым числом, а переведённый VBA по-прежнему компилируется, потому что XlAutoFilterOperator — простой Byte: литерал 1, означавший AND при COM automation, здесь означает OR

Логические значения, пустые ячейки и потолок в 255 символов

Логический критерий хранится как значение Bes ([MS-XLS] §2.5.10), и Bes кладёт байт значения bBoolErr первым, а флаг fError вторым. HotXLS до v2.384.18 писал их в обратном порядке, поэтому фильтр по TRUE клал 1 в флаг ошибки, и Excel читал критерий как код ошибки. Писатель и читатель были перепутаны вместе, оттого HotXLS гонял round-trip собственных файлов без жалоб, а Excel был не согласен, — напоминание, что самосогласованный round-trip ничего не доказывает про соответствие спецификации. Пустым ячейкам операнд не нужен вовсе: передача одного '=' даёт DOPER «все пустые» ($0C), а одного '<>' — DOPER «все непустые» ($0E)

Строковые критерии упираются в жёсткий лимит раскладки DOPER. Поле длины cch — один байт, так что строковый операнд не может превышать 255 символов, и CreateFilterDoper обрезает более длинный текст после срезания оператора, а не даёт байту длины переполниться и рассинхронизировать хвост записи. Обрезка молчалива, и фильтр по длинной колонке описаний может совпадать не так, как переданный вами полный текст. В BIFF8 хвост хранит каждую строку как однобайтовый флаг и следующие за ним кодовые единицы UTF-16, и объявленный размер записи должен считать эти байты точно — та же бухгалтерская дисциплина, что разобрана в статье о дрейфе объявлений длины BIFF-записей в Delphi-писателе XLS

Почему повторный вызов ApplyAutoFilter стирает первый?

Каждый вызов ApplyAutoFilter переопределяет весь диапазон фильтра целиком, так что выживает только критерий последнего вызова. Внутри он зовёт 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');
Assert(Wb.Sheets[1].AutoFilterColumns.Find(1).Active);
Assert(Wb.Sheets[1].AutoFilterColumns.Find(3).Criteria1.DataType = 4);

Имейте в виду: запись AUTOFILTER — это сохранённое определение. HotXLS пишет критерии и не вычисляет их на классическом XLS-листе, так что конвейеру, которому совпавшие строки нужны на сервере, придётся считать их там самому, тогда как XLSX-фасад предлагает вычисление на уровне строк, как показано в статье о валидации данных, AutoFilter и таблицах в HotXLS. А когда Excel всё же скрывает строки, любые итоги под диапазоном зависят от того, как SUBTOTAL и AGGREGATE обращаются со скрытыми и отфильтрованными строками, — следующее место, где числовой фильтр, молча не совпавший ни с чем, вылезает неправильным числом

HotXLS читает и пишет книги BIFF8 XLS и XLSX нативно из Delphi и C++Builder, включая критерии AutoFilter с числовыми, логическими и AND/OR DOPER, которые Excel вычисляет как задумано. Возможности, редакции и пробную загрузку смотрите на странице компонента электронных таблиц HotXLS для Delphi