HotXLS-diagrammer, billeder og tegneobjekter i Delphi

Alt, der svæver over et regnearks gitter (et diagram, et logo, et stempel, en tekstboks), er et tegneobjekt, og et tegneobjekt defineres af to ting: hvad det er, og hvor det er forankret. Ankeret er den del, folk får galt fat i. Et diagram bor ikke i en celle; det sidder i et rektangel fastgjort til et spænd af rækker og kolonner, og de data, det plotter, er et separat sæt A1-referencer, som ankeret intet ved om. Flyt rammen, og plottet bliver, hvor det er. Indsæt rækker under det, og rammen glider ned sammen med dem. At holde de to koordinatsystemer adskilt er det meste af, hvad der får tegnekoden til at opføre sig ordentligt

HotXLS er et native Object Pascal-bibliotek, der læser og skriver XLS og XLSX uden Excel-automatisering, og det bærer to separate tegnemodeller, fordi de to filformater gemmer tegninger forskelligt. BIFF8 .xls-formatet holder diagrammer på deres egne dedikerede ark og flydende figurer i en OfficeArt-stream knyttet til regnearket. OOXML .xlsx-formatet kan indlejre et diagram inde i gitteret, forankret til et cellerektangel, sammen med samme slags flydende billeder og figurer. Objektmodellen afspejler den opdeling, og de fejl, det er værd at skrive om, kommer alle fra at anvende det ene formats regler på det andet

Hvilken container kan rumme hvad

Valget af container skal komme før al diagramkode, fordi de tilgængelige objekttyper er forskellige mellem de to:

• XLS (BIFF8): diagrammer bor på dedikerede diagramark oprettet via AddChartSheet på Sheets-samlingen. Billeder, tekstbokse, rektangler, ovaler og linjer er OfficeArt-figurer administreret via regnearkets Shapes-samling. Der er ingen API til at indlejre et diagram inde i et almindeligt regnearksgitter
• XLSX (OOXML): diagrammer kan indlejres direkte i et regneark med TXLSXWorksheet.AddChart, forankret til et cellerektangel, eller placeres på et dedikeret diagramark med TXLSXWorkbook.AddChartSheet. Billeder tilføjes med AddImage eller AddImageFromFile, og flydende labels med AddTextBox

Så et krav formuleret som "et dashboard-ark med diagrammet ved siden af tallene" er reelt et krav til .xlsx. Man kan tilnærme det i .xls kun ved at skubbe diagrammet ud på sit eget ark, hvilket ændrer, hvordan brugeren navigerer i filen, og ændrer, hvordan ens kode skal opføre sig. Det ark, der returneres af den XLS-side AddChartSheet, er en diagram-substream, ikke et gitter: at skrive til det med Cells.Item producerer en inkonsistent tegne-stream, der genereres uden fejl, og som Excel derefter kasserer ved åbning. Diagrammet forsvinder simpelthen, og intet i build-loggen siger hvorfor. Behandl det returnerede ark som diagram-kun, og hele klassen af "manglende diagram"-rapporter forsvinder

At indlejre et diagram i et XLSX-regneark

XLSX-vejen er den med plads til at manøvrere, og det er her, de to koordinatsystemer fra indledningen bliver konkrete. Ankerrektanglet, der sendes til AddChart, udtrykkes i regnearksrækker og -kolonner og fastlægger, hvor diagramrammen sidder. Seriedataene udtrykkes som absolutte A1-referencer, der inkluderer arknavnet. De er uafhængige: man kan flytte rammen til den fjerneste ende af arket, og det plotter stadig de samme celler

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 rækker 6..22, kolonner 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;

Det argument, der bider, er range-strengen, der gives til AddSeries. Det er en literal, indfanget i det øjeblik kaldet sker, og den aner intet om, at man måske tilføjer tyve rækker data mere bagefter. Byg den ud fra et rækkeantal, man har beregnet, efter data blev skrevet, aldrig før. Scatter- og boble-diagrammer overloader de samme to argumenter med forskellige betydninger: categories-området leverer nu X-værdierne, og values-området leverer Y, og boblens radius kommer fra en tredje reference sat via BubbleSizeRange på den returnerede TXLSXChartSeries. Læs kaldet som "X, Y, størrelse" frem for "kategorier, værdier", når man forlader kolonne-og-søjle-familien

TXLSXChartType spænder over kolonne-, søjle-, linje-, cirkel-, areal-, ring-, spredning-, boble- og radar-plots, hvilket dækker det daglige rapporteringsrepertoire. For et helsides diagram uden omgivende gitter returnerer Book.AddChartSheet et ark, hvis IsChartSheet-egenskab er sand. Det er .xlsx-modstykket til det gamle diagramark og bærer den samme forventning: skriv ikke celleindhold til det

Billeder tilføjes som bytes, og de dimensioneres i EMU

Der er to overloads til at indsætte et billede, og at forveksle dem er den billedfejl, der dukker mest op i kodegennemgang. AddImage(ARow, ACol, AData, AFormat) vil have de allerede kodede billedbytes i AData: rå-indholdet af en PNG, JPEG, GIF eller BMP. Giv den en filsti, og man har gemt en fyrre-byte streng, som ingen viser kan afkode, hvilket er præcis den ødelagt-billede-ikon-rapport, man ikke vil debugge efter deployment. Når kilden er en fil på disk, kald AddImageFromFile i stedet og lad biblioteket læse bytene og klassificere formatet for dig

Så kommer dimensioneringen. DrawingML måler ikke i pixels; det måler i English Metric Units, hvor 914400 EMU udgør en tomme, og ved 96 DPI udgør 9525 EMU en pixel. TXLSXImage-objektet eksponerer WidthEMU og HeightEMU, så et logo, der skal gengives 180 gange 60 pixels, kræver 1714500 gange 571500 EMU. Læg den konvertering i en navngivet konstant, og beregn ud fra den. Magiske tal som 1714500 spredt gennem koden er ulæselige og stille forkerte, den første gang nogen ændrer mål-DPI'en. Anker-rækken og -kolonnen er i øvrigt 1-baserede, i overensstemmelse med resten af celle-API'et frem for den 0-baserede EMU-matematik

Diagramark og figurer i ældre XLS-filer

På BIFF8-siden tager den rigere AddChartSheet-overload diagramtypen, akse-titlerne og et åbent array af TXLSChartSeriesInfo-poster, hvor hver post rummer et navn og et categories- og values-område som strenge. Flydende figurer er en separat sag: de går på selve dataregnearket, via dets Shapes-samling, ikke på diagramarket

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // interface-talt: kald ikke 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-substream: kald aldrig 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 betyder noget her, og de trækker i modsatte retninger. TXLSWorkbook holdes via IXLSWorkbook-grænsefladen og er referencetalt, så at kalde Free på det selv udløser en dobbelt frigivelse. TXLSXWorkbook fra de foregående afsnit er et almindeligt objekt og skal frigives i en try..finally. Den samme kodereviewer, der markerer en manglende Free på XLSX-siden, må markere en tilstedeværende én på XLS-siden, hvilket er en reel snublefælde, når man arbejder i begge formater i samme unit. Shape-hjælperne selv er ensartede: AddRectangle, AddOval og AddLine, med DeleteInRange til at rydde en region af tegninger, alle forankrer efter række- og kolonnepar, så en skabelon, der indsætter rækker over dem, flytter dem sammen med gitteret

Endnu en egenskab fortjener sin plads på ældre filer. TXLSPicture.TransparentColor maskerer en valgt baggrundsfarve ud af en bitmap, hvilket er, hvordan man lægger et ikke-rektangulært stempel (et "Godkendt"-segl, et vandmærke) ned over gitteret i et format, hvis BIFF-gengivelse aldrig lærte PNG-alpha. Sæt den farve, stemplet blev udformet mod, og det omgivende rektangel forsvinder

Temafarver overlever ikke en BIFF8-tur frem og tilbage

OOXML-tegnefyldninger kan pege på en temafarve-slot, hvilket er grunden til, at det er billigt at omfarve en hel .xlsx ved at skifte dens tema. BIFF8-tegneposter har ingen sådan slot. Når HotXLS anvender en temafarve på en XLS-tegning, opløser den farven til en bogstavelig RGB-værdi og gemmer den; det tema-indeks, den kom fra, er væk i det øjeblik, filen skrives, og genåbning kan ikke genskabe det. Dette rammer især white-label-rapporteringsværktøjer, den slags der genbrander det samme genererede dokument til mange kunder. Hold tema-til-RGB-mapningen i din egen konfiguration, og genanvend den, hver gang du genererer, frem for at forvente at kunne læse den ud af en gemt .xls igen

En beslægtet beslutning viser sig på performance-siden. XLS-facaden kan bedes om at springe parsing af tegnelaget helt over, når alt, man vil have fra en stor gammel fil, er dens celledata, ved at sætte _DisableGraphics til sand, og det barberer reel tid af bulk-læsninger. Hagen er permanent: en arbejdsbog åbnet på den måde har ingen OfficeArt-stream i hukommelsen, så at gemme den skriver tegningerne ud af eksistens. Reservér flaget til read-only analysejobs. Det bredere performance-billede er i vores noter om performance for store arbejdsbøger i HotXLS

At holde ankre stabile, mens gitteret ændrer sig

Rapporter forbliver sjældent den størrelse, de blev genereret i, og det er her, ankermodellen fra indledningen betaler sig. XLSX-facadens strukturelle operationer (InsertRows, DeleteRows og kolonne-ækvivalenterne) flytter de afhængige lag sammen med cellerne. Sammenlagte regioner, hyperlinks, kommentarer, fastfrosne ruder, filterområder, betingede formater, valideringer, tabeller, definerede navne, og for dette emne, billed- og diagramankre, rejser alle sammen. Et logo forankret ved række 1 bliver ved toppen, når ti rækker går ind under det. En diagramramme forankret under datablokken glider ned, efterhånden som blokken vokser. Det ene, der ikke bliver omskrevet, er enhver range-streng, man har indfanget som en literal, før indsættelsen skete, da det bare er tekst, biblioteket ikke har nogen grund til at genbesøge. Det fastlægger den sikre rækkefølge for en skabelonudfyldning: skriv og omform dataene først, og opret diagrammer og placér billeder som det sidste pas, med hver eneste range-streng udledt af de rækkeantal, man har efter indsættelserne, ikke før

To mindre værktøjer fuldender placeringssættet. TXLSTextBox.SetArea på XLS-siden genforankrer en eksisterende tekstboks eller autoshape på et nyt cellerektangel, hvilket slår at slette og genskabe den, når en fodblok flytter sig. Og bitmap-overloaden af AddPicture tager en levende TBitmap med et valgfrit gennemsigtighedsflag, så alt, ens egen VCL-kode kan tegne (en måler, en sparkline-strimmel, en diagramtype den native liste ikke tilbyder), kan stemples direkte ind på arket uden først at skrive en midlertidig fil

Diagrammer og billeder er næsten altid det afsluttende lag på en allerede struktureret rapport, hvilket er grunden til, at grundarbejdet afgør, om de lander rent. At udfylde de data, et diagram vil referere til, dækkes i skabelonstyret rapportgenerering, og at holde gitteret stabilt under dine ankre er emnet for sammenlagte celler og layoutkontrol. Fuld klasse- og metodedokumentation findes på produktsiden for HotXLS Delphi Component