Named expressions and tables

This page covers two features that let formulas refer to things by name. A named expression is a sub-expression that has a meaning of its own, or that comes up in so many formulas that it deserves a name, so that SUM(MyData) can stand in for SUM(Sheet1!$A$1:$A$5). A table gives a name to a block of cells and to each of its columns, so that SUM(Table1[Amount]) can stand in for the range of one column. Both are stored in the model_context, and both take part in dependency tracking just like plain references do.

To keep the rest of the code focused on names and tables, let’s first define two helper functions. The first one puts a formula into a cell:

void set_formula(
    ixion::model_context& cxt, const ixion::formula_name_resolver& resolver,
    const ixion::abs_address_t& pos, std::string_view formula)
{
    ixion::formula_tokens_t tokens = ixion::parse_formula_string(cxt, pos, resolver, formula);
    cxt.set_formula_cell(pos, std::move(tokens));
}

It parses the formula string into tokens and stores them in the cell; the model records which cells the formula references as part of that. The second one calculates a set of newly inserted formula cells:

void calculate(ixion::model_context& cxt, const ixion::abs_range_set_t& new_formula_cells)
{
    std::vector<ixion::abs_range_t> sorted =
        ixion::query_and_sort_dirty_cells(cxt, {}, &new_formula_cells);

    ixion::calculate_sorted_cells(cxt, sorted, 0);
}

It calls query_and_sort_dirty_cells() to get the formula cells that need to be recalculated, sorted in dependency order, and hands that sequence to calculate_sorted_cells(), which calculates them on the calling thread.

With those in place, we create a model with a few sheets and a resolver for the Excel A1 syntax:

ixion::model_context cxt;
cxt.append_sheet("Sheet1");
cxt.append_sheet("Sheet2");
cxt.append_sheet("Inventory");

auto resolver = ixion::formula_name_resolver::get(
    ixion::formula_name_resolver_t::excel_a1, &cxt);

Named expressions

A named expression is a sequence of formula tokens stored under a name. You create the tokens the same way you would for a formula cell, with parse_formula_string(), and store them with set_named_expression(). Called without a sheet index, as here, it stores the name in the global scope, visible from every sheet. Once the name exists, any formula can use it in place of the expression it stands for:

for (ixion::row_t row = 0; row < 5; ++row)
    cxt.set_numeric_cell({0, row, 0}, row + 1); // Sheet1!A1:A5 = 1, 2, 3, 4, 5

ixion::abs_address_t Sheet1_A1{0, 0, 0};
ixion::formula_tokens_t tokens = ixion::parse_formula_string(
    cxt, Sheet1_A1, *resolver, "Sheet1!$A$1:$A$5");
cxt.set_named_expression("MyData", Sheet1_A1, std::move(tokens));

ixion::abs_address_t Sheet1_C1{0, 0, 2};
set_formula(cxt, *resolver, Sheet1_C1, "SUM(MyData)");
calculate(cxt, {Sheet1_C1});

std::cout << "Sheet1!C1 = " << cxt.get_numeric_value(Sheet1_C1) << std::endl;
Sheet1!C1 = 15

The position passed along with the tokens is the origin of the named expression: the cell the expression string was initially parsed at. Ixion keeps it for one purpose only, which is to turn the tokens back into a string later, the way a spreadsheet application shows the definition of a name in a dialog. It plays no part in calculation. set_named_expression() also comes in overloads that take no origin, which record the first cell of the first sheet in its place.

The expression doesn’t have to be a range. Any formula fragment will do, such as (1+2)/3 or PI()*2, and a named expression may refer to other named expressions in turn.

When a formula cell is calculated, each name in it is looked up and its expression is evaluated as a unit in place of the name, so MyData*2 means the whole expression times two. The lookup happens at calculation time, not when the formula is parsed, so you can define the name after the formulas that reference it. A name that doesn’t exist by the time the formula is calculated gives the #NAME? error.

The tokens of SUM(MyData) with the MyData token pointing at the entry for MyData in the model's named expressions, which holds a single range reference token.

A name token stands in for the named expression’s own tokens. The lookup happens by name when the formula cell is calculated.

To get a named expression back, call get_named_expression(). It returns a named_expression_t, which holds the tokens and the origin. When printing the tokens back as a string, you can use the origin as the position to print from, which reproduces the expression as it was originally written especially when you use the A1-style reference syntax. When using the R1C1-style reference syntax, where relative references are written as offsets, the origin position mostly makes no difference. The following code:

const ixion::named_expression_t* expr = cxt.get_named_expression(0, "MyData");
std::cout << "MyData = "
    << ixion::print_formula_tokens(cxt, expr->origin, *resolver, expr->tokens) << std::endl;

produces this output:

MyData = $A$1:$A$5

Relative references in a named expression

The origin has no say in how the expression is calculated. When the tokens are spliced into a formula, any relative reference in them is taken relative to the formula cell, not to the origin. This makes it possible to define names like “the cell to my left”:

ixion::abs_address_t Sheet1_B1{0, 0, 1};
tokens = ixion::parse_formula_string(cxt, Sheet1_B1, *resolver, "A1");
cxt.set_named_expression("LeftCell", Sheet1_B1, std::move(tokens));

ixion::abs_address_t Sheet1_B2{0, 1, 1};
ixion::abs_address_t Sheet1_C2{0, 1, 2};
cxt.set_numeric_cell(Sheet1_B2, 7.0);
set_formula(cxt, *resolver, Sheet1_C2, "LeftCell*10");
calculate(cxt, {Sheet1_C2});

std::cout << "Sheet1!C2 = " << cxt.get_numeric_value(Sheet1_C2) << std::endl;
Sheet1!C2 = 70

Here A1 was parsed at B1, so the tokens say “one column to the left”. Used from C2, that lands on B2, which holds 7. The origin only matters when you turn the tokens back into a string, where it decides that the reference prints as A1 rather than, say, B2.

Global and sheet-local names

A named expression is either global, visible from every sheet, or local to one sheet. The overloads of set_named_expression() that take a sheet index store a sheet-local name. When a formula uses a name, the sheet-local names of the formula’s own sheet are searched first, and the global names second:

for (ixion::row_t row = 0; row < 3; ++row)
    cxt.set_numeric_cell({1, row, 0}, (row + 1) * 10); // Sheet2!A1:A3 = 10, 20, 30

ixion::abs_address_t Sheet2_A1{1, 0, 0};
tokens = ixion::parse_formula_string(cxt, Sheet2_A1, *resolver, "Sheet2!$A$1:$A$3");
cxt.set_named_expression(1, "MyData", Sheet2_A1, std::move(tokens));

ixion::abs_address_t Sheet2_C1{1, 0, 2};
set_formula(cxt, *resolver, Sheet2_C1, "SUM(MyData)");
calculate(cxt, {Sheet2_C1});

std::cout << "Sheet1!C1 = " << cxt.get_numeric_value(Sheet1_C1) << std::endl;
std::cout << "Sheet2!C1 = " << cxt.get_numeric_value(Sheet2_C1) << std::endl;
Sheet1!C1 = 15
Sheet2!C1 = 60

Both formulas read SUM(MyData), but on Sheet2 the local definition wins, while Sheet1 falls back to the global one. get_named_expression() follows the same rule when you pass it a sheet index.

To go through all the names in a scope, use iterate_named_expressions(), with no argument for the global names or with a sheet index for the local ones. It returns a named_expressions_range, which you can walk with a range-for loop or hand to the standard algorithms. Each entry gives you the name and the expression it stands for. Here is a function that prints every name in a range:

void print_names(
    const ixion::model_context& cxt, const ixion::formula_name_resolver& resolver,
    const ixion::named_expressions_range& names)
{
    for (const auto& entry : names)
    {
        const ixion::named_expression_t& expr = entry.expression;
        std::cout << "  " << entry.name << " = "
            << ixion::print_formula_tokens(cxt, expr.origin, resolver, expr.tokens) << std::endl;
    }
}

And here it is applied to both scopes:

std::cout << "global names:" << std::endl;
print_names(cxt, *resolver, cxt.iterate_named_expressions());
std::cout << "names local to Sheet2:" << std::endl;
print_names(cxt, *resolver, cxt.iterate_named_expressions(1));
global names:
  LeftCell = A1
  MyData = $A$1:$A$5
names local to Sheet2:
  MyData = $A$1:$A$3

Note

The model looks through named expressions when it records a formula cell’s references, so a change to a cell that a name refers to marks the dependent formula cells dirty as usual. That also means a name has to be defined before the formula cells that use it get set. Redefining the name afterwards is another matter: the formula cells that use it still hold the references recorded from the old definition until you set them again.

Tables

A table is a rectangular range of cells with a name and named columns. Its range may start with a header row and end with one or more totals rows, and the rows in between are the data rows.

The range A1:B5 on the Inventory sheet outlined as Table1, with the header row, three data rows and the totals row bracketed and the columns Item and Amount named above.

The parts of a table, and how a structured reference names each of them.

Let’s put such a block of cells on a sheet:

cxt.set_cell_values(2, {  // Inventory!A1:B5
    { "Item",  "Amount" },
    { "pen",     1.5    },
    { "book",   12.0    },
    { "bag",    30.0    },
    { "Total",  nullptr },
});

The cells alone don’t make a table. You describe the table with a table_t and hand it to set_table():

ixion::table_t table;
table.name = "Table1";
table.sheet = 2;
table.range = {0, 0, 5, 2}; // A1:B5, as row, column, row span, column span
table.columns = { "Item", "Amount" };
table.totals_row_count = 1;
cxt.set_table(std::move(table));

The name must be unique within the model, the range must be valid, and the column names are listed in column order. Ixion doesn’t check that the column names match what the header row says; the header row is just cells.

Table references

With the table in place, formulas can refer to its parts by name. A table reference, commonly known as a structured reference in spreadsheet applications, names the table and a column, optionally narrowed down to one or more areas: #Data, #Headers, #Totals or #All. When no area is given, the reference means the data rows of that column. A formula in a cell that is itself inside the table can leave the table name out:

ixion::abs_address_t Inventory_B5{2, 4, 1};
ixion::abs_address_t Inventory_D1{2, 0, 3};
ixion::abs_address_t Inventory_D2{2, 1, 3};
ixion::abs_address_t Inventory_D3{2, 2, 3};

// inside the table
set_formula(cxt, *resolver, Inventory_B5, "SUM([Amount])");
// data rows only
set_formula(cxt, *resolver, Inventory_D1, "SUM(Table1[Amount])");
// header row
set_formula(cxt, *resolver, Inventory_D2, "COUNTA(Table1[#Headers])");
// totals row
set_formula(cxt, *resolver, Inventory_D3, "SUM(Table1[[#Totals],[Amount]])");

calculate(cxt, {Inventory_B5, Inventory_D1, Inventory_D2, Inventory_D3});

std::cout << "Inventory!B5 = " << cxt.get_numeric_value(Inventory_B5) << std::endl;
std::cout << "Inventory!D1 = " << cxt.get_numeric_value(Inventory_D1) << std::endl;
std::cout << "Inventory!D2 = " << cxt.get_numeric_value(Inventory_D2) << std::endl;
std::cout << "Inventory!D3 = " << cxt.get_numeric_value(Inventory_D3) << std::endl;
Inventory!B5 = 43.5
Inventory!D1 = 43.5
Inventory!D2 = 2
Inventory!D3 = 43.5

The forms a table reference can take are:

  • Table1[Amount] - the data rows of one column

  • Table1[[Item]:[Amount]] - the data rows of a span of columns

  • Table1[#Headers], Table1[#Totals], Table1[#All] - a whole area

  • Table1[[#Headers],[Amount]] - an area narrowed to one column

  • Table1[[#Headers],[#Data],[Amount]] - several areas combined

  • [Amount] - a column of the table the formula cell is in

Note that currently only the Excel A1 syntax supports table references.

A table reference is resolved to a cell range whenever it is needed, so it takes part in dependency tracking like any other reference. You can do the same resolution yourself with get_table_range():

ixion::abs_range_t range = cxt.get_table_range("Table1", "Amount", {}, ixion::table_area_data);
std::cout << "Table1[Amount] -> " << range << std::endl;

range = cxt.get_table_range("Table1", {}, {}, ixion::table_area_all);
std::cout << "Table1[#All]   -> " << range << std::endl;
Table1[Amount] -> (sheet:2; row:1; column:1)-(sheet:2; row:3; column:1)
Table1[#All]   -> (sheet:2; row:0; column:0)-(sheet:2; row:4; column:1)

Finally, get_table() fetches one table by name, and get_tables() lists the tables on a sheet:

for (const ixion::table_t* tab : cxt.get_tables(2))
    std::cout << "table on Inventory: " << tab->name << std::endl;
table on Inventory: Table1

The complete source code of this example is available here.