Dwie kopie tego samego kwartalnego skoroszytu mogą w komórce D2 pokazywać obie 1,250.00 i mimo to różnić się tym, czym ta komórka jest: w jednym to wpisany literał, w drugim =SUM(A2:C2). Recenzent przełączający się między plikami widzi identyczne ekrany. Porównanie bajtowe obu pakietów .xlsx idzie w drugą stronę i zgłasza prawie wszystko jako różne, bo archiwum zip zapisane dwa razy rzadko zgadza się bajt w bajt. Żadne z tych sprawdzeń nie odpowiada na pytanie, które ludzie naprawdę zadają po rundzie przeglądu: które komórki, formuły i arkusze się zmieniły
To pytanie pojawia się w kilku przewidywalnych miejscach. Skoroszyt finansowy wraca z przeglądu, nocny eksport jest regenerowany po zmianie w kodzie, albo dwa działy odsyłają swoje własne edycje jednego szablonu. Oglądanie obok siebie jest w porządku dla jednego arkusza i beznadziejne dla dwudziestu, a to właśnie zamiana formuła–literał z powyższego przykładu wymyka się zmęczonym oczom
Porównywanie dwóch skoroszytów przez TXLSXWorkbookCompare
HotXLS porównuje skoroszyty przez TXLSXWorkbookCompare z jednostki lxCompare. Compare(Left, Right) przyjmuje dwa wczytane obiekty TXLSXWorkbook i zwraca liczbę znalezionych różnic. Lista różnic jest potem dostępna po indeksie przez Diff(I) i Count, a Report dopisuje jeden czytelny wiersz na różnicę do dowolnego TStrings. Excel nie bierze udziału w żadnym momencie; porównanie biegnie po modelu obiektowym, który HotXLS już wczytał, więc działa tak samo w zadaniu konsolowym, usłudze czy aplikacji C++Builder
uses
SysUtils, Classes, lxHandleX, lxCompare;
var
Left, Right: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare;
Lines: TStringList;
begin
Left := TXLSXWorkbook.Create;
Right := TXLSXWorkbook.Create;
Cmp := TXLSXWorkbookCompare.Create;
Lines := TStringList.Create;
try
// Open zwraca 1 przy sukcesie; nieudane otwarcie musi się tu skończyć,
// inaczej porównują się puste skoroszyty i wynik nic nie znaczy
if Left.Open('baseline.xlsx') <> 1 then
raise Exception.Create('cannot open baseline.xlsx');
if Right.Open('reviewed.xlsx') <> 1 then
raise Exception.Create('cannot open reviewed.xlsx');
if Cmp.Compare(Left, Right) = 0 then
Writeln('workbooks are equivalent')
else
begin
Cmp.Report(Lines); // jeden czytelny wiersz na różnicę
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;
Jawne sprawdzenia otwarcia mają większe znaczenie, niż wyglądają. Compare zwraca 0 i czyści swoją listę, gdy którykolwiek argument to nil, a dwa puste skoroszyty też porównują się jako równe, więc wynik zero oznacza "równoważność" tylko wtedy, gdy oba pliki naprawdę się wczytały. Typowy wiersz w workbook-diff.txt wygląda tak: value changed: Data!A2: 10 -> 99
Co domyślnie liczy się jako różnica?
Bez ustawionych opcji TXLSXWorkbookCompare zgłasza osiem rodzajów różnic: dodany lub usunięty arkusz, dodaną lub usuniętą komórkę, zmienioną wartość, zmienioną formułę oraz dodany lub usunięty scalony zakres. Wartości wyliczenia to lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded i lckMergeRemoved. Wszystko jest mierzone względem lewego skoroszytu jako bazy, więc dodany element istnieje tylko po prawej, a usunięty tylko po lewej
Arkusze są parowane po nazwie, a dopasowanie ignoruje wielkość liter, więc Data po lewej paruje się z DATA po prawej. Przeciągnięcie karty na nową pozycję nie daje więc żadnych wpisów. Zmiana nazwy arkusza to inna historia: Q3 przemianowany na Q3 Final wychodzi jako jeden wiersz sheet removed i jeden wiersz sheet added, bo nic w pliku nie zapisuje, że to ten sam arkusz. Arkusz istniejący tylko po jednej stronie produkuje pojedynczy wpis na poziomie arkusza zamiast wiersza na każdą komórkę w jego wnętrzu, co utrzymuje raport czytelnym, gdy dwa skoroszyty różnią się strukturą
Komórki są parowane po pozycji. Porównanie przechodzi zapisane komórki każdego sparowanego arkusza, i obejmuje to komórki trzymające tylko formatowanie. Sformatowana, ale pusta komórka, która istnieje po lewej, a nie po prawej, zostaje zgłoszona jako cell removed: Data!C4 was (blank). Scalony zakresy porównują się po swoim tekście A1, więc A1:D1 poszerzone do A1:E1 daje jeden merge removed i jeden merge added
Dlaczego formuła i literał z tą samą liczbą zgłaszają zmianę?
Bo formuła jest autorską treścią. Gdy któraś strona pary komórek trzyma formułę, TXLSXWorkbookCompare porównuje tekst formuły, a formuła po jednej stronie wobec literału po drugiej to wpis lckFormulaChanged, nawet jeśli obie wyświetlają 1,250. Wiersz raportu dla przykładu otwierającego artykuł to formula changed: Data!D2: 1250 -> =SUM(A2:C2), a rekord niesie LeftKind = lcvNumber i RightKind = lcvFormula. Tekst formuły jest porównywany dokładnie tak, jak jest zapisany, z dodanym wiodącym znakiem równości na potrzeby wyświetlania
Wartości literałów porównują się jak wpisane wartości. Liczba z liczbą, tekst z tekstem, a liczba 10 wobec tekstu "10" to zmiana, choć obie komórki drukują te same znaki. Raport pokazuje etykiety typów, ile razy rodzaje się różnią: value changed: Data!B7: [number] 10 -> [text] 10. Ten przypadek jest częsty w plikach, które przechodzą przez powroty CSV albo przez copy-paste'a użytkownika, i właśnie on psuje później wyszukiwania
Formuła, której tekst się nie zmienił, nie zgłasza domyślnie niczego, nawet gdy jej zbuforowany wynik jest inny. Przy przeglądzie autorskiej treści to poprawne. Przy testach regresyjnych generatora to zbyt pobłażliwe, bo zmieniony arkusz wejściowy potrafi poruszyć wszystkie sumy, nie dotykając żadnej formuły. Ustawienie lcoFormulaCachedResults w Options dodaje wpis lckFormulaCacheChanged zawsze, gdy zbuforowane wyniki się różnią, albo gdy jedna strona ma wartość z cache'a, a druga nie ma żadnej, co jest zgłaszane jako (missing cache). W testach regresyjnych generatora włącz tę opcję; suma, która dryfuje przy niezmienionych formułach, to dokładnie ta awaria, którą te testy istnieją, by łapać
Wyniki w cache'u są tak świeże, jak ostatnie obliczenie, więc najpierw przelicz oba skoroszyty, gdy pytanie brzmi "co te formuły dają dzisiaj". TXLSXWorkbook.Recalculate zwraca lxOk przy sukcesie; przy wyłączonej iteracji cykl odwołań zwraca lxErrorRef. Szum zmiennoprzecinkowy to drugie źródło fałszywych alarmów, a SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) załatwia go i dla literałów liczbowych, i dla zbuforowanych wyników:
uses
SysUtils, lxCalc, lxHandleX, lxCompare;
function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare): Integer;
begin
// Doprowadź oba bufory wyników do aktualności przed porównaniem
if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
raise Exception.Create('recalculation failed');
Cmp.Options := [lcoFormulaCachedResults];
// 1e-9 bezwzględna lub 1e-12 względna; obie muszą być skończone i nieujemne,
// inaczej rzucony zostaje EArgumentException i żadna wartość się nie zmienia
Cmp.SetNumericTolerance(1e-9, 1e-12);
Result := Cmp.Compare(Expected, Actual);
end;
Tolerancje domyślnie wynoszą zero, co znaczy porównanie dokładne. Dotyczą tylko liczb. Daty, geometria wierszy i kolumn oraz style zawsze porównują się dokładnie, więc tolerancja nastrojona na zaokrąglenia walutowe nigdy nie ukryje daty, która przesunęła się o sekundę
Sterowanie logiką na podstawie ustrukturyzowanych różnic
Każdy rekord TlxCompareDiff odsłania Kind, Sheet, liczone od jednego Row i Col, odwołanie A1 w Ref, LeftText i RightText, a dla wpisów komórek LeftKind i RightKind. Wpisy na poziomie arkusza i scalenia niosą zero w Row i Col. Po włączeniu opcjonalnych sprawdzeń wpisy geometrii wierszy niosą tylko wiersz, wpisy geometrii kolumn tylko kolumnę, a funkcje skoroszytu, jak nazwy zdefiniowane, nie niosą ani jednych, ani drugich, więc rozgałęziaj się po Kind, a nie po zerowych współrzędnych:
var
I, FormulaEdits: Integer;
D: TlxCompareDiff;
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!%s: %s => %s',
[D.Sheet, D.Ref, D.LeftText, D.RightText]));
end;
lckValueChanged:
if D.LeftKind <> D.RightKind then
Writeln('type change: ' + D.Describe);
lckSheetAdded, lckSheetRemoved:
Writeln('structure: ' + D.Describe);
lckMergeAdded, lckMergeRemoved:
Writeln('layout: ' + D.Describe);
end;
end;
// Zasada przeglądu blokująca tylko przy edycjach formuł
if FormulaEdits > 0 then
raise Exception.CreateFmt(
'%d formula change(s) need sign-off', [FormulaEdits]);
end;
Kolejność listy jest stała. Compare sortuje różnice po nazwie arkusza, potem po wierszu, kolumnie, rodzaju i odwołaniu, więc dwa przebiegi na tej samej parze plików dają ten sam tekst raportu, wiersz w wiersz. Nazwy arkuszy sortują się porównaniem łańcuchów po wartości znaków, nie po pozycji karty, a wpisy z zerowym wierszem przychodzą pierwsze wewnątrz grupy każdego arkusza. To czyni z tekstu raportu uczciwego golden file'em dla testu regresyjnego: zacommituj oczekiwany workbook-diff.txt i zawal build, gdy nowy się różni. Dla decyzji polityki czytaj pola rekordu bezpośrednio i zostaw wiersz opisowy ludziom
Włączanie sprawdzeń formatowania, układu i obiektów
Domyślny zestaw to porównanie danych, i komórka, której format liczby przeszedł z General na Currency, nie zgłasza pod nim niczego. Właściwość Options poszerza porównanie o jedną kategorię naraz. lcoCellStyles porównuje rozwiązany styl każdej sparowanej komórki, a lcoBlankStyleRegions robi to samo dla sformatowanych obszarów bez zapisanych komórek, sklejając sąsiednie różnice w prostokąty, których daleki róg siedzi w LastRow i LastCol. lcoAxisGeometry porównuje wysokości wierszy i szerokości kolumn razem ze stanem ukrycia, poziomu konspektu i zwinięcia. lcoRichTextRuns porównuje sformatowane przebiegi wewnątrz komórek tekstowych
Zawartość na poziomie obiektów ma własne przełączniki: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (obejmuje też obrazy w komórkach), lcoPivotTables, lcoPivotCaches, lcoSlicerState i lcoTimelineState. Te wpisy wkładają klucz funkcji do Ref i mówią przez Change, czy została dodana, usunięta czy zmieniona. Nazwy zdefiniowane, pamięci pivotów oraz pamięci slicera i osi czasu na poziomie skoroszytu niosą pusty Sheet
Czytaj wyniki tych sprawdzeń z właściwymi oczekiwaniami. Wpisy stylów, tekstu sformatowanego i funkcji zgłaszają skróty SHA-256 znormalizowanej definicji w LeftText i RightText, więc raport mówi, że styl komórki C5 się zmienił, ale nie która właściwość. Wpisy geometrii są czytelne, na przykład size=15; hidden=0; outline=0; collapsed=0, a wiersz bez jawnej wysokości porównuje się na standardowej wysokości arkusza, więc jawny wiersz 15-punktowy zgadza się z nieustawionym, gdy wysokość standardowa też wynosi 15. Trzymaj lcoCellStyles wyłączone przy przeglądach treści. Na dużym skoroszycie zamienia każdą przekształconą kolumnę w mur haszy, który zakrywa trzy edycje komórek, do zaakceptowania których recenzent został poproszony
// Przegląd układu: tylko geometria, scalenia i formatowanie warunkowe
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Limit tego, co sprawdzenia obiektowe mogą przechwycić: 32 MiB, 500000 elementów
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
Cmp.Compare(Left, Right);
except
on E: EXLSXSemanticBudget do
Writeln('workbooks too large for this check: ' + E.Message);
end;
Sprawdzenia obiektowe przechwytują swoje definicje przed porównaniem, a SetFeatureLimits ogranicza ten przechwyt. Domyślnie jest to 67,108,864 bajty i 1,000,000 elementów; przekroczenie czegokolwiek rzuca EXLSXSemanticBudget. Gdy Compare rzuca, lista różnic z poprzedniego udanego wywołania zostaje na miejscu, więc wołający, który łapie wyjątek, nie powinien czytać Count jako wyniku nieudanego przebiegu
Daty, wstawione wiersze i gdzie porównywanie się opłaca
Komórki trzymane przez skoroszyt jako daty porównują się jako daty, dokładnie, i drukują jako date(...) z leżącą u podstaw liczbową wartością daty. Data po jednej stronie wobec zwykłej liczby po drugiej pokazuje etykiety rodzajów — to zwykły znak, że kolumna zgubiła format daty w drodze przez jakieś inne narzędzie. Mieszane systemy dat to tu pułapka. Skoroszyt w systemie dat 1904 i jeden w systemie 1900 zapisują ten sam dzień kalendarzowy jako liczby seryjne różniące się o 1,462, więc sprawdź Date1904 po obu stronach, zanim zaufasz kolumnie zmian dat. Tło opisuje liczby seryjne dat i system 1904
Parowanie pozycyjne ma jedną konsekwencję, którą warto zaplanować. Wstaw wiersz blisko góry arkusza, a każda komórka poniżej się ruszy, więc porównanie zgłasza ciąg zmienionych, dodanych i usuniętych komórek w dół arkusza, a formuły, których odwołania przepisało wstawienie, zgłaszają się jako zmienione. Gdy kontrolujesz potok, uruchom jedno porównanie przed edycją strukturalną, a drugie po niej, żeby każdy raport obejmował jeden rodzaj zmian. Reguły przepisywania opisuje dostosowywanie odwołań formuł przy wstawianiu i usuwaniu
Trzy zadania same uzasadniają podpięcie tej funkcji. Test regresyjny generatora raportów: trzymaj znany dobry skoroszyt, zregeneruj, porównaj z włączonymi wynikami z bufora i zawieś się na każdej różnicy. Przegląd zmian: podaj recenzentowi posortowany raport zamiast dwóch plików. Sprawdzenia migracji: po skonwertowaniu partii starych skoroszytów porównaj każdy wynik z jego źródłem, a potem policz formatowanie i funkcje obiektowe po każdej stronie przebiegiem inwentaryzacji z warsztatu audytu i konwersji skoroszytów
Porównywanie, przeliczanie i konwersja dzielą jeden silnik w HotXLS dla Delphi i C++Builder, a strona komponentu arkusza kalkulacyjnego HotXLS dla Delphi wylicza resztę tego, co ten silnik czyta i zapisuje