Uma biblioteca de folhas de cálculo que apenas armazena strings de fórmulas e uma biblioteca com um motor de fórmulas funcional são dois produtos diferentes que parecem idênticos até ao momento em que se solicita um número a um deles. A maior parte do código de folhas de cálculo em Delphi nunca nota esta lacuna, porque o Excel mascara-a: escreva SUM(B2:B501) numa célula, guarde, e o Excel recalcula o total no instante em que um humano abre o ficheiro. Retire o humano do processo, execute o mesmo livro através de um pipeline de servidor que exporta diretamente para CSV, e a diferença deixa de ser académica. O CSV transporta o texto literal =SUM(B2:B501) onde deveria estar um número, porque em momento algum ocorreu a avaliação real da fórmula.
É deste lado correto da linha que o HotXLS se posiciona. Trata uma fórmula da mesma forma que os formatos de ficheiro o fazem: como texto armazenado mais um resultado em cache opcional, pelo que uma exportação direta para CSV reproduz a receita em vez do prato. No entanto, também inclui um motor de cálculo que pode chamar diretamente, o mesmo motor em ambas as fachadas XLS e XLSX, além de um gancho (hook) para resolver nomes de funções desconhecidos pelo motor. O HotXLS é uma biblioteca nativa em Object Pascal que lê e escreve XLS e XLSX a partir de Delphi e C++Builder sem automação do Excel, e a sua componente de cálculo é o que converte as fórmulas armazenadas novamente em valores sob procura.
As fórmulas são armazenadas, não avaliadas de imediato
Escrever uma fórmula numa célula não calcula nada. Ao guardar, o livro regista o texto da fórmula. No caso do XLS, também regista flags controladas por RecalcOnSave, que por padrão é True e indica ao Excel para recalcular ao abrir. Esse modelo é correto para ficheiros destinados ao Excel e incorreto para pipelines que consomem valores de células diretamente, quer se trate de exportação para CSV, exportação para HTML ou do seu próprio código que lê as células. Para esses casos, avalie explicitamente com Calculate. Este método existe em quatro pontos de entrada: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook e TXLSXWorksheet expõem todos function Calculate(const Formula: WideString): Variant.
// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ','); // the CSV now carries the numberA expressão transmitida a Calculate é texto comum de fórmula do Excel. Referências cruzadas entre folhas, nomes definidos e funções aninhadas são todos resolvidos em relação ao livro atual em memória, o que torna a chamada útil muito além da correção de exportações para CSV. Encare-a como um mecanismo de asserção. Um gerador que acabou de escrever quinhentas linhas de detalhe pode solicitar ao livro o seu próprio total geral e compará-lo com o valor calculado independentemente em Pascal, detetando um erro de intervalo por desvio de um (off-by-one) antes que o auditor de um cliente o faça.
Também estabelece a estratégia de testes correta para outputs ricos em fórmulas. O Excel continua a ser a implementação de referência da linguagem de fórmulas, por isso, para o punhado de fórmulas que têm impacto nos negócios, mantenha um ficheiro fixture aprovado cujos valores esperados foram produzidos pelo próprio Excel, e configure o pipeline de compilação para avaliar as fórmulas do livro gerado com Calculate face a essas fixtures. As diferenças surgirão como testes falhados em Delphi e não como discrepâncias descobertas por um cliente ao comparar dois relatórios.
Adicionar funções de negócio com OnUserFunction
Quando o motor encontra um nome de função que não reconhece, aciona um evento em vez de falhar imediatamente. Atribua OnUserFunction a qualquer uma das classes de livro de cálculo e poderá resolver a chamada manualmente:
procedure TReportBuilder.HandleUserFunction(Sender: TObject;
const FunctionName: WideString; const Args: Variant;
var Value: Variant; var Handled: Boolean);
begin
if SameText(FunctionName, 'DISCOUNT') then
begin
Value := Args[0] * 0.9; // Args arrives as a Variant array
Handled := True;
end;
end;
// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');Três detalhes merecem atenção. Primeiro, defina Handled := True apenas quando reconhecer efetivamente o nome. Deixá-lo como False permite que o motor continue o seu processamento normal de funções desconhecidas, pelo que um único manipulador (handler) pode servir vários livros sem intercetar tudo o que por lá passa. Segundo, compare os nomes sem diferenciar maiúsculas de minúsculas com SameText, uma vez que os autores das fórmulas escrevem discount( e DISCOUNT( de forma indistinta. Terceiro, os argumentos chegam pré-avaliados: DISCOUNT(A1) fornece-lhe o valor de A1, não a referência, pelo que uma função não consegue identificar a origem dos seus inputs. Este último ponto introduz a limitação de que trata a secção seguinte.
Trate o corpo do manipulador com a mesma postura defensiva de qualquer ponto de entrada externo. O array Args reflete o que quer que o autor da fórmula tenha digitado, por isso valide o número e os tipos de argumentos antes de aceder aos seus índices, e decida logo de início o que uma chamada inválida deve retornar: um valor de erro Variant ou uma exceção levantada. A escolha é importante porque uma exceção lançada dentro do manipulador propaga-se através da chamada a Calculate que espoletou a avaliação. Isso é aceitável num gerador estritamente controlado, mas inadequado num serviço que avalia livros criados pelos utilizadores, onde uma fórmula incorreta deitaria abaixo o pedido. Nesse cenário, capture a exceção dentro do manipulador e retorne um sentinela que o fluxo de trabalho circundante possa reconhecer e registar.
Funções cientes da posição necessitam da variante Ex
Algumas funções dependem legitimamente do local onde estão a ser avaliadas. Uma taxa que difere por folha, uma pesquisa relativa à linha, um multiplicador por região que se aplica apenas nas folhas regionais: nenhum destes casos pode ser respondido apenas com os valores dos argumentos. O evento simples não consegue expressar isto, pelo que o motor oferece o OnUserFunctionEx, idêntico exceto por um parâmetro adicional:
procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
const FunctionName: WideString; const Args: Variant;
const Context: TXLSUserFunctionContext;
var Value: Variant; var Handled: Boolean);
begin
if SameText(FunctionName, 'REGIONRATE') then
begin
// the same formula yields a different rate on each regional sheet
Value := RateForSheet(Context.SheetIndex) * Args[0];
Handled := True;
end;
end;O TXLSUserFunctionContext contém o SheetIndex, a linha (Row) e a coluna (Col) da célula em avaliação. Se o resultado de uma função depender da sua localização, mesmo que ligeiramente, configure o evento Ex desde o início. Integrar o contexto num manipulador que já é chamado por trinta fórmulas é muito mais complexo do que escolher a assinatura correta no primeiro dia, sendo que os dois eventos são, de resto, tão semelhantes que há pouca razão para começar com o mais limitado.
As funções personalizadas não viajam para o Excel
Uma função personalizada reside inteiramente dentro do seu processo. O nome DISCOUNT apenas significa algo enquanto o seu código Delphi e o respetivo manipulador de eventos estiverem em execução. Se abrir o ficheiro guardado no Excel, DISCOUNT será apenas um nome não reconhecido; a célula apresentará #NAME? a menos que exista uma função VBA ou suplemento correspondente na máquina do utilizador. Este é o facto de design que separa uma demonstração de um produto comercializável, forçando uma escolha que deve fazer deliberadamente em vez de a descobrir mais tarde.
Decida, por célula, qual dos dois contratos está a entregar. As células que o utilizador deve ver recalcular dentro do Excel têm de ser construídas a partir do vocabulário de funções do próprio Excel e de mais nada. As células cuja lógica é proprietária devem ser avaliadas no processo com Calculate e persistidas como valores simples, para que a função personalizada se comporte como uma regra de cálculo interna e não como conteúdo do ficheiro. O modo de falha que gera invariavelmente pedidos de suporte é o caminho intermédio: permitir uma fórmula de função personalizada e esperar que o Excel a honre.
Há uma vantagem subtil no contrato de apenas valores: protege a propriedade intelectual. Uma regra de preços avaliada no seu processo Delphi e enviada como um número não pode ser alvo de engenharia inversa a partir do livro de cálculo como uma fórmula visível seria, e um utilizador não a pode quebrar editando uma célula intermédia. Os geradores de faturas, extratos de comissões e tabelas de tarifas pertencem quase sempre a este grupo. O caso que necessita genuinamente de fórmulas ativas é o modelo interativo de simulação ('what-if'), onde se espera que o cliente altere os inputs e veja os totais moverem-se, devendo estes ser construídos a partir do vocabulário do próprio Excel mais os nomes definidos.
Modos de cálculo, iteração e R1C1: os seletores da fachada XLS
A fachada XLS expõe as definições de cálculo ao nível BIFF que o Excel lê a partir do ficheiro. O CalculationMode aceita xlCalcManual, xlCalcAutomatic (o padrão) ou xlCalcAutomaticExceptTables, e determina o comportamento do Excel assim que o ficheiro é aberto. Um livro modelo com milhares de fórmulas é muitas vezes entregue de forma mais amigável em modo manual, permitindo ao destinatário decidir quando ocorre o esforço de recalculação. O EnableIteration (padrão False), em conjunto com o MaxIterations (padrão 100) e o MaxIterationChange (padrão 0.001), desbloqueia as referências circulares deliberadas do tipo convergência iterativa que surgem nalguns modelos financeiros. O ReferenceStyle alterna entre a exibição A1 e R1C1, e o UseFullPrecision reflete a opção de precisão conforme exibida do Excel.
Estas propriedades residem na fachada XLS porque se mapeiam para registos BIFF; ao gerar .xlsx, planeie as fórmulas para que não dependam de definições iterativas, ou calcule os valores convergidos em Delphi e escreva os resultados.
Fórmulas de matriz: o ponto de entrada público é o XLSX
As fórmulas de matriz legadas de estilo CSE são criadas através de TXLSXRange.SetArrayFormula:
// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');O método equivalente existe na hierarquia de classes XLS, mas encontra-se numa secção privada, pelo que não há uma forma suportada de criar novas fórmulas de matriz em ficheiros .xls. As existentes em ficheiros abertos efetuam o round-trip intactas; o que não pode fazer é criá-las. A regra que daí advém é bastante simples: quando a semântica de matriz faz parte dos requisitos, opte pelo formato .xlsx. Se um ficheiro legacy .xls necessitar genuinamente de comportamento de matriz, a via mais pragmática é calcular o resultado da matriz em Delphi e escrever os valores individuais nas células.
Duas leituras relacionadas neste site: nomes definidos e fórmulas cruzadas entre folhas cobre a resolução de nomes que o motor executa, e o artigo sobre exportação para CSV e TSV detalha o comportamento de exportação que torna necessário o cálculo explícito. A referência completa do motor, incluindo o conjunto de funções suportadas, acompanha o Componente HotXLS.