Article technique

Comparer deux classeurs Excel dans Delphi avec HotXLS

Deux copies d'un même classeur trimestriel peuvent afficher toutes les deux 1,250.00 en cellule D2 et néanmoins diverger sur ce qu'est cette cellule : dans l'une c'est un littéral saisi, dans l'autre c'est =SUM(A2:C2). Un relecteur qui bascule entre les fichiers voit des écrans identiques. Une comparaison octet par octet des deux paquets .xlsx part dans l'autre sens et signale presque tout comme différent, car une archive zip écrite deux fois coïncide rarement octet pour octet. Aucun des deux contrôles ne répond à la question que les gens posent réellement après une passe de relecture : quelles cellules, quelles formules et quelles feuilles ont changé

Cette question surgit à quelques endroits prévisibles. Un classeur financier revient de relecture, un export nocturne est régénéré après un changement de code, ou deux services renvoient leurs propres éditions d'un même modèle. L'inspection côte à côte convient pour une feuille et désespère pour vingt, et c'est exactement l'échange formule-contre-littéral ci-dessus que des yeux fatigués manquent

Diagramme de vue d'ensemble de la comparaison de classeurs HotXLS en Delphi : baseline.xlsx et reviewed.xlsx alimentent TXLSXWorkbookCompare, qui apparie les feuilles par nom, parcourt les cellules stockées, et émet des enregistrements de différences structurés plus un rapport lisible
TXLSXWorkbookCompare transforme deux classeurs HotXLS chargés en une liste de diff structurée pour la logique et un rapport lisible pour les relecteurs

Comparer deux classeurs avec TXLSXWorkbookCompare

HotXLS compare les classeurs via TXLSXWorkbookCompare dans l'unité lxCompare. Compare(Left, Right) prend deux objets TXLSXWorkbook chargés et renvoie le nombre de différences trouvées. La liste des différences est ensuite disponible par index via Diff(I) et Count, et Report ajoute une ligne lisible par différence à n'importe quel TStrings. Excel n'intervient à aucun moment ; la comparaison tourne sur le modèle objet que HotXLS a déjà chargé, si bien qu'elle fonctionne de la même façon dans un traitement console, un service ou une application 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 renvoie 1 en cas de succès ; un open manqué doit s'arrêter ici,
    // sinon on compare un classeur vide et le résultat ne veut plus rien dire
    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);              // une ligne lisible par différence
      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;

Les contrôles d'ouverture explicites comptent plus qu'on ne le pense. Compare renvoie 0 et vide sa liste quand un argument est nil, et deux classeurs vides se comparent aussi comme égaux, si bien qu'un résultat zéro ne signifie "équivalents" que si les deux fichiers ont réellement chargé. Une ligne typique de workbook-diff.txt se lit value changed: Data!A2: 10 -> 99

Qu'est-ce qui compte comme une différence par défaut ?

Sans aucune option réglée, TXLSXWorkbookCompare rapporte huit types de différence : une feuille ajoutée ou supprimée, une cellule ajoutée ou supprimée, une valeur changée, une formule changée, et une plage fusionnée ajoutée ou supprimée. Les valeurs de l'énumération sont lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded et lckMergeRemoved. Tout est mesuré par rapport au classeur de gauche comme référence, de sorte qu'un élément ajouté n'existe que sur la droite et un élément supprimé que sur la gauche

Diagramme des huit types de différences par défaut de classeurs HotXLS regroupés en niveaux feuille, cellule et fusion, chacun mesuré face au classeur de gauche comme référence, avec ligne et colonne zéro marquant les entrées de niveau feuille et de niveau fusion
Par défaut HotXLS rapporte huit sortes de différences sur trois niveaux, toutes exprimées par rapport au classeur de gauche, si bien que réordonner les feuilles ne rapporte rien du tout

Les feuilles sont appariées par nom, et la correspondance ignore la casse, si bien que Data à gauche s'apparie avec DATA à droite. Glisser un onglet vers une nouvelle position ne produit donc aucune entrée. Renommer une feuille est une autre histoire : Q3 renommé en Q3 Final apparaît comme une ligne sheet removed et une ligne sheet added, car rien dans le fichier n'enregistre que les deux sont la même feuille. Une feuille qui n'existe que d'un côté produit une seule entrée au niveau feuille plutôt qu'une ligne par cellule qu'elle contient, ce qui garde le rapport lisible quand deux classeurs diffèrent par leur structure

Les cellules sont appariées par position. La comparaison parcourt les cellules stockées de chaque paire de feuilles, et cela inclut les cellules qui ne portent que de la mise en forme. Une cellule vide mais formatée, présente à gauche et absente à droite, est signalée comme cell removed: Data!C4 was (blank). Les plages fusionnées se comparent par leur texte A1, si bien que A1:D1 élargi en A1:E1 donne un merge removed et un merge added

Pourquoi une formule et un littéral de même nombre signalent-ils un changement ?

Parce que la formule est le contenu rédigé. Quand l'un des côtés d'une paire de cellules porte une formule, TXLSXWorkbookCompare compare le texte de formule, et une formule d'un côté contre un littéral de l'autre est une entrée lckFormulaChanged même si les deux affichent 1,250. La ligne de rapport pour l'exemple d'ouverture est formula changed: Data!D2: 1250 -> =SUM(A2:C2), et l'enregistrement porte LeftKind = lcvNumber et RightKind = lcvFormula. Le texte de formule est comparé exactement tel que stocké, avec un signe égal en tête ajouté pour l'affichage

Les valeurs littérales se comparent comme des valeurs typées. Un nombre se compare avec un nombre, du texte avec du texte, et un nombre 10 contre le texte "10" est un changement même si les deux cellules impriment les mêmes caractères. Le rapport affiche les étiquettes de type dès que les types diffèrent : value changed: Data!B7: [number] 10 -> [text] 10. Ce cas est courant dans les fichiers qui passent par des allers-retours CSV ou du copier-coller utilisateur, et c'est celui qui casse les recherches plus tard

Une formule dont le texte est inchangé ne rapporte rien par défaut, même quand son résultat en cache diffère. Pour relire du contenu rédigé, c'est correct. Pour tester en régression un générateur, c'est trop laxiste, car une feuille d'entrée modifiée peut déplacer tous les totaux sans toucher une seule formule. Régler lcoFormulaCachedResults dans Options ajoute une entrée lckFormulaCacheChanged dès que les résultats en cache diffèrent, ou quand un côté porte une valeur en cache et l'autre aucune, ce qui est signalé comme (missing cache). Pour les tests de régression de générateurs, activez cette option ; un total qui dérive avec des formules inchangées est exactement la défaillance que ces tests existent pour attraper

Les résultats en cache ne sont frais qu'au dernier calcul, alors recalculez d'abord les deux classeurs quand la question est "ce que ces formules produisent aujourd'hui". TXLSXWorkbook.Recalculate renvoie lxOk en cas de succès ; itération désactivée, un cycle de références renvoie lxErrorRef. Le bruit en virgule flottante est l'autre source de fausses alertes, et SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) le traite pour les littéraux numériques comme pour les résultats en cache :

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // Remettez les deux caches à jour avant de comparer les résultats
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 absolu ou 1e-12 relatif ; les deux doivent être finis et positifs ou nuls,
  // sinon EArgumentException est levée et aucune valeur ne change
  Cmp.SetNumericTolerance(1e-9, 1e-12);

  Result := Cmp.Compare(Expected, Actual);
end;

Les tolérances valent zéro par défaut, ce qui signifie comparaison exacte. Elles ne s'appliquent qu'aux nombres. Dates, géométrie des lignes et colonnes et styles se comparent toujours exactement, si bien qu'une tolérance réglée pour des arrondis monétaires ne masquera jamais une date décalée d'une seconde

Piloter la logique à partir des différences structurées

Chaque enregistrement TlxCompareDiff expose Kind, Sheet, Row et Col indexés à un, une Ref A1, LeftText et RightText, et pour les entrées de cellules LeftKind et RightKind. Les entrées au niveau feuille et au niveau fusion portent zéro dans Row et Col. Une fois les contrôles optionnels activés, les entrées de géométrie de lignes ne portent qu'une ligne, celles de géométrie de colonnes qu'une colonne, et les fonctionnalités de classeur comme les noms définis ne portent ni l'une ni l'autre, si bien qu'on branche sur Kind plutôt que sur des coordonnées à zéro :

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;

  // Une politique de relecture qui ne bloque que sur les modifications de formule
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

L'ordre de la liste est fixe. Compare trie les différences par nom de feuille, puis ligne, colonne, type et référence, si bien que deux exécutions sur la même paire de fichiers produisent le même texte de rapport ligne pour ligne. Les noms de feuilles se trient par comparaison de chaînes ordinale, pas par position d'onglet, et les entrées à ligne zéro viennent en tête à l'intérieur du groupe de chaque feuille. Cela fait du texte de rapport un fichier golden honnête pour un test de régression : committez le workbook-diff.txt attendu et faites échouer le build quand le nouveau diffère. Pour des décisions de politique, lisez directement les champs de l'enregistrement et laissez la ligne descriptive aux humains

Activer les contrôles de mise en forme, de géométrie et d'objets

L'ensemble par défaut est une comparaison de données, et une cellule dont le format de nombre est passé de Standard à Monétaire n'y signale rien. La propriété Options élargit la comparaison une catégorie à la fois. lcoCellStyles compare le style résolu de chaque paire de cellules et lcoBlankStyleRegions fait de même pour les zones formatées sans cellules stockées, en fusionnant les différences adjacentes en rectangles dont le coin lointain se trouve dans LastRow et LastCol. lcoAxisGeometry compare hauteurs de lignes et largeurs de colonnes avec l'état masqué, niveau de plan et réduit. lcoRichTextRuns compare les runs formatés à l'intérieur des cellules de texte

Le contenu au niveau objets a ses propres commutateurs : lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (qui couvre aussi les images dans les cellules), lcoPivotTables, lcoPivotCaches, lcoSlicerState et lcoTimelineState. Ces entrées mettent la clé de la fonctionnalité dans Ref et disent via Change s'il s'agit d'un ajout, d'une suppression ou d'une modification. Noms définis, caches de tableaux croisés et caches de slicers et timelines au niveau classeur portent une Sheet vide

Lisez la sortie de ces contrôles avec les bonnes attentes. Les entrées de style, de texte enrichi et de fonctionnalités rapportent des condensats SHA-256 d'une définition normalisée dans LeftText et RightText, si bien que le rapport dit que le style de la cellule C5 a changé sans dire quelle propriété. Les entrées de géométrie sont lisibles, par exemple size=15; hidden=0; outline=0; collapsed=0, et une ligne sans hauteur explicite se compare à la hauteur standard d'une feuille, si bien qu'une ligne explicite de 15 points coïncide avec une ligne non réglée quand la hauteur standard vaut aussi 15. Gardez lcoCellStyles désactivé pour les relectures de contenu. Sur un grand classeur, il transforme chaque colonne reformattée en un mur de condensats qui cache les trois éditions de cellules que le relecteur devait approuver

// Relecture de mise en forme : géométrie, fusions et formats conditionnels seulement
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Plafonne ce que les contrôles d'objets peuvent capturer : 32 Mio, 500 000 éléments
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;

Les contrôles d'objets capturent leurs définitions avant de comparer, et SetFeatureLimits borne cette capture. Les valeurs par défaut sont 67,108,864 octets et 1,000,000 éléments ; dépasser l'un ou l'autre lève EXLSXSemanticBudget. Quand Compare lève, la liste des différences de l'appel réussi précédent reste en place, si bien qu'un appelant qui attrape l'exception ne doit pas lire Count comme le résultat de l'exécution manquée

Dates, lignes insérées et là où la comparaison porte ses fruits

Les cellules que le classeur porte comme dates se comparent comme dates, exactement, et s'impriment en date(...) avec la valeur de date numérique sous-jacente. Une date d'un côté contre un nombre simple de l'autre affiche les étiquettes de type, le signe habituel qu'une colonne a perdu son format de date en passant par un autre outil. Les systèmes de dates mélangés sont le piège ici. Un classeur sur le système de dates 1904 et un sur le système 1900 stockent le même jour du calendrier comme des numéros de série distants de 1,462, alors vérifiez Date1904 des deux côtés avant de faire confiance à une colonne de changements de dates. L'arrière-plan se trouve dans les numéros de série de date et le système 1904

L'appariement positionnel a une conséquence qu'il vaut la peine d'anticiper. Insérez une ligne près du haut d'une feuille et chaque cellule en dessous se déplace, si bien que la comparaison rapporte une suite de cellules changées, ajoutées et supprimées le long de la feuille, et les formules dont les références ont été réécrites par l'insertion se signalent comme changées. Quand vous maîtrisez le pipeline, lancez une comparaison avant la modification structurelle et une autre après, afin que chaque rapport couvre un seul type de changement. Les règles de réécriture sont traitées dans l'ajustement des références de formule lors d'insertions et de suppressions

Trois travaux justifient de câbler tout cela. Tester en régression un générateur de rapports : gardez un classeur de référence connu comme correct, régénérez, comparez avec les résultats en cache activés, et échouez sur toute différence. Relecture de changements : remettez au relecteur le rapport trié au lieu de deux fichiers. Contrôles de migration : après conversion d'un lot de classeurs hérités, comparez chaque résultat à sa source, puis comptez la mise en forme et les fonctionnalités d'objets de chaque côté avec la passe d'inventaire de l'atelier d'audit et de conversion de classeurs

Comparaison, recalcul et conversion partagent un même moteur dans HotXLS pour Delphi et C++Builder, et la page du composant tableur HotXLS Delphi liste le reste de ce que ce moteur lit et écrit