HotXLS-diagrammer, bilder og tegneobjekter i Delphi

Alt som flyter over et regnearkrutenett (et diagram, en logo, et stempel, en snakkebobleboks) er et tegneobjekt, og et tegneobjekt defineres av to ting: hva det er, og hvor det er forankret. Ankeret er delen folk får galt. Et diagram bor ikke i en celle; det sitter i et rektangel festet til en strekning av rader og kolonner, og dataene det plotter, er et separat sett med A1-referanser ankeret ikke vet noe om. Flytt rammen, og plottet blir stående. Sett inn rader under den, og rammen glir nedover med dem. Å holde disse to koordinatsystemene atskilt er det meste av det som får tegnekoden til å oppføre seg

HotXLS er et nativt Object Pascal-bibliotek som leser og skriver XLS og XLSX uten Excel-automasjon, og det bærer to separate tegnemodeller fordi de to filformatene lagrer tegninger forskjellig. BIFF8-formatet .xls holder diagrammer på sine egne dedikerte ark og flytende former i en OfficeArt-strøm festet til regnearket. OOXML-formatet .xlsx kan bygge inn et diagram inne i rutenettet, forankret til et cellerektangel, ved siden av den samme typen flytende bilder og former. Objektmodellen gjenspeiler den todelingen, og feilene som er verdt å skrive om, kommer alle fra å bruke det ene formatets regler på det andre

Hvilken beholder kan holde hva

Valget av beholder må komme før noen diagramkode, fordi de tilgjengelige objekttypene er forskjellige mellom de to:

• XLS (BIFF8): diagrammer bor på dedikerte diagramark opprettet gjennom AddChartSheet på Sheets-samlingen. Bilder, tekstbokser, rektangler, ovaler og linjer er OfficeArt-former håndtert gjennom regnearkets Shapes-samling. Det finnes ikke noe API for å bygge inn et diagram inne i et vanlig regnearkrutenett
• XLSX (OOXML): diagrammer kan bygges inn direkte i et regneark med TXLSXWorksheet.AddChart, forankret til et cellerektangel, eller plasseres på et dedikert diagramark med TXLSXWorkbook.AddChartSheet. Bilder legges inn med AddImage eller AddImageFromFile, og flytende etiketter med AddTextBox

Så et krav formulert som «et dashbord-ark med diagrammet ved siden av tallene» er egentlig et krav om .xlsx. Du kan tilnærme det i .xls bare ved å skyve diagrammet over på sitt eget ark, noe som endrer hvordan brukeren navigerer i filen, og endrer hvordan koden din må oppføre seg. Arket som returneres av XLS-siden sin AddChartSheet, er en diagram-understrøm, ikke et rutenett: å skrive til det med Cells.Item produserer en inkonsistent tegnestrøm som genereres uten feil, og som Excel deretter forkaster ved åpning. Diagrammet forsvinner ganske enkelt, og ingenting i byggeloggen sier hvorfor. Behandle det returnerte arket som diagram-bare, så forsvinner hele klassen av «manglende diagram»-rapporter

Å bygge inn et diagram i et XLSX-regneark

XLSX-veien er den med rom til å manøvrere, og det er der de to koordinatsystemene fra innledningen blir konkrete. Ankerrektangelet som sendes til AddChart, uttrykkes i regnearkrader og -kolonner og fastsetter hvor diagramrammen sitter. Seriedataene uttrykkes som absolutte A1-referanser som inkluderer arknavnet. De er uavhengige: du kan flytte rammen til motsatt side av arket, og den plotter fortsatt de samme cellene

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;

    // Ramme forankret til radene 6..22, kolonnene 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;

Argumentet som biter, er range-strengen som sendes til AddSeries. Den er en literal, fanget i det øyeblikket kallet skjer, og den har ingen anelse om at du kanskje legger til tjue rader til med data etterpå. Bygg den fra et radantall du beregnet etter at dataene var skrevet, aldri før. Scatter- og boblediagrammer overbelaster de samme to argumentene med forskjellige betydninger: kategori-området leverer nå X-verdiene, og verdi-området leverer Y, og bobleradiusen kommer fra et tredje referansesett satt gjennom BubbleSizeRange på den returnerte TXLSXChartSeries. Les kallet som «X, Y, størrelse» snarere enn «kategorier, verdier» så snart du forlater kolonne-og-stolpe-familien

TXLSXChartType spenner over kolonne-, stolpe-, linje-, kake-, areal-, smultrings-, scatter-, boble- og radarplott, noe som dekker det daglige rapporteringsrepertoaret. For et helsides diagram uten omkringliggende rutenett returnerer Book.AddChartSheet et ark hvis IsChartSheet-egenskap er true. Det er .xlsx-motstykket til det gamle diagramarket og bærer den samme forventningen: ikke skriv celleinnhold til det

Bilder legges inn som byte, og de dimensjoneres i EMU

Det finnes to overloads for å sette inn et bilde, og å blande dem sammen er bildefeilen som dukker opp mest i kodegjennomgang. AddImage(ARow, ACol, AData, AFormat) vil ha de allerede kodede bildebytene i AData: råinnholdet i en PNG, JPEG, GIF eller BMP. Gi den en filsti i stedet, og du har lagret en førti-byte-streng ingen viser kan dekode, noe som er akkurat den ødelagt-bilde-ikon-rapporten du ikke vil feilsøke etter utrulling. Når kilden er en fil på disk, kall AddImageFromFile i stedet og la biblioteket lese bytene og klassifisere formatet for deg

Så kommer dimensjoneringen. DrawingML måler ikke i piksler; det måler i English Metric Units, der 914400 EMU utgjør en tomme, og ved 96 DPI utgjør 9525 EMU en piksel. TXLSXImage-objektet eksponerer WidthEMU og HeightEMU, så en logo ment til å rendres 180 ganger 60 piksler, trenger 1714500 ganger 571500 EMU. Legg den konverteringen i en navngitt konstant og beregn mot den. Magiske tall som 1714500 spredt gjennom koden er uleselige og stille feil den første gangen noen endrer mål-DPI-en. Anker-raden og -kolonnen er for øvrig 1-baserte, i tråd med resten av celle-API-et snarere enn den 0-baserte EMU-matematikken

Diagramark og former i gamle XLS-filer

På BIFF8-siden tar den rikere AddChartSheet-overloaden diagramtypen, akse-titlene og et åpent array med TXLSChartSeriesInfo-records, der hver record holder et navn og et kategori- og verdiområde som strenger. Flytende former er en separat sak: de går på selve dataregnearket, gjennom dets Shapes-samling, ikke på diagramarket

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // grensesnitt-tellet: ikke kall 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 er en diagram-understrøm: kall aldri cellemetoder på den

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

To levetidsdetaljer betyr noe her, og de trekker i motsatte retninger. TXLSWorkbook holdes gjennom IXLSWorkbook-grensesnittet og er referansetelt, så å kalle Free på den selv utløser en dobbel frigjøring. TXLSXWorkbook fra de foregående avsnittene er et vanlig objekt og må frigjøres i en try..finally. Den samme kodeanmelderen som flagger en manglende Free på XLSX-siden, må flagge en tilstedeværende en på XLS-siden, noe som er en ekte snublefelle når du jobber i begge formatene i samme enhet. Selve formhjelperne er ensartede: AddRectangle, AddOval og AddLine, med DeleteInRange for å tømme en region for tegninger, alle forankrer via rad- og kolonnepar, så en mal som setter inn rader over dem, forskyver dem sammen med rutenettet

Enda en egenskap fortjener plassen sin på gamle filer. TXLSPicture.TransparentColor masker ut en valgt bakgrunnsfarge fra et bitmap, som er hvordan du slipper et ikke-rektangulært stempel (et «Godkjent»-segl, et vannmerke) ned over rutenettet i et format hvis BIFF-rendring aldri lærte PNG-alfa. Sett fargen stempelet ble laget mot, og det omkringliggende rektangelet forsvinner

Temafarger overlever ikke en BIFF8-rundtur

OOXML-tegnefyllinger kan peke på en temafarge-plass, som er hvorfor det er billig å re-farge en hel .xlsx ved å bytte temaet dens. BIFF8-tegnerecords har ingen slik plass. Når HotXLS påfører en temafarge på en XLS-tegning, løser den fargen til en bokstavelig RGB-verdi og lagrer den; temaindeksen den kom fra, er borte i det øyeblikket filen skrives, og gjenåpning kan ikke gjenopprette den. Dette fanger spesielt white-label-rapporteringsverktøy, den typen som re-brander det samme genererte dokumentet for mange kunder. Hold temaen-til-RGB-kartleggingen i din egen konfigurasjon og påfør den på nytt hver gang du genererer, i stedet for å forvente å kunne lese den ut igjen fra en lagret .xls

En relatert beslutning dukker opp på ytelsessiden. XLS-fasaden kan bes om å hoppe over parsing av tegnelaget helt når alt du vil ha fra en stor gammel fil er celledataene, ved å sette _DisableGraphics til true, og det barberer bort ekte tid fra masseinnlesinger. Fellen er permanent: en arbeidsbok åpnet på den måten har ingen OfficeArt-strøm i minnet, så å lagre den skriver tegningene ut av eksistens. Reserver flagget for skrivebeskyttede analysejobber. Det bredere ytelsesbildet står i notatene våre om ytelse for store arbeidsbøker i HotXLS

Å holde ankrene stabile mens rutenettet endrer seg

Rapporter forblir sjelden på den størrelsen de ble generert i, og det er her ankermodellen fra innledningen betaler seg. XLSX-fasadens strukturelle operasjoner (InsertRows, DeleteRows, og kolonneekvivalentene) flytter de avhengige lagene sammen med cellene. Sammenslåtte områder, hyperlenker, kommentarer, fastfrosne ruter, filterområder, betingede formater, valideringer, tabeller, definerte navn, og, for dette temaet, bilde- og diagramankre, reiser alle sammen. En logo forankret ved rad 1 blir stående på toppen når ti rader legges inn under den. En diagramramme forankret under datablokken glir nedover etter hvert som blokken vokser. Den ene tingen som ikke skrives om, er enhver range-streng du fanget som en literal før innsettingen skjedde, siden det bare er tekst biblioteket ikke har noen grunn til å revidere. Det fastsetter den trygge rekkefølgen for en malutfylling: skriv og omform dataene først, og opprett diagrammer og plasser bilder som det siste passet, med hver range-streng utledet fra radantallene du har etter innsettingene, ikke før

To mindre verktøy fullfører plasseringssettet. TXLSTextBox.SetArea på XLS-siden re-forankrer en eksisterende tekstboks eller autoform til et nytt cellerektangel, noe som slår å slette og gjenskape den når en footer-blokk forskyver seg. Og bitmap-overloaden til AddPicture tar imot et levende TBitmap med et valgfritt gjennomsiktighetsflagg, så alt din egen VCL-kode kan tegne (en måler, en sparkline-stripe, en diagramtype den native listen ikke tilbyr) kan stemples rett inn i arket uten å skrive en midlertidig fil først

Diagrammer og bilder er nesten alltid det avsluttende laget på en allerede strukturert rapport, som er hvorfor grunnarbeidet avgjør om de lander rent. Å fylle inn dataene et diagram vil referere til, dekkes i malstyrt rapportgenerering, og å holde rutenettet stabilt under ankrene dine, er temaet for sammenslåtte celler og layoutkontroll. Full klasse- og metodedokumentasjon finnes på produktsiden for HotXLS Delphi Component