Ehdollinen muotoilu, muotoiltu teksti ja solutyylit Delphissä HotXLS:n avulla

OOXML:ssa ehdollisen muotoilun sääntö koostuu kahdesta erillisestä, saman nimikkeen alle kätkeytyvästä osasta. Ehto (vertailu, kaava tai tekstiosuma) määrittää, mitkä solut täyttävät vaatimuksen. Ulkoasu (eromuotoilutietue, ECMA-376:n termein dxf) määrittää, miltä nämä solut näyttävät. Excelin valintaikkuna peittää tämän eron, koska siinä täytät molemmat samalla kertaa. HotXLS ei tee niin. Jos luot Delphistä cellIs-säännön mutta jätät tyylin määrittämättä, sääntö on kelvollinen, alue on oikein, kaava antaa toden täsmälleen oikeissa soluissa eikä väri muutu, koska säännön ohje on "tosi, älä maalaa mitään". Ehdon ja seurauksen välinen ero on ensimmäinen asia, joka on ymmärrettävä oikein, ja se selittää useimmat säännöt, jotka näyttävät oikeilta Manage Rules -näkymässä mutta eivät korosta mitään

HotXLS kirjoittaa ehdollisen muotoilun natiivisti sekä BIFF8-.xls- että OOXML-.xlsx-tiedostoihin, samoin muotoillun tekstin jaksoja ja poolattua solutyylimallia. Näillä kolmella ominaisuudella on enemmän yhteistä toteutusta kuin tasainen API-pinta antaa ymmärtää, ja kohdat, joissa tulos poikkeaa tarkoituksesta, ovat tavallisesti niiden välisiä liitoksia

Ehto tarvitsee seurauksen: dxf-tyylin

XLSX-laskentataulukossa vertailusäännöt luodaan AddConditionalFormat-kutsulla, joka vastaanottaa alueen, TXLSXCfOperator-tyyppisen operaattorin sekä kaavan tai literaalin ja palauttaa uuden säännön indeksin laskentataulukon ConditionalFormats-kokoelmassa. Tuon indeksin sääntöoliolla on Style-ominaisuus, ja korostus sijaitsee siinä. Määritä sille täyttöväri, niin ehdon täyttävät solut saavat täytön. Jätä se koskematta, niin olet luonut edellä kuvatun näkymättömän säännön

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

    // negatiivinen poikkeama: vaaleanpunainen täyttö
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // kaksoistilaustunnukset merkitään samalla tavalla
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // mukautettu kaavasääntö: korosta rivit, joissa toteuma jää alle 90 prosenttiin tavoitteesta
    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;

Värit ovat tässä 32-bittisiä ARGB-arvoja, joten $FFFFC7CE on Excelin valintaikkunasta tuttu "vaaleanpunainen", jossa RGB-arvoja edeltää täysin peittävä alfa-tavu. Kaikilla soluittain ehdon perusteella aktivoituvilla sääntötyypeillä on sama luo-sääntö-ja-tyylittele -muoto. Tekstiosumasäännöt (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) palauttavat indeksin, jonka tyylittelet jälkikäteen, kuten myös AddCondFormatTop10, AddCondFormatAboveAverage sekä tyhjien ja virheellisten arvojen tunnistimet. Kun opit mallin kerran, koko teksti- ja vertailusääntöjen perhe toimii samalla tavoin

Datapalkit, väriskaalat ja kuvakejoukot piirtävät itsensä

Visuaaliset sääntötyypit toimivat päinvastoin. Niiden ulkoasu on osa sääntömäärittelyä, ja ne jättävät Style-ominaisuuden kokonaan huomiotta. Jos määrität täyttövärin datapalkkisäännölle, mitään ei tapahdu. Tämä vaikuttaa virheeltä, kunnes luokittelu selviää: AddCondFormatDataBar vastaanottaa palkin värin suoraan argumenttina, kaksi- ja kolmipisteiset väriskaalat vastaanottavat päätepisteiden värit samalla tavoin, ja AddCondFormatIconSet valitsee yhden 26 kuvakejoukkotyypistä, esimerkiksi icsTrafficLights3. Erillistä tyylitietuetta ei voi unohtaa, koska sellaista ei ole lainkaan

Näissä kutsuissa kannattaa harkita arvoankkureita, joiden tyyppi on TXLSCfValueKind. Palkin tai skaalan päätepiste voi olla alueen pienin tai suurin arvo, literaalinen luku, prosentti tai prosenttipiste tai kaavan tulos. Oletusarvot, alueen minimi ja maksimi, toimivat siistissä esimerkkiaineistossa mutta pettävät todellisessa aineistossa poikkeavien arvojen vuoksi: yksi karkaava arvo venyttää asteikon ja litistää kaikki muut palkit tynkämäisiksi. Kun koontinäyttöä on tarkoitus lukea jaksojen välillä, ankkuroidaan päätepisteet mieluummin kiinteisiin lukuihin tai prosenttipisteisiin, jotta puolikas palkki maaliskuussa tarkoittaa samaa määrää kuin puolikas palkki huhtikuussa. Automaattisesti skaalattu palkki on vertailukelpoinen vain itsensä kanssa

XLS-kirjoitin tukee vain neljää sääntötyyppiä

Vanha BIFF8-puoli ei ole pienennetty peilikuva XLSX-puolesta, vaan tarkoituksellinen osajoukko. XLS-julkisivu voi luoda täsmälleen neljä ehdollista sääntömuotoa: datapalkit, kaksiväriset asteikot, kolmiväriset asteikot ja kuvakejoukot, jotka kirjoitetaan virtaan CF12-tietueina. Se ei tarjoa luonti-API:a cellIs-, lauseke- tai tekstisäännöille. Kun avatussa tiedostossa on jo tämänkaltaisia sääntöjä, ne luetaan, säilytetään ja kirjoitetaan takaisin muuttumattomina, joten asiakkaan .xls-tiedoston avaaminen ja tallentaminen ei vahingoita sen olemassa olevia muotoiluja. Et kuitenkaan voi luoda kynnysarvokorostusta alusta alkaen .xls-tiedostoon. Vaihtoehdot ovat jäljitellä sitä koodissa lasketuilla tavallisilla solutäytöillä tai tehdä toimitettavasta tiedostosta .xlsx, jossa koko sääntövalikoima on käytettävissä

Tämä rajoite on ratkaistava ennen tietokerroksen luomista, ei sen jälkeen, koska se muuttaa tiedostomuodon valintaa kaikessa koontinäyttömäisessä. Tiimi, joka valitsee yhteensopivuuden vuoksi .xls-muodon ja määrittelee sitten KPI-raportin cellIs-kynnysarvoilla, on valinnut kaksi toisiinsa sopimatonta asiaa. Tämä on halvempi huomata tiedostomuotoa valittaessa kuin kolme viikkoa toteutuksen aloittamisen jälkeen

Sääntöjen pinoaminen, prioriteetti ja päällekkäiset alueet

Todellisissa koontinäytöissä on harvoin vain yksi sääntö aluetta kohden. Poikkeamasarakkeessa voi olla datapalkki suuruusluokalle, cellIs-sääntö tiukalle kynnysarvolle ja molempien yläpuolella rivitason lausekesääntö eskalointeja varten. Jokaisella TXLSXConditionalFormat-oliolla on Priority-arvo, ja Excel ratkaisee kilpailevat säännöt prioriteettijärjestyksessä. Kun kaksi sääntöä haluaa muotoilla saman solun, voittaja määräytyy asettamasi luvun mukaan, ei sen mukaan, missä järjestyksessä tarkastaja sattuu selaamaan niitä Manage Rules -valintaikkunassa

Käsittele prioriteettia kuten piirto-ohjelman tasojärjestystä. Määritä se tarkoituksella aina, kun kaksi sääntöä voi kohdistua samoihin soluihin, ja jätä arvojen väliin tilaa, jotta myöhempi sääntö mahtuu väliin numeroimatta muita uudelleen. Jos säännöt eivät voi törmätä, esimerkiksi sarakkeeseen E rajoitettu datapalkki ja sarakkeeseen G rajoitettu tekstisääntö, luontijärjestys riittää eikä prioriteetti ansaitse huomiota. Kiinnitä huomio sen sijaan aluerajoihin, sillä kalliit virheet eivät lähes koskaan ole prioriteettikäänteitä. Ne ovat alueita, kuten B2:B200, raportissa joka kasvoi 350 riviin, jolloin kattamaton loppuosa näkyy tavallisina soluina, jotka näyttävät täysin terveeltä aineistolta. Johda jokainen sääntöalue samasta lopullisesta rivimääräarvosta, jota käytetään työkirjan muualla kaaviosarjoihin ja tarkistusalueisiin, niin loppuosa ei jää enää ulkopuolelle

Yksi tarkistustapa on erityisen hyödyllinen. Avaamisen jälkeen avaa tiedosto Excelissä, valitse muotoiltu alue ja käy Manage Rules läpi kerran jokaista mallimuutosta kohti. Ehdollinen muotoilu on yksi harvoista alueista, joissa ainoa luotettava hahmontaja on tiedostoa käyttävä sovellus. XML:ää koskeva yksikkötesti todistaa, että sääntö kirjoitettiin, ei että Excel piirtää sen tarkoittamallasi tavalla. Minuutin silmämääräinen tarkistus sulkee tämän aukon

Muotoiltu teksti: monta muotoa yhdessä solussa

XLSX-mallissa muotoiltua tekstiä sisältävä solu sisältää jaksoluettelon, jossa jokainen jakso koostuu tekstialueesta ja sen omista fonttiominaisuuksista. Luot luettelon erillisenä TXLSXRichText-oliona, lisäät siihen jaksot ja liität sitten koko olion soluun. Omistussääntö on se kohta, jossa on helppo erehtyä. Kun määrität arvon Cell.RichText-ominaisuudelle, solusta tulee olion omistaja, ja solu vapauttaa sen omassa tuhossaan. Jos vapautat sen myös itse, seurauksena on kaksoisvapautus, joka voi pysyä hiljaisena virheen aiheuttaneen ajon ajan ja ilmetä vasta paljon myöhemmin kaatumisena jossakin täysin muualla

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;   // omistajuus siirtyy solulle: älä kutsu Free
end;

Nimenomainen ColorIsAuto := False ei ole valinnainen koristelu. Jaksolla on automaattisen värin lippu, ja väriasetus huomioidaan vasta, kun tämä lippu on poistettu käytöstä. Jos asetat Color-arvon mutta unohdat ColorIsAuto-arvon, jakso näkyy lihavoituna mutta itsepintaisesti mustana ilman virheilmoitusta, joka osoittaisi syyn. Jaksot tukevat myös yliviivausta, eri alleviivaustapoja sekä pystysuuntaista kohdistusta ylä- ja alaindeksille, ja PlainText litistää koko luettelon takaisin yhdeksi merkkijonoksi, kun tekstisisältö täytyy viedä tai vertailla

Solutason muotoiltu teksti on vain XLSX-ominaisuus. XLS-julkisivussa ei ole julkista API:a sen kirjoittamiseen, vaikka jaksot ovat siellä käytettävissä kommenteissa ja tekstiruuduissa TextRuns-ominaisuuden kautta ja olemassa olevasta .xls-tiedostosta luetut muotoillut merkkijonot säilyvät ehjinä tallennuskierroksella. Vetovoima on sama kuin ehdollisessa muotoilussa: kaikki, mikä yhdistää muotoiluja solun sisällä, kuuluu XLSX-kirjoittimeen

Tyylipooli ja tuotantoon päätyvä yhden indeksin virhe

XLSX-mallissa tavallinen solutyylittely käyttää työkirjan poolattuja kokoelmia. Fonts.Add, Fills.AddSolid ja Borders.Add rekisteröivät kukin määritelmän ja palauttavat sen indeksin poolissa. Nämä indeksit ovat nollapohjaisia. Solupuolen niitä käyttävät ominaisuudet, kuten FontIndex, varaavat arvon 0 oletukselle, joten solulle annettava arvo on pooli-indeksi plus yksi:

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

Jos jätät + 1 pois, jokainen otsikko palaa oletusfonttiin. Poikkeusta tai varoitusta ei tule, vain työkirja, joka näyttää siltä kuin kukaan ei olisi tyylitellyt sitä. Toisen asteen virhe piilee silmukassa: Fonts.Add-kutsuminen kerran jokaista riviä varten. Identtiset fonttimääritelmät poistetaan duplikaatteina, joten tiedosto ei vioitu, mutta työ menee hukkaan. Erityisesti kohdistuspooli palauttaa uuden olion jokaisella kutsulla sen sijaan, että se yhdistäisi duplikaatit. Luo pieni joukko tyylejä kerran ennen silmukkaa ja käytä niiden indeksejä uudelleen. Sadan tuhannen rivin raporteissa tämä yksittäinen muutos on yksi vipuvarsista, joita käsitellään artikkelissa HotXLS:n suurten työkirjojen suorituskyvyn viritys. Kun tarvitset vain valmiin semanttisen ulkoasun, molemmat julkisivut tarjoavat alueille ApplyBuiltinStyle-kutsun, joka vastaa Excelin sisäisiä Good-, Bad-, Neutral- ja korostustyylejä ilman, että pooleihin tarvitsee koskea lainkaan

Ehdollinen muotoilu, muotoiltu teksti ja poolatut tyylit ovat raportin viimeinen vaihe. Ne lisätään vasta, kun tietomalli ja asettelu ovat valmiit, ja näitä aiempia vaiheita käsitellään artikkelissa mallipohjainen raporttien luonti HotXLS:llä. Sääntöjen, jaksojen ja tyylien täydelliset viitetiedot ovat HotXLS Delphi Component -tuotesivulla