Qualquer elemento que flutue sobre a grelha de uma folha de cálculo (um gráfico, um logótipo, um carimbo, uma caixa de chamada) é um objeto de desenho, e um objeto de desenho é definido por duas coisas: o que é e onde está ancorado. A ancoragem é a parte em que as pessoas costumam errar. Um gráfico não reside numa célula; situa-se num retângulo fixado a um intervalo de linhas e colunas, e os dados que representa graficamente constituem um conjunto independente de referências A1 sobre o qual a âncora nada sabe. Se mover o enquadramento (frame), o traçado permanece no mesmo sítio. Se inserir linhas abaixo dele, o enquadramento desloca-se para baixo com elas. Manter estes dois sistemas de coordenadas alinhados é essencial para o correto comportamento do código de desenho
O HotXLS é uma biblioteca nativa em Object Pascal que lê e escreve ficheiros XLS e XLSX sem necessidade de automatização do Excel. Esta biblioteca possui dois modelos de desenho distinctos porque os dois formatos de ficheiro guardam os desenhos de forma diferente. O formato BIFF8 .xls mantém os gráficos nas suas próprias folhas dedicadas e as formas flutuantes num fluxo OfficeArt anexado à folha de cálculo. O formato OOXML .xlsx pode incorporar um gráfico diretamente na grelha, ancorado ao retângulo de uma célula, a par do mesmo tipo de imagens e formas flutuantes. O modelo de objetos reflete essa divisão, e as falhas dignas de registo resultam quase sempre da aplicação das regras de um formato ao outro
Que contentor pode conter o quê
A escolha do contentor deve preceder qualquer código de gráfico, pois os tipos de objetos disponíveis diferem entre os dois formatos:
- XLS (BIFF8): os gráficos residem em folhas de gráfico dedicadas criadas através de
AddChartSheetna coleçãoSheets. As imagens, caixas de texto, retângulos, ovais e linhas são formas OfficeArt geridas através da coleçãoShapesda folha de cálculo. Não existe uma API para incorporar um gráfico dentro da grelha normal de uma folha de cálculo - XLSX (OOXML): os gráficos podem ser incorporados diretamente numa folha de cálculo com
TXLSXWorksheet.AddChart, ancorados a um retângulo de célula, ou colocados numa folha de gráfico dedicada comTXLSXWorkbook.AddChartSheet. As imagens são inseridas comAddImageouAddImageFromFile, e os rótulos flutuantes comAddTextBox
Deste modo, um requisito formulado como "uma folha de painel (dashboard) com o gráfico ao lado dos números" é, na verdade, um requisito para .xlsx. Só consegue obter algo aproximado em .xls movendo o gráfico para a sua própria folha, o que altera a forma como o utilizador navega no ficheiro e como o seu código deve comportar-se. A folha devolvida por AddChartSheet do lado XLS é um fluxo secundário (substream) de gráfico e não uma grelha: tentar escrever nela com Cells.Item produz um fluxo de desenho inconsistente que é gerado sem erros, mas que o Excel descarta ao abrir o ficheiro. O gráfico simplesmente desaparece e nada no registo de compilação explica o motivo. Ao tratar a folha devolvida como exclusiva para gráficos, esta classe de relatórios de "gráficos em falta" é totalmente eliminada
Incorporar um gráfico numa folha de cálculo XLSX
O percurso do XLSX é o que oferece maior margem de manobra, e é onde os dois sistemas de coordenadas mencionados inicialmente se tornam concretos. O retângulo de ancoragem passado para AddChart é expresso em linhas e colunas da folha de cálculo e determina a posição do enquadramento do gráfico. Os dados das séries são expressos como referências A1 absolutas que incluem o nome da folha. Estes são independentes: pode mover o enquadramento do gráfico para a extremidade oposta da folha e este continuará a representar as mesmas células
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Chart: TXLSXChart;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sales');
Sheet.Cells[1, 1].Value := 'Region';
Sheet.Cells[1, 2].Value := 'Revenue';
Sheet.Cells[2, 1].Value := 'East';
Sheet.Cells[2, 2].Value := 1184350;
Sheet.Cells[3, 1].Value := 'Central';
Sheet.Cells[3, 2].Value := 902210;
Sheet.Cells[4, 1].Value := 'West';
Sheet.Cells[4, 2].Value := 1010675;
// Frame anchored to rows 6..22, columns 1..8
Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
Chart.ValueAxisTitle := 'USD';
Sheet.AddImageFromFile(1, 5, 'logo.png');
Book.SaveAs('dashboard.xlsx');
finally
Book.Free;
end;
end;O argumento mais propício a erros é a string de intervalo passada para AddSeries. Trata-se de um literal, capturado no momento da chamada, que não prevê que possa adicionar mais vinte linhas de dados posteriormente. Construa-o a partir de uma contagem de linhas calculada após a escrita dos dados, nunca antes. Os gráficos de dispersão (scatter) e de bolhas (bubble) sobrecarregam os mesmos dois argumentos com significados diferentes: o intervalo de categorias fornece agora os valores X e o intervalo de valores fornece Y, e o raio da bolha provém de um terceiro conjunto de referências definido através de BubbleSizeRange no TXLSXChartSeries devolvido. Interprete a chamada como "X, Y, tamanho" em vez de "categorias, valores" assim que sair da família dos gráficos de colunas e barras
O TXLSXChartType abrange gráficos de colunas, barras, linhas, setores (pizza), áreas, rosca, dispersão, bolhas e radar, o que cobre o reportório habitual de relatórios. Para um gráfico de página inteira sem grelha em redor, o Book.AddChartSheet devolve uma folha cuja propriedade IsChartSheet é verdadeira. Trata-se do homólogo em .xlsx da folha de gráfico antiga e acarreta a mesma expectativa: não escreva conteúdo de células nela
As imagens entram como bytes e são dimensionadas em EMUs
Existem duas sobrecargas para inserir uma imagem, e confundi-las é o erro de imagem mais frequente em revisões de código. A função AddImage(ARow, ACol, AData, AFormat) requer os bytes da imagem já codificados em AData: o conteúdo bruto de um ficheiro PNG, JPEG, GIF ou BMP. Se lhe passar o caminho de um ficheiro, guardará apenas uma string de quarenta bytes que nenhum visualizador consegue descodificar, resultando na apresentação do ícone de imagem quebrada após a implementação. Quando a origem for um ficheiro no disco, chame AddImageFromFile e deixe que a biblioteca leia os bytes e identifique o formato por si
Segue-se o dimensionamento. O DrawingML não mede em píxeis; mede em English Metric Units (EMUs), onde 914400 EMUs correspondem a uma polegada e, a 96 DPI, 9525 EMUs equivalem a um píxel. O objeto TXLSXImage expõe as propriedades WidthEMU e HeightEMU, pelo que um logótipo destinado a ser renderizado com 180 por 60 píxeis necessita de 1714500 por 571500 EMUs. Coloque essa conversão numa constante nomeada e faça os cálculos com base nela. Números mágicos como 1714500 espalhados pelo código são ilegíveis e deixam de estar corretos assim que alguém alterar a resolução DPI de destino. O índice da linha e da coluna de ancoragem baseia-se em 1, alinhando-se com a restante API de células em vez da matemática de EMUs baseada em 0
Folhas de gráfico e formas em ficheiros XLS antigos
Do lado do BIFF8, a sobrecarga mais completa de AddChartSheet aceita o tipo de gráfico, os títulos dos eixos e uma estrutura de matriz aberta de registos TXLSChartSeriesInfo, onde cada registo contém um nome e intervalos de categorias e valores como strings. As formas flutuantes são tratadas de modo diferente: são inseridas na própria folha de cálculo de dados, através da sua coleção Shapes, e não na folha de gráfico
var
Book: IXLSWorkbook;
Data, Trend: IXLSWorksheet;
Series: array[0..0] of TXLSChartSeriesInfo;
begin
Book := TXLSWorkbook.Create; // interface-counted: do not Free
Data := Book.Sheets.Add;
Data.Name := 'Data';
Data.Cells.Item[1, 1].Value := 'Month';
Data.Cells.Item[1, 2].Value := 'Units';
Data.Cells.Item[2, 1].Value := 'Apr';
Data.Cells.Item[2, 2].Value := 1530;
Data.Cells.Item[3, 1].Value := 'May';
Data.Cells.Item[3, 2].Value := 1721;
Series[0].Name := 'Units';
Series[0].Categories := 'Data!$A$2:$A$3';
Series[0].Values := 'Data!$B$2:$B$3';
Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
'Units sold', 'Month', 'Units', Series);
// Trend is a chart substream: never call cell methods on it
Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
Data.Shapes.AddPicture('approved-stamp.bmp');
Book.SaveAs('trend.xls');
end;Aqui aplicam-se dois detalhes sobre o tempo de vida dos objetos, os quais apontam em direções opostas. O TXLSWorkbook é gerido através da interface IXLSWorkbook e recorre a contagem de referências, pelo que chamar Free manualmente desencadeia uma dupla libertação. O TXLSXWorkbook mencionado nas secções anteriores é um objeto simples e tem de ser libertado num bloco try..finally. O mesmo revisor de código que assinala a falta de Free do lado XLSX terá de assinalar a presença indevida deste do lado XLS, constituindo um risco real quando se trabalha com ambos os formatos na mesma unidade. Os assistentes de formas são uniformes: AddRectangle, AddOval e AddLine, com DeleteInRange para limpar uma região de desenhos, sendo todos ancorados por pares de linhas e colunas, pelo que um modelo que insira linhas acima deles os deslocará juntamente com a grelha
Mais uma propriedade revela-se útil em ficheiros legados. A propriedade TXLSPicture.TransparentColor mascara uma cor de fundo escolhida de um bitmap, permitindo colocar um selo não retangular (um selo de "Aprovado" ou uma marca de água) sobre a grelha num formato cuja renderização BIFF não suporta a transparência alfa de ficheiros PNG. Defina a cor original de fundo do selo e o retângulo delimitador desaparecerá
As cores do tema não sobrevivem a uma conversão BIFF8
Os preenchimentos de desenho do OOXML podem apontar para um slot de cor do tema, razão pela qual redefinir as cores de um ficheiro .xlsx completo ao trocar o seu tema tem custos reduzidos. Os registos de desenho BIFF8 não possuem este tipo de slot. Quando o HotXLS aplica uma cor de tema a um desenho XLS, resolve a cor para um valor RGB literal e armazena-o; o índice do tema de onde provinha é perdido no instante em que o ficheiro é gravado, e reabrir o documento não o consegue recuperar. Isto afeta especialmente as ferramentas de relatórios personalizáveis, que mudam a imagem corporativa do mesmo documento gerado para múltiplos clientes. Mantenha o mapeamento de tema para RGB na sua própria configuração e reaplique-o sempre que gerar o ficheiro, em vez de esperar conseguir lê-lo a partir de um ficheiro .xls guardado
Uma decisão associada surge do lado do desempenho. Pode indicar-se à fachada XLS que ignore totalmente a análise da camada de desenho quando apenas se pretende aceder aos dados das células de um ficheiro legado de grandes dimensões, definindo a propriedade _DisableGraphics como verdadeira, o que poupa tempo nas leituras em lote. A desvantagem é definitiva: um livro aberto desta forma não mantém o fluxo OfficeArt em memória, pelo que gravá-lo fará desaparecer permanentemente os desenhos. Reserve esta flag para tarefas analíticas de apenas leitura. O cenário geral de desempenho está detalhado nas nossas notas sobre o desempenho de livros de grandes dimensões no HotXLS
Manter as âncoras estáveis enquanto a grelha é alterada
Os relatórios raramente mantêm o tamanho com que foram gerados, e é aqui que o modelo de ancoragem inicial se revela valioso. As operações estruturais da fachada XLSX (InsertRows, DeleteRows e os equivalentes para colunas) deslocam as camadas dependentes juntamente com as células. As regiões unidas, hiperligações, comentários, painéis congelados, intervalos de filtragem, formatação condicional, validações, tabelas, nomes definidos e, no caso deste tópico, as âncoras de imagens e gráficos deslocam-se em conjunto. Um logótipo ancorado na linha 1 permanece no topo quando dez linhas são inseridas abaixo dele. O enquadramento de um gráfico ancorado abaixo do bloco de dados desloca-se para baixo à medida que o bloco cresce. O único elemento que não é reescrito é qualquer string de intervalo capturada como um literal antes de a inserção ocorrer, já que se trata de mero texto que a biblioteca não tem motivos para rever. Isto define a ordem segura para o preenchimento de modelos: primeiro escreva e ajuste a estrutura dos dados, e crie os gráficos e insira as imagens como etapa final, com cada string de intervalo calculada a partir do número de linhas disponível após as inserções, e nunca antes
Duas ferramentas adicionais completam as opções de posicionamento. Do lado XLS, a função TXLSTextBox.SetArea reancora uma caixa de texto ou forma automática existente num novo retângulo de célula, o que é preferível a eliminar e recriar o objeto quando o bloco do rodapé é deslocado. E a sobrecarga de bitmap da função AddPicture aceita um objeto TBitmap ativo com uma flag de transparência opcional, permitindo que qualquer elemento desenhado pelo seu próprio código VCL (um manómetro, uma linha de sparkline ou um tipo de gráfico não suportado nativamente) seja inserido diretamente na folha sem necessidade de gravar previamente um ficheiro temporário
Os gráficos e as imagens constituem quase sempre a camada de finalização de um relatório já estruturado, pelo que o trabalho de base determina se os mesmos são posicionados de forma limpa. O preenchimento dos dados a que o gráfico fará referência é abordado na geração de relatórios orientada a modelos, e a estabilidade da grelha sob as suas âncoras é o tema do artigo células unidas e controlo de layout. A documentação completa das classes e métodos está disponível na página do produto HotXLS Component