Якщо SUBTOTAL(109, ...) та SUBTOTAL(9, ...) повертають те саме число на книзі, що містить приховані рядки, одне з двох неправильне. HotXLS, нативний компонент електронних таблиць Excel для Delphi та C++Builder, поводився саме так до версії 2.197.0, бо його обчислювальний рушій не мав способу запитати аркуш, чи прихований даний рядок
Симптом рідко приходить як звіт про баг щодо кодів формул. Він приходить як розбіжність: пакетне завдання на сервері обчислює підсумок, користувач відкриває той самий файл в Excel з застосованим фільтром, і два числа відрізняються на те, чим у сумі виявилися відфільтровані рядки. Ніхто не підозрює функцію агрегації, бо рядок формули в комірці ідентичний в обох місцях. Різниця повністю в тому, що обчислювачу дозволялося бачити
Чому SUBTOTAL 109 включає приховані рядки?
Бо в більшості дизайнів рушія шар, що обчислює формулу, ніколи не дізнається про видимість рядка. HotXLS був хрестоматійним випадком: обчислювальний рушій у lxCalc.pas діставався до значень комірок через єдиний callback TXLSGetValue, що відповідає значенням для трійки (аркуш, рядок, стовпець) і нічим більше. Видимість — атрибут представлення, що зберігається в записі рядка, і жодна частина цього запису не подорожувала вниз по ланцюжку викликів. Тож рушій мав один шлях агрегації, і обидві половини таблиці номерів функцій 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-посилання розв'язалося у зовнішню книгу. Версія 2.197.0 додала друге. Рушій отримав тип callback, TXLSIsRowHidden, оголошений як функція від (SheetIndex, row), що повертає Boolean, збережена в FIsRowHidden, плюс перехідний прапорець FIgnoreHiddenRows. Прапорець озброюється на вході CalcSubtotalFunc, коли код функції потрапляє в діапазон від 101 до 111, і на вході CalcAggregateFunc для кодів опцій AGGREGATE, що обирають виключення прихованих рядків. Кожен цикл обходу комірок тоді перевіряє його і пропускає один рядок, коли він встановлений, додаючи по одному рядку до кожного
// The shape repeated in all six cell-walk loops
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
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Дві деталі в коді озброєння несуть коректність усієї схеми. Прапорець зберігається й відновлюється, а не просто встановлюється й скидається, бо аргумент SUBTOTAL може містити вираз, що запускає власне обчислення, поки зовнішня агрегація ще на стеку, і ця вкладена робота не повинна успадковувати чи руйнувати зовнішній затвор. А відновлення живе в блоці finally, бо CalcSubtotalFunc має кілька ранніх виходів для кодів помилок; прапорець, що лишився озброєним після повернення з помилкою, тихо зіпсував би наступну непов'язану формулу в порядку перерахунку
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Перевірка Assigned — те, що тримає зміну сумісною. HotXLS розширив конструктор калькулятора третім параметром за замовчуванням nil, тож будь-який код, що будує TXLSCalculator старим викликом з двома аргументами, все ще компілюється й все ще отримує застарілу поведінку включення прихованого
Звідки насправді береться біт прихованого рядка?
З аркуша, через два різних джерела, бо HotXLS несе два рушії книги. Застарілий бік BIFF відповідає з TXLSRowInfoList.GetHidden, досяжного через TXLSWorkbook.GetRowHidden. Бік OOXML відповідає з TXLSXWorksheet.GetRowHidden, досяжного через TXLSXWorkbook.GetCalcRowHidden. Обидва підключені до калькулятора в момент конструювання, поряд з callback значення комірки, який вони віддзеркалюють. Конвенції рядків — те місце, де такий міст зазвичай ламається, тож варто заявити їх явно. Калькулятор передає callback рядок з нумерацією від 0, що збігається з координатами, які вже використовує TXLSGetValue. Аркуш XLSX індексує свою мапу прихованих рядків за номером рядка з нумерацією від 1, точно як Excel нумерує рядки, що також те, що виставляє публічна властивість RowHidden[ARow]. Тож міст XLSX додає одиницю перед пошуком, а міст BIFF — ні, бо TXLSRowInfoList уже з нумерацією від 0. Обидва мости трактують індекс аркуша чи рядок поза валідним діапазоном як видимий, тож запит за межами діапазону деградує до старої відповіді включення прихованого замість втрати даних
Що змінюється для відфільтрованих книг
Це випадок, що породжує звернення в підтримку. Застосування AutoFilter в HotXLS через ApplyAutoFilter обчислює критерії стовпця і приховує кожен рядок даних, що не відповідає, що рівно те, що робить Excel, коли користувач клацає випадний список фільтра. До v2.197.0 ці приховані рядки були невидимі для користувача й повністю видимі для обчислювального рушія, тож серверний 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; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
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, пов'язана з низькими кодами опцій, не реалізована в HotXLS. Виявлення вкладеного SUBTOTAL всередині діапазону з посиланням вимагає позначення стану рекурсії обчислювача, тож внутрішня агрегація може оголосити себе зовнішній, а це більша зміна, ніж затвор прихованих рядків. На практиці вплив невеликий, бо реальні книги майже завжди розміщують формули SUBTOTAL поза діапазонами, які агрегують інші формули SUBTOTAL. Якщо твій генератор справді будує перекривні діапазони агрегації, не покладайся на низькі коди опцій для їхньої дедуплікації
Захист арності, що постачався поруч
Версія 2.197.0 також закрила прогалину валідації в тому самому диспетчері, і причина дизайну та сама, що мотивувала тимчасове поле: помісти перевірку там, де її можна написати один раз. Приблизно 280 тіл вбудованих функцій кожне перевіряло власну кількість аргументів проти Item.ChildCount, що не лишало жодної узгодженої межі для випадку забагатьох аргументів. Виклик на кшталт =SIN(1,2) досягав тіла функції, що оглядало свій перший аргумент, ігнорувало надлишок і повертало правдоподібне число там, де Excel повертає #VALUE!. HotXLS уже зберігав задекларовану арність кожної вбудованої функції в своєму реєстрі функцій, виставлену як THashFunc.ArgsCnt з -1, що позначає варіативну функцію, таку як SUM, IF чи CONCAT. Версія 2.197.0 передала це через нову властивість TXLSFormula.FuncArgsCntByPtg і додала один затвор на початку GetValueItemFunc, головного диспетчера
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Захист відхиляє забагато аргументів і навмисно нічого не каже про замало. Пропуск кінцевого опційного аргументу легальний в Excel для VLOOKUP, SUBSTITUTE та довгого списку інших, тож симетрична перевірка зламала б коректні формули, щоб зловити некоректні. Невідомі ідентифікатори звітуються як варіативні й повністю пропускають затвор, що і тримає визначені користувачем функції поза його дією; якщо ти реєструєш власні функції, поведінка, описана в посібнику з обчислювального рушія та власних функцій, не зачеплена. Централізація випадку "замало" — окрема робота, бо кожне з цих 280 тіл має власну семантику кодів помилок, і їх треба переглянути по одному, а не припускати
Обчислювальний рушій, описаний тут, обидва фасади книги та API AutoFilter і видимості рядків, що його живлять, є частиною компонента електронних таблиць HotXLS для Delphi, що постачається з повним джерельним кодом для Delphi та C++Builder і не вимагає встановлення Excel на машині, яка його запускає