Pemformatan Bersyarat, Rich Text, dan Gaya Sel di Delphi dengan HotXLS

Sebuah rule conditional formatting dalam OOXML sebenarnya adalah dua hal terpisah yang mengenakan satu nama. Kondisinya (sebuah perbandingan, sebuah formula, sebuah pencocokan teks) menentukan cell mana yang memenuhi syarat. Tampilannya (sebuah rekaman differential format, dxf dalam istilah ECMA-376) menentukan seperti apa rupa cell-cell tersebut. Dialog Excel menyembunyikan sambungan ini dengan membuat Anda mengisi keduanya sekaligus. HotXLS tidak begitu. Buat sebuah rule cellIs dari Delphi dan lewati style-nya, maka rule itu tetap valid, range-nya benar, formulanya bernilai true persis pada cell yang tepat, dan tidak ada warna yang berubah, karena instruksi rule tersebut adalah "true, jangan mewarnai apa pun." Celah antara kondisi dan konsekuensi inilah hal pertama yang harus dibenahi, dan celah itulah penyebab sebagian besar rule yang tampak benar di Manage Rules namun tidak menyorot apa pun

HotXLS menulis conditional formatting secara native ke dalam file BIFF8 .xls maupun OOXML .xlsx, dan melakukan hal yang sama untuk rich text run serta model cell-style yang dikumpulkan dalam pool. Ketiga fitur ini berbagi lebih banyak keterkaitan internal daripada yang tersirat dari permukaan API-nya yang tampak datar, dan titik-titik di mana output menyimpang dari maksud biasanya berada tepat di sambungan antar fitur tersebut

Sebuah kondisi butuh sebuah konsekuensi: style dxf

Pada worksheet XLSX, rule perbandingan berasal dari AddConditionalFormat, yang menerima sebuah range, sebuah operator dari TXLSXCfOperator, dan sebuah formula atau literal, lalu mengembalikan indeks rule baru tersebut di dalam koleksi ConditionalFormats milik sheet. Objek rule pada indeks itu mengekspos sebuah properti Style, dan di situlah highlight-nya berada. Setel sebuah fill pada properti itu dan cell yang memenuhi syarat akan mengambil fill tersebut. Biarkan tidak tersentuh, dan Anda telah membangun rule tak kasatmata yang dijelaskan di atas

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Varians negatif: fill merah muda
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Order ID duplikat ditandai dengan cara yang sama
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Rule formula kustom: soroti baris di mana nilai aktual tidak mencapai 90% dari target
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Warna di sini adalah nilai ARGB 32-bit, sehingga $FFFFC7CE adalah "merah muda" Excel yang Anda kenal dari dialog, dengan byte alpha yang sepenuhnya opak berada di depan RGB-nya. Setiap jenis rule yang bekerja pada kondisi per-cell mengikuti bentuk yang sama: buat lalu beri style. Pencocok teks (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) mengembalikan sebuah indeks yang Anda beri style kemudian, begitu pula AddCondFormatTop10, AddCondFormatAboveAverage, dan detektor blank serta error. Pelajari pola ini sekali saja, dan seluruh keluarga teks-dan-perbandingan berperilaku sama

Data bar, color scale, dan icon set mewarnai dirinya sendiri

Jenis rule visual bekerja dengan cara sebaliknya. Mereka membawa tampilannya sendiri di dalam definisi rule dan sepenuhnya mengabaikan properti Style. Berikan sebuah fill pada rule data bar dan tidak terjadi apa-apa, yang terasa seperti bug sampai taksonominya jadi jelas: AddCondFormatDataBar menerima warna bar sebagai argumen langsung, color scale dua- dan tiga-titik menerima warna titik akhirnya dengan cara yang sama, dan AddCondFormatIconSet memilih salah satu dari 26 jenis icon set seperti icsTrafficLights3. Tidak ada rekaman style terpisah yang bisa terlupa di sini, karena memang tidak ada rekaman style terpisah sama sekali

Parameter yang layak dipikirkan pada pemanggilan ini adalah anchor nilai, bertipe TXLSCfValueKind. Titik akhir sebuah bar atau scale bisa berada pada minimum atau maksimum range, pada sebuah angka literal, pada sebuah persen atau persentil, atau pada hasil sebuah formula. Nilai default-nya, min-of-range dan max-of-range, berperilaku baik pada data demo yang rapi lalu mengkhianati Anda pada data sungguhan yang memiliki outlier: satu nilai yang melonjak liar meregangkan skala dan meratakan setiap bar lain menjadi hanya sepotong kecil. Ketika sebuah dashboard dimaksudkan untuk dibaca lintas periode, jangkarkan titik akhirnya ke angka tetap atau persentil, sehingga setengah bar pada bulan Maret berarti kuantitas yang sama dengan setengah bar pada bulan April. Sebuah bar yang berskala otomatis hanya bisa dibandingkan dengan dirinya sendiri

Penulis XLS mencakup empat jenis rule, tidak lebih

Sisi BIFF8 yang lawas bukanlah cermin yang lebih kecil dari sisi XLSX; ia adalah subset yang memang disengaja. Facade XLS bisa membuat persis empat bentuk rule conditional, data bar, color scale dua-warna, color scale tiga-warna, dan icon set, yang dipancarkan sebagai rekaman CF12 ke dalam stream. Facade ini tidak memiliki API pembuatan untuk rule cellIs, expression, atau teks. Rule dari jenis-jenis tersebut yang sudah ada dalam sebuah file yang Anda buka akan dibaca, dipertahankan, dan ditulis kembali tanpa perubahan, sehingga membuka dan menyimpan ulang file .xls milik pelanggan tidak pernah merusak formatting yang sudah dibawanya. Yang tidak bisa Anda lakukan adalah menghasilkan highlighting berbasis threshold dari nol ke dalam sebuah .xls. Pilihan yang tersedia adalah memalsukannya dengan fill cell biasa yang dihitung dalam kode, atau menjadikan deliverable-nya sebuah .xlsx, tempat seluruh keluarga rule tersedia

Ini adalah batasan yang harus dituntaskan sebelum data layer ada, bukan sesudahnya, karena batasan ini mengubah keputusan format file untuk apa pun yang berbentuk dashboard. Sebuah tim yang memilih .xls demi kompatibilitas lalu menspesifikasikan sebuah laporan KPI dengan threshold cellIs telah memilih dua hal yang tidak cocok satu sama lain, dan waktu yang lebih murah untuk menyadarinya adalah pada saat keputusan format, bukan tiga minggu setelah pembangunan dimulai

Penumpukan rule, priority, dan range yang tumpang tindih

Dashboard sungguhan jarang menjalankan satu rule per range. Sebuah kolom varians mungkin membawa sebuah data bar untuk magnitude, sebuah rule cellIs untuk threshold tegas, dan sebuah rule expression level-baris di atas keduanya untuk eskalasi. Setiap TXLSXConditionalFormat mengekspos sebuah nilai Priority, dan Excel menyelesaikan rule yang bersaing berdasarkan urutan priority. Ketika dua rule ingin mewarnai cell yang sama, pemenangnya ditentukan oleh sebuah angka yang Anda tetapkan, bukan oleh urutan mana pun yang kebetulan dilihat seorang reviewer saat menggulir dialog Manage Rules

Perlakukan priority seperti cara sebuah program menggambar memperlakukan z-order. Tetapkan secara sengaja di mana pun dua rule bisa menjangkau cell yang sama, dan sisakan jarak di antara nilai-nilainya sehingga sebuah rule yang datang belakangan bisa disisipkan tanpa harus menomori ulang yang lain. Di tempat rule tidak mungkin bertabrakan, misalnya sebuah data bar yang terbatas pada kolom E dan sebuah rule teks yang terbatas pada kolom G, urutan pembuatan sudah cukup dan priority tidak layak mendapat perhatian. Alihkan perhatian itu ke batas range, karena bug yang mahal di sini nyaris tidak pernah berupa pembalikan priority. Bug itu justru berupa range seperti B2:B200 pada sebuah laporan yang sudah bertumbuh menjadi 350 baris, di mana ekor yang tidak tercakup dirender sebagai cell polos yang terlihat persis seperti data sehat. Turunkan setiap range rule dari nilai jumlah-baris-akhir yang sama yang menggerakkan seri chart dan range validasi di tempat lain dalam workbook, dan ekor itu berhenti terlepas

Satu kebiasaan verifikasi terbukti sepadan usahanya. Setelah generasi, buka file tersebut di Excel, pilih range yang diformat, dan telusuri Manage Rules sekali untuk setiap perubahan template. Conditional formatting adalah salah satu dari sedikit area di mana satu-satunya renderer yang otoritatif adalah aplikasi yang mengonsumsi file itu sendiri, sehingga sebuah unit test atas XML hanya membuktikan bahwa rule tersebut telah ditulis, bukan bahwa Excel mewarnainya sesuai maksud Anda. Semenit memelototinya menutup celah tersebut

Rich text: banyak format di dalam satu cell

Sebuah cell rich-text dalam model XLSX menyimpan sebuah daftar run, di mana setiap run adalah sepotong teks beserta atribut font-nya sendiri. Anda membangun daftar itu secara terpisah sebagai sebuah objek TXLSXRichText, menambahkan run ke dalamnya, lalu melekatkan keseluruhan objek itu ke sebuah cell. Aturan kepemilikan adalah bagian yang bisa menggigit. Menetapkan ke Cell.RichText menyerahkan kepemilikan objek tersebut kepada cell, dan cell itu membebaskannya saat destruksinya sendiri. Bebaskan objek itu sendiri secara manual juga, dan Anda mendapatkan sebuah double-free, jenis yang tetap diam melalui run yang menyebabkannya dan baru muncul sebagai crash di tempat lain yang tidak berhubungan jauh kemudian

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // kepemilikan berpindah ke cell: jangan panggil Free
end;

ColorIsAuto := False yang eksplisit bukanlah dekorasi opsional. Sebuah run membawa flag warna-otomatis, dan sebuah penetapan warna baru dihormati setelah flag itu dikosongkan. Setel Color dan lupakan ColorIsAuto, maka run tersebut akan keluar bold namun tetap keras kepala berwarna hitam, tanpa error yang menunjuk ke penyebabnya. Run juga mendukung strikethrough, varian underline, dan perataan vertikal untuk superscript dan subscript, sementara PlainText meratakan kembali seluruh daftar itu menjadi satu string tunggal ketika Anda perlu mengekspor atau melakukan diff pada konten teksnya

Rich text level-cell hanya tersedia di XLSX. Facade XLS tidak memiliki API publik untuk menulisnya, meski run tersedia di sana pada comment dan text box melalui TextRuns, dan rich string yang dibaca dari sebuah .xls yang sudah ada bertahan utuh melewati round-trip. Tarikannya sama seperti pada conditional formatting: apa pun yang mencampur format di dalam satu cell menjadi milik penulis XLSX

Pool style dan kesalahan off-by-one yang lolos ke produksi

Styling cell biasa dalam model XLSX berjalan lewat koleksi yang dikumpulkan dalam pool pada workbook. Fonts.Add, Fills.AddSolid, dan Borders.Add masing-masing mendaftarkan sebuah definisi dan mengembalikan indeksnya dalam pool. Indeks-indeks itu berbasis 0. Properti pada sisi cell yang mengonsumsinya, seperti FontIndex, mencadangkan 0 untuk "default", sehingga nilai yang Anda tetapkan pada sebuah cell adalah indeks pool ditambah satu:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // indeks pool, berbasis 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // indeks cell, berbasis 1

Hilangkan + 1 dan setiap header jatuh kembali ke font default. Tidak ada exception dan tidak ada peringatan, hanya sebuah workbook yang terlihat seperti tidak pernah diberi style oleh siapa pun. Kesalahan orde-kedua bersembunyi di dalam loop: memanggil Fonts.Add sekali per baris. Definisi font yang identik akan dideduplikasi, sehingga file tidak menjadi rusak, tetapi kerja itu terbuang percuma, dan pool alignment khususnya mengembalikan objek baru pada setiap pemanggilan alih-alih melipat duplikatnya. Bangun segelintir style itu sekali saja sebelum loop dan gunakan kembali indeksnya. Pada laporan ratusan-ribu-baris, perubahan tunggal itu adalah salah satu tuas yang dibahas dalam penyetelan performa workbook besar untuk HotXLS. Ketika Anda hanya butuh tampilan semantik standar, kedua facade mengekspos ApplyBuiltinStyle pada range, yang memetakan ke style bawaan Excel Good, Bad, Neutral, dan accent tanpa Anda perlu menyentuh pool sama sekali

Conditional formatting, rich text, dan pooled style adalah mil terakhir dari sebuah laporan, diterapkan setelah data model dan layout sudah mapan, dan tahap-tahap sebelumnya itu menjadi pokok bahasan pembuatan laporan berbasis template dengan HotXLS. Referensi lengkap untuk rule, run, dan style tersedia pada halaman produk HotXLS Delphi Component