Ocena formatowania warunkowego Excel w Delphi z HotXLS

Reguła Top 10 percent siedzi na kolumnie 27 liczb sprzedaży, a Excel maluje trzy komórki. Dziesięć procent z 27 to 2,7, więc renderer, który zaokrągla, dostaje trzy, renderer, który bierze sufit, dostaje trzy, a renderer, który zaokrągla w dół, dostaje dwie. Dwa z tych trzech strzałów zgadzają się z Excelem na tej kolumnie i oba są złe: Excel zaokrągla liczbę w dół do dwóch, a potem maluje też trzecią komórkę, bo remisuje z drugą na 88. Zmień jedną wartość, a szczęśliwe strzały przestają się zgadzać

To cała trudność rysowania formatowania warunkowego poza Excelem. Reguły są w pliku; odpowiedzi na pytanie "które komórki się zapalają i na jaki kolor" tam nie ma. Jeśli twoim wyjściem jest raport HTML, PDF albo siatka malowana samodzielnie, musisz to policzyć, a rodziny reguł agregujących są miejscem, gdzie ta arytmetyka się kryje. Tworzenie reguł to osobny temat, omówiony w formatowaniu warunkowym i stylach rich text, a to, co dzieje się z zakresem reguły, gdy wiersze się przesuwają, opisuje partycjonowanie zakotwiczonych formatów warunkowych. Ta notatka jest o ewaluacji

Co element conditionalFormatting mówi, a czego nie

ECMA-376 i ISO 29500-1 definiują przechowywanie i na tym się kończą. Element conditionalFormatting (§18.3.1.18) niesie sqref i listę dzieci cfRule (§18.3.1.10). Każda reguła ma type, opcjonalny operator, priority, flagę stopIfTrue, jedno lub dwa dzieci formula i, dla rodzin wizualnych, zestaw progów cfvo. To wszystko wiernie zapisuje, co użytkownik kliknął w oknie dialogowym. Żaden z tych zapisów nie jest algorytmem

Dla wielu typów reguł ta luka jest nieszkodliwa. cellIs z operator="greaterThan" znaczy większe niż, a containsText znaczy, że podciąg jest obecny. Kłopoty zaczynają się przy regułach, których werdykt zależy od reszty zakresu: top10 z percent="1" nie mówi, jak zaokrągla się ułamkowa licznik, aboveAverage z stdDev="1" nie mówi, czy odchylenie jest próbkowe, czy populacyjne, a żadne nie mówi, czy wartość równa progowi się kwalifikuje. Strzelisz źle i twój PDF będzie się różnił od skoroszytu, który klient ma otwarty w sąsiednim oknie

TCondFormatRule.Evaluate: co jedna komórka może rozstrzygnąć sama

HotXLS ma dwie powierzchnie ewaluacji i różnią się tym, na co są gotowe odpowiedzieć. Klasyczny silnik (.xls) wystawia TCondFormatRule.Evaluate w lxCondFormat.pas, które bierze kalkulator, indeks arkusza, pozycję komórki i wartość komórki, a zwraca wartość logiczną dla jednej reguły. Pokrywa osiem operatorów cellIs (between, not between, equal, not equal, greater, less, greater or equal, less or equal), dowolne reguły expression liczone na komórce, dzięki czemu referencje względne przeliczają się poprawnie, cztery predykaty tekstowe oraz predykaty pustości i błędu. Formuły progowe rozwiązują się na pozycji komórki, a reguła between z odwróconymi granicami dostaje je zamienione, a nie odrzucone. Porównania tekstu nie rozróżniają wielkości liter, tak jak w Excelu

// Klasyczny silnik: jedna reguła, jedna komórka, jedna wartość logiczna
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, średnie, duplikaty, okresy czasu i rodziny wizualne
    // odpowiadają tu False; potrzebują całego zakresu
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

Metoda zwraca False dla top10, aboveAverage, belowAverage, duplicateValues i uniqueValues. Jedna komórka nie wie, czy jest w górnych dziesięciu procentach, dopóki nie zobaczy pozostałych wartości, więc odpowiedź byłaby zgadywaniem. Względne reguły dat (timePeriod) też odpowiadają False na tej ścieżce. Cztery rodziny wizualne, dataBar, colorScale2, colorScale3 i iconSet, odpowiadają False z innego powodu: nigdy nie produkują tak albo nie, produkują kolor, długość paska albo indeks ikony, a wartość logiczna nie ma na to miejsca. False z tego wywołania znaczy więc "nie rozstrzygnięte tutaj" równie często jak "nie pasuje", co uchodzi przy szybkiej kontroli reguł porównania i jest niebezpieczne dla wszystkiego, co wysyłasz

Jak ewaluator arkusza unika ponownego skanowania arkusza dla każdej komórki?

Całą pracę dla całego zakresu wykonuje raz, przy tworzeniu. TXLSXConditionalFormatEvaluator w lxHandleX.pas to niemutowalny snapshot formatów warunkowych jednego arkusza, do wzięcia z TXLSXWorksheet.CreateConditionalFormatEvaluator; wywołujący go posiada i zwalnia. Naiwna alternatywa, w której każda malowana komórka wywołuje skan swojego zakresu reguły, żeby przeliczyć średnią albo rangę, zamienia eksport 10 000 komórek w 10 000 skanów zakresu na regułę

Przy konstrukcji ewaluator parsuje każdy odrębny sqref raz, więc dziesięć reguł na tym samym zakresie dzieli jeden parse i jeden przebieg po jego wypełnionych komórkach. Ten przebieg zbiera średnią, odchylenie populacyjne, minimum i maksimum w jednym marszu, a uporządkowaną kopię liczb trzyma tylko wtedy, gdy reguła Top/Bottom albo próg percentylowy naprawdę potrzebuje statystyk porządkowych. Reguły duplikatów i unikatów dostają klucze wartości zbudowane i posortowane raz, więc każde późniejsze wyszukiwanie to wyszukiwanie binarne. Formuły reguł kompilują się raz i są liczone ponownie per komórka z nałożonym przesunięciem komórki, więc =A1>AVERAGE($A$1:$A$100) zachowuje części względne i bezwzględne dokładnie tak, jak w Excelu, bez ponownego parsowania dla każdego wiersza. Wiersze są grupowane tak, by EvaluateCell odwiedzał tylko reguły, których zakresy sięgają żądanego wiersza

Reguły nakładają się potem w kolejności priority, od najniższej liczby, a przy remisach rozstrzyga pozycja w pliku. Każdą właściwość formatowania bierze pierwsza pasująca reguła, która ją ustawia, a reguły o niższym priorytecie dopełniają tylko wciąż pustych właściwości: reguła o priorytecie 1 ustawiająca pogrubienie i reguła o priorytecie 2 ustawiająca czerwone wypełnienie pokazują się obie, i komórka wychodzi pogrubiona na czerwono. Trafienie w regułę z StopIfTrue kończy marsz dla tej komórki

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Jeden snapshot na przebieg renderowania; wiersze i kolumny liczą się od 1
  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, ten sam układ co TColor
        if Res.HasDataBar then  // DrawBar to twoja własna procedura
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon to twoja własna procedura
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText to twoja własna procedura
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

Snapshot buduje się raz na przebieg celowo. Stworzenie go w pętli wewnętrznej kompiluje i skanuje wszystko od nowa dla każdej komórki, a to dokładnie ten koszt, którego ta konstrukcja unika. ShowCellValue wraca jako False, gdy reguła paska danych ma showValue="0" albo zestaw ikon jest ustawiony na pokazywanie samej ikony, więc renderer rysuje pasek albo glif i pomija liczbę

Ile komórek naprawdę podświetla reguła Top 10 percent?

Liczbę rangi zaokrągla się w dół, nigdy nie wolno jej zejść poniżej jedynki, a każda wartość zremisowana z progiem jest wliczana. Specyfikacja tego nie mówi; tak robi Excel, a potwierdzisz to w minutę, wpisując 27 wartości z rysunku do kolumny, nakładając Top 10% i licząc zamalowane komórki. TXLSXConditionalFormatEvaluator liczy liczbę rangi jako Floor(Count * Min(Rank, 100) / 100), podnosi ją do 1, gdy wypadnie zero, przycina do liczby wypełnionych komórek numerycznych, czyta próg z posortowanych wartości, a potem porównuje każdą komórkę przez >=. Dla kolumny 27 wartości to licznik dwa, próg 88 i trzy zamalowane komórki, bo dwie z nich trzymają 88. Reguły Bottom-N robią to samo z <=

Reguły powyżej średniej noszą drugą niejednoznaczność. Z stdDev="1" reguła wybiera komórki co najmniej jedno odchylenie standardowe powyżej średniej, a odchylenie próbkowe i populacyjne różni poprawka Bessela, co najbardziej gra na krótkich zakresach, gdzie ludzie faktycznie używają tej reguły. Na pięciu wartościach różnica wynosi około 12 procent. HotXLS używa odchylenia populacyjnego, tej samej liczby, którą zwraca STDEV.P, więc szybkim sposobem sprawdzenia dowolnego renderera jest postawienie =AVERAGE(r)+STDEV.P(r) obok zakresu i zobaczenie, które komórki go przebiją. Porównania z pasmem odchylenia są włącznie; proste porównanie powyżej lub poniżej jest ostre, chyba że reguła niesie equalAverage="1"

Reguły duplikatów i unikatów włączają pytanie, co liczy się jako "ta sama wartość". Liczba 100 i tekst "100" dzielą klucz, bo tekst numeryczny normalizuje się do przestrzeni kluczy numerycznych; klucze tekstowe porównują się bez rozróżniania wielkości liter; TRUE i 1 zostają odrębne, podobnie wartości błędu różnego rodzaju. Tekst zawierający * albo ? dopasowuje się jako wzorzec z dzikimi znakami, w stylu COUNTIF, więc komórka trzymająca Q* liczy się przeciw każdej wartości zaczynającej się od Q. Puste komórki to odwrotna pułapka: naprawdę pusta komórka wewnątrz zakresu nigdy nie maluje się jako duplikat, więc niedokończona kolumna nie zapala się od góry do dołu, podczas gdy komórka przechowująca pusty string liczy się wśród pustych

Skale kolorów, paski danych i zestawy ikon wracają jako liczby

Rodziny wizualne rozwiązują się do wartości gotowych do renderowania, a ich krawędzie to miejsca, gdzie ręcznie pisane renderery się rozjeżdżają. Dla skali kolorów EvaluateCell przycina pozycję do przedziału od 0 do 1 i interpoluje każdy kanał RGB z obcięciem, więc wartość poniżej dolnego stopnia dostaje kolor minimalny zamiast ekstrapolowanego. Skala o trzech stopniach używa pary środek-maksimum dla wartości powyżej progu środkowego i pary minimum-środek w pozostałych przypadkach. Skala, której pierwszy i ostatni próg rozwiązują się do tej samej liczby, zwraca ostatni kolor zamiast dzielić przez zero. Progi percentylowe interpolują się liniowo między sąsiednimi posortowanymi wartościami, według definicji PERCENTILE.INC

Paski danych normalizują się do szerokości komórki. Dla zakresu w pełni dodatniego DataBarAxis wynosi 0, a DataBarEnd biegnie między minimalną a maksymalną długością paska reguły, domyślnie 10 i 90 procent, więc najmniejsza wartość wciąż dostaje krótki pasek, a największa nigdy do końca nie wypełnia komórki. Gdy zakres przecina zero, oś siedzi na swojej proporcjonalnej pozycji, a DataBarEnd znaczy, gdzie pasek się zatrzymuje, po dalekiej stronie osi dla wartości ujemnych. Kierunek, gradient, obramowanie i kolory paska ujemnego dopełniają się na tym samym rekordzie, z kolorami motywu i tintami już rozwiązanymi

Zestawy ikon wymagają uwagi przy ostrości każdego progu, bo każdy cfvo po pierwszym niesie własną flagę gte. Ewaluator przechodzi progi w górę, nakładając >= albo > per próg, jak mówi ThresholdEqualsInclude, i wygrywa najwyższy próg, który wartość przebija. Odwrócony zestaw odwraca rozwiązany indeks, nadpisania per ikona mogą podstawić glif z innej rodziny przez IconSetType, a próg, którego nie da się rozwiązać, zatrzymuje regułę dla tej komórki, więc komórka nie dostaje żadnej ikony zamiast wiarygodnie złej

Jeden wynik dla siatki, eksportu HTML i PDF

TXLSXCfCellResult niesie wszystko, czego renderer potrzebuje: kolor wypełnienia i czcionki z nałożoną tintą motywu, pogrubienie, kursywę, podkreślenie, przekreślenie, identyfikator formatu liczbowego, geometrię i kolory paska danych oraz rodzinę i indeks ikony. HotXLS podaje ten sam rekord swojemu eksportowi HTML, eksportowi PDF i TXLSWorkbookViewer, co trzyma trzy wyjścia w zgodzie komórka w komórkę. Podgląd tworzy jeden ewaluator dla aktywnego arkusza przy pierwszym malowaniu i używa go ponownie przy przewijaniu, zaznaczaniu i przemalowywaniu, a wyrzuca, gdy przełączysz arkusz albo podepniesz inny skoroszyt

// Edycja za aktywnym podglądem: zbuforowany snapshot nie widzi zmiany
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // indeks arkusza liczony od 1
Sheet.Cells[5, 2].Value := 4200;          // przesuwa średnią, maksimum i ranking
Viewer.RefreshConditionalFormats;         // wyrzuć snapshot i przemaluj

Ponieważ snapshot jest niemutowalny, edycja w miejscu zostawia średnie, rangi i krańce skali kolorów nieaktualne, dopóki nie ruszy RefreshConditionalFormats. To samo dotyczy twojego kodu: ewaluator trzymany przez edycje trzeba zwolnić i stworzyć od nowa, bo CreateConditionalFormatEvaluator przechwytuje wartości w chwili wywołania

Względne reguły dat zasługują na decyzję z góry. Ewaluator arkusza porównuje reguły timePeriod z bieżącą datą maszyny, więc reguła "w tym tygodniu" renderuje się dziś inaczej, a inaczej, gdy ten sam skoroszyt wyeksportujesz w przyszły wtorek. To poprawne zachowanie i wciąż zgłoszenie supportowe, jeśli od twojego archiwum oczekuje się odtwarzalności. Dla raportów archiwizowanych zamień reguły timePeriod na regułę expression porównującą z datą raportu zapisaną w komórce, na przykład =AND(A2>=$Z$1-6,A2<=$Z$1) dla siedmiodniowego okna; wyjście zależy wtedy od skoroszytu i od niczego więcej. Excel odmawia też referencji strukturalnych tabel wewnątrz formuły formatu warunkowego, więc skieruj regułę na zakres komórek kolumny

Jeśli celem jest ekran, a nie plik, ten sam rekord wyniku napędza też własną siatkę arkuszową VCL opisaną gdzie indziej na tym blogu. Model reguł, referencja API ewaluatora i wersje próbne są na stronie HotXLS Delphi spreadsheet component