Delphi'de XLS Kaydı Formülleri Neden Yeniden Hesaplıyor

Yerel Delphi ve C++Builder Excel kütüphanesi HotXLS, klasik bir BIFF8 .xls workbook'unu önce önbellek mantığıyla kaydediyor: TXLSWorksheet.WriteFormula, Excel'in her formülün yanında sakladığı değeri TXLSWorkbook.TryGetCachedFormulaValue üzerinden soruyor ve değerlendiriciyi yalnızca önbellek eksik ya da geçersiz kılındığında çağırıyor. Açıp hiç dokunmadığınız bir workbook aynı sayıları geri yazıyor ve taze sonuçlar SaveAs işleminin gizli bir yan etkisi olmak yerine tek bir açık Recalculate çağrısı gerektiriyor

Bu sözleşmeyi açığa çıkaran hata utanç verecek kadar küçüktü. nested-subtotals.xls adlı bir corpus dosyası, R2C4 hücresinde önbelleğe alınmış değeri 37 olan bir genel toplam tutuyor. HotXLS ile açın, hücre için TryGetCachedFormulaValue çağırın, 37 alın. Tek bir hücreyi değiştirmeden kaydedin, kaydedilen kopyayı açın, aynı soruyu sorun, 67 alın. API'den hiçbir şey hesaplaması istenmemişti, ama dosyadaki bir sayı tam 30 kaymıştı — ve 30, genel toplamın kapsadığı aralıkta duran iki grup ara toplamının, 10 ile 20'nin toplamıydı

Bir XLS dosyasını kaydetmek neden formül değerini değiştiriyor?

O 37'nin 67 olması için iki bağımsız kusurun aynı hizaya gelmesi gerekiyordu ve ikisinden yalnızca birini düzeltmek diğerini gizlerdi. Birincisi yapısaldı: klasik yazıcı her kayıtta her formülü yeniden hesaplıyordu. İkincisi ise diskten yüklenen bir formül için hiçbir zaman doğru olamayan bir tip kontrolüydü ve değerlendiricinin iç içe SUBTOTAL hücrelerini iki kez saymasına yol açıyordu. Corpus dosyası, kayıt sırasındaki bir yeniden hesaplamanın Excel'den farklı bir yanıt ürettiği ve birinin ikisini karşılaştırdığı ilk girdiydi. Yapısal kusuru anlatmak kolay: v2.382.3 öncesinde TXLSWorksheet.WriteFormula ve shared formula kardeşi WriteFormulaWithTExp, her Formula kaydının sekiz baytlık FormulaValue alanını TXLSWorkbook.GetFormulaValue çağırarak elde ediyordu, ki bu da değerlendiricinin kendisidir. ParseFormulanın yükleme sırasında kaynak dosyadan özenle çözdüğü önbelleğe çıkış yolunda hiç danışılmıyordu. Yani her kayıt, workbook düzeyindeki recalc API'si atlanmış tam bir yeniden hesaplamaydı; workbook üzerinde ayarlayabileceğiniz hiçbir şey bunu durduramazdı. HotXLS değerlendiricisinin Excel ile ayrıştığı her yer — ister meşru biçimde desteklenmeyen bir fonksiyon ister düz bir hata olsun — kayıtta sessiz bir veri değişikliğine dönüşüyordu

İkinci kusur, değerlendiricinin kullandığı iç içe subtotal callback'inde yaşıyordu. Excel her SUBTOTAL biçimini, kendi formülü başka bir SUBTOTAL olan hücreleri yok sayacak biçimde tanımlar; bu yüzden lxCalc.pas içindeki hesaplayıcı toplama sırasında FIgnoreSubtotalCells bayrağını kaldırır ve aralıktaki her hücrenin böyle olup olmadığını workbook'a, TXLSWorkbook.GetClassicIsSubtotalCell üzerinden sorar. Bu callback formül metnini Variant olarak alıp VarType(f) = varOleStr ile test ediyordu. Metin GetUnCompiledFormula fonksiyonundan bir Delphi String olarak döner ve bir Variant'a atanan String varUString olur, asla varOleStr olmaz. Yüklem yüklenen her dosyadaki her hücre için false döndü, grup ara toplamları genel toplama ikinci kez katıldı ve her şeyi yeniden hesaplayan bir kayıtta 10 + 20 + 7, 67 oldu

// HotXLS 2.381 ve öncesi: String'den kurulan bir formül Variant'ı
// varUString olur, bu yüzden bu karşılaştırma hiç başarılı olmadı
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr varString, varOleStr ve varUString kabul eder,
// ve Excel'in yaptığı gibi AGGREGATE kapsayan subtotal'ların dışında tutulur
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 VarIsStr düzeltmesini yayınladı ve aynı fonksiyonun içindeyken callback'e AGGREGATE hücrelerinin de kapsayan subtotal'ların dışında tutulduğunu öğretti. Bu tek başına corpus assertion'ını geçirdi, çünkü yeniden hesaplanan 37 artık yüklenen 37 ile eşleşiyordu. Ama kütüphaneyi dürüst yapmadı: kayıt hâlâ yeniden hesaplıyordu ve test yalnızca değerlendirici o dosyada şans eseri Excel ile uyuştuğu için yeşildi. SUBTOTAL ile AGGREGATEin hangi hücreleri, gizli satırlar dahil, atladığının kuralları SUBTOTAL ve AGGREGATE gizli satır makalesinde ele alınıyor; burada önemli olan, hesaplamasını istemediğiniz bir dosyada hiçbir değerlendiricinin oy hakkı olmaması gerektiği

Excel kayıt sırasında önbelleğe alınmış değerler için ne garanti ediyor?

Excel bir kaydı hesaplama olayı değil, anlık görüntü olarak ele alır. Bir Formula kaydının FormulaValue alanına yazılan değer ([MS-XLS] §2.4.127, düzeni §2.5.133) hücrenin o anda gösterdiği şeydir; elle hesaplama modunda bu yıllarca bayat olabilir ve Excel onu yine de sadakatle yazar. Yeniden hesaplama, kendi tetikleyicisi olan ayrı bir işlemdir. HotXLS artık klasik kayıtlar için aynı kuralı izliyor: WriteFormula ve WriteFormulaWithTExp önce TryGetCachedFormulaValue çağırıyor, durum xlfcsLoaded ya da xlfcsCalculated ise CacheInfo.Value değerini alıyor ve GetFormulaValue yoluna yalnızca xlfcsMissing ile xlfcsInvalidated için düşüyor. Bu sözleşmenin okuma tarafı, her durumun ne anlama geldiği ve önbelleğe alınmış bir boş ya da False değerin neden yine de bir değer sayıldığı dahil, Delphi'de Excel Önbelleğe Alınmış Formül Değerlerini Yeniden Hesap Yapmadan Okuma yazısında anlatılıyor

Yedek yol bilinçli olarak korunuyor, kaldırılmıyor. Bu oturumda Cells[Row, Col].Formula üzerinden atadığınız bir formül önbelleksiz gelir ve yüklenmiş bir hücrede değiştirdiğiniz formül _SetCompiledFormula tarafından xlfcsInvalidated olarak işaretlenir; ikisi de kayıt sırasında eskisi gibi değerlendirilir, böylece üretilmiş bir workbook Excel'de yine içinde sayılarla açılır. Değerlendirici bile bir değer üretemezse yazıcı sıfır yüklü bir payload yazar ve fAlwaysCalc bayrağını (§2.4.127'nin grbit bit 0'ı) ayarlar; böylece Excel açarken yer tutucuya güvenmek yerine hücreyi yeniden hesaplar

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1 tabanlı sayfa, satır ve sütun: ilk sayfada R2C4
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // önbelleğe alınmış hücreler için değerlendirici devrede değil
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // nested-subtotals.xls için Before.Value = After.Value = 37
    // Yeniden hesaplayan bir kayıt buraya 67 yazardı
  finally
    Book.Free;
  end;
end;

Bir BIFF shared formula kökü önbelleğe alınmış değerini nerede tutuyor?

Kendi Formula kaydında, her formül hücresi gibi; ve cache-first kaydetmenin hâlâ kaybettiği tek yer olarak shared grubun kök hücresini öne çıkaran da tam olarak bu. BIFF8'de bir shared formula, köşedeki hücrenin Formula kaydını izleyen bir ShrFmla kaydı ([MS-XLS] §2.4.260) olarak saklanır ve kök dahil her üye hücre, tek bir PtgExp token'ından (§2.5.198) oluşan bir rgce taşır: çözümlenmiş ifadenin ilk baytı $01, ardından kök hücrenin satırı ve sütunu gelir. Takipçi hücreler kendi kendine yeterlidir — HotXLS her birinin FormulaValue değerini okur ve ifadeyi kökün derlenmiş formülüne bakarak çözer. Kök hücre farklıdır, çünkü onun Formula kaydı ayrıştırıldığında ifade henüz yoktur; bir kayıt sonra gelir

Önbelleğin kaybolduğu yer o tek kayıtlık boşluk. TXLSReader.ParseFormula önbelleğe alınmış değeri çözer ve koordinatları hücrenin kendisiyle aynı olan bir PtgExp gördüğünde hücreyi FSharedFormulaRow ile FSharedFormulaCol içinde hatırlar ve önbelleği hücreye yayınlar. ShrFmla kaydı ($04BC) geldiğinde ParseSharedFormula ifadeyi derler ve _SetCompiledFormula ile kurar; _SetCompiledFormula ise her formül değişikliğinde yapması gerekeni yapar: FCachedFormulaValue değerini temizler ve durumu xlfcsMissing olarak sıfırlar. Böylece kökün yüklenen 37 değeri kimse okuyamadan atıldı, TryGetCachedFormulaValue kökü önbelleksiz bildirdi ve cache-first yazıcı, herkesin baktığı hücre için usulca değerlendiriciye düştü. Array kaydı (§2.4.4) aynı sıralamayı paylaşır ve aynı deliğe sahipti

v2.382.3'teki düzeltme, bekleyen kök koordinatlarının yanına üçüncü bir alan ekliyor: FSharedFormulaCachedValue. ParseFormula bir kök tanıdığında çözülen önbelleği oraya koyuyor ve hem ParseSharedFormula hem ParseArrayFormula, derlenmiş ifadeyi kurduktan hemen sonra onu _SetCellCachedFormulaValue üzerinden yeniden oynatıp bekletilen değeri Unassigned olarak sıfırlıyor. Önbelleğin String varyantı bütün bunlardan etkilenmiyor, çünkü yükü ayrı bir String kaydında gelir ve kayıt sırasına göre değil hücre koordinatlarına göre yönlendirilir. Aynı kavramın OOXML tarafıyla çalışıyorsanız, XLSX shared formula si genişletme makalesi paket biçiminde neden eşdeğer bir sıralama sorunu olmadığını ama kendi genişletme tuzakları bulunduğunu açıklıyor

Shared formula takipçileri neden göreli bir kaydırmaya ihtiyaç duyuyor?

Çünkü ShrFmla içinde saklanan ifade kök hücreye göre yazılmıştır ve onu olduğu gibi yeniden kullanan bir takipçi, kendi başvuruları yerine kökün başvurularını değerlendirir. Eski reader her takipçiye Value.GetCopy() kuruyordu; bu, yer değiştirmesi olmayan derin bir kopyadır, bu yüzden B1'de köklenen ve =A1*3 içeren bir grup her takipçiye de =A1*3 veriyordu. Cache-first kaydetme yüklenen dosyalarda bunu aslında maskeliyordu, çünkü takipçilerin kendi FormulaValue değerleri vardı ve doğru kaydetmek için ifadeye hiç ihtiyaç duymuyorlardı; sorun herhangi bir yeniden hesaplamada ortaya çıktı. Reader artık TXLSCompiledFormula.GetCopy(row - srow, col - scol) kuruyor; bu, sözdizimi ağacını dolaşıp her göreli başvuruyu takipçinin kökten uzaklığı kadar kaydırıyor, böylece B2'deki takipçi gerçek bir =A2*3 ifadesine sahip oluyor

Her iki davranışı sabitleyen regresyon testi okumaya değer, çünkü bir rastlantının geçmesine izin vermiyor. Test, 2 ve 4 girdileri üzerinde =A1*3 ve =A2*3 içeren bir workbook kurar, sonra _SetCellCachedFormulaValue üzerinden kasıtlı olarak yanlış olan 999 ve 888 önbelleklerini enjekte eder; bir kez UseSharedFormulas açık, bir kez kapalı. Kaydet ve yeniden yükle sonrasında iki hücre de hâlâ 999 ve 888 bildirmeli — bu, kaydın ne kök ne de takipçi önbelleğine dokunduğunun kanıtı. Ancak açık bir Recalculate sonrasında 6 ve 12 olmalılar — bu da takipçinin kaydırılmış ifadesinin doğru olduğunun kanıtı. Gerçek değerleri enjekte eden bir test eski yazıcıda da geçerdi; yanlış olanları enjekte etmenin bütün amacı da bu

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // bir girdiyi değiştir

    // Bağımlı formüllerin yüklenmiş önbellekleri bir literal düzenlemesiyle
    // GEÇERSİZ kılınmaz, bu yüzden düz bir SaveAs eski sayıları korur.
    // Gerçekten taze sonuç istiyorsanız yeniden hesaplama isteyin:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Cache-first sözleşmesinin sizin için yapmadıkları

Cache-first kaydetme yükleneni korur; yüklenenin hâlâ doğru olup olmadığını izlemez. Bir formülün bağlı olduğu bir literali değiştirmek, değerlendirici için bağımlılık grafiğini kirli işaretler ama bağımlı hücrenin xlfcsLoaded önbelleğini yerinde bırakır ve klasik yazıcı, siz Recalculate çağırmadıkça ya da hücrenin Value değerini önce okumadıkça bu bayat değeri memnuniyetle yazar; okumak değeri hesaplar ve durumu xlfcsCalculated konumuna taşır. Bu, Excel'in elle hesaplama modunda yaptığı aynı takastır ve üçüncü taraf dosyaları açan, birkaç etiketi düzenleyen ve kaydeden bir hat için doğru olandır — ama girdileri düzenleyen bir workbook'un yeniden hesaplama adımına açıkça sahip çıkması gerektiği anlamına da gelir. XLSX yazıcısının RecalcBeforeSave politikası bu çalışmadan etkilenmiyor ve aynı ruhla önbellekleri koruyan kendi elle moduna sahip. Bundan iki küçük sınır çıkıyor: cache-first yol yalnızca durumu xlfcsLoaded ya da xlfcsCalculated olan hücrelere yardım eder; formül yazıp onları hiç değerlendirmeyen bir üretici, kayıt sırasında hücre başına bir değerlendirme için yine ödeme yapar, tam da eskisi gibi. İç içe subtotal düzeltmesi ise değerlendiricinin hangi hücreleri atladığını düzeltir, değerlendiricinin uyguladığı her fonksiyonu değil — HotXLS'in Excel ile birebir hesaplayamadığı formüller içeren bir dosyayı artık dokunmadan round-trip etmek güvenli, ama o dosyada bilinçli bir Recalculate yine kütüphanenin yanıtını üretir, Excel'inkini değil; yeniden hesaplanmış bir kayda güvenmeden önce ikisini karşılaştırmalısınız

Cache-first klasik kayıtlar, geri getirilen shared ve array formula kök önbellekleri, shared takipçileri için göreli başvuru kaydırması ve düzeltilmiş SUBTOTAL ile AGGREGATE iç içe geçme kuralları Delphi ve C++Builder için standart HotXLS Delphi Spreadsheet Component içinde geliyor; Excel'e ya da herhangi bir OLE automation sunucusuna bağımlılığı yok. Ürün sayfası, burada kullanılan workbook, cache reader ve yeniden hesaplama giriş noktalarının tam API referansını taşıyor