Se SUBTOTAL(109, ...) e SUBTOTAL(9, ...) restituiscono lo stesso numero su un workbook che contiene righe nascoste, uno dei due è sbagliato. HotXLS, il componente foglio di calcolo Excel nativo per Delphi e C++Builder, si comportava esattamente così fino alla versione 2.197.0, perché il suo motore di calcolo non aveva modo di chiedere a un foglio se una data riga fosse nascosta
Il sintomo arriva raramente come una segnalazione di bug sui codici delle formule. Arriva come una discrepanza: un job batch sul server calcola un totale, un utente apre lo stesso file in Excel con un filtro applicato, e i due numeri differiscono di qualunque cosa sommassero le righe filtrate via. Nessuno sospetta della funzione di aggregazione, perché la stringa di formula nella cella è identica in entrambi i posti. La differenza sta interamente in ciò che il valutatore aveva il permesso di vedere
Perché SUBTOTAL 109 include le righe nascoste?
Perché nella maggior parte dei design di motore lo strato che valuta una formula non viene mai a sapere della visibilità delle righe. HotXLS era un caso da manuale: il motore di calcolo in lxCalc.pas raggiungeva i valori delle celle tramite un'unica callback TXLSGetValue che risponde con un valore per una tripla (foglio, riga, colonna) e nient'altro. La visibilità è un attributo di presentazione memorizzato sul record di riga, e nessuna parte di quel record viaggiava lungo la catena di chiamata. Il motore aveva quindi un unico percorso di aggregazione, ed entrambe le metà della tabella dei numeri di funzione SUBTOTAL vi si risolvevano. Non è una classe di difetto da errore di arrotondamento: è l'intero motivo per cui esiste la seconda metà della tabella. ECMA-376 Part 1, pubblicato come ISO/IEC 29500-1, definisce SUBTOTAL nelle sue definizioni delle funzioni formula (§18.17.7) con un primo argomento che seleziona sia l'aggregazione interna sia la policy sulle righe nascoste. I codici da 1 a 11 mappano su AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, e VARP includendo i valori sulle righe nascoste manualmente. I codici da 101 a 111 selezionano le stesse undici aggregazioni ed escludono quei valori. Un utente che digita 109 invece di 9 sta facendo un'affermazione deliberata sui dati nascosti, e un motore che collassa la distinzione ignora silenziosamente quell'affermazione
A cosa mappano i numeri di funzione dentro il motore
HotXLS risolve il primo argomento di SUBTOTAL in CalcSubtotalFunc, che normalizza i codici da 101 a 111 sugli stessi identificatori di funzione interna dei codici da 1 a 11 e poi smista sull'aggregazione stessa. La maggior parte della famiglia scorre attraverso l'accumulatore incrementale ExcelSum, quello che gestisce SUM, COUNT, COUNTA, MIN, MAX, e AVERAGE. Cinque di esse non possono: STDEV, VAR, STDEVP, VARP, e PRODUCT necessitano di un passaggio in forma chiusa sui dati, quindi CalcSubtotalFunc instrada i codici interni 12, 46, 193, 194, e 183 verso un riduttore separato, SubtotalReduceVariance. Quella separazione è la prima cosa che vale la pena mappare prima di toccare qualunque cosa, perché due percorsi di aggregazione indipendenti significano due cicli di attraversamento celle indipendenti, e una correzione applicata solo a uno di essi produce il peggior risultato possibile: SUBTOTAL(109, ...) rispetta il filtro mentre SUBTOTAL(107, ...) sullo stesso intervallo no. Contare i cicli in HotXLS ne ha rivelati sei una volta incluso AGGREGATE, distribuiti tra valutazione di intervallo, raccolta di intervallo semplice, e tre riduttori separati
Perché un campo scratch invece di sei nuove firme?
Perché far attraversare un nuovo parametro attraverso sei funzioni di percorrenza celle, più tutto ciò che le chiama, è una modifica estesa a un percorso di codice caldo per il bene di un solo booleano. HotXLS aveva già un precedente per l'alternativa: un campo transitorio sul calcolatore, nello stesso spirito del campo scratch che GetRangeInfo usa per registrare quando un riferimento 3D si risolveva in un workbook esterno. La versione 2.197.0 ne ha aggiunto un secondo. Il motore ha guadagnato un tipo di callback, TXLSIsRowHidden, dichiarato come una funzione di (SheetIndex, row) che restituisce Boolean, memorizzato in FIsRowHidden, più un flag transitorio FIgnoreHiddenRows. Il flag viene armato all'ingresso di CalcSubtotalFunc quando il codice funzione cade tra 101 e 111, e all'ingresso di CalcAggregateFunc per i codici opzione AGGREGATE che selezionano l'esclusione delle righe nascoste. Ogni ciclo di percorrenza celle lo ispeziona poi e salta una riga quando è impostato, aggiungendo una singola riga ciascuno
// 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;Due dettagli nel codice di armamento portano la correttezza dell'intero schema. Il flag viene salvato e ripristinato anziché semplicemente impostato e azzerato, perché un argomento SUBTOTAL può contenere un'espressione che esegue una propria valutazione mentre l'aggregazione esterna è ancora sullo stack, e quel lavoro annidato non deve ereditare né distruggere il cancello esterno. E il ripristino vive in un blocco finally, perché CalcSubtotalFunc ha diverse uscite anticipate per i codici di errore; un flag lasciato armato dopo un ritorno di errore corromperebbe silenziosamente la prossima formula non correlata nell'ordine di ricalcolo
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;Il test Assigned è ciò che mantiene la modifica compatibile. HotXLS ha esteso il costruttore del calcolatore con un terzo parametro il cui default è nil, così qualunque codice che costruisce un TXLSCalculator con la vecchia chiamata a due argomenti compila ancora e ottiene ancora il comportamento legacy di inclusione delle righe nascoste. Nulla della API esistente ha cambiato forma
Da dove viene realmente il bit di riga nascosta?
Dal foglio di lavoro, tramite due sorgenti diverse, perché HotXLS porta due motori di workbook. Il lato BIFF legacy risponde da TXLSRowInfoList.GetHidden, raggiunto tramite TXLSWorkbook.GetRowHidden. Il lato OOXML risponde da TXLSXWorksheet.GetRowHidden, raggiunto tramite TXLSXWorkbook.GetCalcRowHidden. Entrambi sono cablati nel calcolatore al momento della costruzione, accanto alla callback dei valori cella che rispecchiano. Le convenzioni di riga sono dove questo tipo di ponte normalmente sbaglia, quindi vale la pena dichiararle esplicitamente. Il calcolatore passa alla callback una riga 0-based, corrispondente alle coordinate che TXLSGetValue già usa. Il foglio XLSX indicizza la sua mappa di righe nascoste per numero di riga 1-based, esattamente come Excel numera le righe, che è anche ciò che espone la proprietà pubblica RowHidden[ARow]. Il ponte XLSX quindi aggiunge uno prima della ricerca, e il ponte BIFF no, perché TXLSRowInfoList è già 0-based. Entrambi i ponti trattano un indice foglio o una riga fuori dall'intervallo valido come visibile, così una query fuori limite degrada nella vecchia risposta di inclusione anziché scartare dati
Cosa cambia per i workbook filtrati
Questo è il caso che genera i ticket di supporto. Applicare un AutoFilter in HotXLS tramite ApplyAutoFilter valuta i criteri di colonna e nasconde ogni riga dati che non corrisponde, che è esattamente ciò che fa Excel quando un utente clicca un dropdown di filtro. Prima della v2.197.0 quelle righe nascoste erano invisibili all'utente e completamente visibili al motore di calcolo, quindi un SUBTOTAL(109, ...) lato server riportava il totale non filtrato. Ora la stessa chiamata riporta quello filtrato
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;Il nascondimento manuale funziona allo stesso modo, poiché RowHidden[ARow] := True è lo stesso stato che scrive il filtro. Quella equivalenza è deliberata in Excel e ora vale anche in HotXLS. Una conseguenza merita una nota in qualunque documentazione fornita con i tuoi workbook generati: un totale calcolato con codice 109 è un numero dipendente dalla vista, quindi un destinatario che rimuove il filtro lo cambia. Quando un report deve dichiarare una cifra fissa indipendentemente da cosa il lettore fa alla vista, il codice 9 è la scelta corretta e lo è sempre stato. Filtri, validazione e tabelle sono trattati insieme in l'articolo sulla validazione dati, AutoFilter e tabelle. Poiché nascondere righe non tocca alcuna formula, non sporca nemmeno da solo il grafo delle dipendenze, il che vale la pena sapere se ti affidi al ricalcolo incrementale sul sottografo sporco per mantenere reattivi i workbook di grandi dimensioni
Codici opzione AGGREGATE e un limite ancora aperto
AGGREGATE è SUBTOTAL con un secondo argomento di policy, e HotXLS lo gestisce in CalcAggregateFunc. L'argomento opzione codifica interruttori indipendenti: se le chiamate SUBTOTAL e AGGREGATE annidate dentro l'intervallo vengono saltate, se i valori sulle righe nascoste vengono saltati, e se i valori di errore vengono soppressi anziché propagati. HotXLS arma il cancello condiviso delle righe nascoste per i codici opzione 2, 3, 6, e 7, e sopprime i valori di errore per i codici opzione da 4 a 7. L'argomento numero di funzione seleziona poi l'aggregazione esattamente come fa SUBTOTAL, incluso l'instradamento di varianza, deviazione standard e prodotto attraverso i propri riduttori. Rimane una lacuna documentata, ed è meglio dichiararla qui che scoprirla in produzione: la semantica ignore-nested-SUBTOTAL associata ai codici opzione bassi non è implementata in HotXLS. Rilevare un SUBTOTAL annidato dentro un intervallo referenziato richiede marcare lo stato di ricorsione del valutatore così un'aggregazione interna può annunciarsi a quella esterna, il che è una modifica più grande del cancello delle righe nascoste. In pratica l'esposizione è piccola, perché i workbook reali quasi sempre posizionano le formule SUBTOTAL fuori dagli intervalli su cui aggregano altre formule SUBTOTAL. Se il tuo generatore costruisce davvero intervalli di aggregazione sovrapposti, non affidarti ai codici opzione bassi per deduplicarli
La protezione di arità spedita insieme ad essa
La versione 2.197.0 ha chiuso anche una lacuna di validazione nello stesso dispatcher, e la ragione di design è la stessa che ha motivato il campo scratch: mettere il controllo dove può essere scritto una sola volta. Circa 280 corpi di funzione built-in verificavano ciascuno il proprio conteggio di argomenti contro Item.ChildCount, il che non lasciava alcun confine coerente per il caso di troppi argomenti. Una chiamata come =SIN(1,2) raggiungeva un corpo di funzione che esaminava il suo primo argomento, ignorava l'eccedenza, e restituiva un numero plausibile dove Excel restituisce #VALUE!. HotXLS memorizzava già l'arità dichiarata di ogni built-in nel suo registro funzioni, esposta come THashFunc.ArgsCnt con -1 a marcare una funzione variadica come SUM, IF, o CONCAT. La versione 2.197.0 ha inoltrato questo tramite una nuova proprietà TXLSFormula.FuncArgsCntByPtg e ha aggiunto un cancello in cima a GetValueItemFunc, il dispatcher principale
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;La protezione rifiuta troppi argomenti e deliberatamente non dice nulla su troppo pochi. Omettere un argomento opzionale finale è legale in Excel per VLOOKUP, SUBSTITUTE, e una lunga lista di altre funzioni, quindi un controllo simmetrico avrebbe rotto formule corrette per catturarne di scorrette. Gli identificatori sconosciuti si riportano come variadici e saltano il cancello del tutto, il che è ciò che tiene le funzioni definite dall'utente fuori dal suo percorso; se registri le tue funzioni, il comportamento descritto nella guida al motore delle formule e alle funzioni personalizzate non è influenzato. Centralizzare il caso di troppo pochi argomenti è un lavoro separato, perché ognuno di quei 280 corpi ha la propria semantica di codice di errore e devono essere rivisti uno alla volta anziché assunti
Il motore di calcolo descritto qui, entrambe le facciate workbook, e le API di AutoFilter e visibilità riga che lo alimentano fanno parte del componente foglio di calcolo Delphi HotXLS, che viene fornito con il codice sorgente completo per Delphi e C++Builder e non richiede alcuna installazione di Excel sulla macchina che lo esegue