Excel sąlyginio formatavimo vertinimas Delphi su HotXLS

HotXLS — natyvus skaičiuoklės komponentas Delphi ir C++Builder platformoms, ir nuo versijos 2.209.0 jis gali atsakyti į klausimą, kurį Excel paprastai laiko sau: būtent šiai ląstelei, kurios sąlyginio formatavimo taisyklės suveikia, ir kokį užpildymą, šriftą, duomenų juostą ar piktogramą jos duoda. Šis atsakymas — tai, ko jums reikia tą akimirką, kai jūsų išvestis yra HTML ataskaita, PDF, ar tinklelis, kurį dažote patys

Tai — kitokia problema nei taisyklių kūrimas. Du ankstesni straipsniai apima autorystės pusę: sąlyginis formatavimas ir turtingo teksto stiliai nagrinėja taisyklių ir diferencinių formatų pridėjimą prie intervalo, o inkaruotų sąlyginių formatų skaidymas nagrinėja, kas atsitinka taisyklės intervalui, kai eilutės ir stulpeliai įterpiami ar ištrinami. Abu — struktūriniai. Šis straipsnis — apie semantiką: turint darbaknygę, jau nešančią taisykles, apskaičiuoti paryškinimą

Kodėl failo formatas nepasako, kurios ląstelės užsidega

Trumpas atsakymas — ECMA-376 ir ISO 29500-1 apibrėžia saugojimą, ne vertinimą. conditionalFormatting elementas (§18.3.1.18) neša sqref ir cfRule vaikų sąrašą (§18.3.1.10), o kiekviena taisyklė neša type, pasirenkamą operator, priority, stopIfTrue vėliavėlę, vieną ar du formula vaikus, ir vizualinėms šeimoms — cfvo ribų rinkinį. Kiekvienas iš jų ištikimai apibūdina, ką vartotojas sukonfigūravo, ir nė vienas iš jų nėra algoritmas. Pusei taisyklių tipų šis atotrūkis nesvarbus: cellIs su operator="greaterThan" reiškia didesnis nei, o containsText reiškia, kad poeilutė yra. Atotrūkis atsiveria suminėse šeimose. top10 taisyklė su rank="10" ir percent="1" per 27 užpildytas skaitines ląsteles paryškina kiek ląstelių? Du kablelis septyni nėra skaičius. Apvalinti į viršų, apačią ar visgi? Specifikacija tyli, o pasirinkus neteisingai, jūsų PDF nesutars su darbaknyge, kurią klientas turi atvertą šalia

Vienos ląstelės taisyklės ir kur sustoja TCondFormatRule.Evaluate

HotXLS pirmiausia ėmėsi pigesnės pusės. TCondFormatRule.Evaluate modulyje lxCondFormat.pas, pridėtas versijoje 2.199.0, atsako, ar viena taisyklė suveikia vienai ląstelei, nieko nežinodama apie likusią intervalo dalį. Ji tvarko aštuonis BIFF palyginimo operatorius už cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), laisvos formos expression taisykles, vertinamas ląstelėje, kad santykinės nuorodos teisingai perkeltų bazę, keturis teksto predikatus bei tuščių ir klaidų predikatus. Ribos ateina iš FFormula1 ir FFormula2, išsprendžiamos per TXLSCalculator.GetRangeValue ląstelės pozicijoje, o apversti tarp ribų sukeičiami vietomis, ne atmetami

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;

Sąžininga šio metodo dalis — tai, ko jis atsisako spėti. top10, aboveAverage, belowAverage, duplicateValues ir uniqueValues grąžina False, ne todėl, kad jos sudėtingos, o todėl, kad jos neišsprendžiamos iš vienos ląstelės — kiekvienai iš jų reikia statistikos per visą domeną. Keturios vizualinės šeimos, dataBar, colorScale2, colorScale3 ir iconSet, grąžina False dėl kitos priežasties: jos apskritai niekada negeneruoja loginio rezultato, jos generuoja atvaizdavimo naudingą krovinį, o loginis grąžinamo tipas joms — netinkama forma

Kaip lapo lygio vertintuvas išvengia pakartotinio lapo skenavimo?

Apskaičiuodamas kiekvieną bendrą dydį vieną kartą, konstravimo metu, ir daugiau niekada. TXLSXConditionalFormatEvaluator modulyje lxHandleX.pas yra nekintanti vieno darbalapio nuotrauka, sukurta per TXLSXWorksheet.CreateConditionalFormatEvaluator, ir visas jos dizainas — gynyba nuo naivios realizacijos, kur kiekviena nudažyta ląstelė sukelia pilną intervalo skenavimą

Konstruktoriuje vyksta keturi dalykai. Kiekvienas atskiras kelių sričių sqref analizuojamas lygiai vieną kartą į TXlsxCfRangeSnapshot, todėl dešimt taisyklių, dalinančiųsi vienu intervalu, dalijasi vienu analizavimu ir vienu statistikos etapu. Tas etapas nuoseklia eiga apskaičiuoja vidurkį, populiacijos nuokrypį, minimumą ir maksimumą per užpildytas ląsteles vienu ėjimu, ir tik palaiko surikiuotą skaitinį masyvą, kai Top/Bottom ar procentilio taisyklei iš tikrųjų reikia rikiavimo statistikos. Dublikatų ir unikalūs raktai sukuriami Unicode-saugiai ir masiniu būdu surūšiuojami vieną kartą, o ne kiekvienai paieškai. Tada eilučių ašis supjaustoma juostomis kiekvienoje srities riboje, todėl EvaluateCell dvejetainiu paieškos būdu suranda juostą ir lanko tik taisykles, kurių intervalai apskritai gali pasiekti tą eilutę

Ketvirtasis dalykas — svarbiausias didele apimtimi. Santykinė taisyklės formulė, tokia kaip =A1>AVERAGE($A$1:$A$100), kiekvienoje domeno ląstelėje reiškia kažką kitą, o akivaizdi realizacija sukompiliuotų šviežią sintaksės medį kiekvienai ląstelei. TXlsxCfRulePlan ją sukompiliuoja vieną kartą ir iš naujo vertina tą patį medį per grįžtamus koordinačių poslinkius, kas išsaugo Excel inkaro elgesį be sintaksės medžio paskirstymo kiekvienai ląstelei. Taisyklės tada sluoksniuojamos pagal priority, o atitikimas taisyklei, kurios StopIfTrue nustatytas, nutraukia ciklą lygiai taip, kaip Excel trumpina jungtis

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;

Kaip Excel iš tikrųjų apvalina Top 10 procentų taisyklę?

Ji apvalina žemyn, minimaliai vienetu, ir įtraukia lygiavertes reikšmes ties riba. Tai niekur nėra parašyta ISO 29500-1 — tai buvo pritvirtinta zonduojant Excel 16 su ranka sukurtomis darbaknygėmis ir stebint, kurias ląsteles programa paryškino. HotXLS realizuoja būtent tai: rango skaičius yra Floor(Count * Min(Rank, 100) / 100), pakeliamas iki 1, kai jis nusileidžia iki nulio, apribojamas iki užpildyto skaičiaus, o riba tada lyginama su >=, todėl kiekviena ląstelė, lygi ribai, paryškinama, net kai tai viršija prašomą skaičių. Dvidešimt septynios reikšmės ir 10 procentų taisyklė paryškina dvi ląsteles, plius visas tolesnes ląsteles, sulygintas su antrąja

Virš vidurkio taisyklės slėpė antrą nevienareikšmiškumą: aboveAverage su stdDev="1" pasirenka ląsteles, esančias vienu standartiniu nuokrypiu virš vidurkio, bet imties ir populiacijos nuokrypiai skiriasi Beselio pataisa, ir jie akivaizdžiai nesutaria mažuose intervaluose, o tai kaip tik ir yra vieta, kur naudojamas sąlyginis formatavimas. Excel 16 naudoja populiacijos nuokrypį, ir HotXLS jį atitinka, o equalAverage vėliavėlė griežtą palyginimą padaro imtinu tik tada, kai jokia nuokrypio juosta nedalyvauja. Dublikatų ir unikalios taisyklės pasikliauja rakto tapatybe. Jei viena ląstelė laiko skaičių 100, o kita — tekstą „100“, Excel juos laiko tuo pačiu dublikato raktu, todėl HotXLS normalizuoja skaitinį tekstą į skaitinę rakto erdvę vietoj žalių eilučių palyginimo. Tuščios ląstelės — atspindys: tikra tuščia ląstelė dalyvauja intervalo skaičiuje, bet pati nėra stilizuojama, todėl tuščios stulpelio ląstelės nesušvyti visos kaip vienos kitų dublikatai

Spalvų skalės ir piktogramų rinkiniai: interpoliacija ir ribinės taisyklės

Vizualinės šeimos duoda skaičius, paruoštus atvaizdavimui, ne loginius reikšmes, ir jų ribinis elgesys buvo pritvirtintas taip pat. Spalvų skalei su aiškiomis skaitinėmis ribomis HotXLS apkarpo pozicijos trupmeną iki uždaro intervalo nuo nulio iki vieno, tada interpoliuoja kiekvieną kanalą su trumpinimu, ne apvalinimu — reikšmė žemiau minimumo gauna minimalią spalvą, o ne ekstrapoliuotą, trijų sustojimų skalė savo porą pasirenka lygindama su vidurio sustojimu, o degeneruota skalė, kurios abu galai neša tą pačią ribą, susitraukia į viršutinę spalvą, o ne dalija iš nulio. Piktogramų rinkiniams reikėjo priešingos rūšies rūpesčio, nes kiekvienas cfvo po pirmojo neša savo pačios palyginimo griežtumą: HotXLS skaito ThresholdEqualsInclude kiekvienai ribai ir taiko >= arba > atitinkamai, eidama į viršų, kad aukščiausia patenkinta riba laimėtų piktogramos indeksą. Apverstas rinkinys apverčia išspręstą indeksą, ne ribas, atskiros piktogramos perrašymai gali ištraukti ženklą iš kitos šeimos, o bet kokia negaliojanti riba nutraukia taisyklę, vietoj to, kad sukurtų patikimai atrodančią, bet neteisingą piktogramą

Vieno tinklelio, HTML eksporto ir PDF maitinimas iš vieno rezultato

Kadangi EvaluateCell grąžina pilnai išspręstą TXLSXCfCellResult — diferencinis užpildymas ir šrifto spalva su jau pritaikytu temos atspalviu, paryškinimas, kursyvas, pabraukimas, skaičiaus formato id, kryptingi teigiami ir neigiami juostos ilgiai, ašies pozicija, piktogramos šeima ir indeksas — kiekvienas naudotojas skaito tą patį įrašą, ir nė vienam nereikia suprasti taisyklių vidinės logikos. HotXLS naudoja tą vieną kelią HTML eksportui, PDF eksportui ir interaktyviai žiūryklei, ir tai — vienintelis praktinis būdas neleisti trims atvaizdavimo mechanizmams nusiplėšti vienas nuo kito. Versija 2.210.0 tai sujungė su TXLSWorkbookViewer, kuri talpina po vieną paruoštą vertintuvą kiekvienam aktyviam darbalapiui ir naudoja jį pakartotinai per slinkimą, pasirinkimą ir perpiešimą, atleisdama, kai darbaknygė ar darbalapis pasikeičia — nuotraukos atkūrimas kiekvieno Paint metu sugriautų visą konstravimo metu grįstą dizainą. Ši podėlio talpykla — priežastis, kodėl egzistuoja TXLSWorkbookViewer.RefreshConditionalFormats: nuotrauka nekintanti, todėl jei pakeičiate prijungtą darbaknygę vietoje, suminė statistika ir išspręstos ribos yra pasenusios, kol jo neiškviesite

// 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, repaint

Ko vertintuvas jums nepadarys

Verta aiškiai įvardyti tris ribas. Klasikinis vienos ląstelės TCondFormatRule.Evaluate ir lapo lygio TXLSXConditionalFormatEvaluator — skirtingi paviršiai su skirtingomis galimybėmis, o vienos ląstelės vertintuvas sąmoningai atsisako suminių ir vizualinių šeimų, o ne bando jas aproksimuoti — jei reikia Top/Bottom ar spalvų skalės, kurkite vertintuvą. Santykiniai laiko periodai priklauso nuo mašinos laikrodžio vertinimo metu, todėl timePeriod taisyklė atvaizduojasi kitaip PDF, sugeneruotame šiandien, ir tokiame, sugeneruotame kitą savaitę, kas yra teisingas elgesys ir vis dar palaikymo bilietas, laukiantis nutikti, jei jūsų archyvas turėtų būti baitas į baitą stabilus. Trečioji riba gramatinė, ne techninė: sąlyginio formato formulės gramatika draudžia struktūrizuotas lentelės nuorodas, todėl taisyklė negali kreiptis į lentelės stulpelį vardu taip, kaip gali darbalapio formulė, ir tai — formato apribojimas, ne realizacijos

Jei kuriate ataskaitos išvestį, eksporto srautą ar pasirinktinį tinklelį, kuris turi sutapti su Excel ląstelė po ląstelės, tas pats išspręstas rezultatas taip pat maitina pasirinktinį VCL skaičiuoklės tinklelį, aprašytą kitur šiame tinklaraštyje. Visa API dokumentacija, taisyklių modelis ir bandomosios versijos HotXLS Delphi skaičiuoklės komponentui yra produkto puslapyje