Teknik Makale

HotXLS ile İki Excel Çalışma Kitabının Farkını Alma

Aynı üç aylık çalışma kitabının iki kopyası, D2 hücresinde ikisi de 1.250,00 gösterebilir ve yine de o hücrenin ne olduğu konusunda ayrışabilir: birinde yazılmış bir sabit değer, diğerinde =SUM(A2:C2) vardır. Dosyalar arasında geçiş yapan bir inceleyici aynı ekranları görür. İki .xlsx paketinin bayt karşılaştırması ise tam tersini yapar ve neredeyse her şeyi farklı bildirir; çünkü iki kez yazılan bir zip arşivi nadiren bayt bayt eşleşir. İnceleme turundan sonra insanların gerçekte sorduğu soruya, yani hangi hücrelerin, formüllerin ve sayfaların değiştiğine, bu iki denetimden hiçbiri cevap vermez

O soru birkaç öngörülebilir yerde karşınıza çıkar. Bir finans çalışma kitabı incelemeden döner, gecelik bir dışa aktarma bir kod değişikliğinden sonra yeniden üretilir ya da iki departman tek bir şablonun kendi düzenlemelerini geri gönderir. Yan yana inceleme bir sayfa için yeterli, yirmisi için umutsuzdur ve yorgun bir çift gözün kaçırdığı şey tam da yukarıdaki formül-sabit değeri değişimidir

Delphi'de HotXLS çalışma kitabı karşılaştırmasına genel bakış şeması: baseline.xlsx ve reviewed.xlsx TXLSXWorkbookCompare'ı besler; bu, sayfaları ada göre eşleştirir, saklanan hücreleri gezer ve yapılandırılmış fark kayıtları artı okunabilir bir rapor üretir
TXLSXWorkbookCompare, yüklenmiş iki HotXLS çalışma kitabını mantık için tek bir yapılandırılmış diff listesine ve inceleyiciler için okunabilir bir rapora çevirir

TXLSXWorkbookCompare ile iki çalışma kitabını karşılaştırmak

HotXLS, lxCompare unit'indeki TXLSXWorkbookCompare üzerinden çalışma kitaplarını karşılaştırır. Compare(Left, Right) iki yüklü TXLSXWorkbook nesnesi alır ve bulunan fark sayısını döndürür. Fark listesi ardından Diff(I) ve Count ile indekse göre kullanılabilir; Report her fark için okunabilir bir satırı herhangi bir TStrings listesine ekler. Hiçbir noktada Excel devrede değildir; karşılaştırma, HotXLS'in çoktan yüklediği nesne modeli üzerinde çalışır, bu yüzden konsol işinde, servisde ya da C++Builder uygulamasında aynı şekilde çalışır

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 başarılı olunca 1 döndürür; başarısız bir açılış burada durmalı,
    // yoksa boş bir çalışma kitabı karşılaştırılır ve sonucun bir anlamı kalmaz
    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);              // fark başına okunabilir bir satır
      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;

Açık açık yazılan açılış denetimleri göründüklerinden daha önemlidir. Compare, iki argümandan biri nil olduğunda 0 döndürür ve listesini temizler; iki boş çalışma kitabı da eşit çıkar. Bu yüzden sıfır sonuç, ancak her iki dosya gerçekten yüklendiğinde "eşdeğer" anlamına gelir. workbook-diff.txt içindeki tipik bir satır value changed: Data!A2: 10 -> 99 biçiminde okunur

Varsayılan olarak ne bir fark sayılır?

Hiçbir seçenek ayarlanmadığında TXLSXWorkbookCompare sekiz tür fark bildirir: eklenen ya da kaldırılan bir sayfa, eklenen ya da kaldırılan bir hücre, değişen bir değer, değişen bir formül ve eklenen ya da kaldırılan bir birleştirilmiş aralık. Enum değerleri lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded ve lckMergeRemoved. Her şey, temel alınan sol çalışma kitabına göre ölçülür; bu yüzden eklenen bir öğe yalnızca sağda, kaldırılan bir öğe yalnızca solda var olur

Sekiz varsayılan HotXLS çalışma kitabı fark türünün şeması: sayfa, hücre ve birleştirme düzeyinde gruplanır; her biri temel olarak sol çalışma kitabına göre ölçülür ve sıfır satır ile sütun, sayfa düzeyi ve birleştirme düzeyi girdileri işaretler
Varsayılan olarak HotXLS üç düzeyde sekiz fark türü bildirir, tamamı sol çalışma kitabına göre ifade edilir; bu yüzden sayfaları yeniden sıralamak hiçbir şey bildirmez

Sayfalar ada göre eşleştirilir ve eşleşme büyük/küçük harfe duyarsızdır; soldaki Data ile sağdaki DATA çiftleşir. Bu yüzden bir sekmeyi yeni bir konuma sürüklemek hiçbir girdi üretmez. Sayfayı yeniden adlandırmak başka bir hikâye: Q3 adının Q3 Final yapılması bir sheet removed satırı ve bir sheet added satırı olarak görünür; çünkü dosyada ikisinin aynı sayfa olduğunu kaydeden hiçbir şey yoktur. Yalnızca bir tarafta var olan bir sayfa, içindeki her hücre için satır üretmek yerine tek bir sayfa düzeyi girdi verir; bu, yapı olarak ayrışan iki çalışma kitabında raporu okunabilir tutar

Hücreler konuma göre eşleşir. Karşılaştırma, her eşleşen sayfanın saklanan hücrelerinde gezer ve bu, yalnızca biçimlendirme taşıyan hücreleri de kapsar. Solda olup sağda olmayan, biçimli ama boş bir hücre cell removed: Data!C4 was (blank) olarak bildirilir. Birleştirilmiş aralıklar A1 metinlerine göre karşılaştırılır; A1:D1 aralığının A1:E1 olarak genişlemesi bir merge removed ve bir merge added üretir

Aynı sayıyı gösteren bir formül ile bir sabit değer neden değişiklik bildirir?

Çünkü formül yazılan içeriğin kendisidir. Bir hücre çiftinin iki tarafından biri formül taşıdığında TXLSXWorkbookCompare formül metnini karşılaştırır ve bir taraftaki formül ile diğer taraftaki sabit değer, ikisi de 1.250 gösterse bile bir lckFormulaChanged girdisidir. Açılış örneğinin rapor satırı formula changed: Data!D2: 1250 -> =SUM(A2:C2) biçimindedir ve kayıt LeftKind = lcvNumber ile RightKind = lcvFormula taşır. Formül metni, saklandığı gibi karşılaştırılır; gösterim için başına bir eşittir işareti eklenir

Sabit değerler yazıldıkları türde karşılaştırılır. Sayı sayıyla, metin metinle karşılaşır; 10 sayısı ile "10" metni, her iki hücre aynı karakterleri bassa bile bir değişikliktir. Türler farklılaştığında rapor tür etiketlerini gösterir: value changed: Data!B7: [number] 10 -> [text] 10. Bu durum CSV dönüşümlerinden geçen ya da kullanıcı kopyala-yapıştır'ıyla gelen dosyalarda yaygındır ve sonradan aramaları (lookup) kıran da tam olarak budur

Metni değişmemiş bir formül, önbelleğe alınmış sonucu farklı olsa bile varsayılan olarak hiçbir şey bildirmez. Yazılan içeriği incelemek için bu doğru davranıştır. Bir üreteci regresyon testine sokmak için fazla toleranslıdır; çünkü değişen bir girdi sayfası, tek bir formüle dokunmadan her toplamı kaydırabilir. Options içinde lcoFormulaCachedResults ayarı, önbelleğe alınmış sonuçlar farklılaştığında ya da bir tarafın önbellek değeri olup diğerininkinin olmadığı durumlarda bir lckFormulaCacheChanged girdisi ekler; eksik önbellek (missing cache) olarak bildirilir. Üretici regresyon testlerinde o seçeneği açın; formülleri değişmeden kayan bir toplam, o testlerin var olma amacı olan arızanın ta kendisidir

Önbelleğe alınmış sonuçlar ancak son hesaplama kadar tazedir; soru "bu formüller bugün ne üretiyor" ise önce her iki çalışma kitabını da yeniden hesaplayın. TXLSXWorkbook.Recalculate başarıda lxOk döndürür; iterasyon kapalıyken bir referans döngüsü lxErrorRef döndürür. Kayan nokta gürültüsü yanlış alarmın diğer kaynağıdır ve SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) bunu sayısal sabit değerler için de önbellek sonuçları için de halleder:

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // Sonuçları karşılaştırmadan önce her iki önbelleği de güncelle
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 mutlak ya da 1e-12 göreli; ikisi de sonlu ve negatif olmayan olmalı,
  // yoksa EArgumentException fırlatılır ve iki değer de değişmez
  Cmp.SetNumericTolerance(1e-9, 1e-12);

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

Toleranslar varsayılan olarak sıfırdır; yani tam karşılaştırma. Yalnızca sayılara uygulanırlar. Tarihler, satır ve sütun geometrisi ve stiller her zaman tam karşılaştırılır; bu yüzden para birimi yuvarlaması için ayarlanmış bir tolerans, bir saniye kaymış bir tarihi asla gizlemez

Yapılandırılmış farklardan mantık yürütmek

Her TlxCompareDiff kaydı Kind, Sheet, 1 tabanlı Row ve Col, bir A1 Ref, LeftText ve RightText açığa çıkarır; hücre girdileri için ayrıca LeftKind ve RightKind vardır. Sayfa düzeyi ve birleştirme düzeyi girdiler Row ve Col alanlarında sıfır taşır. İsteğe bağlı denetimler açıldığında satır geometrisi girdileri yalnızca satırı, sütun geometrisi girdileri yalnızca sütunu taşır ve tanımlı adlar gibi çalışma kitabı özellikleri ikisini de taşımaz; bu yüzden sıfır koordinatlar üzerine değil Kind üzerine dallanın:

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;

  // Yalnızca formül düzenlemelerinde engelleyen bir inceleme ilkesi
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Compare'ın ürettiği listenin sırası sabittir. Farklar sayfa adına, ardından satır, sütun, tür ve referansa göre sıralanır; böylece aynı dosya çifti üzerindeki iki çalışma, satır satır aynı rapor metnini üretir. Sayfa adları sekme konumuna göre değil sıralı dize karşılaştırmasıyla sıralanır ve satırı sıfır olan girdiler her sayfanın grubunda en başta gelir. Bu, rapor metnini bir regresyon testi için adil bir altın dosya yapar: beklenen workbook-diff.txt dosyasını commit edin ve yenisi farklılaştığında derlemeyi düşürün. Politika kararlarında kayıt alanlarını doğrudan okuyun; açıklayıcı satırı insanlara bırakın

Biçimlendirme, yerleşim ve nesne denetimlerini açmak

Varsayılan küme bir veri karşılaştırmasıdır; sayı biçimi General'den Currency'ye kayan bir hücre altında hiçbir şey bildirmez. Options özelliği karşılaştırmayı bir kategoride bir kez genişletir. lcoCellStyles her eşleşen hücrenin çözümlenmiş stilini karşılaştırır; lcoBlankStyleRegions aynı işi saklanan hücresi olmayan biçimli alanlar için yapar ve komşu farkları, uzak köşesi LastRow ve LastCol alanlarında duran dikdörtgenlere birleştirir. lcoAxisGeometry satır yüksekliklerini ve sütun genişliklerini, gizli, anahat düzeyi ve daraltılmış durumlarıyla birlikte karşılaştırır. lcoRichTextRuns metin hücrelerinin içindeki biçimli bölütlere bakar

Nesne düzeyi içeriğin kendi anahtarları vardır: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (hücre içi görüntüleri de kapsar), lcoPivotTables, lcoPivotCaches, lcoSlicerState ve lcoTimelineState. Bu girdiler özelliğin anahtarını Ref içine koyar ve özelliğin eklenip eklenmediğini, kaldırıldığını ya da değiştiğini Change'den söyler. Tanımlı adlar, pivot önbellekleri ve çalışma kitabı düzeyindeki slicer ile timeline önbellekleri boş bir Sheet taşır

Bu denetimlerin çıktısını doğru beklentilerle okuyun. Stil, zengin metin ve özellik girdileri, normalize edilmiş bir tanımın SHA-256 özetlerini LeftText ile RightText içine yazar; yani rapor, C5 hücresinin stilinin değiştiğini söyler ama hangi özelliğin değiştiğini söylemez. Geometri girdileri okunabilirdir, örneğin size=15; hidden=0; outline=0; collapsed=0; açık yüksekliği olmayan bir satır, sayfanın standart yüksekliğinde karşılaştırılır, bu yüzden standart yükseklik de 15 olduğunda açıkça 15 puntoluk yazılmış bir satır ayarlanmamış bir satırla eşleşir. İçerik incelemelerinde lcoCellStyles seçeneğini kapalı tutun. Büyük bir çalışma kitabında bu seçenek, inceleyiciden onaylaması istenen üç hücre düzenlemesini gizleyen bir özet duvarına her yeniden biçimlenen sütunu çevirir

// Yerleşim incelemesi: yalnızca geometri, birleştirmeler ve koşullu biçimler
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Nesne denetimlerinin yakalayabileceğini sınırla: 32 MiB, 500.000 öğe
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;

Nesne denetimleri tanımlarını karşılaştırmadan önce yakalar ve SetFeatureLimits o yakalamayı sınırlar. Varsayılanlar 67.108.864 bayt ve 1.000.000 öğedir; ikisini aşmak da EXLSXSemanticBudget fırlatır. Compare istisna fırlattığında önceki başarılı çağrının fark listesi yerinde kalır; bu yüzden istisnayı yakalayan bir çağıran, başarısız çalışmanın sonucu olarak Count'u okumamalıdır

Tarihler, eklenen satırlar ve diff'in işe yaradığı yerler

Çalışma kitabının tarih olarak tuttuğu hücreler tarih olarak, tam olarak karşılaştırılır ve temeldeki sayısal tarih değeriyle date(...) olarak basılır. Bir tarafta tarih, diğer tarafta düz sayı olan bir hücre tür etiketlerini gösterir; bir sütunun başka bir araçtan geçerken tarih biçimini kaybettiğinin sıradan işareti budur. Asıl tuzak karışık tarih sistemleridir. 1904 tarih sistemindeki bir çalışma kitabı ile 1900 sistemindeki bir çalışma kitabı, aynı takvim gününü 1.462 farkla ayrılan seri değerleri olarak saklar; bu yüzden bir sütunluk tarih değişikliğine güvenmeden önce iki tarafta da Date1904'e bakın. Arka plan tarih seri numaraları ve 1904 sistemi başlığındadır

Konumsal eşleşmenin planlanmaya değer bir sonucu vardır. Bir sayfanın tepesine yakın bir satır ekleyin, altındaki her hücre kayar; karşılaştırma sayfa boyunca değişmiş, eklenmiş ve silinmiş bir hücre dizisi bildirir ve referansları ekleme tarafından yeniden yazılan formüller değişmiş olarak raporlanır. Hattı sizin kontrol ettiğinizde yapısal düzenlemeden önce bir karşılaştırma ve ardından bir karşılaştırma daha çalıştırın; böylece her rapor tek tür değişikliği kapsar. Yeniden yazma kuralları ekleme ve silmede formül referansı düzeltmesi başlığında ele alınır

Bu işi kurmaya değer üç iş vardır. Bir rapor üretecinin regresyon testi: bilinen iyi bir çalışma kitabını tutun, yeniden üretin, önbellek sonuçları açık şekilde karşılaştırın ve herhangi bir farkta başarısız olun. Değişiklik incelemesi: inceleyiciye iki dosya yerine sıralanmış raporu verin. Geçiş denetimleri: bir grup eski çalışma kitabını dönüştürdükten sonra her sonucu kaynağıyla karşılaştırın, ardından her iki tarafın biçimlendirme ve nesne özelliklerini çalışma kitabı denetimi ve dönüştürme çalışma tezgahı başlığındaki envanter geçişiyle sayın

Karşılaştırma, yeniden hesaplama ve dönüştürme, HotXLS'te Delphi ve C++Builder için tek bir motoru paylaşır; o motorun okuyup yazdığı gerisini HotXLS Delphi elektronik tablo bileşeni sayfası listeler