Grafy, obrázky a kreslené objekty HotXLS v Delphi

Cokoli, co se vznáší nad mřížkou listu (graf, logo, razítko, box s popiskem), je kresebný objekt (drawing object), a kresebný objekt je definovaný dvěma věcmi: čím je a kde je ukotvený. Ukotvení je ta část, kterou lidé pletou. Graf nežije v buňce; sedí v obdélníku připnutém k rozsahu řádků a sloupců, a data, která vykresluje, jsou samostatná sada odkazů A1, o kterých ukotvení vůbec neví. Posunete rámeček a vykreslení zůstane na místě. Vložíte řádky pod něj a rámeček se s nimi posune dolů. Udržet tyto dva souřadnicové systémy oddělené je většina toho, co dělá kresebný kód spolehlivým

HotXLS je nativní knihovna v Object Pascalu, která čte a zapisuje XLS a XLSX bez automatizace Excelu, a nese dva oddělené kresebné modely, protože oba formáty souborů ukládají kresby jinak. Formát BIFF8 .xls drží grafy na vlastních vyhrazených listech a plovoucí tvary v proudu OfficeArt připojeném k listu. Formát OOXML .xlsx umí vložit graf přímo do mřížky, ukotvený k obdélníku buněk, vedle stejného druhu plovoucích obrázků a tvarů. Objektový model tento rozdíl zrcadlí, a selhání, o kterých stojí za to psát, všechna pramení z aplikace pravidel jednoho formátu na ten druhý

Který kontejner umí obsahovat co

Volba kontejneru musí přijít dřív, než jakýkoli kód pro grafy, protože dostupné typy objektů se mezi těmito dvěma liší:

• XLS (BIFF8): grafy žijí na vyhrazených listech grafů vytvořených přes AddChartSheet na kolekci Sheets. Obrázky, textová pole, obdélníky, ovály a čáry jsou tvary OfficeArt spravované přes kolekci Shapes listu. Neexistuje žádné API pro vložení grafu do běžné mřížky listu
• XLSX (OOXML): grafy lze vložit přímo do listu pomocí TXLSXWorksheet.AddChart, ukotvené k obdélníku buněk, nebo umístit na vyhrazený list grafu pomocí TXLSXWorkbook.AddChartSheet. Obrázky se vkládají přes AddImage nebo AddImageFromFile, plovoucí popisky přes AddTextBox

Takže požadavek formulovaný jako „list dashboardu s grafem vedle čísel" je ve skutečnosti požadavek na .xlsx. V .xls to lze jen přiblížit tím, že graf posunete na jeho vlastní list, což mění, jak se uživatel v souboru pohybuje, a mění to, jak se musí chovat váš kód. List vrácený XLS variantou AddChartSheet je substream grafu, ne mřížka: zápis do něj přes Cells.Item vyprodukuje nekonzistentní kresebný stream, který se vygeneruje bez chyby a který pak Excel při otevření zahodí. Graf prostě zmizí a nic v buildovacím logu neřekne proč. Zacházejte s vráceným listem jen jako s grafem a celá třída hlášení o „chybějícím grafu" zmizí

Vložení grafu do listu XLSX

Cesta XLSX je ta s prostorem na manévrování, a je to místo, kde se dva souřadnicové systémy z úvodu stávají konkrétními. Obdélník ukotvení předaný do AddChart je vyjádřený v řádcích a sloupcích listu a určuje, kde sedí rámeček grafu. Data série jsou vyjádřená jako absolutní odkazy A1 obsahující název listu. Jsou na sobě nezávislé: rámeček můžete přesunout na druhý konec listu a pořád vykresluje stejné buňky

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;

    // Rámeček ukotvený na řádky 6..22, sloupce 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;

Argument, který kouše, je řetězec rozsahu předaný do AddSeries. Je to literál, zachycený ve chvíli volání, a nemá ponětí, že byste mohli později připojit dalších dvacet řádků dat. Sestavte jej z počtu řádků, který jste spočítali po zapsání dat, nikdy před tím. Bodové (scatter) a bublinové grafy přetěžují stejné dva argumenty jiným významem: rozsah kategorií teď dodává hodnoty X a rozsah hodnot dodává Y, a poloměr bubliny pochází ze třetího odkazu nastaveného přes BubbleSizeRange na vráceném TXLSXChartSeries. Jakmile opustíte rodinu sloupcových a pruhových grafů, čtěte volání jako „X, Y, velikost", ne „kategorie, hodnoty"

TXLSXChartType pokrývá sloupcové, pruhové, čárové, koláčové, plošné, prstencové, bodové, bublinové a paprskové (radar) grafy, což pokrývá běžný reportovací repertoár. Pro graf na celou stránku bez okolní mřížky vrátí Book.AddChartSheet list, jehož vlastnost IsChartSheet je true. Je to protějšek staršího listu grafu ve formátu .xlsx a nese stejné očekávání: nezapisujte do něj obsah buněk

Obrázky se vkládají jako bajty a jejich velikost se udává v EMU

Pro vložení obrázku existují dvě přetížení a jejich záměna je chyba s obrázky, která se v code review objevuje nejčastěji. AddImage(ARow, ACol, AData, AFormat) chce v AData už zakódované bajty obrázku: syrový obsah PNG, JPEG, GIF, nebo BMP. Předejte mu cestu k souboru a uložili jste čtyřicetibajtový řetězec, který žádný prohlížeč nedekóduje, což je přesně to hlášení o ikoně rozbitého obrázku, které nechcete ladit po nasazení. Když je zdrojem soubor na disku, zavolejte místo toho AddImageFromFile a nechte knihovnu, ať za vás přečte bajty a klasifikuje formát

Pak přichází velikost. DrawingML neměří v pixelech; měří v English Metric Units, kde 914400 EMU tvoří jeden palec a při 96 DPI tvoří 9525 EMU jeden pixel. Objekt TXLSXImage zpřístupňuje WidthEMU a HeightEMU, takže logo, které se má vykreslit jako 180 na 60 pixelů, potřebuje 1714500 na 571500 EMU. Dejte si tento převod do pojmenované konstanty a počítejte proti ní. Magická čísla jako 1714500 rozházená po kódu jsou nečitelná a potichu chybná, jakmile někdo změní cílové DPI. Řádek a sloupec ukotvení jsou mimochodem 1-based, ve shodě se zbytkem API pro buňky, ne s 0-based matematikou EMU

Listy grafů a tvary ve starších souborech XLS

Na straně BIFF8 přebírá bohatší přetížení AddChartSheet typ grafu, názvy os a otevřené pole záznamů TXLSChartSeriesInfo, kde každý záznam nese název a rozsahy kategorií a hodnot jako řetězce. Plovoucí tvary jsou samostatná záležitost: jdou na samotný datový list, přes jeho kolekci Shapes, ne na list grafu

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // počítáno přes rozhraní: nevolejte 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 je substream grafu: nikdy na něj nevolejte metody buněk

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

Tady záleží na dvou detailech životního cyklu, a táhnou opačným směrem. TXLSWorkbook je držený přes rozhraní IXLSWorkbook a je počítaný referencemi, takže zavolat na něj Free sami spustí dvojité uvolnění. TXLSXWorkbook z předchozích oddílů je obyčejný objekt a musí se uvolnit v try..finally. Stejný recenzent kódu, který nahlásí chybějící Free na straně XLSX, musí nahlásit přítomný na straně XLS, což je opravdová past, když pracujete v obou formátech ve stejné jednotce. Samotné pomocníky pro tvary jsou jednotné: AddRectangle, AddOval a AddLine, s DeleteInRange pro vyčištění oblasti kreseb, všechny se ukotvují dvojicemi řádek a sloupec, takže šablona, která nad nimi vloží řádky, je posune spolu s mřížkou

Jedna další vlastnost se u starších souborů vyplácí. TXLSPicture.TransparentColor vymaskuje z bitmapy zvolenou barvu pozadí, čímž přes mřížku vložíte razítko s neobdélníkovým tvarem (pečeť „Approved", vodoznak) ve formátu, jehož vykreslování BIFF se nikdy nenaučilo alfa kanál PNG. Nastavte barvu, proti které bylo razítko vytvořené, a okolní obdélník zmizí

Barvy motivu nepřežijí zápis a znovunačtení BIFF8

Výplně kreseb OOXML mohou ukazovat na slot barvy motivu, což je důvod, proč je přebarvení celého .xlsx výměnou motivu levné. Záznamy kreseb BIFF8 žádný takový slot nemají. Když HotXLS aplikuje barvu motivu na kresbu XLS, přeloží barvu na doslovnou hodnotu RGB a tu uloží; index motivu, ze kterého pocházela, je pryč ve chvíli, kdy se soubor zapíše, a opětovné otevření jej nedokáže obnovit. Tohle chytá zvlášť nástroje pro white-label reporting, ten druh, který přeznačkovává stejný vygenerovaný dokument pro mnoho zákazníků. Držte mapování motiv-na-RGB ve vlastní konfiguraci a znovu jej aplikujte při každém generování, místo abyste čekali, že jej přečtete zpátky z uloženého .xls

Související rozhodnutí se objevuje na straně výkonu. Fasádě XLS lze nastavením _DisableGraphics na true říct, ať kresebnou vrstvu úplně přeskočí, když jediné, co ze staršího velkého souboru chcete, jsou data buněk, a to ubere skutečný čas z hromadného čtení. Háček je trvalý: sešit otevřený tímto způsobem nemá v paměti žádný proud OfficeArt, takže jeho uložení kresby vymaže z existence. Vyhraďte tento příznak pro úlohy čistě pro čtení a analytiku. Širší obraz výkonu je v našich poznámkách o výkonu velkých sešitů v HotXLS

Udržení stabilních ukotvení, zatímco se mřížka mění

Reporty jen zřídka zůstávají té velikosti, ve které byly vygenerovány, a tady se vyplácí model ukotvení z úvodu. Strukturální operace fasády XLSX (InsertRows, DeleteRows a sloupcové ekvivalenty) posouvají závislé vrstvy spolu s buňkami. Sloučené oblasti, hypertextové odkazy, komentáře, ukotvené příčky, rozsahy filtrů, podmíněné formáty, validace, tabulky, definované názvy, a pro toto téma i ukotvení obrázků a grafů, cestují všechny společně. Logo ukotvené na řádku 1 zůstane nahoře, když se pod něj vloží deset řádků. Rámeček grafu ukotvený pod datovým blokem se posune dolů, jak blok roste. Jediná věc, která se nepřepíše, je jakýkoli řetězec rozsahu, který jste zachytili jako literál před tím, než k vložení došlo, protože je to jen text, ke kterému knihovna nemá důvod se vracet. Tím se určuje bezpečné pořadí pro vyplnění šablony: nejdřív zapište a přetvarujte data, a vytváření grafů a umísťování obrázků udělejte jako poslední průchod, se všemi řetězci rozsahu odvozenými z počtu řádků, které máte po vloženích, ne před nimi

Dva menší nástroje dokončují sadu pro umísťování. TXLSTextBox.SetArea na straně XLS znovu ukotví existující textové pole nebo automatický tvar na nový obdélník buněk, což je lepší než jej mazat a znovu vytvářet, když se posune blok patičky. A bitmapové přetížení AddPicture přijímá živý TBitmap s volitelným příznakem průhlednosti, takže cokoli, co dokáže nakreslit váš vlastní kód VCL (ukazatel, pruh sparkline, typ grafu, který nativní seznam nenabízí), lze vrazit rovnou do listu, aniž byste nejdřív museli zapisovat dočasný soubor

Grafy a obrázky jsou téměř vždy dokončovací vrstva na už strukturovaném reportu, a proto rozhoduje o tom, zda dosednou čistě, práce udělaná předem. Vyplňování dat, na která se bude graf odkazovat, pokrývá generování reportů řízené šablonou, a udržení mřížky stabilní pod vašimi ukotveními je tématem sloučených buněk a řízení rozvržení. Úplná dokumentace tříd a metod žije na produktové stránce HotXLS Delphi Component