Jos SUBTOTAL(109, ...) ja SUBTOTAL(9, ...) palauttavat saman luvun työkirjassa, joka sisältää piilotettuja rivejä, jompikumpi niistä on väärässä. HotXLS, natiivi Excel-laskentataulukkokomponentti Delphille ja C++Builderille, käyttäytyi täsmälleen niin versioon 2.197.0 asti, koska sen laskentamoottorilla ei ollut tapaa kysyä laskentataulukolta, oliko tietty rivi piilotettu
Oire harvoin saapuu bugiraporttina kaavakoodeista. Se saapuu epätäsmäyksenä: eräajo palvelimella laskee summan, käyttäjä avaa saman tiedoston Excelissä suodattimen kanssa, ja kaksi lukua eroavat sen verran, minkä suodatetut rivit sattuivat summaamaan. Kukaan ei epäile aggregointifunktiota, koska kaavan merkkijono solussa on identtinen molemmissa paikoissa. Ero on kokonaan siinä, mitä evaluoija sai nähdä
Miksi SUBTOTAL 109 sisältää piilotettuja rivejä?
Koska useimmissa moottorisuunnitelmissa kerros, joka evaluoi kaavan, ei koskaan saa tietää rivin näkyvyydestä. HotXLS oli oppikirjaesimerkki: lxCalc.pas:n laskentamoottori tavoitti solujen arvot yhden TXLSGetValue-takaisinkutsun kautta, joka vastaa arvolla (taulukko, rivi, sarake) -kolmikolle eikä mitään muuta. Näkyvyys on esitysattribuutti, joka on tallennettu rivitietueeseen, eikä mikään osa tuosta tietueesta matkustanut alas kutsuketjua. Moottorilla oli siis yksi aggregointipolku, ja SUBTOTAL-funktionumerotaulukon molemmat puoliskot ratkesivat siihen. Se ei ole pyöristysvirheluokan vika: se on koko syy, miksi taulukon toinen puolisko on olemassa. ECMA-376 Part 1, julkaistu ISO/IEC 29500-1:na, määrittelee SUBTOTAL:n kaavafunktiomääritelmissään (§18.17.7) ensimmäisellä argumentilla, joka valitsee sekä sisäisen aggregoinnin että piilotetun rivin käytännön. Koodit 1–11 kartoittuvat funktioihin AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR ja VARP samalla kun ne sisällyttävät arvot manuaalisesti piilotetuilta riveiltä. Koodit 101–111 valitsevat samat yksitoista aggregointia ja sulkevat ne pois. Käyttäjä, joka kirjoittaa 109:n 9:n sijaan, tekee tarkoituksellisen väitteen piilotetusta datasta, ja moottori, joka romahduttaa eron hiljaa, kumoaa hiljaa tuon väitteen
Mihin funktionumerot kartoittuvat moottorin sisällä
HotXLS ratkaisee SUBTOTAL:in ensimmäisen argumentin funktiossa CalcSubtotalFunc, joka normalisoi koodit 101–111 alas samoihin sisäisiin funktiotunnisteisiin kuin koodit 1–11 ja sitten jakaa itse aggregoinnin mukaan. Suurin osa perheestä virtaa inkrementaalisen ExcelSum-kertymän kautta, se, joka käsittelee SUM:in, COUNT:in, COUNTA:n, MIN:in, MAX:in ja AVERAGE:n. Viisi niistä ei voi: STDEV, VAR, STDEVP, VARP ja PRODUCT tarvitsevat suljetun muodon läpikäynnin datan yli, joten CalcSubtotalFunc reitittää sisäiset koodit 12, 46, 193, 194 ja 183 erilliseen reduktoriin, SubtotalReduceVariance. Tuo jako on ensimmäinen asia, joka kannattaa kartoittaa ennen mihinkään koskemista, koska kaksi itsenäistä aggregointipolkua tarkoittaa kahta itsenäistä solunlukusilmukkaa, ja korjaus, jota sovelletaan vain toiseen niistä, tuottaa pahimman mahdollisen lopputuloksen: SUBTOTAL(109, ...) kunnioittaa suodinta, kun taas SUBTOTAL(107, ...) samalla alueella ei. Silmukoiden laskeminen HotXLS:ssä paljasti kuusi niistä kun AGGREGATE otettiin mukaan, levittäytyneenä alueen evaluoinnin, tavallisen alueen keräyksen ja kolmen erillisen reduktorin yli
Miksi kertakäyttökenttä eikä kuusi uutta signatuuria?
Koska uuden parametrin säikeistäminen kuuden solunlukufunktion läpi, sekä kaiken niitä kutsuvan, on laaja muutos kuumaan koodipolkuun yhden totuusarvon vuoksi. HotXLS:llä oli jo ennakkotapaus vaihtoehdolle: väliaikainen kenttä laskimessa, samassa hengessä kuin kertakäyttökenttä, jota GetRangeInfo käyttää tallentamaan, milloin 3D-viittaus ratkesi ulkoiseen työkirjaan. Versio 2.197.0 lisäsi toisen. Moottori sai takaisinkutsutyypin, TXLSIsRowHidden, julistettuna funktiona (SheetIndex, row), joka palauttaa Boolean-arvon, tallennettuna muuttujaan FIsRowHidden, sekä väliaikaisen FIgnoreHiddenRows-lipun. Lippu aseistetaan CalcSubtotalFunc:n aloituskohdassa, kun funktiokoodi osuu väliin 101–111, ja CalcAggregateFunc:n aloituskohdassa AGGREGATE-valintakoodeille, jotka valitsevat piilotetun rivin poissulkemisen. Jokainen solunlukusilmukka tarkastaa sen sitten ja ohittaa yhden rivin, kun se on asetettu, lisäten yhden rivin kukin
// 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;Kaksi yksityiskohtaa aseistuskoodissa kantavat koko järjestelmän oikeellisuutta. Lippu tallennetaan ja palautetaan sen sijaan, että se yksinkertaisesti asetettaisiin ja tyhjennettäisiin, koska SUBTOTAL-argumentti voi sisältää lausekkeen, joka ajaa oman evaluointinsa samalla kun ulompi aggregointi on vielä pinolla, ja tuo sisäkkäinen työ ei saa periä eikä tuhota ulompaa porttia. Ja palautus elää finally-lohkossa, koska CalcSubtotalFunc:lla on useita aikaisia poistumisia virhekoodeille; lippu, joka jätetään aseistetuksi virhepaluun jälkeen, turmelisi hiljaa seuraavan toisiinsa liittymättömän kaavan uudelleenlaskennan järjestyksessä
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-testi on se, mikä pitää muutoksen yhteensopivana. HotXLS laajensi laskimen konstruktoria kolmannella parametrilla, joka oletusarvoisesti on nil, joten mikä tahansa koodi, joka rakentaa TXLSCalculator:n vanhalla kahden argumentin kutsulla, kääntyy silti ja saa yhä vanhan sisällytä-piilotetut-käyttäytymisen. Mikään olemassa olevan API:n muodossa ei muuttunut
Mistä piilotetun rivin bitti oikeasti tulee?
Laskentataulukosta, kahden eri lähteen kautta, koska HotXLS kantaa kahta työkirjamoottoria. Vanha BIFF-puoli vastaa TXLSRowInfoList.GetHidden:sta, tavoitettuna TXLSWorkbook.GetRowHidden:n kautta. OOXML-puoli vastaa TXLSXWorksheet.GetRowHidden:sta, tavoitettuna TXLSXWorkbook.GetCalcRowHidden:n kautta. Molemmat on kytketty laskimeen rakennushetkellä, solunarvon takaisinkutsun rinnalla, jota ne peilaavat. Rivikäytännöt ovat se paikka, jossa tämänlainen silta yleensä menee väärin, joten ne kannattaa todeta eksplisiittisesti. Laskin antaa takaisinkutsulle 0-pohjaisen rivin, täsmäten koordinaatteihin, joita TXLSGetValue jo käyttää. XLSX-laskentataulukko avaa piilotetun-rivin karttansa 1-pohjaisella rivinumerolla, täsmälleen kuten Excel numeroi rivejä, mikä on myös se, mitä julkinen RowHidden[ARow]-ominaisuus paljastaa. XLSX-silta siis lisää yhden ennen hakua, ja BIFF-silta ei, koska TXLSRowInfoList on jo 0-pohjainen. Molemmat sillat käsittelevät laskentataulukkoindeksiä tai riviä kelvollisen alueen ulkopuolella näkyvänä, joten alueen ulkopuolinen kysely rappeutuu vanhaksi sisällytä-piilotetut-vastaukseksi sen sijaan, että se pudottaisi dataa
Mikä muuttuu suodatetuille työkirjoille
Tämä on tapaus, joka tuottaa tukipyyntöjä. AutoFilterin soveltaminen HotXLS:ssä ApplyAutoFilter:n kautta evaluoi sarakekriteerit ja piilottaa jokaisen datarivin, joka ei täsmää, mikä on täsmälleen se, mitä Excel tekee, kun käyttäjä napsauttaa suodattimen pudotusvalikkoa. Ennen v2.197.0:aa nuo piilotetut rivit olivat näkymättömiä käyttäjälle ja täysin näkyviä laskentamoottorille, joten palvelinpuolen SUBTOTAL(109, ...) raportoi suodattamattoman summan. Nyt sama kutsu raportoi suodatetun
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;Manuaalinen piilotus toimii samalla tavalla, koska RowHidden[ARow] := True on sama tila, jonka suodin kirjoittaa. Tuo ekvivalenssi on tarkoituksellinen Excelissä ja pätee nyt myös HotXLS:ssä. Yksi seuraus ansaitsee maininnan missä tahansa dokumentaatiossa, joka toimitetaan tuottamiesi työkirjojen kanssa: koodilla 109 laskettu summa on näkymästä riippuvainen luku, joten vastaanottaja, joka poistaa suodattimen, muuttaa sitä. Kun raportin täytyy ilmoittaa kiinteä luku riippumatta siitä, mitä lukija tekee näkymälle, koodi 9 on oikea valinta ja on aina ollut. Suodattimet, validointi ja taulukot käsitellään yhdessä artikkelissa tietovalidoinnista, AutoFilterista ja taulukoista. Koska rivien piilottaminen ei kosketa yhtään kaavaa, se ei myöskään likaa riippuvuusgraafia itsestään, mikä kannattaa tietää, jos luotat inkrementaaliseen uudelleenlaskentaan likaisen alagraafin yli pitääksesi suuret työkirjat responsiivisina
AGGREGATE-valintakoodit ja yksi raja, joka on yhä avoinna
AGGREGATE on SUBTOTAL toisella käytäntöargumentilla, ja HotXLS käsittelee sen funktiossa CalcAggregateFunc. Valinta-argumentti koodaa itsenäisiä kytkimiä: ohitetaanko sisäkkäiset SUBTOTAL- ja AGGREGATE-kutsut alueen sisällä, ohitetaanko arvot piilotetuilla riveillä, ja tukahdutetaanko virhearvot sen sijaan, että ne levitettäisiin. HotXLS aseistaa jaetun piilotetun-rivin portin valintakoodeille 2, 3, 6 ja 7, ja tukahduttaa virhearvot valintakoodeille 4–7. Funktionumeroargumentti valitsee sitten aggregoinnin täsmälleen kuten SUBTOTAL tekee, mukaan lukien varianssin, keskihajonnan ja tulon reitityksen omien reduktoriensa kautta. Yksi dokumentoitu aukko jää jäljelle, ja se on parempi todeta täällä kuin löytää tuotannosta: matalien valintakoodien yhteydessä oleva ohita-sisäkkäinen-SUBTOTAL-semantiikka ei ole toteutettu HotXLS:ssä. Sisäkkäisen SUBTOTAL:in havaitseminen viitatun alueen sisällä vaatii evaluoijan rekursiotilan merkitsemistä niin, että sisempi aggregointi voi ilmoittautua ulommalle, mikä on isompi muutos kuin piilotetun rivin portti. Käytännössä altistus on pieni, koska todelliset työkirjat lähes aina sijoittavat SUBTOTAL-kaavat alueiden ulkopuolelle, joita muut SUBTOTAL-kaavat aggregoivat. Jos generaattorisi rakentaa päällekkäisiä aggregointialueita, älä luota matalien valintakoodien poistavan niistä duplikaatit
Ariteettivartija, joka toimitettiin sen rinnalla
Versio 2.197.0 sulki myös validointiaukon samassa jakajassa, ja suunnittelusyy on sama, joka motivoi kertakäyttökenttää: laita tarkistus paikkaan, jossa se voidaan kirjoittaa kerran. Suunnilleen 280 sisäänrakennettua funktiorunkoa kukin tarkisti oman argumenttimääränsä Item.ChildCount:ia vasten, mikä ei jättänyt yhtenäistä rajaa liian monen argumentin tapaukselle. Kutsu kuten =SIN(1,2) tavoitti funktiorungon, joka tarkasteli ensimmäistä argumenttiaan, ohitti ylimääräisen, ja palautti uskottavan luvun siellä, missä Excel palauttaa #VALUE!:n. HotXLS tallensi jo jokaisen sisäänrakennetun ilmoitetun ariteetin funktiorekisteriinsä, paljastettuna muuttujana THashFunc.ArgsCnt, jossa -1 merkitsee variadista funktiota kuten SUM, IF tai CONCAT. Versio 2.197.0 välitti tämän eteenpäin uuden TXLSFormula.FuncArgsCntByPtg-ominaisuuden kautta ja lisäsi yhden portin GetValueItemFunc:n huipulle, pääjakajaan
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;Vartija hylkää liian monta argumenttia ja jättää tarkoituksella sanomatta mitään liian harvasta. Perässä olevan valinnaisen argumentin jättäminen pois on laillista Excelissä VLOOKUPille, SUBSTITUTElle ja pitkälle listalle muita, joten symmetrinen tarkistus olisi rikkonut oikeita kaavoja napatakseen vääriä. Tuntemattomat tunnisteet raportoivat variadisina ja ohittavat portin kokonaan, mikä on se, mikä pitää käyttäjän määrittelemät funktiot sen tieltä; jos rekisteröit omia funktioitasi, artikkelissa kaavamoottorista ja mukautetuista funktioista kuvattu käyttäytyminen ei vaikutu. Liian harvan tapauksen keskittäminen on erillinen työ, koska jokaisella noista 280 rungosta on oma virhekoodisemantiikkansa, ja ne täytyy tarkastaa yksi kerrallaan sen sijaan, että niitä oletettaisiin
Tässä kuvattu laskentamoottori, molemmat työkirjajulkisivut, ja AutoFilter- ja rivinäkyvyys-API:t, jotka syöttävät sitä, ovat osa HotXLS Delphi-laskentataulukkokomponenttia, joka toimitetaan täydellisen lähdekoodin kanssa Delphille ja C++Builderille eikä vaadi Excel-asennusta koneella, joka sitä ajaa