Formula syntax and name resolvers
As we saw on the Concepts page, Ixion stores formulas as tokens, and a
formula_name_resolver is what translates between a
formula string and those tokens. This page looks at the syntaxes Ixion
understands, what you can write in a formula, and how the same tokens come
out when printed in a different syntax.
Let’s start by creating a model with three sheets and a resolver for each of the syntaxes we’ll be using:
ixion::model_context cxt;
cxt.append_sheet("Sheet1");
cxt.append_sheet("Sheet2");
cxt.append_sheet("Annual Report");
auto excel_a1 = ixion::formula_name_resolver::get(
ixion::formula_name_resolver_t::excel_a1, &cxt);
auto excel_r1c1 = ixion::formula_name_resolver::get(
ixion::formula_name_resolver_t::excel_r1c1, &cxt);
auto calc_a1 = ixion::formula_name_resolver::get(
ixion::formula_name_resolver_t::calc_a1, &cxt);
auto odff = ixion::formula_name_resolver::get(
ixion::formula_name_resolver_t::odff, &cxt);
Passing the model context to get()
is what lets a resolver turn sheet names into sheet indices and back. If
your formulas never mention sheet names, you can pass nullptr instead.
The five syntaxes
Here is how a reference to cell B1 on a sheet named Sheet2 looks in each
of the syntaxes, along with the value of
formula_name_resolver_t that selects it:
Syntax |
Enum value |
Example |
|---|---|---|
Excel A1 |
|
|
Excel R1C1 |
|
|
LibreOffice Calc A1 |
|
|
OpenFormula (ODFF) |
|
|
ODF cell-range-address |
|
|
The Excel syntaxes separate the sheet name from the address with !,
whereas the Calc and ODF syntaxes use .. OpenFormula additionally wraps
every reference in square brackets. The ODF cell-range-address syntax is
what the ODF file format uses wherever an attribute holds a cell range
outside of a formula, such as the source range of a chart, a pivot table or
a database range. It is the same as Calc A1 except that a reference without
a sheet name starts with a bare ., as in .B1. Since there is no
formula cell for such a range to be relative to, the address is always
understood as absolute, and the $ markers are normally left out.
The R1C1 syntax spells out row and column numbers instead of column letters.
R1C2 is row 1, column 2, and a number in brackets is an offset from the
formula cell, so R[-1]C[2] is one row up and two columns to the right.
A bare R or C with no number at all means an offset of zero, so
RC[2] is the same row as the formula cell, two columns to the right.
Parsing and printing
Let’s parse a formula written in Excel A1 syntax for cell C1 on the first sheet, and take a look at the tokens we get back:
ixion::abs_address_t Sheet1_C1{0, 0, 2};
std::string formula = "SUM(A1:A10)+Sheet2!B1*2";
ixion::formula_tokens_t tokens = ixion::parse_formula_string(
cxt, Sheet1_C1, *excel_a1, formula);
std::cout << "number of tokens: " << tokens.size() << std::endl;
for (const ixion::formula_token& t : tokens)
std::cout << " " << t << std::endl;
number of tokens: 8
function token: (opcode=278; name='SUM')
opcode token: (name=open; s='(')
range ref token: (sheet:0 rel; row:0 rel; column:-2 rel)-(sheet:0 rel; row:9 rel; column:-2 rel)
opcode token: (name=close; s=')')
opcode token: (name=plus; s='+')
single ref token: (sheet:1 abs; row:0 rel; column:-1 rel)
opcode token: (name=multiply; s='*')
value token: 2
Notice that the two references have been turned into offsets from C1, and that neither token remembers which syntax it came from. That’s what lets us print the same tokens in any of the other syntaxes:
std::cout << "excel_a1: "
<< ixion::print_formula_tokens(cxt, Sheet1_C1, *excel_a1, tokens) << std::endl;
std::cout << "excel_r1c1: "
<< ixion::print_formula_tokens(cxt, Sheet1_C1, *excel_r1c1, tokens) << std::endl;
std::cout << "calc_a1: "
<< ixion::print_formula_tokens(cxt, Sheet1_C1, *calc_a1, tokens) << std::endl;
std::cout << "odff: "
<< ixion::print_formula_tokens(cxt, Sheet1_C1, *odff, tokens) << std::endl;
excel_a1: SUM(A1:A10)+Sheet2!B1*2
excel_r1c1: SUM(RC[-2]:R[9]C[-2])+Sheet2!RC[-1]*2
calc_a1: SUM(A1:A10)+$Sheet2.B1*2
odff: SUM([.A1:.A10])+[$Sheet2.B1]*2
Both parse_formula_string() and
print_formula_tokens() take the position of the formula
cell, since relative references only make sense in relation to it. You
don’t have to print from the same position you parsed at, and we’ll make use
of that below.
One parse, four prints. The tokens hold offsets from C1 and nothing else, so any resolver can print them back out.
References
A reference has up to three parts: a sheet, a column and a row. Each part can be relative, meaning it is stored as an offset from the formula cell and follows the formula around when it moves, or absolute, meaning it always points at the same place.
In the A1 syntaxes, a $ in front of the column letters or the row number
makes that part absolute. The R1C1 syntax has no $; a plain number is
absolute and a bracketed number is relative:
ixion::abs_address_t Sheet1_B2{0, 1, 1};
tokens = ixion::parse_formula_string(cxt, Sheet1_B2, *excel_a1, "$A$1+A$1+$A1+A1");
std::cout << "excel_a1: "
<< ixion::print_formula_tokens(cxt, Sheet1_B2, *excel_a1, tokens) << std::endl;
std::cout << "excel_r1c1: "
<< ixion::print_formula_tokens(cxt, Sheet1_B2, *excel_r1c1, tokens) << std::endl;
excel_a1: $A$1+A$1+$A1+A1
excel_r1c1: R1C1+R1C[-1]+R[-1]C1+R[-1]C[-1]
A range is two cell addresses joined by :, as in A1:B10. You can
also refer to whole columns or rows with A:A or 2:2. In the Excel
syntaxes a range can span sheets too, as in Sheet1:Sheet3!A1, and in the
Calc A1 and OpenFormula syntaxes each end of a range can carry its own sheet
name, as in Sheet1.A1:Sheet3.A1.
The three parts of a reference in the Calc A1 and Excel A1 syntaxes. In Excel A1 the sheet name alone makes the sheet part absolute.
Sheet-relative and sheet-absolute references
The sheet part of a reference deserves a closer look, because the syntaxes treat it differently.
In the Excel syntaxes, a reference without a sheet name is relative to the sheet the formula is on. If you copy the formula to another sheet, the reference follows it there. As soon as you write a sheet name, the reference becomes absolute and stays pinned to that sheet, no matter where the formula moves.
In the Calc A1 and OpenFormula syntaxes, writing a sheet name does not
make it absolute. Sheet1.A1 is still relative, and it takes a $ in
front of the sheet name, as in $Sheet1.A1, to pin it down. In Calc A1,
a reference with no sheet name is relative, as in the Excel syntaxes.
We can see the difference by parsing a formula for a cell on the first sheet
and then printing it as if the formula had moved to the second sheet. To
make the sheet part visible, we ask print_formula_tokens()
to always print the sheet name via print_config:
ixion::abs_address_t Sheet2_B2{1, 1, 1};
ixion::print_config config;
config.display_sheet = ixion::display_sheet_t::always;
// parse on Sheet1, print as if the formula had moved to Sheet2
tokens = ixion::parse_formula_string(cxt, Sheet1_B2, *excel_a1, "A1+Sheet1!A1");
std::cout << "excel_a1: "
<< ixion::print_formula_tokens(config, cxt, Sheet2_B2, *excel_a1, tokens) << std::endl;
tokens = ixion::parse_formula_string(cxt, Sheet1_B2, *calc_a1, "A1+Sheet1.A1+$Sheet1.A1");
std::cout << "calc_a1: "
<< ixion::print_formula_tokens(config, cxt, Sheet2_B2, *calc_a1, tokens) << std::endl;
excel_a1: Sheet2!A1+Sheet1!A1
calc_a1: Sheet2.A1+Sheet2.A1+$Sheet1.A1
In the Excel A1 line, the unqualified A1 now points at Sheet2 while
Sheet1!A1 stayed put. In the Calc A1 line, both A1 and Sheet1.A1
moved along to Sheet2, and only $Sheet1.A1 stayed put.
Note
By default, print_formula_tokens() prints a sheet
name only when the referenced sheet differs from the one the formula
cell is on. Use the display_sheet_t value in
print_config to always or never print it.
The same Excel A1 formula on two sheets. The unqualified A1 follows
the formula to Sheet2, while Sheet1!A1 stays pinned to Sheet1.
Sheet names with spaces
A sheet name that contains a space or a quote has to be quoted with single quotes, and a single quote inside the name is doubled. Ixion applies the same rule when printing:
tokens = ixion::parse_formula_string(
cxt, Sheet1_B2, *excel_a1, "'Annual Report'!A1:B2");
std::cout << "excel_a1: "
<< ixion::print_formula_tokens(cxt, Sheet1_B2, *excel_a1, tokens) << std::endl;
std::cout << "calc_a1: "
<< ixion::print_formula_tokens(cxt, Sheet1_B2, *calc_a1, tokens) << std::endl;
excel_a1: 'Annual Report'!A1:B2
calc_a1: $'Annual Report'.A1:B2
Functions, literals and operators
Function names are case-insensitive, and the arguments are separated by
the character stored in ixion::config::sep_function_arg,
which is a comma by default. TRUE() and FALSE() are functions too,
so they need their parentheses.
A string literal is enclosed in double quotes. An inline array is enclosed
in braces, with the argument separator between columns and ; between
rows. Error values such as #DIV/0!, #N/A, #NAME?, #REF! and
#VALUE! can be written directly into a formula:
tokens = ixion::parse_formula_string(
cxt, Sheet1_B2, *excel_a1, "IF(A1>=10,\"big\",\"small\")&{1,2;3,4}");
std::cout << ixion::print_formula_tokens(cxt, Sheet1_B2, *excel_a1, tokens) << std::endl;
IF(A1>=10,"big","small")&{1,2;3,4}
The operators are the usual ones: +, -, *, / and ^ for
arithmetic, & for string concatenation, =, <>, <, <=,
> and >= for comparison, plus parentheses and a leading sign.
Named expressions and table references
Any name that is neither a reference nor a function is taken to be a named
expression. The resolver doesn’t check whether such a name exists; it is
looked up when the formula is calculated, and a name that isn’t found gives
the #NAME? error at that point. As a consequence, a misspelled function
name doesn’t fail to parse either:
tokens = ixion::parse_formula_string(cxt, Sheet1_B2, *excel_a1, "sum(MyRange)");
for (const ixion::formula_token& t : tokens)
std::cout << " " << t << std::endl;
function token: (opcode=278; name='SUM')
opcode token: (name=open; s='(')
named expression token: 'MyRange'
opcode token: (name=close; s=')')
Table references such as Table1[Value] or Table1[[#Headers],[Value]]
are recognized by the Excel A1 resolver. Named expressions and tables each
get their own treatment on a later page.
Parse errors
When a formula string can’t be turned into tokens at all,
parse_formula_string() throws an exception derived from
general_error:
try
{
tokens = ixion::parse_formula_string(cxt, Sheet1_B2, *excel_a1, "A1+#FOO!");
}
catch (const std::exception& e)
{
std::cout << "parse failed: " << e.what() << std::endl;
}
parse failed: failed to parse an error token in lexer tokenizer: '#'
The complete source code of this example is available here.