Ако SUBTOTAL(109, ...) и SUBTOTAL(9, ...) връщат едно и също число за workbook, съдържащ скрити редове, едното от двете е грешно. HotXLS, нативният Excel spreadsheet компонент за Delphi и C++Builder, се държеше точно така до версия 2.197.0, защото неговият calculation engine нямаше начин да попита worksheet дали даден ред е скрит
Симптомът рядко пристига като bug report за формулни кодове. Пристига като несъответствие: batch задача на сървъра изчислява общо, потребител отваря същия файл в Excel с приложен филтър, и двете числа се различават с колкото са сумирали филтрираните редове. Никой не подозира функцията за агрегация, защото формулният низ в клетката е идентичен и на двете места. Разликата е изцяло в това, което evaluator-ът е бил допуснат да види
Защо SUBTOTAL 109 включва скрити редове?
Защото в повечето дизайни на engine слоят, изчисляващ формула, никога не научава за видимостта на реда. HotXLS беше учебникарски случай: calculation engine-ът в lxCalc.pas достигаше стойностите на клетките чрез единствен callback TXLSGetValue, отговарящ със стойност за тройка (лист, ред, колона) и нищо друго. Видимостта е атрибут на представяне, съхраняван в записа на реда, и никоя част от този запис не пътуваше надолу по веригата извиквания. Engine-ът затова имаше един път за агрегация, и двете половини на таблицата с функционални номера на 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, прави умишлено изявление за скрити данни, а engine, свиващ разликата, тихо го отменя
Към какво мапват номерата на функциите вътре в engine-а
HotXLS разрешава първия аргумент на SUBTOTAL в CalcSubtotalFunc, който нормализира кодове от 101 до 111 надолу върху същите вътрешни идентификатори на функции като кодове от 1 до 11, а после диспечира по самата агрегация. Повечето от семейството минава през инкременталния акумулатор ExcelSum, този, обработващ SUM, COUNT, COUNTA, MIN, MAX и AVERAGE. Пет от тях не могат: STDEV, VAR, STDEVP, VARP и PRODUCT се нуждаят от closed-form преминаване над данните, така че CalcSubtotalFunc насочва вътрешни кодове 12, 46, 193, 194 и 183 към отделен reducer, SubtotalReduceVariance. Това разделение е първото нещо, което си струва да се картографира, преди да докосвате каквото и да е, защото два независими пътя на агрегация означават два независими цикъла за обхождане на клетки, а поправка, приложена само към единия, произвежда най-лошия възможен резултат: SUBTOTAL(109, ...) спазва филтъра, докато SUBTOTAL(107, ...) върху същия диапазон не го спазва. Броенето на циклите в HotXLS откри шест от тях, щом AGGREGATE беше включен, разпръснати между оценка на диапазон, обикновено събиране на диапазон и три отделни reducer-а
Защо scratch поле вместо шест нови сигнатури?
Защото прекарването на нов параметър през шест функции за обхождане на клетки, плюс всичко, което ги извиква, е широка промяна в горещ път на кода заради едно булево. HotXLS вече имаше прецедент за алтернативата: преходно поле на калкулатора, в същия дух като scratch полето, което GetRangeInfo използва, за да запише кога 3D референция се е разрешила във външен workbook. Версия 2.197.0 добави второ такова. Engine-ът получи тип callback, TXLSIsRowHidden, деклариран като функция от (SheetIndex, row), връщаща Boolean, съхранена в FIsRowHidden, плюс преходен флаг FIgnoreHiddenRows. Флагът се въоръжава при входа на CalcSubtotalFunc, когато кодът на функцията попада между 101 и 111, и при входа на CalcAggregateFunc за option кодовете на 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 със старото извикване с два аргумента, все още се компилира и все още получава legacy поведението за включване на скрити редове. Нищо в съществуващото API не промени формата си
Откъде всъщност идва битът за скрит ред?
От worksheet, чрез два различни източника, защото HotXLS носи два workbook engine-а. Legacy BIFF страната отговаря от TXLSRowInfoList.GetHidden, достигнат чрез TXLSWorkbook.GetRowHidden. OOXML страната отговаря от TXLSXWorksheet.GetRowHidden, достигнат чрез TXLSXWorkbook.GetCalcRowHidden. И двата са окабелени в калкулатора при конструкция, заедно с callback-а за стойност на клетка, който отразяват. Конвенциите за редове са мястото, където подобен мост обичайно тръгва накриво, така че си струва да се посочат изрично. Калкулаторът подава на callback-а 0-базиран ред, съответстващ на координатите, които TXLSGetValue вече използва. XLSX worksheet ключува своята карта за скрити редове по 1-базиран номер на ред, точно както Excel номерира редовете, което е и това, което публичното свойство RowHidden[ARow] излага. XLSX мостът затова добавя едно преди справката, а BIFF мостът не го прави, защото TXLSRowInfoList вече е 0-базиран. И двата моста третират индекс на лист или ред извън валидния диапазон като видим, така че заявка извън граница деградира до старото поведение за включване на скрити, вместо да изпуска данни
Какво се променя за филтрирани workbook-ове
Това е случаят, генериращ support тикети. Прилагането на AutoFilter в HotXLS чрез ApplyAutoFilter оценява критериите на колоната и скрива всеки ред с данни, който не съвпада, което е точно това, което Excel прави, когато потребител кликне падащото меню на филтър. Преди v2.197.0 тези скрити редове бяха невидими за потребителя и напълно видими за calculation engine-а, така че server-side 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. Едно следствие заслужава бележка във всяка документация, доставяна с генерираните от вас workbook-ове: общо, изчислено с код 109, е зависимо от изгледа число, така че получател, изчистил филтъра, го променя. Когато отчет трябва да заяви фиксирана цифра, независимо какво читателят прави с изгледа, код 9 е правилният избор и винаги е бил. Филтри, валидация и таблици са разгледани заедно в статията за валидация на данни, AutoFilter и таблици. Понеже скриването на редове не докосва никаква формула, то също не замърсява графа на зависимости само по себе си, което си струва да се знае, ако разчитате на инкрементално преизчисление над мръсния подграф, за да поддържате отзивчиви големи workbook-ове
Кодове за опции на AGGREGATE и един лимит, който все още е отворен
AGGREGATE е SUBTOTAL с втори аргумент за политика, а HotXLS го обработва в CalcAggregateFunc. Аргументът за опция кодира независими превключватели: дали вложени извиквания на SUBTOTAL и AGGREGATE вътре в диапазона се пропускат, дали стойности на скрити редове се пропускат, и дали стойности за грешка се потискат вместо разпространени. HotXLS въоръжава споделената порта за скрити редове за option кодове 2, 3, 6 и 7, и потиска стойности за грешка за option кодове от 4 до 7. Аргументът за номер на функция после избира агрегацията точно както SUBTOTAL, включително насочването на variance, standard deviation и product през собствените им reducer-и. Един документиран пропуск остава, и по-добре да е посочен тук, отколкото открит в production: семантиката за игнориране на вложен SUBTOTAL, свързана с ниските option кодове, не е имплементирана в HotXLS. Откриването на вложен SUBTOTAL вътре в реферирания диапазон изисква маркиране на състоянието на рекурсия на evaluator-а, така че вътрешна агрегация да може да се обяви на външната, което е по-голяма промяна от портата за скрити редове. На практика изложението е малко, защото истинските workbook-ове почти винаги поставят SUBTOTAL формули извън диапазоните, които други SUBTOTAL формули агрегират. Ако вашият генератор действително изгражда припокриващи се диапазони за агрегация, не разчитайте на ниските option кодове да ги дедуплицират
Проверката за arity, доставена заедно с това
Версия 2.197.0 затвори и пропуск във валидацията в същия dispatcher, а причината за дизайна е същата, мотивирала scratch полето: поставете проверката там, където може да бъде написана веднъж. Приблизително 280 вградени функционални тела всяко проверяваше собствения си брой аргументи спрямо Item.ChildCount, което не оставяше последователна граница за случая на прекалено много аргументи. Извикване като =SIN(1,2) достигаше функционално тяло, което разглеждаше първия си аргумент, игнорираше излишъка, и връщаше правдоподобно число там, където Excel връща #VALUE!. HotXLS вече съхраняваше декларираната arity на всяка вградена функция в своя регистър от функции, изложена като THashFunc.ArgsCnt, с -1, маркиращо вариадична функция като SUM, IF, или CONCAT. Версия 2.197.0 препрати това през ново свойство TXLSFormula.FuncArgsCntByPtg и добави една порта в началото на GetValueItemFunc, главния dispatcher
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, и дълъг списък от други, така че симетрична проверка би счупила правилни формули, за да хване грешни. Непознати идентификатори се отчитат като вариадични и прескачат портата изцяло, което е това, което държи потребителски дефинирани функции извън пътя ѝ; ако регистрирате собствени функции, поведението, описано в ръководството за calculation engine и потребителски функции, е незасегнато. Централизирането на случая с прекалено малко е отделна задача, защото всяко от тези 280 тела има собствена семантика за кодове за грешка, и трябва да бъдат прегледани едно по едно, а не приети наготово
Calculation engine-ът, описан тук, двете workbook фасади, и API-тата за AutoFilter и видимост на редове, захранващи го, са част от HotXLS Delphi spreadsheet компонент, който се доставя с пълен изходен код за Delphi и C++Builder и не изисква инсталация на Excel на машината, която го изпълнява