Diffa två Excel-arbetsböcker i Delphi med HotXLS

HotXLS jämför två arbetsböcker via TXLSXWorkbookCompare, som parar ihop kalkylblad efter namn, går igenom de fyllda cellerna i varje par, och rapporterar vad som skiljer sig som en strukturerad lista av skillnadsposter och, på begäran, som en läsbar rad per skillnad. Ingen Excel-installation är inblandad, och jämförelsen körs helt på den inlästa objektmodellen i Delphi eller C++Builder

Behovet dyker vanligtvis upp första gången någon frågar vad som ändrats. En finansarbetsbok kommer tillbaka från granskning, en nattlig export regenereras efter en kodändring, eller två avdelningar skickar versioner av samma mall. Att öppna båda sida vid sida fungerar för ett blad och misslyckas för tjugo. Att jämföra filer byte för byte svarar på ingenting alls, eftersom två sparningar av samma arbetsbok skiljer sig på sätt ingen bryr sig om

Vad räknas som en skillnad?

Jämförelsen rapporterar åtta typer, och uppsättningen är medvetet liten: ett blad tillagt eller borttaget, en fylld cell tillagd eller borttagen, en cell vars värde ändrats, en cell vars formel ändrats, och ett sammanslaget intervall tillagt eller borttaget. Allt uttrycks mot den vänstra arbetsboken som baslinje, så ett tillagt objekt finns bara på höger sida och ett borttaget objekt bara på vänster

Blad paras ihop efter namn snarare än efter position. Att ordna om kalkylblad producerar därför inga skillnader alls, vilket nästan alltid är det beteende du vill ha: en användare som drar en flik är ingen dataändring. Ett blad som bara finns på ena sidan rapporterar en post på bladnivå snarare än att expandera varje fylld cell i det, vilket håller en rapport för två strukturellt olika arbetsböcker läsbar istället för tusentals rader lång

Värde eller formel, och hur vardera jämförs

Varje cell bidrar med en signatur, och regeln är enkel: en cell som bär en formel jämförs efter sin formeltext med ett inledande likhetstecken, och en cell utan en sådan jämförs efter sitt värde konverterat till text. Den distinktionen spelar en större roll än den verkar vid första anblicken. Två celler kan bära samma visade tal medan den ena är en literal och den andra en formel, och att behandla dem som lika skulle dölja precis den redigering som är mest värd att fånga i en granskad arbetsbok

Det innebär också att en formel vars text är oförändrad inte rapporterar någon skillnad även om dess cachade resultat skiljer sig, vilket är det korrekta beteendet för att jämföra auktorerat innehåll, och det felaktiga beteendet om du försöker upptäcka omräkningsdrift. För den andra frågan, räkna om båda arbetsböckerna innan du jämför, så att de värden du jämför är de formlerna faktiskt producerar idag

Att köra en jämförelse

Compare tar de två inlästa arbetsböckerna och returnerar antalet skillnader som hittats. Skillnadslistan blir sedan tillgänglig via index, eller kan dumpas till valfri 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);              // en läsbar rad per skillnad
      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;

En rad producerad av Report läser som value changed: Data!A2: 10 -> 99, vilket räcker för en granskare och räcker för ett commit-meddelande. Det är den mänskligt vända ytan. Den programmatiska ytan är själva skillnadsposten, och den är den att använda när jämförelsen matar ett beslut snarare än ett dokument

Att styra logik från de strukturerade skillnaderna

Varje skillnad exponerar sin typ, bladnamnet, ettbaserad rad och kolumn för poster på cellnivå, en A1-referens för poster på sammanslagningsnivå, samt den vänstra och högra texten. Poster på blad- och sammanslagningsnivå rapporterar rad och kolumn som noll, vilket är hur du skiljer dem åt utan att inspektera typen:

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;

  // En granskningspolicy som bara blockerar på formelredigeringar
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Två egenskaper hos utdatan är värda att känna till innan du skriver påståenden mot den. Ordningen på poster på cellnivå följer den interna traverseringsordningen för cellagringen, så tester bör skrivas ordningsoberoende. Och formelsignaturen bär sitt eget inledande likhetstecken, vilket betyder att en beskrivande sträng byggd genom sammanslagning kan visa ett dubblerat ==; kontrollera fältvärdena snarare än att tolka den beskrivande raden när resultatet styr logik

Var lönar sig arbetsboksdiffning?

Tre användningsfall motiverar funktionen på egen hand. Regressionstestning av en rapportgenerator: behåll en känt god arbetsbok, regenerera, jämför, och fäll bygget på varje oväntad skillnad. Ändringsgranskning: ge en granskare den läsbara rapporten istället för två filer. Och migreringsverifiering: efter att ha konverterat en batch äldre arbetsböcker, jämför varje resultat mot sin källa för att bevisa att inget gick förlorat

Det tredje fallet passar naturligt ihop med de inventerings- och granskningspass som beskrivs i arbetsboksgranskning och konverteringsverkstad, där att räkna vad en arbetsbok innehåller sker före konvertering och jämförelse sker efter. Om dina skillnader klustrar sig kring infogade rader förklarar reglerna för referensomskrivning i formelreferensjustering vid infogning och borttagning varför formler som ser oförändrade ut rapporteras som ändrade

Gränserna, uttalade rakt ut

Jämförelsen täcker värden, formler, sammanslagningar och bladnärvaro. Den jämför inte talformat, typsnitt, fyllningar, villkorsstyrda formateringsregler, datavalideringar, diagram, bilder eller definierade namn. En cell vars värde är identiskt men vars format ändrades från Allmänt till Valuta rapporterar ingen skillnad, vilket är korrekt för en datajämförelse och otillräckligt för en formateringsgranskning

Datumvärderade celler förtjänar en specifik varning: de jämförs som sin textkonvertering, så en arbetsbok lagrad på 1904-datumsystemet och en på 1900-systemet kan jämföras som lika eller olika på sätt som överraskar dig om de underliggande serienumren skiljer sig. Reglerna för datumsystemet täcks i datumserienummer och 1904-systemet. När formatering eller trohet på objektnivå är en del av frågan, kombinera diffen med ett granskningspass som räknar dessa funktioner på vardera sidan

Arbetsboksjämförelse, granskning och konvertering körs alla på samma motor för Delphi och C++Builder; den fullständiga funktionslistan finns på sidan för HotXLS Delphi-kalkylbladskomponent