Formatare condiționată, text îmbogățit și stiluri de celulă cu HotXLS în Delphi

O regulă de formatare condiționată în OOXML este de fapt două lucruri separate care poartă un singur nume. Condiția (o comparație, o formulă, o potrivire de text) decide ce celule se califică. Aspectul (o înregistrare de format diferențial, dxf în termenii ECMA-376) decide cum arată celulele acelea. Dialogul din Excel ascunde cusătura punându-te să le completezi pe amândouă deodată. HotXLS nu o ascunde. Creezi din Delphi o regulă cellIs și sari peste stil, iar regula este validă, intervalul este corect, formula se evaluează la adevărat exact pe celulele potrivite și nimic nu își schimbă culoarea, pentru că instrucțiunea regulii a fost „adevărat, nu picta nimic”. Golul acela dintre condiție și consecință este primul lucru care trebuie nimerit și explică majoritatea regulilor care arată corect în Manage Rules, dar nu evidențiază nimic

HotXLS scrie formatarea condiționată nativ atât în fișiere BIFF8 .xls, cât și în fișiere OOXML .xlsx, iar la fel face și cu segmentele de text îmbogățit și cu un model de stiluri de celulă ținute într-un fond comun. Cele trei funcționalități împart mai mult cablaj decât lasă să se vadă suprafața plată a API-ului, iar locurile în care rezultatul se abate de la intenție sunt de obicei încheieturile dintre ele

O condiție are nevoie de o consecință: stilul dxf

Pe foaia de calcul XLSX, regulile de comparație vin din AddConditionalFormat, care primește un interval, un operator din TXLSXCfOperator și o formulă sau un literal, apoi întoarce indexul noii reguli din colecția ConditionalFormats a foii. Obiectul regulă de la indexul acela expune o proprietate Style, iar acolo stă evidențierea. Pune-i o umplere și celulele care se califică preiau umplerea. Las-o neatinsă și ai construit regula invizibilă descrisă mai sus

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

    // Abatere negativă: umplere roșu deschis
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // ID-urile de comandă duplicate sunt semnalate la fel
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Regulă cu formulă proprie: evidențiază rândurile unde realizatul ratează 90% din țintă
    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;

Culorile de aici sunt valori ARGB pe 32 de biți, așa că $FFFFC7CE este „roșul deschis” pe care îl știi din dialog, cu un octet alfa complet opac așezat în fața RGB-ului. Fiecare fel de regulă care se declanșează pe o condiție per celulă urmează aceeași formă, creează-apoi-stilizează. Potrivitoarele de text (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) întorc un index pe care îl stilizezi după aceea, la fel fac și AddCondFormatTop10, AddCondFormatAboveAverage și detectoarele de celule goale și de erori. Învață tiparul o dată și toată familia de comparații și de text se comportă la fel

Barele de date, scalele de culoare și seturile de pictograme se pictează singure

Felurile vizuale de reguli lucrează invers. Își poartă aspectul în interiorul definiției regulii și ignoră complet proprietatea Style. Atribuie o umplere unei reguli de bară de date și nu se întâmplă nimic, ceea ce pare o eroare până când se așază taxonomia: AddCondFormatDataBar primește culoarea barei ca argument direct, scalele de culoare cu două și cu trei puncte își primesc culorile de capăt în același fel, iar AddCondFormatIconSet alege unul dintre cele 26 de tipuri de seturi de pictograme, precum icsTrafficLights3. Aici nu există nicio înregistrare de stil separată pe care să o uiți, pentru că nu există deloc o înregistrare de stil separată

Parametrii la care merită să te gândești la aceste apeluri sunt ancorele de valoare, tipizate ca TXLSCfValueKind. Capătul unei bare sau al unei scale poate sta la minimul sau la maximul intervalului, la un număr literal, la un procent sau la o percentilă ori la rezultatul unei formule. Valorile implicite, minimul și maximul intervalului, se poartă frumos pe date de demonstrație îngrijite și te trădează apoi pe date reale, cu valori aberante: o singură valoare scăpată de sub control întinde scala și turtește orice altă bară până la un ciot. Când un tablou de bord este menit să fie citit de-a lungul mai multor perioade, ancorează în schimb capetele la numere fixe sau la percentile, astfel încât jumătate de bară în martie să însemne aceeași cantitate ca jumătate de bară în aprilie. O bară scalată automat este comparabilă doar cu ea însăși

Scriitorul XLS acoperă patru feluri de reguli, nu mai multe

Partea BIFF8, moștenită din trecut, nu este o oglindă mai mică a părții XLSX; este un subset deliberat. Fațada XLS poate crea exact patru forme de reguli condiționate, bare de date, scale cu două culori, scale cu trei culori și seturi de pictograme, emise ca înregistrări CF12 în flux. Nu are niciun API de creare pentru reguli cellIs, de expresie sau de text. Regulile de felul acesta care există deja într-un fișier pe care îl deschizi sunt citite, păstrate și scrise înapoi neschimbate, așa că deschiderea și resalvarea unui .xls al clientului nu strică niciodată formatarea pe care o avea. Ce nu poți face este să generezi de la zero evidențiere pe praguri într-un .xls. Variantele de acolo sunt fie să o mimezi cu umpleri obișnuite de celulă calculate în cod, fie să faci din livrabil un .xlsx, unde întreaga familie de reguli îți stă la dispoziție

Aceasta este o constrângere de rezolvat înainte să existe stratul de date, nu după, pentru că schimbă decizia de format de fișier pentru orice are formă de tablou de bord. O echipă care a ales .xls pentru compatibilitate și apoi specifică un raport de KPI cu praguri cellIs a ales două lucruri care nu se potrivesc, iar momentul mai ieftin pentru a observa asta este la decizia de format, nu după trei săptămâni de construcție

Stivuirea regulilor, prioritatea și intervalele suprapuse

Tablourile de bord reale rulează rareori o singură regulă pe interval. O coloană de abateri ar putea purta o bară de date pentru mărime, o regulă cellIs pentru pragul dur și o regulă de expresie la nivel de rând deasupra amândurora, pentru escaladări. Fiecare TXLSXConditionalFormat expune o valoare Priority, iar Excel rezolvă regulile concurente în ordinea priorității. Când două reguli vor să picteze aceeași celulă, câștigătoarea este hotărâtă de un număr stabilit de tine, nu de ordinea în care se nimerește să deruleze un recenzent prin dialogul Manage Rules

Tratează prioritatea așa cum tratează un program de desen ordinea pe axa z. Atribuie-o intenționat oriunde două reguli pot ajunge la aceleași celule și lasă goluri între valori, ca o regulă ulterioară să se strecoare fără să le renumerotezi pe celelalte. Acolo unde regulile nu se pot ciocni, să zicem o bară de date limitată la coloana E și o regulă de text limitată la coloana G, ordinea creării este suficientă, iar prioritatea nu merită atenția. Cheltuiește atenția aceea pe limitele intervalelor, pentru că erorile scumpe de aici aproape niciodată nu sunt inversiuni de prioritate. Sunt intervale precum B2:B200 pe un raport care a crescut la 350 de rânduri, unde coada neacoperită se afișează ca niște celule simple care arată exact ca datele sănătoase. Derivă fiecare interval de regulă din aceeași valoare de număr final de rânduri care conduce seriile de grafic și intervalele de validare din restul registrului de lucru, iar coada nu mai cade pe dinafară

Un obicei de verificare își merită locul. După generare, deschide fișierul în Excel, selectează intervalul formatat și parcurge o dată Manage Rules la fiecare schimbare de șablon. Formatarea condiționată este una dintre puținele zone în care singurul randator care contează este chiar aplicația ce consumă fișierul, așa că un test unitar peste XML dovedește că regula a fost scrisă, nu că Excel o pictează așa cum ai vrut tu. Un minut de privit cu ochiul închide golul acela

Text îmbogățit: mai multe formate în interiorul unei singure celule

O celulă cu text îmbogățit în modelul XLSX ține o listă de segmente, unde fiecare segment este o porțiune de text plus propriile atribute de font. Construiești lista deoparte, ca obiect TXLSXRichText, îi adaugi segmente, apoi atașezi tot ansamblul unei celule. Regula de proprietate este partea care mușcă. Atribuirea către Cell.RichText predă celulei proprietatea asupra acelui obiect, iar celula îl eliberează în timpul propriei distrugeri. Eliberează-l și tu și ai o dublă eliberare, genul care tace pe toată durata rulării care a provocat-o și iese la suprafață mult mai târziu, ca o prăbușire în cu totul altă parte

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;   // proprietatea trece la celulă: nu apela Free
end;

ColorIsAuto := False scris explicit nu este o decorațiune opțională. Un segment poartă un indicator de culoare automată, iar o atribuire de culoare este onorată abia după ce indicatorul acela este șters. Setează Color și uită de ColorIsAuto, iar segmentul iese îngroșat, dar încăpățânat de negru, fără nicio eroare care să arate spre cauză. Segmentele acceptă și tăierea cu linie, variantele de subliniere și alinierea verticală pentru exponent și indice, în timp ce PlainText aplatizează întreaga listă înapoi la un singur șir atunci când trebuie să exporți sau să compari conținutul de text

Textul îmbogățit la nivel de celulă există doar în XLSX. Fațada XLS nu are niciun API public pentru a-l scrie, deși segmentele sunt disponibile acolo pe comentarii și pe casete de text prin TextRuns, iar șirurile îmbogățite citite dintr-un .xls existent supraviețuiesc intacte unui dus-întors. Îndemnul este același ca la formatarea condiționată: orice amestecă formate în interiorul unei celule își are locul în scriitorul XLSX

Fondul de stiluri și decalajul cu o unitate care ajunge în producție

Stilizarea obișnuită a celulelor în modelul XLSX trece prin colecții comune de pe registrul de lucru. Fonts.Add, Fills.AddSolid și Borders.Add înregistrează fiecare câte o definiție și îi întorc indexul din fond. Indecșii aceia încep de la 0. Proprietățile din partea celulei care îi consumă, precum FontIndex, rezervă 0 pentru „implicit”, așa că valoarea pe care o atribui unei celule este indexul din fond plus unu:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // index din fond, de la 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // index de celulă, de la 1

Renunță la + 1 și fiecare antet cade înapoi la fontul implicit. Nu există nicio excepție și niciun avertisment, doar un registru de lucru care arată ca și cum nu l-ar fi stilizat nimeni. Greșeala de ordinul doi se ascunde în buclă: apelarea lui Fonts.Add o dată pentru fiecare rând. Definițiile de font identice sunt deduplicate, așa că fișierul nu se strică, dar munca se irosește, iar fondul de aliniere întoarce în plus un obiect nou la fiecare apel, în loc să plieze duplicatele. Construiește o singură dată cele câteva stiluri, înainte de buclă, și refolosește-le indecșii. Pe rapoarte de o sută de mii de rânduri, schimbarea aceea singură este una dintre pârghiile tratate în reglarea performanței pentru registre de lucru mari în HotXLS. Când ai nevoie doar de un aspect semantic de serie, ambele fațade expun ApplyBuiltinStyle pe intervale, care se mapează pe stilurile Good, Bad, Neutral și pe cele de accent din Excel, fără ca tu să atingi deloc fondurile

Formatarea condiționată, textul îmbogățit și stilurile din fondul comun sunt ultima milă a unui raport, aplicate după ce modelul de date și aspectul sunt așezate, iar etapele acelea de dinainte fac subiectul articolului despre generarea de rapoarte pe bază de șabloane cu HotXLS. Referința completă pentru reguli, segmente și stiluri se află pe pagina de produs HotXLS Delphi Component