Diffe to Excel-arbeidsbøker i Delphi med HotXLS

HotXLS sammenligner to arbeidsbøker gjennom TXLSXWorkbookCompare, som parer regneark etter navn, går gjennom de utfylte cellene i hvert par, og rapporterer det som avviker, som en strukturert liste med forskjellsposter og, på forespørsel, som én lesbar linje per forskjell. Ingen Excel-installasjon er involvert, og sammenligningen kjøres helt på den innlastede objektmodellen i Delphi eller C++Builder

Behovet dukker som regel opp første gang noen spør hva som ble endret. En finansarbeidsbok kommer tilbake fra gjennomgang, en nattlig eksport genereres på nytt etter en kodeendring, eller to avdelinger sender versjoner av den samme malen. Å åpne begge side om side fungerer for ett ark og feiler for tjue. Å sammenligne filer byte for byte svarer ikke på noe som helst, fordi to lagringer av den samme arbeidsboken skiller seg på måter ingen bryr seg om

Hva regnes som en forskjell?

Sammenligningen rapporterer åtte typer, og settet er bevisst lite: et ark lagt til eller fjernet, en utfylt celle lagt til eller fjernet, en celle hvis verdi endret seg, en celle hvis formel endret seg, og et sammenslått område lagt til eller fjernet. Alt uttrykkes mot venstre arbeidsbok som grunnlinje, så et lagt-til-element eksisterer bare på høyre side, og et fjernet element bare på venstre

Ark pares etter navn snarere enn etter posisjon. Å endre rekkefølgen på regneark produserer derfor ingen forskjeller i det hele tatt, noe som nesten alltid er ønsket oppførsel: en bruker som drar en fane, er ikke en dataendring. Et ark som bare finnes på den ene siden, rapporterer én oppføring på arknivå i stedet for å utvide hver utfylte celle inni det, noe som holder en rapport for to strukturelt forskjellige arbeidsbøker lesbar i stedet for tusenvis av linjer lang

Verdi eller formel, og hvordan hver av dem sammenlignes

Hver celle bidrar med en signatur, og regelen er enkel: en celle som bærer en formel, sammenlignes etter formelteksten sin med et innledende likhetstegn, og en celle uten formel sammenlignes etter verdien sin konvertert til tekst. Det skillet betyr mer enn det ser ut til ved første øyekast. To celler kan holde det samme viste tallet mens den ene er en literal og den andre en formel, og å behandle dem som like, ville skjule nettopp den redigeringen som er mest verdt å fange opp i en gjennomgått arbeidsbok

Det betyr også at en formel hvis tekst er uendret, ikke rapporterer noen forskjell selv om det bufrede resultatet dens avviker, noe som er den korrekte oppførselen for å sammenligne forfattet innhold, og den gale oppførselen hvis du prøver å oppdage omregningsdrift. For det andre spørsmålet, regn om begge arbeidsbøkene før du sammenligner, slik at verdiene du sammenligner, er dem formlene faktisk produserer i dag

Kjøre en sammenligning

Compare tar imot de to innlastede arbeidsbøkene og returnerer antallet forskjeller som ble funnet. Forskjellslisten er deretter tilgjengelig etter indeks, eller kan dumpes inn i en hvilken som helst 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);              // én lesbar linje per forskjell
      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 linje produsert av Report leses omtrent som value changed: Data!A2: 10 -> 99, noe som er nok for en gjennomgåelsesperson og nok for en commit-melding. Det er den menneskevendte overflaten. Den programmatiske overflaten er selve forskjellsposten, og det er den du skal bruke når sammenligningen mater en beslutning i stedet for et dokument

Styre logikk fra de strukturerte forskjellene

Hver forskjell eksponerer sin type, arknavnet, énbasert rad og kolonne for oppføringer på cellenivå, en A1-referanse for oppføringer på sammenslåingsnivå, og venstre og høyre tekst. Oppføringer på arknivå og sammenslåingsnivå rapporterer rad og kolonne som null, og det er slik du skiller dem uten å inspisere 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 gjennomgangspolicy som bare blokkerer på formelendringer
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

To egenskaper ved utdataen er verdt å kjenne til før du skriver forsikringer mot den. Rekkefølgen til oppføringer på cellenivå følger den interne gjennomgangsrekkefølgen til celle-lageret, så tester bør skrives rekkefølge-uavhengig. Og formelsignaturen bærer sitt eget innledende likhetstegn, noe som betyr at en beskrivelsesstreng bygget ved sammenslåing kan vise et dobbelt ==; sjekk feltverdiene i stedet for å parse den beskrivende linjen når resultatet styrer logikk

Hvor arbeidsbok-diffing lønner seg

Tre bruksområder rettferdiggjør funksjonen på egen hånd. Regresjonstesting av en rapportgenerator: behold en kjent god arbeidsbok, generer på nytt, sammenlign, og feil bygget ved enhver uventet forskjell. Endringsgjennomgang: gi en gjennomgåelsesperson den lesbare rapporten i stedet for to filer. Og migreringsverifisering: etter å ha konvertert en batch med gamle arbeidsbøker, sammenlign hvert resultat mot kilden sin for å bevise at ingenting gikk tapt

Det tredje tilfellet passer naturlig sammen med lagerbeholdnings- og revisjonsgjennomgangene beskrevet i verktøybenken for arbeidsbokrevisjon og konvertering, der å telle hva en arbeidsbok inneholder, skjer før konvertering og sammenligning skjer etter. Hvis forskjellene dine klynger seg rundt innsatte rader, forklarer reglene for referanseomskriving i justering av formelreferanser ved innsetting og sletting hvorfor formler som ser uendret ut, rapporteres som endret

Begrensningene, sagt rett ut

Sammenligningen dekker verdier, formler, sammenslåinger og tilstedeværelse av ark. Den sammenligner ikke tallformater, skrifter, fyll, betingede formateringsregler, datavalideringer, diagrammer, bilder eller definerte navn. En celle hvis verdi er identisk, men hvis format endret seg fra Generelt til Valuta, rapporterer ingen forskjell, noe som er korrekt for en datasammenligning og utilstrekkelig for en formateringsgjennomgang

Celler med datoverdi fortjener én spesifikk advarsel: de sammenlignes som tekstkonverteringen sin, så en arbeidsbok lagret på 1904-datosystemet og en på 1900-systemet kan sammenlignes som like eller ulike på måter som overrasker deg hvis de underliggende serienumrene avviker. Datosystemreglene er dekket i dato-serienumre og 1904-systemet. Når formatering eller objektnivå-troskap er en del av spørsmålet, kombiner diffen med en revisjonsgjennomgang som teller de funksjonene på hver side

Arbeidsboksammenligning, revisjon og konvertering kjører alle på den samme motoren for Delphi og C++Builder; den komplette funksjonslisten finnes på HotXLS Delphi regnearkkomponentsiden