HotXLS Deep Recalc ile Excel Formül Önbelleği Denetimi

HotXLS, her spreadsheet işlem hattının er ya da son sormak zorunda kaldığı soruyu cevaplar: çalışma kitabında saklanan sayılar, onları üreten formüllerle hâlâ örtüşüyor mu? CalculateAndVerify, bütün bağımlılık grafiğini izole bir overlay'e yeniden hesaplar, her sonucu hücrede duran önbellek değeriyle karşılaştırır ve uyuşmazlıkları bildirir. Varsayılan olarak hiçbir şeyi değiştirmez

Bunun önem taşımasının nedeni, bir spreadsheet dosyasının her formül hücresi için iki şey saklamasıdır: formül ve biri onun için en son hesapladığı değer. Excel ikisini senkron tutar. Dünyanın geri kalanı tutmayabilir. Eski bir kütüphaneden geçmiş, kısmi yeniden hesaplanmış, elle düzenlenmiş bir XML parçası taşıyan ya da değerleri yeniden hesaplamadan yazan bir aracın elinden çıkmış bir dosya, girdilerinden artık türemeyen bir toplamı gönül rahatlığıyla sunar ve dosya formatında bunu işaretleyen hiçbir şey yoktur

Formülüyle uyuşmayan bir önbellek değeri neden bu kadar tehlikelidir?

Çünkü her sıradan okuma yolunda görünmezdir. Dosyayı bir görüntüleyicide açın, hücreyi bir API üzerinden okuyun, CSV'ye ya da PDF'e aktarın; aldığınız önbellekteki sayıdır. Formül aynı hücrenin içinde sağda durur ve kimse ikisini karşılaştırmaz. Uyuşmazlık, ancak biri çalışma kitabını Excel'de açtığında (çoğu ayarda yüklemede yeniden hesaplar) ortaya çıkar ve geçen çeyrek onaylanmış bir rapor bir anda farklı toplamlar gösterir

Denetim, o karşılaştırmayı bir kaza olmaktan çıkarıp kasıtlı, planlanmış bir işlem yapmak için vardır. Spreadsheet dünyasının checksum doğrulamasıdır: girdi işlem hattında çalışacak kadar ucuzdur ve sessiz bir veri bütünlüğü sorununu üzerine hareket edebileceğiniz bir rapora çeviren tek şeydir

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Üç overload vardır ve üç farklı soruya cevap verirler. Parametresiz CalculateAndVerify bir uyuşmazlık sayısı döndürür; sağlık kontrolü için gereken de budur. Uyuşmazlıkları out dizisiyle alan overload hücreleri verir. TXLSRecalcAuditOptions alan overload ise tam bir TXLSCalculationAuditReport döndürür; yalnızca bir değerin uyuşmadığını değil, denetimin bir şeyi neden değerlendiremediğini bilmeniz gerektiğinde uzanacağınız da odur

Overlay ve denetimin neden yazmadığı

Yeniden hesaplanan her değer hücre önbelleğine değil bir overlay'e düşer ve overlay, her iki çalışma kitabı motorunda da hücre okuma callback'inin en başına enjekte edilir. Denetimi kendi içinde tutarlı yapan yerleşim budur: B1 yeniden hesaplandığında ve C1, B1'e bağımlıysa C1, bayat önbellek değerini değil bu denetim geçişinin değerini görür. Bu olmadan tek bir yukarı akış hatası bir kez bildirilip sonra yutulur ve her aşağı akış hücresi yanlış bir girdiyle hemfikir görünürdür

Yeniden hesaplanan değeri önbellekle eşleşen hücreler overlay'e hiç girmez. Bu mikro optimizasyon değil, denetimi makul maliyetli tutan şeydir. Yüz bin formüllü temiz bir çalışma kitabı sıfır overlay yazması yapar ve geçiş, tam yeniden hesaplamaya göre 1.35x bütçenin içinde kalır; bu, her girdide çalıştırabileceğiniz bir şeyle çeyrekte bir çalıştırdığınız bir şey arasındaki farktır

Değerlendirme, bağımlılık grafiğinden türetilen seriyel bir topolojik sırayı izler; önce her node kirli işaretlenir, böylece her hücre girdilerinden sonra tam bir kez hesaplanır. Saklanmış bir çalışma kitabını denetlemek yerine canlı bir kitabı güncel tutan artımlı mekanizmayı istiyorsanız, o ayrı bir mekanizmadır ve artımlı yeniden hesaplama ve bağımlılık grafiği makalesinde anlatılır

Başarısızlıklar yığın hâlinde değil sınıflandırılmış gelir

Denetimin değerlendiremediği bir hücre, değeri uyuşmayan bir hücreyle aynı bulgu değildir ve TXLSCalculationAuditIssueKind kategorileri ayrı tutar. xlcaiCacheMismatch değer uyuşmazlığıdır. xlcaiMissingFunction ile xlcaiMissingName, değerlendiricinin uygulatmadığı ya da çözümleyemediği bir şeyle karşılaştığını söyler. xlcaiUnsupportedArguments, desteklenen alt küme dışındaki argüman biçimlerini kapsar. xlcaiExternalReferenceDenied ile xlcaiExternalReferenceMissing, politika reddini olmayan bir çalışma kitabından ayırır. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled ve xlcaiInternalFailure seti tamamlar

Bir ayrım söylemeye değer; çünkü yaygın bir varsayımı tersine çevirir. Pozitif bir Excel hata kodu bir sonuçtur, başarısızlık değil. Meşru biçimde #DIV/0! değerine varan bir hücre doğru hesaplanmıştır; bu yüzden denetim o hatayı overlay'e yazar ve önbellekle herhangi bir değer gibi karşılaştırır. Kasıtlı hata hücreleriyle dolu bir çalışma kitabı sıfır bulgu üretir, değerler önbelleğe alındığından beri bir hatanın belirdiği ya da kaybolduğu bir çalışma kitabı ise tam istediğiniz bulguları üretir

Döngüsel referanslar ayrı bir muamele görür. Döngüdeki node'lar topolojik sıraya hiç giremez; her biri ayrı ayrı xlcaiCircularReference olarak bildirilir ve denetim, iteratif çözücüyü çalıştırmaz. Bu kasıtlı bir salt okunur sözleşmedir: iterasyonun açık olup olmaması, sonuç kodunun nasıl yorumlanacağını etkiler, denetimin ne yaptığını değil. İteratif değerlendirmenin mekaniği ayrıca iteratif hesaplama ve döngüsel referanslar makalesinde ele alınır

Bir başarısızlık zincirini okuma

Bir formül değerlendirilemediğinde hangi hücrenin kaldığını bilmek nadiren yeterlidir; çünkü başarısızlık genellikle bir referans zincirinde üç seviye aşağıdadır. Bu yüzden her bulgu, en dış çerçeveden içe doğru dizilmiş bir Stack dizgesi taşır (Sheet1!A1 > Sheet1!B2 > Data!C7 biçiminde); rapor, tesadüfen baktığınız hücreyi değil gerçekten kıran hücreyi işaret eder

Kaydedicinin sınırları vardır. MaxStackFrames varsayılanı 64'tür, tabanı 8'dir ve saklanan, en derindeki başarısız zinciridir: başarısızlık orada kök saldığında iç çerçeve zinciri kaydeder, ardından sökülen dış çerçeveler onun üzerine yazmaz. Herhangi bir zincir bütçeyi aştıysa Report.StackTruncated kurulur; bu, kısa bir zincirle tamamını görmediğiniz bir zincir arasındaki farkı söyler

// Varsayılan davranış salt okunur. ApplyResults, overlay'i yalnızca
// tümüyle başarılı bir denetimden sonra uygular; yazma guardı, denetim
// sürerken çalışma kitabı yapısı değişirse commit'i reddeder
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // kesin karşılaştırma, sürüklenmeyi görünür kılar
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // denetim bir sonraki node sınırında durur
end;

Denetime çalışma kitabını ne zaman onartmalısınız?

Yalnızca denetim, başarısızlık sınıfı bulgulardan tümüyle temiz döndüğünde; bu da tam olarak ApplyResults'un sizin adınıza dayattığı koşuldur. Commit, tümüyle başarılı bir geçişten sonra, iptal edilmemişken ve yapısal bir guard'dan geçtikten sonra gerçekleşir: binary motor bir çalışma kitabı değişiklik tanımlayıcısını izler, OOXML motoru sayfa başına yapı neslinin anlık görüntüsünü alır. Denetim sürerken herhangi bir şey kaydıysa sonuçlar artık var olmayan bir çalışma kitabını tarif eder ve commit reddedilir

Kasıtlı asimetriye dikkat. Önbellek uyuşmazlıkları uygulamayı engellemez; çünkü commit'in onarmak için var olduğu şey tam da onlardır. Başarısızlık sınıfı bulgular engeller; çünkü bazı formülleri değerlendirilememiş bir çalışma kitabı yarım onarılmış olur ve yarım onarılmış bir kitap, güvenmemesi gerektiğini bildiğiniz onarılmamış bir kitaptan kötüdür

Tolerans bir varsayılan değil, politika kararıdır

Varsayılan karşılaştırma, göreli tolerans kapalıyken 1E-6 mutlak toleranstır; klasik davranışı korur ve 4E-7'lik bir sürüklenmeyi sessizce kabul eder. Bu genellikle doğrudur: dosyayı üreten her neyse onunla güncel değerlendirici arasındaki kayan nokta değerlendirme sırası farkları, uzun toplamlarda bu büyüklükte farklar üretir ve bunları bütünlük bulgusu olarak bildirmek gürültüdür

Soru farklıysa her iki toleransı da sıfıra çekin: bir değerlendiricinin sürümler arasında davranış değiştirip değiştirmediğini ya da üçüncü taraf bir aracın değerleri sinsi biçimde farklı yeniden yazıp yazmadığını öğrenmeye çalışıyorsunuzdur. Sıfırda aynı 4E-7'lik sürüklenme görünür olur; gerisi de öyle. Toleransı sorduğunuz soruya göre seçin ve tercihi raporun yanına yazın; toleransı olmayan bir rapor yorumlanamaz

Komşu iki yetenek tabloyu tamamlar. Tek bir formülün neden o değeri ürettiğini bilmek istediğinizde doğru araç, formül değerlendirme izleyicisindeki adım adım görünümdür. Önbellek değerlerinin hiç yeniden hesaplanmadan sayılmasını kasıtlı olarak istediğinizde (örneğin dosyayı geldiği gibi birebir üretmek zorunda olan bir girdi yolunda) o mod, önbellekteki formül değerlerini yeniden hesaplamadan okuma makalesinde anlatılır. Denetim, ikisinin arasında duran şeydir: önbelleğe güvenmenin güvenli olup olmadığını söyler. Hem binary hem OOXML motorları için HotXLS Delphi spreadsheet bileşeni ile birlikte gelir