Porovnání dvou sešitů Excel v Delphi pomocí HotXLS

HotXLS porovnává dva sešity přes TXLSXWorkbookCompare, který spáruje listy podle názvu, projde vyplněné buňky každé dvojice a nahlásí, co se liší, jako strukturovaný seznam záznamů o rozdílech a na požádání jako jeden čitelný řádek na rozdíl. Není potřeba žádná instalace Excelu a porovnání běží zcela na načteném objektovém modelu v Delphi nebo C++Builder

Potřeba se obvykle objeví ve chvíli, kdy se někdo poprvé zeptá, co se změnilo. Finanční sešit se vrátí z revize, noční export se přegeneruje po změně kódu, nebo dvě oddělení pošlou verze stejné šablony. Otevření obou souborů vedle sebe funguje u jednoho listu a selže u dvaceti. Porovnání souborů bajt po bajtu neodpoví vůbec na nic, protože dvě uložení stejného sešitu se liší způsoby, na kterých nikomu nezáleží

Co se počítá jako rozdíl?

Porovnání hlásí osm druhů a tato množina je záměrně malá: přidaný nebo odebraný list, přidaná nebo odebraná vyplněná buňka, buňka se změněnou hodnotou, buňka se změněným vzorcem a přidaná nebo odebraná sloučená oblast. Vše je vyjádřeno vůči levému sešitu jako výchozímu stavu, takže přidaná položka existuje pouze vpravo a odebraná pouze vlevo

Listy se párují podle názvu, ne podle pozice. Přeuspořádání listů tedy nevytvoří žádné rozdíly, což je téměř vždy chování, které chcete: přetažení karty uživatelem není změna dat. List přítomný jen na jedné straně nahlásí jeden záznam na úrovni listu, místo aby rozepsal každou vyplněnou buňku uvnitř, což udržuje zprávu o dvou strukturálně odlišných sešitech čitelnou místo tisíců řádků dlouhou

Hodnota nebo vzorec, a jak se každý porovnává

Každá buňka přispívá podpisem a pravidlo je jednoduché: buňka nesoucí vzorec se porovnává podle textu vzorce s úvodním rovnítkem, a buňka bez vzorce se porovnává podle své hodnoty převedené na text. Tento rozdíl má větší význam, než se na první pohled zdá. Dvě buňky mohou zobrazovat stejné číslo, přičemž jedna je literál a druhá vzorec, a považovat je za shodné by skrylo přesně tu úpravu, kterou je v revidovaném sešitu nejdůležitější zachytit

Znamená to také, že vzorec, jehož text se nezměnil, nehlásí žádný rozdíl, i když se liší jeho uložený výsledek, což je správné chování pro porovnání autorského obsahu a špatné chování, pokud se snažíte odhalit posun po přepočtu. Pro tuto druhou otázku oba sešity před porovnáním přepočítejte, aby hodnoty, které porovnáváte, byly ty, které vzorce skutečně produkují dnes

Spuštění porovnání

Compare přebírá dva načtené sešity a vrací počet nalezených rozdílů. Seznam rozdílů je pak dostupný podle indexu, nebo jej lze vypsat do libovolného 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);              // jeden čitelný řádek na rozdíl
      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;

Řádek vytvořený Report vypadá jako value changed: Data!A2: 10 -> 99, což stačí recenzentovi i pro zprávu o commitu. To je stránka určená člověku. Programová stránka je samotný záznam rozdílu, a ten je třeba použít, když porovnání pohání rozhodnutí, ne dokument

Řízení logiky na základě strukturovaných rozdílů

Každý rozdíl vystavuje svůj druh, název listu, řádek a sloupec počítané od jedné u záznamů na úrovni buňky, referenci A1 u záznamů na úrovni sloučení a levý a pravý text. Záznamy na úrovni listu a sloučení hlásí řádek a sloupec jako nulu, což je způsob, jak je rozlišit, aniž byste zkoumali druh:

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;

  // Revizní politika, která blokuje pouze na úpravách vzorců
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Dvě vlastnosti výstupu stojí za to znát ještě před psaním assertions proti němu. Pořadí záznamů na úrovni buňky sleduje interní pořadí průchodu úložiště buněk, takže testy by měly být psány nezávisle na pořadí. A podpis vzorce nese vlastní úvodní rovnítko, což znamená, že popisný řetězec sestavený spojováním může zobrazit zdvojené ==; když výsledek pohání logiku, kontrolujte hodnoty polí, ne parsujte popisný řádek

Kde se porovnávání sešitů vyplácí

Tři případy použití samy o sobě funkci ospravedlňují. Regresní testování generátoru sestav: uchovejte znovu ověřený sešit, přegenerujte, porovnejte a nechte sestavení selhat na jakémkoli neočekávaném rozdílu. Revize změn: dejte recenzentovi čitelnou zprávu místo dvou souborů. A ověření migrace: po převodu dávky starších sešitů porovnejte každý výsledek se zdrojem, abyste dokázali, že se nic neztratilo

Tento třetí případ se přirozeně páruje s inventarizačními a auditními průchody popsanými v článku pracoviště auditu a konverze sešitů, kde počítání obsahu sešitu probíhá před konverzí a porovnání probíhá po ní. Pokud se vaše rozdíly soustředí kolem vložených řádků, pravidla přepisování referencí v článku úprava referencí vzorců při vkládání a mazání vysvětlují, proč vzorce, které vypadají nezměněné, hlásí jako změněné

Meze, řečeno otevřeně

Porovnání pokrývá hodnoty, vzorce, sloučení a přítomnost listů. Neporovnává formáty čísel, fonty, výplně, pravidla podmíněného formátování, ověřování dat, grafy, obrázky ani definované názvy. Buňka, jejíž hodnota je identická, ale jejíž formát se změnil z Obecného na Měnu, nehlásí žádný rozdíl, což je správné pro porovnání dat a nedostatečné pro revizi formátování

Buňky s datovou hodnotou si zaslouží jedno konkrétní varování: porovnávají se podle svého textového převodu, takže sešit uložený v systému data 1904 a sešit v systému 1900 se mohou porovnat jako shodné nebo neshodné způsoby, které vás překvapí, pokud se liší podkladová pořadová čísla. Pravidla systému data popisuje článek pořadová čísla dat a systém 1904. Když je součástí otázky formátování nebo věrnost na úrovni objektů, kombinujte porovnání s auditním průchodem, který tyto funkce počítá na každé straně

Porovnávání sešitů, auditování a konverze běží na stejném motoru pro Delphi a C++Builder; kompletní seznam funkcí je na stránce komponenty HotXLS pro tabulky v Delphi