Compară două registre de lucru Excel în Delphi cu HotXLS

HotXLS compară două registre de lucru prin TXLSXWorkbookCompare, care asociază foile de lucru după nume, parcurge celulele populate din fiecare pereche și raportează ce diferă ca o listă structurată de înregistrări de diferență și, la cerere, ca un rând lizibil per diferență. Nu este implicată nicio instalare de Excel, iar comparația rulează în întregime pe modelul de obiecte încărcat în Delphi sau C++Builder

Nevoia apare de obicei prima dată când cineva întreabă ce s-a schimbat. Un registru de lucru financiar se întoarce de la revizuire, un export nocturn este regenerat după o modificare de cod, sau două departamente trimit versiuni ale aceluiași șablon. Deschiderea ambelor alăturat funcționează pentru o foaie și eșuează pentru douăzeci. Compararea fișierelor octet cu octet nu răspunde la nimic, deoarece două salvări ale aceluiași registru de lucru diferă în moduri de care nimănui nu-i pasă

Ce se consideră diferență?

Comparația raportează opt tipuri, iar setul este deliberat mic: o foaie adăugată sau eliminată, o celulă populată adăugată sau eliminată, o celulă a cărei valoare s-a schimbat, o celulă a cărei formulă s-a schimbat și un interval combinat adăugat sau eliminat. Totul este exprimat față de registrul de lucru din stânga ca linie de bază, așa că un element adăugat există doar în dreapta, iar unul eliminat doar în stânga

Foile sunt asociate după nume, nu după poziție. Reordonarea foilor de lucru nu produce așadar nicio diferență, ceea ce este aproape întotdeauna comportamentul dorit: un utilizator care trage o filă nu este o schimbare de date. O foaie prezentă doar pe o parte raportează o singură intrare la nivel de foaie, în loc să extindă fiecare celulă populată din interior, ceea ce menține lizibil un raport pentru două registre de lucru structural diferite, în loc să aibă mii de rânduri

Valoare sau formulă, și cum se compară fiecare

Fiecare celulă contribuie cu o semnătură, iar regula este simplă: o celulă care poartă o formulă se compară după textul formulei cu un semn egal la început, iar o celulă fără formulă se compară după valoarea ei convertită în text. Această distincție contează mai mult decât pare la prima vedere. Două celule pot avea același număr afișat în timp ce una este un literal, iar cealaltă o formulă, iar tratarea lor ca egale ar ascunde exact editarea cea mai importantă de prins într-un registru de lucru revizuit

Mai înseamnă și că o formulă al cărei text este neschimbat nu raportează nicio diferență, chiar dacă rezultatul ei cache diferă, ceea ce este comportamentul corect pentru compararea conținutului creat, dar comportamentul greșit dacă încerci să detectezi derivă de recalculare. Pentru acea a doua întrebare, recalculează ambele registre de lucru înainte de a compara, astfel încât valorile pe care le compari sunt cele pe care formulele le produc efectiv azi

Rularea unei comparații

Compare primește cele două registre de lucru încărcate și returnează numărul de diferențe găsite. Lista de diferențe este apoi disponibilă după index, sau poate fi descărcată în orice TStrings:

uses
  lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    if (Left.Open('baseline.xlsx') <> 1) or
       (Right.Open('reviewed.xlsx') <> 1) then
      Exit;

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // un rând lizibil per diferență
      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;

Un rând produs de Report arată ca value changed: Data!A2: 10 -> 99, ceea ce este suficient pentru un recenzent și suficient pentru un mesaj de commit. Aceasta este fața umană. Suprafața programatică este chiar înregistrarea de diferență, și este cea de folosit când comparația alimentează o decizie, nu un document

Conducerea logicii din diferențele structurate

Fiecare diferență expune tipul ei, numele foii, rândul și coloana bazate pe unu pentru intrările la nivel de celulă, o referință A1 pentru intrările la nivel de combinare, precum și textul din stânga și din dreapta. Intrările la nivel de foaie și la nivel de combinare raportează rândul și coloana ca zero, ceea ce e cum le deosebești fără să inspectezi tipul:

var
  I: Integer;
  D: TlxCompareDiff;
  FormulaEdits: Integer;
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 R%dC%d: %s => %s',
            [D.Sheet, D.Row, D.Col, D.LeftText, D.RightText]));
        end;
      lckSheetAdded, lckSheetRemoved:
        Writeln(Format('structure: %s', [D.Describe]));
      lckMergeAdded, lckMergeRemoved:
        Writeln(Format('layout: %s at %s', [D.Describe, D.Ref]));
    end;
  end;

  // O politică de revizuire care blochează doar pe editările de formulă
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Două proprietăți ale rezultatului merită cunoscute înainte să scrii afirmații (assertions) pe baza lui. Ordinea intrărilor la nivel de celulă urmează ordinea internă de parcurgere a magaziei de celule, așa că testele ar trebui scrise independent de ordine. Iar semnătura de formulă poartă propriul semn egal la început, ceea ce înseamnă că un șir descriptiv construit prin concatenare poate arăta un == dublat; verifică valorile câmpurilor, nu analiza rândului descriptiv, atunci când rezultatul conduce logica

Unde compararea registrelor de lucru dă roade

Trei utilizări justifică singure funcția. Testarea de regresie a unui generator de rapoarte: păstrează un registru de lucru cunoscut ca bun, regenerează, compară și eșuează build-ul la orice diferență neașteptată. Revizuirea schimbărilor: dă-i unui recenzent raportul lizibil în loc de două fișiere. Iar verificarea migrării: după convertirea unui lot de registre de lucru vechi, compară fiecare rezultat cu sursa lui pentru a dovedi că nimic nu s-a pierdut

Acel al treilea caz se combină natural cu trecerile de inventariere și audit descrise în banca de lucru pentru audit și conversie a registrelor de lucru, unde numărarea a ce conține un registru de lucru se întâmplă înainte de conversie, iar comparația după. Dacă diferențele tale se grupează în jurul rândurilor inserate, regulile de rescriere a referințelor din ajustarea referințelor de formulă la inserare și ștergere explică de ce formulele care par neschimbate raportează ca schimbate

Limitele, spuse explicit

Comparația acoperă valori, formule, combinări și prezența foilor. Nu compară formate de numere, fonturi, umpleri, reguli de formatare condiționată, validări de date, grafice, imagini sau nume definite. O celulă a cărei valoare este identică, dar al cărei format s-a schimbat din General în Currency, nu raportează nicio diferență, ceea ce este corect pentru o comparație de date și insuficient pentru o revizuire de formatare

Celulele cu valoare de dată merită o atenționare specifică: se compară după conversia lor în text, așa că un registru de lucru stocat pe sistemul de dată 1904 și unul pe sistemul 1900 pot compara egal sau inegal în moduri care surprind dacă numerele seriale subiacente diferă. Regulile sistemului de dată sunt acoperite în numerele seriale de dată și sistemul 1904. Când formatarea sau fidelitatea la nivel de obiect fac parte din întrebare, combină diferența cu o trecere de audit care numără acele funcții pe fiecare parte

Compararea registrelor de lucru, auditul și conversia rulează pe același motor pentru Delphi și C++Builder; lista completă de funcții este pe pagina componentei de foi de calcul HotXLS pentru Delphi