Evaluasi Conditional Format Excel di Delphi dengan HotXLS

HotXLS adalah komponen spreadsheet native untuk Delphi dan C++Builder, dan sejak versi 2.209.0 ia bisa menjawab pertanyaan yang biasanya disimpan Excel untuk dirinya sendiri: untuk sel ini persis, rule conditional formatting mana yang aktif, dan menghasilkan fill, font, data bar atau icon apa. Jawaban itu yang Anda butuhkan begitu output Anda adalah laporan HTML, PDF, atau grid yang Anda gambar sendiri

Ini adalah masalah yang berbeda dari membuat rule. Dua catatan sebelumnya membahas sisi penulisannya: conditional formatting dan gaya rich text membahas melampirkan rule dan format diferensial ke sebuah range, dan partisi conditional format berjangkar membahas apa yang terjadi pada range rule saat baris dan kolom disisipkan atau dihapus. Keduanya bersifat struktural. Artikel ini soal semantik: dengan workbook yang sudah membawa rule, hitunglah highlight-nya

Kenapa format file tidak memberi tahu sel mana yang menyala?

Jawaban singkatnya adalah ECMA-376 dan ISO 29500-1 mendefinisikan penyimpanan, bukan evaluasi. Elemen conditionalFormatting (§18.3.1.18) membawa sqref dan daftar anak cfRule (§18.3.1.10), dan setiap rule membawa type, operator opsional, priority, flag stopIfTrue, satu atau dua anak formula, dan untuk keluarga visual sekumpulan ambang cfvo. Semuanya secara setia menggambarkan apa yang dikonfigurasi pengguna, dan tidak satu pun dari itu adalah algoritma. Untuk separuh tipe rule, celah itu tidak masalah: cellIs dengan operator="greaterThan" berarti lebih besar dari, dan containsText berarti substring itu ada. Celahnya terbuka pada keluarga agregat. Rule top10 dengan rank="10" dan percent="1" atas 27 sel numerik terisi menyorot berapa sel? Dua koma tujuh bukan angka. Bulatkan, floor, atau ceiling — spesifikasinya diam, dan salah memilih berarti PDF Anda tidak sepakat dengan workbook yang sedang dibuka pelanggan di sampingnya

Rule sel tunggal dan di mana TCondFormatRule.Evaluate berhenti

HotXLS mengambil separuh yang murah lebih dulu. TCondFormatRule.Evaluate di lxCondFormat.pas, ditambahkan di 2.199.0, menjawab apakah satu rule aktif untuk satu sel tanpa tahu apa pun tentang sisa range-nya. Ia menangani delapan operator perbandingan BIFF di balik cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), rule expression bebas yang dievaluasi pada sel sehingga referensi relatif rebase dengan benar, empat predikat teks, dan predikat blank serta error. Ambangnya berasal dari FFormula1 dan FFormula2 yang diselesaikan lewat TXLSCalculator.GetRangeValue pada posisi sel, dan batas yang terbalik ditukar, bukan ditolak

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

Bagian jujur dari metode itu adalah apa yang ditolaknya untuk ditebak. top10, aboveAverage, belowAverage, duplicateValues dan uniqueValues mengembalikan False, bukan karena sulit tapi karena tidak bisa ditentukan dari satu sel — masing-masing butuh statistik atas seluruh domain. Empat keluarga visual, dataBar, colorScale2, colorScale3 dan iconSet, mengembalikan False karena alasan berbeda: mereka sama sekali tidak pernah menghasilkan boolean, mereka menghasilkan payload rendering, dan tipe kembalian Boolean adalah bentuk yang salah untuk mereka

Bagaimana evaluator level-worksheet menghindari pemindaian ulang sheet?

Dengan menghitung setiap kuantitas bersama sekali, saat konstruksi, dan tidak pernah lagi. TXLSXConditionalFormatEvaluator di lxHandleX.pas adalah snapshot immutable untuk satu worksheet, dibangun lewat TXLSXWorksheet.CreateConditionalFormatEvaluator, dan seluruh desainnya adalah pertahanan terhadap implementasi naif di mana setiap sel yang dilukis memicu pemindaian penuh atas range

Empat hal terjadi di constructor. Setiap sqref multi-area yang berbeda di-parse tepat sekali menjadi TXlsxCfRangeSnapshot, jadi sepuluh rule yang berbagi satu range berbagi satu parse dan satu pass statistik. Pass itu menghitung mean, deviasi populasi, minimum dan maksimum atas sel yang terisi dalam satu kali jalan, dan hanya mempertahankan array numerik terurut saat rule Top/Bottom atau persentil benar-benar membutuhkan statistik urutan. Key duplikat dan unik dibangun aman-Unicode dan diurutkan batch sekali alih-alih per pencarian. Lalu sumbu baris dipotong menjadi band pada setiap batas area, sehingga EvaluateCell melakukan binary search satu band dan hanya mengunjungi rule yang range-nya mungkin menjangkau baris itu

Yang keempat adalah yang paling penting pada skala besar. Formula rule relatif seperti =A1>AVERAGE($A$1:$A$100) berarti sesuatu yang berbeda di setiap sel domain, dan implementasi yang jelas mengompilasi pohon sintaks baru per sel. TXlsxCfRulePlan mengompilasinya sekali dan mengevaluasi ulang pohon yang sama lewat offset koordinat yang bisa dibalik, yang mempertahankan perilaku anchor Excel tanpa alokasi pohon sintaks per sel. Rule kemudian dilapiskan berdasarkan priority, dan kecocokan pada rule yang StopIfTrue-nya diatur memutus loop, persis seperti short-circuit Excel

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Bagaimana sebenarnya Excel membulatkan rule Top 10 percent?

Excel membulatkan ke bawah (floor), dengan minimum satu, dan ia menyertakan seri (tie) pada titik potong. Itu tidak tertulis di mana pun dalam ISO 29500-1 — dipatok dengan menguji Excel 16 dengan workbook buatan tangan dan membaca balik sel mana yang disorot aplikasi tersebut. HotXLS mengimplementasikan persis itu: jumlah rank adalah Floor(Count * Min(Rank, 100) / 100), dinaikkan ke 1 saat mendarat di nol, dijepit ke jumlah yang terisi, dan nilai titik potong lalu dibandingkan dengan >= sehingga setiap sel yang sama dengan batas itu disorot bahkan saat itu melampaui jumlah yang diminta. Dua puluh tujuh nilai dan rule 10 persen menyorot dua sel, ditambah sel lain mana pun yang seri dengan yang kedua

Rule above-average menyembunyikan ambiguitas kedua: aboveAverage dengan stdDev="1" memilih sel satu standar deviasi di atas mean, tapi deviasi sampel dan populasi berbeda karena koreksi Bessel dan mereka tidak sepakat secara nyata pada range kecil, yang persis tempat conditional formatting biasa dipakai. Excel 16 memakai deviasi populasi, dan HotXLS mencocokkannya, dengan flag equalAverage membuat perbandingan ketat menjadi inklusif hanya saat tidak ada band deviasi yang berlaku. Rule duplikat dan unik justru bergantung pada identitas key. Jika satu sel berisi angka 100 dan sel lain berisi teks "100", Excel memperlakukannya sebagai key duplikat yang sama, jadi HotXLS menormalkan teks numerik ke ruang key numerik alih-alih membandingkan string mentah. Sel kosong adalah kasus cerminnya: sel kosong sejati berpartisipasi dalam penghitungan range tapi tidak diberi gaya sendiri, sehingga sel-sel kosong dalam satu kolom tidak semuanya menyala sebagai duplikat satu sama lain

Color scale dan icon set: interpolasi dan aturan batas

Keluarga visual diselesaikan menjadi angka siap-render alih-alih boolean, dan perilaku batasnya dipatok dengan cara yang sama. Untuk color scale dengan ambang numerik eksplisit, HotXLS menjepit fraksi posisi ke interval tertutup nol sampai satu, lalu menginterpolasi per channel dengan pemotongan (truncation) alih-alih pembulatan — nilai di bawah stop minimum mendapat warna minimum alih-alih warna hasil ekstrapolasi, skala tiga-stop memilih pasangannya dengan membandingkan terhadap stop titik tengah, dan skala degeneratif yang kedua ujungnya membawa ambang yang sama runtuh ke warna teratas alih-alih dibagi nol. Icon set membutuhkan perhatian yang sebaliknya, karena setiap cfvo setelah yang pertama membawa keketatan perbandingannya sendiri: HotXLS membaca ThresholdEqualsInclude per ambang dan menerapkan >= atau > sesuai itu, berjalan ke atas sehingga ambang tertinggi yang terpenuhi memenangkan indeks icon. Set yang dibalik membalik indeks yang diselesaikan alih-alih ambangnya, override per-icon bisa menarik glyph dari keluarga berbeda, dan ambang tidak valid apa pun membatalkan rule alih-alih menghasilkan icon salah yang terlihat masuk akal

Memasok satu grid, satu ekspor HTML dan satu PDF dari satu hasil

Karena EvaluateCell mengembalikan TXLSXCfCellResult yang sudah sepenuhnya diselesaikan — fill diferensial dan warna font dengan theme tint sudah diterapkan, bold, italic, underline, id format angka, ekstensi bar positif dan negatif berarah, posisi axis, keluarga icon dan indeks — setiap konsumen membaca record yang sama dan tidak satu pun perlu memahami internal rule. HotXLS memakai satu jalur itu untuk ekspor HTML, ekspor PDF dan viewer interaktif, yang merupakan satu-satunya cara praktis menjaga tiga renderer agar tidak saling menyimpang. Versi 2.210.0 menghubungkannya ke TXLSWorkbookViewer, yang meng-cache satu evaluator siap-pakai per worksheet aktif dan memakainya ulang lintas scrolling, seleksi dan repaint, melepaskannya saat workbook atau worksheet berganti — membangun ulang snapshot pada setiap Paint akan mengalahkan seluruh desain waktu-konstruksi ini. Cache itu juga alasan kenapa TXLSWorkbookViewer.RefreshConditionalFormats ada: snapshot-nya immutable, jadi jika Anda mengubah workbook yang terpasang di tempat, statistik agregat dan ambang yang terselesaikan menjadi basi sampai Anda memanggilnya

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

Apa yang tidak akan dilakukan evaluator ini untuk Anda

Tiga batasan layak dinyatakan secara jelas. TCondFormatRule.Evaluate level sel tunggal yang klasik dan TXLSXConditionalFormatEvaluator level worksheet adalah permukaan berbeda dengan kemampuan berbeda, dan yang sel-tunggal sengaja menolak keluarga agregat dan visual alih-alih mengaproksimasinya — jika Anda butuh Top/Bottom atau color scale, bangun evaluatornya. Periode tanggal relatif bergantung pada jam mesin saat evaluasi, jadi rule timePeriod ter-render berbeda dalam PDF yang dihasilkan hari ini dan yang dihasilkan minggu depan, yang merupakan perilaku benar dan tetap saja tiket support yang menunggu terjadi jika arsip Anda diharapkan stabil byte demi byte. Yang ketiga bersifat gramatikal, bukan teknis: grammar formula conditional-format melarang referensi tabel terstruktur, jadi rule tidak bisa mengalamati kolom tabel berdasarkan nama sebagaimana formula worksheet bisa, dan itu adalah batasan format, bukan implementasi

Jika Anda membangun output laporan, pipeline ekspor atau grid kustom yang harus sepakat dengan Excel sel demi sel, hasil yang sama yang sudah diselesaikan juga menggerakkan grid spreadsheet VCL kustom yang dijelaskan di tempat lain di blog ini. Dokumentasi API lengkap, model rule dan unduhan trial untuk komponen spreadsheet Delphi HotXLS tersedia di halaman produk