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

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

Колонка сумм хранит 250, 730 и 1040. Сгенерированный .xls применяет к этой колонке AutoFilter с условием >=100, Excel открывает файл без предложения ремонта, в выпадающем списке заголовка видно, что фильтр установлен, и каждая строка данных исчезает. Байты — корректный BIFF8. Вся проблема в одном байте типа: операнд 100 был сохранён как строка, Excel сравнивает строковый операнд как текст, и ни одна числовая ячейка не проходит текстовое сравнение с "100". Сохраните операнд как IEEE double, и тот же фильтр оставит все три строки

Где AutoFilter BIFF8 хранит свои критерии

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

Первый байт каждого DOPER, vt, говорит, какой операнд следует дальше:

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

Второй байт, grbitSgn, — это сравнение: 1–6 означают <, =, <=, >, <> и >=. В HotXLS оба байта остаются доступными для осмотра после установки критерия. TXLSWorksheet.AutoFilterColumns возвращает TXLSAutofilter, его метод Find возвращает элемент для поля с нумерацией от единицы, а этот элемент выставляет наружу 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?

Фильтр не совпадает ни с чем, потому что текстовый операнд принуждает текстовое сравнение. Естественный первый вариант писателя корректно срезает префикс >=, выставляет знак в 6 и затем выпускает DOPER vtString с символами 100, ведь любой критерий и так приходит строкой — и в собственном диалоге Excel, и в вызове AutoFilter из VBA. Excel принимает такую запись и вычисляет её ровно как написано. Числовая ячейка с 250 в этом сравнении никогда не "больше или равна" тексту "100", поэтому колонка пустеет

TXLSWorksheet.ApplyAutoFilter и TXLSAutofilter.SetFieldCriteria конвертируют критерии одинаково. Строка, начинающаяся с одного из шести операторов, за которым следует текст, разбирающийся как число, становится DOPER vtIEEENumber с этим оператором в качестве знака. Числовой Variant (Integer, Int64, Double, Currency и прочие порядковые и плавающие типы) становится числовым DOPER со знаком 2 — проверкой на равенство. Логический Variant становится DOPER Bes. Голая строка без оператора остаётся строковым 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 поля нумеруются с единицы)
  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;
Тот же критерий AutoFilter >=100, сохранённый как DOPER vtString, не совпадает ни с одной числовой ячейкой, тогда как DOPER vtIEEENumber с grbitSgn 6, который пишет HotXLS, вычисляется численно против суммы 250
Байты в обоих случаях — корректный BIFF8, отличается только байт типа операнда; потому Excel открывает строковый вариант, показывает критерий в выпадающем списке и всё равно не совпадает ни с одной строкой

Правило про голую строку — то, что ловит людей. В Delphi '100' и 100 выглядят взаимозаменяемыми как аргументы Variant, однако первый даёт текстовое совпадение, а второй — числовое равенство. Угадывание было бы хуже. Колонка артикулов, хранящая 00100 как текст, получила бы числовой DOPER для 100 и не совпала бы ни с одной собственной ячейкой. Literal-строки без оператора повторяют то, что Excel пишет при выборе из списка, а когда вы имеете в виду число — передавайте число или пишите '=100', что даёт IEEE DOPER со знаком 2

Десятичные разделители, даты и система дат 1904

Числовой разбор после оператора всегда использует точку как десятичный разделитель, что бы ни говорили региональные настройки Windows. '>=1.5' становится числовым DOPER; '>=1,5' не разбирается, остаётся строковым DOPER и опустошает числовую колонку на немецкой или французской машине так же надёжно, как на американской. Разделители группировки проваливаются так же, поэтому '>=1,000' — это текст. Пробельные символы между оператором и цифрами срезаются, так что '>= 100' — нормально. Фиксация разделителя в одном месте избавляет отчёт от разного поведения в зависимости от того, какой сервер его сгенерировал, и это правильное значение по умолчанию для файлового формата

Даты — та же ловушка в другом костюме. '>=2026-01-01' — не число, поэтому записывается текстом, тогда как Excel держит ячейки с датами как порядковые номера и сравнивает их численно. Передавайте вместо неё порядковый номер. Целая часть Delphi-TDateTime совпадает с порядковым номером системы 1900 Excel для любой даты с 1 марта 1900 и позже. Книга, переключённая на систему дат 1904, считает от 1 января 1904, поэтому её номера на 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 объединяют два условия?

Биты wJoin в grbit записи AUTOFILTER равны 0 для AND и 1 для OR, и перепутать их легко и дорого. Поменяйте местами — и диапазонный фильтр вроде «не меньше 100 и ниже 500» сохранится как «не меньше 100 или ниже 500», что совпадает с любым числом и выглядит так, будто фильтр вообще не применялся. HotXLS пишет 0 для xlAnd и 1 для xlOr, а при участии критериев равенства ещё и выставляет биты fSimple1 и fSimple2 рядом с wJoin, которые помечают DOPER со знаком 2

Публичные константы приносят свою опасность при портировании. В HotXLS xlAnd — это 0, а xlOr — 1, тогда как автоматизация Excel нумерует их 1 и 2. XlAutoFilterOperator объявлен как простой Byte, поэтому макрос VBA, переведённый с literal-числами, компилируется без предупреждения. Literal 1, означавший AND под COM, теперь означает OR. Literal 2, означавший OR, сталкивается с xlBottom10Items, и поскольку HotXLS трактует любое значение оператора, кроме xlOr, как AND при построении двух DOPER, тот критерий соединяется через AND. Оба literal'а перевёрнуты. Именованные константы избавляют от всей проблемы:

// Сумма от 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;
Раскладка битов wJoin для критериев AutoFilter в HotXLS, где 0 соединяет два DOPER через AND, а 1 — через OR; числовая прямая, показывающая, как перепутанные константы раздувают фильтр-диапазон в OR, совпадающий со всем, и конфликт нумерации xlAnd и xlOr с автоматизацией Excel
Перепутанные константы wJoin превращают фильтр-диапазон в совпадающий с любым числом, а переведённый VBA всё равно компилируется, потому что XlAutoFilterOperator — простой Byte: literal 1, означавший AND под COM-автоматизацией, здесь означает OR

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

Логический критерий хранится как значение Bes ([MS-XLS] §2.5.10), а Bes ставит байт значения bBoolErr первым и флаг fError вторым. Запишите их в обратном порядке — и фильтр по TRUE положит 1 во флаг ошибки, так что Excel прочитает критерий как код ошибки. Скверная часть в том, что читатель с той же перестановкой идеально гоняет по кругу собственные файлы. Библиотека, читающая только то, что сама написала, ничего не доказывает о соответствии; а вот открытие файла в Excel и проверка диалога фильтра — доказывает. HotXLS пишет bBoolErr первым и fError как 0, поэтому передача True даёт DOPER с DataType 8 и Value 1

Тесты на пустоту операнда не требуют. Строка '=' сама по себе даёт DOPER совпадения со всеми пустыми ($0C), а '<>' сама по себе — DOPER совпадения со всеми непустыми ($0E). Оба — полноценные критерии, поэтому поле считается активным и FILTERMODE записывается

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

Фильтрация нескольких колонок и проверка сохранённого файла

Каждый вызов 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, как показано в статье о валидации данных, AutoFilter и таблицах в HotXLS для Delphi. Когда Excel скроет строки, любые итоги под диапазоном зависят от того, как SUBTOTAL и AGGREGATE обращаются со скрытыми и отфильтрованными строками, и числовой фильтр, сохранённый как текст, проявится там как итоговый ноль

Описанная выше конверсия критериев, раскладка Bes и биты соединения — то, что HotXLS пишет в каждую сохраняемую книгу BIFF8, рядом с родной поддержкой XLSX для Delphi и C++Builder. Редакции, полный список возможностей и пробная загрузка — на странице HotXLS Delphi spreadsheet component