Ako SUBTOTAL(109, ...) i SUBTOTAL(9, ...) vraćaju isti broj na workbooku koji sadrži skrivene redove, jedan od to dvoje je pogrešan. HotXLS, nativna Excel spreadsheet komponenta za Delphi i C++Builder, ponašao se tačno tako sve do verzije 2.197.0, jer njegov kalkulacioni engine nije imao način da pita worksheet da li je dati red skriven
Simptom retko stiže kao prijava baga o kodovima formula. Stiže kao neusklađenost: batch posao na serveru izračuna ukupan iznos, korisnik otvori isti fajl u Excelu sa primenjenim filterom, a ta dva broja se razlikuju za koliko god su iznosili filtrirani redovi. Niko ne sumnja u funkciju agregacije, jer je string formule u ćeliji identičan na oba mesta. Razlika je u potpunosti u tome šta je evaluatoru bilo dozvoljeno da vidi
Zašto SUBTOTAL 109 uključuje skrivene redove?
Zato što u većini dizajna enginea sloj koji evaluira formulu nikad ne sazna o vidljivosti reda. HotXLS je bio udžbenički primer: kalkulacioni engine u lxCalc.pas je dopirao do vrednosti ćelija kroz jedan callback TXLSGetValue koji odgovara vrednošću za trojku (list, red, kolona) i ništa više. Vidljivost je atribut prezentacije uskladišten na zapisu reda, i nijedan deo tog zapisa nije putovao niz lanac poziva. Engine je zato imao jednu putanju agregacije, i obe polovine tabele brojeva funkcija SUBTOTAL su se razrešavale na nju. To nije klasa defekta tipa greške zaokruživanja: to je ceo razlog zašto druga polovina tabele postoji. ECMA-376 Deo 1, objavljen kao ISO/IEC 29500-1, definiše SUBTOTAL u svojim definicijama funkcija formula (§18.17.7) sa prvim argumentom koji bira i unutrašnju agregaciju i politiku skrivenih redova. Kodovi 1 do 11 mapiraju na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, i VARP dok uključuju vrednosti na ručno skrivenim redovima. Kodovi 101 do 111 biraju istih jedanaest agregacija i isključuju ih. Korisnik koji ukuca 109 umesto 9 daje namernu izjavu o skrivenim podacima, a engine koji urušava tu razliku tiho poništava tu izjavu
Na šta se brojevi funkcija mapiraju unutar enginea
HotXLS razrešava prvi argument SUBTOTAL-a u CalcSubtotalFunc, koja normalizuje kodove 101 do 111 dole na iste identifikatore unutrašnje funkcije kao kodove 1 do 11, a zatim dispečuje na samu agregaciju. Većina te porodice teče kroz inkrementalni akumulator ExcelSum, onaj koji obrađuje SUM, COUNT, COUNTA, MIN, MAX, i AVERAGE. Pet njih ne može: STDEV, VAR, STDEVP, VARP, i PRODUCT trebaju prolaz zatvorenog oblika kroz podatke, pa CalcSubtotalFunc usmerava unutrašnje kodove 12, 46, 193, 194, i 183 ka odvojenom reduceru, SubtotalReduceVariance. Ta podela je prva stvar koju vredi mapirati pre nego što bilo šta dotaknete, jer dve nezavisne putanje agregacije znače dve nezavisne petlje obilaska ćelija, a popravka primenjena samo na jednu od njih proizvodi najgori mogući ishod: SUBTOTAL(109, ...) poštuje filter dok SUBTOTAL(107, ...) na istom opsegu ne poštuje. Brojanje petlji u HotXLS-u je otkrilo šest njih kada je AGGREGATE uključen, raspoređenih preko evaluacije opsega, obične kolekcije opsega, i tri odvojena reducera
Zašto scratch polje umesto šest novih potpisa?
Zato što provlačenje novog parametra kroz šest funkcija obilaska ćelija, plus sve što ih poziva, je široka izmena na vrelom putu koda zbog jednog boolean-a. HotXLS je već imao presedan za alternativu: prolazno polje na kalkulatoru, u istom duhu kao scratch polje koje GetRangeInfo koristi da zabeleži kada se 3D referenca razrešila u eksterni workbook. Verzija 2.197.0 dodala je drugo. Engine je dobio tip callbacka, TXLSIsRowHidden, deklarisan kao funkcija od (SheetIndex, red) koja vraća Boolean, uskladištena u FIsRowHidden, plus prolaznu zastavicu FIgnoreHiddenRows. Zastavica se naoružava na ulazu u CalcSubtotalFunc kada kod funkcije spada u 101 do 111, i na ulazu u CalcAggregateFunc za kodove opcija AGGREGATE koje biraju isključenje skrivenih redova. Svaka petlja obilaska ćelija zatim to proverava i preskače jedan red kada je postavljeno, dodajući po jednu liniju 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;Dva detalja u kodu naoružavanja nose ispravnost cele šeme. Zastavica se čuva i vraća umesto da se prosto postavi i briše, jer argument SUBTOTAL-a može sadržati izraz koji pokreće sopstvenu evaluaciju dok je spoljna agregacija i dalje na steku, i taj ugnežđeni posao ne sme naslediti niti uništiti spoljnu kapiju. A vraćanje živi u finally bloku, jer CalcSubtotalFunc ima nekoliko ranih izlaza za kodove grešaka; zastavica ostavljena naoružanom posle povratka greške bi tiho pokvarila sledeću nepovezanu formulu u redosledu prekalkulacije
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 ono što izmenu čini kompatibilnom. HotXLS je proširio konstruktor kalkulatora trećim parametrom podrazumevanim na nil, pa svaki kod koji gradi TXLSCalculator sa starim pozivom od dva argumenta i dalje kompajlira i i dalje dobija zastarelo ponašanje uključi-skrivene. Ništa u postojećem obliku API-ja se nije promenilo
Odakle zapravo dolazi bit skriven-red?
Sa worksheeta, kroz dva različita izvora, jer HotXLS nosi dva workbook enginea. Zastarela BIFF strana odgovara iz TXLSRowInfoList.GetHidden, dostupnog kroz TXLSWorkbook.GetRowHidden. OOXML strana odgovara iz TXLSXWorksheet.GetRowHidden, dostupnog kroz TXLSXWorkbook.GetCalcRowHidden. Oba su povezana na kalkulator u trenutku konstrukcije, zajedno sa callbackom vrednosti ćelije koji zrcale. Konvencije redova su mesto gde ovakav most obično zapinje, pa ih vredi eksplicitno navesti. Kalkulator predaje callbacku 0-bazirani red, poklapajući se sa koordinatama koje TXLSGetValue već koristi. XLSX worksheet ključa svoju mapu skriven-red po 1-baziranom broju reda, tačno kako Excel numeriše redove, što je takođe ono što javno svojstvo RowHidden[ARow] izlaže. XLSX most zato dodaje jedan pre pretrage, a BIFF most ne, jer je TXLSRowInfoList već 0-baziran. Oba mosta tretiraju indeks lista ili red van validnog opsega kao vidljiv, pa upit van granica degradira u stari odgovor uključi-skrivene umesto da izgubi podatke
Šta se menja za filtrirane workbookove
Ovo je slučaj koji generiše tikete podrške. Primena AutoFilter-a u HotXLS-u kroz ApplyAutoFilter evaluira kriterijume kolone i skriva svaki red podataka koji se ne poklapa, što je tačno ono što Excel radi kada korisnik klikne padajući meni filtera. Pre v2.197.0 ti skriveni redovi su bili nevidljivi korisniku, a potpuno vidljivi kalkulacionom engineu, pa je server-side SUBTOTAL(109, ...) prijavljivao nefiltriranu sumu. Sada isti poziv prijavljuje filtriranu
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, pošto je RowHidden[ARow] := True isto stanje koje filter piše. Ta ekvivalencija je namerna u Excelu i sada važi i u HotXLS-u. Jedna posledica zaslužuje napomenu u bilo kojoj dokumentaciji koja se isporučuje sa vašim generisanim workbookovima: ukupan iznos izračunat sa kodom 109 je broj zavisan od prikaza, pa primalac koji ukloni filter ga menja. Kada izveštaj mora navesti fiksnu cifru bez obzira šta čitalac radi sa prikazom, kod 9 je ispravan izbor i uvek je bio. Filteri, validacija, i tabele su pokriveni zajedno u članku o validaciji podataka, AutoFilter-u, i tabelama. Pošto skrivanje redova ne dodiruje nijednu formulu, takođe samo po sebi ne prlja graf zavisnosti, što vredi znati ako se oslanjate na inkrementalnu prekalkulaciju preko prljavog podgrafa da bi veliki workbookovi ostali responzivni
Kodovi opcija AGGREGATE i jedno ograničenje koje je i dalje otvoreno
AGGREGATE je SUBTOTAL sa drugim argumentom politike, a HotXLS ga obrađuje u CalcAggregateFunc. Argument opcije enkodira nezavisne prekidače: da li se ugnežđeni pozivi SUBTOTAL i AGGREGATE unutar opsega preskaču, da li se preskaču vrednosti na skrivenim redovima, i da li se vrednosti grešaka potiskuju umesto propagacije. HotXLS naoružava zajedničku kapiju skriven-red za kodove opcija 2, 3, 6, i 7, i potiskuje vrednosti grešaka za kodove opcija 4 do 7. Argument broja funkcije zatim bira agregaciju tačno kao što SUBTOTAL radi, uključujući usmeravanje varijanse, standardne devijacije, i proizvoda kroz njihove sopstvene reducere. Jedan dokumentovan propust ostaje, i bolje je da bude naveden ovde nego otkriven u produkciji: semantika ignoriši-ugnežđen-SUBTOTAL povezana sa niskim kodovima opcija nije implementirana u HotXLS-u. Detektovanje ugnežđenog SUBTOTAL-a unutar referenciranog opsega zahteva obeležavanje stanja rekurzije evaluatora tako da unutrašnja agregacija može da se najavi spoljnoj, što je veća izmena od kapije skriven-red. U praksi je izloženost mala, jer stvarni workbookovi gotovo uvek postavljaju formule SUBTOTAL van opsega preko kojih druge formule SUBTOTAL agregiraju. Ako vaš generator zaista gradi preklapajuće opsege agregacije, ne oslanjajte se na niske kodove opcija da ih deduplikuju
Čuvar arnosti koji je isporučen zajedno sa tim
Verzija 2.197.0 je takođe zatvorila propust validacije u istom dispečeru, a razlog dizajna je isti onaj koji je motivisao scratch polje: staviti proveru tamo gde može biti napisana jednom. Otprilike 280 tela ugrađenih funkcija je svako proveravalo sopstveni broj argumenata protiv Item.ChildCount, što nije ostavljalo doslednu granicu za slučaj previše argumenata. Poziv poput =SIN(1,2) stizao je do tela funkcije koje je ispitalo svoj prvi argument, ignorisalo višak, i vratilo verodostojan broj tamo gde Excel vraća #VALUE!. HotXLS je već čuvao deklarisanu arnost svake ugrađene funkcije u svom registru funkcija, izloženu kao THashFunc.ArgsCnt sa -1 koje obeležava varijadičnu funkciju poput SUM, IF, ili CONCAT. Verzija 2.197.0 je to prosledila kroz novo svojstvo TXLSFormula.FuncArgsCntByPtg i dodala jednu kapiju na vrhu GetValueItemFunc, glavnog dispečera
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;Čuvar odbija previše argumenata i namerno ne govori ništa o premalo argumenata. Izostavljanje pratećeg opcionog argumenta je legalno u Excelu za VLOOKUP, SUBSTITUTE, i dugu listu drugih, pa bi simetrična provera pokvarila ispravne formule da bi uhvatila neispravne. Nepoznati identifikatori se prijavljuju kao varijadični i u potpunosti preskaču kapiju, što drži korisnički definisane funkcije van njenog puta; ako registrujete sopstvene funkcije, ponašanje opisano u vodiču za formula engine i prilagođene funkcije je nepromenjeno. Centralizovanje slučaja premalo-argumenata je odvojen posao, jer svako od tih 280 tela ima sopstvenu semantiku koda greške i moraju se pregledati jedno po jedno, ne pretpostaviti
Kalkulacioni engine opisan ovde, obe fasade workbooka, i AutoFilter i API-ji vidljivosti reda koji ga hrane deo su HotXLS Delphi spreadsheet komponente, koja se isporučuje sa kompletnim izvornim kodom za Delphi i C++Builder i ne zahteva instalaciju Excela na mašini koja je pokreće