HotXLS ile Delphi'de Excel Koşullu Biçim Değerlendirme

27 satış rakamından oluşan bir sütunun üzerinde bir Top 10 yüzde kuralı durur ve Excel üç hücre boyar. 27'nin yüzde 10'u 2,7'dir; dolayısıyla yuvarlayan bir renderer üç bulur, tavana yuvarlayan bir renderer üç bulur, aşağı yuvarlayan bir renderer iki bulur. Bu üç tahminden ikisi bu sütunda Excel ile eşleşir ve ikisi de yanlıştır: Excel sayıyı ikiye aşağı yuvarlar ve sonra üçüncü hücreyi de boyar; çünkü ikincisiyle 88'de beraberedir. Bir değeri değiştirin, şanslı tahminler eşleşmeyi bırakır

Excel dışında koşullu biçimlendirme çizmenin tüm zorluğu budur. Kurallar dosyanın içindedir; "hangi hücreler yanar ve hangi renkle" sorusunun cevabı değildir. Çıktınız bir HTML raporu, bir PDF ya da kendinizin boyadığı bir ızgaraysa hesabı siz yapmak zorundasınız ve aritmetiğin saklandığı yer toplam kural aileleridir. Kural yazmak ayrı bir konudur; koşullu biçimlendirme ve zengin metin stilleri bunu ele alır, satırlar hareket ettiğinde bir kural aralığına ne olduğu ise bağlantılı koşullu biçimleri bölümleme makalesinde işlenir. Bu not ise değerlendirme hakkındadır

conditionalFormatting öğesinin söyledikleri ve söylemedikleri

ECMA-376 ve ISO 29500-1 depolamayı tanımlar ve orada durur. Bir conditionalFormatting öğesi (§18.3.1.18) bir sqref ve bir cfRule alt öğe listesi (§18.3.1.10) taşır. Her kuralın bir type'ı, isteğe bağlı bir operator'ü, bir priority'si, bir stopIfTrue bayrağı, bir veya iki formula alt öğesi ve görsel aileler için bir dizi cfvo eşiği vardır. Bunların tamamı kullanıcının iletişim kutusunda neye tıkladığını sadakatle kaydeder. Hiçbiri bir algoritma değildir

Birçok kural türü için bu boşluk zararsızdır. operator="greaterThan" ile cellIs büyüktür demektir, containsText alt dizenin mevcut olduğu demektir. Sorun, yargısı aralığın geri kalanına bağlı kurallarda başlar: percent="1" ile top10 kesirli bir sayının nasıl yuvarlanacağını söylemez, stdDev="1" ile aboveAverage sapmanın örnek mi popülasyon mu olduğunu söylemez ve ikisi de kesme değere eşit bir değerin nitelikli olup olmadığını söylemez. Yanlış seçerseniz PDF'iniz, müşterinin yan pencerede açık tuttuğu çalışma kitabıyla uyuşmaz

TCondFormatRule.Evaluate: tek bir hücre kendi başına neye karar verebilir

HotXLS'in iki değerlendirme yüzeyi vardır ve neyi yanıtlamaya istekli oldukları bakımından farklılaşırlar. Klasik (.xls) motor, lxCondFormat.pas içindeki TCondFormatRule.Evaluate'i açar; bu metot bir hesap makinesi, bir sayfa indeksi, bir hücre konumu ve hücre değeri alır ve tek bir kural için bir Boolean döndürür. Sekiz cellIs operatörünü (between, not between, equal, not equal, greater, less, greater or equal, less or equal), göreceli referansların doğru yeniden temellendirilmesi için hücrede değerlendirilen serbest biçimli expression kurallarını, dört metin yüklemini ve boş ile hata yüklemlerini kapsar. Eşik formülleri hücre konumunda çözülür ve sınırları ters gelen bir between kuralı reddedilmek yerine sınırların yerini değiştirir. Metin karşılaştırmaları Excel'de olduğu gibi büyük-küçük harfe duyarsızdır

// Klasik motor: bir kural, bir hücre, bir Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
  SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to CondFormat.RuleCount - 1 do
    // top10, ortalamalar, kopyalar, zaman dönemleri ve görsel türler burada
    // False yanıtlar; tüm aralığa ihtiyaçları var
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

Metot top10, aboveAverage, belowAverage, duplicateValues ve uniqueValues için False döndürür. Bir hücre, öteki değerleri görmeden ilk yüzde onda olup olmadığını bilemez; yanıtlamak tahmin etmek olurdu. Göreceli tarih kuralları (timePeriod) da bu yolda False yanıtlar. Dört görsel aile, dataBar, colorScale2, colorScale3 ve iconSet, farklı bir nedenle False döndürür: hiçbir zaman evet ya da hayır üretmezler; bir renk, bir çubuk uzunluğu ya da bir simge indeksi üretirler ve Boolean'ın bunları koyacak yeri yoktur. Bu çağrıdan dönen bir False bu yüzden "eşleşmez" olduğu kadar "burada karara bağlanmadı" da demektir; karşılaştırma kuralları üzerinde hızlı bir kontrol için sorun değil ama sevk ettiğiniz her şey için güvensiz

Bir çalışma sayfası değerlendiricisi her hücre için sayfayı yeniden taramaktan nasıl kaçınır?

Aralık genelindeki tüm işi, oluşturulduğu anda bir kez yapar. lxHandleX.pas içindeki TXLSXConditionalFormatEvaluator, bir çalışma sayfasının koşullu biçimlerinin değişmez bir anlık görüntüsüdür; TXLSXWorksheet.CreateConditionalFormatEvaluator üzerinden alınır, sahibi çağırandır ve onu serbest bırakır. Naif alternatifte her boyanan hücre, bir ortalama ya da bir sırayı yeniden hesaplamak için kural aralığının taranmasını tetikler ve 10.000 hücrelik bir export, kural başına 10.000 aralık taramasına döner

Değerlendirici, oluşturum sırasında her farklı sqref'i bir kez ayrıştırır; böylece aynı aralık üzerindeki on kural tek ayrıştırmayı ve dolu hücreleri üzerindeki tek geçişi paylaşır. O geçiş, ortalamayı, popülasyon sapmasını, minimumu ve maksimumu tek yürüyüşte toplar ve sayıların sıralı bir kopyasını yalnızca bir Top/Bottom kuralı ya da bir yüzdelik eşik gerçekten sıralama istatistiğine ihtiyaç duyduğunda tutar. Kopya ve benzersiz kuralların değer anahtarları bir kez kurulur ve sıralanır; sonraki her arama bir ikili aramadır. Kural formülleri bir kez derlenir ve hücrenin ofseti uygulanarak hücre başına yeniden değerlendirilir; böylece =A1>AVERAGE($A$1:$A$100) her satır için yeniden ayrıştırılmadan göreceli ve mutlak bölümlerini tam Excel'deki gibi korur. Satırlar gruplanır; EvaluateCell yalnızca aralıkları istenen satıra ulaşabilen kuralları ziyaret eder

Kurallar sonra priority sırasına göre uygulanır; en küçük sayı önce, eşitlikler dosyadaki konumla çözülür. Her biçimlendirme özelliği, onu ayarlayan ilk eşleşen kuraldan alınır ve daha düşük öncelikli kurallar yalnızca hâlâ boş olan özellikleri doldurur: kalın ayarlayan priority-1 bir kural ile kırmızı dolgu ayarlayan priority-2 bir kural ikisi de görünür ve hücre kırmızı üzerinde kalın çıkar. StopIfTrue taşıyan bir kuraldaki eşleşme, o hücre için yürüyüşü bitirir

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Render geçişi başına bir anlık görüntü; satır ve sütunlar 1 tabanlıdır
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    for Row := FirstRow to LastRow do
      for Col := FirstCol to LastCol do
      begin
        if not Evaluator.EvaluateCell(Row, Col, Res) then
          Continue;
        if Res.HasFillColor then
          Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF);  // BGR, TColor ile aynı düzen
        if Res.HasDataBar then  // DrawBar sizin kendi rutininizdir
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon sizin kendi rutininizdir
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText sizin kendi rutininizdir
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

Anlık görüntü, amaçlı olarak geçiş başına bir kez kurulur. Onu iç döngünün içinde oluşturmak her şeyi her hücre için yeniden derler ve tarar; tasarımın var olma amacı tam da bu maliyetten kaçınmaktır. Bir veri çubuğu kuralı showValue="0" taşıdığında ya da bir simge seti yalnızca simgeyi gösterecek ayarlıysa ShowCellValue False döner; renderer çubuğu ya da glifi çizer ama sayıyı çizmez

Bir Top 10 yüzde kuralı gerçekte kaç hücre vurgular?

Sayı aşağı yuvarlanır, asla birden aşağı inmez ve kesme değerle berabere kalan her değer dahil edilir. Bunu belirtim söylemez; Excel'in yaptığı budur ve şekildeki 27 değeri bir sütuna yazıp Top 10% uygulayarak boyanan hücreleri saymakla bir dakikada doğrulayabilirsiniz. TXLSXConditionalFormatEvaluator sıra sayısını Floor(Count * Min(Rank, 100) / 100) olarak hesaplar, sıfıra düştüğünde 1'e çıkarır, dolu sayısal hücre sayısına kırpar, kesme değeri sıralı değerlerden okur ve sonra her hücreyi >= ile karşılaştırır. 27 değerlik sütun için bu, iki sayı, 88 kesme değeri ve üç boyalı hücre demektir; çünkü ikisi 88 tutar. Bottom-N kuralları bunu <= ile aynalar

Ortalama üstü kuralları ikinci belirsizliği taşır. stdDev="1" ile kural, ortalamanın en az bir standart sapma üstündeki hücreleri seçer; örnek ve popülasyon sapması Bessel düzeltmesi kadar farklılaşır ve insanların bu kuralı gerçekten kullandığı kısa aralıklarda en çok fark yaratan da budur. Beş değerde ikisi yaklaşık yüzde 12 farklıdır. HotXLS popülasyon sapmasını kullanır; STDEV.P'nin döndürdüğü sayının aynısı. Herhangi bir renderer'ı hızlıca sınamanın yolu, aralığın yanına =AVERAGE(r)+STDEV.P(r) koymak ve hangi hücrelerin onu aştığına bakmaktır. Sapma bandına karşı karşılaştırmalar kapsayıcıdır; düz üstünde/altında karşılaştırması ise kural equalAverage="1" taşımadıkça katıdır

Kopya ve benzersiz kuralları "aynı değer" sayılacak şeye bağlıdır. 100 sayısı ile "100" metni bir anahtarı paylaşır; çünkü sayısal metin sayısal anahtar uzayına normalize edilir. Metin anahtarları büyük-küçük harfe duyarsız karşılaştırılır; TRUE ile 1 ayrı kalır, farklı türdeki hata değerleri de öyle. * ya da ? içeren metin, COUNTIF usulü joker desen olarak eşlenir; Q* tutan bir hücre, Q ile başlayan her değere karşı sayılır. Boş hücreler ters tuzaktır: aralık içindeki gerçekten boş bir hücre asla kopya olarak boyanmaz, bu yüzden yarım kalmış bir sütun tepeden tırnağa yanmaz; boş dize saklayan bir hücre ise boşlarla birlikte sayılır

Renk skalaları, veri çubukları ve simge setleri sayı olarak döner

Görsel aileler render'a hazır değerlere çözülür ve elle yazılmış renderer'ların saptığı yer kenarlarıdır. Bir renk skalasında EvaluateCell konumu 0 ile 1 aralığına kırpar ve her RGB kanalını kesmeyle enterpolasyon yapar; minimum durağın altındaki bir değer ekstrapole edilmiş bir renk yerine minimum rengi alır. Üç duraklı bir skala, orta nokta eşiğinin üstündeki değerler için orta-maks çiftini, aksi halde min-orta çiftini kullanır. İlk ve son eşiği aynı sayıya çözülen bir skala, sıfıra bölmek yerine son rengi döndürür. Yüzdelik eşikleri, komşu sıralı değerler arasında doğrusal olarak enterpolasyon yapar; PERCENTILE.INC tanımı

Veri çubukları hücre genişliğine normalize edilir. Tümü pozitif bir aralıkta DataBarAxis 0'dır ve DataBarEnd, kuralın minimum ve maksimum çubuk uzunluğu arasında ilerler; varsayılanları yüzde 10 ve 90'dır, böylece en küçük değer yine de kısa bir çubuk alır ve en büyüğü hücreyi hiçbir zaman tam doldurmaz. Aralık sıfırı kapsadığında eksen orantısal konumunda durur ve DataBarEnd çubuğun nerede durduğunu işaretler; negatif değerler için eksenin uzak tarafında. Yön, gradyan, kenarlık ve negatif çubuk renkleri aynı kayda işlenir; tema renkleri ve tonları çoktan çözülmüş olarak

Simge setleri her eşiğin katılığına özen ister; çünkü ilkinden sonraki her cfvo kendi gte bayrağını taşır. Değerlendirici eşikleri yukarı doğru yürür, ThresholdEqualsInclude'un söylediği gibi eşik başına >= ya da > uygular ve değerin aştığı en yüksek eşik kazanır. Ters çevrilmiş bir set çözülen indeksi çevirir; simge başına geçersiz kılmalar başka bir aileden bir glifi IconSetType üzerinden ikame edebilir ve çözülemeyen bir eşik kuralı o hücre için durdurur; hücre makul görünen yanlış bir simge yerine hiç simge almaz

Izgara, HTML export'u ve PDF için tek sonuç

TXLSXCfCellResult bir renderer'ın ihtiyaç duyduğu her şeyi taşır: tema tonu uygulanmış dolgu ve font rengi, kalın, italik, altı çizili, üstü çizili, bir sayı biçimi kimliği, veri çubuğu geometrisi ve renkleri, simge ailesi ve indeksi. HotXLS aynı kaydı HTML export'una, PDF export'una ve TXLSWorkbookViewer'a verir; üç çıktı hücre hücre uyum içinde kalır. Görüntüleyici ilk boyamada etkin çalışma sayfası için bir değerlendirici kurar ve onu kaydırma, seçim ve yeniden boyama boyunca yeniden kullanır; sayfa değiştirdiğinizde ya da başka bir çalışma kitabı atadığınızda onu bırakır

// Canlı bir görüntüleyicinin arkasında düzenleme: önbellekteki anlık görüntü değişikliği görmez
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // 1 tabanlı sayfa indeksi
Sheet.Cells[5, 2].Value := 4200;          // ortalamayı, maksimumu ve sıralamayı değiştirir
Viewer.RefreshConditionalFormats;         // anlık görüntüyü at, yeniden boya

Anlık görüntü değişmez olduğu için yerinde bir düzenleme, RefreshConditionalFormats çalışana kadar ortalamaları, sıraları ve renk skalası uçlarını bayat bırakır. Aynı şey kendi kodunuz için de geçerlidir: düzenlemeler arasında tuttuğunuz bir değerlendirici serbest bırakılıp yeniden kurulmalıdır; çünkü CreateConditionalFormatEvaluator değerleri çağrıldığı anda yakalar

Göreceli tarih kuralları baştan bir karar hak eder. Çalışma sayfası değerlendiricisi timePeriod kurallarını makinenin geçerli tarihiyle karşılaştırır; "bu hafta" kuralı bugün üretilen bir PDF'de bir şekilde, aynı çalışma kitabı gelecek salı export edildiğinde başka bir şekilde render olur. Bu doğru davranıştır ve arşivinizin yeniden üretilebilir olması bekleniyorsa yine de bir destek talebidir. Arşivlenen raporlar için timePeriod kurallarını, bir hücrede saklanan rapor tarihiyle karşılaştıran bir expression kuralıyla değiştirin; yedi günlük pencere için =AND(A2>=$Z$1-6,A2<=$Z$1) gibi. Çıktı sonra çalışma kitabına ve yalnızca ona bağlı olur. Excel ayrıca koşullu biçim formülü içinde yapılandırılmış tablo referanslarını reddeder; kuralı bunun yerine sütunun hücre aralığına yönlendirin

Hedefiniz dosya değil de bir ekransa, aynı sonuç kaydı bu blogda başka bir yerde anlatılan özel VCL elektronik tablo ızgarasını da yönlendirir. Kural modeli, değerlendirici API referansı ve deneme yapıları HotXLS Delphi elektronik tablo bileşeni sayfasındadır