Запуск Excel AutoFilter без Excel в Delphi: HotXLS

HotXLS вычисляет критерии AutoFilter, уже сохранённые в XLSX-файле, не запуская Excel. С v2.384.136 TXLSXWorksheet.PreviewAutoFilterRows, ApplyAutoFilter и AutoFilterRowVisible исполняют списки значений, группы дат, фильтры Top/Bottom-n и процентные, относительные даты и средние, фильтры по цвету заливки и шрифта и фильтры по иконкам условного форматирования — под фиксированной опорной датой и явными лимитами ресурсов

Обычный триггер — книга, которую человек отфильтровал в Excel, и сервер, которому надо с ней что-то сделать. Кто-то выставляет "Top 10 by revenue", "This month" на дате заказа и красную заливку на просроченных строках, сохраняет и загружает файл. вашему Delphi-сервису теперь нужен набор строк, который показал бы Excel: посчитать его, экспортировать или записать файл обратно со скрытыми нужными строками. Файл хранит только определение. До этого релиза HotXLS умел вычислять обычные кастомные критерии, но продвинутые виды лежали в сохранённом XML без единого исполнителя, и честным ответом было «откройте в Excel»

Что HotXLS реально исполняет из сохранённого AutoFilter?

HotXLS исполняет один вид фильтра на колонку, взятый из элемента filterColumn, который записал Excel, и комбинирует колонки через AND — ровно как дропдауны Excel. Элементы приходят из группы AutoFilter в ECMA-376 Part 1 §18.3.2, и каждый отображается на свой отдельный путь вычисления:

• filters с потомками filter и dateGroupItem: отмеченный список значений, необязательная запись пустых и календарные группы дат от года до секунды, объединённые OR
• customFilters: один или два критерия сравнения, соединённых AND или OR, — путь, существовавший и раньше
• top10: Top или Bottom n элементов, или n процентов, с val в пределах 1–500 элементов или 1–100 процентов
• dynamicFilter: сегодня, вчера, завтра, эта/прошлая/следующая неделя, месяц, квартал и год, с начала года, M1–M12, Q1–Q4, выше среднего и ниже среднего
• colorFilter и iconFilter: эффективный цвет заливки или шрифта, разрешённый через дифференциальный стиль, и иконки условного форматирования по набору иконок и zero-based id иконки

Всё вне этого списка проваливается с EXLSXAutoFilterUnsupported вместо выдачи угаданного набора строк. Туда входят типы выше и ниже стандартного отклонения из ST_DynamicFilterType, негригорианские значения calendarType, неизвестные элементы расширений, неразрешимые ссылки dxfId и колонка, каким-то образом несущая два вида фильтра разом. Движок фильтра, тихо возвращающий «почти те» строки, хуже отказывающего, потому что никто не заметит, пока отчёт не уйдёт с чужими клиентами внутри

Сперва предпросмотр, потом применение

HotXLS делит работу на read-only предпросмотр и транзакционное применение, и оба принимают одну и ту же запись TXLSXAutoFilterExecutionOptions. PreviewAutoFilterRows возвращает число подходящих строк данных и никогда не трогает видимость строк. ApplyAutoFilter(AResetVisible, Options) сперва готовит решения по всем строкам и лишь потом публикует новый набор скрытых строк одним шагом, так что исключение во время подготовки оставляет лист ровно как был

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Options: TXLSXAutoFilterExecutionOptions;
  Matching: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders-filtered-by-sales.xlsx');
    Sheet := Book.Sheets[1];   // индексатор Sheets нумеруется с единицы

    // Всегда стартуйте с значений по умолчанию, затем меняйте отдельные поля
    Options := XLSDefaultAutoFilterExecutionOptions;
    Options.ReferenceDate := EncodeDate(2026, 10, 19);

    Matching := Sheet.PreviewAutoFilterRows(nil, Options);  // только счёт
    WriteLn('Rows Excel would show: ', Matching);

    // True: подходящие строки становятся видимыми, даже если были скрыты
    Sheet.ApplyAutoFilter(True, Options);
    Book.SaveAs('orders-applied.xlsx');
  finally
    Book.Free;
  end;
end;

Инициализируйте запись через XLSDefaultAutoFilterExecutionOptions каждый раз. Функция обнуляет запись и затем выставляет каждый лимит, а контекст исполнения отвергает запись, у которой не все лимиты положительны, исключением EXLSXAutoFilterLimit. Запись, объявленная на стеке и заполненная по полям, обычно несёт мусор или нули в забытых полях. Безаргументные перегрузки (ApplyAutoFilter(True), PreviewAutoFilterRows(Callback), AutoFilterRowVisible(Row)) используют те же значения по умолчанию, с текущей датой как опорной

Флаг AResetVisible решает судьбу строк, прошедших фильтр, но уже скрытых ранее. True делает каждую подходящую строку видимой — то, что делает Excel при повторном применении. False сохраняет прежнее ручное скрытие на подходящих строках и лишь добавляет скрытие для непрошедших. Строки вне диапазона AutoFilter в любом случае сохраняют своё состояние. Если книга изменилась между подготовкой и публикацией, ApplyAutoFilter поднимает исключение, а не публикует решения, вычисленные по протухшим данным

Почему списки значений игнорируют звёздочки и знаки вопроса?

Записи списка значений в элементе filters — литеральные показываемые токены, поэтому HotXLS сравнивает * и ? как обычные символы. Отмеченная запись A* в дропдауне означает, что ячейка показывает ровно A*, а не «начинается с A». Подстановочные знаки принадлежат customFilters, где «начинается с» и «содержит» — настоящие шаблоны, и там действуют отдельные правила, разобранные в статье о том, как HotXLS сопоставляет подстановочные критерии Excel и тильда-экранирование. Перепутать эти две семантики — самый быстрый способ отфильтровать колонку артикулов в бессмыслицу

Сравнение идёт против показываемого значения, и тут встаёт вопрос — какого показывания. HotXLS форматирует ячейку её базовым числовым форматом, включая сохранённые наложенные стили, и нарочно игнорирует числовой формат, накладываемый условным форматированием. Нативные контролы Excel в фикстуре HotXLS показывают, почему токен важен: числовое 1 и текст "1" оба совпадают с выбором 1, числовая 1 в формате 0.0 вместо этого совпадает с 1.0 вместе с текстом "1.0", а текст "01" отличим от обоих. Похожещий на дату текст никогда не парсится в дату, поэтому текстовая ячейка с "2026-10-19" не попадёт в группу дат

Как решают Top 10, среднее и динамические фильтры дат?

HotXLS вычисляет ранжирующие и средние пороги один раз по всей исходной колонке, а не по строкам, оставшимся видимыми от другого фильтра. В популяцию входят только конечные числа, то есть похожий на число текст вроде "100" исключается, и популяция собирается до всякого решения по строкам. В нативной контрольной книге фильтр «выше среднего» держит среднее 27 по всей колонке, даже после того, как вторая колонка скрыла значение 50; он не переключается на среднее 21,25 по оставшимся строкам

Ничьи включаются на пороге, как в Excel. Фильтр Top 3 по значениям с двумя записями, равными 20, выбирает четыре строки. Процентный режим берёт усечённую долю числовой популяции с нижним пределом в один элемент, поэтому Top 50 процентов от пяти значений выбирает два значения с порогом 30. Bottom-варианты зеркалят сравнение. Эти числа взяты из собственных наборов строк Excel, записанных в тестовых фикстурах HotXLS, а не из чтения спецификации, которая о большинстве из них молчит

Динамическим датам нужна фиксированная опорная дата

TXLSXAutoFilterExecutionOptions.ReferenceDate замораживает смысл «сегодня» на всю операцию. Относительные фильтры вроде этой недели или прошлого квартала, и правила дат условного форматирования, кормящие цветовые фильтры, вычисляются против той одной даты, поэтому предпросмотр в 23:59:59 и применение секундой позже согласны. Нулевое значение выбирает текущую дату один раз на старте операции; серийные значения уважают систему дат книги 1900 или 1904, включая фиктивный серийный номер 60 Excel в системе 1900 для фильтров M2 и Q1

Окна — полузакрытые календарные интервалы: эта неделя идёт с воскресенья до следующего воскресенья, этот квартал — с первого дня квартала до первого дня следующего, а с начала года — с 1 января до конца опорного дня. Задавайте ReferenceDate явно во всём, что гоняется пакетом или в тесте. Ночной джоб, пересекающий полночь, или юнит-тест, написанный в октябре и обязанный проходить в январе, иначе выдадут наборы строк, расходящиеся без видимой причины

Формульные ячейки фильтруются по их кешированным значениям. Движок фильтра не пересчитывает книгу молча, поэтому файл, сохранённый писателем, оставшимся файл без кешированных результатов, нуждается в явном проходе вычисления, прежде чем фильтр увидит осмысленные числа или даты

Какие цвета видит цветовой фильтр?

Цветовой фильтр HotXLS видит эффективный цвет из прямого форматирования ячейки плюс любое совпадающее наложение условного форматирования, и ничего, что приходит только из стиля таблицы. Excel ведёт себя так же: полосатая строка, покрашенная в светло-голубой встроенным стилем таблицы, для дропдауна фильтра не «светло-голубая». В нативных контролах ячейка, покрашенная в красный условным форматированием, выбирается красным фильтром заливки, а ячейка, покрашенная в красный напрямую, но перекрытая активным жёлтым правилом, исключается

Три уточнения пришли в последующих релизах. Версия 2.384.138 трактует линейные и path-градиентные заливки как отличные и от сплошных цветов, и от отсутствия заливки, поэтому градиент никогда не совпадает с критерием сплошного цвета, даже если один из его стопов несёт тот самый цвет. Версия 2.384.140 добавила фильтрацию по цвету шрифта для rich text, где ячейка совпадает, если любой непустой прогон несёт запрошенный RGB, automatic или унаследованный цвет. Версия 2.384.150 докончила условные rich-text решения, и они оказались странными

Нативный Excel может переиспользовать цветовое решение между rich-text ячейкой и обычной ячейкой с тем же текстом, тем же базовым стилем и тем же путём совпавшего условного правила, поэтому видимый набор строк может зависеть от порядка этих ячеек. HotXLS воспроизводит это, готовя весь диапазон данных в порядке возрастания строк, даже когда вы спрашиваете AutoFilterRowVisible об одной строке. Инспекция строки 40 потому стоит прохода по диапазону, но инспекция строки, предпросмотр и применение всегда отвечают на одну и ту же строку одинаково

У каждого ресурса есть лимит, и что ловить

HotXLS ограничивает каждый вид работы в операции AutoFilter собственным полем в TXLSXAutoFilterExecutionOptions, потому что отфильтрованная книга из формы загрузки — недоверенный вход. Значения по умолчанию: 1048576 для MaxXmlCharacters, 16384 для MaxXmlNodes, 4096 для MaxFilterItems, 4000000 для MaxExaminedCells, по 1000000 для MaxEffectiveFormatCells и MaxPopulationValues, 67108864 байт для MaxContextBytes и 16000000 для MaxConditionalSteps. MaxContextBytes меряет удерживаемое логическое состояние и зарезервированные временные конверты, а не память процесса

type
  TFilterJob = class
  public
    Deadline: TDateTime;
    VisibleRows: TList<Integer>;
    procedure HandleRow(Sender: TObject; SheetIndex, Row: Integer;
      Visible: Boolean; var Cancel: Boolean);
    procedure HandleWork(Sender: TObject; ACompleted: Int64;
      var ACancel: Boolean);
  end;

procedure TFilterJob.HandleRow(Sender: TObject; SheetIndex, Row: Integer;
  Visible: Boolean; var Cancel: Boolean);
begin
  // Вызывается для каждой строки данных; Visible — решение фильтра
  if Visible then
    VisibleRows.Add(Row);
end;

procedure TFilterJob.HandleWork(Sender: TObject; ACompleted: Int64;
  var ACancel: Boolean);
begin
  // ACompleted считает осмотренные ячейки и может стоять на месте во время форматирования
  ACancel := Now > Deadline;
end;

Строчный колбэк PreviewAutoFilterRows получает каждую строку данных с её решением, а не только подходящие, и функция по-прежнему возвращает счёт подходящих. Установка его параметра Cancel останавливает обход рано, без исключения. OnWork — крючок отмены: установка ACancel в True поднимает EXLSXAutoFilterCancelled. Пока операция готовит решения, она держит read-lease на книге, поэтому ни один колбэк не вправе ту книгу менять

Options := XLSDefaultAutoFilterExecutionOptions;
Options.ReferenceDate := Date;
Options.MaxExaminedCells := 500000;   // более тесный потолок для загруженных файлов
Options.OnWork := Job.HandleWork;
try
  Sheet.PreviewAutoFilterRows(Job.HandleRow, Options);
  Sheet.ApplyAutoFilter(True, Options);
except
  on E: EXLSXAutoFilterUnsupported do
    Log('Filter needs Excel: ' + E.Message);
  on E: EXLSXAutoFilterLimit do
    Log('Filter exceeded a budget: ' + E.Message);
  on E: EXLSXAutoFilterCancelled do
    Log('Filter cancelled at deadline');
  on E: EXLSXAutoFilterExecution do
    Log('Filter state invalid: ' + E.Message);
end;

Все три специфических исключения наследуются от EXLSXAutoFilterExecution, которое само покрывает невалидное состояние вроде опорной даты вне поддерживаемого календаря или книги, изменившейся до публикации, так что держите базовый класс последним. Во всех случаях видимость строк листа не меняется, что делает «залогировать, пропустить, пометить файл на ручную проверку» безопасной политикой по умолчанию

Где проходят границы

Покрытие широкое, но пара краёв стоит знания прежде, чем вы пообещаете клиенту паритет:

• Динамические фильтры стандартного отклонения отвергаются, пока не существует проверенной политики, хотя перечисление для них значения имеет
• Недельные окна начинаются с воскресенья; книги, полагающиеся на другой старт недели, не моделируются
• Результаты формул берутся только из сохранённого кеша, без пересчёта внутри вызова фильтра
• Цвета табличных стилей никогда не совпадают с цветовым фильтром — по дизайну и в согласии с Excel
• Неизвестная структура rich text или неизвестные цветовые метаданные отказывают явно, тогда как известные нецветовые свойства вроде семейства шрифта цветовое совпадение не блокируют
• XLS-фасад и его хранилище BIFF8 — отдельный путь, разобранный в статье о том, как HotXLS пишет критерии DOPER AutoFilter в BIFF8

Если вы ещё решаете, как соотносятся определения AutoFilter, проверка данных и таблицы в книге, которую генерируете сами, начните со статьи проверка данных, AutoFilter и таблицы HotXLS в Delphi — она покрывает SetAutoFilter, идентификаторы колонок и когда фильтр реально вступает в силу

FAQ

Меняет ли PreviewAutoFilterRows скрытость строк?

Нет. PreviewAutoFilterRows лишь вычисляет сохранённые критерии и сообщает решения через возвращаемое значение и опциональный колбэк. Только ApplyAutoFilter публикует новый набор скрытых строк, и делает это после подготовки всех решений, поэтому сбой оставляет исходную видимость нетронутой

Почему относительный фильтр дат даёт разные строки в разные дни?

Фильтры вроде сегодня, этой недели или прошлого месяца определены относительно текущей даты — ровно как в Excel. Задайте ReferenceDate в TXLSXAutoFilterExecutionOptions фиксированной датой, когда нужны повторяемые результаты; нулевое значение означает дату старта операции

Может ли HotXLS фильтровать по цветам из табличного стиля?

Нет, как и Excel. Цветовые фильтры HotXLS совпадают только с прямым форматированием ячеек и поддерживаемыми наложениями условного форматирования. Цвета, нанесённые исключительно встроенным или пользовательским табличным стилем, не участвуют, поэтому результат цветового фильтра согласуется с тем, что выбрал бы дропдаун Excel

Продвинутое исполнение AutoFilter поставляется с XLSX API листов HotXLS, Delphi spreadsheet component для чтения и записи файлов Excel без Excel, начиная с v2.384.136, с обработкой градиентных, rich-text и условных rich-text цветов, добавленной вплоть до v2.384.150