Hvis SUBTOTAL(109, ...) og SUBTOTAL(9, ...) returnerer det samme tal på en arbejdsbog, der indeholder skjulte rækker, er en af de to forkert. HotXLS, den native Excel-regnearkskomponent til Delphi og C++Builder, opførte sig netop sådan indtil version 2.197.0, fordi dens beregningsmotor ingen måde havde til at spørge et regneark, om en given række var skjult
Symptomet ankommer sjældent som en fejlrapport om formelkoder. Det ankommer som en uoverensstemmelse: et batch-job på serveren beregner en total, en bruger åbner den samme fil i Excel med et filter anvendt, og de to tal afviger med, hvad de filtrerede rækker tilfældigvis summerede til. Ingen mistænker aggregeringsfunktionen, fordi formelstrengen i cellen er identisk begge steder. Forskellen ligger udelukkende i, hvad evaluatoren fik lov til at se
Hvorfor inkluderer SUBTOTAL 109 skjulte rækker?
Fordi laget, der evaluerer en formel, i de fleste motordesigns aldrig lærer om rækkesynlighed. HotXLS var et lærebogseksempel: beregningsmotoren i lxCalc.pas nåede celleværdier gennem et enkelt TXLSGetValue-callback, der svarer med en værdi for en (ark, række, kolonne)-tripel og intet andet. Synlighed er en præsentationsattribut gemt på rækkeposten, og ingen del af den post rejste ned gennem kaldekæden. Motoren havde derfor én aggregeringssti, og begge halvdele af SUBTOTAL-funktionsnummertabellen opløste sig til den. Det er ikke en afrundingsfejls-klasse defekt: det er hele grunden til, at anden halvdel af tabellen findes. ECMA-376 Part 1, udgivet som ISO/IEC 29500-1, definerer SUBTOTAL i sine formelfunktionsdefinitioner (§18.17.7) med et første argument, der vælger både den indre aggregering og skjult-række-politikken. Koderne 1 til 11 mapper til AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR og VARP, mens de inkluderer værdier på manuelt skjulte rækker. Koderne 101 til 111 vælger de samme elleve aggregeringer og udelukker dem. En bruger, der skriver 109 i stedet for 9, afgiver en bevidst udtalelse om skjulte data, og en motor der kollapser skelnen, tilsidesætter stille den udtalelse
Hvad funktionsnumrene mapper til inde i motoren
HotXLS opløser SUBTOTAL-førsteargumentet i CalcSubtotalFunc, som normaliserer koderne 101 til 111 ned på de samme indre funktionsidentifikatorer som koderne 1 til 11 og dispatcher så på selve aggregeringen. Størstedelen af familien flyder gennem den inkrementelle ExcelSum-akkumulator, den der håndterer SUM, COUNT, COUNTA, MIN, MAX og AVERAGE. Fem af dem kan ikke: STDEV, VAR, STDEVP, VARP og PRODUCT har brug for en lukket-form-gennemgang over dataene, så CalcSubtotalFunc ruter indre koder 12, 46, 193, 194 og 183 til en separat reducer, SubtotalReduceVariance. Den opdeling er det første, der er værd at kortlægge, før man rører noget, fordi to uafhængige aggregeringsstier betyder to uafhængige celle-gennemgangs-loops, og en rettelse anvendt på kun én af dem producerer det værst mulige resultat: SUBTOTAL(109, ...) respekterer filteret, mens SUBTOTAL(107, ...) på samme interval ikke gør. At tælle loopene i HotXLS afslørede seks af dem, når AGGREGATE var inkluderet, spredt over intervalevaluering, almindelig intervalindsamling og tre separate reducere
Hvorfor et scratch-felt i stedet for seks nye signaturer?
Fordi at tråde en ny parameter gennem seks celle-gennemgangsfunktioner, plus alt der kalder dem, er en bred ændring til en hed kodesti for skyld af én boolean. HotXLS havde allerede en præcedens for alternativet: et transient felt på kalkulatoren, i samme ånd som scratch-feltet GetRangeInfo bruger til at registrere, hvornår en 3D-reference opløstes ind i en ekstern arbejdsbog. Version 2.197.0 tilføjede et andet et. Motoren fik en callback-type, TXLSIsRowHidden, deklareret som en funktion af (SheetIndex, row), der returnerer Boolean, gemt i FIsRowHidden, plus et transient FIgnoreHiddenRows-flag. Flaget bevæbnes ved indgangen til CalcSubtotalFunc, når funktionskoden falder i 101 til 111, og ved indgangen til CalcAggregateFunc for de AGGREGATE-optionskoder, der vælger skjult-række-udelukkelse. Hver celle-gennemgangs-loop inspicerer den derefter og springer én række over, når den er sat, og tilføjer en enkelt linje hver
// 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;To detaljer i bevæbningskoden bærer korrektheden af hele ordningen. Flaget gemmes og gendannes frem for blot at blive sat og ryddet, fordi et SUBTOTAL-argument kan indeholde et udtryk, der kører sin egen evaluering, mens den ydre aggregering stadig er på stakken, og det indlejrede arbejde må ikke arve eller ødelægge den ydre port. Og gendannelsen bor i en finally-blok, fordi CalcSubtotalFunc har flere tidlige exits for fejlkoder; et flag efterladt bevæbnet efter en fejl-return ville stille korrumpere den næste urelaterede formel i genberegningsrækkefølgen
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;Assigned-testen er det, der holder ændringen kompatibel. HotXLS udvidede kalkulator-konstruktøren med en tredje parameter, der som standard er nil, så enhver kode, der bygger en TXLSCalculator med det gamle to-argument-kald, stadig kompilerer og stadig får den gamle inkluder-skjult-opførsel. Intet ved den eksisterende API skiftede form
Hvor kommer den skjult-række-bit egentlig fra?
Fra regnearket, gennem to forskellige kilder, fordi HotXLS bærer to arbejdsbogsmotorer. Den gamle BIFF-side svarer fra TXLSRowInfoList.GetHidden, nået gennem TXLSWorkbook.GetRowHidden. OOXML-siden svarer fra TXLSXWorksheet.GetRowHidden, nået gennem TXLSXWorkbook.GetCalcRowHidden. Begge er koblet ind i kalkulatoren ved konstruktionstidspunktet, sammen med det celleværdi-callback de spejler. Rækkekonventionerne er der, hvor denne slags bro normalt går galt, så de er værd at nævne eksplicit. Kalkulatoren overgiver callbacket en 0-baseret række, matchende de koordinater TXLSGetValue allerede bruger. XLSX-regnearket nøgler sit skjult-række-map med 1-baseret rækkenummer, netop som Excel nummererer rækker, hvilket også er hvad den offentlige RowHidden[ARow]-egenskab eksponerer. XLSX-broen tilføjer derfor én før opslaget, og BIFF-broen gør ikke, fordi TXLSRowInfoList allerede er 0-baseret. Begge broer behandler et arkindeks eller en række uden for det gyldige interval som synlig, så en forespørgsel uden for grænserne degraderer til det gamle inkluder-skjult-svar frem for at droppe data
Hvad ændrer sig for filtrerede arbejdsbøger
Dette er tilfældet, der genererer supportsagerne. At anvende et AutoFilter i HotXLS gennem ApplyAutoFilter evaluerer kolonnekriterierne og skjuler hver datarække, der ikke matcher, hvilket er netop hvad Excel gør, når en bruger klikker på en filter-dropdown. Før v2.197.0 var de skjulte rækker usynlige for brugeren og fuldt synlige for beregningsmotoren, så et server-side SUBTOTAL(109, ...) rapporterede den ufiltrerede total. Nu rapporterer det samme kald den filtrerede
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;Manuel skjulning virker på samme måde, siden RowHidden[ARow] := True er den samme tilstand, filteret skriver. Den ækvivalens er bevidst i Excel og gælder nu også i HotXLS. En konsekvens fortjener en note i hvilken dokumentation, der end leveres med dine genererede arbejdsbøger: en total beregnet med kode 109 er et visningsafhængigt tal, så en modtager, der rydder filteret, ændrer det. Når en rapport skal angive et fast tal uanset hvad læseren gør ved visningen, er kode 9 det korrekte valg og var det altid. Filtre, validering og tabeller dækkes sammen i artiklen om datavalidering, AutoFilter og tabeller. Fordi at skjule rækker ikke rører nogen formel, gør det heller ikke afhængighedsgrafen beskidt af sig selv, hvilket er værd at vide, hvis man er afhængig af inkrementel genberegning over den beskidte undergraf for at holde store arbejdsbøger responsive
AGGREGATE-optionskoder og en grænse der stadig er åben
AGGREGATE er SUBTOTAL med et andet politik-argument, og HotXLS håndterer det i CalcAggregateFunc. Optionsargumentet koder uafhængige kontakter: om indlejrede SUBTOTAL- og AGGREGATE-kald inde i intervallet springes over, om værdier på skjulte rækker springes over, og om fejlværdier undertrykkes frem for at blive propageret. HotXLS bevæbner den delte skjult-række-port for optionskoderne 2, 3, 6 og 7, og undertrykker fejlværdier for optionskoderne 4 til 7. Funktionsnummer-argumentet vælger derefter aggregeringen netop som SUBTOTAL gør, inklusive rutingen af varians, standardafvigelse og produkt gennem deres egne reducere. Ét dokumenteret hul forbliver, og det er bedre nævnt her end opdaget i produktion: ignorer-indlejret-SUBTOTAL-semantikken forbundet med de lave optionskoder er ikke implementeret i HotXLS. At detektere en indlejret SUBTOTAL inde i et refereret interval kræver at markere evaluatorens rekursionstilstand, så en indre aggregering kan annoncere sig selv til den ydre, hvilket er en større ændring end skjult-række-porten. I praksis er eksponeringen lille, fordi rigtige arbejdsbøger næsten altid placerer SUBTOTAL-formler uden for de intervaller, andre SUBTOTAL-formler aggregerer over. Hvis din generator bygger overlappende aggregeringsintervaller, så stol ikke på de lave optionskoder til at deduplikere dem
Aritetsvagten der blev leveret sammen med den
Version 2.197.0 lukkede også et valideringshul i den samme dispatcher, og designbegrundelsen er den samme, der motiverede scratch-feltet: put tjekket der, hvor det kan skrives én gang. Omkring 280 indbyggede funktionskroppe verificerede hver deres eget argumentantal mod Item.ChildCount, hvilket ikke efterlod nogen konsistent grænse for tilfældet med for mange argumenter. Et kald som =SIN(1,2) nåede en funktionskrop, der undersøgte sit første argument, ignorerede overskuddet, og returnerede et plausibelt tal, hvor Excel returnerer #VALUE!. HotXLS gemte allerede den deklarerede aritet af hver indbygget funktion i sit funktionsregister, eksponeret som THashFunc.ArgsCnt med -1 der markerer en variadisk funktion såsom SUM, IF eller CONCAT. Version 2.197.0 videresendte det gennem en ny TXLSFormula.FuncArgsCntByPtg-egenskab og tilføjede én port øverst i GetValueItemFunc, hoveddispatcheren
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;Vagten afviser for mange argumenter og siger bevidst intet om for få. At udelade et efterfølgende valgfrit argument er lovligt i Excel for VLOOKUP, SUBSTITUTE og en lang liste af andre, så et symmetrisk tjek ville have brækket korrekte formler for at fange forkerte. Ukendte identifikatorer rapporteres som variadiske og springer porten helt over, hvilket er hvad der holder brugerdefinerede funktioner ude af dens vej; hvis du registrerer dine egne funktioner, er opførslen beskrevet i guiden til formelmotoren og brugerdefinerede funktioner upåvirket. At centralisere for-få-tilfældet er en separat opgave, fordi hver af de 280 kroppe har sin egen fejlkode-semantik, og de skal gennemgås én ad gangen frem for at blive antaget
Beregningsmotoren beskrevet her, begge arbejdsbogsfacader, og AutoFilter- og rækkesynlighed-API'erne der fodrer den, er del af HotXLS Delphi-regnearkskomponenten, som leveres med fuld kildekode til Delphi og C++Builder og ikke kræver nogen Excel-installation på den maskine, der kører den