Skip to main content

Formulas

XLibur can both write formulas into a workbook and evaluate them with its own calculation engine, so you can read a computed result without opening Excel.

There are four kinds of formula in a worksheet:

KindHow you set itNotes
Normalcell.FormulaA1 / cell.FormulaR1C1One cell, one result
Arrayrange.FormulaArrayA1Legacy CSE formula over a fixed range
Dynamic arraycell.SetDynamicFormulaA1(...)Excel 365 spilling formula
Data table(read-only)What-if tables — preserved on round-trip

Normal formulas​

Assign to FormulaA1. The leading = is optional:

using XLibur.Excel;

var ws = workbook.Worksheet("Data");

ws.Cell("A2").Value = 1;
ws.Cell("B2").Value = 2;

ws.Cell("C2").FormulaA1 = "=A2+$B$2";
ws.Cell("C3").FormulaA1 = "SUM(A2:B2)";        // the = is optional
ws.Cell("C4").SetFormulaA1("=AVERAGE(A2:B2)"); // fluent form, returns the cell

R1C1 notation​

FormulaR1C1 is the relative-offset notation. RC[-2] means "same row, two columns left"; R3C2 is an absolute reference to B3:

ws.Cell("C3").FormulaR1C1 = "RC[-2]+R3C2";
ws.Cell("C5").FormulaR1C1 = "=SUM(R[-3]:R[-1])";

Both properties address the same underlying formula, so you can set one and read the other:

var cell = ws.Cell("C2");
cell.FormulaA1 = "=A2+B2";

Console.WriteLine(cell.FormulaA1);     // "A2+B2"
Console.WriteLine(cell.FormulaR1C1);   // "RC[-2]+RC[-1]"

To change what Excel displays in its UI, set the workbook reference style:

workbook.ReferenceStyle = XLReferenceStyle.R1C1;   // or A1, Default

Filling a formula down a range​

Setting FormulaR1C1 on a range writes the formula into every cell, with relative references resolved per cell — the equivalent of dragging the fill handle:

// Every cell in D2:D100 gets "=B{row}*C{row}"
ws.Range("D2:D100").FormulaR1C1 = "=RC[-2]*RC[-1]";

With A1 notation you build the string per row instead:

for (var row = 2; row <= 100; row++)
{
    ws.Cell(row, 4).FormulaA1 = $"=B{row}*C{row}";
}

Or copy a cell, which shifts its references:

var seed = ws.Cell("D2");
seed.FormulaA1 = "=B2*C2";
seed.CopyTo(ws.Cell("D3"));    // becomes "=B3*C3"

Formulas over named ranges and tables​

ws.Range("A2:A10").AddToNamed("SalesFigures");
ws.Cell("C1").FormulaA1 = "=SUM(SalesFigures)";

// Structured table references
ws.Cell("C2").FormulaA1 = "=SUM(SalesTable[Amount])";
ws.Cell("C3").FormulaA1 = "=SUMIF(SalesTable[Region], \"North\", SalesTable[Amount])";

// Cross-sheet
ws.Cell("C4").FormulaA1 = "=Summary!B2";
ws.Cell("C5").FormulaA1 = "='Q1 Sales'!B2";   // quote names containing spaces

Array formulas​

A legacy array (CSE) formula occupies a fixed range and produces one result per cell in it. Set FormulaArrayA1 on the target range — no curly braces, XLibur adds them:

// Single-cell array formula
ws.Range("B6").FormulaArrayA1 = "A2+A3";

// Multi-cell: transpose A2:A3 into two horizontal cells
ws.Range("C6:D6").FormulaArrayA1 = "TRANSPOSE(A2:A3)";

// The classic SUMPRODUCT-style aggregate
ws.Range("F1").FormulaArrayA1 = "SUM(IF(A2:A100>100, B2:B100, 0))";

The range you set it on is the extent of the array — size it to match the result the formula produces, exactly as you would with Ctrl+Shift+Enter in Excel.

var cell = ws.Cell("C6");
Console.WriteLine(cell.HasArrayFormula);   // true
Console.WriteLine(cell.FormulaReference);  // the array's range address

Dynamic array formulas​

Excel 365 functions such as FILTER, SORT, UNIQUE, SEQUENCE, and XLOOKUP spill: they return a result of whatever size the data implies, filling the cells below and to the right. These need SetDynamicFormulaA1 rather than FormulaA1, so that Excel does not prepend the implicit-intersection operator @:

ws.Cell("E1").SetDynamicFormulaA1("=SORT(UNIQUE(A2:A100))");
ws.Cell("F1").SetDynamicFormulaA1("=FILTER(A2:C100, C2:C100>1000)");
ws.Cell("G1").SetDynamicFormulaA1("=SEQUENCE(10, 1, 1, 5)");
warning

Using plain FormulaA1 for a dynamic array function writes =@FILTER(...), which Excel interprets as a single-cell intersection and will not spill. Use SetDynamicFormulaA1 for any of the functions listed under Dynamic array on the Functions page.

Clearing a formula​

Assigning an empty (or whitespace) string removes the formula. The cell keeps whatever value was last computed:

ws.Cell("C2").FormulaA1 = "";      // formula removed, cached value retained

To remove the formula and the value, clear the contents:

ws.Cell("C2").Clear(XLClearOptions.Contents);

To replace a formula with its result — "paste values" — read the value first:

var cell = ws.Cell("C2");
var result = cell.Value;      // evaluates the formula
cell.FormulaA1 = "";
cell.Value = result;

Applied to a whole sheet:

foreach (var cell in ws.CellsUsed(c => c.HasFormula).ToList())
{
    var value = cell.Value;
    cell.FormulaA1 = "";
    cell.Value = value;
}

Checking before you act:

if (cell.HasFormula)
{
    Console.WriteLine(cell.FormulaA1);
}

Evaluating formulas​

Reading Value on a formula cell evaluates it — XLibur's calculation engine runs the formula and caches the result:

ws.Cell("A1").Value = 10;
ws.Cell("A2").Value = 32;
ws.Cell("A3").FormulaA1 = "=SUM(A1:A2)";

Console.WriteLine(ws.Cell("A3").Value);       // 42 — evaluated on demand
Console.WriteLine(ws.Cell("A3").GetDouble()); // 42

CachedValue returns the stored result without triggering a recalculation. It may be stale — check NeedsRecalculation first:

var cell = ws.Cell("A3");

if (!cell.NeedsRecalculation)
{
    Console.WriteLine(cell.CachedValue);
}

cell.InvalidateFormula();       // force re-evaluation on next read

Recalculating in bulk:

ws.RecalculateAllFormulas();
workbook.RecalculateAllFormulas();

Evaluating an expression directly​

You can run a formula without writing it into a cell:

var value = workbook.Evaluate("=SUM(Data!A1:A10)");

// Sheet-scoped, so unqualified references resolve against that sheet
var local = ws.Evaluate("=SUM(A1:A10)");

// With an address so relative references have an anchor
var relative = ws.Evaluate("=A1+B1", "C1");

Saving computed values​

By default XLibur writes formulas without their results, and Excel computes them on open. Other consumers — a CSV exporter, a headless parser, LibreOffice in some configurations — may need the values present in the file. Ask for them at save time:

workbook.SaveAs("Report.xlsx", validate: false, evaluateFormulae: true);

// Equivalent, with the options object
workbook.SaveAs("Report.xlsx", new SaveOptions { EvaluateFormulasBeforeSaving = true });
note

If a formula throws during evaluation, that cell's value is simply not written — the save still succeeds. This matters when the workbook uses a function XLibur does not implement.

Calculation mode​

workbook.CalculateMode = XLCalculateMode.Auto;         // Excel recalculates automatically
workbook.CalculateMode = XLCalculateMode.Manual;       // user presses F9
workbook.CalculateMode = XLCalculateMode.AutoNoTable;  // auto, except data tables

Formulas and structural edits​

Inserting or deleting rows and columns rewrites affected references across the workbook, and renaming a sheet rewrites the formulas that mention it — the same behaviour as Excel:

ws.Cell("C1").FormulaA1 = "=SUM(B2:B10)";
ws.Row(5).InsertRowsAbove(1);
Console.WriteLine(ws.Cell("C1").FormulaA1);   // "SUM(B2:B11)"

ws.Name = "Renamed";
// A formula elsewhere reading "=Data!A1" now reads "=Renamed!A1"

Data tables​

Excel's Data Table feature (Data → What-If Analysis → Data Table) produces a special {=TABLE(row_input, col_input)} formula. XLibur reads these, preserves them across a load/save cycle, and keeps their cached values — but there is no public API to create one.

If a workbook needs a data table, build it in Excel as a template and let XLibur populate the input cells around it:

using var workbook = new XLWorkbook("WhatIfTemplate.xlsx");
var ws = workbook.Worksheet("Scenarios");

// The data table formula in B2:F10 is preserved; only the inputs change
ws.Cell("B1").Value = 0.05;    // interest rate
ws.Cell("A2").Value = 250_000; // principal

workbook.Save();

For scenario grids built entirely in code, generate the cross-product yourself with ordinary formulas — the result is a plain range Excel and every other reader understands:

double[] rates = [0.03, 0.04, 0.05, 0.06];
int[] terms = [10, 15, 20, 25, 30];

ws.Cell("A1").Value = "Principal";
ws.Cell("B1").Value = 250_000;

for (var c = 0; c < rates.Length; c++)
{
    ws.Cell(3, c + 2).Value = rates[c];
}

for (var r = 0; r < terms.Length; r++)
{
    ws.Cell(r + 4, 1).Value = terms[r];

    for (var c = 0; c < rates.Length; c++)
    {
        ws.Cell(r + 4, c + 2).FormulaA1 =
            $"=PMT({ws.Cell(3, c + 2).Address.ToStringFixed()}/12, " +
            $"{ws.Cell(r + 4, 1).Address.ToStringFixed()}*12, $B$1)";
    }
}

Where to next​