Se SUBTOTAL(109, ...) e SUBTOTAL(9, ...) restituiscono lo stesso numero su un workbook che contiene righe nascoste, una delle due è sbagliata. È una delle trappole più facili quando si costruisce un motore di calcolo per fogli di calcolo, e HotXLS, il componente nativo per fogli di calcolo Excel per Delphi e C++Builder, la evita lasciando che il suo motore di calcolo chieda al foglio di lavoro se una data riga è nascosta
Il sintomo raramente arriva come una domanda 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 quanto le righe filtrate via erano per caso. Nessuno sospetta la funzione di aggregazione, perché la stringa della formula nella cella è identica in entrambi i posti. La differenza sta tutta in ciò che l'evaluator ha avuto il permesso di vedere
Perché SUBTOTAL 109 includerebbe le righe nascoste?
Perché nella maggior parte dei progetti di motori lo strato che valuta una formula non viene mai a sapere della visibilità delle righe. Il progetto ovvio raggiunge i valori delle celle attraverso un'unica callback di valore, come la callback TXLSGetValue in lxCalc.pas, che risponde con un valore per una terna (foglio, riga, colonna) e nient'altro. La visibilità è un attributo di presentazione memorizzato sul record di riga, e se nient'altro viaggia giù per la catena di chiamate, il motore finisce con un unico percorso di aggregazione su cui entrambe le metà della tabella dei numeri di funzione SUBTOTAL si risolvono. Non è uno sbaglio della categoria errore di arrotondamento: la politica sulle righe nascoste è l'intera ragione 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 di formula (§18.17.7) con un primo argomento che seleziona sia l'aggregazione interna sia la politica 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 e le escludono. Un utente che digita 109 invece di 9 sta facendo una dichiarazione deliberata sui dati nascosti, e un motore che appiattisce la distinzione la sopraffà in silenzio
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 dispatcha sull'aggregazione stessa. La maggior parte della famiglia passa per l'accumulatore incrementale ExcelSum, quello che gestisce SUM, COUNT, COUNTA, MIN, MAX e AVERAGE. Cinque non possono: STDEV, VAR, STDEVP, VARP e PRODUCT richiedono un passo in forma chiusa sui dati, quindi CalcSubtotalFunc instrada i codici interni 12, 46, 193, 194 e 183 a un reducer separato, SubtotalReduceVariance. Quella divisione è la prima cosa che vale la pena mappare prima di toccare qualsiasi cosa, perché due percorsi di aggregazione indipendenti significano due cicli di visita delle celle indipendenti, e un controllo sulle righe nascoste aggiunto a uno solo di loro produce il peggior risultato possibile: SUBTOTAL(109, ...) rispetta il filtro mentre SUBTOTAL(107, ...) sullo stesso intervallo no. HotXLS ne ha sei una volta incluso AGGREGATE, sparsi fra valutazione di intervalli, raccolta semplice di intervalli e tre reducer separati
Perché un campo scratch invece di sei nuove firme?
Perché infilare un nuovo parametro attraverso sei funzioni di visita delle celle, più tutto ciò che le chiama, è una modifica ampia a un percorso di codice caldo per il bene di un boolean. HotXLS ha un precedente per l'alternativa: un campo transitorio sul calculator, nello stesso spirito del campo di lavoro che GetRangeInfo usa per registrare quando un riferimento 3D si è risolto in un workbook esterno. Il gate delle righe nascoste è il secondo: un tipo di callback, TXLSIsRowHidden, dichiarato come funzione di (SheetIndex, riga) che restituisce Boolean, memorizzato in FIsRowHidden, più un flag transitorio FIgnoreHiddenRows. Il flag viene armato all'ingresso di CalcSubtotalFunc quando il codice di funzione cade fra 101 e 111, e all'ingresso di CalcAggregateFunc per i codici di opzione AGGREGATE che selezionano l'esclusione delle righe nascoste. Ogni ciclo di visita delle celle poi lo ispeziona e salta una riga quando è impostato, al costo di una singola riga ciascuno
// La forma ripetuta in tutti e sei i cicli di percorrenza delle celle
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
// ... aggrega Cells[rr, cc] nell'accumulatore ...
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
// aggrega su Item.Child[2] .. Item.Child[ChildCount]
// ogni percorso Exit sottostante è coperto dal finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Il test Assigned è ciò che tiene compatibile l'API del calculator. Il costruttore di TXLSCalculator prende un terzo parametro che vale nil per default, così il codice che costruisce un calculator con la chiamata a due argomenti compila invariato e ottiene il comportamento include-hidden
Da dove viene realmente il bit di riga nascosta?
Dal foglio di lavoro, attraverso due sorgenti diverse, perché HotXLS porta due motori di workbook. Il lato BIFF legacy risponde da TXLSRowInfoList.GetHidden, raggiunto attraverso TXLSWorkbook.GetRowHidden. Il lato OOXML risponde da TXLSXWorksheet.GetRowHidden, raggiunto attraverso TXLSXWorkbook.GetCalcRowHidden. Entrambi vengono cablati nel calculator alla costruzione, accanto alla callback del valore di cella che rispecchiano. Le convenzioni sulle righe sono dove questo tipo di ponte di solito va storto, quindi valgono la pena essere enunciate esplicitamente. Il calculator consegna alla callback una riga 0-based, in linea con le coordinate che TXLSGetValue usa già. Il foglio XLSX indicizza la sua mappa delle 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 di foglio o una riga fuori dall'intervallo valido come visibile, così una query fuori bounds degenera nella risposta include-hidden invece di buttare dati
Cosa cambia per i workbook filtrati
Questo è il caso in cui la politica sulle righe nascoste conta di più. Applicare un AutoFilter in HotXLS attraverso ApplyAutoFilter valuta i criteri di colonna e nasconde ogni riga di dati che non combacia, che è precisamente ciò che Excel fa quando un utente clicca un menu a tendina del filtro. Quelle righe sono invisibili all'utente, e poiché il motore di calcolo consulta lo stesso stato delle righe, un SUBTOTAL(109, ...) lato server riporta il totale filtrato invece di quello non 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; // nasconde le righe che non combaciano
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// Il valore della cella concorda con ciò che Excel mostra per lo stesso filtro,
// e VisibleRows vi dice quante righe ci sono entrate
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;L'inserimento manuale funziona allo stesso modo, dato che RowHidden[ARow] := True è lo stesso stato che il filtro scrive. Quell'equivalenza è deliberata in Excel e vale anche in HotXLS. Una conseguenza merita una nota in qualunque documentazione viaggia con i vostri workbook generati: un totale calcolato con il codice 109 è un numero dipendente dalla vista, quindi un destinatario che azzera il filtro lo cambia. Quando un report deve dichiarare una cifra fissa a prescindere da ciò che 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 su validazione dei dati, AutoFilter e tabelle. Poiché nascondere righe non tocca nessuna formula, non sporca da sola nemmeno il grafo delle dipendenze, il che vale la pena sapere se vi affidate al ricalcolo incrementale sul sotto-grafico sporco per tenere reattivi i workbook grandi
Codici di opzione AGGREGATE
AGGREGATE è SUBTOTAL con un secondo argomento di politica, e HotXLS lo gestisce in CalcAggregateFunc. L'argomento opzioni 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 invece che propagati. HotXLS arma il gate condiviso delle righe nascoste per i codici di opzione 2, 3, 6 e 7, e sopprime i valori di errore per i codici di opzione da 4 a 7. L'argomento numero di funzione poi seleziona l'aggregazione esattamente come fa SUBTOTAL, compreso l'instradamento di varianza, deviazione standard e prodotto attraverso i loro reducer dedicati. I workbook reali quasi sempre piazzano le formule SUBTOTAL fuori dagli intervalli su cui altre formule SUBTOTAL aggregano; se il vostro generatore costruisce intervalli di aggregazione sovrapposti, tenete i totali interni fuori dall'intervallo esterno invece di contare sui codici di opzione per deduplicarli
Una guardia di arietà nello stesso dispatcher
Lo stesso dispatcher porta un controllo sul numero di argomenti, e la ragione di progetto è la stessa dietro al campo di lavoro: mettere il controllo dove può essere scritto una volta sola. Con circa 280 corpi di funzione incorporati, lasciare che ognuno verifichi il proprio numero di argomenti contro Item.ChildCount non dà nessun confine coerente per il caso di troppi argomenti, e la trappola è facile da perdere: una chiamata come =SIN(1,2) raggiunge un corpo di funzione che esamina il suo primo argomento, ignora il surplus, e restituisce un numero plausibile dove Excel restituisce #VALUE!. HotXLS memorizza l'arietà dichiarata di ogni funzione incorporata nel suo registro delle funzioni, esposta come THashFunc.ArgsCnt con -1 che marca una funzione variadica come SUM, IF o CONCAT, la inoltra attraverso la proprietà TXLSFormula.FuncArgsCntByPtg, e la controlla in un solo gate in cima a GetValueItemFunc, il dispatcher principale
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] è il nodo funzione
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) dà #VALUE!
Exit;
end;
end;La guardia rifiuta troppi argomenti e deliberatamente non dice niente sui troppo pochi. Omettere un argomento opzionale in coda è legale in Excel per VLOOKUP, SUBSTITUTE e una lunga lista di altri, quindi un controllo simmetrico romperebbe formule corrette per beccarne di sbagliate. Gli identificatori sconosciuti si riportano come variadici e saltano del tutto il gate, che è ciò che tiene fuori le funzioni definite dall'utente; se registrate le vostre funzioni, il comportamento descritto in la guida al motore di formule e alle funzioni personalizzate non è toccato. Il caso troppo pochi resta a ogni corpo di funzione, perché ognuno di quei corpi ha la sua propria semantica di codice di errore
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