Articolo tecnico

Confrontare due cartelle di lavoro Excel in Delphi

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

Diagramma di panoramica del confronto di workbook HotXLS in Delphi: baseline.xlsx e reviewed.xlsx alimentano TXLSXWorkbookCompare, che accoppia i fogli per nome, percorre le celle popolate, ed emette record di differenze strutturati più un rapporto leggibile
TXLSXWorkbookCompare trasforma due cartelle di lavoro HotXLS caricate in un'unica lista diff strutturata per la logica e un unico report leggibile per i revisori

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

Diagramma degli otto tipi di differenza workbook HotXLS raggruppati a livello foglio, cella e unione, ognuno misurato contro il workbook di sinistra come baseline, con riga e colonna zero che marcano le voci a livello foglio e a livello unione
Per impostazione predefinita HotXLS riporta otto tipi di differenza su tre livelli, tutti espressi rispetto alla cartella di lavoro di sinistra, così riordinare i fogli non riporta affatto nulla

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