Le etichette Value From Cells stanno in una estensione c15

Dopo che un generatore riscrive il grafico, le etichette che dovrebbero mostrare la colonna C tornano come numeri nudi, o una barra mostra S1; 2; 20 invece di 20.0, eppure Excel apre il file senza lamentarsi. Le etichette Value From Cells stanno in un'estensione di grafico di Office 2013, che HotXLS scrive tramite TXLSXChartSeries.DataLabelsRange con impostazioni di etichetta complete per punto e in ordine di schema

Dove Excel custodisce le etichette Value From Cells

Lo schema dei grafici ECMA-376 (Parte 1, §21.2) non ha alcun elemento che significhi "prendi il testo di questa etichetta da un intervallo del foglio di lavoro". Il suo tipo CT_DLbls, il blocco <c:dLbls> dentro una serie, contiene un numero qualsiasi di override <c:dLbl> a punto singolo, seguiti o da <c:delete> o dal gruppo di etichetta condiviso, con una lista di estensioni in coda. Il gruppo condiviso è una sequenza fissa: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Quasi ogni casella di controllo nel pannello Formato etichette dati di Excel corrisponde a uno di quegli elementi. Value From Cells no. Excel 2013 l'ha aggiunto sopra lo standard tramite liste di estensioni nel namespace c15 (http://schemas.microsoft.com/office/drawing/2012/chart), che Microsoft documenta in [MS-ODRAWXML], così un lettore costruito rigorosamente dallo schema ECMA-376 non vede lì altro che elementi <c:ext> opachi. Ridotto alle parti che contano, una serie con etichette da intervallo di celle appare così:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- zero o più override di punto <c:dLbl> aprono il blocco -->
    <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 funzionalità è spaccata tra due posti. L'intervallo appartiene alla serie: l'estensione a livello di serie con URI {02D57815-91ED-43cb-92C2-25804820EDAC} porta c15:datalabelsRange, il cui c15:f contiene il riferimento e il cui c15:dlblRangeCache contiene i testi delle etichette così come erano mostrati quando il file è stato salvato. L'interruttore appartiene alle etichette: c15:showDataLabelsRange sta in un'estensione con URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} in coda a <c:dLbls>, e la stessa estensione può chiudere un singolo <c:dLbl> per accendere o spegnere il testo da intervallo per un punto

Ogni metà da sola non basta. Un file con l'intervallo ma senza interruttore non mostra testo delle celle, e un file con l'interruttore ma senza intervallo non ha nulla da mostrare. Un programma che modella solo ciò che §21.2 definisce perde entrambe quando rigenera la parte del grafico, ed è per questo che HotXLS conserva il ChartML non modellato attraverso una modifica. La cache conta quanto il riferimento: un consumatore che non riesce a risolvere l'intervallo, per esempio uno che punta in un'altra cartella di lavoro, ha comunque bisogno di testo da disegnare

Impostare l'intervallo di etichette su una serie

In HotXLS l'intervallo è una proprietà della serie. Assegnare TXLSXChartSeries.DataLabelsRange accende anche ShowDataLabelsRange e lo marca come impostato esplicitamente, perché un intervallo senza l'interruttore non verrebbe mai mostrato, e assegnare una stringa vuota lo spegne di nuovo. Il testo delle celle si combina con gli interruttori ordinari (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), uniti da Separator, mentre LabelNumFmt formatta la parte valore. L'etichetta dell'equazione di una linea di tendenza non fa parte di questo blocco; sta dentro <c:trendline> e segue le regole di Excel per il testo dell'equazione delle linee di tendenza. Quando il grafico viene salvato, ResolveDataLabelsRange aggiorna DataLabelsCache dal testo formattato delle celle, saltando righe e colonne nascoste quando il grafico traccia solo le celle visibili; un intervallo verso un'altra cartella di lavoro conserva la cache con cui è stato aperto. La creazione di cartelle di lavoro e grafici segue il consueto schema dei grafici di HotXLS: una nuova cartella di lavoro non ha fogli finché non ne aggiungete uno, e Sheets.Items conta da 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');

    // Imposta anche ShowDataLabelsRange := True e lo registra come impostato
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // testo della cella più il valore
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // la parte valore legge 20.0, non 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

L'enumerazione ST_DLblPos dello schema elenca nove token di posizione e li accetta tutti su qualsiasi grafico, così un validatore non solleva obiezioni a outEnd su una serie a linee. Quali posizioni abbiano senso dipende dal tipo di grafico: le serie a barre e colonne prendono ctr, inBase, inEnd e outEnd, i grafici a torta aggiungono bestFit, e le serie a linee e a dispersione usano l, r, t e b. HotXLS scrive il valore TXLSXChartDataLabelPos scelto come il suo token e lascia il giudizio a Excel, così LabelPos dovrebbe venire dal sottoinsieme che appartiene al vostro tipo di grafico. Assegnare xlsxDlblPosDefault cancella LabelPosSet, non scrive alcun dLblPos e lascia a Excel il piazzamento delle etichette. Per l'esempio qui sopra, risultato atteso: ogni barra etichettata oltre la sua estremità con il testo della sua cella e il suo valore a una cifra decimale, le due parti unite da |

Che cosa deve ripetere un'etichetta a punto singolo?

Tutto ciò che dice l'etichetta della serie. Lo schema fa sembrare economico un override di punto: CT_DLbl è un idx, seguito o da delete o da un gruppo di etichetta (un opzionale layout e tx davanti alla stessa sequenza da numFmt a separator) e da una lista di estensioni opzionale, e ogni membro del gruppo è opzionale. Una <c:dLbl> che non contiene altro che idx e l'estensione showDataLabelsRange è quindi valida rispetto allo schema. Excel disegna una tale etichetta come S1; 2; 20 invece di 20.0: tratta ogni interruttore show* omesso come acceso, così il nome della serie e la categoria appaiono, e non eredita il formato numerico della serie. Un'etichetta di punto sostituisce l'etichetta della serie per quel punto invece di correggerla, ed è per questo che le etichette di punto che Excel stesso scrive portano il set completo di interruttori. Un generatore che emette override minimali per tenere piccolo l'XML passa la validazione e mostra comunque il testo sbagliato

HotXLS scrive ogni etichetta di punto come una sostituzione completa. SetPointShowDataLabelsRange(APointIndex, AVisible) registra un valore on o off per il testo da intervallo su un punto, con chiave l'indice di punto 0-based che diventa idx. Al salvataggio ogni punto di questo tipo riceve una <c:dLbl>: idx, il formato numerico della serie, posizione e interruttori in ordine di gruppo, e per ultima l'estensione {CE6537A1-…} che trasporta il valore di quel punto. SetPointLabelText(APointIndex, AText) aggiunge testo letterale tramite <c:tx>; un indice con testo, un override di intervallo e un layout conservato riceve comunque una sola <c:dLbl>. In lettura gli interruttori della serie vengono presi dal livello di serie, così le copie dentro le etichette di punto non li sovrascrivono mai. Il codice qui sotto imposta due override sulla serie dei ricavi, e l'XML che segue è l'etichetta attesa per il punto 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Seconda barra (idx 1): mostra solo il valore, senza il testo della cella
  Series.SetPointShowDataLabelsRange(1, False);
  // Terza barra (idx 2): rimpiazza tutta la label con testo letterale
  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 atteso per il punto 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>

Excel che apre un grafico dimostra poco del suo XML

Excel apre grafici con i figli fuori ordine, anche con elementi sconosciuti dentro <c:dLbls>, e rilegge le loro proprietà correttamente; un valore di attributo illegale come val="banana" è ciò che fa fallire Workbooks.Open. Gli altri consumatori sono meno tolleranti. Il OpenXmlValidator dell'Open XML SDK segnala i figli fuori ordine come errori di schema, e gli strumenti che legano lo schema rigidamente li rifiutano, così un grafico che "funziona in Excel" può comunque rompere una pipeline a valle. Validate le parti di grafico generate con OpenXmlValidator; un doppio clic in Excel vi dice solo che Excel se la è cavata. Queste sono le regole di ordinamento dello schema §21.2 più facili da sbagliare:

• In <c:dLbls>, gli override di punto aprono, il gruppo condiviso segue e extLst chiude il blocco; dentro il gruppo dLblPos precede ogni interruttore show* e showLegendKey precede showVal
• Dentro un'etichetta di punto, layout e tx seguono idx e arrivano prima di numFmt; la lista di estensioni del punto lo chiude
• Una serie a barre corre idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; una serie a linee ha marker dove la serie a barre ha invertIfNegative e pictureOptions, e smooth dove ha shape
• Le serie a dispersione, area e bolle conservano lo stesso scheletro (etichette, linee di tendenza, barre di errore e dati, in quest'ordine) con i propri membri: xVal e yVal per i dati di dispersione e bolle, e bubbleSize e bubble3D dopo i dati in una serie a bolle
• I passi falsi consueti a livello di serie sono riferimenti ai dati scritti prima di dLbls, errBars davanti a trendline, e smooth davanti ai dati, e su nessuno di questi Excel si lamenta

Le posizioni manuali delle etichette vanno su un'etichetta di punto

Il layout è dove l'intuizione e lo schema prendono strade diverse. Quando un utente trascina un'etichetta in Excel, la posizione manuale viene memorizzata come <c:layout> dentro la <c:dLbl> di quell'etichetta, subito dopo idx. CT_DLbls non ha alcun figlio layout, così un generatore che ne scrive uno direttamente sotto <c:dLbls>, aspettandosi che posizioni l'intero insieme, produce un file che il validatore segnala come figlio non valido. Excel apre quel file e fa qualcosa che nessuno ha chiesto: applica il layout alla prima etichetta soltanto, mettendola dove la metterebbe un layout di punto idx 0, e legge male le larghezze delle etichette rimanenti, così una larghezza di etichetta di 37.7 torna come 52.3. Il grafico sembra quasi giusto, il che rende l'errore facile da farsi sfuggire in revisione

Un layout copiato fuori da un'estensione può rendere illeggibile l'intera cartella di lavoro. Excel può anche tenere un c15:layout dentro la lista di estensioni di un'etichetta, e il prefisso c15 è dichiarato sulla <c:ext> che lo racchiude, non sul frammento. Un codice che trova i layout per nome locale raccoglie anche quell'elemento, e incollarlo dentro <c:dLbls> trasporta il prefisso senza la sua dichiarazione, così la parte di grafico ricostruita contiene un prefisso di namespace non dichiarato e nessun parser XML che gestisca i namespace la caricherà. Abbinate i layout per namespace e per posizione di figlio diretto, mai per solo nome locale

HotXLS prende il layout di un'etichetta solo da un figlio diretto <c:layout> di <c:dLbls> o di una <c:dLbl>, mai da dentro un'estensione, e lo conserva alla lettera con l'indice di punto come chiave. LabelLayoutXml contiene il layout della prima etichetta, incluso uno smarrito trovato direttamente sotto <c:dLbls>, dato che è così che Excel lo interpreta; PointLabelLayoutCount, PointLabelLayoutIndexes e PointLabelLayoutXmls elencano il resto. Quando il grafico viene rigenerato, quel layout va sulla <c:dLbl> idx 0 e ogni altro layout sulla <c:dLbl> del proprio indice, ognuno fuso con l'override di intervallo e il testo di quel punto in una sola etichetta

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];               // indice di foglio 1-based
    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 legge un'etichetta di punto come un'etichetta intera, quindi scriverla come tale, con la posizione manuale sulla stessa <c:dLbl>, tiene fuori dai vostri grafici sia il testo S1; 2; 20 sia la prima etichetta spostata. In HotXLS, quel modello sta dietro a DataLabelsRange, alle chiamate di override di punto e alle proprietà di layout conservato