Pokud SUBTOTAL(109, ...) a SUBTOTAL(9, ...) vrátí na sešitu obsahujícím skryté řádky stejné číslo, jedna z těchto dvou hodnot je špatně. HotXLS, nativní komponenta pro Excel sešity pro Delphi a C++Builder, se přesně takto chovala až do verze 2.197.0, protože se její výpočetní engine neměl jak zeptat listu, zda je daný řádek skrytý
Tento příznak zřídkakdy přijde jako hlášení chyby o kódech vzorců. Přichází jako nesoulad: dávková úloha na serveru spočítá součet, uživatel otevře stejný soubor v Excelu s aplikovaným filtrem, a obě čísla se liší o to, co náhodou dávaly do součtu odfiltrované řádky. Nikdo nepodezírá agregační funkci, protože textový řetězec vzorce v buňce je na obou místech identický. Rozdíl je celý v tom, co evaluátoru bylo dovoleno vidět
Proč SUBTOTAL 109 zahrnuje skryté řádky?
Protože ve většině návrhů enginů se vrstva, která vyhodnocuje vzorec, nikdy nedozví nic o viditelnosti řádku. HotXLS byl učebnicovým případem: výpočetní engine v lxCalc.pas dosahoval na hodnoty buněk přes jediný callback TXLSGetValue, který odpovídá hodnotou pro trojici (list, řádek, sloupec) a nic víc. Viditelnost je prezentační atribut uložený na záznamu řádku a žádná část tohoto záznamu necestovala po řetězu volání dolů. Engine tedy měl jedinou agregační cestu a obě poloviny tabulky čísel funkcí SUBTOTAL se na ni rozřešily. To není defekt třídy zaokrouhlovací chyby: je to celý důvod, proč druhá polovina tabulky vůbec existuje. ECMA-376 Part 1, publikovaná jako ISO/IEC 29500-1, definuje SUBTOTAL ve svých definicích funkcí vzorců (§18.17.7) s prvním argumentem, který volí jak vnitřní agregaci, tak politiku ohledně skrytých řádků. Kódy 1 až 11 mapují na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR a VARP a přitom zahrnují hodnoty na ručně skrytých řádcích. Kódy 101 až 111 volí stejných jedenáct agregací a tyto hodnoty vylučují. Uživatel, který napíše 109 místo 9, dělá záměrné prohlášení o skrytých datech, a engine, který toto rozlišení sloučí, toto prohlášení potichu přebije
Na co se čísla funkcí uvnitř enginu mapují
HotXLS rozřeší první argument SUBTOTAL v CalcSubtotalFunc, který normalizuje kódy 101 až 111 dolů na stejné vnitřní identifikátory funkcí jako kódy 1 až 11 a poté rozešle podle samotné agregace. Většina této rodiny protéká přírůstkovým akumulátorem ExcelSum, tím, který obsluhuje SUM, COUNT, COUNTA, MIN, MAX a AVERAGE. Pět z nich to nemůže: STDEV, VAR, STDEVP, VARP a PRODUCT potřebují uzavřený průchod přes data, takže CalcSubtotalFunc směruje vnitřní kódy 12, 46, 193, 194 a 183 do samostatného reduceru, SubtotalReduceVariance. Toto rozdělení je první věc, kterou stojí za to zmapovat, než se čehokoli dotknete, protože dvě nezávislé agregační cesty znamenají dva nezávislé cykly procházení buněk, a oprava aplikovaná jen na jednu z nich vyprodukuje nejhorší možný výsledek: SUBTOTAL(109, ...) respektuje filtr, zatímco SUBTOTAL(107, ...) nad stejným rozsahem ne. Spočítání cyklů v HotXLS odhalilo šest z nich, jakmile bylo zahrnuto AGGREGATE, rozprostřených napříč vyhodnocením rozsahu, obyčejným sběrem rozsahu a třemi samostatnými reducery
Proč pomocné (scratch) pole místo šesti nových signatur?
Protože protáhnout nový parametr přes šest funkcí procházejících buňky, plus vše, co je volá, je rozsáhlá změna na horké cestě kódu kvůli jedinému booleanu. HotXLS už měl pro tuto alternativu precedens: přechodné pole na kalkulátoru, v podobném duchu jako pomocné pole, které GetRangeInfo používá k zaznamenání, kdy se 3D reference rozřešila do externího sešitu. Verze 2.197.0 přidala druhé takové pole. Engine získal typ callbacku, TXLSIsRowHidden, deklarovaný jako funkce z (SheetIndex, row) vracející Boolean, uložený v FIsRowHidden, plus přechodný příznak FIgnoreHiddenRows. Příznak se aktivuje na vstupu do CalcSubtotalFunc, když kód funkce spadá do rozsahu 101 až 111, a na vstupu do CalcAggregateFunc pro ty kódy voleb AGGREGATE, které volí vyloučení skrytých řádků. Každý cyklus procházení buněk jej pak zkontroluje a přeskočí jeden řádek, když je nastavený, přičemž ke každému přidává jediný řádek kódu
// 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;Dva detaily v aktivačním kódu nesou správnost celého schématu. Příznak se ukládá a obnovuje, místo aby se prostě nastavil a smazal, protože argument SUBTOTAL může obsahovat výraz, který spouští vlastní vyhodnocení, zatímco vnější agregace je stále na zásobníku, a tato vnořená práce nesmí zdědit ani zničit vnější bránu. A obnovení žije v bloku finally, protože CalcSubtotalFunc má několik předčasných výstupů pro chybové kódy; příznak ponechaný aktivní po chybovém návratu by potichu poškodil další nesouvisející vzorec v pořadí přepočtu
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;Test Assigned je to, co udržuje změnu zpětně kompatibilní. HotXLS rozšířil konstruktor kalkulátoru o třetí parametr s výchozí hodnotou nil, takže jakýkoli kód, který sestavuje TXLSCalculator starým dvouargumentovým voláním, se stále zkompiluje a stále dostane starší chování zahrnující skryté řádky. Na tvaru stávajícího API se nic nezměnilo
Odkud vlastně pochází bit skrytého řádku?
Z listu, přes dva odlišné zdroje, protože HotXLS nese dva enginy sešitu. Starší strana BIFF odpovídá z TXLSRowInfoList.GetHidden, dosažené přes TXLSWorkbook.GetRowHidden. Strana OOXML odpovídá z TXLSXWorksheet.GetRowHidden, dosažené přes TXLSXWorkbook.GetCalcRowHidden. Obě jsou zapojené do kalkulátoru v okamžiku konstrukce, vedle callbacku pro hodnotu buňky, který zrcadlí. Konvence řádků jsou místo, kde se tento typ mostu obvykle pokazí, takže stojí za to je vyslovit explicitně. Kalkulátor předává callbacku řádek číslovaný od 0, což odpovídá souřadnicím, které už používá TXLSGetValue. List XLSX klíčuje svou mapu skrytých řádků číslem řádku od 1, přesně jak Excel čísluje řádky, což je také to, co vystavuje veřejná vlastnost RowHidden[ARow]. Most XLSX proto před vyhledáním přičte jedničku, zatímco most BIFF ne, protože TXLSRowInfoList je už číslovaný od 0. Oba mosty zachází s indexem listu nebo řádkem mimo platný rozsah jako s viditelným, takže dotaz mimo hranice degraduje na starou odpověď zahrnující skryté řádky, místo aby data zahodil
Co se mění u filtrovaných sešitů
Toto je případ, který generuje tikety podpory. Aplikace AutoFiltru v HotXLS přes ApplyAutoFilter vyhodnotí kritéria sloupce a skryje každý datový řádek, který neodpovídá, což je přesně to, co dělá Excel, když uživatel klikne na rozbalovací nabídku filtru. Před v2.197.0 byly tyto skryté řádky neviditelné pro uživatele a plně viditelné pro výpočetní engine, takže SUBTOTAL(109, ...) na straně serveru hlásil nefiltrovaný součet. Teď stejné volání hlásí ten filtrovaný
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;Ruční skrytí funguje stejným způsobem, protože RowHidden[ARow] := True je stejný stav, jaký zapisuje filtr. Tato ekvivalence je v Excelu záměrná a teď platí i v HotXLS. Jeden důsledek si zaslouží poznámku v jakékoli dokumentaci, která se dodává s vašimi generovanými sešity: součet spočítaný kódem 109 je číslo závislé na pohledu, takže příjemce, který vyčistí filtr, jej změní. Když musí zpráva uvádět pevnou hodnotu bez ohledu na to, co čtenář udělá s pohledem, kód 9 je správnou volbou a vždy byl. Filtry, validace a tabulky jsou popsány společně v článku o validaci dat, AutoFiltru a tabulkách. Protože skrývání řádků se nedotýká žádného vzorce, samo o sobě také nešpiní graf závislostí, což stojí za vědět, pokud spoléháte na přírůstkový přepočet nad špinavým podgrafem, aby velké sešity zůstaly responzivní
Kódy voleb AGGREGATE a jedno omezení, které stále zůstává otevřené
AGGREGATE je SUBTOTAL s druhým argumentem politiky a HotXLS jej obsluhuje v CalcAggregateFunc. Argument volby kóduje nezávislé přepínače: zda se přeskakují vnořená volání SUBTOTAL a AGGREGATE uvnitř rozsahu, zda se přeskakují hodnoty na skrytých řádcích a zda se chybové hodnoty potlačují místo šíření dál. HotXLS aktivuje sdílenou bránu skrytých řádků pro kódy voleb 2, 3, 6 a 7 a potlačuje chybové hodnoty pro kódy voleb 4 až 7. Argument čísla funkce pak volí agregaci přesně tak, jako to dělá SUBTOTAL, včetně směrování rozptylu, směrodatné odchylky a součinu přes jejich vlastní reducery. Zůstává jedna zdokumentovaná mezera, a je lepší ji uvést zde, než ji objevit v produkci: sémantika ignorování vnořeného SUBTOTAL spojená s nízkými kódy voleb není v HotXLS implementovaná. Detekce vnořeného SUBTOTAL uvnitř odkazovaného rozsahu vyžaduje označení stavu rekurze evaluátoru, aby se vnitřní agregace mohla ohlásit té vnější, což je větší změna než brána skrytých řádků. V praxi je expozice malá, protože reálné sešity téměř vždy umísťují vzorce SUBTOTAL mimo rozsahy, přes které agregují jiné vzorce SUBTOTAL. Pokud váš generátor skutečně sestavuje překrývající se agregační rozsahy, nespoléhejte na to, že je nízké kódy voleb odduplikují
Pojistka arity, která se dodala společně s tím
Verze 2.197.0 také uzavřela mezeru ve validaci ve stejném dispatcheru, a důvod návrhu je tentýž, který motivoval pomocné pole: umístit kontrolu tam, kde se dá napsat jen jednou. Zhruba 280 těl vestavěných funkcí každé samo ověřovalo svůj počet argumentů proti Item.ChildCount, což nenechalo žádnou konzistentní hranici pro případ příliš mnoha argumentů. Volání jako =SIN(1,2) se dostalo k tělu funkce, které prozkoumalo svůj první argument, ignorovalo přebytek a vrátilo věrohodné číslo tam, kde Excel vrací #VALUE!. HotXLS už ukládal deklarovanou aritu každé vestavěné funkce ve svém registru funkcí, vystavenou jako THashFunc.ArgsCnt, kde -1 označuje variadickou funkci jako SUM, IF nebo CONCAT. Verze 2.197.0 to přenesla přes novou vlastnost TXLSFormula.FuncArgsCntByPtg a přidala jednu bránu na začátek GetValueItemFunc, hlavního dispatcheru
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;Pojistka odmítá příliš mnoho argumentů a záměrně mlčí o příliš málo argumentech. Vynechání koncového volitelného argumentu je v Excelu legální u VLOOKUP, SUBSTITUTE a dlouhé řady dalších, takže symetrická kontrola by rozbila správné vzorce, aby zachytila nesprávné. Neznámé identifikátory se hlásí jako variadické a bránu úplně přeskočí, což je to, co drží uživatelem definované funkce mimo její cestu; pokud registrujete vlastní funkce, chování popsané v průvodci enginem vzorců a vlastními funkcemi tím není ovlivněno. Centralizace případu příliš málo argumentů je samostatný úkol, protože každé z těch 280 těl má vlastní sémantiku chybových kódů a musí být proreviewována jedno po druhém, ne jen předpokládána
Zde popsaný výpočetní engine, obě fasády sešitu a API AutoFiltru a viditelnosti řádků, která jej krmí, jsou součástí HotXLS Delphi spreadsheet component, který se dodává s kompletním zdrojovým kódem pro Delphi a C++Builder a nevyžaduje instalaci Excelu na stroji, na kterém běží