Nama Terdefinisi dan Rumus Lintas Lembar di Delphi dengan HotXLS

Sebuah defined name adalah sebuah label yang mewakili sebuah konstanta, sebuah range cell, atau sebuah ekspresi formula, disimpan sekali dalam workbook dan dirujuk secara simbolik di mana pun ia dibutuhkan. Tulis TaxRate dalam sebuah formula, dan engine-nya akan menyelesaikannya menjadi apa pun yang disimpan definisi name tersebut, entah itu literal 0.08 atau range Data!$A$2:$D$100. Sebuah referensi lintas-sheet adalah gagasan yang ortogonal: Data!D2 menjangkau sebuah cell pada sheet lain dengan mengualifikasi alamatnya dengan sebuah nama sheet. Gabungkan keduanya, dan sebuah sheet ringkasan bisa menjumlahkan sebuah sheet detail lewat sebuah name yang tidak pernah menyebut alamat literal apa pun, yang justru menjadi hal yang Anda inginkan dalam sebuah workbook yang dirakit sebuah generator dan diaudit belakangan oleh seorang akuntan

HotXLS, library Delphi native milik losLab untuk file XLS dan XLSX, mengekspos name table di kedua format dengan akses create, find, dan delete, ditambah sebuah formula engine yang menyelesaikan name dan referensi lintas-sheet dalam proses. Kedua format ini mempertahankan hierarki class yang terpisah, dan perbedaan antara API name keduanya adalah bagian yang membuat kode yang dipindahkan dari satu ke yang lain tersandung

Dua penyimpan name yang tidak berbagi satu antarmuka

Pada sisi XLS, TXLSWorkbook.GetNames mengembalikan sebuah koleksi IXLSNames yang overload Add(Name, RefersTo, Visible)-nya menulis sebuah name ke dalam BIFF name table. Entri individual kembali sebagai objek IXLSName yang membawa Name, RefersTo, sebuah RefersToRange yang sudah diselesaikan, dan sebuah method Delete. Pada sisi XLSX, TXLSXWorkbook.DefinedNames adalah sebuah koleksi TXLSXDefinedNames dengan Add, FindByName, dan DeleteByName

Konvensi lookup-nya menyimpang dengan cara yang baru muncul saat pemindahan kode, bukan saat kompilasi. Properti default Item pada koleksi XLS menerima sebuah Variant, sehingga baik Names[0] maupun Names['TaxRate'] bisa diselesaikan terhadapnya. Koleksi XLSX tidak punya properti default semacam itu; Anda memanggil FindByName('TaxRate'), yang mengembalikan nil ketika name-nya tidak ada. Kode yang ditulis untuk satu facade hanya bisa dikompilasi terhadap facade lain secara kebetulan, dan kegagalannya cenderung muncul sebagai sebuah akses nil saat runtime, bukan sebagai garis merah bergelombang di IDE

Scope adalah keputusan pertama, bukan sebuah flag yang ditambahkan belakangan

Sebuah defined name bisa ber-scope workbook, terlihat oleh formula di setiap sheet, atau ber-scope sheet, terlihat hanya oleh formula pada sheet pemiliknya. Dalam API XLSX, perbedaan ini hanya berupa satu parameter opsional. DefinedNames.Add(AName, AFormula) membuat sebuah name level-workbook, sementara Add(AName, AFormula, ASheetIndex) mengikatnya ke satu sheet. Saat dibaca kembali, TXLSXDefinedName.SheetIndex mengembalikan -1 untuk scope workbook dan indeks sheet berbasis 0 untuk lainnya

Scope sekaligus berfungsi sebagai kebijakan tabrakan Anda, dan itulah alasan untuk menuntaskannya sebelum Anda menulis name pertama. Excel mengizinkan sebuah Total lokal-sheet pada setiap sheet ditambah sebuah Total level-workbook, dan sebuah formula pada sheet tertentu menyelesaikan yang lokal terlebih dahulu. Workbook yang dihasilkan sebaiknya memanfaatkan hal itu secara sengaja. Asumsi bisnis yang dikonsumsi beberapa sheet, seperti tarif pajak, kurs FX, dan periode pelaporan, sebaiknya berada pada scope workbook. Range bantu yang hanya dirujuk formula satu sheet lebih aman ber-scope sheet, di tempat tidak ada apa pun yang bisa membayanginya dan ia tidak bisa membayangi apa pun

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... isi Data!A2:D100 dengan baris detail ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // scope workbook, sebuah konstanta
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // scope workbook, sebuah range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // ber-scope hanya untuk indeks sheet 1

    // formula XLSX tidak memakai '=' di depan
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Sebuah defined name tidak harus menunjuk ke sebuah range. TaxRate di atas merujuk ke konstanta telanjang 0.08, dan itulah cara paling bersih untuk mempublikasikan sebuah asumsi bisnis. Ia muncul satu kali di Name Manager milik Excel, setiap formula merujuknya secara simbolik, dan perubahan tarif kuartal depan hanya sebuah edit satu baris pada generator, bukan sebuah pencarian di antara empat belas string formula yang sudah dirakit

Tanda sama-dengan yang hanya milik satu sisi

Jalur masuk formula adalah tempat kode yang dipindahkan paling sering pecah, karena kedua facade tidak sepakat soal tanda sama-dengan. Cell XLS menerima formula lewat Value dengan sebuah = di depan. Cell XLSX punya sebuah properti Formula khusus yang menerima ekspresi tanpa prefix tersebut. Tulis '=SUM(A1:A10)' ke dalam TXLSXCell.Formula, dan tanda sama-dengan itu menjadi bagian dari teks ekspresi yang tersimpan alih-alih sebuah penanda, dan file itu tidak akan berperilaku sama seperti string yang sama di sisi XLS

var
  Book: IXLSWorkbook;   // dihitung lewat interface: jangan panggil Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // asumsikan sebuah sheet bernama 'Data' sudah menyimpan baris detail
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = disembunyikan dari Name Manager

  // formula XLS melewati Value, dengan prefix '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Cuplikan itu menunjukkan dua keanehan lagi di sisi XLS. Koleksi sheet-nya berbasis 1, sehingga Sheets[1] adalah sheet pertama, berbeda dari Sheets[0] XLSX yang berbasis 0. Dan parameter Add ketiga membuat sebuah name tersembunyi: hadir dalam file dan bisa digunakan formula, namun tidak terlihat di Name Manager milik Excel. Name tersembunyi adalah kendaraan yang tepat untuk mekanisme internal generator yang tidak boleh diedit atau dihapus secara tidak sengaja oleh pengguna akhir

Referensi lintas-sheet, dan apa yang terjadi ketika baris berpindah

Kedua formula engine menerima sintaks lintas-sheet standar. Nama sheet biasa mengualifikasi langsung sebagai Data!A1; sebuah nama yang mengandung spasi atau tanda baca membutuhkan tanda kutip tunggal, seperti pada 'Sheet With Space'!A1. Di dalam teks RefersTo milik sebuah name, gunakan referensi absolut seperti Data!$A$2:$D$100 hampir setiap saat. Sebuah referensi relatif di dalam sebuah defined name diselesaikan relatif terhadap cell yang menggunakannya, yang merupakan sebuah fitur Excel yang memang disengaja dan sumber kebingungan yang bisa diandalkan ketika terpicu secara tidak sengaja

Edit struktural adalah tempat pembukuan lintas-sheet ini terbukti berguna, dan sisi XLSX menjaga name tetap konsisten melewatinya. InsertRows dan DeleteRows menggeser range defined-name bersama cell, gabungan, hyperlink, dan anchor chart, sehingga sebuah name yang menunjuk ke Data!$A$2:$D$100 tetap mencakup blok data itu setelah generator membuka sebuah celah di atasnya. Formula datang dengan satu catatan penting yang terdokumentasi: penyisipan baris hanya menyesuaikan referensi yang menyasar sheet yang sedang diedit. Sebuah formula Summary yang merujuk Data!D2:D100 ditulis ulang ketika baris masuk ke dalam Data, yang biasanya memang menjadi kasus yang Anda inginkan. Verifikasi hal ini, jangan hanya mengasumsikannya, karena engine-nya akan memberi tahu Anda dengan murah:

// calculation engine menyelesaikan name dan referensi lintas-sheet dalam proses
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate mengevaluasi sebuah ekspresi apa pun terhadap keadaan workbook saat ini tanpa menyimpan apa pun, yang menjadikannya primitif assertion yang alami untuk pengujian generator. Hitung agregat yang diharapkan dari data sumber dalam Pascal, evaluasi formula milik workbook itu sendiri, lalu bandingkan keduanya. Artikel tentang formula engine membahas apa yang dievaluasi engine tersebut, kapan, dan cara memperluasnya dengan fungsi kustom

Name _xlnm yang menjadi milik property layer

Buka name table sebuah file yang dihasilkan dalam sebuah inspector level-rendah, dan Anda akan menemukan entri yang tidak pernah Anda tulis: _xlnm.Print_Area, _xlnm.Print_Titles, dan kerabatnya. Begitulah cara OOXML (ECMA-376 / ISO 29500) menyimpan area cetak dan baris judul berulang, sebagai defined name dengan identifier yang dicadangkan. HotXLS mengelolanya lewat properti worksheet khusus, sehingga menyetel PrintArea atau PrintTitleRows menulis entri _xlnm.* yang sesuai untuk Anda

Jebakannya adalah menjangkau langsung ke dalam namespace yang dicadangkan itu secara manual. Tambahkan sebuah entri _xlnm.Print_Area lewat DefinedNames.Add sambil juga menyetel properti PrintArea, dan workbook itu membawa dua definisi yang saling bertentangan untuk satu name yang dicadangkan, sebuah keadaan yang diselesaikan Excel dengan cara yang tidak seharusnya diandalkan produk mana pun. Perlakukan setiap identifier yang dimulai dengan _xlnm. sebagai milik property layer. Untuk memeriksa pengaturan cetak, baca propertinya, bukan name table-nya. Artikel tentang proteksi dan page setup membahas properti area cetak ini dalam konteksnya

Dua batasan yang layak diketahui sebelum Anda menetapkan sebuah desain

Defined name tidak ikut terbawa lewat jembatan kemudahan XLS-ke-XLSX. SaveXLSWorkbookAsXLSX menyalin konten cell dan formatting dasar, dan name table tidak masuk dalam daftar salinannya yang terdokumentasi, sehingga sebuah workbook yang bergantung pada name-nya kehilangan semuanya saat penyeberangan itu. Buat ulang name-nya lewat DefinedNames.Add setelah konversi. Langkah itu tidak seberat kedengarannya, karena ia memberi Anda kesempatan untuk menormalkan scope-nya alih-alih sekadar membawa serta apa pun yang kebetulan dimiliki file XLS tersebut

Batasan lainnya adalah pergeseran antara string formula dan nama sheet. Excel menulis ulang referensi sheet di dalam formula dan name selama sebuah rename interaktif, sehingga file yang diedit pengguna di Excel tetap konsisten dengan sendirinya. Titik rentannya ada di sisi generator: ketika kode Pascal merakit string formula dari sebuah literal nama-sheet, mengganti nama sheet di satu tempat dan lupa di tempat lain menghasilkan sebuah referensi ke sheet yang sudah tidak ada lagi. Simpan nama sheet dalam satu konstanta Delphi tunggal dan berikan konstanta itu baik ke Sheets.Add maupun ke perakitan formula Anda, dan keduanya tidak akan pernah berselisih. Ini adalah insting yang sama yang mendukung pemberian nama pada cell output sebuah laporan alih-alih hard-coding alamatnya: sebuah template yang cell total-nya diberi nama tetap berfungsi setelah seorang desainer menyisipkan tiga baris di atasnya, sementara sebuah generator yang menulis ke sebuah literal B17 diam-diam mendaratkan angkanya di tempat yang salah. Artikel tentang pembuatan laporan berbasis template dibangun persis di atas pola tersebut

API defined-names lengkap untuk kedua format, bersama referensi formula engine-nya, tersedia dalam paket HotXLS Delphi Component