Als SUBTOTAL(109, ...) en SUBTOTAL(9, ...) hetzelfde getal teruggeven op een workbook dat verborgen rijen bevat, is een van de twee fout. HotXLS, het native Excel-spreadsheetcomponent voor Delphi en C++Builder, gedroeg zich precies zo tot en met versie 2.197.0, omdat zijn rekenmachine geen manier had om een werkblad te vragen of een gegeven rij verborgen was
Het symptoom komt zelden binnen als een bugrapport over formulecodes. Het komt binnen als een mismatch: een batchjob op de server berekent een totaal, een gebruiker opent hetzelfde bestand in Excel met een filter toegepast, en de twee getallen verschillen met wat de uitgefilterde rijen toevallig optelden. Niemand verdenkt de aggregatiefunctie, want de formulestring in de cel is op beide plaatsen identiek. Het verschil zit volledig in wat de evaluator mocht zien
Waarom bevat SUBTOTAL 109 verborgen rijen?
Omdat in de meeste engine-ontwerpen de laag die een formule evalueert nooit iets leert over rijzichtbaarheid. HotXLS was een schoolvoorbeeld: de rekenmachine in lxCalc.pas benaderde celwaarden via één enkele TXLSGetValue-callback die antwoordt met een waarde voor een (blad, rij, kolom)-drietal en niets anders. Zichtbaarheid is een presentatie-attribuut opgeslagen op het rij-record, en geen enkel deel van dat record reisde mee door de aanroepketen. De engine had dus één aggregatiepad, en beide helften van de SUBTOTAL-functienummer-tabel losten daarop op. Dat is geen afrondingsfout-klasse defect: het is de hele reden dat de tweede helft van de tabel bestaat. ECMA-376 Deel 1, gepubliceerd als ISO/IEC 29500-1, definieert SUBTOTAL in zijn formule-functiedefinities (§18.17.7) met een eerste argument dat zowel de innerlijke aggregatie als het verborgen-rij-beleid selecteert. Codes 1 tot en met 11 mappen naar AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, en VARP terwijl ze waarden op handmatig verborgen rijen meetellen. Codes 101 tot en met 111 selecteren 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 het onderscheid stilzwijgend laat samenvallen, overrulet die uitspraak stilzwijgend
Waarnaar de functienummers binnen de engine mappen
HotXLS lost het eerste SUBTOTAL-argument op in CalcSubtotalFunc, die codes 101 tot en met 111 normaliseert naar dezelfde innerlijke functie-identifiers als codes 1 tot en met 11 en vervolgens dispatcht op de aggregatie zelf. Het grootste deel van de familie loopt door de incrementele ExcelSum-accumulator, degene die SUM, COUNT, COUNTA, MIN, MAX, en AVERAGE afhandelt. Vijf ervan kunnen dat niet: STDEV, VAR, STDEVP, VARP, en PRODUCT hebben een gesloten-vorm-doorgang over de data nodig, dus routeert CalcSubtotalFunc de innerlijke codes 12, 46, 193, 194, en 183 naar een aparte reducer, SubtotalReduceVariance. Die splitsing is het eerste wat de moeite waard is om in kaart te brengen voordat je iets aanraakt, want twee onafhankelijke aggregatiepaden betekenen twee onafhankelijke cel-doorloop-lussen, en een fix toegepast op slechts één daarvan produceert het slechtst mogelijke resultaat: SUBTOTAL(109, ...) respecteert het filter terwijl SUBTOTAL(107, ...) op hetzelfde bereik dat niet doet. Het tellen van de lussen in HotXLS leverde er zes op zodra AGGREGATE meegerekend werd, verspreid over bereikevaluatie, gewone bereikverzameling, en drie aparte reducers
Waarom een scratch-veld in plaats van zes nieuwe signaturen?
Omdat het doorvoeren van een nieuwe parameter door zes cel-doorloop-functies, plus alles wat ze aanroept, een brede verandering is aan een hot code path omwille van één boolean. HotXLS had al een precedent voor het alternatief: een transiënt veld op de calculator, in dezelfde geest als het scratch-veld dat GetRangeInfo gebruikt om vast te leggen wanneer een 3D-referentie oploste naar een externe workbook. Versie 2.197.0 voegde er een tweede toe. De engine kreeg een callback-type, TXLSIsRowHidden, gedeclareerd als een functie van (SheetIndex, row) die Boolean teruggeeft, opgeslagen in FIsRowHidden, plus een transiënte FIgnoreHiddenRows-vlag. De vlag wordt geactiveerd bij binnenkomst van CalcSubtotalFunc wanneer de functiecode tussen 101 en 111 valt, en bij binnenkomst van CalcAggregateFunc voor de AGGREGATE-optiecodes die verborgen-rij-uitsluiting selecteren. Elke cel-doorloop-lus inspecteert hem vervolgens en slaat één rij over wanneer hij ingesteld is, waarbij elk daarvan één enkele regel toevoegt
// 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 verandering compatibel houdt. HotXLS breidde de calculator-constructor uit met een derde parameter met standaard nil, dus compileert elke code die een TXLSCalculator bouwt met de oude tweeargument-aanroep nog steeds en krijgt nog steeds het legacy include-hidden-gedrag. Niets aan de bestaande API veranderde van vorm
Waar komt het verborgen-rij-bit daadwerkelijk vandaan?
Van het werkblad, via twee verschillende bronnen, omdat HotXLS twee workbook-engines draagt. De legacy BIFF-kant antwoordt vanuit TXLSRowInfoList.GetHidden, bereikt via TXLSWorkbook.GetRowHidden. De OOXML-kant antwoordt vanuit TXLSXWorksheet.GetRowHidden, bereikt via TXLSXWorkbook.GetCalcRowHidden. Beide zijn bekabeld in de calculator op constructietijd, naast de celwaarde-callback die ze spiegelen. De rijconventies zijn waar dit soort brug normaal fout gaat, dus zijn ze het waard om expliciet te noemen. De calculator geeft de callback een 0-gebaseerde rij, overeenkomstig de coördinaten die TXLSGetValue al gebruikt. Het XLSX-werkblad sleutelt zijn rij-verborgen-map op 1-gebaseerd rijnummer, precies zoals Excel rijen nummert, wat ook is wat de publieke RowHidden[ARow]-eigenschap blootstelt. De XLSX-brug telt daarom één op voordat hij zoekt, en de BIFF-brug niet, omdat TXLSRowInfoList al 0-gebaseerd is. Beide bruggen behandelen een blad-index of rij buiten het geldige bereik als zichtbaar, dus degradeert een out-of-bounds-query naar het oude include-hidden-antwoord in plaats van data te laten vallen
Wat verandert er voor gefilterde workbooks
Dit is het geval dat de supporttickets genereert. Het toepassen van een AutoFilter in HotXLS via ApplyAutoFilter evalueert de kolomcriteria en verbergt elke datarij die niet matcht, wat precies is wat Excel doet wanneer een gebruiker op een filter-dropdown klikt. Vóór v2.197.0 waren die verborgen rijen onzichtbaar voor de gebruiker en volledig zichtbaar voor de rekenmachine, dus rapporteerde een server-side SUBTOTAL(109, ...) het ongefilterde totaal. Nu rapporteert dezelfde aanroep het gefilterde
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;Handmatig verbergen werkt op dezelfde manier, aangezien RowHidden[ARow] := True dezelfde toestand is die het filter schrijft. Die gelijkwaardigheid is bewust in Excel en geldt nu ook in HotXLS. Eén gevolg verdient een notitie in welke documentatie dan ook die met je gegenereerde workbooks meegaat: een totaal berekend met code 109 is een view-afhankelijk getal, dus een ontvanger die het filter opheft, verandert het. Wanneer een rapport een vast cijfer moet vermelden ongeacht wat de lezer met de weergave doet, is code 9 de juiste keuze en was dat altijd al. Filters, validatie, en tabellen worden samen behandeld in het artikel over gegevensvalidatie, AutoFilter, en tabellen. Omdat het verbergen van rijen geen enkele formule aanraakt, bevuilt het ook de afhankelijkheidsgraaf niet op eigen kracht, wat de moeite waard is om te weten als je vertrouwt op incrementele herberekening over de vuile subgraaf om grote workbooks responsief te houden
AGGREGATE-optiecodes en één limiet die nog open staat
AGGREGATE is SUBTOTAL met een tweede beleidsargument, en HotXLS handelt het af in CalcAggregateFunc. Het optie-argument codeert onafhankelijke schakelaars: of geneste SUBTOTAL- en AGGREGATE-aanroepen binnen het bereik overgeslagen worden, of waarden op verborgen rijen overgeslagen worden, en of foutwaarden onderdrukt worden in plaats van doorgegeven. HotXLS activeert de gedeelde verborgen-rij-poort voor optiecodes 2, 3, 6, en 7, en onderdrukt foutwaarden voor optiecodes 4 tot en met 7. Het functienummer-argument selecteert vervolgens de aggregatie precies zoals SUBTOTAL dat doet, inclusief de routering van variantie, standaarddeviatie, en product via hun eigen reducers. Eén gedocumenteerd gat blijft over, en het is beter het hier te vermelden dan het in productie te ontdekken: de ignore-nested-SUBTOTAL-semantiek geassocieerd met de lage optiecodes is niet geïmplementeerd in HotXLS. Het detecteren van een geneste SUBTOTAL binnen een gerefereerd bereik vereist het markeren van de evaluator-recursiestatus zodat een innerlijke aggregatie zich aan de buitenste kan aankondigen, wat een grotere verandering is dan de verborgen-rij-poort. In de praktijk is de blootstelling klein, omdat echte workbooks SUBTOTAL-formules bijna altijd buiten de bereiken plaatsen die andere SUBTOTAL-formules aggregeren. Als je generator wel overlappende aggregatiebereiken bouwt, vertrouw dan niet op de lage optiecodes om ze te dedupliceren
De arity-guard die er tegelijk mee meeging
Versie 2.197.0 sloot ook een validatiegat in dezelfde dispatcher, en de ontwerpreden is dezelfde die het scratch-veld motiveerde: plaats de controle waar hij één keer geschreven kan worden. Ruwweg 280 ingebouwde functiehoofdtekst-implementaties controleerden elk hun eigen argumentaantal tegen Item.ChildCount, wat geen consistente grens overliet voor het geval van te veel argumenten. Een aanroep zoals =SIN(1,2) bereikte een functiehoofdtekst die zijn eerste argument onderzocht, het overschot negeerde, en een plausibel getal teruggaf waar Excel #VALUE! teruggeeft. HotXLS sloeg de gedeclareerde ariteit van elke ingebouwde functie al op in zijn functieregister, blootgesteld als THashFunc.ArgsCnt met -1 die een variadische functie zoals SUM, IF, of CONCAT markeert. Versie 2.197.0 stuurde dat door via een nieuwe TXLSFormula.FuncArgsCntByPtg-eigenschap en voegde één poort toe bovenaan GetValueItemFunc, de hoofddispatcher
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;De guard wijst te veel argumenten af en zegt bewust niets over te weinig. Het weglaten van een afsluitend optioneel argument is legaal in Excel voor VLOOKUP, SUBSTITUTE, en een lange lijst andere, dus zou een symmetrische controle correcte formules gebroken hebben om incorrecte te vangen. Onbekende identifiers rapporteren als variadisch en slaan de poort volledig over, wat is wat gebruikersgedefinieerde functies buiten zijn bereik houdt; als je je eigen functies registreert, is het gedrag beschreven in de gids voor de formule-engine en aangepaste functies onaangetast. Het centraliseren van het te-weinig-geval is een apart karwei, want elk van die 280 hoofdteksten heeft zijn eigen foutcode-semantiek en ze moeten één voor één beoordeeld worden in plaats van aangenomen
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