O método AddCopy do HotXLS copia uma planilha de uma pasta de trabalho Excel para outra descompilando cada fórmula dessa planilha em texto no estilo A1 e recompilando o texto dentro da pasta de trabalho de destino, em vez de copiar a árvore de fórmula compilada diretamente, porque referências de série de gráfico, índices de fonte de rich text e numeração de link externo são todos atribuídos independentemente dentro de cada arquivo de pasta de trabalho
A falha aparece exatamente na pasta de trabalho que você esperaria: um job de fim de mês que puxa uma planilha do relatório de cada filial e a anexa a um arquivo de resumo. Abra o resultado e um gráfico de subtotal plota os números de uma filial completamente diferente, uma nota que era negrito e vermelha na origem volta a ser texto simples preto, e uma fórmula que antes puxava uma alíquota de imposto de uma pasta de trabalho de consulta complementar agora mostra um número congelado que ninguém consegue explicar. Nada levanta uma exceção aqui — o arquivo abre, os números parecem plausíveis, e o dano fica ali até alguém notar um gráfico com o título errado sentado ao lado
Por que o AddCopy não pode simplesmente copiar a árvore de fórmula compilada?
O AddCopy não pode mover a árvore de fórmula compilada inalterada, 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 carrega o nome literal Sheet2 uma vez compilada; carrega um campo que a especificação BIFF chama ixti (o HotXLS mantém o mesmo valor em sua própria árvore compilada sob o nome de campo FExternID), um índice na tabela EXTERNSHEET privada daquela pasta de trabalho, numerado da forma como aquela pasta de trabalho específica por acaso registrou suas planilhas e livros externos. Mova o token inalterado para uma pasta de trabalho cuja tabela EXTERNSHEET foi construída em uma ordem diferente, e o índice 3 não significa mais Sheet2 — significa o que quer que ocupe o slot 3 por lá, e o Excel não tem como sinalizar o erro, porque, no que diz respeito ao formato de arquivo, a fórmula está perfeitamente bem formada. Essa é exatamente a falha que TXLSWorksheets.AddCopy existe para evitar: chamado a partir da coleção de planilhas de qualquer uma das pastas de trabalho, em código Delphi ou C++Builder, ele copia uma planilha — valores de célula, formatos, fórmulas, gráficos, comentários, mesclagens, configuração de página e mais — de uma pasta de trabalho de origem que pode ou não ser aquela em que você está chamando o método, e anexa o resultado ao destino sob um nome de sua escolha ou uma cópia desambiguada 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 de indexação nunca deixando a própria árvore compilada cruzar a fronteira da pasta de trabalho. Para cada célula de fórmula em uma cópia entre pastas de trabalho, AddCopy descompila a fórmula de origem no mesmo texto estilo A1 que um usuário veria na barra de fórmulas do Excel, depois entrega esse texto à pasta de trabalho de destino, que o analisa de volta em uma árvore usando suas próprias tabelas do zero — uma referência qualificada por planilha como Data!D2:D100 é apenas uma string naquele ponto, e uma string significa a mesma coisa em qualquer pasta de trabalho, de modo que, se o destino já tiver uma planilha chamada Data, a referência se resolve corretamente sem nenhuma tradução de índice, porque nunca houve um índice bruto em trânsito para traduzir. O HotXLS só paga por essa ida e volta quando precisa: copiar uma planilha dentro da mesma pasta de trabalho toma um caminho mais barato onde a árvore compilada é simplesmente duplicada em memória, já que cada índice dentro dela já é válido onde está permanecendo, e o desvio por texto só roda uma vez que AddCopy detecta que origem e destino são genuinamente instâncias de pasta de trabalho diferentes. Vale ser preciso também sobre o que essa reescrita não é. Não tem nada a ver com o deslocamento de linha e coluna que roda quando você insere ou exclui linhas dentro de uma única planilha, que um artigo complementar cobre em detalhe — esse motor reescreve texto A1 no lugar para rastrear células que se moveram algumas linhas para cima ou para baixo dentro de uma pasta de trabalho, enquanto este roda quando uma fórmula deixa completamente a pasta de trabalho que a compilou, onde linhas movidas não são o problema e numeração privada da pasta de trabalho é
// 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 aquela planilha, ou aquele nome?
A recompilação do AddCopy só tem sucesso quando a pasta de trabalho de destino já tem tudo a que o texto da fórmula se refere, e as duas lacunas que aparecem na prática são uma planilha de mesmo nome que ainda não foi copiada neste lote, e um nome definido com escopo de pasta de trabalho que nunca existiu no destino. O HotXLS não levanta uma exceção quando a recompilação falha no meio de uma cópia de planilha — a atribuição Value da célula silenciosamente armazena o texto da fórmula como uma string simples em vez disso, um modo de falha deliberado e inspecionável, em vez de silencioso, já que uma célula de fórmula que inesperadamente mostra texto literal como =SUM(Q1!B2:B12) em vez de um número calculado é o sinal de que algo upstream na cópia não se resolveu. Antes de desistir, AddCopy tenta um reparo: percorre a árvore de sintaxe da fórmula falha coletando cada ID de nome definido que a fórmula toca, e para cada nome com escopo de pasta de trabalho que existe na origem, mas ainda não no destino, copia o nome e recompila o mesmo texto uma segunda vez. Nomes com escopo de planilha ficam fora do que esse reparo consegue consertar, já que um nome visível apenas para fórmulas em uma planilha da pasta de trabalho de origem não tem um slot equivalente para migrar, e um destino que já possui um nome com a mesma grafia é deixado intocado, em vez de sobrescrito, sob a suposição de que um nome que quem chama deliberadamente pré-criou é o que se quer respeitado. Dentro de uma única pasta de trabalho, a busca de nome de uma fórmula entre planilhas percorre do escopo de planilha para o escopo de pasta de trabalho automaticamente, que é o mecanismo que o artigo do HotXLS sobre nomes definidos e fórmulas entre planilhas cobre; cruzar uma fronteira real de pasta de trabalho remove essa rede de segurança por completo, e um nome precisa ser deliberadamente carregado através, ou a fórmula que depende dele degrada para texto
Referências de série de gráfico precisam da mesma correção, mas um caminho de código diferente
Uma série de gráfico do HotXLS que plota um intervalo de células atinge precisamente 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 chama o registro que a carrega de BRAI ([MS-XLS] seção 2.4.51) — mas AddCopy não consegue corrigi-la reutilizando o caminho normal de carregamento de gráfico, porque esse caminho é exatamente o que cria o bug. Quando um registro de gráfico é analisado a partir do disco no curso normal de abertura de um arquivo, sua árvore de fórmula é construída traduzindo os bytes brutos por meio de qualquer instância de calculadora que esteja fazendo a análise; alimente os bytes BRAI brutos de um gráfico de origem pelo próprio carregador de registro comum da pasta de trabalho de destino, e o ixti embutido nesses bytes é resolvido contra a tabela EXTERNSHEET do destino, de modo que a série silenciosamente aponta para o que quer que ocupe aquele slot por lá — a mesma classe de erro que copiar a árvore compilada de uma célula inalterada, só que mais difícil de perceber porque ninguém lê fórmulas de série de gráfico da forma como lê fórmulas de célula. O HotXLS evita essa armadilha com um caminho de clonagem dedicado, em vez disso: TXLSCustomChart.AssignFrom copia os próprios bytes de cabeçalho não-fórmula de cada registro de gráfico literalmente, depois reconstrói o intervalo anexado por meio do mesmo primitivo de descompilar-e-recompilar usado para células comuns, de modo que a nova árvore é construída contra a tabela EXTERNSHEET do destino do zero, em vez de reinterpretada contra ela posteriormente
O mesmo problema de numeração, um índice de fonte por vez
Nem todo número local de pasta de trabalho dentro de um gráfico ou uma célula de rich text é uma fórmula, e um índice de fonte é a mesma classe de problema em miniatura. Sequências de rich text, junto com mais dois tipos de registro de gráfico que carregam uma legenda ou fonte de eixo, armazenam uma referência de fonte como um inteiro bruto, um índice na própria tabela de fontes da pasta de trabalho proprietária, e esse índice não significa nada na tabela de uma pasta de trabalho diferente — poderia igualmente apontar para uma tipografia, tamanho ou cor completamente diferente por lá. O HotXLS resolve isso por valor, em vez de por número: procura os atributos de fonte reais naquele índice na tabela de origem, encontra ou cria uma entrada correspondente na tabela de fontes do destino, e reescreve o índice armazenado para apontar para esse novo slot. Uma peculiaridade de formato torna a própria busca complicada — o índice numerado no arquivo pula o slot 4, uma lacuna de numeração que a [MS-XLS] seção 2.5.339 documenta, de modo que o código precisa deslocar o índice para baixo em um antes de comparar fontes e de volta para cima em um 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 com uma fórmula que já aponta para fora da pasta de trabalho?
Uma fórmula que alcança uma terceira pasta de trabalho antes mesmo de você chamar AddCopy é o único caso que a ida e volta por texto não consegue carregar, porque o próprio descompilador de fórmula-para-texto do HotXLS deliberadamente não sintetiza texto de colchete [Book]Sheet! para uma referência externa, e o compilador do outro lado também não aceita essa sintaxe como entrada — então esse caso único roda por meio de um segundo mecanismo que nunca toca texto de forma alguma. Quando o reparo de migração de nome descrito acima ainda deixa uma célula como uma string, e a pasta de trabalho de origem tem um nome de arquivo real, AddCopy muda de estratégia: copia profundamente a própria árvore de fórmula compilada, em vez de seu texto, depois entrega a cópia a uma passada de reancoramento dedicada, RebindExternRefsInTree, que a percorre nó por nó. Para cada referência de intervalo que encontra, essa passada resolve a entrada EXTERNSHEET da origem de volta em um par de nomes de planilha, e registra, ou reutiliza, uma entrada equivalente nas próprias tabelas de referência externa do destino, criando um link de pasta de trabalho externa totalmente novo se o destino nunca tiver referenciado aquele arquivo de origem antes
É aqui que o problema de numeração local da pasta de trabalho está em sua forma mais literal, porque um token de referência externa agrupa três coordenadas separadas em um único campo, e cada uma delas é privada à pasta de trabalho que a escreveu: qual pasta de trabalho externa, um slot na própria lista de livros externos do destino, atribuído na ordem em que essa pasta de trabalho por acaso os registrou; qual planilha dentro da própria lista de planilhas daquela pasta de trabalho externa, armazenada como um índice baseado em 1 com escopo especificamente para o livro externo, um domínio de numeração inteiramente diferente dos próprios IDs de planilha internos do destino; e o próprio intervalo de células, coordenadas simples de linha e coluna que não precisam de tradução porque nunca foram relativas à pasta de trabalho, para começo de conversa. Erre qualquer um dos dois primeiros e o Excel ainda abre o arquivo, ainda mostra uma fórmula, e a avalia contra as células externas erradas sem reclamar. Um tipo de nó derrota até esse reancoramento em nível de árvore: uma referência a um nome definido, um índice na própria tabela de nomes privada da pasta de trabalho, exatamente da forma como um índice de planilha é privado ao próprio EXTERNSHEET, sem nenhum reparo equivalente em nível de árvore disponível — no instante em que a passada de reancoramento encontra uma referência de nome em qualquer lugar da árvore, ela abandona a fórmula inteira, em vez de escrever uma parcialmente correta. Mesmo quando o reancoramento tem sucesso, a célula de destino não mostra um número recém-recalculado; mostra o valor que a célula de origem já mantinha no momento da cópia, mantido em um slot em cache da mesma forma que o próprio Excel armazena em cache o último valor conhecido de qualquer referência externa até você atualizar links explicitamente, que é o padrão certo, já que recalcular através de um link vivo para outro arquivo é exatamente o tipo de operação que você quer disparar uma vez, deliberadamente, em vez de a cada abertura
O que esse design custa a você
A maquinaria de descompilar-e-recompilar do AddCopy não é gratuita, e o custo vale a pena planejar antes, e não depois, de você roteirizar um grande job de consolidação. Copiar uma planilha dentro da mesma pasta de trabalho toma o caminho barato, uma duplicação direta em memória da árvore compilada, porque cada índice dentro dela já é válido na pasta de trabalho onde está permanecendo; uma cópia entre pastas de trabalho paga por uma análise genuína em cada célula de fórmula, em vez disso, descompilar para texto e depois compilar esse texto de novo do zero, e embora a diferença não valha a pena medir em uma planilha com algumas dezenas de fórmulas, uma pasta de trabalho de origem com dezenas de milhares de células de fórmula, copiada como uma planilha entre dezenas em um job em lote, deve esperar que a recompilação domine o tempo de execução, e não o I/O de arquivo ao redor dela. A ordem de cópia importa por um segundo motivo além da velocidade: uma fórmula que referencia uma planilha que AddCopy ainda não alcançou neste lote falha sua recompilação pelo mesmo motivo que uma fórmula referenciando uma planilha genuinamente inexistente falha, de modo que um job que copia a planilha B antes da planilha A cuja fórmula depende dela vai ver essa fórmula degradar exatamente como descrito acima, texto de string ou um fallback de link externo apontando de volta exatamente para o arquivo de origem de onde acabou de vir. E como cada pasta de trabalho de origem em um lote de consolidação geralmente é escrita independentemente, vale a pena testar explicitamente o único modo de falha que nenhum arquivo de origem individual jamais poderia ter avisado — cinco pastas de trabalho de filial que cada uma totaliza os números de uma filial parceira podem se combinar em uma referência circular genuína dentro da pasta de trabalho de resumo sem que nenhum arquivo de origem individual jamais tenha contido uma, um ciclo que só existe uma vez que cada planilha pousou no mesmo lugar e o recálculo roda sobre o conjunto combinado
A cópia de planilha entre pastas de trabalho vem como comportamento padrão de AddCopy no Componente Excel HotXLS para Delphi para Delphi e C++Builder; a página do produto traz a referência completa da API de planilha e pasta de trabalho, incluindo o comportamento de gráfico, rich text e referência externa descrito aqui