O método AddCopy do HotXLS copia uma folha de cálculo de uma pasta de trabalho do Excel para outra descompilando cada fórmula dessa folha em texto no estilo A1 e recompilando esse texto dentro da pasta de trabalho de destino, em vez de copiar diretamente a árvore de fórmula compilada, porque as referências de séries de gráficos, os índices de tipo de letra de texto formatado e a numeração de ligações externas são todos atribuídos de forma independente dentro de cada ficheiro de pasta de trabalho
A falha surge exatamente na pasta de trabalho que seria de esperar: uma tarefa de fim de mês que extrai uma folha do relatório de cada filial e a acrescenta a um ficheiro de resumo. Ao abrir o resultado, um gráfico de subtotais apresenta os números de uma filial completamente diferente, uma nota que era a negrito e a vermelho na origem volta a ser texto simples a preto, e uma fórmula que antes ia buscar uma taxa de imposto a uma pasta de trabalho de consulta associada mostra agora um número congelado que ninguém consegue explicar. Nada aqui gera uma exceção — o ficheiro abre, os números parecem plausíveis, e o dano fica lá até alguém reparar num gráfico com o título errado ao lado
Porque é que o AddCopy não copia simplesmente a árvore de fórmula compilada?
O AddCopy não pode mover a árvore de fórmula compilada sem alterações, porque uma fórmula BIFF compilada não é texto autónomo — é uma sequência de tokens, e vários desses tokens são inteiros pequenos que só se resolvem corretamente dentro da pasta de trabalho que os produziu. Uma referência 3D como Sheet2!A1:A10 não transporta o nome literal Sheet2 depois de compilada — transporta um campo que a especificação BIFF designa por ixti (o HotXLS mantém o mesmo valor na sua própria árvore compilada sob o nome de campo FExternID), um índice para a tabela privada EXTERNSHEET dessa pasta de trabalho, numerada consoante a ordem em que essa pasta de trabalho em particular registou as suas folhas e livros externos. Se esse token for movido sem alterações para uma pasta de trabalho cuja tabela EXTERNSHEET foi construída por outra ordem, o índice 3 deixa de significar Sheet2 — passa a significar seja qual for a folha que ocupa a posição 3 nessa outra pasta de trabalho, e o Excel não tem forma de sinalizar o erro, porque, no que toca ao formato do ficheiro, a fórmula está perfeitamente bem formada. É exatamente esta falha que o TXLSWorksheets.AddCopy existe para evitar: invocado a partir da coleção de folhas de qualquer uma das pastas de trabalho em código Delphi ou C++Builder, copia uma folha de cálculo — valores de células, formatos, fórmulas, gráficos, comentários, fusões, configuração de página, e mais — de uma pasta de trabalho de origem que pode ou não ser aquela sobre a qual está a chamar o método, e acrescenta o resultado ao destino sob um nome à escolha ou uma cópia com nome desambiguado do original
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;A correção: descompilar para texto, recompilar no destino
O HotXLS resolve o problema da indexação nunca deixando que a própria árvore compilada atravesse a fronteira entre pastas de trabalho. Para cada célula com fórmula numa cópia entre pastas de trabalho, o AddCopy descompila a fórmula de origem para o mesmo texto no estilo A1 que um utilizador veria na barra de fórmulas do Excel, e entrega depois esse texto à pasta de trabalho de destino, que o interpreta de novo numa árvore usando as suas próprias tabelas de raiz — uma referência qualificada por folha como Data!D2:D100 é, nesse momento, apenas uma cadeia de texto, e uma cadeia de texto significa o mesmo em qualquer pasta de trabalho, pelo que, se o destino já tiver uma folha chamada Data, a referência resolve-se corretamente sem qualquer tradução de índices, porque nunca existiu um índice em bruto a traduzir. O HotXLS só paga este esforço extra quando é mesmo necessário: copiar uma folha dentro da mesma pasta de trabalho segue um caminho mais barato em que a árvore compilada é simplesmente duplicada em memória, uma vez que todos os índices nela contidos já são válidos onde permanecem, e o desvio pelo texto só é executado quando o AddCopy deteta que a origem e o destino são, de facto, instâncias de pasta de trabalho distintas. Vale a pena precisar também o que esta reescrita não é. Não tem nada a ver com o deslocamento de linhas e colunas que ocorre ao inserir ou eliminar linhas dentro de uma única folha, tema que um artigo relacionado aborda em pormenor — esse motor reescreve o texto A1 no próprio local para acompanhar células que se moveram algumas linhas para cima ou para baixo dentro de uma única pasta de trabalho, ao passo que este é executado quando uma fórmula sai por completo da pasta de trabalho que a compilou, situação em que as linhas deslocadas deixam de ser o problema e a numeração privada da pasta de trabalho passa a sê-lo
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);E se o destino ainda não tiver essa folha, ou esse nome?
A recompilação do AddCopy só é bem-sucedida quando a pasta de trabalho de destino já tem tudo aquilo a que o texto da fórmula faz referência, e as duas lacunas que surgem na prática são uma folha com o mesmo nome que ainda não foi copiada neste lote, e um nome definido ao nível da pasta de trabalho que nunca existiu no destino. O HotXLS não gera uma exceção quando a recompilação falha a meio da cópia de uma folha — a atribuição de Value da célula guarda simplesmente o texto da fórmula como uma cadeia de texto simples, um modo de falha deliberado e inspecionável em vez de silencioso, já que uma célula com fórmula que mostra inesperadamente texto literal como =SUM(Q1!B2:B12) em vez de um número calculado é o sinal de que algo, mais atrás na cópia, não se resolveu. Antes de desistir, o AddCopy tenta uma reparação: percorre a árvore sintática da fórmula falhada recolhendo todos os identificadores de nomes definidos que a fórmula toca, e, para cada nome ao nível da pasta de trabalho que exista na origem mas ainda não no destino, copia o nome e recompila o mesmo texto uma segunda vez. Os nomes ao nível da folha ficam fora do alcance desta reparação, já que um nome visível apenas às fórmulas de uma folha da pasta de trabalho de origem não tem uma posição equivalente para onde migrar, e um destino que já possua um nome com a mesma grafia é deixado intacto em vez de substituído, partindo do princípio de que um nome deliberadamente pré-criado pelo chamador é o que este pretende que seja respeitado. Dentro de uma única pasta de trabalho, a pesquisa de nomes de uma fórmula entre folhas percorre automaticamente do âmbito da folha até ao âmbito da pasta de trabalho, mecanismo que o artigo do HotXLS sobre nomes definidos e fórmulas entre folhas aborda; atravessar uma verdadeira fronteira entre pastas de trabalho remove por completo essa rede de segurança, e um nome tem de ser deliberadamente transportado, ou a fórmula que dele depende degrada-se para texto
As referências de séries de gráficos precisam da mesma correção, mas por um caminho de código diferente
Uma série de gráfico do HotXLS que representa um intervalo de células depara-se exatamente com o mesmo problema de numeração que uma fórmula de célula comum, porque a referência de intervalo de dados de um gráfico também é um fluxo de tokens de fórmula compilada — a especificação BIFF designa o registo que a transporta por BRAI (secção 2.4.51 do [MS-XLS]) —, mas o AddCopy não pode corrigi-la reaproveitando o caminho normal de carregamento de gráficos, porque é precisamente esse caminho que cria o erro. Quando um registo de gráfico é interpretado a partir do disco no decurso normal da abertura de um ficheiro, a sua árvore de fórmula é construída traduzindo os bytes em bruto através da instância de calculadora que está a fazer a interpretação; se, em vez disso, os bytes BRAI em bruto de um gráfico de origem forem passados pelo carregador de registos comum da pasta de trabalho de destino, o ixti incorporado nesses bytes é resolvido em função da tabela EXTERNSHEET do destino, pelo que a série passa a apontar silenciosamente para o que quer que ocupe essa posição nessa outra pasta de trabalho — a mesma classe de erro que copiar sem alterações a árvore compilada de uma célula, só que mais difícil de notar, porque ninguém lê as fórmulas de séries de gráficos da forma como lê as fórmulas de células. O HotXLS evita a armadilha com um caminho de clonagem dedicado: o TXLSCustomChart.AssignFrom copia literalmente os bytes de cabeçalho não relacionados com fórmulas de cada registo de gráfico, e depois reconstrói o intervalo associado através do mesmo mecanismo de descompilação e recompilação usado para as células comuns, pelo que a nova árvore é construída de raiz em função da tabela EXTERNSHEET do destino, em vez de ser reinterpretada em função dela a posteriori
O mesmo problema de numeração, agora com o índice de tipo de letra
Nem todos os números privados de uma pasta de trabalho, dentro de um gráfico ou de uma célula com texto formatado, são fórmulas, e um índice de tipo de letra é a mesma classe de problema em miniatura. Os trechos de texto formatado, juntamente com mais dois tipos de registo de gráfico que transportam um tipo de letra de legenda ou de eixo, guardam a referência de tipo de letra como um índice inteiro em bruto para a tabela de tipos de letra da própria pasta de trabalho, e esse índice não significa nada na tabela de outra pasta de trabalho — poderia perfeitamente apontar para um tipo de letra, tamanho ou cor completamente diferentes nesse outro contexto. O HotXLS resolve isto por valor e não por número: procura os atributos reais do tipo de letra nesse índice na tabela de origem, encontra ou cria uma entrada correspondente na tabela de tipos de letra do destino, e reescreve o índice guardado para apontar para essa nova posição. Uma particularidade do formato torna a própria pesquisa complicada — o índice numerado no ficheiro salta a posição 4, uma lacuna de numeração documentada na secção 2.5.339 do [MS-XLS], pelo que o código tem de deslocar o índice uma unidade para baixo antes de comparar os tipos de letra e uma unidade para cima antes de escrever o resultado
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);O que acontece a uma fórmula que já aponta para fora da pasta de trabalho?
Uma fórmula que alcança uma terceira pasta de trabalho antes de sequer se chamar o AddCopy é o único caso que o percurso por texto não consegue resolver, porque o descompilador de fórmula para texto do próprio HotXLS não sintetiza deliberadamente o texto entre parênteses retos [Book]Sheet! para uma referência externa, e o compilador do outro lado também não aceita essa sintaxe como entrada — pelo que este único caso passa por um segundo mecanismo que nunca chega a tocar em texto. Quando a reparação por migração de nomes descrita acima ainda deixa uma célula como cadeia de texto, e a pasta de trabalho de origem tem um nome de ficheiro real, o AddCopy muda de estratégia: copia em profundidade a própria árvore de fórmula compilada em vez do seu texto, e entrega depois a cópia a uma passagem de reassociação dedicada, a RebindExternRefsInTree, que a percorre nó a nó. Para cada referência de intervalo que encontra, essa passagem resolve a entrada EXTERNSHEET da origem, reconvertendo-a num par de nomes de folha, e regista, ou reutiliza, uma entrada equivalente nas próprias tabelas de referência externa do destino, criando uma ligação a uma nova pasta de trabalho externa se o destino nunca tiver referenciado antes esse ficheiro de origem
É aqui que o problema da numeração privada da pasta de trabalho se manifesta de forma mais literal, porque um token de referência externa agrupa três coordenadas distintas num único campo, e cada uma delas é privada da pasta de trabalho que a escreveu: qual a pasta de trabalho externa, uma posição na lista própria do destino de livros externos, atribuída pela ordem em que essa pasta de trabalho os foi registando; qual a folha dentro da lista de folhas dessa pasta de trabalho externa, guardada como um índice de base 1 restrito especificamente a esse livro externo, um domínio de numeração completamente distinto dos identificadores internos de folha do próprio destino; e o próprio intervalo de células, meras coordenadas de linha e coluna que não precisam de tradução, porque nunca foram relativas à pasta de trabalho em primeiro lugar. Se qualquer um dos dois primeiros elementos ficar errado, o Excel continua a abrir o ficheiro, continua a mostrar uma fórmula, e avalia-a em função das células externas erradas sem qualquer aviso. Há um tipo de nó que derrota mesmo esta reassociação ao nível da árvore: uma referência a um nome definido, um índice para a tabela de nomes privada da própria pasta de trabalho exatamente da mesma forma que um índice de folha é privado da sua própria EXTERNSHEET, sem qualquer reparação equivalente disponível ao nível da árvore — no momento em que a passagem de reassociação encontra uma referência de nome em qualquer ponto da árvore, abandona a fórmula inteira em vez de escrever uma versão parcialmente correta. Mesmo quando a reassociação é bem-sucedida, a célula de destino não mostra um número recém-recalculado; mostra o valor que a célula de origem já tinha no momento da cópia, guardado numa posição em cache da mesma forma que o próprio Excel guarda em cache o último valor conhecido de qualquer referência externa até que as ligações sejam explicitamente atualizadas, o que é o comportamento correto por predefinição, já que recalcular através de uma ligação ativa a outro ficheiro é exatamente o tipo de operação que se quer despoletar uma vez, deliberadamente, e não em cada abertura
O que este desenho custa
O mecanismo de descompilação e recompilação do AddCopy não é gratuito, e o custo vale a pena ser planeado antes de escrever um script para uma grande tarefa de consolidação, não depois. Copiar uma folha dentro da mesma pasta de trabalho segue o caminho barato, uma simples duplicação em memória da árvore compilada, porque todos os índices nela contidos já são válidos na pasta de trabalho onde permanecem; uma cópia entre pastas de trabalho paga por uma verdadeira interpretação em cada célula com fórmula, descompilando para texto e depois compilando esse texto de novo a partir do zero, e, embora a diferença não valha a pena medir numa folha com algumas dezenas de fórmulas, uma pasta de trabalho de origem com dezenas de milhares de células com fórmulas, copiada como uma folha entre dezenas noutra tarefa em lote, deve esperar que a recompilação domine o tempo de execução, e não a E/S de ficheiros à sua volta. A ordem de cópia importa por uma segunda razão, além da velocidade: uma fórmula que referencia uma folha que o AddCopy ainda não alcançou neste lote falha a recompilação pela mesma razão que falharia se referenciasse uma folha genuinamente inexistente, pelo que uma tarefa que copie a folha B antes da fórmula da folha A que dela depende verá essa fórmula degradar-se exatamente da forma descrita acima, texto em cadeia ou um recurso de ligação externa a apontar de volta precisamente para o ficheiro de origem de onde acabou de vir. E como cada pasta de trabalho de origem, num lote de consolidação, é normalmente criada de forma independente, vale a pena testar explicitamente o único modo de falha que nenhum ficheiro de origem isolado alguma vez poderia ter avisado — cinco pastas de trabalho de filiais que totalizam cada uma os números de uma filial homóloga podem combinar-se numa verdadeira referência circular dentro da pasta de trabalho de resumo sem que nenhum ficheiro de origem, isoladamente, alguma vez tenha contido uma, um ciclo que só existe depois de todas as folhas terem chegado ao mesmo local e o recálculo correr sobre o conjunto combinado
A cópia de folhas de cálculo entre pastas de trabalho é entregue como comportamento padrão do AddCopy no Componente Excel para Delphi HotXLS para Delphi e C++Builder; a página do produto contém a referência completa da API de folhas de cálculo e pastas de trabalho, incluindo o comportamento de gráficos, texto formatado e referências externas aqui descrito