Duplicar uma folha XLSX no Delphi com HotXLS

Já construiu uma folha de cálculo exatamente como deve ser. A faixa do cabeçalho está unida, as larguras das colunas ajustam-se aos dados, as duas primeiras linhas estão congeladas, a área de impressão e as margens estão definidas para uma exportação A4 limpa, e o separador está colorido para as finanças a encontrarem. Agora o relatório precisa de doze destas, uma por região, todas a começar a partir do mesmo esquema. Reconstruir essa folha de cálculo em código doze vezes é o caminho para os desvios subtis se instalarem: a região 7 fica com uma coluna um ponto mais estreita, a região 11 perde o congelamento e ninguém dá por isso até o PDF chegar à secretária de um gestor. O que realmente se quer é a versão programática do clique-direito do Excel, Mover ou Copiar, Criar uma cópia: pegar na folha de cálculo acabada e carimbar duplicados independentes

O motor XLSX do HotXLS, uma biblioteca nativa para Delphi e C++Builder que lê e escreve ficheiros Excel sem automatizar o próprio Excel, já sabia mover folhas de cálculo, eliminar folhas de cálculo e copiar intervalos de células entre folhas de cálculo. O que não sabia fazer até à v2.91.0 era clonar uma folha de cálculo inteira numa só chamada. Essa versão acrescenta dois pontos de entrada: TXLSXWorksheet.CopyFrom, que copia o estado ao nível da folha de uma folha de cálculo para outra, e TXLSXSheets.Duplicate, que cria uma nova folha de cálculo e executa CopyFrom por si. A parte interessante não é o facto de copiar coisas. É a linha deliberada traçada entre o que é copiado em profundidade e o que não é, e a razão por que essa linha está exatamente onde está

Uma chamada para clonar uma folha de cálculo acabada

A operação de alto nível é Duplicate. Entregue-lhe o índice baseado em 1 da folha de cálculo de origem e ela devolve uma folha de cálculo totalmente nova que espelha o esquema e os dados do original. A convenção de índices corresponde a Items[] no lado XLSX, portanto a primeira folha de cálculo tem o índice 1, não 0; passe um índice fora do intervalo e recebe nil em vez de uma exceção, o mesmo contrato de falha que o resto da coleção de folhas de cálculo XLSX usa

var
  Book: TXLSXWorkbook;
  Template, Copy: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Template := Book.Sheets.Add('Template');
    Template.Cells[1, 1].Value := 'Quarterly Statement';
    Template.Range['A1:C1'].Merge;
    Template.ColWidth[1] := 18;
    Template.FreezePanes(2, 1);          // congelar a linha superior + a primeira coluna
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clonar com um nome explícito...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...ou deixar escolher o nome predefinido ao estilo do Excel.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

    Book.SaveAs('regions.xlsx');
  finally
    Book.Free;
  end;
end;

Duas coisas neste excerto merecem uma pausa. Primeiro, FreezePanes recebe os argumentos com a linha em primeiro lugar, FreezePanes(ARow, ACol), alinhando-se assim com a indexação Cells[Row, Col]; o duplicado herda exatamente a mesma divisão congelada. Segundo, o método chama-se Duplicate e não o mais óbvio Copy, e isso não é uma preferência de estilo. Copy é uma rotina da unit System, usada constantemente com strings e arrays dinâmicos. Um método chamado Copy numa classe faria-lhe sombra dentro dos corpos dos métodos e criaria exatamente o tipo de ambiguidade de resolução que cobra a conta seis meses depois. Duplicate contorna o problema inteiro e lê-se corretamente no local da chamada

O nome predefinido segue a própria regra do Excel

Quando chama a sobrecarga de um argumento, ou passa uma string de nome vazia, a nova folha de cálculo recebe o nome da origem com o sufixo (2), e o sufixo avança até o nome ficar único. Duplique a folha de cálculo Template uma vez e obtém Template (2); duplique-a outra vez e obtém Template (3), porque Template (2) já está ocupado. Isto espelha os nomes que o próprio Excel gera com o comando Criar uma cópia, pelo que um livro produzido pelo seu código fica com o aspeto que um utilizador esperaria de uma duplicação manual. A verificação de unicidade corre sobre a coleção de folhas de cálculo ativa, o que significa que também tem em conta os nomes criados manualmente, não apenas os vindos de duplicações anteriores

Se está a gerar uma folha de cálculo por região ou por mês, prefira a sobrecarga com nome explícito. Um esquema previsível como Region-North e Region-South é mais fácil de endereçar mais tarde do que uma sequência de sufixos (2), (3), e mantém os nomes definidos e as fórmulas entre folhas de cálculo legíveis

O que o CopyFrom copia em profundidade

Por baixo do capô, Duplicate acrescenta a folha de cálculo e depois chama CopyFrom(ASource), que também pode ser chamado diretamente quando se quer clonar para uma folha de cálculo já criada. CopyFrom protege desde logo os dois casos degenerados: copiar de nil ou copiar uma folha de cálculo sobre ela própria devolvem de imediato sem fazer nada. Tudo o que se segue é a própria cópia, e é deliberadamente abrangente

Os dados das células vêm primeiro. CopyFrom pede à origem o seu UsedRange, a caixa delimitadora exata das células preenchidas e das regiões unidas, e reutiliza a maquinaria existente de CopyRangeTo para transportar todos os valores, fórmulas e índices de estilo por célula para o destino, a começar em A1. Por cima das células, reproduz toda a camada de estado ao nível da folha que faz um modelo parecer acabado:

• Regiões unidas, recriadas por coordenada para que a faixa do cabeçalho ocupe o mesmo retângulo
• Larguras de colunas e alturas de linhas, mais as listas de ocultas, recolhidas e níveis de contorno, copiadas literalmente para que as linhas e colunas fora do predefinido alinhem com exatidão
• Congelamento de painéis e o estado da vista: nível de zoom, apresentação de linhas de grelha e de zeros, direção da direita para a esquerda e o tipo de vista
• Estado de proteção com as suas opções de permissão por ação, para que um modelo bloqueado permaneça bloqueado da mesma forma
• Todo o bloco de configuração de página: margens, orientação, tamanho do papel, dimensionamento e ajuste à página, área de impressão, títulos de impressão, cabeçalhos e rodapés, além dos sinalizadores de impressão de linhas de grelha e títulos
• O intervalo do AutoFiltro, a cor do separador e a visibilidade da folha de cálculo

O resultado é uma folha de cálculo que imprime, filtra e se apresenta de forma idêntica à origem. E, como as células, as uniões e as listas de dimensões são fisicamente recriadas na nova folha de cálculo em vez de permanecerem como alias, o duplicado é totalmente independente. Escreva 999 numa célula da cópia e a origem mantém o seu valor original; essa independência é a propriedade mais importante de um clone destinado a relatórios regionais em paralelo, e a demonstração SheetCopy incluída verifica-a explicitamente

O que fica superficial, e porquê

Agora a parte honesta. Gráficos, imagens incorporadas, tabelas XLSX, validações de dados e regras de formatação condicional não são copiados. Esta é uma fronteira documentada e deliberada, não um descuido, e vale a pena perceber o raciocínio para planear em torno dela em vez de ser surpreendido por ela

Cada uma dessas coleções transporta identidade e referências que não sobrevivem a uma cópia ingénua de campos. Um gráfico aponta para um intervalo de dados de origem e é dono de uma relação de desenho no pacote OOXML; clonar o objeto sem remapear a relação e as referências das séries produz um gráfico desenhado contra os dados errados, ou um pacote que o Excel assinala como precisando de reparação. Uma tabela tem um nome que tem de ser único dentro do livro, uma linha de cabeçalho ligada a colunas específicas e a sua própria relação gerada automaticamente. Formatações condicionais e validações de dados ligam-se a intervalos de coordenadas e, no caso da validação, podem referenciar outros intervalos por fórmula. Copiar em profundidade qualquer uma delas corretamente significa reescrever referências e cunhar identidades novas, o que é trabalho real com modos reais de falha. Fazê-lo a meio, copiando o objeto mas não as suas referências, é pior do que não copiar nada: dá origem a um ficheiro que abre com um pedido de reparação e descarta conteúdo em silêncio. Por isso, o motor copia o que consegue copiar de forma limpa e deixa as coleções portadoras de referências para a aplicação chamadora, que sabe para onde o destino deve apontar

Na prática, isto significa que o fluxo de trabalho para um modelo mais rico é: duplicar a folha de cálculo para obter células, esquema e configuração de impressão, e depois recriar o gráfico, a tabela, as validações ou as formatações condicionais na cópia com a mesma API usada para os criar da primeira vez. Como os está a recriar sobre os intervalos da própria cópia, as referências saem corretas por construção. Para um gráfico que lê A1:C10, acrescente um novo gráfico na cópia apontado para o A1:C10 da cópia; para um AutoFiltro que queira manter ativo, note que o intervalo do filtro é preservado, por isso só reaplica os critérios das colunas. As regras de formatação condicional e de validação de dados que reaplicaria através das mesmas chamadas descritas no artigo sobre células unidas e esquema de modelos de relatório, que percorre a tabela de uniões e o modelo de intervalos que a cópia herda

Onde a duplicação se encaixa num pipeline de relatórios

A duplicação de folhas de cálculo é a companheira natural da geração guiada por marcadores. A abordagem ancorada em tokens do guia de geração de relatórios a partir de modelos no Delphi resolve o problema de escrever dados num esquema que outras pessoas editam; a duplicação resolve o problema de precisar desse esquema muitas vezes num único livro. Combine as duas e o padrão fica limpo: mantenha uma folha de cálculo Template imaculada, com os seus tokens, uniões e configuração de impressão, e depois, para cada região ou período, chame Duplicate, preencha os tokens da cópia com essa fatia de dados e avance. O modelo original nunca é modificado, por isso continua a ser uma fonte fiável para o clone seguinte, e cada folha de cálculo de saída parte de um esquema idêntico byte a byte

Uma nota de sequenciamento evita uma classe inteira de confusão. Duplique a folha de cálculo antes de deitar dados para dentro dela, não depois. Um modelo deve guardar estrutura e formatação, não os números do trimestre passado, e clonar uma folha de cálculo vazia e estilizada significa que cada duplicado começa limpo. Se duplicar uma folha de cálculo que já transporta dados, esses dados vêm junto, porque CopyFrom copia fielmente o intervalo usado; às vezes é isso que se quer, mas, para um relatório em fan-out, normalmente não é

Um hábito rápido de verificação

Como a divisão entre cópia profunda e cópia superficial é invisível até alguém procurar por ela, incorpore no processo uma verificação de cinco linhas em vez de confiar que tudo passou. Depois de duplicar, leia de volta os sinais estruturais que o duplicado deveria herdar e confirme que correspondem à origem

Copy := Book.Sheets.Duplicate(1, 'Region-North');
WriteLn(Format('merged=%d  colA=%.1f  freezeRow=%d  tabAuto=%d',
  [Copy.MergedCells.Count, Copy.ColWidth[1],
   Copy.FreezeRow, Integer(Copy.TabColorIsAuto)]));
// Provar a independência: altere a cópia, confirme que a origem fica intacta.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value continua a ser o que era.

A contagem de uniões, uma largura de coluna, a linha de congelamento e o sinalizador da cor do separador dizem-lhe que a camada que é copiada realmente chegou lá. À parte, em qualquer folha de cálculo que transportasse um gráfico, uma tabela, validações ou formatações condicionais, trate esses elementos como uma lista a reconstruir na cópia: a ausência é propositada, e a correção são umas quantas chamadas, não um relatório de erro. Esse modelo mental, profundo onde é seguro e superficial onde as referências se partiriam, é toda a história de como usar bem esta funcionalidade

A duplicação de folhas de cálculo e a cópia de estado de folha de cálculo via CopyFrom aqui descritas fazem parte da v2.91.0 do componente nativo HotXLS de folhas de cálculo para Delphi, junto com um exemplo executável SheetCopy que exercita o ciclo de clonar e alterar de ponta a ponta