Etiquetas Value From Cells en gráficos con HotXLS

Después de que un generador reescribe el gráfico, las etiquetas que deberían mostrar la columna C vuelven como números desnudos, o una barra lee S1; 2; 20 en lugar de 20.0, y aun así Excel abre el archivo sin queja alguna. Las etiquetas Value From Cells viven en una extensión de gráfico de Office 2013, que HotXLS escribe a través de TXLSXChartSeries.DataLabelsRange con ajustes de etiqueta completos por punto en orden de esquema

Dónde guarda Excel las etiquetas Value From Cells

El esquema de gráficos de ECMA-376 (Parte 1, §21.2) no tiene ningún elemento que signifique "toma el texto de esta etiqueta de un rango de la hoja". Su tipo CT_DLbls, el bloque <c:dLbls> dentro de una serie, contiene cualquier cantidad de overrides <c:dLbl> de punto único, seguidos por <c:delete> o por el grupo de etiqueta compartido, con una lista de extensiones al final. El grupo compartido es una secuencia fija: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Casi cada casilla del panel Format Data Labels de Excel mapea a uno de esos elementos. Value From Cells no. Excel 2013 lo agregó por encima del estándar mediante listas de extensiones en el namespace c15 (http://schemas.microsoft.com/office/drawing/2012/chart), que Microsoft documenta en [MS-ODRAWXML], así que un lector construido estrictamente desde el esquema ECMA-376 no ve ahí más que elementos <c:ext> opacos. Reducida a las partes que importan, una serie con etiquetas de rango de celdas se ve así:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- cero o más overrides de punto <c:dLbl> encabezan el bloque -->
    <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 funcionalidad está partida en dos lugares. El rango pertenece a la serie: la extensión a nivel de serie con URI {02D57815-91ED-43cb-92C2-25804820EDAC} lleva c15:datalabelsRange, cuyo c15:f guarda la referencia y cuyo c15:dlblRangeCache guarda los textos de las etiquetas tal como se mostraban cuando se guardó el archivo. El interruptor pertenece a las etiquetas: c15:showDataLabelsRange vive en una extensión con URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} al final de <c:dLbls>, y la misma extensión puede cerrar un <c:dLbl> individual para encender o apagar el texto de rango de un punto

Cada mitad falla por su cuenta. Un archivo con el rango pero sin el interruptor no muestra texto de celda, y un archivo con el interruptor pero sin rango no tiene nada que mostrar. Un programa que solo modela lo que §21.2 define pierde ambas cosas cuando regenera la parte del gráfico, razón por la que HotXLS conserva el ChartML no modelado a través de una edición. La caché importa tanto como la referencia: un consumidor que no puede resolver el rango, por ejemplo uno que apunta a otro libro, sigue necesitando texto que dibujar

Fijar el rango de etiquetas en una serie

En HotXLS el rango es una propiedad de la serie. Asignar TXLSXChartSeries.DataLabelsRange también enciende ShowDataLabelsRange y lo marca como fijado explícitamente, porque un rango sin el interruptor jamás se mostraría, y asignar un string vacío lo vuelve a apagar. El texto de celda se combina con los interruptores ordinarios (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), unidos por Separator, mientras que LabelNumFmt formatea la parte del valor. La etiqueta de ecuación de una trendline no es parte de este bloque; vive dentro de <c:trendline> y sigue las reglas propias de Excel para el texto de ecuación de trendline. Cuando se guarda el gráfico, ResolveDataLabelsRange refresca DataLabelsCache desde el texto formateado de las celdas, saltándose filas y columnas ocultas cuando el gráfico grafica solo celdas visibles; un rango hacia otro libro conserva la caché con la que se abrió. La creación del libro y del gráfico sigue el patrón de gráficos usual de HotXLS: un libro nuevo no tiene hoja hasta que usted agrega una, y Sheets.Items cuenta desde 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');

    // También pone ShowDataLabelsRange := True y lo registra como fijado
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // texto de celda más el valor
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // la parte del valor se lee 20.0, no 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

La enumeración ST_DLblPos del esquema lista nueve tokens de posición y acepta cualquiera en cualquier gráfico, así que un validador no pone objeción a outEnd en una serie de líneas. Qué posiciones tienen sentido depende del tipo de gráfico: las series de barras y columnas toman ctr, inBase, inEnd y outEnd, los gráficos de pie agregan bestFit, y las series de líneas y scatter usan l, r, t y b. HotXLS escribe el valor TXLSXChartDataLabelPos elegido como su token y le deja el juicio a Excel, así que LabelPos debe salir del subconjunto que corresponde a su tipo de gráfico. Asignar xlsxDLblPosDefault limpia LabelPosSet, no escribe ningún dLblPos y le deja a Excel colocar las etiquetas. Para el ejemplo de arriba, Esperado: cada barra etiquetada por fuera de su extremo con su texto de celda y su valor a un decimal, las dos partes unidas por |

¿Qué tiene que repetir una etiqueta de un solo punto?

Todo lo que dice la etiqueta de la serie. El esquema hace que un override de punto parezca barato: CT_DLbl es un idx, seguido de delete o de un grupo de etiqueta (un layout y un tx opcionales delante de la misma tanda numFmt-a-separator) y una lista de extensiones opcional, y cada miembro del grupo es opcional. Un <c:dLbl> que no carga más que idx y la extensión showDataLabelsRange es entonces válido para el esquema. Excel dibuja semejante etiqueta como S1; 2; 20 en lugar de 20.0: trata cada interruptor show* omitido como encendido, así que aparecen el nombre de la serie y la categoría, y no hereda el formato numérico de la serie. Una etiqueta de punto reemplaza a la etiqueta de la serie para ese punto en lugar de parcharla, razón por la que las etiquetas de punto que el propio Excel escribe llevan el juego completo de interruptores. Un generador que emite overrides mínimos para mantener el XML chico pasa la validación y aun así muestra el texto equivocado

HotXLS escribe cada etiqueta de punto como un reemplazo completo. SetPointShowDataLabelsRange(APointIndex, AVisible) registra un valor de encendido o apagado para el texto de rango en un punto, indexado por el índice de punto base 0 que se vuelve idx. Al guardar, cada punto así marcado recibe un <c:dLbl>: idx, el formato numérico de la serie, posición e interruptores en orden de grupo, y al final la extensión {CE6537A1-…} con el valor de ese punto. SetPointLabelText(APointIndex, AText) agrega texto literal mediante <c:tx>; un índice con texto, un override de rango y un layout conservado sigue recibiendo un solo <c:dLbl>. Al leer, los interruptores de la serie se toman del nivel de serie, así que las copias dentro de las etiquetas de punto jamás los sobrescriben. El código de abajo fija dos overrides en la serie de ingresos, y el XML que viene después es la etiqueta esperada para el punto 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Segunda barra (idx 1): muestra solo el valor, sin el texto de celda
  Series.SetPointShowDataLabelsRange(1, False);
  // Tercera barra (idx 2): reemplaza la etiqueta completa con texto literal
  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 esperado para el 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>

Que Excel abra un gráfico dice poco sobre su XML

Excel abre gráficos cuyos hijos están fuera de orden, incluso con elementos desconocidos dentro de <c:dLbls>, y lee sus propiedades de vuelta correctamente; un valor de atributo ilegal como val="banana" es lo que hace fallar a Workbooks.Open. Los demás consumidores son menos indulgentes. El OpenXmlValidator del Open XML SDK reporta los hijos fuera de orden como errores de esquema, y las herramientas que se atan estrictamente al esquema los rechazan, así que un gráfico que "funciona en Excel" puede igual romper un pipeline aguas abajo. Valide las partes de gráfico generadas con OpenXmlValidator; un doble clic en Excel solo le dice que Excel las aguantó. Estas son las reglas de orden del esquema §21.2 que es más fácil equivocarse:

• En <c:dLbls>, los overrides de punto van primero, el grupo compartido sigue y extLst cierra el bloque; dentro del grupo dLblPos antecede a cada interruptor show* y showLegendKey antecede a showVal
• Dentro de una etiqueta de punto, layout y tx siguen a idx y van antes de numFmt; la lista de extensiones del punto lo cierra
• Una serie de barras corre idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; una serie de líneas lleva marker donde la de barras lleva invertIfNegative y pictureOptions, y smooth donde aquella lleva shape
• Las series scatter, area y bubble conservan el mismo esqueleto (etiquetas, trendlines, barras de error y datos, en ese orden) con sus miembros propios: xVal y yVal para datos scatter y bubble, y bubbleSize y bubble3D después de los datos en una serie bubble
• Los tropiezos usuales a nivel de serie son referencias de datos escritas antes de dLbls, errBars antes de trendline, y smooth antes de los datos, de ninguno de los cuales se queja Excel

Las posiciones manuales de etiquetas van en una etiqueta de punto

El layout es donde la intuición y el esquema se separan. Cuando un usuario arrastra una etiqueta en Excel, la posición manual se guarda como <c:layout> dentro del <c:dLbl> de esa etiqueta, justo después de idx. CT_DLbls no tiene hijo layout, así que un generador que escribe uno directamente bajo <c:dLbls>, esperando que posicione el conjunto entero, produce un archivo que el validador marca por hijo inválido. Excel abre ese archivo y hace algo que nadie pidió: aplica el layout solo a la primera etiqueta, poniéndola donde la pondría un layout de punto idx 0, y lee mal los anchos de las etiquetas restantes, de modo que un ancho de etiqueta de 37.7 vuelve como 52.3. El gráfico se ve casi bien, lo que hace que el error pase fácil desapercibido en la revisión

Un layout copiado desde una extensión puede dejar ilegible el libro entero. Excel también puede guardar un c15:layout dentro de la lista de extensiones de una etiqueta, y el prefijo c15 se declara en el <c:ext> que lo envuelve, no en el fragmento. El código que encuentra layouts por nombre local también levanta ese elemento, y pegarlo dentro de <c:dLbls> lleva el prefijo sin su declaración, de modo que la parte del gráfico reconstruida contiene un prefijo de namespace sin declarar y ningún parser XML consciente de namespaces la cargará. Empareje layouts por namespace y por posición como hijo directo, jamás por nombre local solamente

HotXLS toma el layout de una etiqueta solo de un hijo <c:layout> directo de <c:dLbls> o de un <c:dLbl>, jamás de dentro de una extensión, y lo conserva verbatim indexado por índice de punto. LabelLayoutXml guarda el layout de la primera etiqueta, incluido uno extraviado encontrado directamente bajo <c:dLbls>, porque así es como lo interpreta Excel; PointLabelLayoutCount, PointLabelLayoutIndexes y PointLabelLayoutXmls listan el resto. Cuando el gráfico se regenera, ese layout va en el <c:dLbl> idx 0 y cada otro layout en el <c:dLbl> de su propio índice, cada uno fusionado con el override de rango y el texto de ese punto en una sola etiqueta

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];               // índice de hoja 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 lee una etiqueta de punto como una etiqueta entera, así que escribirla como tal, con la posición manual en el mismo <c:dLbl>, mantiene fuera de sus gráficos tanto el texto S1; 2; 20 como la primera etiqueta corrida. En HotXLS, ese modelo vive detrás de DataLabelsRange, las llamadas de override de punto y las propiedades de layout conservado