HotXLS to natywny komponent arkuszy kalkulacyjnych dla Delphi i C++Builder, a od wersji 2.209.0 potrafi odpowiedzieć na pytanie, które Excel normalnie zachowuje dla siebie: dla tej konkretnej komórki, które reguły formatowania warunkowego się uruchamiają i do jakiego wypełnienia, czcionki, paska danych albo ikony się rozwiązują. Ta odpowiedź jest tym, czego potrzebujesz w chwili, gdy Twoim wyjściem jest raport HTML, PDF albo siatka, którą malujesz sam
To inny problem niż tworzenie reguł. Dwie wcześniejsze notatki pokrywają stronę autorską: formatowanie warunkowe i style tekstu sformatowanego zajmuje się dołączaniem reguł i formatów różnicowych do zakresu, a partycjonowanie zakotwiczonych formatów warunkowych zajmuje się tym, co dzieje się z zakresem reguły, gdy wiersze i kolumny są wstawiane lub usuwane. Obie są strukturalne. Ten artykuł dotyczy semantyki: mając skoroszyt, który już niesie reguły, obliczyć podświetlenie
Dlaczego format pliku nie mówi, które komórki się zapalają
Krótka odpowiedź brzmi: ECMA-376 i ISO 29500-1 definiują przechowywanie, nie ocenę. Element conditionalFormatting (§18.3.1.18) niesie sqref i listę dzieci cfRule (§18.3.1.10), a każda reguła niesie type, opcjonalny operator, priority, flagę stopIfTrue, jedno lub dwa dzieci formula, a dla rodzin wizualnych zestaw progów cfvo. Każdy z nich wiernie opisuje to, co skonfigurował użytkownik, i żaden nie jest algorytmem. Dla połowy typów reguł ta luka nie ma znaczenia: cellIs z operator="greaterThan" znaczy większe niż, a containsText znaczy, że podciąg jest obecny. Luka otwiera się na rodzinach agregujących. Reguła top10 z rank="10" i percent="1" nad 27 wypełnionymi komórkami liczbowymi podświetla ile komórek? Dwa i siedem dziesiątych to nie liczba. Zaokrąglenie, podłoga, czy sufit — specyfikacja milczy, a wybór błędny oznacza, że Twój PDF nie zgadza się ze skoroszytem, który klient ma otwarty obok
Reguły jednokomórkowe i gdzie TCondFormatRule.Evaluate się zatrzymuje
HotXLS wzięło najpierw tanią połowę. TCondFormatRule.Evaluate w lxCondFormat.pas, dodane w 2.199.0, odpowiada, czy jedna reguła uruchamia się dla jednej komórki, nie wiedząc nic o reszcie zakresu. Obsługuje osiem operatorów porównania BIFF stojących za cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), dowolne reguły expression oceniane w komórce, tak by referencje względne poprawnie się przebazowywały, cztery predykaty tekstowe oraz predykaty pustych i błędów. Progi pochodzą z FFormula1 i FFormula2, rozwiązywane przez TXLSCalculator.GetRangeValue w pozycji komórki, a odwrócone granice są zamieniane miejscami, nie odrzucane
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;Uczciwą częścią tej metody jest to, czego odmawia zgadywać. top10, aboveAverage, belowAverage, duplicateValues i uniqueValues zwracają False, nie dlatego, że są trudne, lecz dlatego, że są nierozstrzygalne na podstawie jednej komórki — każda z nich potrzebuje statystyki nad całą domeną. Cztery rodziny wizualne, dataBar, colorScale2, colorScale3 i iconSet, zwracają False z innego powodu: nigdy w ogóle nie produkują booleana, produkują ładunek do renderowania, a typ zwracany Boolean jest dla nich niewłaściwym kształtem
Jak ewaluator na poziomie arkusza unika ponownego skanowania arkusza?
Obliczając każdą współdzieloną wielkość raz, przy konstrukcji, i nigdy więcej. TXLSXConditionalFormatEvaluator w lxHandleX.pas to niemutowalna migawka dla jednego arkusza, budowana przez TXLSXWorksheet.CreateConditionalFormatEvaluator, a cały jej projekt jest obroną przed naiwną implementacją, w której każda malowana komórka wyzwala pełne skanowanie zakresu
W konstruktorze dzieją się cztery rzeczy. Każdy odrębny wieloobszarowy sqref jest parsowany dokładnie raz do TXlsxCfRangeSnapshot, więc dziesięć reguł współdzielących jeden zakres współdzieli jeden parsing i jeden przebieg statystyk. Ten przebieg strumieniuje średnią, odchylenie populacyjne, minimum i maksimum nad wypełnionymi komórkami w jednym przejściu i zachowuje uporządkowaną tablicę liczbową tylko wtedy, gdy reguła Top/Bottom albo percentyla faktycznie potrzebuje statystyk pozycyjnych. Klucze duplikatów i unikatów są budowane w sposób bezpieczny dla Unicode i sortowane wsadowo raz, zamiast przy każdym wyszukaniu. Potem oś wierszy jest cięta na pasma na każdej granicy obszaru, więc EvaluateCell przeszukuje binarnie pasmo i odwiedza tylko reguły, których zakresy potencjalnie mogą sięgać do tego wiersza
Czwarta rzecz ma największe znaczenie w skali. Względna formuła reguły, taka jak =A1>AVERAGE($A$1:$A$100), znaczy coś innego w każdej komórce domeny, a oczywista implementacja kompiluje świeże drzewo składni na komórkę. TXlsxCfRulePlan kompiluje je raz i ponownie ocenia to samo drzewo przez odwracalne przesunięcia współrzędnych, co zachowuje zachowanie zakotwiczenia Excela bez alokacji drzewa składni na komórkę. Reguły są potem warstwowane według priority, a trafienie w regułę, której StopIfTrue jest ustawione, przerywa pętlę, dokładnie tak jak Excel skraca obwód
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;Jak Excel faktycznie zaokrągla regułę Top 10 procent?
Zaokrągla w dół, z minimum jeden, i obejmuje remisy przy progu odcięcia. Nigdzie nie jest to zapisane w ISO 29500-1 — zostało przypięte przez sondowanie Excela 16 ręcznie zbudowanymi skoroszytami i odczytywanie, które komórki aplikacja podświetliła. HotXLS implementuje dokładnie to: liczba rankingowa to Floor(Count * Min(Rank, 100) / 100), podniesiona do 1, gdy wypada zero, przycięta do liczby wypełnionych komórek, a wartość progowa jest potem porównywana za pomocą >=, więc każda komórka równa granicy jest podświetlana, nawet gdy to przekracza żądaną liczbę. Dwadzieścia siedem wartości i reguła 10 procent podświetla dwie komórki, plus wszelkie dalsze komórki zremisowane z drugą
Reguły powyżej średniej ukrywały drugą niejednoznaczność: aboveAverage z stdDev="1" wybiera komórki jedno odchylenie standardowe powyżej średniej, ale odchylenie próbkowe i populacyjne różnią się o poprawkę Bessela i widocznie się nie zgadzają na małych zakresach, czyli dokładnie tam, gdzie formatowanie warunkowe bywa używane. Excel 16 używa odchylenia populacyjnego, a HotXLS to dopasowuje, z flagą equalAverage czyniącą ścisłe porównanie inkluzywnym tylko wtedy, gdy żadne pasmo odchylenia nie jest w grze. Reguły duplikatów i unikatów okazały się polegać na tożsamości klucza. Jeśli jedna komórka trzyma liczbę 100, a druga tekst „100”, Excel traktuje je jako ten sam klucz duplikatu, więc HotXLS normalizuje tekst liczbowy do przestrzeni kluczy liczbowych zamiast porównywać surowe ciągi znaków. Puste komórki są przypadkiem lustrzanym: prawdziwie pusta komórka bierze udział w rozliczeniu zakresu, ale sama nie jest stylizowana, więc puste komórki w kolumnie nie zapalają się wszystkie jako wzajemne duplikaty
Skale kolorów i zestawy ikon: interpolacja i reguły graniczne
Rodziny wizualne rozwiązują się do liczb gotowych do renderowania zamiast booleanów, a ich zachowanie brzegowe zostało przypięte w ten sam sposób. Dla skali kolorów z jawnymi progami liczbowymi HotXLS przycina ułamek pozycji do domkniętego przedziału zero do jeden, potem interpoluje per kanał z obcinaniem zamiast zaokrąglania — wartość poniżej minimalnego progu dostaje kolor minimalny zamiast ekstrapolowanego, skala trzyprogowa wybiera swoją parę, porównując z progiem środkowym, a zdegenerowana skala, której dwa końce niosą ten sam próg, zapada się do koloru górnego zamiast dzielić przez zero. Zestawy ikon wymagały odwrotnego rodzaju uwagi, ponieważ każdy cfvo po pierwszym niesie własną rygorystyczność porównania: HotXLS czyta ThresholdEqualsInclude na próg i stosuje odpowiednio >= lub >, idąc w górę, tak że najwyższy spełniony próg wygrywa indeks ikony. Odwrócony zestaw odwraca rozwiązany indeks, nie progi, nadpisania per ikona mogą wyciągnąć glif z innej rodziny, a jakikolwiek nieprawidłowy próg przerywa regułę zamiast produkować prawdopodobnie wyglądającą, lecz błędną ikonę
Zasilanie siatki, eksportu HTML i PDF jednym wynikiem
Ponieważ EvaluateCell zwraca w pełni rozwiązany TXLSXCfCellResult — kolor wypełnienia i czcionki różnicowy z już zastosowaną tonacją motywu, pogrubienie, kursywę, podkreślenie, identyfikator formatu liczb, kierunkowe dodatnie i ujemne zasięgi paska, pozycję osi, rodzinę i indeks ikony — każdy odbiorca czyta ten sam rekord i żaden nie musi rozumieć wnętrza reguł. HotXLS używa tej jednej ścieżki dla eksportu HTML, eksportu PDF i interaktywnej przeglądarki, co jest jedynym praktycznym sposobem, by nie dopuścić do rozjechania się trzech rendererów. Wersja 2.210.0 podpięła to do TXLSWorkbookViewer, który buforuje jeden przygotowany ewaluator na aktywny arkusz i ponownie go używa przy przewijaniu, zaznaczaniu i przemalowywaniu, zwalniając go, gdy zmienia się skoroszyt albo arkusz — odbudowywanie migawki przy każdym Paint pokonałoby cały projekt zorientowany na czas konstrukcji. Ten cache to też powód, dla którego istnieje TXLSWorkbookViewer.RefreshConditionalFormats: migawka jest niemutowalna, więc jeśli mutujesz podłączony skoroszyt w miejscu, zbiorcze statystyki i rozwiązane progi są nieaktualne, dopóki go nie wywołasz
// 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, repaintCzego ewaluator za Ciebie nie zrobi
Warto jasno wskazać trzy granice. Klasyczne jednokomórkowe TCondFormatRule.Evaluate i ewaluator na poziomie arkusza TXLSXConditionalFormatEvaluator to różne powierzchnie o różnych możliwościach, a jednokomórkowa celowo odmawia obsługi rodzin agregujących i wizualnych zamiast je przybliżać — jeśli potrzebujesz Top/Bottom albo skali kolorów, zbuduj ewaluator. Względne okresy dat zależą od zegara maszyny w momencie oceny, więc reguła timePeriod renderuje się inaczej w PDF-ie wygenerowanym dziś i takim wygenerowanym za tydzień, co jest zachowaniem poprawnym, a mimo to czekającym zgłoszeniem wsparcia, jeśli Twoje archiwum ma być stabilne bajtowo. Trzecia granica jest raczej gramatyczna niż techniczna: gramatyka formuły formatowania warunkowego zabrania strukturalnych referencji tabelarycznych, więc reguła nie może zaadresować kolumny tabeli po nazwie tak, jak może to zrobić formuła arkusza, i jest to ograniczenie formatu, nie implementacji
Jeśli budujesz wyjście raportu, pipeline eksportu albo własną siatkę, która musi zgadzać się z Excelem komórka po komórce, ten sam rozwiązany wynik napędza też własną siatkę arkusza VCL opisaną gdzie indziej na tym blogu. Pełna dokumentacja API, model reguł i wersje próbne dla komponentu arkusza Delphi HotXLS są dostępne na stronie produktu