HotXLS: formatowanie warunkowe i tekst formatowany

Reguła formatowania warunkowego w OOXML to dwie osobne rzeczy noszące jedną nazwę. Warunek (porównanie, formuła, dopasowanie tekstu) decyduje, które komórki się kwalifikują. Wygląd (rekord formatu różnicowego, dxf w terminologii ECMA-376) decyduje, jak te komórki wyglądają. Okno dialogowe Excela ukrywa ten szew, każąc ci wypełnić oba naraz. HotXLS tego nie robi. Utwórz regułę cellIs z Delphi i pomiń styl, a reguła jest poprawna, zakres się zgadza, formuła daje prawdę dokładnie na właściwych komórkach i nic nie zmienia koloru, bo instrukcja reguły brzmiała "prawda, nie maluj niczego". Ta luka między warunkiem a konsekwencją jest pierwszą rzeczą do ustawienia poprawnie i odpowiada za większość reguł, które wyglądają poprawnie w Menedżerze reguł, a jednak niczego nie podświetlają

HotXLS zapisuje formatowanie warunkowe natywnie zarówno do plików BIFF8 .xls, jak i OOXML .xlsx, i robi to samo dla przebiegów tekstu formatowanego oraz dla modelu stylów komórek trzymanych w puli. Te trzy funkcje dzielą więcej instalacji, niż sugeruje płaska powierzchnia API, a miejsca, w których wynik odchodzi od zamiaru, to zwykle złącza między nimi

Warunek potrzebuje konsekwencji: styl dxf

W arkuszu XLSX reguły porównań pochodzą z AddConditionalFormat, które przyjmuje zakres, operator z TXLSXCfOperator oraz formułę lub literał, a potem zwraca indeks nowej reguły w kolekcji ConditionalFormats arkusza. Obiekt reguły pod tym indeksem wystawia właściwość Style i to tam mieszka podświetlenie. Ustaw na nim wypełnienie, a kwalifikujące się komórki wezmą to wypełnienie. Zostaw je nietknięte, a zbudowałeś niewidzialną regułę opisaną wyżej

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

    // Ujemne odchylenie: jasnoczerwone wypełnienie
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Zduplikowane identyfikatory zamówień są oznaczane tak samo
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Własna reguła formułowa: podświetl wiersze, gdzie wynik nie sięga 90% celu
    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;

Kolory są tutaj 32-bitowymi wartościami ARGB, więc $FFFFC7CE to excelowa "jasna czerwień", którą znasz z okna dialogowego, z w pełni nieprzezroczystym bajtem alfa siedzącym przed RGB. Każdy rodzaj reguły, który odpala się na warunku liczonym per komórka, ma ten sam kształt utwórz-potem-ostyluj. Dopasowania tekstu (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) zwracają indeks, który stylujesz potem, i tak samo robią AddCondFormatTop10, AddCondFormatAboveAverage oraz detektory pustek i błędów. Naucz się tego wzorca raz, a cała rodzina tekstowo-porównawcza zachowuje się tak samo

Paski danych, skale kolorów i zestawy ikon malują się same

Wizualne rodzaje reguł działają odwrotnie. Niosą swój wygląd wewnątrz definicji reguły i całkowicie ignorują właściwość Style. Przypisz wypełnienie do reguły paska danych, a nic się nie stanie, co czyta się jak błąd, dopóki taksonomia nie zaskoczy: AddCondFormatDataBar przyjmuje kolor paska jako bezpośredni argument, dwu- i trójpunktowe skale kolorów biorą kolory swoich punktów końcowych tak samo, a AddCondFormatIconSet wybiera jeden z 26 typów zestawów ikon, taki jak icsTrafficLights3. Nie ma tu osobnego rekordu stylu do zapomnienia, bo osobnego rekordu stylu nie ma w ogóle

Parametrami wartymi przemyślenia przy tych wywołaniach są kotwice wartości, typowane jako TXLSCfValueKind. Punkt końcowy paska albo skali może siedzieć na minimum lub maksimum zakresu, na literale liczbowym, na procencie albo percentylu, albo na wyniku formuły. Ustawienia domyślne, minimum i maksimum zakresu, sprawują się dobrze na uporządkowanych danych demonstracyjnych, a potem zdradzają cię na prawdziwych danych z wartościami odstającymi: jedna wystrzelona wartość rozciąga skalę i spłaszcza każdy inny pasek do kikuta. Kiedy pulpit ma być czytany między okresami, zakotwicz punkty końcowe na stałych liczbach albo percentylach, żeby pół paska w marcu znaczyło tę samą wielkość co pół paska w kwietniu. Pasek skalowany automatycznie jest porównywalny tylko z samym sobą

Zapisujący XLS obsługuje cztery rodzaje reguł, nie więcej

Starsza strona BIFF8 nie jest mniejszym lustrem strony XLSX; jest celowym podzbiorem. Fasada XLS potrafi utworzyć dokładnie cztery kształty reguł warunkowych — paski danych, dwukolorowe skale, trójkolorowe skale i zestawy ikon — emitowane do strumienia jako rekordy CF12. Nie ma API tworzenia dla reguł cellIs, wyrażeniowych ani tekstowych. Reguły tych rodzajów, które już żyją w otwieranym pliku, są czytane, zachowywane i zapisywane z powrotem bez zmian, więc otwarcie i ponowny zapis klienckiego .xls nigdy nie psuje formatowania, które w nim przyszło. Czego nie możesz zrobić, to wygenerować podświetlania progowego od zera do .xls. Wybór sprowadza się do udawania go zwykłymi wypełnieniami komórek wyliczonymi w kodzie albo do zrobienia z dostarczanego pliku formatu .xlsx, gdzie pełna rodzina reguł leży na stole

To ograniczenie do rozstrzygnięcia przed powstaniem warstwy danych, a nie po, bo zmienia decyzję o formacie pliku dla wszystkiego, co ma kształt pulpitu. Zespół, który wybrał .xls dla zgodności, a potem specyfikuje raport KPI z progami cellIs, wybrał dwie rzeczy, które do siebie nie pasują, a tańszą chwilą na zauważenie tego jest decyzja o formacie, a nie trzeci tydzień budowy

Piętrzenie reguł, priorytet i nakładające się zakresy

Prawdziwe pulpity rzadko mają jedną regułę na zakres. Kolumna odchyleń może nieść pasek danych dla wielkości, regułę cellIs dla twardego progu i regułę wyrażeniową na poziomie wiersza ponad obiema dla eskalacji. Każdy TXLSXConditionalFormat wystawia wartość Priority, a Excel rozstrzyga konkurujące reguły w kolejności priorytetów. Kiedy dwie reguły chcą pomalować tę samą komórkę, zwycięzcę wyznacza liczba, którą ustawiasz, a nie kolejność, w jakiej recenzent akurat przewija okno Menedżera reguł

Traktuj priorytet tak, jak program graficzny traktuje kolejność warstw. Przypisuj go świadomie wszędzie tam, gdzie dwie reguły mogą sięgnąć tych samych komórek, i zostawiaj odstępy między wartościami, żeby późniejsza reguła wskoczyła bez przenumerowywania reszty. Tam, gdzie reguły nie mogą kolidować, powiedzmy pasek danych zamknięty w kolumnie E i reguła tekstowa zamknięta w kolumnie G, kolejność tworzenia wystarczy, a priorytet nie jest wart uwagi. Wydaj tę uwagę na granice zakresów, bo kosztowne błędy prawie nigdy nie są tu inwersjami priorytetu. To zakresy takie jak B2:B200 w raporcie, który urósł do 350 wierszy, gdzie nieobjęty ogon renderuje się jako zwykłe komórki wyglądające dokładnie jak zdrowe dane. Wyprowadzaj każdy zakres reguły z tej samej wartości ostatecznej liczby wierszy, która napędza serie wykresów i zakresy walidacji gdzie indziej w skoroszycie, a ogon przestanie odpadać

Jeden nawyk weryfikacyjny zarabia na siebie. Po wygenerowaniu otwórz plik w Excelu, zaznacz sformatowany zakres i przejdź raz przez Menedżera reguł dla każdej zmiany szablonu. Formatowanie warunkowe to jeden z niewielu obszarów, gdzie jedynym miarodajnym rendererem jest aplikacja konsumująca plik, więc test jednostkowy na XML dowodzi, że reguła została zapisana, a nie że Excel maluje ją tak, jak chciałeś. Minuta oglądania zamyka tę lukę

Tekst formatowany: wiele formatów w jednej komórce

Komórka z tekstem formatowanym w modelu XLSX trzyma listę przebiegów, gdzie każdy przebieg to fragment tekstu wraz z własnymi atrybutami czcionki. Listę budujesz z boku jako obiekt TXLSXRichText, dodajesz do niej przebiegi, a potem doczepiasz całość do komórki. Reguła własności jest tą częścią, która gryzie. Przypisanie do Cell.RichText przekazuje własność tego obiektu komórce, a komórka zwalnia go podczas własnego niszczenia. Zwolnij go dodatkowo samodzielnie, a masz podwójne zwolnienie — takie, które milczy przez cały przebieg, który je spowodował, i wychodzi jako awaria gdzieś zupełnie indziej dużo później

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;   // własność przechodzi na komórkę: nie wywołuj Free
end;

Jawne ColorIsAuto := False nie jest opcjonalną ozdobą. Przebieg niesie flagę koloru automatycznego, a przypisanie koloru jest respektowane dopiero po wyczyszczeniu tej flagi. Ustaw Color i zapomnij o ColorIsAuto, a przebieg wyjdzie pogrubiony, ale uparcie czarny, bez żadnego błędu wskazującego przyczynę. Przebiegi wspierają też przekreślenie, warianty podkreślenia oraz wyrównanie pionowe dla indeksu górnego i dolnego, podczas gdy PlainText spłaszcza całą listę z powrotem do jednego ciągu, gdy potrzebujesz wyeksportować albo porównać treść tekstu

Tekst formatowany na poziomie komórki jest wyłącznie w XLSX. Fasada XLS nie ma publicznego API do jego zapisu, choć przebiegi są tam dostępne na komentarzach i polach tekstowych przez TextRuns, a ciągi formatowane wczytane z istniejącego .xls przeżywają podróż w obie strony nietknięte. Ciążenie jest to samo co przy formatowaniu warunkowym: wszystko, co miesza formaty wewnątrz komórki, należy do zapisującego XLSX

Pula stylów i błąd o jeden, który trafia do wydania

Zwykłe stylowanie komórek w modelu XLSX przechodzi przez kolekcje puli na skoroszycie. Fonts.Add, Fills.AddSolid i Borders.Add rejestrują definicję i zwracają jej indeks w puli. Te indeksy liczą się od zera. Właściwości po stronie komórki, które je konsumują, takie jak FontIndex, rezerwują 0 dla wartości domyślnej, więc wartość przypisywana komórce to indeks puli plus jeden:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // indeks puli, od zera
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // indeks komórki, od jedynki

Pomiń + 1, a każdy nagłówek spada do czcionki domyślnej. Nie ma wyjątku ani ostrzeżenia, tylko skoroszyt, który wygląda, jakby nikt go nie ostylował. Błąd drugiego rzędu chowa się w pętli: wywoływanie Fonts.Add raz na wiersz. Identyczne definicje czcionek są deduplikowane, więc plik nie jest uszkodzony, ale praca jest zmarnowana, a w szczególności pula wyrównań oddaje świeży obiekt przy każdym wywołaniu, zamiast składać duplikaty. Zbuduj tę garstkę stylów raz przed pętlą i wykorzystuj ponownie ich indeksy. Na raportach o stu tysiącach wierszy ta jedna zmiana jest jedną z dźwigni omówionych w artykule strojenie wydajności dużych skoroszytów w HotXLS. Kiedy potrzebujesz tylko gotowego wyglądu semantycznego, obie fasady wystawiają ApplyBuiltinStyle na zakresach, co mapuje się na wbudowane style Excela Good, Bad, Neutral i akcentowe, bez dotykania puli w ogóle

Formatowanie warunkowe, tekst formatowany i style z puli to ostatnia mila raportu, nakładana po ustaleniu modelu danych i układu, a te wcześniejsze etapy są tematem artykułu generowanie raportów na podstawie szablonów w HotXLS. Pełna referencja reguł, przebiegów i stylów mieszka na stronie produktu HotXLS Delphi Component