SUBTOTAL a AGGREGATE skryté riadky v Delphi s HotXLS

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á

// Tvar opakovaný vo všetkých šiestich slučkách prechodu bunkami
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
      // ... zlúčiť Cells[rr, cc] do akumulátora ...
    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
  // agregovať cez Item.Child[2] .. Item.Child[ChildCount]
  // každá cesta Exit nižšie je pokrytá blokom 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;   // skryje nezodpovedajúce riadky

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // Hodnota bunky teraz zodpovedá tomu, čo Excel zobrazuje pre rovnaký filter,
    // a VisibleRows udáva, koľko riadkov do nej vstúpilo

    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] je uzol funkcie
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) teraz vráti #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