Fórmulas Entre Livros ao Vivo em Delphi com o HotXLS

O HotXLS consegue avaliar uma fórmula como =[Rates.xlsx]FX!B4*C10 em relação a outro livro que esteja aberto em memória, e não em relação a um valor em cache desatualizado. Regista-se o segundo livro pelo nome com RegisterExternalWorkbook, e cada recálculo lê as células referenciadas diretamente a partir desse objeto. Ao desregistá-lo, a avaliação recorre à cache de ligações externas que o ficheiro já traz

Isto fecha uma lacuna específica e frustrante. Os livros do Excel referenciam-se uns aos outros com regularidade: uma tabela de taxas, um plano de contas, uma lista de preços mantida por outra equipa. Quando esse ficheiro é aberto por uma biblioteca em vez de pelo Excel, essas referências resolvem normalmente para os valores que estavam em cache da última vez que o Excel recalculou, o que pode ter meses, e nenhuma quantidade de recálculos corrige isso, porque os dados simplesmente não estão lá

Como é uma referência entre livros dentro do ficheiro?

Uma referência externa não é armazenada como o texto que se vê na barra de fórmulas. O livro mantém uma tabela de livros de suporte, 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 ficheiro aberto sem os seus companheiros continua a mostrar números

A análise da forma textual tem as suas próprias subtilezas. O analisador léxico trata um nome de livro entre parênteses retos como parte de um único identificador, pelo que [Book.xlsx]Sheet!A1 se torna um único token, em vez de um parêntese reto desequilibrado seguido de uma referência de folha. Os nomes são depois normalizados para a pesquisa: aspas, prefixos de caminho, a extensão do ficheiro e a forma de índice numérico colapsam todos para um nome base em maiúsculas, pelo que 'C:\Rates\[Rates.xlsx]FX'!B4 e [rates.xlsx]FX!B4 resolvem para o mesmo livro registado

Registar um livro e recalcular

O registo é feito pelo nome, e o nome é aquele que aparece nas fórmulas do livro 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;

    // As fórmulas em valuation.xlsx que dizem [Rates.xlsx]... agora leem
    // a partir do objeto vivo, em vez dos valores externos em cache
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Alterar a origem e recalcular novamente: o novo valor propaga-se
    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 registo mantém uma referência fraca. O HotXLS não assume a posse do livro registado e não o vai libertar, o que significa que a gestão do tempo de vida é sua responsabilidade e a ordem importa: desregiste antes de libertar o livro de origem, ou, no mínimo, não recalcule depois de o libertar

O que acontece quando um livro não está registado?

A avaliação recorre à cache de ligações externas armazenada no ficheiro, que é exatamente o comportamento de um livro aberto sem os seus companheiros. É esse recurso à cache que torna o desregisto seguro, em vez de destrutivo

Vale a pena descrever com precisão o comportamento após o desregisto, porque surpreende as pessoas. Quando uma referência não pode ser resolvida nem a partir de um livro registado nem a partir da cache, a avaliação produz um erro, e esse erro não é escrito de volta na célula. A célula mantém o seu último valor calculado. Volte a registar a origem e recalcule, e o valor volta a atualizar-se

Esta regra de preservação em caso de falha é deliberada. A alternativa, escrever um valor de erro em cada célula dependente no momento em que uma origem fica indisponível, transformaria uma tabela de taxas temporariamente em falta num livro cheio de marcadores de erro que um recálculo bem-sucedido posterior teria de reparar. Manter o último valor válido significa que um pipeline interrompido deixa um documento que continua a fazer sentido na leitura

A propagação de sujidade (dirty) para na fronteira do livro

Editar uma célula na origem registada não marca como sujas (dirty) as células dependentes no livro principal. O recálculo incremental acompanha as dependências dentro de um único livro; não observa através da fronteira. O Excel comporta-se da mesma forma, razão pela qual as suas referências externas se atualizam ao abrir ou numa atualização explícita, e não continuamente

A consequência prática é que alterar um valor de origem exige um recálculo explícito do livro que faz a referência, como no exemplo acima. Se a sua aplicação editar os dois livros num ciclo, recalcule uma vez no final, em vez de o fazer após cada edição; o motor incremental descrito em recálculo incremental e o grafo de dependências faz então o trabalho mínimo dentro de cada livro

Três formas que esta funcionalidade serve

A consolidação é a mais óbvia. Um livro-mãe referencia doze ficheiros mensais; regista-se os doze, recalcula-se uma vez, e lêem-se os valores consolidados sem alguma vez abrir o Excel. O nome de registo de cada um tem de corresponder ao que as fórmulas do livro-mãe realmente indicam, o que vale a pena verificar contra o texto da fórmula, em vez de presumir que corresponde ao nome do ficheiro

A análise de cenários é a segunda. Regista-se uma origem, escreve-se nela um conjunto de pressupostos, recalcula-se o modelo dependente, regista-se os resultados, e repete-se. Como a origem é um objeto vivo, nenhum ficheiro é escrito entre iterações, e cem cenários custam cem recálculos, em vez de cem ciclos de gravar e recarregar

A validação antes da entrega é a terceira e a menos óbvia. Abre-se um livro, regista-se os seus companheiros, recalcula-se, e compara-se os resultados com os valores que o ficheiro já trazia. Uma discrepância significa que os valores em cache estão desatualizados, o que é um defeito que vale a pena detetar antes de um cliente receber o ficheiro. Essa comparação é um complemento natural da comparação de livros descrita em comparar dois livros do Excel

Higiene de referências antes de confiar nelas

Execute primeiro uma passagem de análise de fórmulas. Reporta funções em falta, referências externas e componentes de dependência circular numa única passagem, o que lhe diz exatamente que nomes de livros um documento espera antes de sair à procura dos ficheiros:

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;

Dois outros hábitos poupam tempo. Mantenha o nome de registo canónico — registe com o nome de ficheiro simples e deixe que a normalização trate das variantes entre aspas e com prefixo de caminho nas fórmulas. E trate um livro que referencia ficheiros que não consegue fornecer como um artefacto só de leitura: recalculá-lo preserva os valores em cache, mas qualquer edição que altere uma célula dependente deixa o documento num estado em que alguns valores são atuais e outros são históricos, sem nada no ficheiro que indique qual é qual. Copiar fórmulas entre livros é uma operação diferente, com as suas próprias regras de reassociação, abordada em copiar entre livros e a reassociação de fórmulas, e os intervalos com nome acrescentam outra camada descrita em nomes definidos e fórmulas entre folhas

A avaliação entre livros ao vivo, o recálculo incremental e a análise de fórmulas funcionam sobre o mesmo motor para Delphi e C++Builder; a lista completa de funcionalidades está disponível na página do componente de folha de cálculo HotXLS para Delphi