XLS kaydında sessiz formül yeniden hesaplamasını durdurmak

Excel’in 37 gösterdiği bir genel toplam, bir program .xls dosyasını açıp tek bir hücreyi bile düzenlemeden kaydettikten sonra 67 olarak geri gelebilir. Fazladan gelen 30 rastgele değildir; genel toplamın kapsadığı aralıkta duran, 10 ve 20 değerindeki iki grup ara toplamının tam olarak toplamıdır. O sayının yer değiştirmesi için iki şeyin aynı anda ters gitmesi gerekir: kaydetmenin çıkış yolunda her formülü yeniden hesaplaması ve yeniden hesaplamayı yapanın, bir SUBTOTALun diğer SUBTOTAL hücrelerini atladiğini unutması. Hangisini kaldırırsanız kaldırın dosya sağ çıkar; ilk sorunun ikincinin arkasında fark edilmeden durabilmesinin nedeni de budur

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

Bir kaydetme, yazıcı her formülün yanına saklayacağı sayıyı yüklediğini kopyalamak yerine kendisi hesapladığında formül değerini değiştirir. Her BIFF8 Formula kaydı, o hücre için Excel’in en son gösterdiği sonucu tutan sekiz baytlık bir FormulaValue alanı taşır ([MS-XLS] §2.4.127, değer düzeni §2.5.133). Yazıcının o sekiz bayta bir şey koyması gerekir ve en kısa kod yolu, her formülü hesaplama motoruna teslim etmektir. Bu bir kez gerçekleştiğinde her kaydetme, kimsenin istemediği tam bir yeniden hesaplamaya dönüşür ve o motorun Excel ile ayrıştığı her nokta, kullanıcının dokunulmadığına inandığı dosyada değişmiş bir sayı olur

SUBTOTAL uyuşmazlığı iyi bir örnektir, çünkü kural basittir ve tuzak Delphi’nin Variant tiplemesindedir. Excel, her SUBTOTAL fonksiyon numarasını, kendi formülü başka bir SUBTOTAL olan hücreleri yok sayacak biçimde tanımlar. Ayrıntı satırları 37’e ulaşan bir sayfa için (10 ve 20’ye ara toplam yapan iki grubun altındaki satırlar artı hiçbir gruba dahil olmayan 7 birim daha) genel toplam 37’dir. İç içe ara toplamları göremeyen bir motor, 10 ile 20’yi bir kez daha sayar ve 67’ye ulaşır. Onları fark etmek çoğunlukla formül metni üzerinde kısa bir kontrol demektir; akla gelen Delphi sürümü ise derlenir, çalışır ve hiçbir şeyle eşleşmez:

// Kendiniz yazdığınız bir yardımcı; örneğin bir sayfayı tararken
// kapsayan bir SUBTOTALun atlaması gereken formüller için
function IsNestedSubtotal(const F: Variant): Boolean;
begin
  // Çekici görünen test Unicode Delphi’de asla başarılı olmaz: Variant’a
  // atanan bir String varUString olur, varOleStr değil
  //   Result := (VarType(F) = varOleStr) and ...

  Result := False;
  if VarIsStr(F) then  // varString, varOleStr ve varUString kabul eder
    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(');
end;

// HotXLS ile kullanımı (sayfalar, satırlar ve sütunlar 1 tabanlıdır):
//   if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...

VarIsStr, HotXLS değerlendiricisinin aynı soruya uyguladığı testtir ve AGGREGATE hücrelerini de Excel’in yaptığı gibi kapsayan bir ara toplamın dışında tutar. Ne var ki yüklemi düzeltmek işin tamamı değildir. Doğru bir kontrolle yeniden hesaplayan kaydetme, 37’yi yalnızca motorun bu özel dosyada tesadüfen Excel ile aynı fikirde olması yüzünden yazar. Motoru biraz daha farklı ele alacağı bir sonraki formül yine kayar. SUBTOTAL ile AGGREGATEin, gizli satırlar dahil, hangi hücreleri atladiğinin kuralları SUBTOTAL ve AGGREGATE gizli satır yazısında ele alınır. Cache-first kaydetmeyle hesaplamasını istemediğiniz bir dosya, motorun ne yapacağına bakılmaksızın hücresinde 37 ile diske geri döner

Klasik bir XLS kaydetmesi her Formula kaydına ne yazar?

HotXLS bir BIFF8 çalışma kitabını önce önbellekle kaydeder: her formül hücresi için TXLSWorkbook.SaveAs, TryGetCachedFormulaValue bir değer bildirecekse önbellekteki değeri yazar ve değerlendiriciyi yalnızca bildirmeyecekse çalıştırır. Kararı önbellek durumu verir. xlfcsLoaded (değer kaynak dosyadan geldi) ya da xlfcsCalculated (HotXLS bu oturumda hesapladı) durumundaki bir hücrenin CacheInfo.Value değeri bayt bayt yazılır. xlfcsMissing ya da xlfcsInvalidated durumundaki hücre ise kaydetme zamanında değerlendirilir. Bu, Excel’in bir kaydetmeye yaklaşımıyla aynıdır: her hücrenin gösterdiğinin anlık görüntüsü, elle hesaplama modunda çok bayat olabilen bir görüntü, yine de sadakatle yazılır

Değerlendirme yolu iyi nedenlerle yerinde kalır. Kod içinde Cells[Row, Col].Formula üzerinden atadığınız bir formülün henüz önbelleği yoktur ve yüklenmiş bir hücreye yeni formül atamak onu xlfcsInvalidated’a taşır; çünkü eski sonuç eski ifadeye aittir. İkisi de kaydetme sırasında hesaplanır, böylece sıfırdan üretilen bir çalışma kitabı Excel’de sıfırlar yerine hesaplanmış sayılarla açılır. Değerlendiricinin bile bir değer üretemediği durumda (desteklenmeyen bir fonksiyon, kopmuş bir dış bağlantı) yazıcı sıfır saklar ve §2.4.127’deki kayıt bayraklarının 0. biti olan fAlwaysCalc’ı ayarlar; böylece Excel, yer tutucuya güvenmek yerine açılışta hücreyi yeniden hesaplar

Tek bir özellik ilgili görünür ve değildir. TXLSWorkbook.RecalcOnSave, CalcSaveRecalc kaydındaki Excel’in "kaydetmeden önce çalışma kitabını yeniden hesapla" seçeneğini yansıtır ([MS-XLS] §2.4.37). Yalnızca Excel için, yalnızca elle hesaplama modunda önemlidir ve HotXLS onu olduğu gibi dosyaya yazar. SaveAs, FormulaValue’a ne gireceğine karar verirken ona bakmaz. Aşağıdaki round-trip, sözleşmeyi kendi dosyalarınızdan birinde görmenin en hızlı yoludur:

procedure CheckRoundTrip(const Source, Target: string;
  Row, Col: Integer);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Cannot open ' + Source);
    // Sayfa, satır ve sütun 1 tabanlıdır
    if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
      raise Exception.Create('Cell carries no usable cached value');
    if Book.SaveAs(Target) <> 1 then   // önbellekli hücreler saklandığı gibi yazılır
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, Row, Col, After);
    // Beklenen: After.Value, Before.Value’a eşittir ve After.State
    // kaydedilen dosyadan geldiği için yeniden xlfcsLoaded olur
    Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
  finally
    Book.Free;
  end;
end;

Bu sözleşmenin okuma tarafı, her durumun ne anlama geldiği ve önbellekteki bir boş değerin ya da False’un neden yine de bir değer sayıldığı dahil, Delphi’de yeniden hesaplama yapmadan Excel’in önbellekteki formül değerlerini okuma yazısında anlatılır. Yukarıdaki kontrolü, iç içe ara toplamlı bir çalışma kitabının genel toplam hücresinde çalıştırın; iki satır da aynı sayıyı yazdırır

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

Kendi Formula kaydında, diğer her formül hücresi gibi; ama ifade bir kayıt sonra gelir ve dikkatsiz bir okuyucunun önbelleği kaybettiği yer tam olarak bu boşluktur. BIFF8, bir shared formula’yı grubun sol üst hücresinin Formula kaydının hemen ardından gelen bir ShrFmla kaydı olarak saklar ([MS-XLS] §2.4.260). Kök dahil her üye hücre, tek bir PtgExp token’ından (§2.5.198) oluşan bir rgce taşır: $01 baytı ve ardından kökün satırı ile sütunu. Takipçi hücreler kolaydır; onlar görünene dek kökün ifadesi çoktan vardır ve kendi FormulaValue değerleri kendi kayıtlarında durur. Kök kolay değildir. Formula kaydı okunduğunda elinde 37 değerinde bir önbellek ve henüz hiç ifade yoktur

Bir hücreye ifade kurmak onun önbelleğini temizler ve bu böyle kalmalıdır; değiştirilmiş bir formül eski sonucu gerçekten anlamsız kılar. Ne var ki aynı kural ShrFmla kaydına körlemesine uygulandığında, ifade gelir gelmez kökün 37’sini çöpe atar, TryGetCachedFormulaValue kökü xlfcsMissing olarak bildirir ve kaydetme, tam olarak kullanıcının en çok bakacağı hücre için değerlendiriciye geri düşer. HotXLS okuyucusu ShrFmla kaydını beklerken kökün çözülmüş değerini elinde tutar, ifadeyi kurar ve ardından yüklenen değeri xlfcsLoaded durumuyla geri koyar. Dizi formülleri (Array kaydı, §2.4.4) aynı kayıt sırasını izler ve aynı muameleyi görür. Önbelleğin String biçimi bu sorunu hiç yaşamadı; çünkü metni ayrı bir String kaydında gelir ve satır ile sütun üzerinden hücreyle eşleşir, akıştaki konumu asla önemli olmaz

OOXML tarafını da işliyorsanız XLSX shared formula si genişletme yazısı, paket biçiminde kayıt sırası boşluğu bulunmadığını ama kendi genişletme tuzaklarını getirdiğini açıklar

Shared formula takipçileri ifadenin kaydırılmış bir kopyasına sahiptir

ShrFmla kaydındaki ifade kök hücreye göre yazılır; bu yüzden her takipçinin, kökten uzaklığı kadar kaydırılmış kendi kopyasına ihtiyacı vardır. Olduğu gibi kopyalayın, B1’de köklenen ve =A1*3 içeren bir grup aynı =A1*3’ü B2’ye ve B3’e verir. HotXLS her takipçiye, göreli başvuruları takipçinin satır ve sütun uzaklığı kadar kaydırılmış bir kopya verir; böylece B2 gerçek bir =A2*3’e, B3 ise =A3*3’e sahip olur

Bu yüzden sıradan bir round-trip, takipçi ifadelerinin doğru olup olmadığını söyleyemez. Takipçiler yüklenen değerlerini kaydetme boyunca korur; birebir kopya geçerdi. Bir şey kanıtlayan kontrolün bir girdiyi değiştirip yeniden hesaplaması gerekir. Aşağıdaki yordam, UseSharedFormulas açıkken (varsayılanı False’tur; bu durumda hücre başına bir Formula kaydı yazılır) bir shared grup yazar, dosyayı yeniden açar, A2’yi düzenler ve B2’yi izler:

procedure CheckSharedFollower(const FileName: string);
var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Info: TXLSFormulaCacheInfo;
  Row: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    for Row := 1 to 3 do
    begin
      Sheet.Cells[Row, 1].Value := Row * 2;                 // 2, 4, 6
      Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
    end;
    Book.UseSharedFormulas := True;   // B1:B3 tek bir ShrFmla grubu olur
    Book.SaveAs(FileName);            // henüz önbellek yok: 6, 12, 18 olarak hesaplanır
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(FileName);
    Book.Sheets[1].Cells[2, 1].Value := 10;   // takipçinin girdisini düzenle

    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 before: ', VarToStr(Info.Value));   // Beklenen: 12, hâlâ yüklenmiş

    Book.Recalculate;
    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 after: ', VarToStr(Info.Value));    // Beklenen: 30, =A2*3’ten
  finally
    Book.Free;
  end;
end;

=A1*3’ü yeniden kullanmış bir takipçi ikinci satırda 6 yazdırırdı. İlk satır da en az o kadar önemlidir: bir literal düzenlemenin, bir şey yeniden hesaplayana dek bağımlı formülün yüklenen değerini yerinde bıraktığını gösterir

Cache-first kaydetmenin size bıraktıkları

Cache-first kaydetme yükleneni korur; yüklenenin hâlâ doğru olup olmadığını izlemez. Bir formülün bağımlı olduğu bir hücreyi değiştirmek, bağımlılık grafiğini bir sonraki yeniden hesaplama için işaretler ama bağımlı hücre xlfcsLoaded değerini tutar ve önce Recalculate çağırmadığınız ya da hücrenin Value değerini okumadığınız sürece SaveAs o bayat sayıyı yazar; okumak formülü hesaplar ve hücreyi xlfcsCalculated’a taşır. Recalculate, değerlendiremediği formül hücrelerinin sayısını döndürür; sıfırdan farklı bir sonuç kaydetmeden önce günlüğe yazılmaya değer

Yaygın işlem hattında bir program, başkasının kurduğu bir çalışma kitabını açar, birkaç etiket ya da başlık doldurur ve kaydeder. O dosyadaki hiçbir şeyin değeri değişmemeli ve cache-first kaydetmeyle değişmez. Girdileri düzenleyen bir program ise yeniden hesaplama adımının sahipliğini üstlenir ve bu adım, kodu okuyan herkesin görebileceği şekilde düzenlemelerin yanında tek bir görünür Recalculate çağrısında durmalıdır. Excel, elle hesaplama modunda aynı tercihi yapar. XLSX yazıcısının kendi politikası vardır: xrmManual, xrmIfDirty ve xrmAlways modlu RecalcBeforeSave; XLSX çalışma kitabı sınıfında ayarlanır ve yukarıdakilerin hepsinden bağımsızdır

Tasarımdan iki sınır çıkar. Önbellek yolu yalnızca xlfcsLoaded ya da xlfcsCalculated durumundaki hücrelere yarar; binlerce yeni formül yazıp hiçbirini değerlendirmeyen bir üretici, kaydetme sırasında yine hücre başına bir değerlendirme öder. Ayrıca sözleşme bir kaydetmenin ne yazdığını yönetir, açık bir yeniden hesaplamanın ne hesaplayacağını değil: dokunulmamış bir dosya Excel’in sakladığı sayıları korurken bir Recalculate, HotXLS değerlendiricisinin sonuçlarını yazar. İş hattını yeniden hesaplayan kaydetmelere çevirmeden önce ikisini birkaç temsili çalışma kitabı üzerinde karşılaştırın

HotXLS Delphi Spreadsheet Component, klasik BIFF8 ve XLSX çalışma kitaplarını Excel ya da OLE automation kurulu olmaksızın Delphi ve C++Builder’dan okur ve yazar. Ürün sayfası, bu örneklerde kullanılan çalışma kitabı, önbellek değeri ve yeniden hesaplama çağrılarını belgeler