Ak SUBTOTAL(109, ...) a SUBTOTAL(9, ...) vrátia rovnaké číslo na workbooku, ktorý obsahuje skryté riadky, jedno z tých dvoch je zlé. HotXLS, natívna Excel spreadsheet komponenta pre Delphi a C++Builder, sa presne takto správal až do verzie 2.197.0, pretože jej výpočtový engine nemal spôsob, ako sa spýtať hárka, či je daný riadok skrytý
Symptóm zriedka príde ako bug report o kódoch vzorca. Príde ako nesúlad: dávková úloha na serveri vypočíta súčet, používateľ otvorí ten istý súbor v Exceli s aplikovaným filtrom, a tie dve čísla sa líšia o to, čo vyfiltrované riadky práve dávali dokopy. Nikto nepodozrieva agregačnú funkciu, pretože reťazec vzorca v bunke je na oboch miestach identický. Rozdiel je úplne v tom, čo evaluátor smel vidieť
Prečo SUBTOTAL 109 zahŕňa skryté riadky?
Pretože vo väčšine návrhov enginov sa vrstva, ktorá vyhodnocuje vzorec, nikdy nedozvie o viditeľnosti riadku. HotXLS bol učebnicový prípad: výpočtový engine v lxCalc.pas dosahoval hodnoty buniek cez jediný callback TXLSGetValue, ktorý odpovedá hodnotou pre trojicu (hárok, riadok, stĺpec) a ničím iným. Viditeľnosť je atribút prezentácie uložený v zázname riadku, a žiadna časť tohto záznamu necestovala dole reťazou volaní. Engine teda mal jednu agregačnú cestu, a obe polovice tabuľky čísel funkcií SUBTOTAL sa vyriešili na ňu. To nie je chyba triedy zaokrúhľovacej chyby: je to celý dôvod, prečo druhá polovica tabuľky existuje. ECMA-376 Part 1, publikovaný ako ISO/IEC 29500-1, definuje SUBTOTAL vo svojich definíciách funkcií vzorcov (§18.17.7) s prvým argumentom, ktorý vyberá vnútornú agregáciu aj politiku skrytých riadkov. Kódy 1 až 11 sa mapujú na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR a VARP a zahŕňajú hodnoty na ručne skrytých riadkoch. Kódy 101 až 111 vyberajú tú istú jedenástku agregácií a vylučujú ich. Používateľ, ktorý napíše 109 namiesto 9, robí zámerné vyhlásenie o skrytých dátach, a engine, ktorý toto rozlíšenie potichu zruší, toto vyhlásenie potichu prebije
Na čo sa čísla funkcií mapujú vnútri enginu
HotXLS rieši prvý argument SUBTOTAL v CalcSubtotalFunc, ktorý normalizuje kódy 101 až 111 dole na rovnaké vnútorné identifikátory funkcií ako kódy 1 až 11, a potom rozhoduje o samotnej agregácii. Väčšina rodiny prúdi cez inkrementálny akumulátor ExcelSum, ten, ktorý spracúva SUM, COUNT, COUNTA, MIN, MAX a AVERAGE. Päť z nich to nemôže: STDEV, VAR, STDEVP, VARP a PRODUCT potrebujú prechod v uzavretej forme cez dáta, takže CalcSubtotalFunc smeruje vnútorné kódy 12, 46, 193, 194 a 183 do samostatného reduktora, SubtotalReduceVariance. Toto rozdelenie je prvá vec, ktorú sa oplatí zmapovať skôr, než sa čohokoľvek dotknete, pretože dve nezávislé agregačné cesty znamenajú dve nezávislé slučky prechodu bunkami, a oprava aplikovaná len na jednu z nich vyprodukuje najhorší možný výsledok: SUBTOTAL(109, ...) rešpektuje filter, kým SUBTOTAL(107, ...) na tom istom rozsahu nie. Spočítanie slučiek v HotXLS ukázalo šesť z nich, keď bol zahrnutý AGGREGATE, rozložených naprieč vyhodnotením rozsahu, obyčajným zberom rozsahu a tromi samostatnými reduktormi
Prečo scratch pole namiesto šiestich nových signatúr?
Pretože prevliecť nový parameter cez šesť funkcií prechádzajúcich bunky, plus všetko, čo ich volá, je široká zmena horúcej cesty kódu kvôli jednému boolean. HotXLS už mal precedens pre alternatívu: prechodné pole na kalkulátore, v rovnakom duchu ako scratch pole, ktoré GetRangeInfo používa na zaznamenanie, kedy sa 3D referencia vyriešila do externého workbooku. Verzia 2.197.0 pridala druhé. Engine dostal typ callbacku, TXLSIsRowHidden, deklarovaný ako funkcia (SheetIndex, row) vracajúca Boolean, uložený v FIsRowHidden, plus prechodný príznak FIgnoreHiddenRows. Príznak sa vyzbrojí na vstupe CalcSubtotalFunc, keď kód funkcie padne do rozsahu 101 až 111, a na vstupe CalcAggregateFunc pre option kódy AGGREGATE, ktoré vyberajú vylúčenie skrytých riadkov. Každá slučka prechodu bunkami potom skontroluje tento príznak a preskočí jeden riadok, keď je nastavený, pridávajúc jediný riadok každá
// 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 kóde vyzbrojenia nesú správnosť celej schémy. Príznak sa uloží a obnoví namiesto jednoducho nastaveného a vymazaného, pretože argument SUBTOTAL môže obsahovať výraz, ktorý beží svoje vlastné vyhodnotenie, kým je vonkajšia agregácia ešte na zásobníku, a táto vnorená práca nesmie zdediť ani zničiť vonkajšiu bránu. A obnovenie žije v bloku finally, pretože CalcSubtotalFunc má niekoľko skorých výstupov pre chybové kódy; príznak ponechaný vyzbrojený po návrate z chyby by potichu skorumpoval ďalší nesúvisiaci vzorec v poradí prepoč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, čo udržuje zmenu kompatibilnú. HotXLS rozšíril konštruktor kalkulátora o tretí parameter s predvolenou hodnotou nil, takže akýkoľvek kód, ktorý zostavuje TXLSCalculator so starým dvojargumentovým volaním, sa stále skompiluje a stále dostane legacy správanie zahŕňajúce skryté. Na tvare existujúceho API sa nič nezmenilo
Odkiaľ vlastne pochádza bit skrytého riadku?
Z hárka, cez dva rôzne zdroje, pretože HotXLS nesie dva workbook enginy. Legacy BIFF strana odpovedá z TXLSRowInfoList.GetHidden, dosiahnutá cez TXLSWorkbook.GetRowHidden. OOXML strana odpovedá z TXLSXWorksheet.GetRowHidden, dosiahnutá cez TXLSXWorkbook.GetCalcRowHidden. Obidve sú zapojené do kalkulátora pri konštrukcii, popri callbacku hodnoty bunky, ktorý zrkadlia. Konvencie riadkov sú miesto, kde tento druh mosta zvyčajne pochybí, takže sa oplatí ich explicitne uviesť. Kalkulátor odovzdá callbacku 0-based riadok, zhodujúci sa so súradnicami, ktoré TXLSGetValue už používa. OOXML hárok kľúčuje svoju mapu skrytých riadkov 1-based číslom riadku, presne ako Excel čísluje riadky, čo je tiež to, čo vystavuje verejná vlastnosť RowHidden[ARow]. XLSX most preto pred vyhľadaním pripočíta jedna, a BIFF most nie, pretože TXLSRowInfoList je už 0-based. Oba mosty traktujú index hárka alebo riadku mimo platného rozsahu ako viditeľný, takže dopyt mimo hraníc degraduje na starú odpoveď zahŕňajúcu skryté namiesto zahodenia dát
Čo sa zmení pre filtrované workbooky
Toto je prípad, ktorý generuje support tikety. Aplikovanie AutoFilter v HotXLS cez ApplyAutoFilter vyhodnotí kritériá stĺpca a skryje každý dátový riadok, ktorý nezodpovedá, čo je presne to, čo Excel robí, keď používateľ klikne na dropdown filtra. Pred v2.197.0 boli tieto skryté riadky neviditeľné pre používateľa a plne viditeľné pre výpočtový engine, takže serverový SUBTOTAL(109, ...) hlásil nefiltrovaný súčet. Teraz to isté volanie hlási 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é skrytie funguje rovnako, keďže RowHidden[ARow] := True je ten istý stav, ktorý zapisuje filter. Táto ekvivalencia je v Exceli zámerná a teraz platí aj v HotXLS. Jeden dôsledok si zaslúži poznámku v akejkoľvek dokumentácii, ktorá sa dodáva s vašimi generovanými workbookmi: súčet vypočítaný s kódom 109 je číslo závislé od pohľadu, takže príjemca, ktorý vyčistí filter, ho zmení. Keď musí správa uvádzať pevnú hodnotu bez ohľadu na to, čo čitateľ urobí s pohľadom, kód 9 je správna voľba a vždy bol. Filtre, validácia a tabuľky sú pokryté spolu v článku o validácii dát, AutoFilter a tabuľkách. Pretože skrytie riadkov sa nedotýka žiadneho vzorca, tiež samo osebe nezašpiní graf závislostí, čo stojí za vedomosť, ak sa spoliehate na inkrementálny prepočet cez špinavý podgraf, aby veľké workbooky ostali responzívne
Option kódy AGGREGATE a jeden limit, ktorý je stále otvorený
AGGREGATE je SUBTOTAL s druhým argumentom politiky, a HotXLS ho spracuje v CalcAggregateFunc. Argument voľby kóduje nezávislé prepínače: či sú preskočené vnorené volania SUBTOTAL a AGGREGATE vnútri rozsahu, či sú preskočené hodnoty na skrytých riadkoch, a či sú chybové hodnoty potlačené namiesto propagácie. HotXLS vyzbrojí zdieľanú bránu skrytých riadkov pre option kódy 2, 3, 6 a 7, a potlačí chybové hodnoty pre option kódy 4 až 7. Argument čísla funkcie potom vyberá agregáciu presne ako SUBTOTAL, vrátane smerovania variancie, smerodajnej odchýlky a produktu cez ich vlastné reduktory. Jedna zdokumentovaná medzera zostáva, a je lepšie ju uviesť tu než ju objaviť v produkcii: sémantika ignore-nested-SUBTOTAL spojená s nízkymi option kódmi nie je v HotXLS implementovaná. Detekcia vnoreného SUBTOTAL vnútri referencovaného rozsahu vyžaduje označenie stavu rekurzie evaluátora, aby sa vnútorná agregácia mohla ohlásiť vonkajšej, čo je väčšia zmena než brána skrytých riadkov. V praxi je expozícia malá, pretože skutočné workbooky takmer vždy umiestňujú vzorce SUBTOTAL mimo rozsahov, ktoré agregujú iné vzorce SUBTOTAL. Ak váš generátor buduje prekrývajúce sa agregačné rozsahy, nespoliehajte sa na nízke option kódy, že ich deduplikujú
Ochrana arity, ktorá sa dodala popri tomto
Verzia 2.197.0 tiež uzavrela medzeru vo validácii v tom istom dispatcheri, a dôvod dizajnu je ten istý, ktorý motivoval scratch pole: dať kontrolu tam, kde sa dá napísať raz. Zhruba 280 vstavaných tiel funkcií si každé overovalo vlastný počet argumentov voči Item.ChildCount, čo nenechalo žiadnu konzistentnú hranicu pre prípad príliš veľa argumentov. Volanie ako =SIN(1,2) dosiahlo telo funkcie, ktoré preskúmalo svoj prvý argument, ignorovalo prebytok, a vrátilo vierohodné číslo tam, kde Excel vracia #VALUE!. HotXLS už ukladal deklarovanú aritu každej vstavanej funkcie vo svojom registri funkcií, vystavenú ako THashFunc.ArgsCnt s -1 označujúcim variadickú funkciu ako SUM, IF, alebo CONCAT. Verzia 2.197.0 to preposlala cez novú vlastnosť TXLSFormula.FuncArgsCntByPtg a pridala jednu bránu na vrchol GetValueItemFunc, hlavného dispatchera
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;Ochrana odmieta príliš veľa argumentov a zámerne nehovorí nič o príliš málo. Vynechanie koncového voliteľného argumentu je v Exceli legálne pre VLOOKUP, SUBSTITUTE a dlhý zoznam ďalších, takže symetrická kontrola by pokazila správne vzorce, aby zachytila nesprávne. Neznáme identifikátory sa hlásia ako variadické a vynechajú bránu úplne, čo je to, čo drží používateľom definované funkcie mimo jej cesty; ak si registrujete vlastné funkcie, správanie popísané v sprievodcovi výpočtovým enginom a vlastnými funkciami je nedotknuté. Centralizácia prípadu príliš málo je samostatná úloha, pretože každé z týchto 280 tiel má vlastnú sémantiku chybových kódov a musia byť preskúmané jedno po druhom, nie predpokladané
Výpočtový engine tu popísaný, obe fasády workbooku, a API AutoFilter a viditeľnosti riadkov, ktoré ho napájajú, sú súčasťou HotXLS Delphi spreadsheet komponenty, ktorá sa dodáva s plným zdrojovým kódom pre Delphi a C++Builder a nevyžaduje inštaláciu Excelu na stroji, ktorý ju spúšťa