Excel feltételes formázás kiértékelése Delphiben HotXLS-szel

A HotXLS egy natív táblázatkezelő komponens Delphihez és C++Builderhez, és a 2.209.0-s verzió óta meg tudja válaszolni azt a kérdést, amelyet az Excel normál esetben magának tart meg: erre a pontos cellára mely feltételes formázási szabályok lépnek életbe, és milyen kitöltésre, betűtípusra, adatsávra vagy ikonra oldódnak fel. Erre a válaszra abban a pillanatban van szükségünk, amint a kimenetünk egy HTML jelentés, egy PDF, vagy egy rács, amelyet magunk festünk

Ez más probléma, mint a szabályok létrehozása. Két korábbi jegyzet foglalkozik a szerkesztési oldallal: a feltételes formázás és a rich text stílusok a szabályok és a differenciálformátumok tartományhoz kapcsolásával foglalkozik, a horgonyzott feltételes formátumok particionálása pedig azzal, mi történik egy szabály tartományával, amikor sorokat vagy oszlopokat szúrunk be vagy törlünk. Mindkettő strukturális. Ez a cikk a szemantikáról szól: adott egy munkafüzet, amely már hordoz szabályokat, számítsuk ki a kiemelést

Miért nem árulja el a fájlformátum, mely cellák gyulladnak ki

A rövid válasz az, hogy az ECMA-376 és az ISO 29500-1 a tárolást definiálja, nem a kiértékelést. Egy conditionalFormatting elem (§18.3.1.18) hordoz egy sqref-et és egy cfRule gyermeklistát (§18.3.1.10), és minden szabály hordoz egy type-ot, opcionálisan egy operator-t, egy priority-t, egy stopIfTrue jelzőt, egy vagy két formula gyermeket, a vizuális családoknál pedig egy cfvo küszöbérték-halmazt. Ezek mindegyike hűen leírja, amit a felhasználó beállított, és egyik sem algoritmus. A szabálytípusok felénél ez a hiányosság nem számít: a cellIs operator="greaterThan"-nal nagyobbat jelent, a containsText pedig azt, hogy a részsztring jelen van. A hiányosság az összesítő családoknál nyílik meg. Egy top10 szabály rank="10" és percent="1" mellett 27 kitöltött numerikus cella fölött hány cellát emel ki? A kettő egész hét nem szám. Kerekítés, lefelé kerekítés vagy felfelé kerekítés — a specifikáció hallgat, és a rossz választás azt jelenti, hogy a PDF-ünk nem ért egyet azzal a munkafüzettel, amely mellette nyitva van az ügyfélnél

Egycellás szabályok, és hol áll meg a TCondFormatRule.Evaluate

A HotXLS előbb a könnyebb felét vette elő. A lxCondFormat.pas-ban lévő TCondFormatRule.Evaluate, amely a 2.199.0-ban került hozzáadásra, azt válaszolja meg, hogy egy szabály életbe lép-e egyetlen cellánál, anélkül hogy bármit is tudna a tartomány többi részéről. Kezeli a cellIs mögötti nyolc BIFF-összehasonlító operátort (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), a cellánál kiértékelt szabadformájú expression szabályokat, így a relatív hivatkozások helyesen áthelyeződnek, a négy szövegpredikátumot, valamint az üres és hiba predikátumokat. A küszöbértékek az FFormula1-ből és FFormula2-ből származnak, a cella pozíciójánál a TXLSCalculator.GetRangeValue-n keresztül feloldva, a megfordított határok pedig felcserélésre kerülnek, nem elutasításra

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;

Ennek a metódusnak az őszinte része az, amit visszautasít találgatni. A top10, aboveAverage, belowAverage, duplicateValues és uniqueValues False-szal tér vissza, nem azért mert nehezek, hanem mert eldönthetetlenek egyetlen cellából — mindegyikük statisztikát igényel a teljes tartomány fölött. A négy vizuális család, a dataBar, colorScale2, colorScale3 és iconSet, más okból tér vissza False-szal: soha nem állítanak elő boolean értéket egyáltalán, egy renderelési hasznos adatot állítanak elő, és egy boolean visszatérési típus rossz forma a számukra

Hogyan kerüli el egy munkalapszintű kiértékelő a lap újraszkennelését?

Úgy, hogy minden megosztott mennyiséget egyszer számít ki, a konstrukciónál, és soha többé. A lxHandleX.pas-ban lévő TXLSXConditionalFormatEvaluator egy megváltoztathatatlan pillanatkép egy munkalaphoz, a TXLSXWorksheet.CreateConditionalFormatEvaluator-on keresztül felépítve, és a teljes tervezése egy védelem a naiv megvalósítás ellen, ahol minden megfestett cella egy teljes tartományú szkennelést vált ki

Négy dolog történik a konstruktorban. Minden egyes különböző, több területet lefedő sqref pontosan egyszer kerül elemzésre egy TXlsxCfRangeSnapshot-ba, így tíz szabály, amely egy tartományt oszt meg, egy elemzést és egy statisztikai lépést is megoszt. Ez a lépés egyetlen bejárásban streameli az átlagot, a populációs eltérést, a minimumot és a maximumot a kitöltött cellák fölött, és csak akkor tart meg egy rendezett numerikus tömböt, ha egy Top/Bottom vagy percentilis szabálynak ténylegesen szüksége van rendezési statisztikákra. A duplikátum és egyedi kulcsok Unicode-biztos módon épülnek fel, és egyszer kerülnek kötegelten rendezésre lekérdezésenkénti rendezés helyett. Ezután a sortengely sávokra van vágva minden területhatárnál, így az EvaluateCell bináris keresést végez egy sávban, és csak azokat a szabályokat látogatja meg, amelyek tartományai egyáltalán elérhetik azt a sort

A negyedik az, amelyik a legjobban számít nagy méretben. Egy relatív szabályformula, mint az =A1>AVERAGE($A$1:$A$100), mást jelent a tartomány minden cellájában, és a nyilvánvaló megvalósítás cellánként egy friss szintaxisfát fordít. A TXlsxCfRulePlan egyszer fordítja, és ugyanazt a fát reverzibilis koordináta-eltolásokon keresztül újraértékeli, ami megőrzi az Excel horgonyviselkedést cellánkénti szintaxisfa-allokáció nélkül. A szabályok ezután priority szerint rétegződnek, és egy találat egy olyan szabálynál, amelynek StopIfTrue mezője be van állítva, megszakítja a ciklust, pontosan úgy, ahogy az Excel is rövidzár-kapcsolást alkalmaz

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;

Hogyan kerekít valójában az Excel egy Top 10 százalékos szabálynál?

Lefelé kerekít, minimum eggyel, és beleérti a döntetlen eredményeket a vágási pontnál. Ez sehol nincs leírva az ISO 29500-1-ben — kézzel épített munkafüzetekkel próbálgatva rögzítettük az Excel 16-tal, visszaolvasva, mely cellákat emelte ki az alkalmazás. A HotXLS pontosan ezt valósítja meg: a rangszám Floor(Count * Min(Rank, 100) / 100), egyre emelve, amikor nullára esik, a kitöltött darabszámra korlátozva, a vágási érték pedig ezután >=-vel kerül összehasonlításra, így minden, a határértékkel egyenlő cella kiemelésre kerül, még akkor is, ha ez túllépi a kért darabszámot. Huszonhét érték és egy 10 százalékos szabály két cellát emel ki, plusz minden további, a másodikkal döntetlen cellát

Az átlag feletti szabályok egy második kétértelműséget rejtettek: az aboveAverage stdDev="1"-gyel az átlag fölötti egy szórásnyi cellákat választja ki, de a minta- és a populációs eltérés a Bessel-korrekcióval eltér egymástól, és láthatóan nem értenek egyet kis tartományoknál, ami pontosan az, ahol a feltételes formázást használják. Az Excel 16 a populációs eltérést használja, a HotXLS pedig ezt követi, az equalAverage jelző pedig csak akkor teszi befogadóvá a szigorú összehasonlítást, ha nincs eltérési sáv játékban. A duplikátum és egyedi szabályok kulcsidentitás alapján fordulnak be. Ha az egyik cella a 100 számot, egy másik pedig a "100" szöveget tartalmazza, az Excel ugyanazon duplikátumkulcsnak kezeli őket, így a HotXLS a numerikus szöveget a numerikus kulcstérbe normalizálja nyers sztringek összehasonlítása helyett. Az üres cellák a tükörkép esete: egy valóban üres cella részt vesz a tartomány összesítésében, de önmaga nem stilizálódik, így egy oszlop üres cellái nem gyulladnak ki mind egymás duplikátumaként

Színskálák és ikonkészletek: interpoláció és határszabályok

A vizuális családok renderelésre kész számokra oldódnak fel, nem booleanekre, és a szélső viselkedésüket ugyanígy rögzítettük. Egy explicit numerikus küszöbértékekkel rendelkező színskálánál a HotXLS a pozíciótörtet a zárt nulla-egy intervallumra korlátozza, majd csatornánként interpolál csonkolással kerekítés helyett — egy a minimum alatti érték a minimum színt kapja extrapolált szín helyett, egy háromlépcsős skála a párját a középső küszöbértékkel összehasonlítva választja ki, egy degenerált skála pedig, amelynek két vége azonos küszöbértéket hordoz, a felső színre esik össze nulla osztás helyett. Az ikonkészletek fordított típusú gondosságot igényeltek, mert az első utáni minden cfvo saját összehasonlítási szigorúságot hordoz: a HotXLS küszöbértékenként beolvassa a ThresholdEqualsInclude-ot, és ennek megfelelően alkalmazza a >=-t vagy a >-t, felfelé bejárva, így a legmagasabb kielégített küszöbérték nyeri az ikonindexet. Egy megfordított készlet a feloldott indexet fordítja meg, nem a küszöbértékeket, az ikononkénti felülbírálások egy másik családból is húzhatnak glifát, és bármilyen érvénytelen küszöbérték megszakítja a szabályt ahelyett, hogy hihetőnek tűnő, de hibás ikont állítana elő

Egy rács, egy HTML export és egy PDF táplálása egyetlen eredményből

Mivel az EvaluateCell egy teljesen feloldott TXLSXCfCellResult-tal tér vissza — differenciál kitöltési és betűtípusszín témaárnyalattal már alkalmazva, félkövér, dőlt, aláhúzott, számformátum-azonosító, irányított pozitív és negatív sávkiterjedések, tengelypozíció, ikoncsalád és -index —, minden fogyasztó ugyanazt a rekordot olvassa, és egyiknek sem kell értenie a szabályok belsejét. A HotXLS ezt az egy útvonalat használja a HTML exporthoz, a PDF exporthoz és az interaktív megjelenítőhöz, ami az egyetlen gyakorlati módja annak, hogy megakadályozzuk, hogy a három renderelő eltérjen egymástól. A 2.210.0-s verzió bekötötte a TXLSWorkbookViewer-be, amely munkalaponként egyetlen előkészített kiértékelőt gyorsítótáraz, és azt görgetés, kijelölés és újrarajzolás közben újrahasznosítja, majd felszabadítja, amikor a munkafüzet vagy a munkalap megváltozik — a pillanatkép minden Paint alkalmával történő újraépítése legyőzné az egész konstrukció-idejű tervezést. Ez a gyorsítótár az oka annak is, hogy a TXLSWorkbookViewer.RefreshConditionalFormats létezik: a pillanatkép megváltoztathatatlan, így ha a csatolt munkafüzetet a helyén módosítjuk, az összesített statisztikák és a feloldott küszöbértékek elavultak maradnak, amíg meg nem hívjuk

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

Amit a kiértékelő nem fog megtenni helyettünk

Három határt érdemes egyértelműen kimondani. A klasszikus egycellás TCondFormatRule.Evaluate és a munkalapszintű TXLSXConditionalFormatEvaluator különböző felületek, különböző képességekkel, és az egycellás szándékosan elutasítja az összesítő és vizuális családokat ahelyett, hogy közelítené őket — ha Top/Bottom vagy egy színskála kell, építsük fel a kiértékelőt. A relatív időszakok a gép óráján múlnak a kiértékelés pillanatában, így egy timePeriod szabály másképp jelenik meg egy ma és egy jövő héten generált PDF-ben, ami helyes viselkedés, mégis egy hibajegy, amely arra vár, hogy megtörténjen, ha az archívumunknak bájtstabilnak kellene lennie. A harmadik inkább grammatikai, mint technikai: a feltételes formátum-formula nyelvtan tiltja a strukturált táblahivatkozásokat, így egy szabály nem tud egy táblaoszlopra hivatkozni néven, ahogy azt egy munkalap-formula megteheti, és ez inkább a formátum, mint a megvalósítás korlátja

Ha jelentéskimenetet, egy export pipeline-t vagy egy egyedi rácsot építünk, amelynek cellánként egyet kell értenie az Excellel, ugyanaz a feloldott eredmény hajtja a blogunk másik cikkében leírt egyedi VCL táblázatkezelő rácsot is. A teljes API-dokumentáció, a szabálymodell és a próbaverziók a HotXLS Delphi spreadsheet component termékoldalán érhetők el