SUBTOTAL и AGGREGATE скрытых строк в Delphi с HotXLS

Если SUBTOTAL(109, ...) и SUBTOTAL(9, ...) возвращают одно и то же число на книге, содержащей скрытые строки, одно из двух неверно. Это одна из самых лёгких ловушек при постройке вычислительного движка электронных таблиц, и HotXLS, нативный компонент работы с электронными таблицами Excel для Delphi и C++Builder, обходит её, позволяя своему вычислительному движку спрашивать у листа, скрыта ли данная строка

Симптом редко приходит как вопрос о кодах формул. Он приходит как расхождение: батч на сервере считает итог, пользователь открывает тот же файл в Excel с применённым фильтром, и два числа различаются ровно на сумму строк, отфильтрованных прочь. Никто не подозревает функцию агрегации, потому что строка формулы в ячейке идентична в обоих местах. Разница целиком в том, что вычислителю было позволено видеть

Почему SUBTOTAL 109 включает скрытые строки?

Потому что в большинстве архитектур движка слой, вычисляющий формулу, никогда не узнаёт о видимости строки. Очевидная конструкция добирается до значений ячеек через единственный value-колбэк, вроде TXLSGetValue в lxCalc.pas, отвечающего значением для тройки (лист, строка, столбец) и ничем больше. Видимость — атрибут представления, хранящийся в записи строки, и если вниз по цепочке вызова не едет ничего ещё, движок получает один путь агрегации, в который разрешаются обе половины таблицы номеров функций SUBTOTAL. Это не ошибка класса «погрешность округления»: политика скрытых строк — вся причина существования второй половины таблицы. ECMA-376 Part 1, опубликованная как ISO/IEC 29500-1, определяет SUBTOTAL в определениях функций формул (§18.17.7) с первым аргументом, выбирающим и внутреннюю агрегацию, и политику скрытых строк. Коды с 1 по 11 отображаются на AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR и VARP, включая значения на вручную скрытых строках. Коды с 101 по 111 выбирают те же одиннадцать агрегаций и исключают их. Пользователь, набравший 109 вместо 9, делает намеренное заявление о скрытых данных, а движок, схлопывающий это различие, молча его отменяет

На что отображаются номера функций внутри движка

HotXLS разрешает первый аргумент SUBTOTAL в CalcSubtotalFunc, который нормализует коды со 101 по 111 к тем же внутренним идентификаторам функций, что и коды с 1 по 11, а затем диспетчеризует по самой агрегации. Большая часть семейства проходит через инкрементальный аккумулятор ExcelSum — тот, что обрабатывает SUM, COUNT, COUNTA, MIN, MAX и AVERAGE. Пять не могут: STDEV, VAR, STDEVP, VARP и PRODUCT нуждаются в проходе с замкнутой формой по данным, так что CalcSubtotalFunc маршрутизирует внутренние коды 12, 46, 193, 194 и 183 в отдельный редуктор, SubtotalReduceVariance. Это разделение — первое, что стоит нанести на карту, прежде чем что-либо трогать, потому что два независимых пути агрегации означают два независимых цикла обхода ячеек, а проверка скрытых строк, добавленная лишь к одному из них, даёт худший из возможных исходов: SUBTOTAL(109, ...) учитывает фильтр, пока SUBTOTAL(107, ...) на том же диапазоне — нет. У HotXLS их шесть, если считать AGGREGATE, распределённых по вычислению диапазона, простому сбору диапазона и трём отдельным редукторам

Почему скретч-поле вместо шести новых сигнатур?

Потому что протягивание нового параметра через шесть функций обхода ячеек, плюс всё, что их вызывает, — это широкое изменение горячего пути кода ради одного булева значения. У HotXLS есть прецедент альтернативы: временное поле на калькуляторе, в том же духе, что и скретч-поле, которым GetRangeInfo записывает, когда 3D-ссылка разрешилась во внешнюю книгу. Флаг скрытых строк — второй такой: тип колбэка TXLSIsRowHidden, объявленный как функция от (SheetIndex, row), возвращающая Boolean, хранящийся в FIsRowHidden, плюс временный флаг FIgnoreHiddenRows. Флаг взводится на входе в CalcSubtotalFunc, когда код функции попадает в 101–111, и на входе в CalcAggregateFunc для кодов опций AGGREGATE, выбирающих исключение скрытых строк. Каждый цикл обхода ячеек затем проверяет его и пропускает строку, когда он установлен, ценой одной строки кода каждый

// Форма, повторяющаяся во всех шести циклах обхода ячеек
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... сложить Cells[rr, cc] в аккумулятор ...
    end;
  end;

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

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // агрегировать по Item.Child[2] .. Item.Child[ChildCount]
  // каждый путь Exit ниже покрыт блоком finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Проверка Assigned — то, что сохраняет совместимость API калькулятора. Конструктор TXLSCalculator принимает третий параметр, по умолчанию nil, так что код, строящий калькулятор двухаргументным вызовом, компилируется без изменений и получает поведение «включая скрытые»

Откуда на самом деле берётся бит скрытой строки?

С листа, через два разных источника, потому что HotXLS несёт два движка книг. Легаси-сторона BIFF отвечает из TXLSRowInfoList.GetHidden, до которой добираешься через TXLSWorkbook.GetRowHidden. Сторона OOXML отвечает из TXLSXWorksheet.GetRowHidden, до которой добираешься через TXLSXWorkbook.GetCalcRowHidden. Оба подключены в калькулятор при конструировании, рядом с колбэком значений ячеек, зеркалом которого они служат. Соглашения о строках — то место, где такие мосты обычно ломаются, поэтому их стоит проговорить явно. Калькулятор вручает колбэку строку с отсчётом от нуля, в тех же координатах, которыми уже пользуется TXLSGetValue. Лист XLSX ключует свою карту скрытых строк по номеру строки с отсчётом от единицы, ровно как нумерует строки Excel — это же показывает и публичное свойство RowHidden[ARow]. Мост XLSX потому прибавляет единицу перед поиском, а мост BIFF нет, потому что TXLSRowInfoList уже с нулевой базой. Оба моста трактуют индекс листа или строку вне валидного диапазона как видимые, так что запрос вне границ деградирует в ответ «включая скрытые», а не роняет данные

Что меняется для отфильтрованных книг

Это случай, где политика скрытых строк важнее всего. Применение AutoFilter в HotXLS через ApplyAutoFilter вычисляет критерии столбца и скрывает каждую строку данных, не соответствующую им, — в точности то, что делает Excel, когда пользователь щёлкает выпадающий список фильтра. Эти строки невидимы пользователю, и поскольку вычислительный движок сверяется с тем же состоянием строк, серверный SUBTOTAL(109, ...) сообщает отфильтрованный итог, а не неотфильтрованный

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // скрывает не совпадающие строки

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // Значение ячейки сходится с тем, что Excel показывает для того же
    // фильтра, а VisibleRows сообщает, сколько строк в него попало

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Ручное скрытие работает так же, поскольку RowHidden[ARow] := True — то же состояние, которое пишет фильтр. Эта эквивалентность в Excel намеренна, и в HotXLS она тоже держится. Одно следствие заслуживает заметки в любой документации, что едет с вашими сгенерированными книгами: итог, посчитанный с кодом 109, — число, зависящее от представления, поэтому получатель, снимающий фильтр, его меняет. Когда отчёт обязан показывать фиксированную цифру независимо от того, что читатель делает с представлением, код 9 — правильный выбор, и всегда был. Фильтры, валидация и таблицы разобраны вместе в статье о валидации данных, AutoFilter и таблицах. Поскольку скрытие строк не трогает ни одну формулу, оно само по себе не грязнит граф зависимостей, — стоит знать, если вы полагаетесь на инкрементальный пересчёт по грязному подграфу, чтобы держать большие книги отзывчивыми

Коды опций AGGREGATE

AGGREGATE — это SUBTOTAL со вторым аргументом политики, и HotXLS обрабатывает его в CalcAggregateFunc. Аргумент опций кодирует независимые переключатели: пропускать ли вложенные вызовы SUBTOTAL и AGGREGATE внутри диапазона, пропускать ли значения на скрытых строках и давить ли значения ошибок вместо передачи. HotXLS взводит общий флаг скрытых строк для кодов опций 2, 3, 6 и 7 и давит значения ошибок для кодов опций с 4 по 7. Аргумент номера функции затем выбирает агрегацию ровно как SUBTOTAL, включая маршрутизацию дисперсии, стандартного отклонения и произведения через их собственные редукторы. Реальные книги почти всегда кладут формулы SUBTOTAL вне диапазонов, которые агрегируют другие SUBTOTAL; если ваш генератор строит перекрывающиеся диапазоны агрегации, держите внутренние итоги вне внешнего диапазона, а не полагайтесь на дедупликацию кодами опций

Проверка арности в том же диспетчере

Тот же диспетчер несёт проверку числа аргументов, и проектная причина — та же, что за скретч-полем: поставить проверку там, где её можно написать один раз. С примерно 280 встроенными телами функций оставлять каждому самому сверять свою арность с Item.ChildCount — значит не иметь единообразной границы для случая «слишком много аргументов», а ловушка легко пропускается: вызов вроде =SIN(1,2) доходит до тела функции, которое осматривает первый аргумент, игнорирует излишек и возвращает правдоподобное число там, где Excel возвращает #VALUE!. HotXLS хранит заявленную арность каждого встроенного в реестре функций, выставленную как THashFunc.ArgsCnt, где -1 помечает вариадическую функцию вроде SUM, IF или CONCAT, прокидывает её через свойство TXLSFormula.FuncArgsCntByPtg и проверяет одними воротами на входе в GetValueItemFunc, главном диспетчере

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] — узел функции
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) даёт #VALUE!
    Exit;
  end;
end;

Затвор отклоняет слишком много аргументов и намеренно молчит о слишком малых. Пропуск хвостового опционального аргумента легален в Excel для VLOOKUP, SUBSTITUTE и длинного списка других, поэтому симметричная проверка ломала бы корректные формулы, чтобы поймать некорректные. Неизвестные идентификаторы отчитываются как вариадические и проходят мимо затвора целиком — именно это держит пользовательские функции вне его досягаемости; если вы регистрируете собственные функции, поведение, описанное в руководстве по формульному движку и пользовательским функциям, не затронуто. Случай «слишком мало» остаётся за каждым телом функции, потому что у каждого свои семантики кодов ошибок

Описанный здесь вычислительный движок, оба фасада книги и API AutoFilter и видимости строк, его питающие, — часть Delphi-компонента электронных таблиц HotXLS, который поставляется с полным исходным кодом для Delphi и C++Builder и не требует установки Excel на машине, где он выполняется