Excel Value From Cells-labels zitten in de c15-chartextensie

Na een herschrijving van de grafiek door een generator komen labels die kolom C moeten tonen terug als kale getallen, of één staaf leest S1; 2; 20 in plaats van 20.0, terwijl Excel het bestand zonder klachten opent. Value From Cells-labels zitten in een chartextensie uit Office 2013, die HotXLS via TXLSXChartSeries.DataLabelsRange schrijft met complete labelinstellingen per punt in schema-volgorde

Waar Excel Value From Cells-labels bewaart

Het chartschema van ECMA-376 (Part 1, §21.2) kent geen element dat betekent: neem de tekst van dit label uit een worksheet-bereik. Zijn type CT_DLbls, het <c:dLbls>-blok in een serie, herbergt naar wens veel single-point-<c:dLbl>-overrides, gevolgd door ofwel <c:delete> of de gedeelde labelgroep, met een extensielijst op het einde. De gedeelde groep is een vaste volgorde: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Bijna elk vinkje in het paneel Format Data Labels van Excel hoort bij een van die elementen. Value From Cells niet. Excel 2013 voegde het toe bovenop de standaard via extensielijsten in de c15-namespace (http://schemas.microsoft.com/office/drawing/2012/chart), gedocumenteerd door Microsoft in [MS-ODRAWXML], dus een reader die strikt op het ECMA-376-schema is gebouwd ziet daar niets dan ondoorzichtige <c:ext>-elementen. Ontdoen van alles wat niet telt, ziet een serie met labels uit celbereiken er zo uit:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- nul of meer <c:dLbl>-puntoverrides gaan voorop in het blok -->
    <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>

De feature is over twee plekken verdeeld. Het bereik hoort bij de serie: de extensie op serieniveau met URI {02D57815-91ED-43cb-92C2-25804820EDAC} draagt c15:datalabelsRange, waarvan c15:f de verwijzing bevat en c15:dlblRangeCache de labelteksten zoals ze werden getoond toen het bestand werd opgeslagen. De schakelaar hoort bij de labels: c15:showDataLabelsRange zit in een extensie met URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} aan het einde van <c:dLbls>, en dezelfde extensie kan een individuele <c:dLbl> afsluiten om de bereiktekst voor één punt aan of uit te zetten

Elke helft faalt op zichzelf. Een bestand met het bereik maar zonder schakelaar toont geen celtekst, en een bestand met de schakelaar maar zonder bereik heeft niets te tonen. Een programma dat alleen modelleert wat §21.2 definieert, verliest allebei wanneer het het chartonderdeel regenereert, en daarom houdt HotXLS niet-gemodeleerde ChartML vast door een bewerking heen. De cache weegt even zwaar als de verwijzing: een consumer die het bereik niet kan oplossen, bijvoorbeeld één die naar een andere workbook wijst, heeft toch tekst nodig om te tekenen

Het labelbereik op een serie zetten

In HotXLS is het bereik een eigenschap van de serie. Het toekennen van TXLSXChartSeries.DataLabelsRange zet ook ShowDataLabelsRange aan en markeert het als expliciet ingesteld, want een bereik zonder de schakelaar zou nooit worden getoond, en een lege string toekennen zet het weer uit. De celtekst combineert met de gewone schakelaars (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), samengevoegd met Separator, terwijl LabelNumFmt het waardegedeelte opmaakt. Het vergelijkingslabel van een trendline hoort niet in dit blok; het zit in <c:trendline> en volgt de eigen regels van Excel voor trendline-vergelijkingstekst. Bij het opslaan van de grafiek ververst ResolveDataLabelsRange de DataLabelsCache vanuit de opgemaakte tekst van de cellen, waarbij verborgen rijen en kolommen worden overgeslagen wanneer de grafiek alleen zichtbare cellen plot; een bereik naar een andere workbook houdt de cache waarmee het werd geopend. Workbook- en chartcreatie volgen het gebruikelijke HotXLS-chartpatroon: een nieuwe workbook heeft geen sheet tot u er een toevoegt, en Sheets.Items telt vanaf 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');

    // Zet ook ShowDataLabelsRange := True en registreert het als ingesteld
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // celtekst plus de waarde
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // waardegedeelte leest 20.0, niet 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

De ST_DLblPos-enumeratie in het schema somt negen positioneringstokens op en accepteert ze allemaal op elke grafiek, dus een validator heeft geen bezwaar tegen outEnd op een lijnserie. Welke posities zin hebben hangt af van het charttype: bar- en kolomseries nemen ctr, inBase, inEnd en outEnd, cirkeldiagrammen voegen bestFit toe, en lijn- en scatterseries gebruiken l, r, t en b. HotXLS schrijft de gekozen TXLSXChartDataLabelPos-waarde als zijn token en laat het oordeel aan Excel, dus LabelPos hoort uit de subset te komen die bij uw charttype hoort. Het toekennen van xlsxDlblPosDefault wist LabelPosSet, schrijft helemaal geen dLblPos en laat Excel de labels zelf plaatsen. Voor het voorbeeld hierboven is het verwachte resultaat: elke staaf buiten zijn einde gelabeld, met zijn celtekst en zijn waarde op één decimaal, de twee delen samengevoegd met |

Wat moet een single-point-label allemaal herhalen?

Alles wat het serielabel zegt. Het schema laat een puntoverride goedkoop lijken: CT_DLbl is een idx, gevolgd door ofwel delete of een labelgroep (een optionele layout en tx vóór dezelfde reeks van numFmt tot separator) en een optionele extensielijst, en elk lid van de groep is optioneel. Een <c:dLbl> met niets dan idx en de showDataLabelsRange-extensie is dus schema-geldig. Excel rendert een dergelijk label als S1; 2; 20 in plaats van 20.0: het behandelt elke weggelaten show*-schakelaar als aan, dus de serienaam en categorie verschijnen, en het erft het getalformaat van de serie niet. Een puntlabel vervangt het serielabel voor dat punt in plaats van het te patchen, en daarom dragen de puntlabels die Excel zelf schrijft de complete set schakelaars. Een generator die minimale overrides uitgeeft om de XML klein te houden, komt door de validatie en toont toch de verkeerde tekst

HotXLS schrijft elk puntlabel als volledige vervanging. SetPointShowDataLabelsRange(APointIndex, AVisible) legt een aan- of uitwaarde voor de bereiktekst vast op één punt, met als sleutel de 0-based puntindex die idx wordt. Bij het opslaan krijgt elk dergelijk punt één <c:dLbl>: idx, het getalformaat van de serie, positie en schakelaars in groepsvolgorde, en als laatste de {CE6537A1-…}-extensie met de waarde van dat punt. SetPointLabelText(APointIndex, AText) voegt letterlijke tekst toe via <c:tx>; een index met tekst, een bereikoverride en een bewaarde layout krijgt nog steeds één <c:dLbl>. Bij het lezen komen de serieschakelaars van het serieniveau, dus de kopieën in puntlabels overschrijven ze nooit. De code hieronder zet twee overrides op de omzetserie, en de XML erna is het verwachte label voor punt 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Tweede staaf (idx 1): toon alleen de waarde, zonder de celtekst
  Series.SetPointShowDataLabelsRange(1, False);
  // Derde staaf (idx 2): vervang het hele label door letterlijke tekst
  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;

<!-- Verwachte XML voor punt 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>

Dat Excel een grafiek opent bewijst weinig over zijn XML

Excel opent grafieken waarvan de kinderen in de verkeerde volgorde staan, zelfs met onbekende elementen in <c:dLbls>, en leest hun eigenschappen correct terug; een illegale attribuutwaarde zoals val="banana" is wat Workbooks.Open doet struikelen. Andere consumers zijn minder mild. De OpenXmlValidator van de Open XML SDK meldt kinderen in verkeerde volgorde als schemafouten, en tools die het schema strikt binden wijzen ze af, dus een grafiek die in Excel werkt kan nog steeds een downstream-pipeline breken. Valideer gegenereerde chartonderdelen met OpenXmlValidator; een dubbelklik in Excel vertelt u alleen dat Excel ermee heeft kunnen omgaan. Dit zijn de volgorderegels uit het §21.2-schema die het makkelst misgaan:

• In <c:dLbls> gaan puntoverrides voorop, volgt de gedeelde groep en sluit extLst het blok af; binnen de groep gaat dLblPos voor elke show*-schakelaar en gaat showLegendKey voor showVal
• In een puntlabel volgen layout en tx op idx en komen vóór numFmt; de extensielijst van het punt sluit het af
• Een barserie loopt idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; een lijnserie heeft marker waar de barserie invertIfNegative en pictureOptions heeft, en smooth waar die shape heeft
• Scatter-, area- en bubbelseries houden hetzelfde skelet aan (labels, trendlines, foutbalken en data, in die volgorde) met hun eigen leden: xVal en yVal voor scatter- en bubbeldata, en bubbleSize en bubble3D na de data in een bubbelserie
• Waar het op serieniveau vaak misgaat: datareferenties die vóór dLbls worden geschreven, errBars vóór trendline, en smooth vóór de data, en over geen ervan klaagt Excel

Handmatige labelposities gaan op een puntlabel

Layout is waar intuïtie en schema uit elkaar gaan. Sleep een gebruiker een label in Excel, dan wordt de handmatige positie opgeslagen als <c:layout> in de <c:dLbl> van dat label, direct na idx. CT_DLbls heeft geen layout-kind, dus een generator die er een direct onder <c:dLbls> schrijft in de verwachting dat het de hele set positioneert, produceert een bestand dat de validator aanmerkt als ongeldig kind. Excel opent dat bestand en doet iets wat niemand vroeg: het past de layout alleen op het eerste label toe, op de plek waar een puntlayout van idx 0 zou zetten, en leest de breedtes van de resterende labels verkeerd, zodat een labelbreedte van 37.7 als 52.3 terugkomt. De grafiek lijkt bijna goed, en daardoor is de fout in review makkelijk te missen

Een layout die uit een extensie is gekopieerd kan de hele workbook onleesbaar maken. Excel kan een c15:layout ook in de extensielijst van een label bewaren, en het c15-prefix wordt gedeclareerd op de omringende <c:ext>, niet op het fragment. Code die layouts op lokale naam vindt, pakt dat element ook mee, en het plakken in <c:dLbls> neemt het prefix mee zonder zijn declaratie, dus het herbouwde chartonderdeel bevat een ongedeclareerd namespace-prefix en geen namespace-bewuste XML-parser laadt het nog. Match layouts op namespace en op positie als direct kind, nooit op lokale naam alleen

HotXLS neemt een labellayout alleen van een direct <c:layout>-kind van <c:dLbls> of van een <c:dLbl>, nooit van binnen in een extensie, en bewaart het letterlijk, gesleuteld op puntindex. LabelLayoutXml bevat de layout van het eerste label, inclusief een verdwaalde die direct onder <c:dLbls> staat, want zo interpreteert Excel het; PointLabelLayoutCount, PointLabelLayoutIndexes en PointLabelLayoutXmls sommen de rest op. Bij het regenereren van de grafiek gaat die layout op de <c:dLbl> van idx 0 en elke andere layout op de <c:dLbl> van zijn eigen index, elk samengevoegd met de bereikoverride en tekst van dat punt tot één 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];               // sheetindex vanaf 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 leest een puntlabel als een heel label, dus het als één geheel schrijven, met de handmatige positie op dezelfde <c:dLbl>, houdt zowel de S1; 2; 20-tekst als het verschoven eerste label uit uw grafieken. In HotXLS zit dat model achter DataLabelsRange, de puntoverride-aanroepen en de bewaarde layout-eigenschappen