Excel Value From Cells-Labels mit HotXLS in Delphi

Nachdem ein Generator das Diagramm neu geschrieben hat, zeigen Labels, die Spalte C darstellen sollten, nur nackte Zahlen, oder ein Balken liest sich als S1; 2; 20 statt 20.0, und Excel öffnet die Datei trotzdem ohne Murren. Value-From-Cells-Labels sitzen in einer Office-2013-Diagrammerweiterung, die HotXLS über TXLSXChartSeries.DataLabelsRange schreibt, mit vollständigen Label-Einstellungen pro Punkt in Schema-Reihenfolge

Wo Excel Value-From-Cells-Labels ablegt

Das ECMA-376-Chart-Schema (Part 1, §21.2) hat kein Element, das „hol den Text dieser Beschriftung aus einem Arbeitsblattbereich“ bedeutet. Sein Typ CT_DLbls, der <c:dLbls>-Block in einer Serie, hält beliebig viele <c:dLbl>-Overrides für einzelne Punkte, gefolgt von entweder <c:delete> oder der gemeinsamen Label-Gruppe, mit einer Erweiterungsliste am Ende. Die gemeinsame Gruppe ist eine feste Sequenz: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Fast jedes Kontrollkästchen in Excels Pane „Datenbeschriftungen formatieren“ mappt auf eines dieser Elemente. Value From Cells tut es nicht. Excel 2013 hat es über Erweiterungslisten im c15-Namespace (http://schemas.microsoft.com/office/drawing/2012/chart) oben auf den Standard gesetzt, von Microsoft in [MS-ODRAWXML] dokumentiert, also sieht ein Leser, der strikt nach dem ECMA-376-Schema gebaut ist, dort nichts als opake <c:ext>-Elemente. Auf die Teile reduziert, die zählen, sieht eine Serie mit Zellbereichs-Labels so aus:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- null oder mehr <c:dLbl>-Punkt-Overrides führen den Block an -->
    <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>

Das Feature ist auf zwei Orte verteilt. Der Bereich gehört zur Serie: die Erweiterung auf Serien-Ebene mit URI {02D57815-91ED-43cb-92C2-25804820EDAC} trägt c15:datalabelsRange, dessen c15:f die Referenz hält und dessen c15:dlblRangeCache die Label-Texte so vorhält, wie sie beim Speichern der Datei angezeigt wurden. Der Schalter gehört zu den Labels: c15:showDataLabelsRange sitzt in einer Erweiterung mit URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} am Ende von <c:dLbls>, und dieselbe Erweiterung kann ein einzelnes <c:dLbl> abschließen, um den Bereichs-Text für einen Punkt ein- oder auszuschalten

Jede Hälfte scheitert für sich. Eine Datei mit dem Bereich, aber ohne den Schalter zeigt keinen Zelltext, und eine Datei mit dem Schalter, aber ohne den Bereich hat nichts zu zeigen. Ein Programm, das nur modelliert, was §21.2 definiert, verliert beides, wenn es den Chart-Part regeneriert, weshalb HotXLS unbemodelliertes ChartML über eine Bearbeitung hinweg bewahrt. Der Cache zählt genauso viel wie die Referenz: ein Consumer, der den Bereich nicht auflösen kann, etwa einer, der in eine andere Mappe zeigt, braucht trotzdem Text zum Zeichnen

Den Label-Bereich an einer Serie setzen

In HotXLS ist der Bereich eine Eigenschaft der Serie. TXLSXChartSeries.DataLabelsRange zuzuweisen schaltet auch ShowDataLabelsRange ein und markiert es als explizit gesetzt, denn ein Bereich ohne den Schalter würde nie angezeigt, und einen leeren String zuzuweisen schaltet ihn wieder ab. Der Zelltext kombiniert mit den gewöhnlichen Schaltern (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), verbunden über Separator, während LabelNumFmt den Wertteil formatiert. Das Gleichungs-Label einer Trendline gehört nicht zu diesem Block; es sitzt in <c:trendline> und folgt Excels eigenen Regeln für Trendline-Gleichungstext. Beim Speichern des Diagramms aktualisiert ResolveDataLabelsRange den DataLabelsCache aus dem formatierten Text der Zellen und überspringt ausgeblendete Zeilen und Spalten, wenn das Diagramm nur sichtbare Zellen zeichnet; ein Bereich in eine andere Mappe behält den Cache, mit dem er geöffnet wurde. Mappen- und Diagrammerzeugung folgen dem gewöhnlichen HotXLS-Chart-Muster: eine neue Mappe hat kein Sheet, bis Sie eines hinzufügen, und Sheets.Items zählt ab 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');

    // Setzt auch ShowDataLabelsRange := True und hält es als gesetzt fest
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // Zelltext plus der Wert
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // der Wertteil liest sich 20.0, nicht 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

Die ST_DLblPos-Enumeration des Schemas listet neun Positions-Tokens auf und akzeptiert jeden davon an jedem Diagramm, also hat ein Validator nichts gegen outEnd an einer Linien-Serie. Welche Positionen Sinn ergeben, hängt von der Diagrammart ab: Balken- und Säulen-Serien nehmen ctr, inBase, inEnd und outEnd, Kreisdiagramme kommen mit bestFit, und Linien- und Scatter-Serien nutzen l, r, t und b. HotXLS schreibt den gewählten TXLSXChartDataLabelPos-Wert als sein Token und überlässt das Urteil Excel, also sollte LabelPos aus der Teilmenge kommen, die zu Ihrem Diagrammtyp gehört. xlsxDlblPosDefault zuzuweisen leert LabelPosSet, schreibt gar kein dLblPos und lässt Excel die Labels selbst platzieren. Erwartet für das Beispiel oben: jeder Balken außerhalb seines Endes beschriftet, mit seinem Zelltext und seinem Wert auf eine Dezimalstelle, die beiden Teile verbunden über |

Was muss ein Ein-Punkt-Label alles wiederholen?

Alles, was das Serien-Label sagt. Das Schema lässt einen Punkt-Override billig aussehen: CT_DLbl ist ein idx, gefolgt von entweder delete oder einer Label-Gruppe (ein optionales layout und tx vor demselben Lauf von numFmt bis separator) und einer optionalen Erweiterungsliste, und jedes Mitglied der Gruppe ist optional. Ein <c:dLbl>, das nichts als idx und die showDataLabelsRange-Erweiterung hält, ist also schema-gültig. Excel rendert so ein Label als S1; 2; 20 statt 20.0: es behandelt jeden ausgelassenen show*-Schalter als eingeschaltet, also erscheinen Serienname und Kategorie, und es erbt nicht das Zahlenformat der Serie. Ein Punkt-Label ersetzt das Serien-Label für diesen Punkt, statt es zu patchen, weshalb die Punkt-Labels, die Excel selbst schreibt, den vollständigen Schalter-Satz tragen. Ein Generator, der minimale Overrides emittiert, um das XML klein zu halten, besteht die Validierung und zeigt trotzdem den falschen Text

HotXLS schreibt jedes Punkt-Label als vollständigen Ersatz. SetPointShowDataLabelsRange(APointIndex, AVisible) hält einen Ein- oder Aus-Wert für den Bereichs-Text an einem Punkt fest, verschlüsselt über den 0-basierten Punktindex, der zu idx wird. Zur Speicherzeit bekommt jeder solche Punkt ein <c:dLbl>: idx, das Zahlenformat der Serie, Position und Schalter in Gruppenreihenfolge, und zuletzt die {CE6537A1-…}-Erweiterung mit dem Wert dieses Punkts. SetPointLabelText(APointIndex, AText) fügt literalen Text über <c:tx> hinzu; ein Index mit Text, einem Bereichs-Override und einem bewahrten Layout bekommt weiterhin ein einziges <c:dLbl>. Beim Lesen werden die Serien-Schalter von der Serien-Ebene genommen, also überschreiben die Kopien in Punkt-Labels sie nie. Der Code unten setzt zwei Overrides an der Umsatz-Serie, und das XML danach ist das Label, das für Punkt 1 erwartet wird

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Zweiter Balken (idx 1): nur den Wert zeigen, ohne den Zelltext
  Series.SetPointShowDataLabelsRange(1, False);
  // Dritter Balken (idx 2): das ganze Label durch literalen Text ersetzen
  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;

<!-- Erwartetes XML für Punkt 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>

Dass Excel ein Diagramm öffnet, beweist wenig über sein XML

Excel öffnet Diagramme, deren Kinder aus der Reihenfolge sind, sogar mit unbekannten Elementen in <c:dLbls>, und liest ihre Eigenschaften korrekt zurück; ein illegaler Attributwert wie val="banana" ist es, der Workbooks.Open scheitern lässt. Andere Consumer sind weniger kulant. Der OpenXmlValidator des Open XML SDK meldet Kinder in falscher Reihenfolge als Schemafehler, und Werkzeuge, die strikt an das Schema binden, weisen sie zurück, also kann ein Diagramm, das „in Excel funktioniert“, trotzdem eine nachgelagerte Pipeline brechen. Validieren Sie erzeugte Chart-Parts mit OpenXmlValidator; ein Doppelklick in Excel sagt Ihnen nur, dass Excel zurechtgekommen ist. Dies sind die Ordnungsregeln aus dem §21.2-Schema, die am leichtesten schiefgehen:

• In <c:dLbls> führen die Punkt-Overrides, die gemeinsame Gruppe folgt, und extLst schließt den Block; in der Gruppe geht dLblPos jedem show*-Schalter voraus, und showLegendKey geht showVal voraus
• In einem Punkt-Label folgen layout und tx auf idx und kommen vor numFmt; die Erweiterungsliste des Punkts schließt es ab
• Eine Balken-Serie läuft idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; eine Linien-Serie hat marker, wo die Balken-Serie invertIfNegative und pictureOptions hat, und smooth, wo sie shape hat
• Scatter-, Flächen- und Blasen-Serien behalten dasselbe Skelett (Labels, Trendlines, Fehlerbalken und Daten, in dieser Reihenfolge) mit ihren eigenen Mitgliedern: xVal und yVal für Scatter- und Blasendaten, und bubbleSize und bubble3D nach den Daten in einer Blasen-Serie
• Die gewöhnlichen Ausrutscher auf Serien-Ebene sind Datenreferenzen, geschrieben vor dLbls, errBars vor trendline und smooth vor den Daten, nichts davon beklagt Excel

Manuelle Label-Positionen gehören auf ein Punkt-Label

Beim Layout trennen sich Intuition und Schema. Zieht ein Nutzer ein Label in Excel herum, wird die manuelle Position als <c:layout> im <c:dLbl> dieses Labels gespeichert, direkt nach idx. CT_DLbls hat kein layout-Kind, also erzeugt ein Generator, der eines direkt unter <c:dLbls> schreibt in der Erwartung, es positioniere den ganzen Satz, eine Datei, die der Validator als ungültiges Kind markiert. Excel öffnet diese Datei und tut etwas, das niemand verlangt hat: es wendet das Layout nur auf das erste Label an, setzt es dorthin, wo ein idx-0-Punkt-Layout es setzen würde, und missdeutet die Breiten der übrigen Labels, sodass eine Label-Breite von 37.7 als 52.3 zurückkommt. Das Diagramm sieht fast richtig aus, was den Fehler im Review leicht übersehen lässt

Ein Layout, das aus einer Erweiterung herauskopiert wird, kann die ganze Mappe unlesbar machen. Excel kann auch ein c15:layout in der Erweiterungsliste eines Labels halten, und das c15-Präfix ist auf dem umschließenden <c:ext> deklariert, nicht auf dem Fragment. Code, der Layouts über den lokalen Namen findet, greift auch dieses Element, und das Einfügen in <c:dLbls> trägt das Präfix ohne seine Deklaration heran, also enthält der neu gebaute Chart-Part ein undeklariertes Namespace-Präfix, und kein namespace-bewusster XML-Parser lädt ihn. Matchen Sie Layouts über Namespace und Position als direktes Kind, nie über den lokalen Namen allein

HotXLS nimmt ein Label-Layout nur von einem direkten <c:layout>-Kind von <c:dLbls> oder eines <c:dLbl>, nie von innerhalb einer Erweiterung, und hält es wörtlich fest, verschlüsselt über den Punktindex. LabelLayoutXml hält das Layout des ersten Labels, einschließlich eines verirrten, das direkt unter <c:dLbls> gefunden wurde, denn so interpretiert es Excel; PointLabelLayoutCount, PointLabelLayoutIndexes und PointLabelLayoutXmls listen den Rest auf. Wird das Diagramm regeneriert, wandert dieses Layout auf das idx-0-<c:dLbl> und jedes andere Layout auf das <c:dLbl> seines eigenen Index, jeweils verschmolzen mit dem Bereichs-Override und dem Text dieses Punkts zu einem Label

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];               // 1-basierter Sheet-Index
    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 liest ein Punkt-Label als ganzes Label, also hält das Schreiben als eines, mit der manuellen Position auf demselben <c:dLbl>, sowohl den S1; 2; 20-Text als auch das verschobene erste Label aus Ihren Diagrammen fern. In HotXLS sitzt dieses Modell hinter DataLabelsRange, den Punkt-Override-Aufrufen und den bewahrten Layout-Eigenschaften