O HotXLS pode avaliar uma fórmula como =[Rates.xlsx]FX!B4*C10 em relação a outra pasta de trabalho que está aberta em memória, não em relação a um valor obsoleto em cache. Você registra a segunda pasta de trabalho pelo nome com RegisterExternalWorkbook, e todo recálculo lê as células referenciadas diretamente daquele objeto. Cancele o registro e a avaliação recorre ao cache de vínculo externo que o arquivo já carrega
Isso fecha uma lacuna específica e frustrante. Pastas de trabalho do Excel referenciam umas às outras com frequência: uma tabela de taxas, um plano de contas, uma lista de preços mantida por outra equipe. Quando um arquivo desses é aberto por uma biblioteca em vez de pelo Excel, essas referências normalmente resolvem para os valores que foram armazenados em cache na última vez em que o Excel recalculou, o que pode ter meses, e nenhuma quantidade de recálculo corrige isso porque os dados simplesmente não estão lá
Como uma referência entre pastas de trabalho aparece dentro do arquivo?
Uma referência externa não é armazenada como o texto que você vê na barra de fórmulas. A pasta de trabalho mantém uma tabela de pastas de apoio, cada token de fórmula aponta para um índice nessa tabela, e uma parte separada de valores em cache guarda o último resultado conhecido para cada célula referenciada. É por isso que um arquivo aberto sem seus companheiros ainda mostra números
Analisar a forma textual tem suas próprias sutilezas. O lexer trata um nome de pasta entre colchetes como parte de um único identificador, de modo que [Book.xlsx]Sheet!A1 se torna um único token em vez de um colchete desbalanceado seguido de uma referência de planilha. Os nomes são então normalizados para busca: aspas, prefixos de caminho, a extensão do arquivo e a forma de índice numérico, tudo colapsa para um nome-base em maiúsculas, de modo que 'C:\Rates\[Rates.xlsx]FX'!B4 e [rates.xlsx]FX!B4 resolvem para a mesma pasta registrada
Registrando uma pasta de trabalho e recalculando
O registro é pelo nome, e o nome é aquele que aparece nas fórmulas da pasta de trabalho que faz a referência:
uses
lxHandleX;
var
Main, Rates: TXLSXWorkbook;
begin
Main := TXLSXWorkbook.Create;
Rates := TXLSXWorkbook.Create;
try
if (Main.Open('valuation.xlsx') <> 1) or
(Rates.Open('rates.xlsx') <> 1) then
Exit;
// Fórmulas em valuation.xlsx que dizem [Rates.xlsx]... agora leem
// do objeto ativo em vez dos valores externos em cache
Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
// Muda a origem e recalcula de novo: o novo valor flui adiante
Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
finally
Main.UnregisterExternalWorkbook('Rates.xlsx');
Rates.Free;
Main.Free;
end;
end;O registro mantém uma referência fraca. O HotXLS não assume a posse da pasta de trabalho registrada e não vai liberá-la, o que significa que a vida útil é sua para gerenciar, e a ordem importa: cancele o registro antes de liberar a pasta de trabalho de origem, ou, no mínimo, não recalcule depois de liberá-la
O que acontece quando uma pasta não está registrada?
A avaliação recorre ao cache de vínculo externo armazenado no arquivo, que é exatamente o comportamento de uma pasta de trabalho aberta sem seus companheiros. Esse fallback é o motivo pelo qual cancelar o registro é seguro, e não destrutivo
O comportamento após o cancelamento do registro vale a pena declarar com precisão, porque surpreende as pessoas. Quando uma referência não pode ser resolvida nem por uma pasta registrada nem pelo cache, a avaliação produz um erro, e esse erro não é gravado de volta na célula. A célula mantém seu último valor calculado. Registre novamente a origem e recalcule, e o valor volta a se atualizar
Essa regra de preservar em caso de falha é deliberada. A alternativa, gravar um valor de erro em toda célula dependente no momento em que uma origem fica indisponível, transformaria uma tabela de taxas temporariamente ausente em uma pasta de trabalho cheia de marcadores de erro que um recálculo bem-sucedido posterior teria de reparar. Manter o último valor bom significa que um pipeline interrompido deixa um documento que ainda faz sentido ao ser lido
A propagação de sujeira para na fronteira da pasta de trabalho
Editar uma célula na origem registrada não marca as células dependentes na pasta de trabalho principal como sujas. O recálculo incremental rastreia dependências dentro de uma pasta de trabalho; ele não observa através da fronteira. O Excel se comporta da mesma forma, razão pela qual suas referências externas se atualizam na abertura ou em uma atualização explícita, e não continuamente
A consequência prática é que mudar um valor de origem exige um recálculo explícito da pasta de trabalho que faz a referência, como no exemplo acima. Se seu aplicativo edita as duas pastas em um loop, recalcule uma vez no final em vez de após cada edição; o motor incremental descrito em recálculo incremental e o grafo de dependências então faz o mínimo de trabalho dentro de cada pasta de trabalho
Três formatos que esse recurso atende
Consolidação é o óbvio. Uma pasta de trabalho principal referencia doze arquivos mensais; registre os doze, recalcule uma vez e leia os números consolidados sem nunca abrir o Excel. O nome de registro de cada um precisa corresponder ao que as fórmulas da pasta principal realmente dizem, o que vale a pena verificar contra o texto da fórmula em vez de supor que corresponde ao nome do arquivo
Análise de cenários é o segundo. Registre uma origem, grave um conjunto de premissas nela, recalcule o modelo dependente, registre as saídas, depois repita. Como a origem é um objeto ativo, nenhum arquivo é gravado entre as iterações, e cem cenários custam cem recálculos em vez de cem ciclos de salvar e recarregar
Validação antes da entrega é o terceiro e o menos óbvio. Abra uma pasta de trabalho, registre suas companheiras, recalcule e compare os resultados com os valores que o arquivo já carregava. Uma discrepância significa que os valores em cache estão obsoletos, o que é um defeito que vale a pena capturar antes de um cliente receber o arquivo. Essa comparação é uma companheira natural do diffing de pastas de trabalho descrito em comparando duas pastas de trabalho do Excel
Higiene de referências antes de confiar nelas
Rode primeiro uma passagem de análise de fórmulas. Ela relata funções ausentes, referências externas e componentes de dependência circular em uma única travessia, o que diz exatamente quais nomes de pasta um documento espera antes de você sair procurando os arquivos:
var
Issues: TArray<TXLSXFormulaIssue>;
I: Integer;
begin
Issues := Main.AnalyzeFormulaIssues;
for I := 0 to High(Issues) do
if Issues[I].Kind = xlsxFormulaIssueExternalReference then
Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
[Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;Outros dois hábitos economizam tempo. Mantenha o nome de registro canônico — registre pelo nome de arquivo simples e deixe a normalização cuidar das variantes entre aspas e com prefixo de caminho nas fórmulas. E trate uma pasta de trabalho que referencia arquivos que você não pode fornecer como um artefato somente leitura: recalculá-la vai preservar os valores em cache, mas qualquer edição que mude uma célula dependente deixa o documento em um estado em que alguns valores são atuais e outros são históricos, sem nada no arquivo para dizer qual é qual. Copiar fórmulas entre pastas de trabalho é uma operação diferente, com suas próprias regras de reassociação, abordada em cópia entre pastas de trabalho e reassociação de fórmulas, e intervalos nomeados acrescentam outra camada descrita em nomes definidos e fórmulas entre planilhas
A avaliação ativa entre pastas de trabalho, o recálculo incremental e a análise de fórmulas rodam no mesmo motor para Delphi e C++Builder; a lista completa de recursos está na página do componente de planilha Delphi HotXLS