Hvis SUBTOTAL(109, ...) og SUBTOTAL(9, ...) returnerer samme tall på en arbeidsbok som inneholder skjulte rader, er én av de to feil. HotXLS, den native Excel-regnearkkomponenten for Delphi og C++Builder, oppførte seg akkurat slik frem til versjon 2.197.0, fordi beregningsmotoren dens ikke hadde noen måte å spørre et regneark om en gitt rad var skjult
Symptomet ankommer sjelden som en feilrapport om formelkoder. Det ankommer som et avvik: en batch-jobb på serveren beregner en totalsum, en bruker åpner samme fil i Excel med et filter påført, og de to tallene skiller seg med det de filtrerte-bort radene tilfeldigvis summerte til. Ingen mistenker aggregeringsfunksjonen, fordi formelstrengen i cellen er identisk begge steder. Forskjellen ligger utelukkende i hva evaluatoren fikk lov til å se
Hvorfor inkluderer SUBTOTAL 109 skjulte rader?
Fordi laget som evaluerer en formel i de fleste motordesign aldri lærer om radsynlighet. HotXLS var et lærebokeksempel: beregningsmotoren i lxCalc.pas nådde celleverdier gjennom én enkelt TXLSGetValue-callback som svarer med en verdi for en (ark, rad, kolonne)-triplett og ingenting annet. Synlighet er et presentasjonsattributt lagret på radposten, og ingen del av den posten reiste ned gjennom kallkjeden. Motoren hadde derfor én aggregeringsvei, og begge halvdelene av SUBTOTAL-funksjonsnummer-tabellen løste seg opp til den. Det er ikke en avrundingsfeil-klasse defekt: det er hele grunnen til at den andre halvdelen av tabellen finnes. ECMA-376 Part 1, utgitt som ISO/IEC 29500-1, definerer SUBTOTAL i sine formelfunksjonsdefinisjoner (§18.17.7) med et første argument som velger både den indre aggregeringen og skjult-rad-policyen. Koder 1 til 11 mapper til AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR og VARP mens de inkluderer verdier på manuelt skjulte rader. Koder 101 til 111 velger de samme elleve aggregeringene og ekskluderer dem. En bruker som skriver 109 i stedet for 9 gir en bevisst uttalelse om skjulte data, og en motor som kollapser skillet overstyrer stille den uttalelsen
Hva funksjonsnumrene mapper til inne i motoren
HotXLS løser opp SUBTOTAL-førsteargumentet i CalcSubtotalFunc, som normaliserer koder 101 til 111 ned til de samme indre funksjonsidentifikatorene som koder 1 til 11 og deretter dispatcher på selve aggregeringen. Det meste av familien flyter gjennom den inkrementelle ExcelSum-akkumulatoren, den som håndterer SUM, COUNT, COUNTA, MIN, MAX, og AVERAGE. Fem av dem kan ikke: STDEV, VAR, STDEVP, VARP, og PRODUCT trenger en lukket-form gjennomgang over dataene, så CalcSubtotalFunc ruter indre koder 12, 46, 193, 194, og 183 til en separat reduserer, SubtotalReduceVariance. Det skillet er det første som er verdt å kartlegge før du rører noe som helst, fordi to uavhengige aggregeringsveier betyr to uavhengige cellegjennomgang-løkker, og en fiks anvendt på bare én av dem produserer det verst tenkelige utfallet: SUBTOTAL(109, ...) respekterer filteret mens SUBTOTAL(107, ...) på samme rekkevidde ikke gjør det. Å telle løkkene i HotXLS avdekket seks av dem når AGGREGATE var inkludert, spredt over rekkevidde-evaluering, vanlig rekkevidde-innsamling, og tre separate reduserere
Hvorfor et scratch-felt i stedet for seks nye signaturer?
Fordi å tre et nytt parameter gjennom seks cellegjennomgang-funksjoner, pluss alt som kaller dem, er en bred endring på en het kodevei for skyld av én boolean. HotXLS hadde allerede en presedens for alternativet: et transient felt på kalkulatoren, i samme ånd som scratch-feltet GetRangeInfo bruker for å registrere når en 3D-referanse løste seg opp inn i en ekstern arbeidsbok. Versjon 2.197.0 la til en til. Motoren fikk en callback-type, TXLSIsRowHidden, deklarert som en funksjon av (SheetIndex, row) som returnerer Boolean, lagret i FIsRowHidden, pluss et transient FIgnoreHiddenRows-flagg. Flagget armeres ved inngangen til CalcSubtotalFunc når funksjonskoden faller i 101 til 111, og ved inngangen til CalcAggregateFunc for AGGREGATE-alternativkodene som velger skjult-rad-ekskludering. Hver cellegjennomgang-løkke inspiserer den deretter og hopper over én rad når den er satt, med én linje lagt til 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 armeringskoden bærer korrektheten til hele ordningen. Flagget lagres og gjenopprettes fremfor bare å settes og tømmes, fordi et SUBTOTAL-argument kan inneholde et uttrykk som kjører sin egen evaluering mens den ytre aggregeringen fortsatt er på stakken, og det nøstede arbeidet må verken arve eller ødelegge den ytre porten. Og gjenopprettingen bor i en finally-blokk, fordi CalcSubtotalFunc har flere tidlige utganger for feilkoder; et flagg latt armert etter en feil-retur ville stille korrumpere neste urelaterte formel i rekalkuleringsrekkefø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 som holder endringen kompatibel. HotXLS utvidet kalkulator-konstruktøren med en tredje parameter som som standard er nil, så all kode som bygger en TXLSCalculator med det gamle to-argument-kallet kompilerer fortsatt og får fortsatt den gamle inkluder-skjulte-atferden. Ingenting ved den eksisterende API-en endret form
Hvor kommer skjult-rad-biten faktisk fra?
Fra regnearket, gjennom to forskjellige kilder, fordi HotXLS bærer to arbeidsbokmotorer. Den eldre BIFF-siden svarer fra TXLSRowInfoList.GetHidden, nådd gjennom TXLSWorkbook.GetRowHidden. OOXML-siden svarer fra TXLSXWorksheet.GetRowHidden, nådd gjennom TXLSXWorkbook.GetCalcRowHidden. Begge er koblet inn i kalkulatoren ved konstruksjonstidspunktet, ved siden av celleverdi-callbacken de speiler. Radkonvensjonene er der denne typen bro normalt går galt, så de er verdt å si eksplisitt. Kalkulatoren gir callbacken en 0-basert rad, som samsvarer med koordinatene TXLSGetValue allerede bruker. XLSX-regnearket nøkler rad-skjult-kartet sitt med 1-basert radnummer, akkurat slik Excel nummererer rader, som også er det den offentlige RowHidden[ARow]-egenskapen eksponerer. XLSX-broen legger derfor til én før oppslaget, og BIFF-broen gjør ikke det, fordi TXLSRowInfoList allerede er 0-basert. Begge broene behandler en ark-indeks eller rad utenfor gyldig område som synlig, så en spørring utenfor grensene degraderer til det gamle inkluder-skjulte-svaret fremfor å miste data
Hva endrer seg for filtrerte arbeidsbøker
Dette er tilfellet som genererer support-sakene. Å påføre et AutoFilter i HotXLS gjennom ApplyAutoFilter evaluerer kolonnekriteriene og skjuler hver dataraden som ikke matcher, som er akkurat det Excel gjør når en bruker klikker en filter-nedtrekksmeny. Før v2.197.0 var de skjulte radene usynlige for brukeren og fullt synlige for beregningsmotoren, så et server-side SUBTOTAL(109, ...) rapporterte den ufiltrerte totalsummen. Nå rapporterer samme kall den filtrerte
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;Manuell skjuling fungerer på samme måte, siden RowHidden[ARow] := True er samme tilstand filteret skriver. Den ekvivalensen er bevisst i Excel og gjelder nå også i HotXLS. Én konsekvens fortjener en merknad i hvilken som helst dokumentasjon som leveres med dine genererte arbeidsbøker: en totalsum beregnet med kode 109 er et visningsavhengig tall, så en mottaker som fjerner filteret endrer det. Når en rapport må angi en fast figur uansett hva leseren gjør med visningen, er kode 9 det riktige valget og har alltid vært det. Filtre, validering og tabeller dekkes sammen i artikkelen om datavalidering, AutoFilter og tabeller. Fordi å skjule rader ikke rører noen formel, tilsmusser det heller ikke avhengighetsgrafen på egen hånd, noe som er verdt å vite hvis du er avhengig av inkrementell rekalkulering over den tilsmussede undergrafen for å holde store arbeidsbøker responsive
AGGREGATE-alternativkoder og én grense som fortsatt er åpen
AGGREGATE er SUBTOTAL med et andre policy-argument, og HotXLS håndterer det i CalcAggregateFunc. Alternativargumentet koder uavhengige brytere: hvorvidt nøstede SUBTOTAL- og AGGREGATE-kall inne i rekkevidden hoppes over, hvorvidt verdier på skjulte rader hoppes over, og hvorvidt feilverdier undertrykkes fremfor å forplante seg. HotXLS armerer den delte skjult-rad-porten for alternativkoder 2, 3, 6, og 7, og undertrykker feilverdier for alternativkoder 4 til 7. Funksjonsnummer-argumentet velger deretter aggregeringen akkurat slik SUBTOTAL gjør, inkludert rutingen av varians, standardavvik og produkt gjennom sine egne reduserere. Ett dokumentert gap gjenstår, og det er bedre å si det her enn å oppdage det i produksjon: ignorer-nøstet-SUBTOTAL-semantikken assosiert med de lave alternativkodene er ikke implementert i HotXLS. Å oppdage en nøstet SUBTOTAL inne i en referert rekkevidde krever å markere evaluator-rekursjonstilstanden slik at en indre aggregering kan kunngjøre seg selv til den ytre, som er en større endring enn skjult-rad-porten. I praksis er eksponeringen liten, fordi ekte arbeidsbøker nesten alltid plasserer SUBTOTAL-formler utenfor rekkeviddene andre SUBTOTAL-formler aggregerer over. Hvis generatoren din faktisk bygger overlappende aggregeringsrekkevidder, ikke stol på de lave alternativkodene for å deduplisere dem
Aritetsvakten som ble levert sammen med den
Versjon 2.197.0 lukket også et valideringsgap i samme dispatcher, og designgrunnen er den samme som motiverte scratch-feltet: legg sjekken der den kan skrives én gang. Omtrent 280 innebygde funksjonskropper verifiserte hver sitt eget argumentantall mot Item.ChildCount, noe som ikke etterlot noen konsistent grense for tilfellet med for mange argumenter. Et kall som =SIN(1,2) nådde en funksjonskropp som undersøkte sitt første argument, ignorerte overskuddet, og returnerte et plausibelt tall der Excel returnerer #VALUE!. HotXLS lagret allerede den deklarerte ariteten til hver innebygd funksjon i funksjonsregisteret sitt, eksponert som THashFunc.ArgsCnt med -1 som markerer en variadisk funksjon som SUM, IF, eller CONCAT. Versjon 2.197.0 videresendte det gjennom en ny TXLSFormula.FuncArgsCntByPtg-egenskap og la til é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;Vakten avviser for mange argumenter og sier bevisst ingenting om for få. Å utelate et etterfølgende valgfritt argument er lovlig i Excel for VLOOKUP, SUBSTITUTE, og en lang liste av andre, så en symmetrisk sjekk ville ødelagt korrekte formler for å fange feilaktige. Ukjente identifikatorer rapporterer som variadiske og hopper over porten helt, noe som er det som holder brukerdefinerte funksjoner unna dens vei; hvis du registrerer dine egne funksjoner, er atferden beskrevet i veiledningen om formelmotoren og egendefinerte funksjoner upåvirket. Å sentralisere for-få-tilfellet er en separat jobb, fordi hver av de 280 kroppene har sin egen feilkodesemantikk og de må gjennomgås én om gangen fremfor å antas
Beregningsmotoren beskrevet her, begge arbeidsbok-fasadene, og AutoFilter- og radsynlighet-API-ene som mater den er en del av HotXLS Delphi regnearkkomponent, som leveres med full kildekode for Delphi og C++Builder og krever ingen Excel-installasjon på maskinen som kjører den