Če SUBTOTAL(109, ...) in SUBTOTAL(9, ...) na delovnem zvezku s skritimi vrsticami vrneta isto število, je eden od njiju napačen. HotXLS, izvorna komponenta preglednice Excel za Delphi in C++Builder, se je natanko tako obnašal do različice 2.197.0, ker njegov računski pogon ni imel načina, da bi delovni list vprašal, ali je dana vrstica skrita
Simptom redko prispe kot poročilo o hrošču glede kod formul. Prispe kot neujemanje: paketno opravilo na strežniku izračuna vsoto, uporabnik odpre isto datoteko v Excelu z uporabljenim filtrom, obe števili pa se razlikujeta za karkoli, kar so izfiltrirane vrstice slučajno vsote. Nihče ne posumi na funkcijo agregacije, ker je niz formule v celici na obeh mestih identičen. Razlika je v celoti v tem, kaj je bilo ocenjevalniku dovoljeno videti
Zakaj SUBTOTAL 109 vključi skrite vrstice?
Ker se v večini zasnov pogonov sloj, ki oceni formulo, nikoli ne nauči o vidnosti vrstice. HotXLS je bil učbeniški primer: računski pogon v lxCalc.pas je dosegal vrednosti celic prek enega samega povratnega klica TXLSGetValue, ki odgovori z vrednostjo za trojico (list, vrstica, stolpec) in ničesar drugega. Vidnost je predstavitveni atribut, shranjen v zapisu vrstice, noben del tega zapisa pa ni potoval navzdol po verigi klicev. Pogon je torej imel eno pot agregacije, obe polovici tabele številk funkcij SUBTOTAL pa sta se razreševali vanjo. To ni razred napake zaokroževanja: to je cel razlog, zakaj druga polovica tabele sploh obstaja. ECMA-376 Part 1, objavljen kot ISO/IEC 29500-1, definira SUBTOTAL v svojih definicijah funkcij formul (§18.17.7) s prvim argumentom, ki izbere tako notranjo agregacijo kot politiko skritih vrstic. Kode 1 do 11 se preslikajo v AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR in VARP, medtem ko vključujejo vrednosti na ročno skritih vrsticah. Kode 101 do 111 izberejo istih enajst agregacij in jih izključijo. Uporabnik, ki natipka 109 namesto 9, poda namerno izjavo o skritih podatkih, pogon, ki razliko zlije skupaj, pa to izjavo tiho prekliče
Na kaj se številke funkcij preslikajo znotraj pogona
HotXLS razreši prvi argument SUBTOTAL v CalcSubtotalFunc, ki kode 101 do 111 normalizira navzdol na iste notranje identifikatorje funkcij kot kode 1 do 11 in nato razpošlje po sami agregaciji. Večina družine teče skozi prirastni akumulator ExcelSum, tisti, ki obravnava SUM, COUNT, COUNTA, MIN, MAX in AVERAGE. Pet od njih ne more: STDEV, VAR, STDEVP, VARP in PRODUCT potrebujejo prehod v zaprti obliki čez podatke, zato CalcSubtotalFunc notranje kode 12, 46, 193, 194 in 183 usmeri v ločen reduktor, SubtotalReduceVariance. Ta razcep je prva stvar, ki jo je vredno preslikati, preden se česarkoli dotaknete, ker dve neodvisni poti agregacije pomenita dve neodvisni zanki prehoda po celicah, popravek, uporabljen le na eni od njiju, pa proizvede najslabši mogoč izid: SUBTOTAL(109, ...) spoštuje filter, medtem ko SUBTOTAL(107, ...) na istem obsegu tega ne stori. Štetje zank v HotXLS je, ko je bil vključen AGGREGATE, pokazalo šest, razpršenih po oceni obsega, navadnem zbiranju obsega in treh ločenih reduktorjih
Zakaj polje po meri namesto šest novih podpisov?
Ker je preigravanje novega parametra skozi šest funkcij prehoda po celicah, plus vse, kar jih kliče, obsežna sprememba na vroči poti kode zaradi enega booleana. HotXLS je že imel precedens za alternativo: prehodno polje na kalkulatorju, v istem duhu kot polje po meri, ki ga GetRangeInfo uporablja za beleženje, kdaj se je 3D referenca razrešila v zunanji delovni zvezek. Različica 2.197.0 je dodala drugega. Pogon je pridobil tip povratnega klica, TXLSIsRowHidden, deklariran kot funkcija (SheetIndex, row), ki vrne Boolean, shranjena v FIsRowHidden, plus prehodna zastavica FIgnoreHiddenRows. Zastavica je oborožena ob vstopu v CalcSubtotalFunc, kadar koda funkcije pade med 101 in 111, in ob vstopu v CalcAggregateFunc za tiste kode možnosti AGGREGATE, ki izberejo izključitev skritih vrstic. Vsaka zanka prehoda po celicah jo nato pregleda in preskoči eno vrstico, kadar je nastavljena, s po eno dodano vrstico
// 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;Dve podrobnosti v kodi oboroževanja nosita pravilnost celotne sheme. Zastavica je shranjena in obnovljena namesto preprosto nastavljena in počiščena, ker lahko argument SUBTOTAL vsebuje izraz, ki izvede lastno ocenjevanje, medtem ko je zunanja agregacija še vedno na skladu, to gnezdeno delo pa ne sme podedovati ali uničiti zunanjega vratca. Obnova pa živi v bloku finally, ker ima CalcSubtotalFunc več zgodnjih izhodov za kode napak; zastavica, puščena oboroženo po vrnitvi z napako, bi tiho pokvarila naslednjo nepovezano formulo v vrstnem redu preračunavanja
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;Test Assigned je tisto, kar spremembo drži združljivo. HotXLS je konstruktor kalkulatorja razširil s tretjim parametrom, ki privzeto je nil, tako da se vsaka koda, ki gradi TXLSCalculator s starim dvoargumentnim klicem, še vedno prevede in še vedno dobi podedovano vedenje vključi-skrito. Nič glede oblike obstoječega API-ja se ni spremenilo
Od kod dejansko prihaja bit skrite vrstice?
Iz delovnega lista, prek dveh različnih virov, ker HotXLS nosi dva pogona delovnega zvezka. Podedovana stran BIFF odgovori iz TXLSRowInfoList.GetHidden, dosežena prek TXLSWorkbook.GetRowHidden. Stran OOXML odgovori iz TXLSXWorksheet.GetRowHidden, dosežena prek TXLSXWorkbook.GetCalcRowHidden. Obe sta ožičeni v kalkulator ob konstrukciji, zraven povratnega klica vrednosti celice, ki ga zrcalita. Konvencije vrstic so mesto, kjer ta vrsta mostu običajno zaide narobe, zato jih je vredno izrecno navesti. Kalkulator povratnemu klicu preda vrstico, oštevilčeno od 0, kar se ujema s koordinatami, ki jih TXLSGetValue že uporablja. Delovni list XLSX svojo mapo skritih vrstic ključi po številki vrstice, oštevilčeni od 1, natanko tako, kot Excel oštevilči vrstice, kar je tudi tisto, kar izpostavi javna lastnost RowHidden[ARow]. Most XLSX zato pred iskanjem doda ena, most BIFF pa tega ne stori, ker je TXLSRowInfoList že oštevilčen od 0. Oba mostova obravnavata indeks lista ali vrstico zunaj veljavnega razpona kot vidna, tako da poizvedba zunaj meja degradira v staro odgovor vključi-skrito namesto da bi izgubila podatke
Kaj se spremeni za filtrirane delovne zvezke
To je primer, ki generira podporne prijave. Uporaba samodejnega filtra v HotXLS prek ApplyAutoFilter oceni merila stolpca in skrije vsako podatkovno vrstico, ki se ne ujema, kar je natanko tisto, kar Excel stori, ko uporabnik klikne spustni seznam filtra. Pred v2.197.0 so bile te skrite vrstice nevidne uporabniku in popolnoma vidne računskemu pogonu, tako da je strežniški SUBTOTAL(109, ...) poročal nefiltrirano vsoto. Zdaj isti klic poroča filtrirano
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;Ročno skrivanje deluje enako, ker je RowHidden[ARow] := True isto stanje, ki ga zapiše filter. Ta enakovrednost je v Excelu namerna in zdaj velja tudi v HotXLS. Ena posledica si zasluži opombo v katerikoli dokumentaciji, ki jo izdate z generiranimi delovnimi zvezki: vsota, izračunana s kodo 109, je od pogleda odvisno število, tako da jo prejemnik, ki počisti filter, spremeni. Kadar mora poročilo navesti fiksno številko ne glede na to, kaj bralec stori s pogledom, je koda 9 pravilna izbira in je to vedno bila. Filtri, preverjanje veljavnosti in tabele so skupaj obravnavani v članku o preverjanju veljavnosti podatkov, AutoFilter in tabelah. Ker skrivanje vrstic ne dotika nobene formule, tudi samo po sebi ne umaže grafa odvisnosti, kar je vredno vedeti, če se za odzivnost velikih delovnih zvezkov zanašate na prirastno preračunavanje čez umazan podgraf
Kode možnosti AGGREGATE in ena meja, ki je še vedno odprta
AGGREGATE je SUBTOTAL z drugim argumentom politike, HotXLS pa ga obravnava v CalcAggregateFunc. Argument možnosti kodira neodvisna stikala: ali so gnezdeni klici SUBTOTAL in AGGREGATE znotraj obsega preskočeni, ali so vrednosti na skritih vrsticah preskočene, in ali so vrednosti napak zatrte namesto razširjene naprej. HotXLS oboroži skupno vratce skrite vrstice za kode možnosti 2, 3, 6 in 7 ter zatre vrednosti napak za kode možnosti 4 do 7. Argument številke funkcije nato izbere agregacijo natanko tako, kot to počne SUBTOTAL, vključno z usmerjanjem variance, standardnega odklona in produkta skozi njihove lastne reduktorje. Ena dokumentirana vrzel ostaja, bolje pa jo je navesti tukaj kot odkriti v produkciji: semantika preskoči-gnezden-SUBTOTAL, povezana z nizkimi kodami možnosti, v HotXLS ni implementirana. Zaznavanje gnezdenega SUBTOTAL znotraj referenciranega obsega zahteva označevanje stanja rekurzije ocenjevalnika, tako da lahko notranja agregacija sama sebe napove zunanji, kar je večja sprememba od vratca skrite vrstice. V praksi je izpostavljenost majhna, ker resnični delovni zvezki skoraj vedno postavijo formule SUBTOTAL zunaj obsegov, po katerih agregirajo druge formule SUBTOTAL. Če vaš generator dejansko gradi prekrivajoče se obsege agregacije, se ne zanašajte na nizke kode možnosti, da bi jih razdvojile
Varovalo aritete, ki je bilo izdano zraven
Različica 2.197.0 je zaprla tudi vrzel preverjanja v istem dispečerju, razlog zasnove pa je isti, ki je motiviral polje po meri: preverjanje postaviti tja, kjer je lahko napisano enkrat. Približno 280 teles vgrajenih funkcij je vsako samo preverjalo svoje število argumentov proti Item.ChildCount, kar ni pustilo dosledne meje za primer preveč argumentov. Klic, kot je =SIN(1,2), je dosegel telo funkcije, ki je pregledalo svoj prvi argument, prezrlo presežek in vrnilo verjetno število, kjer Excel vrne #VALUE!. HotXLS je že shranjeval deklarirano ariteto vsake vgrajene funkcije v svojem registru funkcij, izpostavljeno kot THashFunc.ArgsCnt z -1, ki označuje spremenljivo funkcijo, kot je SUM, IF ali CONCAT. Različica 2.197.0 je to posredovala prek nove lastnosti TXLSFormula.FuncArgsCntByPtg in dodala eno vratce na vrhu GetValueItemFunc, glavnega dispečerja
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;Varovalo zavrne preveč argumentov in namerno ne pove ničesar o premalo. Izpustitev sledečega izbirnega argumenta je v Excelu zakonita za VLOOKUP, SUBSTITUTE in dolg seznam drugih, tako da bi simetrično preverjanje pokvarilo pravilne formule, da bi ujelo nepravilne. Neznani identifikatorji se poročajo kot spremenljivi in v celoti preskočijo vratce, kar je tisto, kar uporabniško definirane funkcije drži stran od njega; če registrirate lastne funkcije, vedenje, opisano v vodniku po pogonu formul in funkcijah po meri, ni prizadeto. Centralizacija primera premalo je ločena naloga, ker ima vsako od teh 280 teles svojo lastno semantiko kod napak, pregledana pa morajo biti eno naenkrat, ne pa predpostavljena
Tukaj opisani računski pogon, obe fasadi delovnega zvezka ter API-ji AutoFilter in vidnosti vrstic, ki ga hranijo, so del HotXLS Delphi spreadsheet component, ki je izdan s polno izvorno kodo za Delphi in C++Builder ter ne zahteva namestitve Excela na stroju, kjer teče