Embutindo objetos de gráfico em planilhas com o HotXLS

O HotXLS pode colocar um gráfico diretamente numa planilha, ancorado a um intervalo de células, em vez de pô-lo numa aba de gráfico dedicada. Em termos de BIFF8, isso significa escrever uma drawing shape com um record OBJ do tipo 5 e estacionar o substream do gráfico no fim do stream de records da planilha, que é exatamente o layout que o Excel produz e exatamente onde o leitor espera encontrá-lo

A distinção importa para quem gera relatórios operacionais. Uma aba de gráfico é uma boa casa para um visual de manchete único. Um detalhamento regional mensal quer o gráfico ao lado dos números que ele resume, na mesma planilha, dimensionado para o bloco de células a que pertence, para o leitor rolar uma vez em vez de trocar de aba e perder o contexto

A leitura já existia, a escrita não

A assimetria vale ser nomeada porque molda o trabalho. O HotXLS já sabia ler gráficos embutidos: quando o stream de records da planilha contém um BOF marcado como substream de gráfico, o parser troca de contexto, coleta os records do gráfico e, no EOF de fechamento, os devolve à drawing shape que o record OBJ introduziu. Esse caminho tinha sido exercitado por toda workbook criada no Excel que a biblioteca já abriu

O que faltava era o lado da autoria, e a consequência útil é que o novo writer tinha uma especificação precisa a acertar: produzir o layout de bytes que o leitor existente já religa. Não há critério de aceitação melhor para uma funcionalidade de formato binário do que um leitor escrito de forma independente que você não pôde mudar

Do que um gráfico embutido é feito

Três peças precisam estar de acordo. A camada de desenho contribui com uma shape de host-control, a camada de objetos contribui com um record OBJ cujo common object data declara o tipo de objeto 5, e o stream de records contribui com o próprio substream do gráfico. Os flags de opção no record OBJ são os que o Excel escreve para um chart frame: posicionado, travado, linha automática e preenchimento automático, que é o que faz o gráfico embutido se comportar como um nativo quando um usuário clica nele

A âncora merece uma nota porque é uma fonte comum de bugs de off-by-one. A API do HotXLS recebe números de linha e coluna baseados em um, como o resto da biblioteca, e o client anchor escrito no arquivo é baseado em zero. A conversão acontece dentro de AddChartObject, então os callers permanecem no sistema de coordenadas que usam em todo o resto, mas quem comparar um hex dump com a própria chamada precisa lembrar de que lado dessa fronteira está lendo

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Series: array[0..1] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('regional-sales.xls');
    Sheet := Book.Sheets[0];

    FillChar(Series, SizeOf(Series), 0);
    Series[0].Name := 'Actual';
    Series[0].Categories := 'Data!$A$2:$A$13';
    Series[0].Values := 'Data!$B$2:$B$13';
    Series[0].DataLabels.ShowValue := True;
    Series[0].HasDataLabels := True;

    Series[1].Name := 'Target';
    Series[1].Categories := 'Data!$A$2:$A$13';
    Series[1].Values := 'Data!$C$2:$C$13';
    Series[1].SecondaryAxis := True;

    // Ancorado em E2:M20 nesta planilha, base um
    Sheet.AddChartObject(xlsChartTypeColumn, 'Regional sales',
      'Month', 'Amount', Series, 2, 5, 20, 13);

    Book.SaveToFile('regional-sales-charted.xls');
  finally
    Book.Free;
  end;
end;

O FillChar no array de séries não é enfeite. O TXLSChartSeriesInfo carrega vários sub-records opcionais, data labels, estilo por série, trendlines e error bars, cada um gateado por um boolean, e um record parcialmente inicializado na stack vai entregar ao emitter flags que ninguém definiu. Zere o array, e então defina os campos que você quer

Quais referências de série o caminho embutido aceita?

Intervalos estilo A1 simples dentro da mesma workbook, e essa restrição é deliberada, não um descuido. Toda referência é resolvida contra a lista de planilhas da workbook e virada no índice de referência externa de que os records do gráfico precisam. Um intervalo nomeado ou uma referência a workbook externa cai num placeholder com expressão parseada de comprimento zero, então o gráfico é escrito de forma limpa, mas essa série em particular fica sem fonte de dados até você apontá-la para um intervalo

A razão é uma troca de engenharia direta. O caminho completo de compilação de referências existe na rota da aba de gráfico, embrulhado na camada de coleção de planilhas, e extraí-lo limpo significaria duplicar cem linhas de lógica de resolução para um caso incomum na prática. Um gráfico embutido quase sempre plota células na própria planilha dele ou numa planilha de dados irmã. Referências nomeadas e externas são cobertas no caminho da aba de gráfico via AddChartSheet, então nada está indisponível, só alcançado por um entry point diferente

Todo o resto do modelo de séries funciona identicamente nas duas rotas. Vinculação de eixo secundário, estilos de linha, preenchimento e marcador por série, trendlines, error bars e data labels fazem parte do TXLSChartSeriesInfo e todos são emitidos da mesma forma, então uma definição de gráfico pode migrar entre um objeto embutido e uma aba de gráfico mudando só a chamada. A mecânica de axis-group por trás do flag de eixo secundário está coberta em grupos de eixo secundário na escrita BIFF

Por que o título do gráfico leu como dois caracteres?

Porque uma contagem de caracteres foi passada onde se esperava uma contagem de bytes, e strings Unicode do BIFF tornam esse erro fácil de escrever e difícil de ver. Uma string Unicode curta do BIFF começa com uma contagem de caracteres e um byte de flags, e o byte de flags carrega o bit de high-byte que diz se o payload é de um ou dois bytes por caractere. Leia um payload de 16 bits usando a contagem de caracteres como se fosse um comprimento em bytes e você recebe exatamente metade da string: uma série chamada Sales volta como Sa, e um título de gráfico trunca do mesmo jeito porque títulos e rótulos de série dividem o caminho de decodificação

O que torna esse defeito notável é que ele recorreu três vezes na mesma família de records, uma vez em nomes de trendline, uma vez em nomes de pivot chart, e uma vez em títulos de gráfico. Cada ocorrência parecia um bug novo numa funcionalidade nova. As três eram a mesma multiplicação faltando. A regra que finalmente fechou o caso é mecânica e deve ser aplicada sem julgamento: sempre que ler uma dessas strings, consulte primeiro o flag de high-byte e multiplique a contagem de caracteres pela largura do payload antes de tocar no buffer. Os detalhes no nível de record estão em decodificação de contagens de caracteres do XLUnicodeString e do flag de high-byte

// O gráfico embutido divide a camada de desenho com imagens e shapes,
// então um desenho existente na planilha é preservado. AddChartObject
// devolve o índice do objeto criado
var
  ObjIndex: Integer;
begin
  ObjIndex := Sheet.AddChartObject(xlsChartTypeLine, 'Trend',
    'Week', 'Units', Series, 2, 8, 18, 16);
  if ObjIndex < 0 then
    raise Exception.Create('chart object was not created');
end;

Onde os gráficos embutidos se encaixam frente às alternativas

Existem três rotas e elas respondem a perguntas diferentes. Um objeto de gráfico embutido pertence ao lado dos seus dados numa planilha e é o que a maioria dos relatórios quer. Uma aba de gráfico serve para um visual único de apresentação e te dá o caminho completo de compilação de referências. Preservar um gráfico existente de um arquivo carregado, intocado, é a resposta certa quando a workbook veio do Excel com formatação que ninguém quer que a biblioteca reinterprete; esse comportamento de pass-through está descrito em ChartML preservado e gráficos de combinação

Como o gráfico embutido anda sobre a camada de desenho, ele coexiste com imagens e shapes na mesma planilha em vez de substituí-las, e o modelo geral dessa camada está coberto em gráficos, imagens e desenhos no HotXLS. As três rotas vêm no componente de planilha HotXLS para Delphi, então a escolha é sobre como o relatório deve ficar, e não sobre o que a biblioteca consegue expressar

O ponto metodológico é o que vale guardar. Quando uma funcionalidade de formato binário tem um leitor existente, construa o writer contra o leitor, e não contra a sua leitura da especificação. O leitor codifica anos de contato com arquivos que aplicações reais de fato produziram, incluindo as partes que a especificação enuncia de forma vaga, e um writer que o satisfaz tem muito mais chance de satisfazer o Excel também