HotXLS, komponen Excel Delphi dan C++Builder, secara otomatis memecah sebuah aturan conditional formatting atau data validation menjadi dua atau lebih objek aturan terpisah kapan pun penyisipan atau penghapusan baris maupun kolom memotong rentang cakupan aturan tersebut menjadi bagian-bagian yang membutuhkan anchor formula relatif berbeda, lalu menetapkan ulang setiap aturan conditional-format dengan nomor prioritas baru yang unik. Perilaku ini dirilis pada versi 2.196 mesin XLSX dan berjalan secara otomatis, tanpa pengaturan untuk menonaktifkannya. Pemicunya sempit tetapi umum: sebuah aturan cellIs atau expression yang formulanya membaca sebuah sel relatif terhadap rentangnya sendiri, berada pada sebuah worksheet yang kemudian mendapat baris disisipkan atau dihapus di suatu tempat di tengah rentang persis itu
Kebanyakan tulisan tentang otomasi Excel berhenti pada masalah teks-formula: geser nomor baris dan kolom di dalam setiap SUM() dan setiap VLOOKUP() agar referensinya tetap menunjuk ke sel yang benar. Separuh cerita itu memang nyata, dan dibahas di artikel pendamping tentang bagaimana HotXLS menulis ulang referensi formula ketika baris dan kolom berpindah, tetapi sebuah conditional format atau aturan data validation bukan sekadar formula yang duduk di sebuah sel. Ia memasangkan sebuah formula dengan sebuah rentang, sqref dalam istilah ECMA-376, dan keduanya harus berpindah bersama. Ketika sebuah edit struktural memotong rentang itu menjadi dua bagian yang membutuhkan dua offset relatif berbeda agar tetap benar, mempertahankan satu objek aturan dengan satu string formula berhenti menjadi pilihan, dan berpura-pura sebaliknya adalah cara sebuah aturan highlight diam-diam mulai membandingkan baris yang salah
Mengapa menyisipkan sebuah baris memecah aturan conditional formatting alih-alih sekadar memindahkannya?
Sebuah conditional format atau aturan data validation menyimpan tepat satu formula untuk seluruh rentangnya, dievaluasi relatif terhadap satu sel anchor, sehingga begitu sebuah edit memaksa dua bagian rentang itu membutuhkan dua offset relatif berbeda, satu formula tidak bisa lagi mendeskripsikan kedua bagian dengan benar. ECMA-376 mengekspresikan cakupan sebuah aturan sebagai atribut sqref pada elemen conditionalFormatting atau dataValidation, dan Excel mengevaluasi Formula1 dan Formula2 seolah teks itu diketikkan di sel kiri-atas sqref tersebut dan diisi ke seluruh sisanya, dengan cara yang sama seperti sebuah formula relatif biasa mengisi ke bawah sepanjang kolom. Bayangkan sebuah highlight variansi di atas B2:B50 yang menandai angka aktual mana pun yang melebihi anggarannya, dibangun sebagai aturan cellIs yang Formula1-nya adalah teks literal C2, artinya bandingkan sel B baris saat ini terhadap sel C baris yang sama itu
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Sisipkan satu baris pemisah itu pada baris lama 25 dan baris-baris di atas titik penyisipan tidak berpindah, sehingga bagian aturan mereka tetap membaca Formula1 sebagai C2 dengan benar. Baris-baris yang dulunya 25 hingga 50 bergeser turun menjadi 26 hingga 51, dan bagi mereka C2 sekarang sepenuhnya sel yang salah, karena baris 26 perlu membandingkan terhadap C26, bukan terhadap angka anggaran dua puluh empat baris di atasnya
Bagaimana HotXLS memutuskan apakah sebuah aturan perlu dipecah
HotXLS hanya membuat objek aturan tambahan ketika geometrinya benar-benar membutuhkannya: sebuah rutin internal, XlsxBuildShiftedRuleParts, menelusuri setiap area terpisah dalam sqref aturan tersebut, menghitung apa sel anchor area itu sebelum edit dan menjadi apa setelahnya, dan memeriksa apakah setiap bagian hasilnya akan membutuhkan koreksi offset relatif yang sama. Jika semua bagian sepakat, satu aturan bertahan, sqref-nya dibangun ulang sebagai gabungan bagian-bagian yang bergeser dan formulanya di-rebase sekali. Sebuah pemecahan sungguhan hanya terjadi ketika bagian-bagiannya tidak sepakat, persis kasus B2:B50 di atas, di mana blok atas mempertahankan anchor aslinya dan blok bawah membutuhkan anchor baru
Me-rebase formula sebuah bagian adalah gerakan dua-langkah yang menggunakan kembali mesin yang sudah dimiliki HotXLS untuk grup formula bersama OOXML: pertama formula diterjemahkan seolah aslinya berlabuh di sel kiri-atas bagian itu sendiri, menggunakan matematika offset-relatif yang sama yang memperluas sebuah formula bersama di seluruh rentangnya, lalu hasilnya melalui pemindai pergeseran baris-dan-kolom yang sama yang menulis ulang formula worksheet biasa. Begitulah cara Formula1 berubah dari C2 menjadi C26 dalam dua gerakan alih-alih satu kasus khusus yang ditulis tangan: terjemahkan C2 maju 23 baris untuk mendapatkan C25, seolah aturan itu selalu dimulai di sana, lalu biarkan pergeseran biasa pada baris 25 mendorongnya lebih jauh menjadi C26. Setiap properti lain, warna fill, stop-if-true, operatornya sendiri, ikut tanpa berubah ke objek aturan baru, sehingga kedua bagian tetap mengecat sel dengan warna yang selalu mereka gunakan
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Apakah data bar dan icon set terpecah dengan cara yang sama seperti aturan cellIs?
Tidak: HotXLS hanya mempartisi jenis aturan yang kebenarannya benar-benar bergantung pada formula relatif per-region, perbandingan cellIs dan aturan expression, dan membiarkan setiap jenis conditional-format lainnya sebagai satu objek aturan tunggal yang sqref-nya sekadar tumbuh untuk mencakup bagian-bagian yang bergeser sebagai gabungan multi-area. Secara internal cabangnya adalah sekadar pemeriksaan Kind biasa, cf.Kind in [cfkCellIs, cfkExpression], tidak ada yang lebih eksotis dari itu. Data bar, skala dua- dan tiga-warna, icon set, peringkat teratas dan terbawah, serta detektor duplikat, blank, dan error membawa sebuah payload, sebuah warna bar, sekumpulan stop skala, sebuah keluarga icon, yang mendeskripsikan seluruh rentang cakupan sekaligus alih-alih perbandingan relatif per-sel, sehingga memecahnya menjadi beberapa objek aturan berprioritas tidak akan membeli kebenaran apa pun dan hanya akan menambah aturan untuk dikelola. Ketika sebuah edit membelah rentang mereka, HotXLS menggabungkan kembali bagian-bagian tersebut menjadi satu aturan dengan sqref multi-area dan me-re-anchor payload-nya sebagai satu unit alih-alih mengkloning objek aturan baru per bagian. Perbedaan ini sejalan dengan taksonomi jenis-aturan di artikel dasar conditional formatting dan rich text: data bar, color scale, dan icon set sudah berbeda dari aturan cellIs dengan sepenuhnya mengabaikan properti Style, dan sekarang ternyata mereka juga berbeda dari re-anchoring per-region karena alasan mendasar yang sama
Mengapa prioritas aturan berubah setelah edit struktural?
Prioritas berubah karena setiap klon awalnya memegang persis nilai prioritas yang sama seperti aturan asal pemecahannya, dan HotXLS menjalankan langkah normalisasi setelahnya yang menyelesaikan duplikat yang dihasilkan menjadi urutan bersih tanpa celah alih-alih membiarkan dua aturan terikat pada peringkat yang sama. Sebuah rutin internal kedua, XlsxNormalizeConditionalFormatPriorities, mengambil prioritas saat ini dari setiap conditional format, jatuh kembali ke posisi aturan itu dalam koleksi untuk aturan mana pun yang tidak pernah diatur secara eksplisit, mengurutkan seluruh daftar secara stabil sehingga yang seri mempertahankan urutan relatif aslinya, dan menomori ulang hasil yang terurut itu menjadi urutan padat 1, 2, 3 tanpa celah dan tanpa pengulangan. HotXLS menjalankannya sekali sebelum sebuah pergeseran dimulai, sehingga cloning dimulai dari baseline yang bersih, dan sekali lagi setelah setiap pemecahan dan setiap aturan yang kosong dihapus, sehingga file yang disimpan tidak pernah memiliki dua entri aturan yang mengklaim prioritas yang sama. Ini penting jika Anda mengikuti saran di artikel dasar conditional formatting untuk meninggalkan celah di antara nilai prioritas sehingga sebuah aturan belakangan bisa disisipkan tanpa menomori ulang sisanya: celah-celah itu bertahan sampai edit baris atau kolom berikutnya menyentuh worksheet tersebut, lalu runtuh, karena normalisasi hanya menjamin keunikan dan urutan stabil, bukan bahwa skema penomoran asli Anda kembali tanpa berubah
Aturan data validation juga terpecah, tanpa prioritas untuk dinomori ulang
Aturan data validation melalui logika partisi-rentang yang sama seperti conditional format cellIs dan expression, dan tidak seperti conditional formatting, setiap jenis validation mengambil jalur itu secara seragam: HotXLS tidak memiliki keluarga non-formula terpisah untuk data validation seperti data bar dan icon set untuk conditional formatting, sehingga sebuah aturan list biasa atau whole-number dipartisi oleh rutin yang identik dengan yang menangani formula kustom relatif. Yang berbeda adalah prioritas: ECMA-376 tidak memberikan atribut priority sama sekali pada elemen dataValidation, sehingga tidak ada langkah penomoran ulang untuk validation seperti yang ada untuk conditional format. Bayangkan sebuah validation formula-kustom yang menjaga jumlah aktual setiap baris agar tidak melebihi anggarannya sendiri di kolom sebelahnya
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Ini penting karena alasan yang sama artikel dasar data validation memperingatkan agar tidak melampirkan sebuah aturan sebelum jumlah baris final: sebuah validation hanya mencakup sel-sel literal yang Anda berikan padanya, dan sebuah edit struktural belakangan bisa meninggalkan dua atau lebih aturan yang melakukan pekerjaan yang dulunya dilakukan satu. Tidak ada yang rusak secara fungsional: setiap sel dalam rentang asli tetap divalidasi oleh sesuatu, tetapi kode yang mengasumsikan satu entri DataValidations per kolom akan mulai salah mengindeks setelah edit pertama menyentuhnya. Ada batas atas keras seberapa jauh ini bisa berjalan: jika pemecahan akan mendorong sebuah worksheet melewati 65.534 aturan data-validation, HotXLS memunculkan sebuah exception alih-alih menulis sebuah file yang akan ditolak Excel secara diam-diam, yang merupakan library ini menolak untuk memproduksi sebuah workbook yang korup alih-alih sebuah batas yang kemungkinan besar akan dicapai penggunaan biasa
Apa yang perlu diperiksa setelah penyisipan atau penghapusan massal
Dua hal yang layak diverifikasi setelah sebuah script menjalankan sekumpulan edit baris atau kolom di atas sebuah sheet penuh conditional format dan validation adalah jumlah total aturan dan urutan prioritas, karena keduanya bisa melenceng dengan cara yang mudah terlewat dalam code review dan jelas terlihat begitu seseorang membuka Manage Rules di Excel. Satu edit jarang menyebabkan banyak kerusakan: satu penyisipan di tengah satu aturan cellIs menghasilkan paling banyak dua objek aturan dari yang tadinya satu. Risikonya menumpuk ketika sebuah rutin pembuatan laporan menyisipkan baris satu per satu dalam sebuah loop di atas sebuah sheet yang sudah membawa beberapa aturan berlabuh-formula: setiap langkah bisa memecah ulang aturan yang sudah dipecah langkah sebelumnya, dan lima aturan cellIs asli bisa berakhir menjadi beberapa kali lipat fragmen bernilai-rendah yang mencakup irisan tipis dari rentang aslinya. Melakukan batch pada edit struktural, menyisipkan seluruh blok baru dalam satu pemanggilan alih-alih satu baris pada satu waktu, menjaga jumlah aturan tetap terikat pada jumlah anchor yang benar-benar berbeda alih-alih jumlah edit yang dilakukan
Partisi aturan dan normalisasi prioritas disertakan sebagai perilaku standar mesin XLSX di dalam HotXLS Delphi Excel Component untuk Delphi dan C++Builder; halaman produk membawa referensi API pengeditan worksheet lengkap, termasuk metode conditional formatting dan data validation yang dijelaskan di sini