HotXLS: charts, images, and drawing objects in Delphi

Qualsiasi cosa fluttui sopra la griglia di un foglio di lavoro (un grafico, un logo, un timbro, un riquadro di richiamo) è un oggetto di disegno, e un oggetto di disegno è definito da due cose: cos'è, e dove è ancorato. L'ancora è la parte che la gente sbaglia. Un grafico non vive in una cella; siede in un rettangolo fissato a un intervallo di righe e colonne, e i dati che traccia sono un insieme separato di riferimenti A1 di cui l'ancora non sa nulla. Sposta la cornice e il tracciato resta fermo. Inserisci righe sotto di esso e la cornice scivola giù insieme a loro. Tenere separati quei due sistemi di coordinate è gran parte di ciò che fa comportare bene il codice di disegno

HotXLS è una libreria Object Pascal nativa che legge e scrive XLS e XLSX senza automazione Excel, e porta con sé due modelli di disegno separati perché i due formati di file memorizzano i disegni in modo diverso. Il formato BIFF8 .xls tiene i grafici su propri fogli dedicati e le forme fluttuanti in uno stream OfficeArt attaccato al foglio di lavoro. Il formato OOXML .xlsx può incorporare un grafico dentro la griglia, ancorato a un rettangolo di celle, insieme allo stesso tipo di immagini e forme fluttuanti. Il modello a oggetti rispecchia questa separazione, e i fallimenti che vale la pena descrivere derivano tutti dall'applicare le regole di un formato all'altro

Quale contenitore può contenere cosa

La scelta del contenitore deve precedere qualsiasi codice per i grafici, perché i tipi di oggetto disponibili differiscono tra i due:

• XLS (BIFF8): i grafici vivono su fogli grafico dedicati creati tramite AddChartSheet sulla collezione Sheets. Immagini, caselle di testo, rettangoli, ovali e linee sono forme OfficeArt gestite tramite la collezione Shapes del foglio di lavoro. Non esiste alcuna API per incorporare un grafico dentro una normale griglia di foglio di lavoro
• XLSX (OOXML): i grafici possono essere incorporati direttamente in un foglio di lavoro con TXLSXWorksheet.AddChart, ancorati a un rettangolo di celle, oppure collocati su un foglio grafico dedicato con TXLSXWorkbook.AddChartSheet. Le immagini si inseriscono con AddImage o AddImageFromFile, e le etichette fluttuanti con AddTextBox

Quindi un requisito espresso come "un foglio dashboard con il grafico accanto ai numeri" è in realtà un requisito per .xlsx. Puoi approssimarlo in .xls solo spingendo il grafico sul proprio foglio, il che cambia come l'utente naviga il file e cambia come deve comportarsi il tuo codice. Il foglio restituito da AddChartSheet sul lato XLS è un sottostream grafico, non una griglia: scriverci sopra con Cells.Item produce uno stream di disegno incoerente che viene generato senza errori e che Excel poi scarta all'apertura. Il grafico semplicemente svanisce, e nulla nel log di build dice perché. Tratta il foglio restituito come solo-grafico e l'intera classe di segnalazioni "grafico mancante" scompare

Incorporare un grafico in un foglio di lavoro XLSX

Il percorso XLSX è quello con margine di manovra, ed è dove i due sistemi di coordinate dell'apertura diventano concreti. Il rettangolo di ancoraggio passato ad AddChart è espresso in righe e colonne del foglio di lavoro e fissa dove siede la cornice del grafico. I dati della serie sono espressi come riferimenti A1 assoluti che includono il nome del foglio. Sono indipendenti: puoi spostare la cornice dall'altra parte del foglio e traccerà comunque le stesse celle

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;

    // Cornice ancorata alle righe 6..22, colonne 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;

L'argomento che morde è la stringa di intervallo passata ad AddSeries. È un letterale, catturato al momento della chiamata, e non ha idea che tu possa aggiungere altre venti righe di dati in seguito. Costruiscila da un conteggio di righe che hai calcolato dopo che i dati sono stati scritti, mai prima. I grafici a dispersione e a bolle sovraccaricano gli stessi due argomenti con significati diversi: l'intervallo delle categorie ora fornisce i valori X e l'intervallo dei valori fornisce Y, e il raggio della bolla proviene da un terzo riferimento impostato tramite BubbleSizeRange sul TXLSXChartSeries restituito. Leggi la chiamata come "X, Y, dimensione" invece che "categorie, valori" una volta che esci dalla famiglia colonna-e-barra

TXLSXChartType copre grafici a colonne, a barre, a linee, a torta, ad area, ad anello, a dispersione, a bolle e radar, il che copre il repertorio quotidiano della reportistica. Per un grafico a pagina intera senza griglia circostante, Book.AddChartSheet restituisce un foglio la cui proprietà IsChartSheet è true. È la controparte .xlsx del foglio grafico legacy e porta la stessa aspettativa: non scriverci contenuto di cella

Le immagini si inseriscono come byte, e si dimensionano in EMU

Ci sono due overload per inserire un'immagine, e confonderli è il bug sulle immagini che compare più spesso nella code review. AddImage(ARow, ACol, AData, AFormat) vuole in AData i byte dell'immagine già codificati: il contenuto grezzo di un PNG, JPEG, GIF o BMP. Passagli un percorso di file e avrai memorizzato una stringa di quaranta byte che nessun visualizzatore può decodificare, esattamente la segnalazione dell'icona di immagine rotta che non vuoi debuggare dopo il deployment. Quando la sorgente è un file su disco, chiama invece AddImageFromFile e lascia che sia la libreria a leggere i byte e classificare il formato per te

Poi viene il dimensionamento. DrawingML non misura in pixel; misura in English Metric Unit, dove 914400 EMU fanno un pollice e, a 96 DPI, 9525 EMU fanno un pixel. L'oggetto TXLSXImage espone WidthEMU e HeightEMU, quindi un logo pensato per renderizzare 180 per 60 pixel richiede 1714500 per 571500 EMU. Metti quella conversione in una costante con nome e calcola rispetto a essa. Numeri magici come 1714500 sparsi nel codice sono illeggibili e silenziosamente sbagliati la prima volta che qualcuno cambia il DPI di destinazione. La riga e la colonna di ancoraggio, tra l'altro, sono a base 1, in linea con il resto dell'API delle celle piuttosto che con la matematica EMU a base 0

Fogli grafico e forme nei file XLS legacy

Sul lato BIFF8, l'overload più ricco di AddChartSheet accetta il tipo di grafico, i titoli degli assi e un array aperto di record TXLSChartSeriesInfo, dove ogni record contiene un nome e un intervallo di categorie e valori come stringhe. Le forme fluttuanti sono una questione separata: vanno sul foglio di lavoro dati stesso, tramite la sua collezione Shapes, non sul foglio grafico

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // a conteggio di riferimenti: non chiamare 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 è un sottostream grafico: non chiamare mai metodi di cella su di esso

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

Due dettagli sul ciclo di vita contano qui, e tirano in direzioni opposte. TXLSWorkbook è gestito tramite l'interfaccia IXLSWorkbook ed è a conteggio di riferimenti, quindi chiamare Free su di esso da soli innesca un doppio rilascio. TXLSXWorkbook delle sezioni precedenti è un oggetto semplice e deve essere liberato in un try..finally. Lo stesso revisore di codice che segnala un Free mancante sul lato XLSX deve segnalarne uno presente sul lato XLS, il che è un vero pericolo di inciampo quando lavori in entrambi i formati nella stessa unit. Gli helper per le forme in sé sono uniformi: AddRectangle, AddOval e AddLine, con DeleteInRange per cancellare una regione di disegni, tutti ancorati tramite coppie riga e colonna, così un template che inserisce righe sopra di essi li sposta insieme alla griglia

Un'altra proprietà si guadagna il suo posto sui file legacy. TXLSPicture.TransparentColor maschera un colore di sfondo scelto fuori da una bitmap, che è come fai cadere un timbro non rettangolare (un sigillo "Approvato", una filigrana) sulla griglia in un formato il cui rendering BIFF non ha mai imparato l'alpha PNG. Imposta il colore su cui è stato creato il timbro e il rettangolo circostante scompare

I colori del tema non sopravvivono a un'andata e ritorno BIFF8

I riempimenti di disegno OOXML possono puntare a uno slot di colore del tema, motivo per cui ricolorare un intero .xlsx scambiando il suo tema è economico. I record di disegno BIFF8 non hanno un tale slot. Quando HotXLS applica un colore di tema a un disegno XLS risolve il colore in un valore RGB letterale e memorizza quello; l'indice del tema da cui proveniva sparisce nell'istante in cui il file viene scritto, e riaprirlo non può recuperarlo. Questo coglie in particolare gli strumenti di reportistica white-label, quelli che ri-brandizzano lo stesso documento generato per molti clienti. Mantieni la mappatura tema-a-RGB nella tua configurazione e riapplicala ogni volta che generi, invece di aspettarti di rileggerla da un .xls salvato

Una decisione correlata emerge sul lato delle prestazioni. Si può dire alla facciata XLS di saltare del tutto il parsing del layer di disegno quando tutto ciò che vuoi da un grande file legacy sono i suoi dati di cella, impostando _DisableGraphics a true, e questo taglia tempo reale dalle letture massive. La trappola è permanente: una cartella di lavoro aperta in quel modo non ha alcuno stream OfficeArt in memoria, quindi salvarla scrive i disegni fuori dall'esistenza. Riserva il flag ai job di analisi in sola lettura. Il quadro più ampio sulle prestazioni è nelle nostre note sulle prestazioni delle cartelle di lavoro di grandi dimensioni in HotXLS

Mantenere le ancore stabili mentre la griglia cambia

I report raramente restano della dimensione con cui sono stati generati, ed è qui che il modello di ancoraggio dell'apertura ripaga. Le operazioni strutturali della facciata XLSX (InsertRows, DeleteRows e gli equivalenti per colonna) spostano i layer dipendenti insieme alle celle. Regioni unite, collegamenti ipertestuali, commenti, riquadri bloccati, intervalli di filtro, formattazioni condizionali, validazioni, tabelle, nomi definiti e, per questo argomento, le ancore di immagini e grafici viaggiano tutte insieme. Un logo ancorato alla riga 1 resta in cima quando dieci righe entrano sotto di esso. Una cornice di grafico ancorata sotto il blocco dati scivola giù man mano che il blocco cresce. L'unica cosa che non viene riscritta è qualsiasi stringa di intervallo che hai catturato come letterale prima che avvenisse l'inserimento, dato che è solo testo che la libreria non ha motivo di rivisitare. Questo fissa l'ordine sicuro per il riempimento di un template: scrivi e rimodella prima i dati, e crea grafici e posiziona immagini come passaggio finale, con ogni stringa di intervallo derivata dai conteggi di righe che hai dopo gli inserimenti, non prima

Due strumenti più piccoli completano il kit di posizionamento. TXLSTextBox.SetArea sul lato XLS riancoraggia una casella di testo o una forma automatica esistente su un nuovo rettangolo di celle, il che batte l'eliminarla e ricrearla quando un blocco di piè di pagina si sposta. E l'overload bitmap di AddPicture accetta un TBitmap vivo con un flag di trasparenza opzionale, così qualsiasi cosa il tuo stesso codice VCL sappia disegnare (un indicatore, una striscia sparkline, un tipo di grafico che l'elenco nativo non offre) può essere timbrata direttamente nel foglio senza prima scrivere un file temporaneo

Grafici e immagini sono quasi sempre il layer di rifinitura su un report già strutturato, motivo per cui il lavoro preparatorio decide se atterrano in modo pulito. Riempire i dati a cui un grafico farà riferimento è trattato nella generazione di report guidata da template, e mantenere stabile la griglia sotto le tue ancore è l'argomento di celle unite e controllo del layout. La documentazione completa di classi e metodi vive sulla pagina prodotto HotXLS Delphi Component