„HotXLS“ sąlyginis formatavimas ir raiškusis tekstas

Sąlyginio formatavimo taisyklė OOXML formate yra du skirtingi dalykai, turintys vieną pavadinimą. Sąlyga (palyginimas, formulė, teksto atitikmuo) nusprendžia, kurie langeliai atitinka reikalavimus. Išvaizda (diferencinis formato įrašas, dxf pagal ECMA-376 specifikaciją) nusprendžia, kaip tie langeliai atrodys. „Excel“ dialogo langas paslepia šią ribą priversdamas užpildyti abu nustatymus vienu metu. „HotXLS“ to nedaro. Jei sukursite cellIs taisyklę „Delphi“ programoje ir nenustatysite stiliaus, taisyklė bus galiojanti, rėžis teisingas, formulė bus teisinga tiksliai tiems langeliams, tačiau spalva nepasikeis, nes taisyklės nurodymas buvo „jei tiesa, nieko nepiešti“. Šis skirtumas tarp sąlygos ir pasekmės yra pirmoji taisyklė, kurią reikia suprasti, ir būtent ji lemia, kodėl taisyklės, kurios atrodo teisingos valdymo dialoge, nieko nepažymi darbalapyje

„HotXLS“ įrašo sąlyginį formatavimą tiesiogiai į BIFF8 „.xls“ ir OOXML „.xlsx“ failus. Ji taip pat palaiko raiškiojo teksto fragmentus (angl. rich text runs) ir bendrą langelių stilių modelį. Šios trys funkcijos turi daugiau bendro ryšio, nei atrodo iš pirmo žvilgsnio, o vietos, kur galutinis rezultatas skiriasi nuo planuoto, dažniausiai yra jų sujungimo taškai

Sąlygai reikia pasekmės: dxf stilius

XLSX darbalapyje palyginimo taisyklės sukuriamos naudojant AddConditionalFormat, kuri priima rėžį, operatorių iš TXLSXCfOperator ir formulę arba tiesioginę reikšmę, o po to grąžina naujos taisyklės indeksą darbalapio ConditionalFormats kolekcijoje. Taisyklės objektas pagal šį indeksą turi savybę Style, kurioje ir nustatomas spalvinis žymėjimas. Nustatykite jai užpildymą, ir sąlygą atitinkantys langeliai bus nuspalvinti. Nepalieskite jos, ir sukursite aukščiau aprašytą nematomą taisyklę

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

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of target
    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;

Spalvos čia nurodomos kaip 32 bitų ARGB reikšmės, todėl $FFFFC7CE atitinka standartinę šviesiai raudoną „Excel“ spalvą iš dialogo lango, kur pirmasis baitas nurodo pilną nepermatomumą (Alpha), o kiti trys – RGB. Kiekviena taisyklė, kuri veikia pagal atskiro langelio sąlygą, vadovaujasi tuo pačiu sukūrimo ir stilizavimo modeliu. Teksto paieškos taisyklės (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) grąžina indeksą, kurį vėliau stilizuojate. Taip pat veikia ir AddCondFormatTop10, AddCondFormatAboveAverage bei tuščių langelių ar klaidų aptikimo funkcijos. Įsiminkite šį šabloną vieną kartą, ir visa palyginimų bei tekstų taisyklių šeima veiks vienodai

Duomenų stulpeliai, spalvų skalės ir piktogramų rinkiniai nusispalvina patys

Vizualinio tipo taisyklės veikia priešingai. Jos turi savo išvaizdos nustatymus pačiame taisyklės apibrėžime ir visiškai nepaiso savybės Style. Priskirkite užpildymo stilių duomenų stulpelio taisyklei (angl. data bar) ir nieko neįvyks. Tai gali atrodyti kaip klaida, kol nesuprasite klasifikacijos: AddCondFormatDataBar priima stulpelio spalvą kaip tiesioginį argumentą, dviejų ir trijų taškų spalvų skalės priima savo spalvas taip pat, o AddCondFormatIconSet parenka vieną iš 26 piktogramų rinkinių tipų (pavyzdžiui, icsTrafficLights3). Čia nereikia nustatinėti atskiro stiliaus įrašo, nes jo tiesiog nėra

Šiuose iškvietimuose svarbu apgalvoti reikšmių inkarus, kurie nurodomi kaip TXLSCfValueKind. Stulpelio ar skalės pabaigos taškas gali būti rėžio minimumas arba maksimumas, konkretus skaičius, procentas, procentilis arba formulės rezultatas. Numatytoji minimumo ir maksimumo parinktis puikiai veikia su tvarkingais demonstraciniais duomenimis, tačiau gali suklaidinti realiuose duomenyse su išskirtimis (angl. outliers): viena neįprastai didelė reikšmė ištemps skalę ir sumažins visus kitus stulpelius iki mažų fragmentų. Kai prietaisų skydelis turi būti lyginamas tarp skirtingų laikotarpių, susiekite galinius taškus su konkrečiais skaičiais arba procentiliais, kad pusė stulpelio kovo mėnesį reikštų tą patį kiekį kaip ir pusė stulpelio balandį. Automatiškai pritaikyta taisyklė gali būti lyginama tik pati su savimi

XLS rašyklė palaiko tik keturis taisyklių tipus

Senasis BIFF8 formatas nėra mažesnis XLSX veidrodis; tai yra sąmoningai apribotas poaibis. XLS sąsaja gali sukurti tik keturis sąlyginio formatavimo tipus: duomenų stulpelius, dviejų spalvų skales, trijų spalvų skales ir piktogramų rinkinius, įrašomus kaip CF12 įrašai į srautą. Ji neturi funkcijų kurti cellIs, sąlygų ar teksto taisykles iš kodo. Tačiau tokios taisyklės, kurios jau yra darbalapyje atidarant failą, bus perskaitytos, išsaugotos ir įrašytas atgal be pakeitimų. Taigi, atidarant ir iš naujo išsaugant kliento XLS failą, jo formatavimas nebus sugadintas. Ko negalite padaryti, tai sukurti naujų slenksčių taisyklių XLS faile iš kodo. Tokiu atveju turite imituoti formatavimą nuspalvindami langelius pačiame kode arba naudoti XLSX formatą, kur yra palaikoma visa taisyklių šeima

Šį apribojimą svarbu įvertinti prieš pradedant kurti duomenų sluoksnį, nes tai lemia failo formato pasirinkimą ataskaitoms. Komanda, pasirinkusi XLS formatą dėl suderinamumo ir suplanavusi KPI ataskaitą su cellIs taisyklėmis, pasirenka du nesuderinamus dalykus, ir geriau tai pastebėti iškart, o ne po trijų savaičių darbo

Taisyklių eiliškumas, prioritetai ir persidengiantys rėžiai

Realūs prietaisų skydeliai retai naudoja tik vieną taisyklę tam pačiam rėžiui. Nuokrypio stulpelis gali turėti duomenų stulpelį dydžiui parodyti, cellIs taisyklę ribinei reikšmei ir eilutės lygio formulės taisyklę virš jų svarbiems atvejams pažymėti. Kiekvienas TXLSXConditionalFormat objektas turi savybę Priority, o „Excel“ sprendžia taisyklių taikymą pagal šį prioritetą. Kai dvi taisyklės bando nuspalvinti tą patį langelį, laimėtojas nustatomas pagal jūsų nurodytą skaičių, o ne pagal tai, kokia tvarka vartotojas jas mato taisyklų valdymo dialoge

Vertinkite prioritetą taip pat, kaip grafikos redaktoriai vertina sluoksnių išdėstymą (Z-tvarką). Nustatykite jį sąmoningai ten, kur dvi taisyklės gali pasiekti tuos pačius langelius, ir palikite tarpus tarp reikšmių, kad vėliau galėtumėte įterpti naują taisyklę nepernumeruodami visų kitų. Ten, kur taisyklės negali susidurti (pavyzdžiui, duomenų stulpelis stulpelyje E ir teksto taisyklė stulpelyje G), pakanka sukūrimo eilės ir prioriteto nustatinėti nereikia. Daugiau dėmesio skirkite rėžių riboms, nes brangiausios klaidos čia yra susijusios ne su prioritetų supainiojimu, o su neteisingai nurodytais rėžiais, pavyzdžiui, B2:B200 ataskaitoje, kuri išaugo iki 350 eilučių. Tuomet nepadengta dalis bus rodoma be formatavimo, kas vizualiai atrodys kaip teisingi duomenys. Susiekite kiekvieną taisyklės rėžį su galutiniu eilučių skaičiumi, kuris valdo ir kitus darbalapio elementus, ir išvengsite šios problemos

Vienas naudingas įprotis – po failo sugeneravimo atidaryti jį „Excel“ programoje, pasirinkti suformatuotą rėžį ir patikrinti taisykles valdymo dialoge po kiekvieno šablono pakeitimo. Sąlyginis formatavimas yra viena iš sričių, kur vienintelis teisingas atvaizdavimo vertintojas yra pati darbalapių programa. XML patikra patvirtina tik tai, kad taisyklė buvo įrašyta, bet ne tai, kad „Excel“ ją atvaizduoja teisingai. Minutė vizualinio patikrinimo išsprendžia šią problemą

Raiškusis tekstas: keli formatai viename langelyje

Raiškiojo teksto langelis XLSX modelyje turi fragmentų (srautų) sąrašą, kur kiekvienas fragmentas yra tekstas su savo šrifto atributais. Jūs sukuriate šį sąrašą kaip atskirą TXLSXRichText objektą, pridedate prie jo fragmentus ir priskiriate visą objektą langeliui. Čia svarbi nuosavybės taisyklė: priskyrus objektą Cell.RichText savybei, jo valdymas pereina langeliui, ir pats langelis jį atlaisvina sunaikinimo metu. Jei pabandysite jį atlaisvinti rankiniu būdu, gausite double-free klaidą, kuri gali nesukelti problemų iškart, bet sugeneruos programos lūžį kitoje nesusijusioje vietoje vėliau

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;   // ownership moves to the cell: do not Free
end;

Nustatymas ColorIsAuto := False yra privalomas. Kiekvienas fragmentas turi automatinės spalvos vėliavėlę, ir spalvos priskyrimas bus pritaikytas tik tada, kai ši vėliavėlė bus išjungta. Priskyrus spalvą ir pamiršus išjungti šią vėliavėlę, tekstas bus paryškintas, bet liks juodas be jokių klaidų pranešimų. Raiškusis tekstas taip pat palaiko perbraukimą, pabraukimo variantus ir vertikalų lygiavimą viršutiniam bei apatiniam indeksui, o PlainText leidžia supaprastinti visą sąrašą iki vienos teksto eilutės eksportui ar palyginimui

Langelių raiškusis tekstas palaikomas tik XLSX formate. XLS sąsaja neturi viešo metodo jam rašyti, nors fragmentai ten yra prieinami komentaruose ir teksto laukeliuose per TextRuns savybę, o esami raiškiojo teksto duomenys iš XLS failo išlieka po apdorojimo. Taisyklė išlieka ta pati: viskas, kas susiję su skirtingais formatais langelio viduje, turi būti atliekama XLSX formate

Stilių fondas ir indeksavimo taisyklė

Langelių stiliai XLSX modelyje valdomi per bendras knygos kolekcijas. Funkcijos Fonts.Add, Fills.AddSolid ir Borders.Add užregistruoja stiliaus apibrėžimą ir grąžina jo indeksą fonde. Stiliai prasideda nuo 0. Tačiau langelių savybės, kurios naudoja šiuos indeksus (pavyzdžiui, FontIndex), reikšmę 0 laiko numatytuoju nustatymu. Todėl indeksas, kurį priskiriate langeliui, turi būti fondo indeksas plius vienas:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Pamiršus pridėti + 1, visi antraščių šriftai sugrįš į numatytąjį stilių. Nebus jokių klaidų ar perspėjimų, tiesiog jūsų darbalapis atrodys nestilizuotas. Kita klaida yra iškviesti Fonts.Add kiekvienai eilutei. Identiški šriftų apibrėžimai yra apjungiami (deduplikuojami), todėl tai veltui naudoja resursus, o lygiavimų fondas kiekvieno iškvietimo metu sukuria naują objektą. Sukurkite stilius vieną kartą prieš ciklą ir naudokite jų indeksus. Didelių ataskaitų atveju tai yra vienas iš našumo didinimo būdų, aprašytų straipsnyje didelių darbalapių našumo derinimas su HotXLS. Jei jums reikia tik standartinio stiliaus, abi sąsajos palaiko ApplyBuiltinStyle funkciją rėžiams, kuri pritaiko „Excel“ numatytuosius stilius (Good, Bad, Neutral ir kt.) neliečiant stilių fondų rankiniu būdu

Sąlyginis formatavimas, raiškusis tekstas ir stiliai yra paskutinis ataskaitos kūrimo etapas, atliekamas suderinus duomenų modelį ir darbalapio struktūrą. Ankstesni etapai aprašyti straipsnyje ataskaitų generavimas pagal šablonus su HotXLS. Pilną taisyklių, fragmentų ir stilių aprašymą rasite „HotXLS Component“ produkto puslapyje HotXLS Component