HotXLS: charts, images, and drawing objects in Delphi

Alles wat boven een werkbladraster zweeft (een grafiek, een logo, een stempel, een callout-vak) is een tekenobject, en een tekenobject wordt gedefinieerd door twee dingen: wat het is, en waar het is verankerd. Het anker is het deel waar mensen het fout doen. Een grafiek leeft niet in een cel; hij zit in een rechthoek die is vastgepind aan een reeks rijen en kolommen, en de data die hij plot is een aparte set A1-verwijzingen waar het anker niets van weet. Verplaats het kader en de plot blijft staan. Voeg rijen eronder in en het kader schuift ermee naar beneden. Die twee coördinatensystemen uit elkaar houden is het grootste deel van wat tekencode goed laat gedragen

HotXLS is een native Object Pascal-bibliotheek die XLS en XLSX leest en schrijft zonder Excel-automatisering, en het draagt twee gescheiden tekenmodellen omdat de twee bestandsformaten tekeningen anders opslaan. Het BIFF8-.xls-formaat houdt grafieken op hun eigen toegewijde bladen en zwevende vormen in een OfficeArt-stream gekoppeld aan het werkblad. Het OOXML-.xlsx-formaat kan een grafiek insluiten in het raster, verankerd aan een celrechthoek, naast hetzelfde soort zwevende afbeeldingen en vormen. Het objectmodel weerspiegelt die splitsing, en de mislukkingen die het waard zijn om over te schrijven komen allemaal voort uit het toepassen van de regels van het ene formaat op het andere

Welke container wat kan bevatten

De keuze van container moet vóór alle grafiekcode komen, want de beschikbare objecttypen verschillen tussen de twee:

• XLS (BIFF8): grafieken leven op toegewijde grafiekbladen aangemaakt via AddChartSheet op de Sheets-verzameling. Afbeeldingen, tekstvakken, rechthoeken, ovalen en lijnen zijn OfficeArt-vormen beheerd via de Shapes-verzameling van het werkblad. Er is geen API om een grafiek in te sluiten in een normaal werkbladraster
• XLSX (OOXML): grafieken kunnen rechtstreeks in een werkblad worden ingesloten met TXLSXWorksheet.AddChart, verankerd aan een celrechthoek, of geplaatst op een toegewijd grafiekblad met TXLSXWorkbook.AddChartSheet. Afbeeldingen gaan erin met AddImage of AddImageFromFile, en zwevende labels met AddTextBox

Dus een eis geformuleerd als "een dashboardblad met de grafiek naast de getallen" is eigenlijk een eis voor .xlsx. Je kunt het in .xls alleen benaderen door de grafiek naar zijn eigen blad te duwen, wat verandert hoe de gebruiker door het bestand navigeert en verandert hoe je code zich moet gedragen. Het blad dat de XLS-kant AddChartSheet teruggeeft is een grafiek-substream, geen raster: er met Cells.Item naar schrijven produceert een inconsistente tekenstroom die zonder fout wordt gegenereerd en die Excel vervolgens bij het openen weggooit. De grafiek verdwijnt gewoon, en niets in het buildlog zegt waarom. Behandel het teruggegeven blad als alleen-voor-grafieken en de hele klasse van "ontbrekende grafiek"-meldingen verdwijnt

Een grafiek insluiten in een XLSX-werkblad

Het XLSX-pad is degene met ruimte om te manoeuvreren, en het is waar de twee coördinatensystemen uit de opening concreet worden. De ankerrechthoek doorgegeven aan AddChart is uitgedrukt in werkbladrijen en -kolommen en legt vast waar het grafiekkader zit. De reeksdata is uitgedrukt als absolute A1-verwijzingen die de bladnaam bevatten. Ze zijn onafhankelijk: je kunt het kader naar de andere kant van het blad verplaatsen en het plot nog steeds dezelfde cellen

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;

    // Kader verankerd aan rijen 6..22, kolommen 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;

Het argument dat bijt is de bereikstring die aan AddSeries wordt meegegeven. Het is een letterlijke waarde, vastgelegd op het moment van de aanroep, en heeft geen idee dat je daarna misschien twintig rijen data toevoegt. Bouw hem op uit een rijaantal dat je hebt berekend nadat de data is geschreven, nooit ervoor. Spreidings- en bellendiagrammen overladen dezelfde twee argumenten met andere betekenissen: het categoriebereik levert nu de X-waarden en het waardenbereik levert Y, en de bellenradius komt van een derde verwijzing ingesteld via BubbleSizeRange op de teruggegeven TXLSXChartSeries. Lees de aanroep als "X, Y, grootte" in plaats van "categorieën, waarden" zodra je de kolom-en-staaf-familie verlaat

TXLSXChartType omvat kolom-, staaf-, lijn-, taart-, vlak-, donut-, spreidings-, bellen- en radardiagrammen, wat het dagelijkse rapportagerepertoire dekt. Voor een grafiek op volledige pagina zonder omringend raster geeft Book.AddChartSheet een blad terug waarvan de eigenschap IsChartSheet true is. Het is de .xlsx-tegenhanger van het legacy grafiekblad en draagt dezelfde verwachting: schrijf er geen celinhoud naartoe

Afbeeldingen gaan erin als bytes, en worden gemeten in EMU's

Er zijn twee overloads voor het invoegen van een afbeelding, en ze door elkaar halen is de afbeeldingsbug die het vaakst opduikt in codereview. AddImage(ARow, ACol, AData, AFormat) wil de al gecodeerde afbeeldingsbytes in AData: de ruwe inhoud van een PNG, JPEG, GIF of BMP. Geef het een bestandspad en je hebt een string van veertig bytes opgeslagen die geen enkele viewer kan decoderen, precies de melding over een kapot afbeeldingspictogram die je niet na deployment wilt debuggen. Wanneer de bron een bestand op schijf is, roep dan in plaats daarvan AddImageFromFile aan en laat de bibliotheek de bytes lezen en het formaat voor je classificeren

Dan komt de maatvoering. DrawingML meet niet in pixels; het meet in English Metric Units, waarbij 914400 EMU een inch vormen en, bij 96 DPI, 9525 EMU een pixel vormen. Het TXLSXImage-object biedt WidthEMU en HeightEMU, dus een logo dat op 180 bij 60 pixels moet renderen heeft 1714500 bij 571500 EMU nodig. Zet die conversie in een benoemde constante en reken ertegen. Magic numbers zoals 1714500 verspreid door de code zijn onleesbaar en stilletjes fout de eerste keer dat iemand de doel-DPI verandert. De ankerrij en -kolom zijn overigens 1-gebaseerd, in lijn met de rest van de cel-API in plaats van de 0-gebaseerde EMU-wiskunde

Grafiekbladen en vormen in legacy XLS-bestanden

Aan de BIFF8-kant neemt de rijkere AddChartSheet-overload het grafiektype, de astitels en een open array van TXLSChartSeriesInfo-records, waarbij elk record een naam en een categorie- en waardenbereik als strings draagt. Zwevende vormen zijn een aparte zaak: die gaan op het datawerkblad zelf, via zijn Shapes-verzameling, niet op het grafiekblad

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // interface geteld: geen 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 is een grafiek-substream: roep er nooit celmethoden op aan

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

Twee levensduurdetails zijn hier van belang, en ze trekken in tegengestelde richtingen. TXLSWorkbook wordt vastgehouden via de IXLSWorkbook-interface en is referentiegeteld, dus zelf Free erop aanroepen triggert een dubbele vrijgave. TXLSXWorkbook uit de vorige secties is een gewoon object en moet worden vrijgegeven in een try..finally. Dezelfde codereviewer die een ontbrekende Free aan de XLSX-kant markeert, moet een aanwezige aan de XLS-kant markeren, wat een echt struikelgevaar is wanneer je in dezelfde unit met beide formaten werkt. De vormhelpers zelf zijn uniform: AddRectangle, AddOval en AddLine, met DeleteInRange om een regio met tekeningen te wissen, allemaal verankerd via rij- en kolomparen, dus een sjabloon dat rijen erboven invoegt, verschuift ze mee met het raster

Nog een eigenschap verdient zijn plek op legacy bestanden. TXLSPicture.TransparentColor maskeert een gekozen achtergrondkleur uit een bitmap, en dat is hoe je een niet-rechthoekige stempel (een "Approved"-zegel, een watermerk) over het raster laat vallen in een formaat waarvan de BIFF-rendering PNG-alpha nooit heeft geleerd. Stel de kleur in waartegen de stempel is gemaakt en de omringende rechthoek verdwijnt

Themakleuren overleven geen BIFF8-round-trip

OOXML-tekenvullingen kunnen naar een themakleursleuf wijzen, en dat is waarom een hele .xlsx herkleuren door het thema te wisselen goedkoop is. BIFF8-tekenrecords hebben geen zo'n sleuf. Wanneer HotXLS een themakleur toepast op een XLS-tekening, lost het de kleur op naar een letterlijke RGB-waarde en slaat die op; de themaindex waar hij vandaan kwam is verdwenen op het moment dat het bestand wordt geschreven, en heropenen kan het niet terughalen. Dit treft vooral white-label-rapportagetools, het soort dat hetzelfde gegenereerde document voor veel klanten herbrandt. Houd de thema-naar-RGB-mapping in je eigen configuratie en pas hem elke keer opnieuw toe wanneer je genereert, in plaats van te verwachten dat je hem terugleest uit een opgeslagen .xls

Een gerelateerde beslissing duikt op aan de prestatiekant. De XLS-facade kan worden verteld de tekenlaag helemaal over te slaan bij het parsen wanneer alles wat je van een groot legacy bestand wilt zijn celdata is, door _DisableGraphics op true te zetten, en dat scheelt echte tijd bij bulkleeswerk. Het addertje is permanent: een werkmap die zo geopend is, heeft geen OfficeArt-stream in het geheugen, dus opslaan schrijft de tekeningen weg uit het bestaan. Bewaar de vlag voor alleen-lezen analytics-taken. Het bredere prestatiebeeld staat in onze notities over prestaties van grote werkmappen in HotXLS

Ankers stabiel houden terwijl het raster verandert

Rapporten blijven zelden de grootte die ze bij generatie hadden, en hier betaalt het ankermodel uit de opening zich uit. De structurele bewerkingen van de XLSX-facade (InsertRows, DeleteRows en de kolomequivalenten) verplaatsen de afhankelijke lagen samen met de cellen. Samengevoegde bereiken, hyperlinks, opmerkingen, bevroren deelvensters, filterbereiken, voorwaardelijke opmaak, validaties, tabellen, gedefinieerde namen en, voor dit onderwerp, afbeeldings- en grafiekankers, reizen allemaal samen. Een logo verankerd bij rij 1 blijft bovenaan wanneer er tien rijen eronder ingaan. Een grafiekkader verankerd onder het datablok schuift naar beneden terwijl het blok groeit. Het enige dat niet wordt herschreven is elke bereikstring die je als letterlijke waarde hebt vastgelegd vóórdat de invoeging gebeurde, want dat is gewoon tekst waar de bibliotheek geen reden heeft om op terug te komen. Dat legt de veilige volgorde vast voor een sjabloonvulling: schrijf en vorm de data eerst, en maak grafieken en plaats afbeeldingen als laatste pas, met elke bereikstring afgeleid van de rijaantallen die je hebt na de invoegingen, niet ervoor

Twee kleinere tools maken de plaatsingskit compleet. TXLSTextBox.SetArea aan de XLS-kant herverankert een bestaand tekstvak of auto-vorm naar een nieuwe celrechthoek, wat beter is dan verwijderen en opnieuw aanmaken wanneer een voettekstblok verschuift. En de bitmap-overload van AddPicture neemt een levende TBitmap met een optionele transparantievlag, zodat alles wat je eigen VCL-code kan tekenen (een meter, een sparkline-strip, een grafiektype dat de native lijst niet biedt) rechtstreeks in het blad kan worden gestempeld zonder eerst een tijdelijk bestand te schrijven

Grafieken en afbeeldingen zijn bijna altijd de afwerkingslaag op een al gestructureerd rapport, en dat is waarom het voorwerk bepaalt of ze netjes landen. Het vullen van de data waar een grafiek naar zal verwijzen wordt behandeld in sjabloongestuurde rapportgeneratie, en het raster onder je ankers stabiel houden is het onderwerp van samengevoegde cellen en lay-outcontrole. Volledige klasse- en methodedocumentatie staat op de productpagina van HotXLS Delphi Component