Rótulos Valor De Células do Excel vivem numa extensão c15

Depois que um gerador reescreve o gráfico, rótulos que deveriam mostrar a coluna C voltam como números crus, ou uma barra lê S1; 2; 20 em vez de 20.0, mas o Excel abre o arquivo sem reclamar. Os rótulos de Valor De Células ficam numa extensão de gráfico do Office 2013, que o HotXLS grava via TXLSXChartSeries.DataLabelsRange com configurações completas de rótulo por ponto na ordem do schema

Onde o Excel guarda os rótulos de Valor De Células

O schema de gráfico ECMA-376 (Parte 1, §21.2) não tem elemento nenhum que signifique “pegar o texto deste rótulo de um intervalo da planilha”. O tipo CT_DLbls dele, o bloco <c:dLbls> dentro de uma série, guarda qualquer número de overrides de ponto único <c:dLbl>, seguidos de <c:delete> ou do grupo de rótulo compartilhado, com uma lista de extensão no fim. O grupo compartilhado é uma sequência fixa: numFmt, spPr, txPr, dLblPos, showLegendKey, showVal, showCatName, showSerName, showPercent, showBubbleSize, separator, showLeaderLines, leaderLines. Quase toda caixa de seleção do painel Formatar Rótulos de Dados do Excel mapeia num desses elementos. Valor De Células não. O Excel 2013 o adicionou por cima do padrão por meio de listas de extensão no namespace c15 (http://schemas.microsoft.com/office/drawing/2012/chart), que a Microsoft documenta em [MS-ODRAWXML], então um leitor construído estritamente a partir do schema ECMA-376 não vê ali nada além de elementos <c:ext> opacos. Reduzida às partes que importam, uma série com rótulos de intervalo de células fica assim:

<c:ser>
  <c:idx val="0"/><c:order val="0"/>
  <c:tx>...</c:tx>
  <c:dLbls>
    <!-- zero ou mais overrides de ponto <c:dLbl> abrem o bloco -->
    <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>

O recurso fica dividido entre dois lugares. O intervalo pertence à série: a extensão de nível de série com URI {02D57815-91ED-43cb-92C2-25804820EDAC} carrega o c15:datalabelsRange, cujo c15:f guarda a referência e cujo c15:dlblRangeCache guarda os textos dos rótulos como estavam sendo exibidos quando o arquivo foi salvo. A chave pertence aos rótulos: o c15:showDataLabelsRange fica numa extensão com URI {CE6537A1-D6FC-4f65-9D91-7224C49458BB} no fim do <c:dLbls>, e a mesma extensão pode fechar um <c:dLbl> individual para ligar ou desligar o texto do intervalo num ponto

Cada metade falha sozinha. Um arquivo com o intervalo mas sem a chave não mostra texto de célula, e um arquivo com a chave mas sem o intervalo não tem nada para mostrar. Um programa que modela só o que o §21.2 define perde as duas coisas quando regenera a chart part, e é por isso que o HotXLS preserva ChartML não modelado durante uma edição. O cache importa tanto quanto a referência: um consumidor que não consegue resolver o intervalo, por exemplo um que aponta para outra pasta de trabalho, ainda precisa de texto para desenhar

Definindo o intervalo de rótulos numa série

No HotXLS o intervalo é uma propriedade da série. Atribuir o TXLSXChartSeries.DataLabelsRange também liga o ShowDataLabelsRange e o marca como definido explicitamente, porque um intervalo sem a chave jamais seria exibido, e atribuir uma string vazia o desliga de novo. O texto da célula se combina com as chaves comuns (ShowValue, ShowCategoryName, ShowSeriesName, ShowPercent, ShowLegendKey, ShowBubbleSize), unidas pelo Separator, enquanto o LabelNumFmt formata a parte do valor. O rótulo de equação de uma linha de tendência não faz parte deste bloco; ele vive dentro do <c:trendline> e segue as regras do próprio Excel para texto de equação de linha de tendência. Quando o gráfico é salvo, o ResolveDataLabelsRange atualiza o DataLabelsCache a partir do texto formatado das células, pulando linhas e colunas ocultas quando o gráfico plota só células visíveis; um intervalo para outra pasta de trabalho mantém o cache com que foi aberto. A criação de pasta de trabalho e gráfico segue o padrão de gráfico usual do HotXLS: uma pasta de trabalho nova não tem planilha até você adicionar uma, e o Sheets.Items conta a partir de 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');

    // Também define ShowDataLabelsRange := True e o registra como definido
    Series.DataLabelsRange := 'Sales!$C$1:$C$3';
    Series.ShowValue := True;           // texto da célula mais o valor
    Series.Separator := ' | ';
    Series.LabelNumFmt := '0.0';        // a parte do valor lê 20.0, não 20
    Series.LabelPos := xlsxDlblPosOutEnd;

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

A enumeração ST_DLblPos do schema lista nove tokens de posição e aceita qualquer um deles em qualquer gráfico, então um validador não objeta outEnd numa série de linhas. Quais posições fazem sentido depende do tipo de gráfico: séries de barras e colunas aceitam ctr, inBase, inEnd e outEnd, gráficos de pizza adicionam bestFit, e séries de linhas e dispersão usam l, r, t e b. O HotXLS grava o valor TXLSXChartDataLabelPos escolhido como o token dele e deixa o julgamento para o Excel, então o LabelPos deve vir do subconjunto que pertence ao seu tipo de gráfico. Atribuir xlsxDlblPosDefault limpa o LabelPosSet, não grava dLblPos nenhum e deixa o Excel posicionar os rótulos por conta própria. Para o exemplo acima, Esperado: cada barra rotulada fora da ponta dela com o texto da célula dela e o valor dela com uma casa decimal, as duas partes unidas por |

O que um rótulo de ponto único tem que repetir?

Tudo o que o rótulo da série diz. O schema faz um override de ponto parecer barato: o CT_DLbl é um idx, seguido de delete ou de um grupo de rótulo (um layout e um tx opcionais à frente do mesmo trecho que vai de numFmt a separator) e de uma lista de extensão opcional, e todo membro do grupo é opcional. Um <c:dLbl> que não carrega nada além de idx e da extensão showDataLabelsRange é, portanto, válido pelo schema. O Excel renderiza tal rótulo como S1; 2; 20 em vez de 20.0: ele trata cada chave show* omitida como ligada, então o nome da série e a categoria aparecem, e ele não herda o formato numérico da série. Um rótulo de ponto substitui o rótulo da série para aquele ponto em vez de remendá-lo, e é por isso que os rótulos de ponto que o próprio Excel grava carregam o conjunto completo de chaves. Um gerador que emite overrides mínimos para manter o XML pequeno passa na validação e ainda mostra o texto errado

O HotXLS grava todo rótulo de ponto como uma substituição completa. O SetPointShowDataLabelsRange(APointIndex, AVisible) registra um valor ligado ou desligado para o texto do intervalo num ponto, indexado pelo índice de ponto base 0 que vira o idx. No momento de salvar, cada ponto desse ganha um <c:dLbl>: o idx, o formato numérico da série, posição e chaves na ordem do grupo, e por último a extensão {CE6537A1-…} carregando o valor daquele ponto. O SetPointLabelText(APointIndex, AText) adiciona texto literal via <c:tx>; um índice com texto, um override de intervalo e um layout preservado ainda ganha um único <c:dLbl>. Na leitura, as chaves da série são tiradas do nível da série, então as cópias dentro dos rótulos de ponto jamais as sobrescrevem. O código abaixo define dois overrides na série de receita, e o XML depois dele é o rótulo esperado para o ponto 1

procedure OverridePoints(Series: TXLSXChartSeries);
var
  I: Integer;
  Visible: Boolean;
begin
  // Segunda barra (idx 1): mostrar só o valor, sem o texto da célula
  Series.SetPointShowDataLabelsRange(1, False);
  // Terceira barra (idx 2): substituir o rótulo inteiro por 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 o ponto 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>

O Excel abrir um gráfico prova pouco sobre o XML dele

O Excel abre gráficos cujos filhos estão fora de ordem, mesmo com elementos desconhecidos dentro do <c:dLbls>, e lê as propriedades deles de volta corretamente; um valor de atributo ilegal como val="banana" é o que faz o Workbooks.Open falhar. Outros consumidores são menos tolerantes. O OpenXmlValidator do Open XML SDK reporta filhos fora de ordem como erros de schema, e ferramentas que ligam o schema estritamente os rejeitam, então um gráfico que “funciona no Excel” ainda pode quebrar um pipeline downstream. Valide as chart parts geradas com o OpenXmlValidator; um duplo clique no Excel só diz que o Excel se virou. Estas são as regras de ordem do schema §21.2 mais fáceis de errar:

• No <c:dLbls>, os overrides de ponto vêm primeiro, o grupo compartilhado vem depois e o extLst fecha o bloco; dentro do grupo, o dLblPos precede toda chave show* e o showLegendKey precede o showVal
• Dentro de um rótulo de ponto, layout e tx seguem o idx e vêm antes do numFmt; a lista de extensão do ponto o fecha
• Uma série de barras corre idx, order, tx, spPr, invertIfNegative, pictureOptions, dPt, dLbls, trendline, errBars, cat, val, shape, extLst; uma série de linhas tem marker onde a série de barras tem invertIfNegative e pictureOptions, e smooth onde ela tem shape
• Séries de dispersão, área e bolha mantêm o mesmo esqueleto (rótulos, linhas de tendência, barras de erro e dados, nessa ordem) com membros próprios: xVal e yVal para dados de dispersão e bolha, e bubbleSize e bubble3D depois dos dados numa série de bolha
• Os deslizes de nível de série usuais são referências de dados gravadas antes do dLbls, errBars à frente do trendline, e smooth à frente dos dados, e o Excel não reclama de nenhum deles

Posições manuais de rótulo vão num rótulo de ponto

O layout é onde a intuição e o schema se separam. Quando um usuário arrasta um rótulo no Excel, a posição manual é armazenada como <c:layout> dentro do <c:dLbl> daquele rótulo, logo depois do idx. O CT_DLbls não tem filho layout, então um gerador que grava um diretamente sob o <c:dLbls>, esperando que ele posicione o conjunto inteiro, produz um arquivo que o validador sinaliza como filho inválido. O Excel abre esse arquivo e faz algo que ninguém pediu: aplica o layout só ao primeiro rótulo, colocando-o onde um layout de ponto idx 0 o colocaria, e lê errado as larguras dos rótulos restantes, então uma largura de rótulo de 37.7 volta como 52.3. O gráfico quase parece certo, o que torna o erro fácil de perder numa revisão

Um layout copiado de dentro de uma extensão pode deixar a pasta de trabalho inteira ilegível. O Excel também pode guardar um c15:layout dentro da lista de extensão de um rótulo, e o prefixo c15 é declarado no <c:ext> que o envolve, não no fragmento. Código que encontra layouts por nome local pega esse elemento também, e colá-lo no <c:dLbls> leva o prefixo sem a declaração dele, então a chart part reconstruída contém um prefixo de namespace não declarado e nenhum parser XML consciente de namespace a carregará. Case layouts por namespace e por posição como filho direto, jamais por nome local sozinho

O HotXLS aceita o layout de um rótulo só de um filho <c:layout> direto do <c:dLbls> ou de um <c:dLbl>, nunca de dentro de uma extensão, e o mantém verbatim indexado pelo índice do ponto. O LabelLayoutXml guarda o layout do primeiro rótulo, incluindo um perdido encontrado diretamente sob o <c:dLbls>, já que é assim que o Excel o interpreta; PointLabelLayoutCount, PointLabelLayoutIndexes e PointLabelLayoutXmls listam o resto. Quando o gráfico é regenerado, esse layout vai no <c:dLbl> do idx 0 e cada outro layout vai no <c:dLbl> do índice próprio dele, cada um mesclado com o override de intervalo e o texto daquele ponto num único rótulo

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 planilha 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;

O Excel lê um rótulo de ponto como um rótulo inteiro, então gravá-lo como um, com a posição manual no mesmo <c:dLbl>, mantém tanto o texto S1; 2; 20 quanto o primeiro rótulo deslocado fora dos seus gráficos. No HotXLS, esse modelo fica por trás do DataLabelsRange, das chamadas de override de ponto e das propriedades de layout preservado