A reporting service writes SUM(B2:B501) into a totals cell, saves the workbook, then exports the same sheet to CSV for a downstream importer. The .xlsx opens perfectly in Excel, totals and all — yet the CSV contains the literal text =SUM(B2:B501) where a number should be. Nothing is broken. HotXLS, like the file formats themselves, treats a formula as stored text plus an optional cached result: Excel evaluates formulas when it opens the file, but a CSV exporter reproduces cell content as it stands, and nobody in a server-only pipeline ever ran the calculation. The fix is the part of HotXLS this article is about — a built-in formula engine you invoke explicitly, and extend with your own functions.
HotXLS is a native Object Pascal library for reading and writing XLS and XLSX files from Delphi and C++Builder without Excel automation, and its calculation engine works the same way in both facades.
Formulas are stored, not eagerly evaluated
Writing a formula into a cell does not compute anything. At save time the workbook records the formula text (and, on the XLS side, flags governed by RecalcOnSave, which defaults to True and tells Excel to recalculate on open). That model is correct for files destined for Excel, and wrong for pipelines that consume cell values directly — CSV export, HTML export, or your own code reading cells back. For those, evaluate explicitly with Calculate, which exists at four entry points: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook and TXLSXWorksheet all expose 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 numberThe expression handed to Calculate is ordinary Excel formula text — cross-sheet references, defined names and nested functions all resolve against the current in-memory workbook. That makes it useful far beyond patching CSV exports: it is an assertion mechanism. A generator that just wrote five hundred detail rows can ask the workbook for its own grand total and compare it against the figure computed independently in Pascal, catching off-by-one range errors before a customer's auditor does.
It also frames the right testing strategy for formula-heavy output. Excel remains the reference implementation of the formula language, so for the handful of formulas that carry business consequences, keep an approved fixture file whose expected values were produced by Excel itself, and have the build pipeline evaluate the generated workbook's formulas with Calculate against those fixtures. Differences then surface as failing tests in Delphi rather than as discrepancies discovered by a customer comparing two reports.
Adding business functions with OnUserFunction
When the engine meets a function name it does not recognize, it raises an event instead of failing outright. Assign OnUserFunction on either workbook class and you can resolve the call yourself:
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)');Three details earn attention. First, set Handled := True only when you actually recognized the name — leaving it False lets the engine continue its normal unknown-function handling, so one handler can serve several workbooks without claiming everything. Second, compare names case-insensitively (SameText), since formula authors type discount( and DISCOUNT( interchangeably. Third, arguments arrive pre-evaluated: DISCOUNT(A1) hands you the value of A1, not the reference, so a function cannot tell where its inputs came from — which leads directly to the next section.
Treat the handler body with the same defensiveness as any external entry point. The Args array reflects whatever the formula author typed, so validate the argument count and types before indexing into it, and decide what an invalid call returns — a Variant error value or a raised exception. An exception thrown inside the handler propagates out through the Calculate call that triggered evaluation, which is acceptable in a tightly controlled generator but rude in a service evaluating user-authored workbooks; in that setting, catch inside the handler and return a sentinel the surrounding workflow can recognize and log.
Position-aware functions need the Ex variant
Some functions legitimately depend on where they are being evaluated — a rate that differs per sheet, a row-relative lookup, a per-region multiplier on regional sheets. The plain event cannot express that, so the engine offers OnUserFunctionEx, identical except for one extra parameter:
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;TXLSUserFunctionContext carries SheetIndex, Row and Col of the evaluating cell. If a function's result depends on its location even slightly, wire the Ex event from the start; retrofitting context into a handler that thirty formulas already call is far messier than choosing the right signature on day one.
Custom functions do not travel to Excel
This is the design decision that separates a demo from a product: a formula such as DISCOUNT(A1) is meaningful only while your Delphi process and its event handler are alive. Open the saved file in Excel and DISCOUNT is an unknown name — the cell shows #NAME? unless a matching VBA function or add-in happens to exist on the user's machine.
So decide, per cell, which of two contracts you are shipping. Cells the user should see recalculate in Excel must be limited to Excel's built-in function vocabulary. Cells whose logic is proprietary should be evaluated in-process via Calculate and persisted as plain values — the custom function then acts as an internal calculation rule, not as file content. Mixing the two by persisting custom-function formulas is the one option that reliably generates support tickets.
There is a quiet upside to the values-only contract: it protects intellectual property. A pricing rule evaluated in your Delphi process and shipped as a number cannot be reverse-engineered from the workbook the way a visible formula can, and it cannot be broken by a user editing an intermediate cell. Invoice generators, commission statements and rate cards almost always belong in this camp; interactive what-if models, where the customer is expected to change inputs and watch totals move, are the case that genuinely needs live formulas — built from Excel's own vocabulary plus defined names.
Calculation modes, iteration, and R1C1: the XLS-facade dials
The XLS facade exposes the BIFF-level calculation settings that Excel reads from the file. CalculationMode accepts xlCalcManual, xlCalcAutomatic (the default) or xlCalcAutomaticExceptTables, and determines how Excel behaves after opening — a model workbook with thousands of formulas is often friendlier delivered in manual mode. EnableIteration (default False) with MaxIterations (default 100) and MaxIterationChange (default 0.001) unlocks deliberate circular references of the iterative-convergence kind used in some financial models. ReferenceStyle switches between A1 and R1C1 display, and UseFullPrecision mirrors Excel's precision-as-displayed option.
These properties live on the XLS facade because they map to BIFF records; when generating .xlsx, plan formulas so they do not depend on iterative settings, or compute the converged values in Delphi and write results.
Array formulas: the public entry point is XLSX
Legacy CSE-style array formulas are created through TXLSXRange.SetArrayFormula:
// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');The equivalent method exists in the XLS class hierarchy but sits in a private section, so there is no supported way to author new array formulas into .xls files — existing ones in opened files round-trip intact, but generation targets should be .xlsx whenever array semantics are part of the requirement. If a legacy .xls deliverable genuinely needs array behavior, the pragmatic route is to compute the array result in Delphi and write the individual values.
Two related reads on this site: defined names and cross-sheet formulas covers the name resolution the engine performs, and the CSV and TSV export article details the export behavior that makes explicit calculation necessary. The full engine reference, including the supported function set, ships with the HotXLS Component.