Jei SUBTOTAL(109, ...) ir SUBTOTAL(9, ...) grąžina tą patį skaičių darbaknygėje, kurioje yra paslėptų eilučių, vienas iš dviejų yra neteisingas. HotXLS, natyvus Excel skaičiuoklių komponentas Delphi ir C++Builder, elgėsi lygiai taip iki 2.197.0 versijos, nes jo skaičiavimo variklis neturėjo būdo paklausti darbalapio, ar tam tikra eilutė yra paslėpta
Simptomas retai atkeliauja kaip klaidos pranešimas apie formulės kodus. Jis atkeliauja kaip neatitikimas: serveryje veikiantis paketinis darbas apskaičiuoja sumą, vartotojas atidaro tą patį failą Excel su pritaikytu filtru, ir du skaičiai skiriasi tiek, kiek sudarė atfiltruotos eilutės. Niekas neįtaria agregavimo funkcijos, nes formulės eilutė langelyje identiška abiejose vietose. Skirtumas yra visiškai tame, ką skaičiavimo varikliui buvo leista matyti
Kodėl SUBTOTAL 109 įtraukia paslėptas eilutes?
Todėl, kad daugumoje variklio projektavimų sluoksnis, vertinantis formulę, niekada nesužino apie eilutės matomumą. HotXLS buvo klasikinis atvejis: skaičiavimo variklis lxCalc.pas pasiekdavo langelio reikšmes per vieną TXLSGetValue atgalinį iškvietimą, atsakantį reikšme (lapas, eilutė, stulpelis) trigubui ir niekuo daugiau. Matomumas yra pateikimo atributas, saugomas eilutės įraše, ir jokia to įrašo dalis nekeliavo žemyn iškvietimo grandine. Variklis todėl turėjo vieną agregavimo kelią, ir abi SUBTOTAL funkcijos numerio lentelės pusės išsprendė į jį. Tai nėra apvalinimo klaidos klasė: tai visa priežastis, kodėl egzistuoja antra lentelės pusė. ECMA-376 1 dalis, publikuota kaip ISO/IEC 29500-1, apibrėžia SUBTOTAL savo formulės funkcijų apibrėžimuose (§18.17.7) su pirmuoju argumentu, pasirenkančiu ir vidinį agregavimą, ir paslėptos eilutės politiką. Kodai nuo 1 iki 11 susieja su AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR ir VARP, įtraukiant reikšmes rankiniu būdu paslėptose eilutėse. Kodai nuo 101 iki 111 pasirenka tuos pačius vienuolika agregavimų ir jas atmeta. Vartotojas, įrašęs 109 vietoj 9, sąmoningai teigia apie paslėptus duomenis, o variklis, sujungiantis skirtumą, tyliai tą teiginį panaikina
Ką funkcijų numeriai atvaizduoja variklio viduje
HotXLS išsprendžia SUBTOTAL pirmąjį argumentą CalcSubtotalFunc, kuris normalizuoja kodus nuo 101 iki 111 žemyn ant tų pačių vidinių funkcijų identifikatorių, kaip ir kodai nuo 1 iki 11, o tada dispečerizuoja pačiu agregavimu. Dauguma šeimos teka per prieauginį ExcelSum akumuliatorių, kuris tvarko SUM, COUNT, COUNTA, MIN, MAX ir AVERAGE. Penki iš jų negali: STDEV, VAR, STDEVP, VARP ir PRODUCT reikalauja uždaros formos praėjimo per duomenis, todėl CalcSubtotalFunc nukreipia vidinius kodus 12, 46, 193, 194 ir 183 į atskirą reduktorių, SubtotalReduceVariance. Tas padalinimas yra pirmas dalykas, kurį verta atvaizduoti prieš liečiant bet ką, nes du nepriklausomi agregavimo keliai reiškia du nepriklausomus langelio vaikščiojimo ciklus, o pataisymas, taikytas tik vienam iš jų, sukuria patį blogiausią rezultatą: SUBTOTAL(109, ...) paiso filtro, o SUBTOTAL(107, ...) tame pačiame intervale ne. Ciklų skaičiavimas HotXLS aptiko šešis, įtraukus AGGREGATE, paskirstytus intervalo vertinime, paprastame intervalo surinkime ir trijuose atskiruose reduktoriuose
Kodėl juodraštinis laukas vietoj šešių naujų signatūrų?
Todėl, kad naujo parametro pervėrimas per šešias langelio vaikščiojimo funkcijas, plius viską, kas jas kviečia, yra platus pakeitimas karštame kodo kelyje dėl vieno loginio kintamojo. HotXLS jau turėjo precedentą alternatyvai: laikiną lauką skaičiuotuve, ta pačia dvasia kaip juodraštinis laukas, kurį naudoja GetRangeInfo, kad įrašytų, kada 3D nuoroda išsisprendė į išorinę darbaknygę. Versija 2.197.0 pridėjo antrą. Variklis įgijo atgalinio iškvietimo tipą, TXLSIsRowHidden, deklaruotą kaip funkcija (SheetIndex, row), grąžinanti Boolean, saugomą FIsRowHidden, plius laikiną FIgnoreHiddenRows vėliavėlę. Vėliavėlė įjungiama CalcSubtotalFunc pradžioje, kai funkcijos kodas patenka tarp 101 ir 111, ir CalcAggregateFunc pradžioje AGGREGATE parinkties kodams, pasirenkantiems paslėptos eilutės atmetimą. Kiekvienas langelio vaikščiojimo ciklas tada tikrina ją ir praleidžia vieną eilutę, kai ji nustatyta, pridėdamas po vieną eilutę kiekvienam
// 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;Dvi smulkmenos ginklavimo kode neša visos schemos teisingumą. Vėliavėlė išsaugoma ir atkuriama, o ne tiesiog nustatoma ir išvaloma, nes SUBTOTAL argumentas gali turėti išraišką, vykdančią savo pačios vertinimą, kol išorinis agregavimas dar dėklo viršuje, ir tas įdėtas darbas neturi paveldėti ar sunaikinti išorinio vartų. O atkūrimas gyvena finally bloke, nes CalcSubtotalFunc turi kelis ankstyvus išėjimus klaidos kodams; vėliavėlė, palikta ginkluota po klaidingo grąžinimo, tyliai sugadintų kitą nesusijusią formulę perskaičiavimo tvarkoje
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 patikra yra tai, kas išlaiko pakeitimą suderinamą. HotXLS praplėtė skaičiuotuvo konstruktorių trečiu parametru, kurio numatytoji reikšmė nil, todėl bet koks kodas, kuriantis TXLSCalculator su senuoju dviejų argumentų iškvietimu, vis tiek kompiliuojasi ir vis tiek gauna paveldėtą įtraukti-paslėptus elgesį. Niekas apie esamą API formą nepasikeitė
Iš kur iš tikrųjų ateina paslėptos eilutės bitas?
Iš darbalapio, per du skirtingus šaltinius, nes HotXLS turi du darbaknygės variklius. Legacy BIFF pusė atsako iš TXLSRowInfoList.GetHidden, pasiekiamo per TXLSWorkbook.GetRowHidden. OOXML pusė atsako iš TXLSXWorksheet.GetRowHidden, pasiekiamo per TXLSXWorkbook.GetCalcRowHidden. Abu sujungti su skaičiuotuvu konstravimo metu, šalia langelio-reikšmės atgalinio iškvietimo, kurį jie atspindi. Eilučių konvencijos yra vieta, kur šio tipo tiltas paprastai suklysta, todėl jas verta aiškiai išsakyti. Skaičiuotuvas paduoda atgaliniam iškvietimui 0 pagrįstą eilutę, atitinkančią koordinates, kurias jau naudoja TXLSGetValue. XLSX darbalapis raktuoja savo paslėptos eilutės žemėlapį pagal 1 pagrįstą eilutės numerį, lygiai kaip Excel numeruoja eilutes, kas taip pat yra tai, ką atskleidžia vieša RowHidden[ARow] savybė. XLSX tiltas todėl prideda vienetą prieš paiešką, o BIFF tiltas ne, nes TXLSRowInfoList jau 0 pagrįstas. Abu tiltai laiko lapo indeksą ar eilutę už galiojančio intervalo ribų kaip matomą, todėl užklausa už ribų nusileidžia iki seno įtraukti-paslėptus atsakymo, o ne prarandant duomenis
Kas keičiasi filtruotoms darbaknygėms
Tai yra atvejis, generuojantis palaikymo bilietus. AutoFilter pritaikymas HotXLS per ApplyAutoFilter įvertina stulpelio kriterijus ir paslepia kiekvieną duomenų eilutę, neatitinkančią jų, kas yra tiksliai tai, ką Excel daro, kai vartotojas spusteli filtro iškrentantį meniu. Iki v2.197.0 tos paslėptos eilutės buvo nematomos vartotojui ir visiškai matomos skaičiavimo varikliui, todėl serverio pusės SUBTOTAL(109, ...) pranešdavo neatfiltruotą sumą. Dabar tas pats iškvietimas praneša atfiltruotą
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;Rankinis paslėpimas veikia taip pat, nes RowHidden[ARow] := True yra ta pati būsena, kurią rašo filtras. Tas atitikmuo yra tyčinis Excel ir dabar galioja ir HotXLS. Viena pasekmė nusipelno pastabos bet kurioje dokumentacijoje, kuri pridedama prie jūsų sugeneruotų darbaknygių: suma, apskaičiuota su kodu 109, yra nuo vaizdo priklausantis skaičius, todėl gavėjas, išvalęs filtrą, ją pakeičia. Kai ataskaita turi teigti fiksuotą skaičių nepriklausomai nuo to, ką skaitytojas daro su vaizdu, kodas 9 yra teisingas pasirinkimas ir visada buvo. Filtrai, validacija ir lentelės aptariamos kartu straipsnyje apie duomenų validaciją, AutoFilter ir lenteles. Kadangi eilučių paslėpimas nepaliečia jokios formulės, jis taip pat savaime neišpurvina priklausomybių grafo, kas verta žinoti, jei pasikliaujate prieaugine perskaičiavimu per nešvarų pografį, kad didelės darbaknygės liktų reaguojančios
AGGREGATE parinkties kodai ir viena vis dar atvira riba
AGGREGATE yra SUBTOTAL su antru politikos argumentu, ir HotXLS tai tvarko CalcAggregateFunc. Parinkties argumentas koduoja nepriklausomus jungiklius: ar įdėti SUBTOTAL ir AGGREGATE iškvietimai intervale praleidžiami, ar reikšmės paslėptose eilutėse praleidžiamos, ir ar klaidų reikšmės nuslopinamos, o ne perduodamos. HotXLS ginkluoja bendrą paslėptos eilutės vartų mechanizmą parinkties kodams 2, 3, 6 ir 7, ir nuslopina klaidų reikšmes parinkties kodams nuo 4 iki 7. Funkcijos numerio argumentas tada pasirenka agregavimą lygiai kaip SUBTOTAL, įskaitant dispersijos, standartinio nuokrypio ir sandaugos nukreipimą per savo reduktorius. Viena dokumentuota spraga lieka, ir geriau ją čia išsakyti, nei aptikti produkcijoje: ignoruoti-įdėtą-SUBTOTAL semantika, susieta su žemais parinkties kodais, nerealizuota HotXLS. Įdėto SUBTOTAL aptikimas nurodytame intervale reikalauja pažymėti vertinimo rekursijos būseną, kad vidinis agregavimas galėtų apie save pranešti išoriniam, kas yra didesnis pakeitimas nei paslėptos eilutės vartai. Praktikoje ekspozicija maža, nes realios darbaknygės beveik visada patalpina SUBTOTAL formules už intervalų, kuriuos agreguoja kitos SUBTOTAL formulės, ribų. Jei jūsų generatorius kuria persidengiančius agregavimo intervalus, nesitikėkite, kad žemi parinkties kodai juos duplikuos
Aritmetikos apsauga, pristatyta kartu su tuo
Versija 2.197.0 taip pat uždarė validacijos spragą tame pačiame dispečeryje, ir dizaino priežastis yra ta pati, kuri motyvavo juodraštinį lauką: padėti patikrą ten, kur ji gali būti parašyta vieną kartą. Maždaug 280 įtaisytų funkcijų kūnų kiekvienas patikrindavo savo argumentų skaičių prieš Item.ChildCount, kas nepaliko nuoseklios ribos per didelio argumentų skaičiaus atvejui. Iškvietimas kaip =SIN(1,2) pasiekdavo funkcijos kūną, kuris tikrindavo pirmąjį argumentą, ignoruodavo perteklių ir grąžindavo tikėtiną skaičių ten, kur Excel grąžina #VALUE!. HotXLS jau saugojo deklaruotą kiekvienos įtaisytos funkcijos aritmetiką savo funkcijų registre, atskleistą kaip THashFunc.ArgsCnt, su -1, žyminčiu variadinę funkciją, tokią kaip SUM, IF ar CONCAT. Versija 2.197.0 tai perdavė per naują TXLSFormula.FuncArgsCntByPtg savybę ir pridėjo vieną vartų mechanizmą pagrindinio dispečerio, GetValueItemFunc, viršuje
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;Apsauga atmeta per daug argumentų ir sąmoningai nieko nesako apie per mažai. Trūkstamo pasirinktinio galinio argumento praleidimas yra teisėtas Excel VLOOKUP, SUBSTITUTE ir ilgam kitų sąrašui, todėl simetriška patikra būtų sugadinusi teisingas formules, gaudydama neteisingas. Nežinomi identifikatoriai praneša kaip variadiniai ir praleidžia vartus visiškai, kas ir išlaiko vartotojo apibrėžtas funkcijas už jų kelio; jei registruojate savo pačių funkcijas, elgesys, aprašytas gide apie formulės variklį ir pasirinktines funkcijas, nepaveiktas. Per mažo skaičiaus atvejo centralizavimas yra atskiras darbas, nes kiekvienas iš tų 280 kūnų turi savo klaidos kodo semantiką, ir jie turi būti peržiūrėti po vieną, o ne prielaida
Čia aprašytas skaičiavimo variklis, abi darbaknygės fasadai ir AutoFilter bei eilučių-matomumo API, kuris jį maitina, yra HotXLS Delphi skaičiuoklių komponento dalis, kuris pristatomas su pilnu šaltiniu Delphi ir C++Builder ir nereikalauja Excel diegimo mašinoje, kurioje veikia