Due copie della stessa cartella di lavoro trimestrale possono mostrare entrambe 1.250,00 nella cella D2 e continuare a non essere d'accordo su cosa quella cella sia: in una è un letterale digitato, nell'altra è =SUM(A2:C2). Un revisore che passa da un file all'altro vede schermate identiche. Un confronto byte per byte dei due pacchetti .xlsx va nella direzione opposta e riporta quasi tutto come diverso, perché un archivio zip scritto due volte raramente coincide byte per byte. Nessuno dei due controlli risponde alla domanda che le persone si pongono davvero dopo un giro di revisione, cioè quali celle, formule e fogli sono cambiati
Quella domanda emerge in pochi punti prevedibili. Una cartella di lavoro finanziaria torna da una revisione, un'esportazione notturna viene rigenerata dopo una modifica al codice, o due reparti restituiscono le loro modifiche allo stesso modello. L'ispezione affiancata va bene per un foglio ed è disperata per venti, ed è esattamente lo scambio formula-contro-letterale di cui sopra che un paio di occhi stanchi non vede
Confrontare due cartelle di lavoro con TXLSXWorkbookCompare
HotXLS confronta le cartelle di lavoro tramite TXLSXWorkbookCompare nell'unità lxCompare. Compare(Left, Right) prende due oggetti TXLSXWorkbook caricati e restituisce il numero di differenze trovate. L'elenco delle differenze è quindi disponibile per indice tramite Diff(I) e Count, e Report aggiunge una riga leggibile per differenza a qualsiasi TStrings. Excel non è coinvolto in nessun punto; il confronto gira sul modello a oggetti che HotXLS ha già caricato, quindi funziona allo stesso modo in un job da console, in un servizio o in un'applicazione C++Builder
uses
SysUtils, Classes, lxHandleX, lxCompare;
var
Left, Right: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare;
Lines: TStringList;
begin
Left := TXLSXWorkbook.Create;
Right := TXLSXWorkbook.Create;
Cmp := TXLSXWorkbookCompare.Create;
Lines := TStringList.Create;
try
// Open restituisce 1 in caso di successo; un open fallito deve fermarsi qui,
// altrimenti viene confrontata una cartella vuota e il risultato non dice niente
if Left.Open('baseline.xlsx') <> 1 then
raise Exception.Create('cannot open baseline.xlsx');
if Right.Open('reviewed.xlsx') <> 1 then
raise Exception.Create('cannot open reviewed.xlsx');
if Cmp.Compare(Left, Right) = 0 then
Writeln('workbooks are equivalent')
else
begin
Cmp.Report(Lines); // una riga leggibile per differenza
Lines.SaveToFile('workbook-diff.txt');
Writeln(Format('%d difference(s) written', [Cmp.Count]));
end;
finally
Lines.Free;
Cmp.Free;
Right.Free;
Left.Free;
end;
end;
I controlli di open espliciti contano più di quanto sembri. Compare restituisce 0 e svuota il suo elenco quando uno dei due argomenti è nil, e due cartelle di lavoro vuote confrontate risultano uguali anche loro, così un risultato zero significa "equivalente" solo quando entrambi i file sono realmente caricati. Una riga tipica di workbook-diff.txt suona così: value changed: Data!A2: 10 -> 99
Cosa conta come differenza per impostazione predefinita?
Senza opzioni impostate, TXLSXWorkbookCompare riporta otto tipi di differenza: un foglio aggiunto o rimosso, una cella aggiunta o rimossa, un valore cambiato, una formula cambiata, e un intervallo unito aggiunto o rimosso. I valori dell'enum sono lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded e lckMergeRemoved. Tutto è misurato contro la cartella di lavoro di sinistra come baseline, quindi un elemento aggiunto esiste solo a destra e un elemento rimosso solo a sinistra
I fogli sono accoppiati per nome, e l'abbinamento ignora le maiuscole, così Data a sinistra si accoppia con DATA a destra. Trascinare una scheda in una nuova posizione quindi non produce voci. Rinominare un foglio è un'altra storia: Q3 rinominato in Q3 Final compare come una riga sheet removed e una riga sheet added, perché nulla nel file registra che i due sono lo stesso foglio. Un foglio che esiste solo su un lato produce una singola voce a livello di foglio anziché una riga per ogni cella al suo interno, il che mantiene il report leggibile quando due cartelle di lavoro differiscono nella struttura
Le celle sono accoppiate per posizione. Il confronto percorre le celle conservate di ogni foglio accoppiato, e questo include le celle che contengono solo formattazione. Una cella formattata ma vuota che esiste a sinistra e non a destra viene riportata come cell removed: Data!C4 was (blank). Gli intervalli uniti si confrontano per il loro testo A1, così A1:D1 allargato a A1:E1 produce un merge removed e un merge added
Perché una formula e un letterale con lo stesso numero riportano una modifica?
Perché la formula è il contenuto scritto dall'autore. Quando uno dei due lati di una coppia di celle contiene una formula, TXLSXWorkbookCompare confronta il testo della formula, e una formula da un lato contro un letterale dall'altro è una voce lckFormulaChanged anche se entrambe mostrano 1.250. La riga del report per l'esempio iniziale è formula changed: Data!D2: 1250 -> =SUM(A2:C2), e il record porta LeftKind = lcvNumber e RightKind = lcvFormula. Il testo della formula viene confrontato esattamente come conservato, con un segno di uguale iniziale aggiunto per la visualizzazione
I valori letterali si confrontano come valori digitati. Un numero si confronta con un numero, testo con testo, e un numero 10 contro il testo "10" è una modifica anche se entrambe le celle stampano gli stessi caratteri. Il report mostra le etichette di tipo ogni volta che i tipi differiscono: value changed: Data!B7: [number] 10 -> [text] 10. Quel caso è comune nei file che passano per andate e ritorni CSV o copia-incolla degli utenti, ed è quello che poi rompe le ricerche
Una formula il cui testo è invariato non riporta nulla per impostazione predefinita, anche quando il suo risultato in cache differisce. Per la revisione di contenuto scritto è corretto. Per i test di regressione di un generatore è troppo permissivo, dato che un foglio di input cambiato può spostare ogni totale senza toccare una sola formula. Impostare lcoFormulaCachedResults in Options aggiunge una voce lckFormulaCacheChanged ogni volta che i risultati in cache differiscono, o quando un lato ha un valore in cache e l'altro no, riportato come (missing cache). Per i test di regressione dei generatori, attivate quell'opzione; un totale che deriva con formule invariate è esattamente il guasto che quei test esistono per cogliere
I risultati in cache sono freschi quanto l'ultimo calcolo, quindi ricalcolate prima entrambe le cartelle di lavoro quando la domanda è "cosa producono queste formule oggi". TXLSXWorkbook.Recalculate restituisce lxOk in caso di successo; con l'iterazione disattivata, un ciclo di riferimenti restituisce lxErrorRef. Il rumore in virgola mobile è l'altra sorgente di falsi allarmi, e SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) lo gestisce allo stesso modo per letterali numerici e risultati in cache:
uses
SysUtils, lxCalc, lxHandleX, lxCompare;
function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare): Integer;
begin
// Porta entrambe le cache aggiornate prima di confrontare i risultati
if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
raise Exception.Create('recalculation failed');
Cmp.Options := [lcoFormulaCachedResults];
// 1e-9 assoluto o 1e-12 relativo; entrambi devono essere finiti e non negativi,
// altrimenti viene sollevata EArgumentException e nessun valore cambia
Cmp.SetNumericTolerance(1e-9, 1e-12);
Result := Cmp.Compare(Expected, Actual);
end;
Le tolleranze valgono zero per impostazione predefinita, il che significa confronto esatto. Si applicano solo ai numeri. Date, geometria di righe e colonne e stili si confrontano sempre esattamente, quindi una tolleranza tarata per l'arrotondamento di valute non nasconde mai una data spostata di un secondo
Guidare la logica dalle differenze strutturate
Ogni record TlxCompareDiff espone Kind, Sheet, Row e Col a base uno, un Ref A1, LeftText e RightText, e per le voci di cella LeftKind e RightKind. Le voci a livello di foglio e di unione portano zero in Row e Col. Una volta attivati i controlli opzionali, le voci di geometria delle righe portano solo una riga, quelle di geometria delle colonne solo una colonna, e le funzionalità della cartella di lavoro come i nomi definiti non portano nessuna delle due, quindi ramificate su Kind anziché su coordinate a zero:
var
I, FormulaEdits: Integer;
D: TlxCompareDiff;
begin
FormulaEdits := 0;
for I := 0 to Cmp.Count - 1 do
begin
D := Cmp.Diff(I);
case D.Kind of
lckFormulaChanged:
begin
Inc(FormulaEdits);
Writeln(Format('%s!%s: %s => %s',
[D.Sheet, D.Ref, D.LeftText, D.RightText]));
end;
lckValueChanged:
if D.LeftKind <> D.RightKind then
Writeln('type change: ' + D.Describe);
lckSheetAdded, lckSheetRemoved:
Writeln('structure: ' + D.Describe);
lckMergeAdded, lckMergeRemoved:
Writeln('layout: ' + D.Describe);
end;
end;
// Una policy di revisione che blocca solo sulle modifiche alle formule
if FormulaEdits > 0 then
raise Exception.CreateFmt(
'%d formula change(s) need sign-off', [FormulaEdits]);
end;
L'ordine dell'elenco è fisso. Compare ordina le differenze per nome del foglio, poi riga, colonna, tipo e riferimento, quindi due esecuzioni sulla stessa coppia di file producono lo stesso testo di report, riga per riga. I nomi dei fogli si ordinano per confronto ordinale di stringa, non per posizione della scheda, e le voci con riga zero vengono prima dentro il gruppo di ogni foglio. Questo rende il testo del report un buon golden file per un test di regressione: committate il workbook-diff.txt atteso e fate fallire la build quando il nuovo differisce. Per le decisioni di policy, leggete i campi del record direttamente e lasciate la riga descrittiva agli umani
Attivare i controlli di formattazione, layout e oggetti
L'insieme predefinito è un confronto di dati, e una cella il cui formato numerico è passato da Generale a Valuta non riporta nulla sotto di esso. La proprietà Options allarga il confronto una categoria alla volta. lcoCellStyles confronta lo stile risolto di ogni cella accoppiata e lcoBlankStyleRegions fa lo stesso per le aree formattate che non hanno celle conservate, fondendo le differenze adiacenti in rettangoli il cui angolo lontano sta in LastRow e LastCol. lcoAxisGeometry confronta le altezze delle righe e le larghezze delle colonne insieme allo stato hidden, outline level e collapsed. lcoRichTextRuns confronta le sequenze formattate dentro le celle di testo
Il contenuto a livello di oggetto ha i suoi interruttori: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (che copre anche le immagini dentro le celle), lcoPivotTables, lcoPivotCaches, lcoSlicerState e lcoTimelineState. Quelle voci mettono la chiave della funzionalità in Ref e dicono tramite Change se è stata un'aggiunta, una rimozione o una modifica. Nomi definiti, pivot cache e le cache di slicer e timeline a livello di cartella portano uno Sheet vuoto
Leggete l'output di questi controlli con le giuste aspettative. Le voci di stile, rich text e funzionalità riportano digest SHA-256 di una definizione normalizzata in LeftText e RightText, quindi il report dice che lo stile della cella C5 è cambiato ma non quale proprietà. Le voci di geometria sono leggibili, per esempio size=15; hidden=0; outline=0; collapsed=0, e una riga senza altezza esplicita si confronta all'altezza standard del foglio, così una riga esplicita di 15 punti coincide con una non impostata quando anche l'altezza standard è 15. Tenete lcoCellStyles spento per le revisioni di contenuto. Su una cartella di lavoro grande trasforma ogni colonna riformattata in una parete di hash che nasconde le tre modifiche alle celle per cui era stato chiesto l'ok al revisore
// Revisione del layout: geometria, unioni e formati condizionali soltanto
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Limita ciò che i controlli sugli oggetti possono catturare: 32 MiB, 500.000 elementi
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
Cmp.Compare(Left, Right);
except
on E: EXLSXSemanticBudget do
Writeln('workbooks too large for this check: ' + E.Message);
end;
I controlli sugli oggetti catturano le loro definizioni prima di confrontare, e SetFeatureLimits delimita quella cattura. I valori predefiniti sono 67.108.864 byte e 1.000.000 di elementi; superare uno dei due solleva EXLSXSemanticBudget. Quando Compare solleva, l'elenco delle differenze della precedente chiamata riuscita resta al suo posto, quindi un chiamante che cattura l'eccezione non dovrebbe leggere Count come risultato dell'esecuzione fallita
Date, righe inserite e dove il diff ripaga
Le celle che la cartella di lavoro conserva come date si confrontano come date, esattamente, e si stampano come date(...) con il valore numerico della data sottostante. Una data da un lato contro un numero puro dall'altro mostra le etichette di tipo, il segnale usuale che una colonna ha perso il suo formato data lungo il passaggio per qualche altro strumento. I sistemi di date misti sono la trappola qui. Una cartella di lavoro sul sistema di date 1904 e una sul sistema 1900 conservano lo stesso giorno di calendario come serial distanti 1.462, quindi controllate Date1904 su entrambi i lati prima di fidarvi di una colonna di modifiche di date. Lo sfondo è in numeri seriali di data e il sistema 1904
L'accoppiamento posizionale ha una conseguenza che vale la pena pianificare. Inserite una riga vicino alla cima di un foglio e ogni cella sotto di essa si sposta, così il confronto riporta una sequenza di celle cambiate, aggiunte e rimosse lungo il foglio, e le formule i cui riferimenti sono stati riscritti dall'inserimento riportano come cambiate. Quando il pipeline lo controllate voi, eseguite un confronto prima della modifica strutturale e un altro dopo, così ogni report copre un solo tipo di modifica. Le regole di riscrittura sono trattate in adeguamento dei riferimenti di formula su inserimento ed eliminazione
Tre lavori giustificano l'integrarlo. Test di regressione di un generatore di report: conservate una cartella di lavoro nota come buona, rigenerate, confrontate con i risultati in cache attivi, e fate fallire su qualsiasi differenza. Revisione delle modifiche: consegnate al revisore il report ordinato invece di due file. Verifiche di migrazione: dopo aver convertito un lotto di cartelle di lavoro legacy, confrontate ogni risultato con la propria sorgente, poi contate formattazione e funzionalità degli oggetti su ciascun lato con il passaggio di inventario di il banco di audit e conversione delle cartelle di lavoro
Confronto, ricalcolo e conversione condividono un unico motore in HotXLS per Delphi e C++Builder, e la pagina del componente foglio di calcolo HotXLS Delphi elenca il resto di ciò che quel motore legge e scrive