Ha a SUBTOTAL(109, ...) és a SUBTOTAL(9, ...) ugyanazt a számot adja vissza egy olyan munkafüzeten, amely rejtett sorokat tartalmaz, a kettő közül az egyik hibás. A HotXLS, a natív Excel táblázatkomponens Delphihez és C++Builderhez, pontosan így viselkedett a 2.197.0-s verzióig, mert a számítási motorja nem tudta megkérdezni egy munkalaptól, hogy egy adott sor rejtett-e
A tünet ritkán érkezik hibajelentésként a formulakódokról. Egyeztetlenségként érkezik: egy szerveren futó batch munka kiszámít egy összeget, egy felhasználó ugyanazt a fájlt Excelben nyitja meg szűrővel alkalmazva, és a két szám eltér, amennyi a kiszűrt sorok éppen összeadódtak. Senki sem gyanakszik az összesítő függvényre, mert a cellában lévő formulasztring azonos mindkét helyen. A különbség teljes egészében abban van, mit engedélyeztek az értékelőnek látni
Miért tartalmazza a SUBTOTAL 109 a rejtett sorokat?
Mert a legtöbb motortervezésben a réteg, amely egy formulát értékel ki, sosem tudja meg a sor láthatóságát. A HotXLS tankönyvi eset volt: a lxCalc.pas-ban lévő számítási motor egyetlen TXLSGetValue callbacken keresztül érte el a cellaértékeket, amely egy (lap, sor, oszlop) hármasra válaszol egy értékkel, és semmi mással. A láthatóság egy megjelenítési attribútum, amely a sor-rekordban tárolt, és a rekord egyetlen része sem utazott le a hívási láncon. A motornak ezért egy összesítő útvonala volt, és a SUBTOTAL függvényszám-táblázat mindkét fele erre oldódott fel. Ez nem egy kerekítési-hiba osztályú defektus: ez az egész oka annak, hogy a tábla második fele létezik. Az ECMA-376 1. rész, ISO/IEC 29500-1-ként kiadva, a SUBTOTAL-t definiálja a formulafüggvény-definícióiban (§18.17.7), egy első argumentummal, amely mind a belső összesítést, mind a rejtett-sor-szabályzatot kiválasztja. Az 1-től 11-ig terjedő kódok az AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR és VARP-ra képeznek le, miközben a manuálisan rejtett sorokon lévő értékeket is tartalmazzák. A 101-től 111-ig terjedő kódok ugyanazt a tizenegy összesítést választják, és kizárják ezeket. Egy felhasználó, aki 109-et gépel be 9 helyett, szándékos kijelentést tesz a rejtett adatokról, és egy motor, amely elmossa a megkülönböztetést, csendben felülbírálja ezt a kijelentést
Mire képezik le a függvényszámok a motoron belül
A HotXLS a SUBTOTAL első argumentumát a CalcSubtotalFunc-ban oldja fel, amely a 101-től 111-ig terjedő kódokat ugyanazokra a belső függvényazonosítókra normalizálja, mint az 1-től 11-ig terjedőket, majd magára az összesítésre diszpécsel. A család nagy része a inkrementális ExcelSum akkumulátoron folyik keresztül, azon, amely a SUM, COUNT, COUNTA, MIN, MAX és AVERAGE-t kezeli. Öt közülük nem tud: a STDEV, VAR, STDEVP, VARP és PRODUCT zárt formájú áthaladást igényel az adaton, így a CalcSubtotalFunc a 12, 46, 193, 194 és 183 belső kódokat egy külön redukálóhoz irányítja, a SubtotalReduceVariance-hoz. Ezt a szétválasztást érdemes elsőként feltérképezni, mielőtt bármihez hozzáérnél, mert két független összesítő útvonal két független cellabejárási ciklust jelent, és egy javítás, amelyet csak az egyikre alkalmaznak, a lehető legrosszabb kimenetet produkálja: a SUBTOTAL(109, ...) tiszteletben tartja a szűrőt, míg ugyanazon a tartományon a SUBTOTAL(107, ...) nem. A ciklusok megszámlálása a HotXLS-ben hatot hozott elő, amikor az AGGREGATE-et is beleszámították, szétszórva a tartomány-értékelésen, a sima tartomány-gyűjtésen és három külön redukálón
Miért egy piszkozat-mező hat új szignatúra helyett?
Mert egy új paraméter átfűzése hat cellabejárási függvényen, plusz mindenen, ami hívja őket, széles változtatás egy forró kódútvonalon egyetlen boolean kedvéért. A HotXLS-nek már volt precedense az alternatívára: egy átmeneti mező a kalkulátoron, ugyanabban a szellemben, mint az a piszkozat-mező, amelyet a GetRangeInfo használ annak feljegyzésére, amikor egy 3D hivatkozás egy külső munkafüzetbe oldódott fel. A 2.197.0-s verzió hozzáadott egy másodikat. A motor kapott egy callback-típust, a TXLSIsRowHidden-t, egy (SheetIndex, row) függvényként deklarálva, amely Boolean-t ad vissza, a FIsRowHidden-ben tárolva, plusz egy átmeneti FIgnoreHiddenRows jelzőt. A jelző a CalcSubtotalFunc belépésénél kerül élesítésre, amikor a függvénykód a 101-től 111-ig terjedő tartományba esik, és a CalcAggregateFunc belépésénél az AGGREGATE opciókódokhoz, amelyek rejtett-sor-kizárást választanak. Minden cellabejárási ciklus ekkor megvizsgálja azt, és kihagy egy sort, amikor be van állítva, soronként egyetlen sort hozzáadva
// 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;Két részlet az élesítő kódban hordozza az egész séma helyességét. A jelző elmentésre és visszaállításra kerül ahelyett, hogy egyszerűen beállítanák és törölnék, mert egy SUBTOTAL argumentum tartalmazhat egy kifejezést, amely saját értékelést futtat, miközben a külső összesítés még a veremen van, és ennek a beágyazott munkának nem szabad örökölnie vagy elpusztítania a külső kaput. És a visszaállítás egy finally blokkban él, mert a CalcSubtotalFunc-nak több korai kilépése van hibakódokra; egy hiba-visszatérés után élesítve maradt jelző csendben megrontaná a következő, kapcsolódásmentes formulát az újraszámítási sorrendben
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;Az Assigned teszt az, ami kompatibilissé teszi a változtatást. A HotXLS egy harmadik paraméterrel bővítette a kalkulátor konstruktorát, amely alapértelmezetten nil, így bármely kód, amely a régi kétargumentumos hívással épít egy TXLSCalculator-t, továbbra is fordul, és továbbra is megkapja a régi, rejtett-tartalmazó viselkedést. Semmi a meglévő API-ban nem változtatta meg az alakját
Honnan származik valójában a rejtett-sor bit?
A munkalapról, két különböző forráson keresztül, mert a HotXLS két munkafüzet-motort hordoz. A régi BIFF oldal a TXLSRowInfoList.GetHidden-ből válaszol, a TXLSWorkbook.GetRowHidden-en keresztül elérve. Az OOXML oldal a TXLSXWorksheet.GetRowHidden-ből válaszol, a TXLSXWorkbook.GetCalcRowHidden-en keresztül elérve. Mindkettő be van drótozva a kalkulátorba a konstrukciónál, a cellaérték-callback mellett, amelyet tükröznek. A sor-konvenciók az a hely, ahol az ilyen híd normál esetben elromlik, így érdemes ezeket explicit módon kimondani. A kalkulátor egy 0-alapú sort ad át a callbacknek, egyezve azokkal a koordinátákkal, amelyeket a TXLSGetValue már használ. Az XLSX munkalap a rejtett-sor térképét 1-alapú sorszámmal kulcsolja, pontosan úgy, ahogy az Excel számozza a sorokat, ami az is, amit a nyilvános RowHidden[ARow] tulajdonság kitesz. Az XLSX híd ezért hozzáad egyet a keresés előtt, a BIFF híd pedig nem, mert a TXLSRowInfoList már 0-alapú. Mindkét híd láthatóként kezel egy tartományon kívüli laptagot vagy sort, így egy tartományon kívüli lekérdezés a régi, rejtett-tartalmazó válaszra fokozódik le adatvesztés helyett
Mi változik a szűrt munkafüzeteknél
Ez az az eset, amely a support-jegyeket generálja. Egy AutoFilter alkalmazása a HotXLS-ben az ApplyAutoFilter-en keresztül kiértékeli az oszlopfeltételeket, és elrejt minden adatsort, amely nem egyezik, ami pontosan az, amit az Excel tesz, amikor egy felhasználó rákattint egy szűrő legördülőre. A v2.197.0 előtt ezek a rejtett sorok láthatatlanok voltak a felhasználó számára, és teljesen láthatók a számítási motor számára, így egy szerveroldali SUBTOTAL(109, ...) a szűretlen összeget jelentette. Most ugyanaz a hívás a szűrtet jelenti
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;A manuális rejtés ugyanígy működik, mivel a RowHidden[ARow] := True ugyanaz az állapot, amelyet a szűrő ír. Ez az egyenértékűség szándékos az Excelben, és most a HotXLS-ben is fennáll. Egy következmény megjegyzést érdemel bármely dokumentációban, amely a generált munkafüzeteiddel érkezik: egy 109-es kóddal számított összeg nézet-függő szám, így egy címzett, aki törli a szűrőt, megváltoztatja azt. Amikor egy jelentésnek egy rögzített számot kell kimondania, függetlenül attól, mit tesz az olvasó a nézettel, a 9-es kód a helyes választás, és mindig is az volt. A szűrők, validáció és táblák együtt vannak tárgyalva a a validáció, az AutoFilter és a táblák cikkében. Mivel a sorok elrejtése nem érint egyetlen formulát sem, magától nem is piszkolja be a függőségi grafot, ami érdemes tudni, ha a inkrementális újraszámításra a piszkos alrendszeren keresztül hagyatkozol, hogy nagy munkafüzeteket reszponzívan tarts
AGGREGATE opciókódok és egy még nyitott korlát
Az AGGREGATE a SUBTOTAL egy második szabályzat-argumentummal, és a HotXLS a CalcAggregateFunc-ban kezeli. Az opció-argumentum független kapcsolókat kódol: hogy a tartományon belüli beágyazott SUBTOTAL és AGGREGATE hívásokat kihagyják-e, hogy a rejtett sorokon lévő értékeket kihagyják-e, és hogy a hibaértékeket elnyomják-e a terjesztés helyett. A HotXLS élesíti a megosztott rejtett-sor kaput a 2, 3, 6 és 7 opciókódokhoz, és elnyomja a hibaértékeket a 4-től 7-ig terjedő opciókódokhoz. A függvényszám-argumentum ezután pontosan úgy választja ki az összesítést, mint a SUBTOTAL, beleértve a szórás, szórásnégyzet és szorzat irányítását saját redukálóikhoz. Egy dokumentált hiányosság megmarad, és jobb itt kimondani, mint a gyártásban felfedezni: az alacsony opciókódokhoz társított kihagyás-beágyazott-SUBTOTAL szemantika nincs megvalósítva a HotXLS-ben. Egy beágyazott SUBTOTAL érzékelése egy hivatkozott tartományon belül megköveteli az értékelő rekurziós állapotának megjelölését, hogy egy belső összesítés bejelenthesse magát a külsőnek, ami nagyobb változtatás, mint a rejtett-sor kapu. Gyakorlatban a kitettség kicsi, mert valódi munkafüzetek szinte mindig a SUBTOTAL formulákat azokon a tartományokon kívül helyezik el, amelyeket más SUBTOTAL formulák összesítenek. Ha a generátorod tényleg átfedő összesítő tartományokat épít, ne hagyatkozz az alacsony opciókódokra ezek deduplikálásához
Az aritásőr, amely vele együtt szállított
A 2.197.0-s verzió egy validációs rést is bezárt ugyanabban a diszpécserben, és a tervezési ok ugyanaz, amely a piszkozat-mezőt motiválta: tedd az ellenőrzést oda, ahol egyszer megírható. Nagyjából 280 beépített függvénytörzs mindegyike a saját argumentumszámát ellenőrizte az Item.ChildCount ellen, ami nem hagyott konzisztens határt a túl sok argumentum esetére. Egy olyan hívás, mint az =SIN(1,2), elért egy függvénytörzset, amely megvizsgálta az első argumentumát, figyelmen kívül hagyta a fölösleget, és hihető számot adott vissza ott, ahol az Excel #VALUE!-t ad. A HotXLS már tárolta minden beépített függvény deklarált aritását a függvénynyilvántartásában, kitéve THashFunc.ArgsCnt-ként, ahol a -1 egy variadikus függvényt jelöl, mint a SUM, IF vagy CONCAT. A 2.197.0-s verzió ezt egy új TXLSFormula.FuncArgsCntByPtg tulajdonságon keresztül továbbította, és hozzáadott egy kaput a GetValueItemFunc, a fő diszpécser tetejéhez
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;Az őr elutasítja a túl sok argumentumot, és szándékosan semmit nem mond a túl kevésről. Egy záró opcionális argumentum kihagyása jogszerű az Excelben a VLOOKUP, SUBSTITUTE és egy hosszú lista más függvény esetén, így egy szimmetrikus ellenőrzés elrontotta volna a helyes formulákat, hogy elkapja a helyteleneket. Ismeretlen azonosítók variadikusként jelentődnek, és kihagyják a kaput, ami az, ami kívül tartja a felhasználó által definiált függvényeket az útjából; ha a saját függvényeidet regisztrálod, a a formulamotor és az egyéni függvények útmutatójában leírt viselkedést ez nem érinti. A túl-kevés eset központosítása külön munka, mert e 280 törzs mindegyikének saját hibakód-szemantikája van, és egyenként kell felülvizsgálni őket, nem feltételezni
Az itt leírt számítási motor, mindkét munkafüzet-fasád, és az AutoFilter és sor-láthatóság API-k, amelyek táplálják, a HotXLS Delphi táblázatkomponens részei, amely teljes forráskóddal érkezik Delphihez és C++Builderhez, és nem igényel Excel telepítést azon a gépen, amely futtatja