SUBTOTAL en AGGREGATE Verborgen Rijen in Delphi met HotXLS

Als SUBTOTAL(109, ...) en SUBTOTAL(9, ...) op een workbook met verborgen rijen hetzelfde getal teruggeven, is één van de twee fout. Het is een van de makkelijkste valkuilen bij het bouwen van een spreadsheet-rekenengine, en HotXLS, de native Excel-spreadsheetcomponent voor Delphi en C++Builder, ontwijkt hem door zijn rekenengine het werkblad te laten vragen of een gegeven rij verborgen is

Het symptoom komt zelden als een vraag over formulecodes binnen. Het komt als een discrepantie: een batchjob op de server berekent een totaal, een gebruiker opent hetzelfde bestand in Excel met een actief filter, en de twee getallen verschillen met wat de weggefilterde rijen toevallig opammen. Niemand verdenkt de aggregatiefunctie, want de formulertekst in de cel is op beide plekken identiek. Het verschil zit volledig in wat de evaluator te zien kreeg

Waarom zou SUBTOTAL 109 verborgen rijen meenemen?

Omdat in de meeste engineontwerpen de laag die een formule evalueert nooit iets te horen krijgt over rijzichtbaarheid. Het voor de hand liggende ontwerp haalt celwaarden op via één value-callback, zoals de TXLSGetValue-callback in lxCalc.pas, die met een waarde voor een (sheet, row, column)-drietal antwoordt en niets verder. Zichtbaarheid is een presentatieattribuut dat op de rijrecord staat, en als er niets anders door de aanroepketen meereist, houdt de engine één aggregatiepad over waar beide helften van de SUBTOTAL-functienummertabel op uitkomen. Dit is geen fout die je als afrondingsfout af kunt doen: het verborgen-rij-beleid is de hele reden dat de tweede helft van de tabel bestaat. ECMA-376 Part 1, gepubliceerd als ISO/IEC 29500-1, definieert SUBTOTAL in zijn formulefunctiedefinities (§18.17.7) met een eerste argument dat zowel de innerlijke aggregatie als het verborgen-rij-beleid kiest. Codes 1 tot en met 11 mappen op AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR en VARP inclusief waarden op handmatig verborgen rijen. Codes 101 tot en met 111 kiezen dezelfde elf aggregaties en sluiten ze uit. Een gebruiker die 109 typt in plaats van 9 doet een bewuste uitspraak over verborgen data, en een engine die dat onderscheid laat samenvallen overrulet die uitspraak stilletjes

Waarnaar de functienummers binnen de engine mappen

HotXLS lost het eerste argument van SUBTOTAL op in CalcSubtotalFunc, dat codes 101 tot en met 111 normaliseert naar dezelfde innerlijke functie-identificatoren als codes 1 tot en met 11 en dan op de aggregatie zelf dispatcht. Het grootste deel van de familie stroomt door de incrementele ExcelSum-accumulator, degene die SUM, COUNT, COUNTA, MIN, MAX en AVERAGE afhandelt. Vijf van hen kunnen dat niet: STDEV, VAR, STDEVP, VARP en PRODUCT hebben een passage in gesloten vorm over de data nodig, dus CalcSubtotalFunc routeert de innerlijke codes 12, 46, 193, 194 en 183 naar een aparte reducer, SubtotalReduceVariance. Die splitsing is het eerste wat de moeite van in kaart brengen waard is voordat je iets aanraakt, want twee onafhankelijke aggregatiepaden betekenen twee onafhankelijke cel-doorloop-lussen, en een verborgen-rij-controle op slechts één ervan levert het slechtst mogelijke resultaat op: SUBTOTAL(109, ...) honoreert het filter terwijl SUBTOTAL(107, ...) op dezelfde range dat niet doet. HotXLS heeft er zes zodra AGGREGATE meetelt, verspreid over range-evaluatie, kale range-verzameling en drie aparte reducers

Waarom een scratch-veld in plaats van zes nieuwe signaturen?

Omdat een nieuwe parameter door zes cel-doorloop-functies voeren, plus alles die die aanroepen, een brede ingreep in een heet codepad is voor de wil van één boolean. HotXLS heeft een precedent voor het alternatief: een transient veld op de calculator, in dezelfde geest als het scratchveld dat GetRangeInfo gebruikt om bij te houden wanneer een 3D-verwijzing in een extern workbook uitkwam. De verborgen-rij-gate is een tweede: een callbacktype, TXLSIsRowHidden, gedeclareerd als functie van (SheetIndex, row) die een Boolean teruggeeft, bewaard in FIsRowHidden, plus een transiente FIgnoreHiddenRows-vlag. De vlag wordt aan gezet bij binnenkomst van CalcSubtotalFunc wanneer de functiecode tussen 101 en 111 valt, en bij binnenkomst van CalcAggregateFunc voor de AGGREGATE-optiecodes die het uitsluiten van verborgen rijen kiezen. Elke cel-doorloop-lus bekijkt hem daarna en slaat één rij over wanneer hij aan staat, tegen de kosten van één regel per lus

// 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;

Twee details in de activeringscode dragen de correctheid van het hele schema. De vlag wordt bewaard en hersteld in plaats van simpelweg gezet en gewist, omdat een SUBTOTAL-argument een expressie kan bevatten die zijn eigen evaluatie draait terwijl de buitenste aggregatie nog op de stack staat, en dat geneste werk mag de buitenste poort niet erven of vernietigen. En het herstel leeft in een finally-blok, omdat CalcSubtotalFunc verschillende vroege exits heeft voor foutcodes; een vlag die na een foutretour geactiveerd blijft, zou de volgende ongerelateerde formule in de herberekeningsvolgorde stilzwijgend corrumperen

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;

De Assigned-test is wat de calculator-API compatibel houdt. De TXLSCalculator-constructor neemt een derde parameter die standaard nil is, dus code die een calculator met de aanroep met twee argumenten bouwt compileert onveranderd en krijgt het include-hidden-gedrag

Waar komt het verborgen-rij-bit daadwerkelijk vandaan?

Uit het werkblad, via twee verschillende bronnen, want HotXLS draagt twee workbook-engines mee. De legacy BIFF-kant antwoordt uit TXLSRowInfoList.GetHidden, bereikt via TXLSWorkbook.GetRowHidden. De OOXML-kant antwoordt uit TXLSXWorksheet.GetRowHidden, bereikt via TXLSXWorkbook.GetCalcRowHidden. Beide zijn bij de constructie in de calculator bedraad, naast de celwaarde-callback die ze spiegelen. De rijconventies zijn de plek waar dit soort brug normaal misgaat, dus ze zijn de moeite van expliciet benoemen waard. De calculator geeft de callback een 0-based rij, gelijk aan de coördinaten die TXLSGetValue al gebruikt. Het XLSX-werkblad sleutelt zijn row-hidden-map op 1-based rijnummer, precies zoals Excel rijen nummert, wat ook is wat de publieke property RowHidden[ARow] blootgeeft. De XLSX-brug telt er daarom één op vóór de opvraag en de BIFF-brug niet, want TXLSRowInfoList is al 0-based. Beide bruggen behandelen een bladindex of rij buiten het geldige bereik als zichtbaar, dus een query buiten de grenzen degradeert naar het include-hidden-antwoord in plaats van data te laten vallen

Wat verandert er voor gefilterde workbooks

Dit is het geval waarin het verborgen-rij-beleid het meest telt. Een AutoFilter toepassen in HotXLS via ApplyAutoFilter evalueert de kolomcriteria en verbergt elke datarij die niet matcht, precies wat Excel doet wanneer een gebruiker op een filterdropdown klikt. Die rijen zijn onzichtbaar voor de gebruiker, en omdat de rekenengine dezelfde rijstate raadpleegt, meldt een serverzijdige SUBTOTAL(109, ...) het gefilterde totaal in plaats van het ongefilterde

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;   // verbergt de niet-matchende rijen

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // De celwaarde komt overeen met wat Excel voor hetzelfde filter toont,
    // en VisibleRows vertelt je hoeveel rijen erin zijn meegeteld

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Handmatig verbergen werkt hetzelfde, want RowHidden[ARow] := True is dezelfde state die het filter wegschrijft. Die gelijkwaardigheid is in Excel bewust zo en geldt in HotXLS net zo. Eén consequentie verdient een notitie in welke documentatie je ook bij je gegenereerde workbooks levert: een totaal berekend met code 109 is een weergaveafhankelijk getal, dus een ontvanger die het filter weghaalt verandert het. Wanneer een rapport een vast getal moet noemen ongeacht wat de lezer met de weergave doet, is code 9 de juiste keuze en dat was hij altijd al. Filters, validatie en tabellen worden samen behandeld in het artikel over datavalidatie, AutoFilter en tabellen. Omdat rijen verbergen geen enkele formule aanraakt, maakt het ook van zichzelf de afhankelijkheidsgraf niet vuil, wat goed om te weten is als je vertrouwt op incrementele herberekening over de vuile subgraaf om grote workbooks responsief te houden

AGGREGATE-optiecodes

AGGREGATE is SUBTOTAL met een tweede beleidsargument, en HotXLS behandelt het in CalcAggregateFunc. Het optieargument codeert onafhankelijke schakelaars: of geneste SUBTOTAL- en AGGREGATE-aanroepen binnen de range worden overgeslagen, of waarden op verborgen rijen worden overgeslagen en of foutwaarden worden onderdrukt in plaats van doorgegeven. HotXLS zet de gedeelde verborgen-rij-gate aan voor optiecodes 2, 3, 6 en 7, en onderdrukt foutwaarden voor optiecodes 4 tot en met 7. Het functienummerargument kiest daarna de aggregatie precies zoals SUBTOTAL dat doet, inclusief het routeren van variantie, standaarddeviatie en product door hun eigen reducers. Echte workbooks zetten SUBTOTAL-formules bijna altijd buiten de ranges die andere SUBTOTAL-formules aggregeren; bouwt je generator overlappende aggregatieranges, houd dan de binnenste totalen buiten de buitenste range in plaats van op de optiecodes te vertrouwen om ze te dedupliceren

Een arity-bewaking in dezelfde dispatcher

Dezelfde dispatcher draagt een argumenttelling-controle mee, en de ontwerpreden is dezelfde als achter het scratchveld: zet de controle waar hij één keer geschreven kan worden. Met zo'n 280 ingebouwde functielichamen geeft het aan elk lichaam zelf overlaten om zijn eigen argumenttelling tegen Item.ChildCount te verifiëren geen consistente grens voor het geval van te veel argumenten, en de valkuil is makkelijk te missen: een aanroep als =SIN(1,2) bereikt een functielichaam dat zijn eerste argument bekijkt, het overschot negeert en een geloofwaardig getal teruggeeft waar Excel #VALUE! teruggeeft. HotXLS bewaart de gedeclareerde arity van elke ingebouwde functie in zijn functieregister, blootgegeven als THashFunc.ArgsCnt met -1 voor een variadische functie als SUM, IF of CONCAT, stuurt hem door via de property TXLSFormula.FuncArgsCntByPtg en controleert hem in één gate bovenaan GetValueItemFunc, de hoofddispatcher

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] is het functieknooppunt
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) geeft #VALUE!
    Exit;
  end;
end;

De bewaking wijst te veel argumenten af en zegt bewust niets over te weinig. Een optioneel argument achteraan weglaten is in Excel geldig voor VLOOKUP, SUBSTITUTE en een lange lijst anderen, dus een symmetrische controle zou correcte formules breken om incorrecte te vangen. Onbekende identificatoren melden zich als variadisch en slaan de gate volledig over, wat gebruiker-gedefinieerde functies uit haar vaarwater houdt; registreer je eigen functies, dan blijft het gedrag uit de gids over de formule-engine en custom functions onaangetast. Het te-weinig-geval blijft bij elk functielichaam zelf, want elk van die lichamen heeft zijn eigen foutcodesemantiek

De hier beschreven rekenmachine, beide workbook-facades, en de AutoFilter- en rijzichtbaarheids-API's die hem voeden, maken deel uit van het HotXLS Delphi-spreadsheetcomponent, dat wordt geleverd met volledige broncode voor Delphi en C++Builder en geen Excel-installatie vereist op de machine die het uitvoert