Uma biblioteca de folhas de cálculo que apenas guarda strings de fórmulas e outra que possui um motor de cálculo ativo são produtos totalmente distintos que parecem idênticos até que tente obter o valor numérico de uma célula. A maioria do código Delphi não deteta esta separação porque o Excel a encobre: se escrever a instrução SUM(B2:B501) numa célula e gravar, o Excel calculará o total assim que o usuário abrir o arquivo. Contudo, se remover o usuário do fluxo e enviar o mesmo livro para um processo automático no servidor que o exporte para CSV, a diferença passa a ser crítica: o arquivo CSV conterá o texto literal =SUM(B2:B501) no local onde devia figurar um número, dado que o motor de cálculo nunca foi executado
É nesta separação que reside o valor do HotXLS. Este trata as fórmulas de acordo com a especificação dos formatos, como texto armazenado acompanhado por um valor em cache opcional; como tal, uma exportação simples para CSV gera a fórmula (a receita) e não o valor (o prato final). Contudo, o HotXLS também inclui um motor de cálculo que pode invocar diretamente (comum às interfaces XLS e XLSX), bem como um gancho (hook) para resolver funções desconhecidas do motor. O HotXLS é uma biblioteca nativa Object Pascal que lê e escreve formatos XLS e XLSX a partir de Delphi e C++Builder sem automação do Excel, e o motor de cálculo é precisamente o componente que converte as fórmulas em valores reais quando necessário
As fórmulas são armazenadas e não avaliadas de imediato
Escrever uma fórmula numa célula não realiza qualquer cálculo no imediato. No momento da gravação, o livro limita-se a registar o texto da fórmula. No lado XLS, grava também sinalizadores controlados pela propriedade RecalcOnSave (com valor padrão True), instruindo o Excel a recalcular no carregamento. Esta abordagem é correta para arquivos lidos no Excel, mas falha em processos automatizados que consultem diretamente o valor da célula (como exportações para CSV ou HTML ou a leitura de dados via código). Para estes cenários, force a avaliação explicitamente com Calculate. Este método está disponível em quatro classes: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook e TXLSXWorksheet expõem a assinatura function Calculate(const Formula: WideString): Variant
A expressão fornecida a Calculate segue a sintaxe comum do Excel. Referências entre folhas de cálculo, nomes definidos e funções aninhadas são resolvidas em relação ao estado do livro na memória, o que torna a chamada útil muito além da correção de CSVs. Utilize-a como rotina de validação: um gerador que tenha escrito quinhentas linhas de detalhe pode solicitar o total calculado à folha de cálculo e compará-lo com o valor somado internamente em Pascal, detetando desvios nos limites do intervalo (off-by-one) antes que o erro chegue ao cliente
Esta funcionalidade também define a estratégia correta para testar livros complexos. O Excel continua a ser o padrão no cálculo de fórmulas; como tal, para fórmulas críticas do negócio, mantenha arquivos modelo (fixtures) validados previamente no Excel e use o método Calculate no fluxo de testes para comparar o resultado gerado com os valores de referência. As divergências manifestar-se-ão como falhas nos testes unitários em Delphi, evitando que discrepâncias sejam descobertas pelo cliente final ao comparar relatórios
// 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 numberAdicionar funções de negócio com o OnUserFunction
Quando o motor encontra um nome de função desconhecido, gera um evento em vez de falhar. Associe um manipulador a OnUserFunction na classe do livro de cálculo para resolver a chamada no seu código:
Três detalhes merecem atenção: em primeiro lugar, defina Handled := True apenas se identificar a função procurada. Manter o valor False permite ao motor prosseguir com a validação padrão de erro, viabilizando a partilha do mesmo manipulador por vários livros sem intercetar todo o tráfego; em segundo lugar, compare os nomes sem diferenciar maiúsculas de minúsculas via SameText, uma vez que os usuários escrevem discount( ou DISCOUNT( indiferentemente; em terceiro lugar, os argumentos são recebidos já calculados (a chamada DISCOUNT(A1) envia-lhe o valor de A1 e não a sua referência, pelo que a função não consegue determinar a coordenada de origem do dado). Esta particularidade dita a limitação detalhada na secção seguinte
Desenhe o manipulador com as mesmas salvaguardas que aplicaria a qualquer ponto de entrada externo. O array Args reflete o que foi digitado na fórmula; como tal, valide a contagem de argumentos e respetivos tipos antes de aceder aos índices, e determine de início qual o comportamento perante argumentos inválidos (retornar um erro tipo Variant ou gerar uma exceção). Esta escolha é importante porque uma exceção gerada no manipulador propaga-se na chamada a Calculate que iniciou a avaliação. Tal comportamento é aceitável em geradores internos controlados, mas inadequado em serviços que processem arquivos de terceiros (onde uma fórmula malformada derrubaria o processamento). Neste último cenário, trate as exceções no interior do manipulador e devolva um valor de erro mapeado no fluxo principal
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)');Funções sensíveis ao posicionamento requerem a variação Ex
Algumas funções dependem legitimamente do local onde estão a ser avaliadas (como uma taxa variável por folha, uma pesquisa baseada na linha ou um fator de multiplicação regional restrito a certas folhas). Nenhuma destas variáveis é exposta nos argumentos; como tal, a assinatura simples do evento não é suficiente, disponibilizando o motor o método OnUserFunctionEx com um parâmetro complementar:
O tipo TXLSUserFunctionContext disponibiliza as propriedades SheetIndex, Row e Col da célula sob avaliação. Se o resultado da função depender do posicionamento (ainda que ligeiramente), adote o evento Ex desde o início. Adaptar o contexto num manipulador já utilizado por dezenas de fórmulas é muito mais complexo do que selecionar a assinatura correta no início do projeto, sendo ambos os eventos muito semelhantes
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;As funções personalizadas não são portáveis para o Excel
Uma função personalizada reside exclusivamente no seu processo. O nome DISCOUNT apenas é interpretado enquanto o seu código em Pascal e respetivo manipulador estiverem ativos na memória. Se abrir o arquivo no Excel, o nome DISCOUNT será desconhecido e a célula exibirá o erro #NAME? (a menos que exista uma macro VBA ou suplemento correspondente na máquina do usuário). Esta particularidade dita a diferença entre um protótipo e um produto final, obrigando a uma decisão deliberada no projeto
Determine o comportamento pretendido para cada célula: células que o usuário deva ver recalcular dinamicamente no Excel têm de usar estritamente o vocabulário de funções padrão do Excel; células cuja lógica seja proprietária ou confidencial devem ser avaliadas internamente via Calculate e gravadas como valores fixos (desta forma, a função personalizada atua como uma regra de cálculo interna e não como código no arquivo). O erro comum que gera pedidos de suporte consiste em gravar a fórmula de uma função personalizada esperando que o Excel a consiga avaliar
Gravar apenas os valores traz uma vantagem relevante: protege a propriedade intelectual. Uma regra de preços calculada em Pascal e exportada como número não pode ser analisada por engenharia reversa no livro de cálculo da mesma forma que uma fórmula visível; adicionalmente, o usuário não consegue corromper o cálculo alterando células intermédias. Faturas, mapas de comissões e tabelas de taxas enquadram-se nesta categoria. Os cenários que exigem fórmulas ativas limitam-se a modelos de simulação interativa, onde o usuário altera variáveis para observar a variação de totais, devendo estes ser desenhados com funções padrão do Excel e nomes definidos
Modos de cálculo, iteração e R1C1: os controles da faceta XLS
A interface XLS expõe as configurações de cálculo de nível BIFF que o Excel lê do arquivo. A propriedade CalculationMode aceita os valores xlCalcManual, xlCalcAutomatic (o padrão) ou xlCalcAutomaticExceptTables, determinando o comportamento do Excel no carregamento. Livros complexos com milhares de fórmulas são habitualmente distribuídos em modo de cálculo manual para que o usuário decida quando atualizar os cálculos. As propriedades EnableIteration (padrão False), em conjunto com MaxIterations (padrão 100) e MaxIterationChange (padrão 0.001), ativam referências circulares deliberadas para convergência iterativa em modelos financeiros. A propriedade ReferenceStyle altera a visualização entre os estilos A1 e R1C1, e UseFullPrecision controla a precisão decimal exibida
Estas definições residem na interface XLS porque correspondem a registros BIFF; se gerar arquivos .xlsx, evite desenhar fórmulas que dependam de ciclos iterativos ou realize os cálculos de convergência em Delphi antes de gravar
Fórmulas matriciais: o suporte público reside em XLSX
As fórmulas matriciais legadas (CSE-style) são criadas através de TXLSXRange.SetArrayFormula:
O método correspondente existe na hierarquia de classes XLS mas reside numa secção privada, pelo que não há suporte para criar novas fórmulas matriciais em arquivos .xls. Fórmulas matriciais já existentes são mantidas no processamento, mas o seu código não as consegue criar. A regra é simples: se necessitar de comportamento matricial, opte pelo formato .xlsx. Se um arquivo legado .xls exigir este comportamento, a solução prática consiste em realizar o cálculo matricial em Delphi e escrever os valores estáticos nas células
Artigos complementares: nomes definidos e fórmulas entre folhas aborda a resolução de referências do motor, e o artigo sobre exportações para CSV e TSV detalha o comportamento que exige o cálculo explícito. A referência completa do motor de cálculo, bem como as funções suportadas, são fornecidas com o HotXLS Component
// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');