Etiquetas de datos Value From Cells en Excel desde Delphi

Después de que un generador reescribe el gráfico, las etiquetas que deberían mostrar la columna C vuelven como números pelados, 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 completos de etiqueta 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, admite cualquier cantidad de overrides <c:dLbl> de punto único, seguidos de <c:delete> o del 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 añadió 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 <c:dLbl> de punto 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 función está repartida entre dos sitios. El rango pertenece a la serie: la extensión de nivel de serie con URI {02D57815-91ED-43cb-92C2-25804820EDAC} lleva c15:datalabelsRange, cuyo c15:f contiene la referencia y cuyo c15:dlblRangeCache contiene los textos de las etiquetas tal como se mostraban cuando se guardó el archivo. El switch 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 activar o desactivar el texto del rango en un punto

Cada mitad falla por sí sola. Un archivo con el rango pero sin el switch no muestra texto de celda, y un archivo con el switch pero sin rango no tiene nada que mostrar. Un programa que modela solo lo que §21.2 define pierde ambas cosas cuando regenera la parte de gráfico, y por eso HotXLS conserva el ChartML sin modelar 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 activa ShowDataLabelsRange y lo marca como establecido explícitamente, porque un rango sin el switch nunca se mostraría, y asignar una cadena vacía lo vuelve a apagar. El texto de celda se combina con los switches ordinarios (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), unidos por Separator, mientras que LabelNumFmt da formato a la parte del valor. La etiqueta de ecuación de una línea de tendencia no forma parte de este bloque; vive dentro de <c:trendline> y sigue las reglas propias de Excel para el texto de ecuación de líneas de tendencia. Al guardar el gráfico, ResolveDataLabelsRange refresca DataLabelsCache desde el texto formateado de las celdas, saltándose filas y columnas ocultas cuando el gráfico traza solo celdas visibles; un rango hacia otro libro conserva la caché con la que se abrió. La creación de libros y gráficos sigue el patrón de gráficos habitual de HotXLS: un libro nuevo no tiene hojas hasta que añades 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 activa ShowDataLabelsRange := True y lo registra como establecido
    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 pegas a outEnd en una serie de líneas. Qué posiciones tienen sentido depende del tipo de gráfico: las series de barras y columnas admiten ctr, inBase, inEnd y outEnd, los gráficos circulares añaden bestFit, y las series de líneas y dispersión usan l, r, t y b. HotXLS escribe el valor TXLSXChartDataLabelPos elegido como su token y deja el juicio a Excel, así que LabelPos debería salir del subconjunto que corresponde a tu tipo de gráfico. Asignar xlsxDlblPosDefault limpia LabelPosSet, no escribe ningún dLblPos y deja que Excel coloque las etiquetas por su cuenta. Para el ejemplo de arriba, esperado: cada barra etiquetada 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 punto único

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 por delante de la misma secuencia de numFmt a separator) y una lista de extensiones opcional, y cada miembro del grupo es opcional. Un <c:dLbl> que no lleva más que idx y la extensión showDataLabelsRange es por tanto válido para el esquema. Excel renderiza esa etiqueta como S1; 2; 20 en lugar de 20.0: trata cada switch show* omitido como activado, 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 la etiqueta de la serie para ese punto en lugar de parchearla, y por eso las etiquetas de punto que el propio Excel escribe llevan el juego completo de switches. Un generador que emite overrides mínimos para mantener el XML pequeño pasa la validación y sigue mostrando el texto equivocado

HotXLS escribe cada etiqueta de punto como un reemplazo completo. SetPointShowDataLabelsRange(APointIndex, AVisible) registra un valor on u off para el texto del rango en un punto, claveado por el índice de punto 0-based que se convierte en idx. Al guardar, cada punto de esos recibe un <c:dLbl>: idx, el formato numérico de la serie, posición y switches en orden de grupo, y al final la extensión {CE6537A1-…} con el valor de ese punto. SetPointLabelText(APointIndex, AText) añade texto literal a través de <c:tx>; un índice con texto, un override de rango y un layout conservado sigue recibiendo un único <c:dLbl>. Al leer, los switches de la serie se toman del nivel de serie, así que las copias dentro de las etiquetas de punto nunca 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 entera 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 con hijos fuera de orden, incluso con elementos desconocidos dentro de <c:dLbls>, y lee sus propiedades correctamente; un valor de atributo ilegal como val="banana" es lo que hace fallar a Workbooks.Open. Otros consumidores son menos indulgentes. El OpenXmlValidator del Open XML SDK reporta los hijos fuera de orden como errores de esquema, y las herramientas que ligan el esquema estrictamente los rechazan, así que un gráfico que «funciona en Excel» aún puede romper un pipeline posterior. Valida las partes de gráfico generadas con OpenXmlValidator; un doble clic en Excel solo te dice que Excel apañó. Estas son las reglas de orden del esquema §21.2 con las que es más fácil equivocarse:

• En <c:dLbls>, los overrides de punto encabezan, el grupo compartido sigue y extLst cierra el bloque; dentro del grupo dLblPos precede a cada switch show* y showLegendKey precede 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 tiene marker donde la de barras tiene invertIfNegative y pictureOptions, y smooth donde esta tiene shape
• Las series de dispersión, área y burbuja conservan el mismo esqueleto (etiquetas, líneas de tendencia, barras de error y datos, en ese orden) con sus miembros propios: xVal e yVal para datos de dispersión y burbuja, y bubbleSize y bubble3D tras los datos en una serie de burbuja
• Los deslices habituales de nivel de serie son referencias de datos escritas antes de dLbls, errBars por delante de trendline, y smooth por delante de los datos, y Excel no se queja con ninguno

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 como 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, así 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ácilmente desapercibido en la revisión

Un layout copiado fuera de una extensión puede dejar todo el libro ilegible. 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 captura también ese elemento, y pegarlo en <c:dLbls> arrastra el prefijo sin su declaración, así que la parte de gráfico reconstruida contiene un prefijo de namespace sin declarar y ningún parser XML consciente de namespaces la cargará. Empareja layouts por namespace y por posición como hijo directo, nunca por nombre local solo

HotXLS toma el layout de una etiqueta solo de un hijo <c:layout> directo de <c:dLbls> o de un <c:dLbl>, nunca de dentro de una extensión, y lo conserva verbatim claveado por índice de punto. LabelLayoutXml guarda el layout de la primera etiqueta, incluido un extraviado encontrado directamente bajo <c:dLbls>, porque así lo interpreta Excel; PointLabelLayoutCount, PointLabelLayoutIndexes y PointLabelLayoutXmls listan el resto. Al regenerar el gráfico, ese layout va en el <c:dLbl> del 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 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 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 tanto el texto S1; 2; 20 como la primera etiqueta desplazada fuera de tus gráficos. En HotXLS, ese modelo está detrás de DataLabelsRange, las llamadas de override de punto y las propiedades de layout conservado