Mesin Rumus HotXLS dan Fungsi Kustom di Delphi

Sebuah library spreadsheet yang hanya menyimpan string formula dan sebuah library dengan formula engine yang benar-benar bekerja adalah dua produk berbeda yang terlihat identik sampai saat Anda meminta salah satunya menghasilkan sebuah angka. Sebagian besar kode spreadsheet Delphi tidak pernah menyadari celah ini, karena Excel menutupinya: tulis SUM(B2:B501) ke dalam sebuah cell, simpan, dan Excel menghitung ulang totalnya seketika seorang manusia membuka file itu. Keluarkan manusia dari alur tersebut, jalankan workbook yang sama lewat sebuah pipeline server yang mengekspor langsung ke CSV, dan perbedaannya berhenti menjadi sekadar teori. CSV itu membawa teks literal =SUM(B2:B501) di tempat yang seharusnya berisi sebuah angka, karena pada titik mana pun tidak ada yang benar-benar mengevaluasi formulanya

Itulah garis batas tempat HotXLS berdiri di sisi yang benar. Ia memperlakukan sebuah formula persis seperti cara format file itu sendiri memperlakukannya, sebagai teks tersimpan plus sebuah hasil cache opsional, sehingga sebuah ekspor CSV telanjang mereproduksi resepnya, bukan hidangannya. Tetapi ia juga membawa sebuah calculation engine yang bisa Anda panggil secara langsung, engine yang sama pada facade XLS maupun XLSX, ditambah sebuah hook untuk menyelesaikan nama fungsi yang belum pernah didengar engine itu. HotXLS adalah sebuah library Object Pascal native yang membaca dan menulis XLS serta XLSX dari Delphi dan C++Builder tanpa Excel automation, dan separuh kalkulasinya itulah yang mengubah formula tersimpan kembali menjadi nilai sesuai permintaan

Formula disimpan, bukan dievaluasi secara langsung

Menulis sebuah formula ke dalam sebuah cell tidak menghitung apa pun. Saat penyimpanan, workbook mencatat teks formulanya. Di sisi XLS, ia juga mencatat flag yang dikendalikan oleh RecalcOnSave, yang defaultnya True dan memberi tahu Excel untuk menghitung ulang saat dibuka. Model itu benar untuk file yang ditujukan ke Excel dan salah untuk pipeline yang mengonsumsi nilai cell secara langsung, entah itu ekspor CSV, ekspor HTML, atau kode Anda sendiri yang membaca kembali cell-nya. Untuk kasus-kasus itu, evaluasi secara eksplisit dengan Calculate. Ini tersedia pada empat titik masuk: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook, dan TXLSXWorksheet semuanya mengekspos function Calculate(const Formula: WideString): Variant

// evaluasi dalam proses, lalu kirim nilainya, bukan resepnya
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV sekarang membawa angkanya

Ekspresi yang diberikan ke Calculate adalah teks formula Excel biasa. Referensi lintas-sheet, defined name, dan fungsi bersarang semuanya diselesaikan terhadap workbook in-memory saat ini, yang membuat pemanggilan ini berguna jauh melampaui sekadar menambal ekspor CSV. Perlakukan ia sebagai sebuah mekanisme assertion. Sebuah generator yang baru saja menulis lima ratus baris detail bisa meminta workbook itu sendiri untuk grand total-nya dan membandingkannya dengan angka yang dihitungnya sendiri secara independen dalam Pascal, menangkap sebuah kesalahan range off-by-one sebelum auditor pelanggan yang menangkapnya

Ini juga membingkai strategi pengujian yang tepat untuk output yang sarat formula. Excel tetap menjadi implementasi acuan bahasa formula, sehingga untuk segelintir formula yang membawa konsekuensi bisnis, simpan sebuah file fixture yang disetujui yang nilai-nilai yang diharapkannya dihasilkan oleh Excel sendiri, dan buat build pipeline mengevaluasi formula workbook yang dihasilkan dengan Calculate terhadap fixture-fixture itu. Perbedaannya kemudian muncul sebagai test yang gagal dalam Delphi, bukan sebagai ketidaksesuaian yang ditemukan seorang pelanggan yang membandingkan dua laporan

Menambahkan fungsi bisnis dengan OnUserFunction

Ketika engine bertemu sebuah nama fungsi yang tidak dikenalinya, ia memicu sebuah event alih-alih langsung gagal. Tetapkan OnUserFunction pada class workbook mana pun dan Anda bisa menyelesaikan pemanggilan itu sendiri:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args datang sebagai sebuah array Variant
    Handled := True;
  end;
end;

// penyambungan dan penggunaan
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Tiga detail layak diperhatikan. Pertama, setel Handled := True hanya ketika Anda benar-benar mengenali namanya. Membiarkannya False memungkinkan engine melanjutkan penanganan fungsi-tak-dikenal normalnya, sehingga satu handler bisa melayani beberapa workbook tanpa mengklaim semua yang lewat. Kedua, bandingkan nama secara case-insensitive dengan SameText, karena penulis formula mengetik discount( dan DISCOUNT( secara bergantian. Ketiga, argumen datang dalam keadaan sudah dievaluasi: DISCOUNT(A1) menyerahkan kepada Anda nilai A1, bukan referensinya, sehingga sebuah fungsi tidak bisa mengetahui dari mana inputnya berasal. Poin terakhir itulah yang menyiapkan batasan yang menjadi topik bagian berikutnya

Perlakukan tubuh handler ini dengan kewaspadaan yang sama seperti titik masuk eksternal mana pun. Array Args mencerminkan apa pun yang diketik penulis formula, sehingga validasi jumlah dan tipe argumen sebelum mengindeksnya, dan tentukan lebih dulu apa yang dikembalikan sebuah pemanggilan yang tidak valid: sebuah nilai error Variant, atau sebuah exception yang dilempar. Pilihan ini penting karena sebuah exception yang dilempar di dalam handler merambat keluar lewat pemanggilan Calculate yang memicu evaluasi tersebut. Itu bisa diterima dalam sebuah generator yang terkendali ketat dan kasar dalam sebuah service yang mengevaluasi workbook buatan pengguna, tempat satu formula yang salah bisa menjatuhkan seluruh request. Dalam situasi itu, tangkap exception-nya di dalam handler dan kembalikan sebuah sentinel yang bisa dikenali dan dicatat oleh workflow di sekitarnya

Fungsi yang sadar-posisi membutuhkan varian Ex

Beberapa fungsi memang secara sah bergantung pada di mana mereka sedang dievaluasi. Sebuah tarif yang berbeda per sheet, sebuah lookup relatif-baris, sebuah pengali per-region yang hanya berlaku pada sheet regional: tidak satu pun dari ini bisa dijawab hanya dengan nilai argumen. Event biasa tidak bisa mengungkapkan hal itu, sehingga engine ini menawarkan OnUserFunctionEx, identik kecuali untuk satu parameter tambahan:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // formula yang sama menghasilkan tarif berbeda pada setiap sheet regional
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext membawa SheetIndex, Row, dan Col dari cell yang sedang dievaluasi. Jika hasil sebuah fungsi bergantung pada lokasinya, bahkan sedikit sekalipun, sambungkan event Ex sejak awal. Menyuntikkan context ke dalam sebuah handler yang sudah dipanggil tiga puluh formula jauh lebih berantakan dibanding memilih signature yang tepat sejak hari pertama, dan kedua event ini di sisi lain begitu mirip sehingga hampir tidak ada alasan untuk memulai dengan yang lebih sempit

Fungsi kustom tidak ikut terbawa ke Excel

Sebuah fungsi kustom hidup sepenuhnya di dalam proses Anda sendiri. Nama DISCOUNT berarti sesuatu hanya selama kode Delphi Anda dan event handler-nya sedang berjalan. Buka file yang tersimpan itu di Excel, dan DISCOUNT hanyalah sebuah nama yang tidak dikenali; cell-nya menampilkan #NAME? kecuali kebetulan ada sebuah fungsi VBA atau add-in yang cocok di mesin pengguna. Inilah fakta desain yang memisahkan sebuah demo dari sebuah produk yang siap dikirim, dan ia memaksa sebuah pilihan yang harus Anda buat secara sengaja, bukan ditemukan belakangan

Tentukan, per cell, kontrak mana dari kedua kontrak yang sedang Anda kirimkan. Cell yang dimaksudkan agar pengguna melihatnya dihitung ulang di dalam Excel harus dibangun dari kosakata fungsi milik Excel sendiri dan tidak lain. Cell yang logikanya bersifat hak milik sebaiknya dievaluasi dalam proses dengan Calculate dan disimpan sebagai nilai polos, sehingga fungsi kustom itu berperilaku sebagai sebuah aturan kalkulasi internal, bukan sebagai konten file. Mode kegagalan yang secara andal menghasilkan tiket dukungan adalah jalan tengahnya: menyimpan sebuah formula fungsi-kustom dan berharap Excel menghormatinya

Ada sebuah keuntungan tersembunyi dari kontrak hanya-nilai ini: ia melindungi kekayaan intelektual. Sebuah rule harga yang dievaluasi dalam proses Delphi Anda dan dikirim sebagai sebuah angka tidak bisa di-reverse-engineer dari workbook itu seperti halnya sebuah formula yang terlihat, dan seorang pengguna tidak bisa merusaknya dengan mengedit sebuah cell perantara. Generator invoice, laporan komisi, dan kartu tarif hampir selalu masuk dalam kubu ini. Kasus yang benar-benar membutuhkan formula hidup adalah model what-if interaktif, tempat pelanggan diharapkan mengubah input dan menyaksikan total-totalnya bergerak, dan itu harus dibangun dari kosakata Excel sendiri plus defined name

Mode kalkulasi, iterasi, dan R1C1: tombol-tombol pada facade XLS

Facade XLS mengekspos pengaturan kalkulasi level-BIFF yang dibaca Excel dari file. CalculationMode menerima xlCalcManual, xlCalcAutomatic (default) atau xlCalcAutomaticExceptTables, dan ia menentukan bagaimana Excel berperilaku begitu file dibuka. Sebuah workbook model dengan ribuan formula seringkali lebih ramah jika dikirimkan dalam mode manual, sehingga penerima yang menentukan kapan badai penghitungan ulang itu terjadi. EnableIteration (default False), bersama MaxIterations (default 100) dan MaxIterationChange (default 0.001), membuka referensi sirkular yang memang disengaja dari jenis konvergensi-iteratif yang muncul pada sebagian model finansial. ReferenceStyle beralih antara tampilan A1 dan R1C1, dan UseFullPrecision mencerminkan opsi precision-as-displayed milik Excel

Properti-properti ini berada pada facade XLS karena mereka memetakan ke rekaman BIFF; ketika menghasilkan .xlsx, rancang formula sehingga tidak bergantung pada pengaturan iteratif, atau hitung nilai yang sudah konvergen dalam Delphi dan tulis hasilnya

Formula array: titik masuk publiknya adalah XLSX

Formula array bergaya CSE lawas dibuat lewat TXLSXRange.SetArrayFormula:

// satu formula array yang membentang A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Method yang setara ada dalam hierarki class XLS tetapi berada di bagian private, sehingga tidak ada cara yang didukung untuk menulis formula array baru ke dalam file .xls. Formula array yang sudah ada dalam file yang dibuka tetap round-trip utuh; yang tidak bisa Anda lakukan adalah membuatnya. Aturan yang mengikutinya cukup sederhana: ketika semantik array menjadi bagian dari persyaratan, sasar .xlsx. Jika sebuah deliverable .xls lawas benar-benar membutuhkan perilaku array, jalur yang pragmatis adalah menghitung hasil array itu dalam Delphi dan menulis nilai-nilai individualnya ke dalam cell

Dua bacaan terkait di situs ini: defined names dan formula lintas-sheet membahas resolusi name yang dilakukan engine ini, dan artikel tentang ekspor CSV dan TSV merinci perilaku ekspor yang membuat kalkulasi eksplisit menjadi perlu. Referensi engine lengkap, termasuk kumpulan fungsi yang didukung, tersedia dalam paket HotXLS Delphi Component