Diagrame, imagini și obiecte desenate în Delphi cu HotXLS

Orice element care plutește deasupra grilei unei foi de calcul (un grafic, un logo, o ștampilă, o casetă explicativă) este un obiect de desen, iar un obiect de desen este definit de două lucruri: ce anume este și unde este ancorat. Ancora este partea la care lumea greșește. Un grafic nu locuiește într-o celulă; stă într-un dreptunghi fixat pe un interval de rânduri și coloane, iar datele pe care le reprezintă sunt un set separat de referințe A1 despre care ancora nu știe nimic. Muți cadrul și reprezentarea rămâne pe loc. Inserezi rânduri sub el și cadrul coboară odată cu ele. Ținerea separată și limpede a acestor două sisteme de coordonate este cea mai mare parte din ceea ce face codul de desenare să se comporte cum trebuie

HotXLS este o bibliotecă nativă Object Pascal care citește și scrie XLS și XLSX fără automatizare Excel și poartă cu sine două modele de desenare separate, pentru că cele două formate de fișier stochează desenele diferit. Formatul BIFF8 .xls păstrează graficele pe foi dedicate proprii, iar formele plutitoare într-un flux OfficeArt atașat foii de calcul. Formatul OOXML .xlsx poate încorpora un grafic în interiorul grilei, ancorat la un dreptunghi de celule, alături de același tip de imagini și forme plutitoare. Modelul de obiecte oglindește această despărțire, iar eșecurile despre care merită scris vin toate din aplicarea regulilor unui format asupra celuilalt

Ce container poate găzdui ce

Alegerea containerului trebuie făcută înaintea oricărui cod de grafic, pentru că tipurile de obiecte disponibile diferă între cele două:

• XLS (BIFF8): graficele stau pe foi de grafic dedicate, create prin AddChartSheet pe colecția Sheets. Imaginile, casetele de text, dreptunghiurile, ovalurile și liniile sunt forme OfficeArt gestionate prin colecția Shapes a foii de calcul. Nu există niciun API pentru încorporarea unui grafic în grila unei foi de calcul obișnuite
• XLSX (OOXML): graficele pot fi încorporate direct într-o foaie de calcul cu TXLSXWorksheet.AddChart, ancorate la un dreptunghi de celule, sau pot fi plasate pe o foaie de grafic dedicată cu TXLSXWorkbook.AddChartSheet. Imaginile intră cu AddImage sau AddImageFromFile, iar etichetele plutitoare cu AddTextBox

Așadar, o cerință formulată drept „o foaie de tablou de bord cu graficul lângă cifre” este în realitate o cerință pentru .xlsx. O poți aproxima în .xls doar împingând graficul pe o foaie proprie, ceea ce schimbă felul în care utilizatorul navighează prin fișier și schimbă felul în care trebuie să se comporte codul tău. Foaia returnată de AddChartSheet din partea XLS este un subflux de grafic, nu o grilă: scrierea în ea cu Cells.Item produce un flux de desenare inconsistent, care se generează fără eroare și pe care Excel îl aruncă apoi la deschidere. Graficul pur și simplu dispare, iar nimic din jurnalul de generare nu spune de ce. Tratează foaia returnată ca fiind exclusiv pentru grafic și întreaga categorie de sesizări „lipsește graficul” se stinge

Încorporarea unui grafic într-o foaie de calcul XLSX

Calea XLSX este cea cu spațiu de manevră și este locul în care cele două sisteme de coordonate din deschidere devin concrete. Dreptunghiul de ancorare transmis lui AddChart este exprimat în rânduri și coloane ale foii de calcul și fixează locul unde stă cadrul graficului. Datele seriei sunt exprimate ca referințe A1 absolute care includ numele foii. Cele două sunt independente: poți muta cadrul în celălalt capăt al foii și tot aceleași celule le va reprezenta

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;

    // Cadru ancorat la rândurile 6..22, coloanele 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;

Argumentul care mușcă este șirul de interval predat lui AddSeries. Este un literal, capturat în chiar momentul apelului, și habar nu are că ai putea adăuga după aceea încă douăzeci de rânduri de date. Construiește-l dintr-un număr de rânduri calculat după ce datele au fost scrise, niciodată înainte. Graficele scatter și bubble încarcă aceleași două argumente cu înțelesuri diferite: intervalul de categorii furnizează acum valorile X, intervalul de valori furnizează Y, iar raza bulei vine dintr-o a treia referință, stabilită prin BubbleSizeRange pe obiectul TXLSXChartSeries returnat. Citește apelul ca „X, Y, dimensiune”, nu ca „categorii, valori”, din clipa în care ieși din familia coloanelor și barelor

TXLSXChartType acoperă graficele de tip coloană, bară, linie, structură radială, arie, inel, scatter, bubble și radar, adică repertoriul de zi cu zi al raportării. Pentru un grafic pe pagină întreagă, fără grilă în jur, Book.AddChartSheet returnează o foaie a cărei proprietate IsChartSheet este adevărată. Este echivalentul .xlsx al foii de grafic din formatul vechi și vine cu aceeași așteptare: nu îi scrie conținut în celule

Imaginile intră ca octeți, iar dimensionarea lor se face în EMU

Există două supraîncărcări pentru inserarea unei imagini, iar confundarea lor este eroarea de imagine care apare cel mai des la analiza codului. AddImage(ARow, ACol, AData, AFormat) vrea în AData octeții deja codificați ai imaginii: conținutul brut al unui PNG, JPEG, GIF sau BMP. Dă-i o cale de fișier și ai stocat un șir de patruzeci de octeți pe care niciun vizualizator nu îl poate decoda, adică exact sesizarea cu pictograma de imagine ruptă pe care nu vrei să o depanezi după punerea în producție. Când sursa este un fișier de pe disc, apelează în schimb AddImageFromFile și lasă biblioteca să citească octeții și să clasifice formatul în locul tău

Apoi vine dimensionarea. DrawingML nu măsoară în pixeli; măsoară în English Metric Units, unde 914400 EMU fac un inch și, la 96 DPI, 9525 EMU fac un pixel. Obiectul TXLSXImage expune WidthEMU și HeightEMU, așa că un logo menit să se afișeze la 180 pe 60 de pixeli are nevoie de 1714500 pe 571500 EMU. Pune conversia aceea într-o constantă cu nume și calculează pornind de la ea. Numerele magice de tipul 1714500 împrăștiate prin cod sunt ilizibile și devin discret greșite prima dată când cineva schimbă DPI-ul țintă. Rândul și coloana de ancorare sunt, de altfel, numerotate de la 1, la fel ca restul API-ului de celule, nu de la 0 ca aritmetica EMU

Foi de grafic și forme în fișierele XLS vechi

În partea BIFF8, supraîncărcarea mai bogată a lui AddChartSheet primește tipul graficului, titlurile axelor și un tablou deschis de înregistrări TXLSChartSeriesInfo, în care fiecare înregistrare conține un nume și câte un interval de categorii și de valori sub formă de șiruri. Formele plutitoare sunt o chestiune separată: ele merg pe foaia de calcul cu date, prin colecția ei Shapes, nu pe foaia de grafic

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // numărat prin interfață: nu apela 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 este un subflux de grafic: nu apela niciodată metode de celulă pe el

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

Aici contează două detalii de durată de viață, iar ele trag în direcții opuse. TXLSWorkbook este ținut prin interfața IXLSWorkbook și este numărat prin referință, așa că apelarea lui Free pe cont propriu declanșează o dublă eliberare. TXLSXWorkbook din secțiunile anterioare este un obiect obișnuit și trebuie eliberat într-un try..finally. Același recenzent de cod care semnalează un Free lipsă în partea XLSX trebuie să semnaleze unul prezent în partea XLS, ceea ce este o capcană reală când lucrezi cu ambele formate în aceeași unitate. Metodele ajutătoare pentru forme sunt în schimb uniforme: AddRectangle, AddOval și AddLine, plus DeleteInRange pentru a curăța de desene o regiune, se ancorează toate prin perechi de rând și coloană, așa că un șablon care inserează rânduri deasupra lor le deplasează odată cu grila

Încă o proprietate își merită locul pe fișierele vechi. TXLSPicture.TransparentColor maschează o culoare de fundal aleasă dintr-o imagine bitmap, iar așa poți așeza o ștampilă nedreptunghiulară (un sigiliu „Aprobat”, un filigran) peste grilă, într-un format a cărui randare BIFF nu a apucat niciodată să învețe canalul alfa PNG. Setează culoarea pe fundalul căreia a fost creată ștampila și dreptunghiul din jur dispare

Culorile de temă nu supraviețuiesc unui dus-întors BIFF8

Umplerile de desen OOXML pot trimite către un slot de culoare de temă, motiv pentru care recolorarea unui întreg .xlsx prin schimbarea temei este ieftină. Înregistrările de desen BIFF8 nu au un asemenea slot. Când HotXLS aplică o culoare de temă unui desen XLS, rezolvă culoarea la o valoare RGB literală și o stochează pe aceea; indexul de temă din care provine dispare în clipa în care fișierul este scris, iar redeschiderea nu îl mai poate recupera. Lucrul acesta prinde mai ales instrumentele de raportare white-label, genul care personalizează sub altă marcă același document generat pentru mulți clienți. Ține maparea temă-către-RGB în propria ta configurație și reaplic-o la fiecare generare, în loc să te aștepți să o citești înapoi dintr-un .xls salvat

O decizie înrudită apare pe partea de performanță. Fațadei XLS i se poate cere să sară complet peste analizarea stratului de desenare atunci când tot ce vrei de la un fișier vechi și mare sunt datele din celule, setând _DisableGraphics pe true, iar asta taie timp real din citirile în masă. Capcana este definitivă: un registru de lucru deschis astfel nu are niciun flux OfficeArt în memorie, așa că salvarea lui scoate desenele din existență. Rezervă indicatorul pentru joburile de analiză exclusiv în citire. Imaginea mai largă a performanței se află în notele noastre despre performanța registrelor de lucru mari în HotXLS

Menținerea stabilă a ancorelor în timp ce grila se schimbă

Rapoartele rămân rareori la dimensiunea la care au fost generate, iar aici se răscumpără modelul de ancorare din deschidere. Operațiile structurale ale fațadei XLSX (InsertRows, DeleteRows și echivalentele pentru coloane) deplasează straturile dependente odată cu celulele. Regiunile îmbinate, hyperlinkurile, comentariile, panourile înghețate, intervalele de filtrare, formatările condiționate, validările, tabelele, numele definite și, pentru subiectul de față, ancorele de imagine și de grafic călătoresc toate împreună. Un logo ancorat la rândul 1 rămâne sus atunci când sub el intră zece rânduri. Un cadru de grafic ancorat sub blocul de date coboară pe măsură ce blocul crește. Singurul lucru care nu este rescris este orice șir de interval capturat ca literal înainte să se producă inserarea, de vreme ce este doar text pe care biblioteca nu are motiv să îl revadă. Asta fixează ordinea sigură pentru umplerea unui șablon: scrie și remodelează întâi datele, apoi creează graficele și așază imaginile într-o trecere finală, cu fiecare șir de interval derivat din numărul de rânduri de după inserări, nu de dinainte

Două unelte mai mici completează trusa de plasare. TXLSTextBox.SetArea din partea XLS reancorează o casetă de text sau o formă automată existentă pe un nou dreptunghi de celule, ceea ce bate varianta cu ștergere și recreare atunci când un bloc de subsol se deplasează. Iar supraîncărcarea bitmap a lui AddPicture primește un TBitmap viu, cu un indicator opțional de transparență, așa că orice poate desena propriul tău cod VCL (un cadran, o bandă de sparkline-uri, un tip de grafic pe care lista nativă nu îl oferă) poate fi imprimat direct în foaie fără să scrii mai întâi un fișier temporar

Graficele și imaginile sunt aproape întotdeauna stratul de finisare al unui raport deja structurat, motiv pentru care temelia decide dacă ele aterizează curat. Umplerea datelor la care se va referi un grafic este tratată în generarea de rapoarte pe bază de șabloane, iar menținerea stabilă a grilei de sub ancorele tale este subiectul articolului despre celulele îmbinate și controlul aspectului. Documentația completă a claselor și metodelor stă pe pagina de produs HotXLS Delphi Component