HotXLS: charts, images, and drawing objects in Delphi

Allt som flyter över ett kalkylbladsrutnät (ett diagram, en logotyp, en stämpel, en pratbubbelruta) är ett ritobjekt, och ett ritobjekt definieras av två saker: vad det är, och var det är förankrat. Ankaret är delen folk gör fel. Ett diagram bor inte i en cell; det sitter i en rektangel fäst vid ett spann av rader och kolumner, och datan det plottar är en separat uppsättning A1-referenser som ankaret inte vet något om. Flytta ramen och plottningen ligger kvar. Infoga rader under den och ramen glider ner tillsammans med dem. Att hålla de två koordinatsystemen isär är det mesta av vad som får ritkod att bete sig

HotXLS är ett nativt Object Pascal-bibliotek som läser och skriver XLS och XLSX utan Excel-automation, och det bär två separata ritmodeller eftersom de två filformaten lagrar ritningar olika. BIFF8-formatet .xls håller diagram på egna dedikerade blad och flytande former i en OfficeArt-ström fäst vid kalkylbladet. OOXML-formatet .xlsx kan bädda in ett diagram i rutnätet, förankrat till en cellrektangel, tillsammans med samma sorts flytande bilder och former. Objektmodellen speglar den uppdelningen, och de fel som är värda att skriva om kommer alla från att tillämpa ett formats regler på det andra

Vilken behållare kan hålla vad

Valet av behållare måste komma före all diagramkod, eftersom de tillgängliga objekttyperna skiljer sig mellan de två:

• XLS (BIFF8): diagram bor på dedikerade diagramblad skapade via AddChartSheet på samlingen Sheets. Bilder, textrutor, rektanglar, ovaler och linjer är OfficeArt-former hanterade via kalkylbladets samling Shapes. Det finns inget API för att bädda in ett diagram i ett vanligt kalkylbladsrutnät
• XLSX (OOXML): diagram kan bäddas in direkt i ett kalkylblad med TXLSXWorksheet.AddChart, förankrat till en cellrektangel, eller placeras på ett dedikerat diagramblad med TXLSXWorkbook.AddChartSheet. Bilder går in med AddImage eller AddImageFromFile, och flytande etiketter med AddTextBox

Så ett krav formulerat som "ett dashboard-blad med diagrammet bredvid siffrorna" är egentligen ett krav på .xlsx. Du kan bara approximera det i .xls genom att skjuta diagrammet till sitt eget blad, vilket ändrar hur användaren navigerar filen och ändrar hur din kod måste bete sig. Bladet som returneras av XLS-sidans AddChartSheet är en diagramunderström, inte ett rutnät: att skriva till det med Cells.Item producerar en inkonsekvent ritström som genereras utan fel och som Excel sedan kastar bort vid öppning. Diagrammet försvinner helt enkelt, och inget i byggloggen säger varför. Behandla det returnerade bladet som enbart diagram och hela klassen av "saknat diagram"-rapporter försvinner

Att bädda in ett diagram i ett XLSX-kalkylblad

XLSX-vägen är den med utrymme att manövrera, och det är där de två koordinatsystemen från inledningen blir konkreta. Ankarrektangeln som skickas till AddChart uttrycks i kalkylbladsrader och -kolumner och fastställer var diagramramen sitter. Seriedatan uttrycks som absoluta A1-referenser som inkluderar bladnamnet. De är oberoende: du kan flytta ramen till andra sidan bladet och den plottar fortfarande samma 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;

    // Ram förankrad vid rader 6..22, kolumner 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 är områdessträngen given till AddSeries. Den är en literal, fångad i ögonblicket av anropet, och den har ingen aning om att du kanske lägger till tjugo rader data till efteråt. Bygg den från ett radantal du beräknade efter att datan skrevs, aldrig innan. Punkt- och bubbeldiagram överbelastar samma två argument med olika betydelser: kategoriområdet ger nu X-värdena och värdeområdet ger Y, och bubbelradien kommer från en tredje referens satt via BubbleSizeRange på det returnerade TXLSXChartSeries. Läs anropet som "X, Y, storlek" snarare än "kategorier, värden" så fort du lämnar kolumn- och stapelfamiljen

TXLSXChartType spänner över kolumn-, stapel-, linje-, tårtdiagram, yta, ring, punkt-, bubbel- och radardiagram, vilket täcker det vardagliga rapporteringsrepertoaren. För ett diagram som tar en hel sida utan omgivande rutnät returnerar Book.AddChartSheet ett blad vars egenskap IsChartSheet är sant. Det är .xlsx-motsvarigheten till det äldre diagrambladet och bär samma förväntning: skriv inte cellinnehåll till det

Bilder går in som byte, och de storleksmäts i EMU

Det finns två överlagringar för att infoga en bild, och att blanda ihop dem är bildbuggen som dyker upp mest i kodgranskning. AddImage(ARow, ACol, AData, AFormat) vill ha de redan kodade bildbyten i AData: det råa innehållet i en PNG, JPEG, GIF eller BMP. Ge den en filsökväg och du har lagrat en fyrtio-byte sträng ingen visare kan avkoda, vilket är precis den trasig-bild-ikon-rapport du inte vill felsöka efter driftsättning. När källan är en fil på disk, anropa AddImageFromFile i stället och låt biblioteket läsa byten och klassificera formatet åt dig

Sedan kommer storleksbestämningen. DrawingML mäter inte i pixlar; det mäter i English Metric Units, där 914400 EMU utgör en tum och, vid 96 DPI, 9525 EMU utgör en pixel. Objektet TXLSXImage exponerar WidthEMU och HeightEMU, så en logotyp avsedd att renderas 180 gånger 60 pixlar behöver 1714500 gånger 571500 EMU. Lägg den omvandlingen i en namngiven konstant och beräkna mot den. Magiska tal som 1714500 utspridda genom koden är oläsliga och tyst fel den första gången någon ändrar mål-DPI:n. Ankarraden och -kolumnen är för övrigt 1-baserade, i linje med resten av cell-API:et snarare än den 0-baserade EMU-matematiken

Diagramblad och former i äldre XLS-filer

På BIFF8-sidan tar den rikare överlagringen av AddChartSheet diagramtypen, axeltitlarna, och en öppen array av TXLSChartSeriesInfo-poster, där varje post bär ett namn och ett kategori- och värdeområde som strängar. Flytande former är en separat sak: de går på själva databladet, via dess samling Shapes, inte på diagrambladet

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // gränssnittsräknat: anropa inte 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 är en diagramunderström: anropa aldrig cellmetoder på den

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

Två livstidsdetaljer spelar roll här, och de drar åt motsatta håll. TXLSWorkbook hålls via gränssnittet IXLSWorkbook och är referensräknat, så att anropa Free på det själv utlöser en dubbel frigöring. TXLSXWorkbook från tidigare avsnitt är ett vanligt objekt och måste frigöras i ett try..finally. Samma kodgranskare som flaggar en saknad Free på XLSX-sidan måste flagga en närvarande på XLS-sidan, vilket är en genuin snubbelrisk när du arbetar i båda formaten i samma enhet. Formhjälparna själva är enhetliga: AddRectangle, AddOval och AddLine, med DeleteInRange för att rensa ett område av ritningar, förankrar alla via rad- och kolumnpar, så en mall som infogar rader ovanför dem förskjuter dem tillsammans med rutnätet

Ytterligare en egenskap förtjänar sin plats på äldre filer. TXLSPicture.TransparentColor maskerar bort en vald bakgrundsfärg från en bitmapp, vilket är hur du släpper en icke-rektangulär stämpel (ett "Godkänt"-sigill, en vattenstämpel) över rutnätet i ett format vars BIFF-rendering aldrig lärde sig PNG-alfa. Sätt färgen stämpeln skapades mot och den omgivande rektangeln försvinner

Temafärger överlever inte en BIFF8-tur-och-retur

OOXML-ritfyllningar kan peka mot en temafärgplats, vilket är varför omfärgning av en hel .xlsx genom att byta dess tema är billigt. BIFF8-ritposter har ingen sådan plats. När HotXLS applicerar en temafärg på en XLS-ritning löser den upp färgen till ett bokstavligt RGB-värde och lagrar det; temaindexet den kom från är borta i samma stund filen skrivs, och att öppna den igen kan inte återfå det. Det här fångar vitmärkta rapporteringsverktyg i synnerhet, den sortens som ommärker samma genererade dokument för många kunder. Håll tema-till-RGB-mappningen i din egen konfiguration och applicera den på nytt varje gång du genererar, i stället för att förvänta dig att läsa tillbaka den ur en sparad .xls

Ett relaterat beslut dyker upp på prestandasidan. XLS-fasaden kan bes hoppa över att tolka ritlagret helt och hållet när allt du vill ha från en stor äldre fil är dess celldata, genom att sätta _DisableGraphics till true, och det skalar av verklig tid från bulkläsningar. Fångsten är permanent: en arbetsbok öppnad på det sättet har ingen OfficeArt-ström i minnet, så att spara den skriver bort ritningarna för gott. Reservera flaggan för skrivskyddade analysjobb. Den bredare prestandabilden finns i våra anteckningar om prestanda för stora arbetsböcker i HotXLS

Att hålla ankare stabila medan rutnätet ändras

Rapporter förblir sällan den storlek de genererades i, och det är här ankarmodellen från inledningen lönar sig. XLSX-fasadens strukturella operationer (InsertRows, DeleteRows och kolumnmotsvarigheterna) flyttar de beroende lagren tillsammans med cellerna. Sammanslagna områden, hyperlänkar, kommentarer, frysta rutor, filterintervall, villkorlig formatering, valideringar, tabeller, definierade namn och, för det här ämnet, bild- och diagramankare färdas alla tillsammans. En logotyp förankrad vid rad 1 stannar högst upp när tio rader går in nedanför den. En diagramram förankrad under datablocket glider ner allteftersom blocket växer. Det enda som inte skrivs om är alla områdessträngar du fångade som en literal innan infogningen skedde, eftersom det bara är text biblioteket inte har någon anledning att återbesöka. Det fastställer den säkra ordningen för en mallifyllning: skriv och omforma datan först, och skapa diagram och placera bilder som det sista passet, med varje områdessträng härledd från radantalen du har efter infogningarna, inte innan

Två mindre verktyg avrundar placeringsutrustningen. TXLSTextBox.SetArea på XLS-sidan förankrar om en befintlig textruta eller autoform till en ny cellrektangel, vilket slår att ta bort och återskapa den när ett sidfotsblock flyttas. Och bitmappöverlagringen av AddPicture tar en levande TBitmap med en valfri transparensflagga, så allt din egen VCL-kod kan rita (en mätare, en sparkline-remsa, en diagramtyp den nativa listan inte erbjuder) kan stämplas rakt in i bladet utan att först skriva en temporär fil

Diagram och bilder är nästan alltid det avslutande lagret på en redan strukturerad rapport, vilket är varför grundarbetet avgör om de landar rent. Att fylla i datan ett diagram kommer att referera täcks i mallbaserad rapportgenerering, och att hålla rutnätet stabilt under dina ankare är ämnet för sammanslagna celler och layoutkontroll. Fullständig klass- och metoddokumentation finns på produktsidan för HotXLS Delphi Component