Ako SUBTOTAL(109, ...) i SUBTOTAL(9, ...) vraćaju isti broj na radnoj bilježnici koja sadrži skrivene retke, jedan od njih dvaju je pogrešan. HotXLS, nativna Excel komponenta za tablice za Delphi i C++Builder, ponašala se upravo tako do verzije 2.197.0, jer njezin proračunski motor nije imao način pitati list ima li dani redak skriven
Simptom rijetko stiže kao prijava buga o kôdovima formula. Stiže kao neusklađenost: batch posao na poslužitelju izračuna zbroj, korisnik otvori istu datoteku u Excelu s primijenjenim filtrom, a dva broja se razlikuju za onoliko koliko su filtrirani retci sumirali. Nitko ne sumnja na funkciju agregacije, jer je niz formule u ćeliji identičan na oba mjesta. Razlika je u potpunosti u onome što je evaluatoru bilo dopušteno vidjeti
Zašto SUBTOTAL 109 uključuje skrivene retke?
Zato što u većini dizajna motora sloj koji evaluira formulu nikad ne sazna za vidljivost retka. HotXLS je bio udžbenički slučaj: proračunski motor u lxCalc.pas dohvaćao je vrijednosti ćelija kroz jedan povratni poziv TXLSGetValue koji odgovara vrijednošću za trojku (list, redak, stupac) i ništa drugo. Vidljivost je atribut prikaza pohranjen u zapisu retka, a nijedan dio tog zapisa nije putovao niz lanac poziva. Motor je stoga imao jedan put agregacije, i obje polovice tablice brojeva funkcija SUBTOTAL razrješavale su se na njega. To nije klasa greške razine zaokruživanja: to je cijeli razlog zašto druga polovica tablice postoji. ECMA-376 dio 1, objavljen kao ISO/IEC 29500-1, definira SUBTOTAL u svojim definicijama funkcija formula (§18.17.7) s prvim argumentom koji bira i unutarnju agregaciju i politiku skrivenih redaka. Kôdovi 1 do 11 mapiraju se na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR i VARP dok uključuju vrijednosti na ručno skrivenim retcima. Kôdovi 101 do 111 biraju istih jedanaest agregacija i isključuju ih. Korisnik koji upiše 109 umjesto 9 daje namjernu izjavu o skrivenim podacima, a motor koji tiho sruši tu razliku tiho poništava tu izjavu
Na što se brojevi funkcija mapiraju unutar motora
HotXLS razrješava prvi argument SUBTOTAL-a u CalcSubtotalFunc, koji normalizira kôdove 101 do 111 dolje na iste unutarnje identifikatore funkcija kao kôdovi 1 do 11, a zatim raspoređuje na samu agregaciju. Većina obitelji teče kroz inkrementalni akumulator ExcelSum, onaj koji obrađuje SUM, COUNT, COUNTA, MIN, MAX i AVERAGE. Pet od njih ne mogu: STDEV, VAR, STDEVP, VARP i PRODUCT trebaju prolaz zatvorenog oblika preko podataka, pa CalcSubtotalFunc usmjerava unutarnje kôdove 12, 46, 193, 194 i 183 u zaseban reduktor, SubtotalReduceVariance. Ta podjela je prva stvar koju vrijedi mapirati prije dodirivanja ičega, jer dva neovisna puta agregacije znače dvije neovisne petlje prolaska kroz ćelije, a popravak primijenjen samo na jedan od njih proizvodi najgori mogući ishod: SUBTOTAL(109, ...) poštuje filtar dok SUBTOTAL(107, ...) na istom rasponu ne poštuje. Brojanje petlji u HotXLS-u otkrilo je šest njih kad je AGGREGATE uključen, raspoređenih po evaluaciji raspona, običnom prikupljanju raspona i tri zasebna reduktora
Zašto privremeno polje umjesto šest novih potpisa?
Zato što provlačenje novog parametra kroz šest funkcija prolaska kroz ćelije, plus sve što ih poziva, je široka promjena vrućeg puta kôda radi jednog boola. HotXLS je već imao presedan za alternativu: prolazno polje na kalkulatoru, u istom duhu kao privremeno polje koje GetRangeInfo koristi za bilježenje kad se 3D referenca razriješila u vanjsku radnu bilježnicu. Verzija 2.197.0 dodala je drugo. Motor je dobio tip povratnog poziva, TXLSIsRowHidden, deklariran kao funkcija (SheetIndex, row) koja vraća Boolean, pohranjen u FIsRowHidden, plus prolaznu zastavicu FIgnoreHiddenRows. Zastavica se naoruža na ulazu u CalcSubtotalFunc kad kôd funkcije padne u raspon 101 do 111, i na ulazu u CalcAggregateFunc za kôdove opcija AGGREGATE koji biraju isključivanje skrivenih redaka. Svaka petlja prolaska kroz ćelije zatim provjerava tu zastavicu i preskače jedan redak kad je postavljena, dodajući po jedan redak svakoj
// 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;Dvije pojedinosti u kôdu naoružavanja nose ispravnost cijele sheme. Zastavica se sprema i vraća umjesto da se jednostavno postavi i obriše, jer argument SUBTOTAL-a može sadržavati izraz koji pokreće vlastitu evaluaciju dok je vanjska agregacija još na stogu, a taj ugniježđeni posao ne smije naslijediti niti uništiti vanjska vrata. A vraćanje sjedi u bloku finally, jer CalcSubtotalFunc ima nekoliko ranih izlaza za kôdove pogrešaka; zastavica ostavljena naoružanom nakon povratka pogreške tiho bi pokvarila sljedeću nepovezanu formulu u redoslijedu ponovnog izračuna
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;Provjera Assigned ono je što drži promjenu kompatibilnom. HotXLS je proširio konstruktor TXLSCalculator trećim parametrom koji prema zadanom iznosi nil, pa se svaki kôd koji gradi TXLSCalculator starim pozivom s dva argumenta i dalje kompajlira i i dalje dobiva naslijeđeno ponašanje uključivanja skrivenih. Ništa se u postojećem API-ju nije promijenilo po obliku
Odakle zapravo dolazi bit skrivenog retka?
Od lista, kroz dva različita izvora, jer HotXLS nosi dva motora radne bilježnice. Naslijeđena BIFF strana odgovara iz TXLSRowInfoList.GetHidden, dosegnuta kroz TXLSWorkbook.GetRowHidden. OOXML strana odgovara iz TXLSXWorksheet.GetRowHidden, dosegnuta kroz TXLSXWorkbook.GetCalcRowHidden. Oba su ožičena u kalkulator pri konstrukciji, uz povratni poziv vrijednosti ćelije koji odražavaju. Konvencije redaka su mjesto gdje ova vrsta mosta obično krene po zlu, pa ih vrijedi izričito navesti. Kalkulator predaje povratnom pozivu redak s bazom 0, podudarajući se s koordinatama koje TXLSGetValue već koristi. XLSX list ključa svoju mapu skrivenih redaka po broju retka s bazom 1, točno kako Excel numerira retke, što je također ono što izlaže javno svojstvo RowHidden[ARow]. XLSX most stoga dodaje jedan prije pretrage, a BIFF most ne, jer je TXLSRowInfoList već s bazom 0. Oba mosta tretiraju indeks lista ili retka izvan valjanog raspona kao vidljiv, pa upit izvan granica degradira na stari odgovor uključivanja skrivenih umjesto da ispusti podatke
Što se mijenja za filtrirane radne bilježnice
Ovo je slučaj koji generira prijave podrške. Primjena AutoFiltera u HotXLS-u kroz ApplyAutoFilter evaluira kriterije stupca i skriva svaki redak podataka koji se ne podudara, što je upravo ono što Excel radi kad korisnik klikne padajući izbornik filtra. Prije v2.197.0 ti skriveni retci bili su nevidljivi korisniku, a potpuno vidljivi proračunskom motoru, pa je SUBTOTAL(109, ...) na poslužiteljskoj strani prijavljivao nefiltrirani zbroj. Sad isti poziv prijavljuje filtrirani
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;Ručno skrivanje radi na isti način, jer je RowHidden[ARow] := True isto stanje koje filtar piše. Ta ekvivalencija je namjerna u Excelu i sad vrijedi i u HotXLS-u. Jedna posljedica zaslužuje bilješku u bilo kojoj dokumentaciji koja se isporučuje s vašim generiranim radnim bilježnicama: zbroj izračunat s kôdom 109 broj je ovisan o prikazu, pa ga primatelj koji očisti filtar mijenja. Kad izvještaj mora navesti fiksnu brojku bez obzira što čitatelj radi s prikazom, kôd 9 je ispravan izbor i uvijek je bio. Filtri, validacija i tablice pokriveni su zajedno u članku o validaciji podataka, AutoFilteru i tablicama. Budući da skrivanje redaka ne dira nijednu formulu, ono također samo od sebe ne prlja graf ovisnosti, što vrijedi znati ako se oslanjate na inkrementalno ponovno računanje preko prljavog podgrafa da velike radne bilježnice drži odzivnima
Kôdovi opcija AGGREGATE i jedno ograničenje koje je još otvoreno
AGGREGATE je SUBTOTAL s drugim argumentom politike, a HotXLS ga obrađuje u CalcAggregateFunc. Argument opcije kodira neovisne prekidače: preskaču li se ugniježđeni pozivi SUBTOTAL i AGGREGATE unutar raspona, preskaču li se vrijednosti na skrivenim retcima, i suzbijaju li se vrijednosti pogrešaka umjesto da se propagiraju. HotXLS naoružava zajednička vrata skrivenog retka za kôdove opcija 2, 3, 6 i 7, i suzbija vrijednosti pogrešaka za kôdove opcija 4 do 7. Argument broja funkcije zatim bira agregaciju točno kako to radi SUBTOTAL, uključujući usmjeravanje varijance, standardne devijacije i produkta kroz njihove vlastite reduktore. Jedan dokumentiran nedostatak ostaje, i bolje ga je navesti ovdje nego otkriti u produkciji: semantika ignoriraj-ugniježđeni-SUBTOTAL povezana s niskim kôdovima opcija nije implementirana u HotXLS-u. Otkrivanje ugniježđenog SUBTOTAL-a unutar referenciranog raspona zahtijeva označavanje stanja rekurzije evaluatora tako da unutarnja agregacija može najaviti sebe vanjskoj, što je veća promjena od vrata skrivenog retka. U praksi je izloženost mala, jer stvarne radne bilježnice gotovo uvijek smještaju SUBTOTAL formule izvan raspona koje agregiraju druge SUBTOTAL formule. Ako vaš generator gradi preklapajuće raspone agregacije, ne oslanjajte se na niske kôdove opcija da ih deduplicira
Zaštita aritete koja se isporučila uz to
Verzija 2.197.0 zatvorila je i prazninu validacije u istom raspoređivaču, a razlog dizajna je isti onaj koji je motivirao prolazno polje: staviti provjeru gdje se može napisati jednom. Otprilike 280 ugrađenih tijela funkcija svako je provjeravalo vlastiti broj argumenata prema Item.ChildCount, što nije ostavljalo dosljednu granicu za slučaj previše argumenata. Poziv poput =SIN(1,2) dosegnuo je tijelo funkcije koje je pregledalo svoj prvi argument, zanemarilo višak, i vratilo vjerodostojan broj gdje Excel vraća #VALUE!. HotXLS je već pohranjivao deklariranu aritetu svake ugrađene funkcije u svom registru funkcija, izloženu kao THashFunc.ArgsCnt s -1 koji označava varijadičnu funkciju poput SUM, IF ili CONCAT. Verzija 2.197.0 proslijedila je to kroz novo svojstvo TXLSFormula.FuncArgsCntByPtg i dodala jedna vrata na vrhu GetValueItemFunc, glavnog raspoređivača
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;Vrata odbacuju previše argumenata i namjerno ne govore ništa o premalo argumenata. Izostavljanje krajnjeg opcionalnog argumenta legalno je u Excelu za VLOOKUP, SUBSTITUTE i dugi popis drugih, pa bi simetrična provjera pokvarila ispravne formule kako bi uhvatila neispravne. Nepoznati identifikatori prijavljuju se kao varijadični i preskaču vrata u potpunosti, što je ono što drži korisnički definirane funkcije izvan njihova puta; ako registrirate vlastite funkcije, ponašanje opisano u vodiču o proračunskom motoru i prilagođenim funkcijama nije pogođeno. Centraliziranje slučaja premalo je zaseban posao, jer svako od tih 280 tijela ima vlastitu semantiku kôda pogreške i moraju se pregledati jedno po jedno, a ne pretpostaviti
Proračunski motor opisan ovdje, obje fasade radne bilježnice i API-ji AutoFiltera i vidljivosti redaka koji ga hrane dio su HotXLS Delphi komponente za tablice, koja se isporučuje s punim izvornim kôdom za Delphi i C++Builder i ne zahtijeva instalaciju Excela na stroju koji je pokreće