Kahden Excel-työkirjan vertailu Delphissä HotXLS:llä

HotXLS vertailee kahta työkirjaa luokan TXLSXWorkbookCompare avulla, joka pariuttaa laskentataulukot nimen perusteella, käy läpi kunkin parin täytetyt solut ja raportoi erot rakenteellisena erolistana ja pyydettäessä yhtenä luettavana rivinä eroa kohden. Mukana ei ole Excel-asennusta, ja vertailu toimii kokonaan ladatussa objektimallissa Delphissä tai C++Builderissa

Tarve ilmenee yleensä ensimmäisen kerran, kun joku kysyy, mikä muuttui. Talousosaston työkirja palaa tarkastuksesta, yöllinen vienti luodaan uudelleen koodimuutoksen jälkeen, tai kaksi osastoa lähettävät saman mallin versioita. Molempien avaaminen rinnakkain toimii yhdelle taulukolle ja epäonnistuu kahdellekymmenelle. Tiedostojen vertaaminen tavu tavulta ei vastaa mihinkään, koska saman työkirjan kaksi tallennusta eroavat tavoin, joista kukaan ei välitä

Mikä lasketaan eroksi?

Vertailu raportoi kahdeksan tyyppiä, ja joukko on tarkoituksella pieni: lisätty tai poistettu taulukko, lisätty tai poistettu täytetty solu, solu, jonka arvo muuttui, solu, jonka kaava muuttui, ja lisätty tai poistettu yhdistetty alue. Kaikki ilmaistaan vasenta työkirjaa perustasona vasten, joten lisätty kohde on olemassa vain oikealla ja poistettu kohde vain vasemmalla

Taulukot pariutetaan nimen, ei sijainnin perusteella. Laskentataulukoiden uudelleenjärjestäminen ei siis tuota lainkaan eroja, mikä on lähes aina haluttu käyttäytyminen: käyttäjän välilehden raahaaminen ei ole datamuutos. Taulukko, joka on läsnä vain toisella puolella, raportoi yhden taulukkotason merkinnän sen sijaan, että se laajentaisi jokaisen sisällä olevan täytetyn solun, mikä pitää kahden rakenteellisesti erilaisen työkirjan raportin luettavana tuhansien rivien sijaan

Arvo vai kaava, ja miten kumpaakin vertaillaan

Jokainen solu tuottaa allekirjoituksen, ja sääntö on yksinkertainen: kaavaa kantava solu vertailee kaavatekstinsä perusteella alkavalla yhtäsuuruusmerkillä, ja solu ilman kaavaa vertailee tekstiksi muunnetun arvonsa perusteella. Tällä erottelulla on enemmän merkitystä kuin ensin näyttää. Kaksi solua voivat kantaa samaa näytettyä lukua, kun toinen on kirjaimellinen arvo ja toinen kaava, ja niiden kohteleminen samanarvoisina piilottaisi juuri sen muokkauksen, joka tarkastellussa työkirjassa kannattaisi eniten huomata

Se tarkoittaa myös, että kaava, jonka teksti on muuttumaton, ei raportoi eroa, vaikka sen välimuistiin tallennettu tulos eroaisi, mikä on oikea käyttäytyminen kirjoitetun sisällön vertailussa ja väärä käyttäytyminen, jos yrität havaita uudelleenlaskennan ajautumista. Tätä toista kysymystä varten laske molemmat työkirjat uudelleen ennen vertailua, jotta vertailemasi arvot ovat niitä, joita kaavat todella tuottavat tänään

Vertailun suorittaminen

Compare ottaa kaksi ladattua työkirjaa ja palauttaa löydettyjen erojen määrän. Erolista on sitten käytettävissä indeksin mukaan, tai se voidaan tyhjentää mihin tahansa TStrings-objektiin:

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);              // yksi luettava rivi eroa kohden
      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;

Rivi, jonka Report tuottaa, näyttää tältä: value changed: Data!A2: 10 -> 99, mikä riittää tarkastajalle ja riittää commit-viestiksi. Tämä on ihmiselle näkyvä pinta. Ohjelmallinen pinta on itse erotietue, ja sitä kannattaa käyttää, kun vertailu syöttää päätöksen eikä dokumenttia

Logiikan ohjaaminen rakenteellisista eroista

Jokainen ero paljastaa tyyppinsä, taulukon nimen, ykköspohjaisen rivin ja sarakkeen solutason merkinnöille, A1-viittauksen yhdistämistason merkinnöille sekä vasemman ja oikean tekstin. Taulukkotason ja yhdistämistason merkinnät raportoivat rivin ja sarakkeen nollana, ja näin ne voi erottaa toisistaan tarkastamatta tyyppiä:

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;

  // Tarkastuskäytäntö, joka estää vain kaavamuutokset
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Kaksi tulosteen ominaisuutta kannattaa tuntea, ennen kuin kirjoitat väitteitä sitä vastaan. Solutason merkintöjen järjestys noudattaa solusäilön sisäistä läpikäyntijärjestystä, joten testit tulisi kirjoittaa järjestyksestä riippumattomina. Ja kaavan allekirjoitus kantaa oman alkavan yhtäsuuruusmerkkinsä, mikä tarkoittaa, että katenoinnilla rakennettu kuvausmerkkijono voi näyttää kaksinkertaisen ==-merkin; tarkista kenttäarvot jäsentämisen sijaan kuvaavasta rivistä, kun tulos ohjaa logiikkaa

Missä työkirjan diffaus kannattaa

Kolme käyttötapaa oikeuttavat ominaisuuden yksinään. Raporttigeneraattorin regressiotestaus: säilytä tunnetusti toimiva työkirja, luo uudelleen, vertaa ja epäonnistuta koonnos minkä tahansa odottamattoman eron kohdalla. Muutostarkastus: anna tarkastajalle luettava raportti kahden tiedoston sijaan. Ja migraation todentaminen: kun vanhojen työkirjojen erä on muunnettu, vertaa jokaista tulosta lähteeseensä todistaaksesi, ettei mitään kadonnut

Tämä kolmas tapaus yhdistyy luontevasti inventaario- ja tarkastusvaiheisiin, jotka kuvataan artikkelissa työkirjan tarkastus- ja muuntotyöpenkki, jossa työkirjan sisällön laskenta tapahtuu ennen muunnosta ja vertailu sen jälkeen. Jos erot kasautuvat lisättyjen rivien ympärille, viittausten uudelleenkirjoitussäännöt artikkelissa kaavaviittausten säätö lisäyksessä ja poistossa selittävät, miksi muuttumattomilta näyttävät kaavat raportoituvat muuttuneina

Rajat, suoraan sanottuna

Vertailu kattaa arvot, kaavat, yhdistämiset ja taulukoiden läsnäolon. Se ei vertaile lukumuotoja, fontteja, täyttöjä, ehdollisen muotoilun sääntöjä, datan validointeja, kaavioita, kuvia tai nimettyjä alueita. Solu, jonka arvo on identtinen mutta jonka muoto muuttui yleisestä valuuttamuotoon, ei raportoi eroa, mikä on oikein datavertailulle ja riittämätöntä muotoilutarkastukselle

Päivämääräarvoiset solut ansaitsevat yhden erityisen varoituksen: ne vertailevat tekstiksi muunnettuina, joten työkirja, joka on tallennettu 1904-päivämääräjärjestelmässä, ja työkirja 1900-järjestelmässä, voivat vertailla samoina tai erilaisina tavoilla, jotka yllättävät, jos taustalla olevat sarjanumerot eroavat. Päivämääräjärjestelmän säännöt käsitellään artikkelissa päivämäärän sarjanumerot ja 1904-järjestelmä. Kun muotoilu tai objektitason uskollisuus on osa kysymystä, yhdistä diff tarkastusvaiheeseen, joka laskee nämä ominaisuudet molemmilla puolilla

Työkirjan vertailu, tarkastus ja muunnos toimivat kaikki samalla moottorilla Delphille ja C++Builderille; täydellinen ominaisuusluettelo on sivulla HotXLS Delphi -laskentataulukkokomponentin sivulla