Étiquettes Value From Cells des graphiques HotXLS en Delphi

Après qu'un générateur a réécrit le graphique, les étiquettes qui devraient montrer la colonne C reviennent en nombres nus, ou une barre affiche S1; 2; 20 au lieu de 20.0, alors qu'Excel ouvre le fichier sans la moindre plainte. Les étiquettes Value From Cells se trouvent dans une extension de graphique Office 2013, que HotXLS écrit au travers de TXLSXChartSeries.DataLabelsRange avec des réglages d'étiquette complets par point dans l'ordre du schéma

Où Excel range les étiquettes Value From Cells

Le schéma de graphique ECMA-376 (Partie 1, §21.2) ne contient aucun élément signifiant « prendre le texte de cette étiquette dans une plage de feuille ». Son type CT_DLbls, le bloc <c:dLbls> à l'intérieur d'une série, accueille autant de remplacements <c:dLbl> mono-point que nécessaire, suivis soit de <c:delete> soit du groupe d'étiquettes partagé, avec une liste d'extension à la fin. Le groupe partagé est une séquence fixe : numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Presque chaque case à cocher du volet Mise en forme des étiquettes de données d'Excel correspond à l'un de ces éléments. Value From Cells, non. Excel 2013 l'a ajouté par-dessus la norme au travers de listes d'extension dans l'espace de noms c15 (http://schemas.microsoft.com/office/drawing/2012/chart), que Microsoft documente dans [MS-ODRAWXML], si bien qu'un lecteur construit strictement depuis le schéma ECMA-376 n'y voit que des éléments <c:ext> opaques. Réduit aux parties qui comptent, une série avec des étiquettes issues d'une plage de cellules ressemble à ceci :

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- zéro ou plusieurs remplacements de point <c:dLbl> ouvrent le bloc -->
    <c:showLegendKey val="0"/>
    <c:showVal val="1"/>
    <c:showCatName val="0"/>
    <c:showSerName val="0"/>
    <c:showPercent val="0"/>
    <c:showBubbleSize val="0"/>
    <c:extLst>
      <c:ext uri="{CE6537A1-D6FC-4f65-9D91-7224C49458BB}"
             xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
        <c15:showDataLabelsRange val="1"/>
      </c:ext>
    </c:extLst>
  </c:dLbls>
  <c:cat>...</c:cat>
  <c:val>...</c:val>
  <c:extLst>
    <c:ext uri="{02D57815-91ED-43cb-92C2-25804820EDAC}"
           xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
      <c15:datalabelsRange>
        <c15:f>Sales!$C$1:$C$3</c15:f>
        <c15:dlblRangeCache>
          <c:ptCount val="3"/>
          <c:pt idx="0"><c:v>Target met</c:v></c:pt>
          ...
        </c15:dlblRangeCache>
      </c15:datalabelsRange>
    </c:ext>
  </c:extLst>
</c:ser>

La fonctionnalité est coupée en deux endroits. La plage appartient à la série : l'extension au niveau de la série avec l'URI {02D57815-91ED-43cb-92C2-25804820EDAC} porte c15:datalabelsRange, dont c15:f contient la référence et dont c15:dlblRangeCache contient les textes d'étiquette tels qu'ils étaient affichés quand le fichier a été sauvegardé. Le commutateur appartient aux étiquettes : c15:showDataLabelsRange se trouve dans une extension avec l'URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} à la fin de <c:dLbls>, et la même extension peut fermer un <c:dLbl> individuel pour allumer ou éteindre le texte de plage d'un point

Chaque moitié échoue seule. Un fichier avec la plage mais sans commutateur ne montre aucun texte de cellule, et un fichier avec le commutateur mais sans plage n'a rien à montrer. Un programme qui ne modélise que ce que la §21.2 définit perd les deux quand il régénère la partie graphique, raison pour laquelle HotXLS conserve le ChartML non modélisé au travers d'une édition. Le cache compte autant que la référence : un consommateur qui ne peut pas résoudre la plage, par exemple une plage pointant dans un autre classeur, a quand même besoin de texte à dessiner

Poser la plage d'étiquettes sur une série

Dans HotXLS la plage est une propriété de la série. Affecter TXLSXChartSeries.DataLabelsRange allume aussi ShowDataLabelsRange et le marque comme posé explicitement, car une plage sans le commutateur ne serait jamais affichée, et affecter une chaîne vide l'éteint à nouveau. Le texte de cellule se combine avec les commutateurs ordinaires (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), joints par Separator, tandis que LabelNumFmt met en forme la partie valeur. L'étiquette d'équation d'une courbe de tendance ne fait pas partie de ce bloc ; elle vit à l'intérieur de <c:trendline> et suit les règles propres d'Excel pour le texte d'équation de courbe de tendance. À la sauvegarde du graphique, ResolveDataLabelsRange rafraîchit DataLabelsCache depuis le texte mis en forme des cellules, en sautant lignes et colonnes cachées quand le graphique ne trace que les cellules visibles ; une plage vers un autre classeur garde le cache avec lequel elle a été ouverte. La création de classeur et de graphique suit le schéma de graphique HotXLS habituel : un nouveau classeur naît sans feuille tant que vous n'en ajoutez pas une, et Sheets.Items compte depuis 1

uses
  lxHandleX;

procedure BuildRevenueChart(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
  Series: TXLSXChartSeries;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'East';
    Sheet.Cells[1, 2].Value := 18.25;
    Sheet.Cells[1, 3].Value := 'Target met';
    Sheet.Cells[2, 1].Value := 'Central';
    Sheet.Cells[2, 2].Value := 20;
    Sheet.Cells[2, 3].Value := 'Restated';
    Sheet.Cells[3, 1].Value := 'West';
    Sheet.Cells[3, 2].Value := 14.6;
    Sheet.Cells[3, 3].Value := 'Below plan';

    Chart := Sheet.AddChart(xlsxChartBar, 'Revenue by region', 1, 5, 16, 12);
    Series := Chart.AddSeries('Revenue', 'Sales!$A$1:$A$3', 'Sales!$B$1:$B$3');

    // Pose aussi ShowDataLabelsRange := True et le note comme posé
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // texte de cellule plus la valeur
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // la partie valeur se lit 20.0, pas 20
    Series.LabelPos := xlsxDlblPosOutEnd;

    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

L'énumération ST_DLblPos du schéma liste neuf jetons de position et en accepte n'importe lequel sur n'importe quel graphique, si bien qu'un validateur ne fait aucune objection à outEnd sur une série en courbes. Les positions qui ont un sens dépendent du type de graphique : les séries en barres et colonnes prennent ctr, inBase, inEnd et outEnd, les graphiques secteurs ajoutent bestFit, et les séries en courbes et nuages de points utilisent l, r, t et b. HotXLS écrit la valeur TXLSXChartDataLabelPos choisie sous la forme de son jeton et laisse le jugement à Excel, si bien que LabelPos devrait venir du sous-ensemble qui appartient à votre type de graphique. Affecter xlsxDlblPosDefault efface LabelPosSet, n'écrit aucun dLblPos du tout, et laisse Excel placer les étiquettes lui-même. Pour l'exemple ci-dessus, attendu : chaque barre étiquetée à l'extérieur de son extrémité avec son texte de cellule et sa valeur à une décimale, les deux parties jointes par |

Que doit répéter une étiquette mono-point ?

Tout ce que dit l'étiquette de la série. Le schéma donne au remplacement de point un air bon marché : CT_DLbl est un idx, suivi soit de delete soit d'un groupe d'étiquette (un layout et un tx optionnels devant la même séquence allant de numFmt à separator) et d'une liste d'extension optionnelle, et chaque membre du groupe est optionnel. Un <c:dLbl> ne portant rien que idx et l'extension showDataLabelsRange est donc valide au regard du schéma. Excel rend une telle étiquette sous la forme S1; 2; 20 au lieu de 20.0 : il traite chaque commutateur show* omis comme allumé, si bien que le nom de série et la catégorie apparaissent, et il n'hérite pas du format numérique de la série. Une étiquette de point remplace l'étiquette de série pour ce point au lieu de la retoucher, raison pour laquelle les étiquettes de point qu'Excel écrit lui-même portent le jeu complet de commutateurs. Un générateur qui émet des remplacements minimaux pour garder le XML petit passe la validation et montre quand même le mauvais texte

HotXLS écrit chaque étiquette de point comme un remplacement complet. SetPointShowDataLabelsRange(APointIndex, AVisible) enregistre une valeur allumée ou éteinte pour le texte de plage sur un point, indexée par l'index de point base 0 qui devient idx. À la sauvegarde, chaque point de ce genre reçoit un <c:dLbl> : idx, le format numérique de la série, la position et les commutateurs dans l'ordre du groupe, et en dernier l'extension {CE6537A1-…} portant la valeur de ce point. SetPointLabelText(APointIndex, AText) ajoute du texte littéral au travers de <c:tx> ; un index avec texte, un remplacement de plage et un layout préservé reçoit quand même un seul <c:dLbl>. À la lecture, les commutateurs de série sont pris au niveau de la série, si bien que les copies à l'intérieur des étiquettes de point ne les écrasent jamais. Le code ci-dessous pose deux remplacements sur la série de revenus, et le XML qui suit est l'étiquette attendue pour le point 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Deuxième barre (idx 1) : montrer la valeur seule, sans le texte de cellule
  Series.SetPointShowDataLabelsRange(1, False);
  // Troisième barre (idx 2) : remplacer toute l'étiquette par du texte littéral
  Series.SetPointLabelText(2, 'Q3 pending audit');

  for I := 0 to Series.DataLabelRangeOverrideCount - 1 do
    Writeln('point ', Series.DataLabelRangeOverrideIndexes[I],
      ' range text: ', Series.DataLabelRangeOverrideValues[I]);

  if Series.TryGetPointShowDataLabelsRange(0, Visible) then
    Writeln('point 0 has its own override')
  else
    Writeln('point 0 follows ShowDataLabelsRange = ',
      Series.ShowDataLabelsRange);
end;

<!-- XML attendu pour le point 1 -->
<c:dLbl>
  <c:idx val="1"/>
  <c:numFmt formatCode="0.0" sourceLinked="0"/>
  <c:dLblPos val="outEnd"/>
  <c:showLegendKey val="0"/>
  <c:showVal val="1"/>
  <c:showCatName val="0"/>
  <c:showSerName val="0"/>
  <c:showPercent val="0"/>
  <c:showBubbleSize val="0"/>
  <c:separator> | </c:separator>
  <c:extLst>
    <c:ext uri="{CE6537A1-D6FC-4f65-9D91-7224C49458BB}"
           xmlns:c15="http://schemas.microsoft.com/office/drawing/2012/chart">
      <c15:showDataLabelsRange val="0"/>
    </c:ext>
  </c:extLst>
</c:dLbl>

Qu'Excel ouvre un graphique ne prouve pas grand-chose de son XML

Excel ouvre des graphiques dont les enfants sont dans le désordre, même avec des éléments inconnus à l'intérieur de <c:dLbls>, et relit leurs propriétés correctement ; une valeur d'attribut illégale telle que val="banana" est ce qui fait échouer Workbooks.Open. Les autres consommateurs sont moins tolérants. OpenXmlValidator de l'Open XML SDK rapporte les enfants dans le désordre comme des erreurs de schéma, et les outils qui lient le schéma strictement les rejettent, si bien qu'un graphique qui « marche dans Excel » peut quand même casser un pipeline en aval. Validez les parties de graphique générées avec OpenXmlValidator ; un double-clic dans Excel ne vous dit que qu'Excel a traversé. Voici les règles d'ordre du schéma §21.2 les plus faciles à rater :

• Dans <c:dLbls>, les remplacements de point ouvrent, le groupe partagé suit et extLst ferme le bloc ; à l'intérieur du groupe dLblPos précède chaque commutateur show* et showLegendKey précède showVal
• À l'intérieur d'une étiquette de point, layout et tx suivent idx et viennent avant numFmt ; la liste d'extension du point la ferme
• Une série en barres déroule idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst ; une série en courbes a marker là où la série en barres a invertIfNegative et pictureOptions, et smooth là où elle a shape
• Les séries nuage de points, aire et bulle gardent le même squelette (étiquettes, courbes de tendance, barres d'erreur et données, dans cet ordre) avec leurs membres propres : xVal et yVal pour les données de nuage et de bulle, et bubbleSize et bubble3D après les données dans une série en bulles
• Les glissements habituels au niveau de la série sont des références de données écrites avant dLbls, errBars devant trendline, et smooth devant les données, et Excel ne s'en plaint pour aucun

Les positions d'étiquette manuelles vont sur une étiquette de point

La mise en page est l'endroit où l'intuition et le schéma se séparent. Quand un utilisateur fait glisser une étiquette dans Excel, la position manuelle est stockée comme <c:layout> à l'intérieur du <c:dLbl> de cette étiquette, directement après idx. CT_DLbls n'a aucun enfant layout, si bien qu'un générateur qui en écrit un directement sous <c:dLbls>, en espérant qu'il positionne tout l'ensemble, produit un fichier que le validateur signale comme enfant invalide. Excel ouvre ce fichier et fait quelque chose que personne n'a demandé : il applique le layout à la première étiquette seulement, en le mettant où un layout de point d'idx 0 le mettrait, et lit mal les largeurs des étiquettes restantes, si bien qu'une largeur d'étiquette de 37.7 revient en 52.3. Le graphique semble presque correct, ce qui rend l'erreur facile à rater en relecture

Un layout copié depuis une extension peut rendre tout le classeur illisible. Excel peut aussi garder un c15:layout à l'intérieur de la liste d'extension d'une étiquette, et le préfixe c15 est déclaré sur le <c:ext> englobant, pas sur le fragment. Du code qui trouve les layouts par nom local attrape cet élément aussi, et le coller dans <c:dLbls> emporte le préfixe sans sa déclaration, si bien que la partie graphique reconstruite contient un préfixe d'espace de noms non déclaré et qu'aucun analyseur XML conscient des espaces de noms ne la chargera. Appariez les layouts par espace de noms et par position d'enfant direct, jamais par nom local seul

HotXLS prend un layout d'étiquette uniquement d'un enfant <c:layout> direct de <c:dLbls> ou d'un <c:dLbl>, jamais de l'intérieur d'une extension, et le garde verbatim indexé par index de point. LabelLayoutXml porte le layout de la première étiquette, y compris un égaré trouvé directement sous <c:dLbls>, car c'est ainsi qu'Excel l'interprète ; PointLabelLayoutCount, PointLabelLayoutIndexes et PointLabelLayoutXmls listent les autres. À la régénération du graphique, ce layout part sur le <c:dLbl> d'idx 0 et chaque autre layout sur le <c:dLbl> de son propre index, chacun fusionné avec le remplacement de plage et le texte de ce point en une seule étiquette

procedure ReportLabelLayouts(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Series: TXLSXChartSeries;
  C, S, I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      Exit;
    Sheet := Book.Sheets[1];               // index de feuille base 1
    for C := 0 to Sheet.Charts.Count - 1 do
      for S := 0 to Sheet.Charts[C].SeriesCount - 1 do
      begin
        Series := Sheet.Charts[C].Series[S];
        Writeln('chart ', C, ' series ', S,
          ' label range: ', Series.DataLabelsRange,
          ' cached texts: ', Series.DataLabelsCache.Count);
        if Series.LabelLayoutXml <> '' then
          Writeln('  first label has a manual layout');
        for I := 0 to Series.PointLabelLayoutCount - 1 do
          Writeln('  point ', Series.PointLabelLayoutIndexes[I],
            ' layout: ', Series.PointLabelLayoutXmls[I]);
      end;
  finally
    Book.Free;
  end;
end;

Excel lit une étiquette de point comme une étiquette entière, si bien que l'écrire comme une, avec la position manuelle sur le même <c:dLbl>, garde le texte S1; 2; 20 et la première étiquette décalée hors de vos graphiques. Dans HotXLS, ce modèle se cache derrière DataLabelsRange, les appels de remplacement de point et les propriétés de layout préservé