HotXLS-kaaviot, kuvat ja piirto-objektit Delphissä

Kaikki laskentataulukon ruudukon päällä kelluva (kaavio, logo, leima tai selitekehys) on piirto-objekti, ja piirto-objektin määrittävät kaksi asiaa: mikä se on ja mihin se on ankkuroitu. Ankkuri on se osa, jonka ihmiset ymmärtävät väärin. Kaavio ei sijaitse solussa, vaan suorakulmiossa, joka on kiinnitetty rivi- ja sarakealueeseen. Sen esittämät tiedot ovat erillinen joukko A1-viittauksia, joista ankkuri ei tiedä mitään. Siirrä kehystä, niin kaavio pysyy paikallaan. Lisää rivejä sen alle, niin kehys siirtyy niiden mukana alaspäin. Näiden kahden koordinaatiston pitäminen erillään on suurin osa siitä, mikä saa piirto-objektien koodin toimimaan oikein

HotXLS on natiivi Object Pascal -kirjasto, joka lukee ja kirjoittaa XLS- ja XLSX-tiedostoja ilman Excel-automaatiota. Siinä on kaksi erillistä piirto-objektimallia, koska tiedostomuodot tallentavat piirrokset eri tavoin. BIFF8-.xls-muodossa kaaviot ovat omilla kaaviovälilehdillään ja kelluvat muodot laskentataulukkoon liitetyssä OfficeArt-virrassa. OOXML-.xlsx-muodossa kaavio voidaan upottaa ruudukkoon solusuorakulmioon ankkuroituna samantyyppisten kelluvien kuvien ja muotojen rinnalle. Oliomalli heijastaa tätä jakoa, ja kuvaamisen arvoiset virheet syntyvät kaikki siitä, että yhden tiedostomuodon sääntöjä sovelletaan toiseen

Mikä säilö voi sisältää mitäkin

Säilö on valittava ennen kaaviokoodia, koska käytettävissä olevat objektityypit eroavat näiden kahden välillä:

• XLS (BIFF8): kaaviot ovat erillisillä kaaviovälilehdillä, jotka luodaan Sheets-kokoelman AddChartSheet-kutsulla. Kuvat, tekstiruudut, suorakulmiot, soikiot ja viivat ovat OfficeArt-muotoja, joita hallitaan laskentataulukon Shapes-kokoelmalla. Kaaviota ei voi upottaa tavalliseen laskentataulukon ruudukkoon API:n avulla
• XLSX (OOXML): kaaviot voidaan upottaa suoraan laskentataulukkoon TXLSXWorksheet.AddChart-kutsulla solusuorakulmioon ankkuroituina tai sijoittaa erilliselle kaaviovälilehdelle TXLSXWorkbook.AddChartSheet-kutsulla. Kuvat lisätään AddImage- tai AddImageFromFile-kutsulla ja kelluvat otsikot AddTextBox-kutsulla

Niinpä vaatimus "koontinäyttövälilehti, jossa kaavio on lukujen vieressä" tarkoittaa todellisuudessa .xlsx-muodon vaatimusta. .xls-muodossa sitä voi vain jäljitellä siirtämällä kaavion omalle välilehdelleen, mikä muuttaa tiedoston navigointia ja sitä, miten koodin on toimittava. XLS-puolen AddChartSheet-kutsun palauttama välilehti on kaavioalivirta, ei ruudukko: siihen kirjoittaminen Cells.Item-kutsulla tuottaa epäjohdonmukaisen piirto-objektivirran, joka syntyy ilman virhettä mutta jonka Excel hylkää avatessaan tiedoston. Kaavio vain katoaa, eikä koontilokissa kerrota miksi. Käsittele palautettua välilehteä vain kaaviolle tarkoitettuna, niin koko "puuttuva kaavio" -ilmoitusten luokka häviää

Kaavion upottaminen XLSX-laskentataulukkoon

XLSX-polku tarjoaa enemmän liikkumavaraa, ja siinä alussa mainitut kaksi koordinaatistoa konkretisoituvat. AddChart-kutsulle annettu ankkurisuorakulmio ilmaistaan laskentataulukon riveinä ja sarakkeina, ja se määrää kaaviokehyksen sijainnin. Sarjatiedot ilmaistaan laskentataulukon nimen sisältävinä absoluuttisina A1-viittauksina. Ne ovat toisistaan riippumattomia: voit siirtää kehyksen laskentataulukon toiselle puolelle, ja se esittää silti samat solut

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // Kehys ankkuroituna riveihin 6..22, sarakkeisiin 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

Hankala argumentti on AddSeries-kutsulle annettava alueen merkkijono. Se on literaali, joka tallentuu kutsuhetkellä, eikä se tiedä, että saatat lisätä jälkeenpäin vielä kaksikymmentä tietoriviä. Rakenna se aina tietojen kirjoittamisen jälkeen lasketusta rivimäärästä, ei koskaan ennen sitä. Piste- ja kuplakaaviot käyttävät samoja kahta argumenttia eri merkityksessä: luokka-alue antaa nyt X-arvot ja arvoalue Y-arvot, ja kuplan säde tulee kolmannesta viitejoukosta palautetun TXLSXChartSeries-olion BubbleSizeRange-ominaisuuden kautta. Kun poistut pylväs- ja palkkikaavioiden perheestä, lue kutsu muodossa "X, Y, koko", älä muodossa "luokat, arvot"

TXLSXChartType kattaa pylväs-, palkki-, viiva-, ympyrä-, alue-, rengas-, piste-, kupla- ja tutkakaaviot, mikä kattaa tavallisen raportoinnin tarpeet. Koko sivun kaaviolle ilman ympäröivää ruudukkoa Book.AddChartSheet palauttaa välilehden, jonka IsChartSheet-ominaisuus on tosi. Se on vanhan kaaviovälilehden .xlsx-vastine ja siihen liittyy sama odotus: älä kirjoita siihen solusisältöä

Kuvat lisätään tavuina ja niiden koko ilmoitetaan EMU-yksikköinä

Kuvan lisäämiseen on kaksi ylikuormitettua kutsua, ja niiden sekoittaminen on koodikatselmuksissa yleisimmin näkyvä kuvavirhe. AddImage(ARow, ACol, AData, AFormat) odottaa AData-argumentissa jo koodattuja kuvatavuja, eli PNG-, JPEG-, GIF- tai BMP-tiedoston raakasisältöä. Jos annat sille tiedostopolun, olet tallentanut neljänkymmenen tavun merkkijonon, jota mikään katselin ei voi purkaa. Juuri tällaisen rikkinäisen kuvakkeen ilmoitusta et halua selvittää käyttöönoton jälkeen. Kun lähde on levyllä oleva tiedosto, kutsu sen sijaan AddImageFromFile-metodia ja anna kirjaston lukea tavut sekä tunnistaa muoto puolestasi

Seuraavaksi tulee koon määritys. DrawingML ei mittaa pikseleinä vaan English Metric Units -yksiköinä: tuumassa on 914400 EMU:ta ja 96 DPI:n tarkkuudella pikselissä on 9525 EMU:ta. TXLSXImage-olio tarjoaa WidthEMU- ja HeightEMU-ominaisuudet, joten 180 kertaa 60 pikselin kokoisena piirrettävä logo tarvitsee 1714500 kertaa 571500 EMU:ta. Tallenna muunnos nimettyyn vakioon ja laske sen avulla. Koodiin ripotellut maagiset luvut, kuten 1714500, ovat lukukelvottomia ja hiljaisesti vääriä heti, kun joku muuttaa kohde-DPI:tä. Ankkuririvi ja -sarake ovat muuten yksipohjaisia, kuten muu solujen API, eivätkä noudata nollapohjaista EMU-laskentaa

Kaaviovälilehdet ja muodot vanhoissa XLS-tiedostoissa

BIFF8-puolella monipuolisempi AddChartSheet-ylikuormitus vastaanottaa kaaviotyypin, akselien otsikot ja avoimen taulukon TXLSChartSeriesInfo-tietueita, joissa jokaisessa on nimi sekä luokkien ja arvojen alue merkkijonoina. Kelluvat muodot ovat erillinen asia: ne lisätään itse tietoja sisältävälle laskentataulukolle sen Shapes-kokoelman kautta, ei kaaviovälilehdelle

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // liittymälaskennallinen: älä kutsu Free
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend on kaavion alivirta: älä koskaan kutsu sen solumetodeja

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

Kaksi elinkaareen liittyvää yksityiskohtaa ovat tässä tärkeitä, ja ne vetävät vastakkaisiin suuntiin. TXLSWorkbook pidetään IXLSWorkbook-rajapinnan kautta, ja se on viitelaskettu, joten Free-kutsun tekeminen itse aiheuttaa kaksoisvapautuksen. Edellisten osioiden TXLSXWorkbook on tavallinen olio, joka on vapautettava try..finally-lohkossa. Sama koodikatselmoija, joka huomauttaa puuttuvasta Free-kutsusta XLSX-puolella, joutuu huomauttamaan olemassa olevasta kutsusta XLS-puolella. Tämä on todellinen kompastumisvaara, kun molempia muotoja käsitellään samassa yksikössä. Muotojen apumetodit ovat itsessään yhdenmukaisia: AddRectangle, AddOval ja AddLine sekä piirto-objektialueen tyhjentävä DeleteInRange ankkuroituvat kaikki rivi- ja sarakepareilla, joten niiden yläpuolelle rivejä lisäävä malli siirtää niitä ruudukon mukana

Vielä yksi ominaisuus on hyödyllinen vanhoissa tiedostoissa. TXLSPicture.TransparentColor peittää valitun taustavärin bittikartasta. Näin voit sijoittaa ruudukon päälle epämuotoisen leiman, kuten "Approved"-sinetin tai vesileiman, muodossa jonka BIFF-hahmontaja ei koskaan oppinut käsittelemään PNG:n alfakanavaa. Aseta väri, jota vasten leima luotiin, niin ympäröivä suorakulmio katoaa

Teemavärit eivät säily BIFF8-tallennuskierroksella

OOXML-piirto-objektien täytöt voivat viitata teemaväripaikkaan, minkä vuoksi koko .xlsx-tiedoston uudelleenväritys vaihtamalla sen teemaa on edullista. BIFF8-piirtotietueilla ei ole vastaavaa paikkaa. Kun HotXLS käyttää teemaväriä XLS-piirto-objektiin, se muuntaa värin literaaliseksi RGB-arvoksi ja tallentaa sen; teemahakemisto, josta väri tuli, häviää heti tiedoston kirjoitushetkellä, eikä sitä voi palauttaa avaamalla tiedoston uudelleen. Tämä koskee erityisesti raportointityökaluja, jotka brändäävät saman luodun asiakirjan useille asiakkaille. Säilytä teema–RGB-kartoitus omassa asetuksessasi ja käytä sitä aina uudelleen luotaessa sen sijaan, että odottaisit voivasi lukea sen takaisin tallennetusta .xls-tiedostosta

Sama päätös näkyy suorituskykypuolella. XLS-julkisivun voi käskeä ohittamaan piirto-objektikerroksen jäsennyksen kokonaan, kun suuresta vanhasta tiedostosta tarvitaan vain solutiedot, asettamalla _DisableGraphics-arvoksi true. Tämä säästää huomattavasti aikaa massaluvuissa. Seurauksella on kuitenkin pysyvä luonne: näin avatun työkirjan muistissa ei ole OfficeArt-virtaa, joten tallennus poistaa piirrokset olemassaolosta. Varaa lippu vain luku -analyysitöihin. Laajempaa suorituskykykuvaa käsitellään HotXLS:n suurten työkirjojen suorituskykyä koskevissa muistiinpanoissamme

Ankkurien pitäminen vakaina ruudukon muuttuessa

Raportit pysyvät harvoin siinä koossa, jossa ne luotiin, ja juuri tässä alun ankkurimalli kannattaa. XLSX-julkisivun rakenneoperaatiot (InsertRows, DeleteRows ja sarakkeiden vastaavat operaatiot) siirtävät riippuvaisia tasoja solujen mukana. Yhdistetyt alueet, hyperlinkit, kommentit, jäädytetyt ruudut, suodatusalueet, ehdolliset muotoilut, tarkistukset, taulukot, määritetyt nimet sekä tässä aiheessa kuvien ja kaavioiden ankkurit kulkevat kaikki yhdessä. Ensimmäiselle riville ankkuroitu logo pysyy ylhäällä, kun sen alle lisätään kymmenen riviä. Tietolohkon alle ankkuroitu kaaviokehys siirtyy alaspäin lohkon kasvaessa. Yhtä asiaa ei kirjoiteta uudelleen: aluemerkkijonoa, jonka tallensit literaalina ennen lisäystä, koska se on vain tekstiä, jota kirjastolla ei ole syytä tarkistaa uudelleen. Tämä määrittää turvallisen järjestyksen mallin täyttämiselle: kirjoita ja muotoile tiedot ensin, luo kaaviot ja sijoita kuvat vasta viimeisessä vaiheessa sekä johda jokainen aluemerkkijono lisäysten jälkeisistä rivimääristä, ei niitä edeltävistä

Kaksi pienempää työkalua täydentää sijoittelupakin. XLS-puolen TXLSTextBox.SetArea ankkuroi olemassa olevan tekstiruudun tai automaattisen muodon uuteen solusuorakulmioon, mikä on parempi kuin objektin poistaminen ja luominen uudelleen, kun alatunnisteen lohko siirtyy. Lisäksi AddPicture-metodin bittikarttaylikuormitus vastaanottaa elävän TBitmap-olion valinnaisella läpinäkyvyyslipulla, joten kaikki, mitä oma VCL-koodisi voi piirtää (mittari, pienoistrippi tai kaaviotyyppi, jota natiiviluettelo ei tarjoa), voidaan leimata suoraan laskentataulukkoon ilman väliaikaisen tiedoston kirjoittamista

Kaaviot ja kuvat ovat lähes aina viimeistelykerros jo jäsennellyssä raportissa, minkä vuoksi pohjatyö ratkaisee, asettuvatko ne siististi. Kaavion käyttämien tietojen täyttämistä käsitellään artikkelissa mallipohjainen raporttien luonti, ja ruudukon pitämistä vakaana ankkurien alla käsitellään artikkelissa yhdistetyt solut ja asettelun hallinta. Luokkien ja metodien täydelliset ohjeet ovat HotXLS Delphi Component -tuotesivulla