Graphiques HotXLS et images : objets de dessin Excel en

Tout ce qui flotte au-dessus de la grille d'une feuille de calcul (un graphique, un logo, un tampon, une zone de légende) est un objet de dessin, et un objet de dessin est défini par deux choses : ce qu'il est, et où il est ancré. L'ancrage est la partie que l'on rate. Un graphique ne vit pas dans une cellule ; il occupe un rectangle épinglé sur une plage de lignes et de colonnes, et les données qu'il trace forment un ensemble séparé de références A1 que l'ancrage ignore totalement. Déplacez le cadre et le tracé reste en place. Insérez des lignes en dessous et le cadre glisse avec elles. Garder ces deux systèmes de coordonnées bien distincts, c'est l'essentiel de ce qui fait fonctionner le code de dessin correctement

HotXLS est une bibliothèque Object Pascal native qui lit et écrit des fichiers XLS et XLSX sans automatisation Excel, et elle embarque deux modèles de dessin distincts, car les deux formats stockent les dessins différemment. Le format BIFF8 .xls conserve les graphiques sur leurs propres feuilles dédiées et les formes flottantes dans un flux OfficeArt rattaché à la feuille de calcul. Le format OOXML .xlsx peut intégrer un graphique dans la grille, ancré à un rectangle de cellules, aux côtés d'images et de formes flottantes de même nature. Le modèle objet reflète cette séparation, et les erreurs qui méritent d'être documentées viennent toutes de l'application des règles d'un format à l'autre

Quel conteneur peut recevoir quoi

Le choix du conteneur doit précéder tout code de graphique, car les types d'objets disponibles diffèrent entre les deux :

XLS (BIFF8) : les graphiques vivent sur des feuilles graphiques dédiées créées via AddChartSheet sur la collection Sheets. Les images, zones de texte, rectangles, ovales et lignes sont des formes OfficeArt gérées par la collection Shapes de la feuille de calcul. Il n'existe pas d'API pour intégrer un graphique dans une grille de feuille normale
XLSX (OOXML) : les graphiques peuvent être intégrés directement dans une feuille avec TXLSXWorksheet.AddChart, ancrés à un rectangle de cellules, ou placés sur une feuille graphique dédiée avec TXLSXWorkbook.AddChartSheet. Les images s'ajoutent avec AddImage ou AddImageFromFile, et les étiquettes flottantes avec AddTextBox

Ainsi, une exigence formulée comme « une feuille tableau de bord avec le graphique à côté des chiffres » est en réalité une exigence .xlsx. En .xls, vous ne pouvez qu'approximer ce résultat en renvoyant le graphique sur sa propre feuille, ce qui change la façon dont l'utilisateur navigue dans le fichier et la façon dont votre code doit se comporter. La feuille renvoyée par AddChartSheet côté XLS est un sous-flux graphique, pas une grille : écrire dedans avec Cells.Item produit un flux de dessin incohérent qui se génère sans erreur mais qu'Excel écarte à l'ouverture. Le graphique disparaît tout simplement, sans que rien dans le journal de build n'explique pourquoi. Traitez la feuille renvoyée comme strictement graphique et toute une classe de rapports « graphique manquant » disparaît

Intégrer un graphique dans une feuille XLSX

Le chemin XLSX est celui qui offre de la marge de manœuvre, et c'est là que les deux systèmes de coordonnées évoqués en introduction deviennent concrets. Le rectangle d'ancrage passé à AddChart s'exprime en lignes et colonnes de feuille et fixe où se place le cadre du graphique. Les données de série s'expriment en références A1 absolues incluant le nom de feuille. Ils sont indépendants : vous pouvez déplacer le cadre de l'autre côté de la feuille et il trace toujours les mêmes cellules

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;

    // Frame anchored to rows 6..22, columns 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'argument qui piège le plus est la chaîne de plage passée à AddSeries. C'est un littéral, capturé au moment de l'appel, et il ne sait pas que vous pourriez ajouter vingt lignes de données par la suite. Construisez-le à partir du nombre de lignes calculé après l'écriture des données, jamais avant. Les graphiques en nuage de points et à bulles surchargent les deux mêmes arguments avec des significations différentes : la plage de catégories fournit désormais les valeurs X et la plage de valeurs fournit Y, et le rayon de la bulle vient d'une troisième référence définie via BubbleSizeRange sur le TXLSXChartSeries renvoyé. Lisez l'appel comme « X, Y, taille » plutôt que « catégories, valeurs » dès que vous sortez de la famille histogrammes et barres

TXLSXChartType couvre les histogrammes, barres, courbes, secteurs, aires, anneaux, nuages de points, bulles et radars, ce qui représente le répertoire habituel des rapports. Pour un graphique pleine page sans grille alentour, Book.AddChartSheet renvoie une feuille dont la propriété IsChartSheet est vraie. C'est l'équivalent .xlsx de la feuille graphique héritée et elle porte la même attente : n'écrivez pas de contenu de cellule dedans

Les images sont des octets, et les tailles sont en EMU

Il existe deux surcharges pour insérer une image, et les confondre est le bug d'image qui revient le plus souvent en revue de code. AddImage(ARow, ACol, AData, AFormat) attend les octets d'image déjà encodés dans AData : le contenu brut d'un PNG, JPEG, GIF ou BMP. Passez-lui un chemin de fichier et vous avez stocké une chaîne de quarante octets qu'aucun visualiseur ne peut décoder, ce qui correspond exactement au rapport d'icône d'image cassée que vous ne voulez pas déboguer après déploiement. Lorsque la source est un fichier sur disque, appelez AddImageFromFile et laissez la bibliothèque lire les octets et classifier le format pour vous

Vient ensuite le dimensionnement. DrawingML ne mesure pas en pixels ; il mesure en English Metric Units, où 914 400 EMU font un pouce et, à 96 DPI, 9 525 EMU font un pixel. L'objet TXLSXImage expose WidthEMU et HeightEMU, ainsi un logo destiné à s'afficher en 180 par 60 pixels nécessite 1 714 500 par 571 500 EMU. Mettez cette conversion dans une constante nommée et calculez à partir d'elle. Des nombres magiques comme 1 714 500 éparpillés dans le code sont illisibles et silencieusement faux dès que quelqu'un change le DPI cible. La ligne et la colonne d'ancrage, au passage, sont en base 1, ce qui correspond au reste de l'API cellule plutôt qu'aux calculs EMU en base 0

Feuilles graphiques et formes dans les fichiers XLS hérités

Côté BIFF8, la surcharge plus riche de AddChartSheet accepte le type de graphique, les titres d'axes, et un tableau ouvert de records TXLSChartSeriesInfo, où chaque record contient un nom ainsi que des plages de catégories et de valeurs sous forme de chaînes. Les formes flottantes sont une question distincte : elles se placent sur la feuille de données elle-même, via sa collection Shapes, et non sur la feuille graphique

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // interface-counted: do not 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 a chart substream: never call cell methods on it

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

Deux détails de durée de vie comptent ici, et ils tirent dans des directions opposées. TXLSWorkbook est détenu via l'interface IXLSWorkbook et est compté par références, donc l'appel de Free sur lui déclenche une double libération. TXLSXWorkbook des sections précédentes est un objet ordinaire qui doit être libéré dans un bloc try..finally. Le même réviseur qui signale un Free manquant côté XLSX doit signaler un Free présent côté XLS, ce qui est un vrai piège lorsqu'on travaille avec les deux formats dans la même unité. Les helpers de formes sont uniformes : AddRectangle, AddOval et AddLine, avec DeleteInRange pour effacer une région de dessins, tous s'ancrent par paires ligne/colonne, de sorte qu'un modèle qui insère des lignes au-dessus d'eux les décale en même temps que la grille

Une propriété supplémentaire mérite sa place sur les fichiers hérités. TXLSPicture.TransparentColor masque une couleur de fond choisie dans un bitmap, ce qui permet de poser un tampon non rectangulaire (un sceau « Approuvé », un filigrane) sur la grille dans un format dont le rendu BIFF n'a jamais appris la transparence alpha PNG. Définissez la couleur sur laquelle le tampon a été conçu et le rectangle environnant disparaît

Les couleurs de thème ne survivent pas à un aller-retour BIFF8

Les remplissages de dessin OOXML peuvent pointer vers un emplacement de couleur de thème, ce qui explique pourquoi recolorer un .xlsx entier en remplaçant son thème est peu coûteux. Les enregistrements de dessin BIFF8 n'ont pas de tel emplacement. Lorsque HotXLS applique une couleur de thème à un dessin XLS, il résout la couleur en une valeur RGB littérale et la stocke ; l'index de thème d'où elle provient est perdu dès l'instant où le fichier est écrit, et la réouverture ne peut pas le récupérer. Cela piège en particulier les outils de reporting en marque blanche, le genre qui re-marque le même document généré pour de nombreux clients. Conservez la correspondance thème vers RGB dans votre propre configuration et réappliquez-la à chaque génération, plutôt que d'espérer la relire depuis un .xls sauvegardé

Une décision connexe apparaît côté performance. La façade XLS peut être configurée pour ignorer totalement l'analyse de la couche de dessin lorsque tout ce que vous voulez d'un grand fichier hérité, ce sont ses données de cellules, en définissant _DisableGraphics à true, ce qui économise un temps réel sur les lectures en masse. L'inconvénient est permanent : un classeur ouvert de cette façon n'a pas de flux OfficeArt en mémoire, donc l'enregistrer efface les dessins. Réservez ce flag aux travaux analytiques en lecture seule. Le tableau de performance général est présenté dans nos notes sur la performance des grands classeurs dans HotXLS

Maintenir les ancrages stables pendant que la grille change

Les rapports restent rarement à la taille à laquelle ils ont été générés, et c'est là que le modèle d'ancrage évoqué en introduction porte ses fruits. Les opérations structurelles de la façade XLSX (InsertRows, DeleteRows et leurs équivalents de colonnes) déplacent les couches dépendantes en même temps que les cellules. Les régions fusionnées, les hyperliens, les commentaires, les volets figés, les plages de filtres, les formats conditionnels, les validations, les tableaux, les noms définis, et pour ce sujet, les ancrages d'images et de graphiques, bougent tous ensemble. Un logo ancré à la ligne 1 reste en haut quand dix lignes sont insérées en dessous. Un cadre de graphique ancré sous le bloc de données glisse vers le bas lorsque le bloc grandit. Ce qui n'est pas réécrit, c'est toute chaîne de plage que vous avez capturée comme littéral avant l'insertion, puisque c'est juste du texte que la bibliothèque n'a aucune raison de revisiter. Cela fixe l'ordre sûr pour remplir un modèle : écrire et remodeler les données d'abord, puis créer les graphiques et placer les images lors de la dernière passe, avec chaque chaîne de plage dérivée des nombres de lignes après les insertions, pas avant

Deux outils plus petits complètent la trousse de placement. TXLSTextBox.SetArea côté XLS repositionne une zone de texte ou une forme automatique existante sur un nouveau rectangle de cellules, ce qui est moins coûteux que la supprimer et la recréer lorsqu'un bloc de pied de page se déplace. Et la surcharge bitmap de AddPicture accepte un TBitmap actif avec un flag de transparence optionnel, de sorte que tout ce que votre propre code de dessin VCL peut produire (une jauge, une bande sparkline, un type de graphique absent de la liste native) peut être tamponné directement dans la feuille sans écrire d'abord un fichier temporaire

Les graphiques et les images constituent presque toujours la couche finale d'un rapport déjà structuré, raison pour laquelle les fondations décident si elles atterrissent proprement. Le remplissage des données qu'un graphique va référencer est couvert dans la génération de rapports pilotée par modèle, et la stabilité de la grille sous vos ancrages est le sujet de cellules fusionnées et contrôle de mise en page. La documentation complète des classes et méthodes se trouve sur la page produit HotXLS Component