Save XLS yang Diam-diam Menghitung Ulang Formula di Delphi

HotXLS, library Excel native untuk Delphi dan C++Builder, menyimpan workbook .xls BIFF8 klasik dengan cache lebih dulu: TXLSWorksheet.WriteFormula menanyakan TXLSWorkbook.TryGetCachedFormulaValue untuk nilai yang disimpan Excel di samping setiap formula dan baru memanggil evaluator ketika cache itu hilang atau terinvalidasi. Workbook yang Anda buka dan tidak pernah sentuh tersimpan kembali dengan angka yang sama, dan hasil yang segar menuntut satu panggilan Recalculate eksplisit alih-alih menjadi efek samping tersembunyi dari SaveAs

Bug yang memaksa kontrak ini naik ke permukaan sangatlah kecil. File korpus bernama nested-subtotals.xls memuat grand total di R2C4 dengan nilai cache 37. Buka dengan HotXLS, tanyakan TryGetCachedFormulaValue untuk sel itu, dapat 37. Simpan tanpa mengubah satu sel pun, buka salinannya, tanyakan hal yang sama, dapat 67. Tidak ada apa pun di API itu yang diminta menghitung sesuatu, tapi sebuah angka di file telah bergeser tepat 30 — dan 30 kebetulan jumlah dari dua subtotal grup, 10 dan 20, yang berada di dalam rentang yang dicakup grand total-nya

Kenapa menyimpan file XLS mengubah nilai formula?

Dua cacat independen harus bertemu agar 37 bisa menjadi 67, dan memperbaiki salah satunya saja akan menyembunyikan yang lain. Yang pertama struktural: writer klasik menghitung ulang setiap formula di setiap save. Yang kedua adalah pemeriksaan tipe yang mustahil bernilai benar untuk formula yang dimuat dari disk, yang membuat evaluator menghitung sel SUBTOTAL bersarang dua kali. File korpusnya sekadar input pertama di mana recalculasi saat save menghasilkan jawaban berbeda dari Excel dan ada orang yang membandingkan keduanya. Cacat strukturalnya mudah diutarakan: sebelum v2.382.3, TXLSWorksheet.WriteFormula dan kerabatnya untuk shared formula WriteFormulaWithTExp memperoleh field FormulaValue delapan byte di setiap record Formula dengan memanggil TXLSWorkbook.GetFormulaValue, yaitu evaluator-nya. Cache yang dengan cermat sudah didekode ParseFormula dari file sumber saat load tidak pernah dikonsultasikan di jalan keluar. Efeknya, setiap save adalah recalculasi penuh dengan API recalc level workbook dilewati, jadi tidak ada yang bisa Anda set di workbook yang akan menghentikannya. Di mana pun evaluator HotXLS berbeda pendapat dengan Excel, entah fungsi yang memang tidak didukung atau bug biasa, perubahan datanya menjadi senyap saat save

Cacat kedua bersarang di callback nested-subtotal yang dipakai evaluator. Excel mendefinisikan setiap bentuk SUBTOTAL sebagai mengabaikan sel yang formulanya sendiri SUBTOTAL lain, jadi kalkulator di lxCalc.pas mengaktifkan FIgnoreSubtotalCells selama agregasi dan menanyakan ke workbook, lewat TXLSWorkbook.GetClassicIsSubtotalCell, apakah setiap sel di rentang itu termasuk. Callback itu mengambil teks formulanya sebagai Variant lalu mengujinya dengan VarType(f) = varOleStr. Teksnya kembali dari GetUnCompiledFormula sebagai String Delphi, dan String yang diassign ke Variant adalah varUString, bukan pernah varOleStr. Predikatnya false untuk setiap sel di setiap file yang dimuat, subtotal grup ikut terhitung lagi ke grand total, dan pada save yang menghitung ulang semuanya, 10 + 20 + 7 menjadi 67

// HotXLS 2.381 dan sebelumnya: Variant formula yang dibangun dari String
// adalah varUString, jadi perbandingan ini tidak pernah berhasil
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr menerima varString, varOleStr dan varUString,
// dan AGGREGATE dikecualikan dari subtotal yang melingkupinya seperti Excel
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 merilis perbaikan VarIsStr-nya dan, sekalian di fungsi yang sama, mengajari callback itu bahwa sel AGGREGATE juga dikecualikan dari subtotal yang melingkupinya. Itu saja sudah membuat assertion korpusnya lolos, karena 37 hasil recalculasi kini cocok dengan 37 hasil load. Tapi itu tidak membuat library-nya jujur: save-nya masih menghitung ulang, dan ujinya hanya hijau karena evaluatornya kebetulan sepakat dengan Excel pada file tertentu itu. Aturan tentang sel mana yang dilewati SUBTOTAL dan AGGREGATE, termasuk baris tersembunyi, dibahas di artikel baris tersembunyi SUBTOTAL dan AGGREGATE; yang penting di sini adalah tidak ada evaluator yang boleh punya suara atas file yang tidak Anda minta hitung

Apa yang dijamin Excel soal nilai cache saat save?

Excel memperlakukan save sebagai snapshot, bukan peristiwa perhitungan. Nilai yang ditulis ke field FormulaValue sebuah record Formula ([MS-XLS] §2.4.127, tata letaknya di §2.5.133) adalah apa pun yang sedang ditampilkan sel itu, yang dalam mode perhitungan manual bisa berumur bertahun-tahun, dan Excel tetap menuliskannya dengan setia. Recalculasi adalah operasi terpisah dengan pemicunya sendiri. HotXLS kini mengikuti aturan yang sama untuk save klasik: WriteFormula dan WriteFormulaWithTExp memanggil TryGetCachedFormulaValue lebih dulu, mengambil CacheInfo.Value ketika state-nya xlfcsLoaded atau xlfcsCalculated, dan baru jatuh ke GetFormulaValue untuk xlfcsMissing dan xlfcsInvalidated. Separuh sisi baca dari kontrak ini, termasuk arti setiap state dan kenapa cache berisi blank atau False tetap dihitung sebagai nilai, dijelaskan di Membaca Nilai Cache Formula Excel di Delphi Tanpa Recalc

Jalur fallback-nya sengaja dipertahankan, bukan dihapus. Formula yang Anda assign dalam sesi ini lewat Cells[Row, Col].Formula datang tanpa cache, dan formula yang Anda ganti pada sel hasil load ditandai xlfcsInvalidated oleh _SetCompiledFormula; keduanya dievaluasi saat save persis seperti sebelumnya, sehingga workbook hasil generator tetap terbuka di Excel dengan angka di dalamnya. Ketika evaluatornya pun tidak bisa menghasilkan nilai, writer mengeluarkan payload nol dan menyetel fAlwaysCalc (bit 0 grbit di §2.4.127) agar Excel menghitung ulang selnya saat membuka alih-alih memercayai placeholder-nya

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // Sheet, baris dan kolom berbasis 1: R2C4 di sheet pertama
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // tidak ada evaluator yang terlibat untuk sel yang punya cache
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 untuk nested-subtotals.xls
    // Save yang menghitung ulang akan menulis 67 di sini
  finally
    Book.Free;
  end;
end;

Di mana sel root shared formula BIFF menyimpan nilai cache-nya?

Di record Formula-nya sendiri, seperti setiap sel formula lainnya, dan justru itulah yang menjadikan sel root sebuah grup shared sebagai satu-satunya tempat save cache-first masih kehilangan nilainya. Shared formula di BIFF8 disimpan sebagai record ShrFmla ([MS-XLS] §2.4.260) yang mengikuti record Formula sel kiri atasnya, dan setiap sel anggota, termasuk root-nya, membawa rgce yang terdiri dari satu token PtgExp (§2.5.198): byte pertama ekspresi yang di-parse adalah $01, diikuti baris dan kolom sel root-nya. Sel-sel pengikutnya mandiri — HotXLS membaca FormulaValue masing-masing lalu menyelesaikan ekspresinya dengan mencari formula terkompilasi milik root. Sel root-nya berbeda, karena saat record Formula-nya di-parse ekspresinya belum ada; ia datang satu record kemudian

Celah satu record itulah tempat cache-nya hilang. TXLSReader.ParseFormula mendekode nilai cache-nya dan, saat melihat PtgExp yang koordinatnya sama dengan sel itu sendiri, mengingat sel tersebut di FSharedFormulaRow dan FSharedFormulaCol lalu mempublikasikan cache-nya ke sel itu. Ketika record ShrFmla ($04BC) tiba, ParseSharedFormula mengompilasi ekspresinya dan memasangnya dengan _SetCompiledFormula, dan _SetCompiledFormula melakukan apa yang memang harus ia lakukan untuk setiap perubahan formula: ia mengosongkan FCachedFormulaValue dan mengembalikan state-nya ke xlfcsMissing. Nilai 37 hasil load di root itu karena itu dibuang sebelum ada yang sempat membacanya, TryGetCachedFormulaValue melaporkan root-nya sebagai tanpa cache, dan writer cache-first dengan patuh jatuh ke evaluator tepat untuk sel yang sedang diperhatikan semua orang. Record Array (§2.4.4) berbagi urutan yang sama dan punya lubang yang sama

Perbaikan di v2.382.3 menambahkan field ketiga, FSharedFormulaCachedValue, di samping koordinat root yang masih tertunda. ParseFormula menyimpankan cache yang sudah didekode ke situ ketika mengenali root, dan ParseSharedFormula maupun ParseArrayFormula me-replay-nya lewat _SetCellCachedFormulaValue tepat setelah memasang ekspresi terkompilasinya, lalu mengembalikan simpanannya ke Unassigned. Varian String dari cache-nya tidak terpengaruh semua ini karena payload-nya tiba di record String terpisah dan dirutekan berdasarkan koordinat sel, bukan berdasarkan urutan record. Kalau Anda bekerja dengan sisi OOXML dari konsep yang sama, artikel ekspansi si shared formula XLSX menjelaskan kenapa format package-nya tidak punya masalah urutan yang setara tapi punya jebakan ekspansinya sendiri

Kenapa pengikut shared formula butuh pergeseran relatif?

Karena ekspresi yang disimpan di ShrFmla ditulis relatif terhadap sel root-nya, dan pengikut yang memakainya apa adanya akan mengevaluasi referensi milik root, bukan miliknya sendiri. Reader lama memasang Value.GetCopy() pada setiap pengikutnya, salinan dalam tanpa pergeseran, sehingga grup yang berakar di B1 dengan =A1*3 memberi setiap pengikutnya =A1*3 juga. Save cache-first sebenarnya menutupi ini untuk file hasil load, karena pengikutnya punya FormulaValue sendiri dan tidak pernah butuh ekspresinya untuk tersimpan dengan benar; ia muncul begitu ada yang menghitung ulang. Reader-nya kini memasang TXLSCompiledFormula.GetCopy(row - srow, col - scol), yang menelusuri pohon sintaksnya dan menggeser setiap referensi relatif sebesar jarak pengikutnya dari root-nya, sehingga pengikut di B2 memiliki =A2*3 yang sungguhan

Uji regresi yang mengunci kedua perilaku itu layak dibaca karena ia menolak membiarkan kebetulan lolos. Ia membangun workbook dengan =A1*3 dan =A2*3 atas input 2 dan 4, lalu menyuntikkan cache 999 dan 888 yang sengaja salah lewat _SetCellCachedFormulaValue, sekali dengan UseSharedFormulas aktif dan sekali mati. Setelah save dan reload, kedua sel itu harus tetap melaporkan 999 dan 888 — bukti bahwa save-nya tidak menyentuh cache root maupun pengikutnya. Baru setelah Recalculate eksplisit keduanya harus menjadi 6 dan 12, bukti bahwa ekspresi pengikut yang sudah digeser itu benar. Uji yang menanam nilai yang benar akan lolos juga di bawah writer lama, dan itulah intinya menanam yang salah

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // ubah sebuah input

    // Cache hasil load milik formula dependen TIDAK terinvalidasi oleh
    // editan literal, jadi SaveAs biasa akan mempertahankan angka lamanya.
    // Mintalah recalculasi ketika Anda benar-benar ingin hasil segar:
    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;

Apa yang tidak dilakukan kontrak cache-first untuk Anda

Save cache-first mempertahankan apa yang sudah dimuat; ia tidak melacak apakah yang dimuat itu masih benar. Mengubah literal yang menjadi dependensi sebuah formula menandai graph dependency-nya kotor untuk evaluator, tapi ia meninggalkan cache xlfcsLoaded sel dependennya tetap di tempatnya, dan writer klasiknya akan dengan senang hati menulis nilai basi itu kecuali Anda memanggil Recalculate atau membaca Value sel itu lebih dulu, yang menghitungnya dan memindahkan state-nya ke xlfcsCalculated. Ini pertukaran yang sama yang dilakukan Excel dalam mode perhitungan manual, dan itu tepat untuk pipeline yang membuka file pihak ketiga, mengedit beberapa label, lalu menyimpannya — tapi artinya workbook yang mengedit input harus memiliki langkah recalculasi-nya sendiri secara eksplisit. Kebijakan RecalcBeforeSave milik writer XLSX tidak berubah oleh pekerjaan ini dan punya mode manualnya sendiri yang mempertahankan cache dalam semangat yang sama. Dua batas yang lebih kecil mengikutinya: jalur cache-first hanya menolong sel yang state-nya xlfcsLoaded atau xlfcsCalculated; generator yang menulis formula dan tidak pernah mengevaluasinya tetap membayar satu evaluasi per sel saat save, persis seperti sebelumnya. Dan perbaikan nested-subtotal-nya membetulkan sel mana yang dilewati evaluator, bukan setiap fungsi yang diimplementasikan evaluator — file yang formulanya tidak bisa dihitung HotXLS secara identik dengan Excel kini aman untuk round-trip tanpa disentuh, tapi Recalculate yang sengaja dijalankan atas file itu tetap akan menghasilkan jawaban library-nya alih-alih jawaban Excel, dan Anda sebaiknya membandingkan keduanya sebelum memercayai save hasil recalculasi

Save klasik cache-first, cache root formula shared dan array yang dipulihkan, pergeseran referensi relatif untuk pengikut shared, serta aturan penyarangan SUBTOTAL dan AGGREGATE yang dibetulkan semuanya sudah ada di HotXLS Delphi Spreadsheet Component standar untuk Delphi dan C++Builder, tanpa ketergantungan pada Excel atau server OLE automation mana pun; halaman produknya memuat referensi API lengkap untuk workbook, pembaca cache, dan titik masuk recalculasi yang dipakai di sini