Twee exemplaren van dezelfde kwartaalwerkmap kunnen allebei 1,250.00 tonen in cel D2 en het toch oneens zijn over wat die cel is: in de ene is het een getypte literal, in de andere =SUM(A2:C2). Een reviewer die tussen de bestanden wisselt ziet identieke schermen. Een bytevergelijking van de twee .xlsx-pakketten slaat de andere kant op en meldt bijna alles als verschillend, want een zip-archief dat twee keer wordt geschreven matcht zelden byte voor byte. Geen van beide controles beantwoordt de vraag die mensen na een beoordelingsronde echt stellen: welke cellen, formules en sheets zijn er veranderd
Die vraag komt op een paar voorspelbare plekken naar boven. Een financiële werkmap komt terug van review, een nachtelijke export wordt na een codewijziging opnieuw gegenereerd, of twee afdelingen sturen hun eigen bewerkingen van één sjabloon terug. Naast elkaar leggen werkt voor één sheet en is kansloos bij twintig, en precies de formule-tegen-literal-wissel hierboven ontgaat een moe paar ogen
Twee werkmappen vergelijken met TXLSXWorkbookCompare
HotXLS vergelijkt werkmappen via TXLSXWorkbookCompare in de unit lxCompare. Compare(Left, Right) neemt twee geladen TXLSXWorkbook-objecten aan en retourneert het aantal gevonden verschillen. De verschillenlijst is daarna via index beschikbaar met Diff(I) en Count, en Report plakt één leesbare regel per verschil aan elke TStrings. Excel komt nergens aan te pas; de vergelijking draait op het objectmodel dat HotXLS al heeft geladen, dus hij werkt hetzelfde in een consoletaak, een service of een C++Builder-toepassing
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 geeft 1 terug bij succes; een mislukte open moet hier stoppen,
// anders wordt een lege werkmap vergeleken en betekent het resultaat niets
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); // één leesbare regel per verschil
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;
De expliciete open-controles tellen zwaarder dan ze lijken. Compare retourneert 0 en leegt zijn lijst wanneer één van beide argumenten nil is, en twee lege werkmappen vergelijken ook als gelijk, dus een nulresultaat betekent alleen "equivalent" als beide bestanden echt geladen zijn. Een typische regel in workbook-diff.txt luidt value changed: Data!A2: 10 -> 99
Wat telt standaard als een verschil?
Zonder opties meldt TXLSXWorkbookCompare acht soorten verschillen: een sheet toegevoegd of verwijderd, een cel toegevoegd of verwijderd, een veranderde waarde, een veranderde formule, en een samengevoegd bereik toegevoegd of verwijderd. De enum-waarden zijn lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded en lckMergeRemoved. Alles wordt gemeten tegen de linkerwerkmap als baseline, dus een toegevoegd item bestaat alleen rechts en een verwijderd item alleen links
Sheets worden op naam gepaard, en de match neemt hoofdletters niet mee, dus Data links paart met DATA rechts. Een tabblad naar een nieuwe positie slepen levert dus geen invoer op. Een sheet hernoemen is een ander verhaal: Q3 die Q3 Final wordt, verschijnt als één regel sheet removed en één regel sheet added, want niks in het bestand legt vast dat het om dezelfde sheet gaat. Een sheet die maar aan één kant bestaat levert één invoer op sheetniveau op in plaats van één regel per cel erin, wat het rapport leesbaar houdt wanneer twee werkmappen van structuur verschillen
Cellen worden op positie gepaard. De vergelijking loopt de opgeslagen cellen van elk gepaard sheet langs, en dat omvat cellen die alleen opmaak bevatten. Een opgemaakte maar lege cel die links bestaat en rechts niet, wordt gemeld als cell removed: Data!C4 was (blank). Samengevoegde bereiken worden vergeleken op hun A1-tekst, dus A1:D1 verbreed naar A1:E1 levert één merge removed en één merge added op
Waarom meldt een formule en een literal met hetzelfde getal een wijziging?
Omdat de formule de geschreven inhoud is. Houdt één van beide kanten van een celpaar een formule, dan vergelijkt TXLSXWorkbookCompare de formuletekst, en een formule tegenover een literal is een lckFormulaChanged-invoer, ook als allebei 1,250 tonen. De rapportregel voor het openingsvoorbeeld is formula changed: Data!D2: 1250 -> =SUM(A2:C2), en het record draagt LeftKind = lcvNumber en RightKind = lcvFormula. Formuletekst wordt exact vergeleken zoals hij is opgeslagen, met een voorloop-isgelijkteken voor de weergave
Letterlijke waarden vergelijken als getypte waarden. Een getal vergelijkt met een getal, tekst met tekst, en het getal 10 tegenover de tekst "10" is een wijziging, ook al printen beide cellen dezelfde tekens. Het rapport toont de typelabels zodra de soorten verschillen: value changed: Data!B7: [number] 10 -> [text] 10. Dat geval is gebruikelijk in bestanden die een CSV-ronde hebben gemaakt of door gebruikers zijn gekopieerd en geplakt, en het is het geval dat later opzoekingen breekt
Een formule waarvan de tekst ongewijzigd is meldt standaard niets, ook niet als zijn gecachte resultaat verschilt. Voor het reviewen van geschreven inhoud is dat correct. Voor regressietests van een generator is het te mild, want een gewijzigd invoerblad kan elk totaal verschuiven zonder ook maar één formule aan te raken. lcoFormulaCachedResults in Options zetten voegt een lckFormulaCacheChanged-invoer toe zodra de gecachte resultaten verschillen, of wanneer één kant een gecachte waarde heeft en de ander geen, wat als (missing cache) wordt gemeld. Zet die optie aan voor generatorregressietests; een totaal dat afdwaalt met ongewijzigde formules is precies de fout die die tests moeten vangen
Gecachte resultaten zijn hooguit zo vers als de laatste berekening, dus herrekent u eerst beide werkmappen als de vraag is "wat produceren deze formules vandaag". TXLSXWorkbook.Recalculate geeft lxOk terug bij succes; zonder iteratie geeft een referentiecyclus lxErrorRef. Drijvende-kommaruis is de andere bron van valse alarmen, en SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) lost dat op, voor numerieke literals net zo goed als voor gecachte resultaten:
uses
SysUtils, lxCalc, lxHandleX, lxCompare;
function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare): Integer;
begin
// Werk beide caches bij voordat u resultaten vergelijkt
if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
raise Exception.Create('recalculation failed');
Cmp.Options := [lcoFormulaCachedResults];
// 1e-9 absoluut of 1e-12 relatief; allebei moeten eindig en niet-negatief zijn,
// anders wordt EArgumentException gegooid en verandert geen van beide waarden
Cmp.SetNumericTolerance(1e-9, 1e-12);
Result := Cmp.Compare(Expected, Actual);
end;
De toleranties staan standaard op nul, wat exact vergelijken betekent. Ze gelden alleen voor getallen. Datums, rij- en kolomgeometrie en stijlen vergelijken altijd exact, dus een tolerantie die voor valuta-afronding is getuned verbergt nooit een datum die een seconde is opgeschoven
Logica aansturen vanuit de gestructureerde verschillen
Elk TlxCompareDiff-record toont Kind, Sheet, de bij-één-beginnende Row en Col, een A1-Ref, LeftText en RightText, en voor celinvoer LeftKind en RightKind. Invoer op sheet- en samenvoegingsniveau draagt nul in Row en Col. Zodra optionele controles aan staan, draagt rijgeometrie alleen een rij, kolomgeometrie alleen een kolom, en werkmapfuncties als benoemde bereiken dragen allebei niet, dus tak af op Kind in plaats van op nulcoördinaten:
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;
// Een beoordelingsbeleid dat alleen blokkeert op formulewijzigingen
if FormulaEdits > 0 then
raise Exception.CreateFmt(
'%d formula change(s) need sign-off', [FormulaEdits]);
end;
De volgorde van de lijst is vast. Compare sorteert de verschillen op sheetnaam, dan rij, kolom, soort en verwijzing, dus twee runs over hetzelfde bestandspaar produceren dezelfde rapporttekst, regel voor regel. Sheetnamen sorteren op ordinale stringvergelijking, niet op tabbladpositie, en invoer met rij nul komt binnen een sheet's groep als eerste. Dat maakt de rapporttekst een prima golden file voor een regressietest: commit de verwachte workbook-diff.txt en laat de build falen zodra de nieuwe afwijkt. Voor beleidsbeslissingen lees je de recordvelden direct en laat je de beschrijvende regel aan mensen
Aanmelden voor opmaak-, layout- en objectcontroles
De standaardset is een datavergelijking, en een cel waarvan de getalnotatie van General naar Currency ging meldt daaronder niets. De property Options verbreedt de vergelijking één categorie per keer. lcoCellStyles vergelijkt de opgeloste stijl van elke gepaarde cel en lcoBlankStyleRegions doet hetzelfde voor opgemaakte gebieden zonder opgeslagen cellen, waarbij aangrenzende verschillen samenvoegen tot rechthoeken waarvan de verre hoek in LastRow en LastCol zit. lcoAxisGeometry vergelijkt rijhoogten en kolombreedtes samen met de verborgen-, overzichtsniveau- en samengeklapte status. lcoRichTextRuns vergelijkt opgemaakte runs in tekstcellen
Inhoud op objectniveau heeft eigen schakelaars: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (die ook afbeeldingen in cellen dekt), lcoPivotTables, lcoPivotCaches, lcoSlicerState en lcoTimelineState. Die invoer zetten de feature's sleutel in Ref en zeggen via Change of hij is toegevoegd, verwijderd of veranderd. Benoemde bereiken, pivotcaches en de slicer- en timelinecaches op werkmapniveau dragen een lege Sheet
Lees de uitvoer van deze controles met de juiste verwachtingen. Stijl-, rich-text- en feature-invoer rapporteren SHA-256-digests van een genormaliseerde definitie in LeftText en RightText, dus het rapport zegt dat cel C5's stijl is veranderd, maar niet welke eigenschap. Geometrie-invoer is leesbaar, bijvoorbeeld size=15; hidden=0; outline=0; collapsed=0, en een rij zonder expliciete hoogte wordt vergeleken op de standaardhoogte van het werkblad, dus een expliciete rij van 15 punten matcht een niet-ingestelde zodra de standaardhoogte ook 15 is. Houd lcoCellStyles uit voor inhoudsreviews. Op een grote werkmap verandert hij elke geherformatteerde kolom in een muur van hashes die de drie celbewerkingen verbergt waarvoor de reviewers' goedkeuring is gevraagd
// Layoutreview: alleen geometrie, samenvoegingen en voorwaardelijke opmaak
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Begrens wat de objectcontroles mogen vastleggen: 32 MiB, 500000 items
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;
De objectcontroles leggen hun definities vast vóór het vergelijken, en SetFeatureLimits begrenst die vastlegging. De defaults zijn 67.108.864 bytes en 1.000.000 items; eroverheen gaan gooit EXLSXSemanticBudget. Wanneer Compare gooit, blijft de verschillenlijst van de vorige geslaagde aanroep staan, dus een aanroeper die de exception vangt moet Count niet lezen als het resultaat van de mislukte run
Datums, ingevoegde rijen en waar diffen zich terugbetaalt
Cellen die de werkmap als datum vasthoudt vergelijken als datums, exact, en printen als date(...) met de onderliggende numerieke datumwaarde. Een datum tegenover een gewoon getal toont de soortlabels, het gebruikelijke teken dat een kolom zijn datumnotatie is kwijtgeraakt op weg door een ander gereedschap. Gemengde datumsystemen zijn hier de valkuil. Een werkmap op het 1904-datumsysteem en een op het 1900-systeem slaan dezelfde kalenderdag op als seriën die 1.462 uit elkaar liggen, dus controleer Date1904 aan beide kanten voordat je een kolom datumwijzigingen vertrouwt. De achtergrond staat in datumseriële getallen en het 1904-systeem
Positioneel paren heeft één gevolg om op voorhand rekening mee te houden. Voeg een rij in nabij de bovenkant van een sheet en elke cel eronder schuift op, dus de vergelijking meldt een reeks veranderde, toegevoegde en verwijderde cellen naar beneden, en formules waarvan de verwijzingen door de invoeging zijn herschreven melden zich als veranderd. Beheer je de pijplijn zelf, dan draai je één vergelijking vóór de structurele bewerking en één erna, zodat elk rapport één soort wijziging dekt. De herschrijfregels staan in formuleverwijzingen aanpassen bij invoegen en verwijderen
Drie taken rechtvaardigen de inbouw. Regressietests van een rapportgenerator: houd een bekend-goede werkmap vast, genereer opnieuw, vergelijk met gecachte resultaten aan, en faal bij elk verschil. Wijzigingsreview: geef de reviewer het gesorteerde rapport in plaats van twee bestanden. Migratiecontroles: vergelijk na het converteren van een batch legacy-werkmappen elk resultaat met zijn bron, tel dan de opmaak- en objectfuncties aan beide kanten met de inventarisatiepas uit de werkmapaudit- en conversiewerkbank
Vergelijking, herberekening en conversie delen één engine in HotXLS voor Delphi en C++Builder, en de HotXLS Delphi spreadsheetcomponent-pagina somt de rest op van wat die engine leest en schrijft