Dacă SUBTOTAL(109, ...) și SUBTOTAL(9, ...) returnează același număr pe un workbook care conține rânduri ascunse, unul dintre cele două e greșit. HotXLS, componenta nativă de foaie de calcul Excel pentru Delphi și C++Builder, s-a comportat exact așa până la versiunea 2.197.0, pentru că motorul lui de calcul nu avea niciun mod să întrebe o foaie de lucru dacă un rând anume era ascuns
Simptomul ajunge rar ca un raport de bug despre codurile de formulă. Ajunge ca o nepotrivire: un job pe server calculează un total, un utilizator deschide același fișier în Excel cu un filtru aplicat, iar cele două numere diferă cu exact cât însumau rândurile filtrate. Nimeni nu suspectează funcția de agregare, pentru că șirul formulei din celulă e identic în ambele locuri. Diferența stă în întregime în ce i s-a permis evaluatorului să vadă
De ce SUBTOTAL 109 include rândurile ascunse?
Pentru că, în majoritatea designurilor de motor, nivelul care evaluează o formulă nu află niciodată nimic despre vizibilitatea rândului. HotXLS era un caz de manual: motorul de calcul din lxCalc.pas ajungea la valorile de celulă printr-un singur callback TXLSGetValue, care răspunde cu o valoare pentru un triplet (sheet, row, column) și nimic altceva. Vizibilitatea e un atribut de prezentare stocat în înregistrarea rândului, și nicio parte a acelei înregistrări nu călătorea de-a lungul lanțului de apeluri. Motorul avea deci o singură cale de agregare, iar ambele jumătăți ale tabelului de numere de funcție SUBTOTAL se rezolvau la ea. Nu e o clasă de defect de eroare de rotunjire: e chiar motivul pentru care există a doua jumătate a tabelului. ECMA-376 Partea 1, publicată ca ISO/IEC 29500-1, definește SUBTOTAL în definițiile funcțiilor de formulă (§18.17.7), cu un prim argument care selectează atât agregarea internă, cât și politica pentru rânduri ascunse. Codurile 1 până la 11 se mapează pe AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR și VARP, incluzând valorile de pe rândurile ascunse manual. Codurile 101 până la 111 selectează aceleași unsprezece agregări și le exclud. Un utilizator care tastează 109 în loc de 9 face o declarație deliberată despre datele ascunse, iar un motor care colapsează distincția anulează silențios acea declarație
La ce se mapează numerele de funcție în interiorul motorului
HotXLS rezolvă primul argument al SUBTOTAL în CalcSubtotalFunc, care normalizează codurile 101 până la 111 pe aceiași identificatori interni de funcție ca și codurile 1 până la 11 și apoi dispatch-uiește pe agregarea în sine. Cea mai mare parte a familiei trece prin acumulatorul incremental ExcelSum, cel care tratează SUM, COUNT, COUNTA, MIN, MAX și AVERAGE. Cinci dintre ele nu pot: STDEV, VAR, STDEVP, VARP și PRODUCT au nevoie de o trecere în formă închisă peste date, așa că CalcSubtotalFunc rutează codurile interne 12, 46, 193, 194 și 183 către un reducer separat, SubtotalReduceVariance. Acea despărțire e primul lucru care merită mapat înainte de a atinge orice altceva, pentru că două căi de agregare independente înseamnă două bucle independente de parcurgere a celulelor, iar un fix aplicat doar uneia produce cel mai rău rezultat posibil: SUBTOTAL(109, ...) respectă filtrul, în timp ce SUBTOTAL(107, ...) pe același interval nu-l respectă. Numărând buclele din HotXLS au ieșit șase, odată ce AGGREGATE a fost inclusă, împrăștiate pe evaluarea de intervale, colectarea simplă de intervale și trei reducere separate
De ce un câmp scratch în loc de șase semnături noi?
Pentru că a fira un parametru nou prin șase funcții de parcurgere a celulelor, plus tot ce le apelează, e o schimbare largă pe o cale de cod fierbinte, de dragul unui singur boolean. HotXLS avea deja un precedent pentru alternativă: un câmp tranzitoriu pe calculator, în același spirit ca și câmpul scratch pe care GetRangeInfo îl folosește ca să înregistreze când o referință 3D s-a rezolvat într-un workbook extern. Versiunea 2.197.0 a adăugat un al doilea. Motorul a câștigat un tip de callback, TXLSIsRowHidden, declarat ca o funcție de (SheetIndex, row) care returnează Boolean, stocat în FIsRowHidden, plus un flag tranzitoriu FIgnoreHiddenRows. Flag-ul e armat la intrarea în CalcSubtotalFunc atunci când codul de funcție cade între 101 și 111, și la intrarea în CalcAggregateFunc pentru codurile de opțiune AGGREGATE care selectează excluderea rândurilor ascunse. Fiecare buclă de parcurgere a celulelor îl inspectează apoi și sare peste un rând atunci când e setat, adăugând o singură linie fiecare
// 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;Două detalii din codul de armare poartă corectitudinea întregii scheme. Flag-ul e salvat și restaurat, nu doar setat și șters, pentru că un argument SUBTOTAL poate conține o expresie care rulează propria ei evaluare cât timp agregarea exterioară e încă pe stivă, iar acea muncă imbricată nu trebuie să moștenească sau să distrugă poarta exterioară. Iar restaurarea trăiește într-un bloc finally, pentru că CalcSubtotalFunc are mai multe ieșiri timpurii pentru coduri de eroare; un flag lăsat armat după o întoarcere de eroare ar corupe silențios următoarea formulă neînrudită din ordinea de recalculare
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;Testul Assigned e ceea ce menține schimbarea compatibilă. HotXLS a extins constructorul calculatorului cu un al treilea parametru, implicit nil, așa că orice cod care construiește un TXLSCalculator cu vechiul apel cu doi argumente tot compilează și tot primește comportamentul vechi de include-hidden. Nimic din forma API-ului existent nu s-a schimbat
De unde vine de fapt bitul de rând ascuns?
De pe foaia de lucru, prin două surse diferite, pentru că HotXLS poartă două motoare de workbook. Partea BIFF veche răspunde din TXLSRowInfoList.GetHidden, atinsă prin TXLSWorkbook.GetRowHidden. Partea OOXML răspunde din TXLSXWorksheet.GetRowHidden, atinsă prin TXLSXWorkbook.GetCalcRowHidden. Ambele sunt cablate în calculator la momentul construcției, alături de callback-ul de valoare de celulă pe care-l oglindesc. Convențiile de rând sunt locul unde acest tip de punte greșește de obicei, așa că merită menționate explicit. Calculatorul dă callback-ului un rând 0-based, potrivindu-se cu coordonatele pe care TXLSGetValue le folosește deja. Foaia de lucru XLSX își indexează harta de rânduri ascunse după numărul de rând 1-based, exact cum numerotează Excel rândurile, ceea ce e și ce expune proprietatea publică RowHidden[ARow]. Puntea XLSX adaugă deci unu înainte de căutare, iar puntea BIFF nu, pentru că TXLSRowInfoList e deja 0-based. Ambele punți tratează un index de sheet sau un rând în afara intervalului valid ca vizibil, așa că o interogare în afara limitelor degradează la vechiul răspuns include-hidden, în loc să piardă date
Ce se schimbă pentru workbook-urile filtrate
Acesta e cazul care generează tichetele de suport. Aplicarea unui AutoFilter în HotXLS prin ApplyAutoFilter evaluează criteriile de coloană și ascunde fiecare rând de date care nu se potrivește, exact ce face Excel când un utilizator dă clic pe un dropdown de filtru. Înainte de v2.197.0, acele rânduri ascunse erau invizibile pentru utilizator și complet vizibile pentru motorul de calcul, așa că un SUBTOTAL(109, ...) pe server raporta totalul nefiltrat. Acum același apel raportează pe cel filtrat
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;Ascunderea manuală funcționează la fel, pentru că RowHidden[ARow] := True e aceeași stare pe care o scrie filtrul. Acea echivalență e deliberată în Excel, și acum se ține și în HotXLS. O consecință merită o notă în orice documentație vine cu workbook-urile tale generate: un total calculat cu codul 109 e un număr dependent de vizualizare, așa că un destinatar care șterge filtrul îl schimbă. Când un raport trebuie să declare o cifră fixă, indiferent ce face cititorul cu vizualizarea, codul 9 e alegerea corectă și a fost mereu. Filtrele, validarea și tabelele sunt tratate împreună în articolul despre validarea datelor, AutoFilter și tabele. Pentru că ascunderea rândurilor nu atinge nicio formulă, nici nu murdărește graful de dependențe de la sine, ceea ce merită știut dacă te bazezi pe recalcularea incrementală peste subgraful murdar ca să menții workbook-uri mari responsive
Codurile de opțiune AGGREGATE și o limită încă deschisă
AGGREGATE e SUBTOTAL cu un al doilea argument de politică, iar HotXLS îl tratează în CalcAggregateFunc. Argumentul de opțiune encodează comutatoare independente: dacă apelurile imbricate de SUBTOTAL și AGGREGATE din interiorul intervalului sunt sărite, dacă valorile de pe rânduri ascunse sunt sărite și dacă valorile de eroare sunt suprimate în loc să fie propagate. HotXLS armează poarta partajată de rânduri ascunse pentru codurile de opțiune 2, 3, 6 și 7, și suprimă valorile de eroare pentru codurile de opțiune 4 până la 7. Argumentul de număr de funcție selectează apoi agregarea exact cum face SUBTOTAL, inclusiv rutarea varianței, deviației standard și produsului prin reducerele lor proprii. Rămâne un decalaj documentat, și e mai bine menționat aici decât descoperit în producție: semantica de ignore-nested-SUBTOTAL asociată codurilor de opțiune joase nu e implementată în HotXLS. Detectarea unui SUBTOTAL imbricat în interiorul unui interval referit cere marcarea stării de recursie a evaluatorului, astfel încât o agregare internă să se poată anunța pe cea exterioară, ceea ce e o schimbare mai mare decât poarta de rânduri ascunse. În practică, expunerea e mică, pentru că workbook-urile reale plasează aproape întotdeauna formulele SUBTOTAL în afara intervalelor peste care agregă alte formule SUBTOTAL. Dacă generatorul tău chiar construiește intervale de agregare suprapuse, nu te baza pe codurile de opțiune joase ca să le deduplici
Garda de aritate livrată alături de asta
Versiunea 2.197.0 a mai închis și un decalaj de validare în același dispatcher, iar motivul de design e același care a motivat câmpul scratch: pune verificarea acolo unde poate fi scrisă o singură dată. Aproximativ 280 de corpuri de funcții built-in verificau fiecare propriul număr de argumente contra Item.ChildCount, ceea ce nu lăsa nicio graniță consistentă pentru cazul cu prea multe argumente. Un apel ca =SIN(1,2) ajungea la un corp de funcție care examina primul ei argument, ignora surplusul și returna un număr plauzibil, acolo unde Excel returnează #VALUE!. HotXLS stoca deja aritatea declarată a fiecărei funcții built-in în registrul ei de funcții, expusă ca THashFunc.ArgsCnt, cu -1 marcând o funcție variadică precum SUM, IF sau CONCAT. Versiunea 2.197.0 a transportat asta printr-o proprietate nouă TXLSFormula.FuncArgsCntByPtg și a adăugat o poartă în vârful lui GetValueItemFunc, dispatcher-ul principal
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;Garda respinge prea multe argumente și deliberat nu spune nimic despre prea puține. Omiterea unui argument opțional final e legală în Excel pentru VLOOKUP, SUBSTITUTE și o listă lungă de altele, așa că o verificare simetrică ar fi stricat formule corecte ca să le prindă pe cele incorecte. Identificatorii necunoscuți raportează ca variadici și sar peste poartă în întregime, ceea ce ține funcțiile definite de utilizator departe de calea ei; dacă îți înregistrezi propriile funcții, comportamentul descris în ghidul motorului de formule și funcțiilor personalizate rămâne neafectat. Centralizarea cazului cu prea puține e o muncă separată, pentru că fiecare dintre acele 280 de corpuri are propria ei semantică de cod de eroare, iar ele trebuie revizuite unul câte unul, nu presupuse
Motorul de calcul descris aici, ambele fațade de workbook și API-urile de AutoFilter și vizibilitate de rând care îl alimentează fac parte din componenta de foaie de calcul HotXLS pentru Delphi, care este livrată cu sursă completă pentru Delphi și C++Builder și nu cere nicio instalare de Excel pe mașina pe care rulează