Etykiety Value From Cells mieszkają w rozszerzeniu c15

Po tym, jak generator przepisze wykres, etykiety, które powinny pokazywać kolumnę C, wracają jako gołe liczby, albo jeden słupek czyta się S1; 2; 20 zamiast 20.0, a Excel otwiera plik bez jednego słowa skargi. Etykiety Value From Cells siedzą w rozszerzeniu wykresu z Office 2013, które HotXLS zapisuje przez TXLSXChartSeries.DataLabelsRange z kompletnymi ustawieniami etykiet per punkt w kolejności ze schematu

Gdzie Excel trzyma etykiety Value From Cells

Schemat wykresów ECMA-376 (część 1, §21.2) nie ma elementu znaczącego „weź tekst tej etykiety z zakresu arkusza”. Jego typ CT_DLbls, blok <c:dLbls> wewnątrz serii, mieści dowolną liczbę nadpisań <c:dLbl> dla pojedynczych punktów, po których następuje albo <c:delete>, albo wspólna grupa etykiety, z listą rozszerzeń na końcu. Wspólna grupa to sztywna sekwencja: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Prawie każdy checkbox w panelu Format Data Labels Excela mapuje się na jeden z tych elementów. Value From Cells nie. Excel 2013 dodał go ponad standardem przez listy rozszerzeń w przestrzeni nazw c15 (http://schemas.microsoft.com/office/drawing/2012/chart), którą Microsoft dokumentuje w [MS-ODRAWXML], więc czytnik zbudowany ściśle ze schematu ECMA-376 nie widzi tam nic poza nieprzejrzystymi elementami <c:ext>. Ograne do części, które się liczą, seria z etykietami z zakresu komórek wygląda tak:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- zero lub więcej nadpisań punktów <c:dLbl> otwiera 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>

Funkcja jest rozbita na dwa miejsca. Zakres należy do serii: rozszerzenie poziomu serii z URI {02D57815-91ED-43cb-92C2-25804820EDAC} niesie c15:datalabelsRange, którego c15:f trzyma referencję, a którego c15:dlblRangeCache trzyma teksty etykiet tak, jak były wyświetlane w chwili zapisu pliku. Przełącznik należy do etykiet: c15:showDataLabelsRange siedzi w rozszerzeniu z URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} na końcu <c:dLbls>, i to samo rozszerzenie może domknąć pojedyncze <c:dLbl>, żeby włączyć albo wyłączyć tekst z zakresu dla jednego punktu

Każda połowa zawodzi osobno. Plik z zakresem bez przełącznika nie pokazuje tekstu z komórek, a plik z przełącznikiem bez zakresu nie ma nic do pokazania. Program modelujący tylko to, co definiuje §21.2, gubi jedno i drugie przy regeneracji części wykresu, dlatego HotXLS zachowuje niemodelowane ChartML przez edycję. Cache znaczy tyle, co referencja: konsument, który nie potrafi rozwiązać zakresu, na przykład takiego wskazującego do innego skoroszytu, wciąż potrzebuje tekstu do narysowania

Ustawianie zakresu etykiet na serii

W HotXLS zakres jest własnością serii. Przypisanie TXLSXChartSeries.DataLabelsRange przy okazji włącza ShowDataLabelsRange i odnotowuje go jako jawnie ustawiony, bo zakres bez przełącznika nigdy nie zostałby wyświetlony, a przypisanie pustego łańcucha wyłącza go z powrotem. Tekst z komórek łączy się ze zwykłymi przełącznikami (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), sklejany przez Separator, a LabelNumFmt formatuje część wartości. Etykieta równania linii trendu nie jest częścią tego bloku; mieszka wewnątrz <c:trendline> i podlega własnym regułom Excela dla tekstu równania linii trendu. Przy zapisie wykresu ResolveDataLabelsRange odświeża DataLabelsCache ze sformatowanego tekstu komórek, pomijając ukryte wiersze i kolumny, gdy wykres kreśli tylko widoczne komórki; zakres wskazujący do innego skoroszytu zachowuje cache, z którym został otwarty. Tworzenie skoroszytu i wykresu idzie za zwykłym wzorcem wykresów HotXLS: nowy skoroszyt nie ma arkusza, dopóki go nie dodasz, a Sheets.Items liczy od 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');

    // Przy okazji ustawia ShowDataLabelsRange := True i odnotowuje to jako ustawione
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // tekst z komórek plus wartość
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // część wartości czyta się 20.0, nie 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

Wyliczenie ST_DLblPos ze schematu wymienia dziewięć tokenów pozycji i przyjmuje każdy z nich na każdym wykresie, więc walidator nie ma zastrzeżeń do outEnd na serii liniowej. Które pozycje mają sens, zależy od rodzaju wykresu: serie słupkowe i kolumnowe biorą ctr, inBase, inEnd i outEnd, wykresy kołowe dodają bestFit, a serie liniowe i rozrzutu używają l, r, t i b. HotXLS zapisuje wybraną wartość TXLSXChartDataLabelPos jako jej token i zostawia ocenę Excelowi, więc LabelPos powinno pochodzić z podzbioru należącego do twojego rodzaju wykresu. Przypisanie xlsxDlblPosDefault czyści LabelPosSet, nie zapisuje wcale dLblPos i pozwala Excelowi samemu rozmieścić etykiety. Dla przykładu wyżej oczekiwane: każdy słupek z etykietą na zewnątrz swojego końca, z tekstem z komórki i wartością do jednego miejsca po przecinku, obie części sklejone przez |

Co etykieta pojedynczego punktu musi powtórzyć?

Wszystko, co mówi etykieta serii. Schemat sprawia, że nadpisanie punktu wygląda tanio: CT_DLbl to idx, po którym idzie albo delete, albo grupa etykiety (opcjonalne layout i tx przed tym samym ciągiem od numFmt do separator) i opcjonalna lista rozszerzeń, a każdy członek grupy jest opcjonalny. <c:dLbl> z samym idx i rozszerzeniem showDataLabelsRange jest więc poprawny ze schematem. Excel renderuje taką etykietę jako S1; 2; 20 zamiast 20.0: każdy pominięty przełącznik show* traktuje jako włączony, więc pojawia się nazwa serii i kategoria, i nie dziedziczy formatu liczbowego serii. Etykieta punktu zastępuje etykietę serii dla tego punktu, zamiast ją łatać, dlatego etykiety punktów, które sam Excel zapisuje, niosą kompletny zestaw przełączników. Generator emitujący minimalne nadpisania, żeby trzymać XML małym, przechodzi walidację i wciąż pokazuje zły tekst

HotXLS zapisuje każdą etykietę punktu jako pełne zastąpienie. SetPointShowDataLabelsRange(APointIndex, AVisible) odnotowuje wartość włączoną albo wyłączoną dla tekstu z zakresu na jednym punkcie, kluczowaną indeksem punktu od zera, który staje się idx. W chwili zapisu każdy taki punkt dostaje jedno <c:dLbl>: idx, format liczbowy serii, pozycję i przełączniki w kolejności grupy, a na końcu rozszerzenie {CE6537A1-…} niosące wartość tego punktu. SetPointLabelText(APointIndex, AText) dodaje literalny tekst przez <c:tx>; indeks z tekstem, nadpisaniem zakresu i zachowanym layoutem wciąż dostaje jedno <c:dLbl>. Przy czytaniu przełączniki serii są brane z poziomu serii, więc kopie wewnątrz etykiet punktów nigdy ich nie nadpisują. Kod poniżej ustawia dwa nadpisania na serii przychodów, a XML po nim to etykieta oczekiwana dla punktu 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Drugi słupek (idx 1): pokaż samą wartość, bez tekstu z komórki
  Series.SetPointShowDataLabelsRange(1, False);
  // Trzeci słupek (idx 2): zastąp całą etykietę literalnym tekstem
  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;

<!-- Oczekiwany XML dla punktu 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>

Otwarcie wykresu w Excelu mało mówi o jego XML-u

Excel otwiera wykresy z dziećmi w złej kolejności, nawet z nieznanymi elementami wewnątrz <c:dLbls>, i czyta ich właściwości poprawnie z powrotem; nielegalna wartość atrybutu, taka jak val="banana", to to, co sprawia, że Workbooks.Open zawodzi. Inni konsumenci są mniej wyrozumiali. OpenXmlValidator z Open XML SDK zgłasza dzieci w złej kolejności jako błędy schematu, a narzędzia wiążące schemat ściśle je odrzucają, więc wykres, który „działa w Excelu”, wciąż może złamać downstreamowy potok. Waliduj generowane części wykresów przez OpenXmlValidator; dwuklik w Excelu mówi ci tylko, że Excel sobie poradził. To reguły kolejności ze schematu §21.2, o które najłatwiej się potknąć:

• W <c:dLbls> nadpisania punktów idą na czele, wspólna grupa za nimi, a extLst domyka blok; wewnątrz grupy dLblPos wyprzedza każdy przełącznik show*, a showLegendKey wyprzedza showVal
• Wewnątrz etykiety punktu layout i tx idą za idx i przychodzą przed numFmt; lista rozszerzeń punktu go domyka
• Seria słupkowa biegnie idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; seria liniowa ma marker tam, gdzie seria słupkowa ma invertIfNegative i pictureOptions, i smooth tam, gdzie ma shape
• Serie rozrzutu, warstwowe i bąbelkowe trzymają ten sam szkielet (etykiety, linie trendu, słupki błędów i dane, w tej kolejności) z własnymi członkami: xVal i yVal dla danych rozrzutu i bąbelków, oraz bubbleSize i bubble3D po danych w serii bąbelkowej
• Zwykłe potknięcia na poziomie serii to referencje danych zapisane przed dLbls, errBars przed trendline i smooth przed danymi, i na żaden z nich Excel nie narzeka

Ręczne pozycje etykiet idzą na etykietę punktu

Layout to miejsce, gdzie intuicja i schemat się rozchodzą. Gdy użytkownik przeciągnie etykietę w Excelu, ręczna pozycja jest zapisywana jako <c:layout> wewnątrz <c:dLbl> tej etykiety, wprost po idx. CT_DLbls nie ma dziecka layout, więc generator zapisujący je bezpośrednio pod <c:dLbls>, licząc, że ustawi cały zestaw, produkuje plik, który walidator flaguje jako nieprawidłowe dziecko. Excel otwiera ten plik i robi coś, o co nikt nie prosił: aplikuje layout tylko do pierwszej etykiety, kładzie ją tam, gdzie położyłaby ją layout punktu idx 0, i błędnie czyta szerokości pozostałych etykiet, więc szerokość etykiety 37.7 wraca jako 52.3. Wykres wygląda prawie dobrze, co czyni ten błąd łatwym do przegapienia w recenzji

Layout wycięty z rozszerzenia może uczynić cały skoroszyt nieczytelnym. Excel potrafi też trzymać c15:layout wewnątrz listy rozszerzeń etykiety, a prefiks c15 jest deklarowany na otaczającym <c:ext>, nie na fragmencie. Kod znajdujący layouty po nazwie lokalnej podchwyci i ten element, a wklejenie go do <c:dLbls> niesie prefiks bez jego deklaracji, więc przebudowana część wykresu zawiera niezadeklarowany prefiks przestrzeni nazw i żaden parser XML świadomy przestrzeni nazw go nie wczyta. Dopasowuj layouty po przestrzeni nazw i po pozycji bezpośredniego dziecka, nigdy po samej nazwie lokalnej

HotXLS bierze layout etykiety wyłącznie z bezpośredniego dziecka <c:layout> <c:dLbls> albo <c:dLbl>, nigdy z wnętrza rozszerzenia, i trzyma go verbatim kluczowany indeksem punktu. LabelLayoutXml trzyma layout pierwszej etykiety, łącznie z zabłąkanym znalezionym wprost pod <c:dLbls>, bo tak interpretuje go Excel; PointLabelLayoutCount, PointLabelLayoutIndexes i PointLabelLayoutXmls wyliczają resztę. Przy regeneracji wykresu ten layout ląduje na <c:dLbl> idx 0, a każdy inny na <c:dLbl> własnego indeksu, każdy sklejony z nadpisaniem zakresu i tekstem tego punktu w jedną etykietę

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];               // indeks arkusza liczony od 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 czyta etykietę punktu jako całą etykietę, więc zapisywanie jej jako jednej, z ręczną pozycją na tym samym <c:dLbl>, trzyma i tekst S1; 2; 20, i przesuniętą pierwszą etykietę z dala od twoich wykresów. W HotXLS ten model siedzi za DataLabelsRange, wywołaniami nadpisań punktów i właściwościami zachowanego layoutu