First step - creating model_context

This page walks through the low-level API one step at a time. If you haven’t read the Concepts page yet, it’s worth doing so first, as it explains the pieces we’ll be using here.

Create a model context instance

When using ixion, the very first step is to create a model_context instance:

ixion::model_context cxt;

The model_context class represents a document model data store that stores cell values spreading over one or more sheets. At the time of construction, the model contains no sheets. So the obvious next step is to insert a sheet:

// First and foremost, insert a sheet.
cxt.append_sheet("MySheet");

The append_sheet() method will append a new sheet to the model. You need to give a name when appending a sheet, and the name must be unique for each sheet.

Note

Each sheet has a fixed size which cannot be changed once the model_context object is instantiated. The default sheet size is 1048576 rows by 16384 columns. You can specify a custom sheet size by passing a desired sheet size value to the model_context constructor at the time of instantiation.

Populate model context with values

Now that you have your first sheet inserted, let’s put in some numeric values. In this example, we’ll insert into A1:A10 their respective row positions. To insert a numeric value, you use set_numeric_cell() which takes the position of the cell as its first argument and the value to set as its second argument. You need to use abs_address_t to specify a cell position.

// Now, populate it with some numeric values in A1:A10.
for (ixion::abs_address_t pos(0, 0, 0); pos.row <= 9; ++pos.row)
{
    double value = pos.row + 1.0; // Set the row position + 1 as the cell value.
    cxt.set_numeric_cell(pos, value);
}

Note that, since row and column positions are internally 0-based, we add one to emulate how the row positions are presented in typical spreadsheet program.

Inserting a string value can be done via set_string_cell() in one of two ways. The first way is to store the value to a type that decays to std::string_view, such as std::string, char array, or string literal, and pass it to the method directly:

// Insert a string value into B2.
ixion::abs_address_t B2(0, 1, 1);
std::string s = "This cell contains a string value.";
cxt.set_string_cell(B2, s);

// Insert a literal string value into B3.
ixion::abs_address_t B3(0, 2, 1);
cxt.set_string_cell(B3, "This too contains a string value.");

The string cells inserted this way will keep their values “inline”; the cells themselves have direct access to the values they store.

The second way is to add your string to the model_context’s internal string pool first which will return its string ID, and pass that ID to the method:

// Insert a string value into B4 via string identifier.
ixion::string_id_t sid = cxt.add_string("Yet another string value.");
ixion::abs_address_t B4(0, 3, 1);
cxt.set_string_cell(B4, sid);

The string cells inserted this way will internally only stores the IDs and don’t have direct access to the values associated with the cells.

The model_context class has two methods for inserting a string to the string pool: add_string() and append_string(). The add_string() method checks for an existing entry with the same string value upon each insertion attempt, and it will not insert the new value if the value already exists in the pool. The append_string() method, on the other hand, does not check the pool for an existing value and always inserts the value.

The append_string() method is appropriate if you know all your string entries ahead of time and wish to bulk-insert them. Otherwise using add_string() is appropriate in most cases.

Insert a formula cell into model context

Inserting a formula cell requires a few extra steps. First, you need to tokenize your formula string, and to do that, you need to create an instance of formula_name_resolver. The formula_name_resolver class is responsible for resolving “names” into references, functions, and named expressions names. Ixion provides multiple types of name resolvers, and you specify its type when passing an enum value of type formula_name_resolver_t when calling its static ixion::formula_name_resolver::get() function. In this example, we’ll be using the Excel A1 syntax:

// Tokenize formula string first.
auto resolver = ixion::formula_name_resolver::get(ixion::formula_name_resolver_t::excel_a1, &cxt);

You can also optionally pass a memory address of your model_context instance which is required for resolving sheet names. You can pass a nullptr if you don’t need to resolve sheet names.

Next, let’s create a formula string we want to tokenize. Here, we are inserting a formula expression SUM(A1:A10) into cell A11:

ixion::abs_address_t A11(0, 10, 0);
ixion::formula_tokens_t tokens = ixion::parse_formula_string(cxt, A11, *resolver, "SUM(A1:A10)");

To tokenize a formula string, you call the ixion::parse_formula_string() function and pass

  • a model_context instance

  • the position of the cell to insert the formula into,

  • a formula_name_resolver instance, and

  • the formula string to tokenize.

The function will then return a sequence of tokens representing the original formula string. Once you have the tokens, you can finally pass them to your model_context instance via set_formula_cell():

// Set the tokens into the model.
cxt.set_formula_cell(A11, std::move(tokens));

Note that you need to move your tokens to the method since instances of type ixion::formula_tokens_t are non-copyable and only movable. Once the formula cell is set, the model tracks its dependencies, so it can tell which formula cells to re-calculate for given modified cells.

Calculate formula cell

Now that we have the formula cell in, let’s run our first calculation. To calculate formula cells, you need to first specify a range of modified cells in order to query for all formula cells affected by it either directly or indirectly, which we refer to as “dirty” formula cells. Since this is our initial calculation, we can simply specify the entire sheet to be “modified” which will effectively trigger all formula cells:

ixion::rc_size_t sheet_size = cxt.get_sheet_size();
ixion::abs_range_t entire_sheet(0, 0, 0, sheet_size.row, sheet_size.column); // sheet, row, column, row span, column span
ixion::abs_range_set_t modified_cells{entire_sheet};

We will then pass it to query_and_sort_dirty_cells() to get a sequence of formula cell addresses to calculate:

// Determine formula cells that need re-calculation given the modified cells.
// There should be only one formula cell in this example.
std::vector<ixion::abs_range_t> dirty_cells = ixion::query_and_sort_dirty_cells(cxt, modified_cells);
std::cout << "number of dirty cells: " << dirty_cells.size() << std::endl;

Since so far we only have one formula cell, this should only return one range with the size of one row and one column. You will see the following output:

number of dirty cells: 1

Let’s inspect which cell it actually refers to:

std::cout << "dirty cell: " << dirty_cells[0] << std::endl;

which will print:

dirty cell: (sheet:0; row:10; column:0)-(sheet:0; row:10; column:0)

confirming that it certainly points to cell A11. Finally, pass this to calculate_sorted_cells():

// Now perform calculation.
ixion::calculate_sorted_cells(cxt, dirty_cells, 0);

to calculate cell A11. After that, you can retrieve the result of the calculation by calling get_numeric_value() for A11:

double value = cxt.get_numeric_value(A11);
std::cout << "value of A11: " << value << std::endl;

You will see the following output:

value of A11: 55

Modify formula cell

Let’s say you need to overwrite the formula in A11 to something else. The steps you need to take are the same as the steps for inserting a brand-new formula cell.

Let’s go through this step by step. First, create new tokens to insert:

// Insert a new formula to A11.
tokens = ixion::parse_formula_string(cxt, A11, *resolver, "AVERAGE(A1:A10)");

This time we are inserting the formula AVERAGE(A1:A10) in A11 to overwrite the previous one SUM(A1:A10). Overwriting a formula cell is the same as inserting a new one; the model drops the dependency information of the old formula and records that of the new one:

// Set the new formula cell.
cxt.set_formula_cell(A11, std::move(tokens));

Let’s re-calculate the new formula cell. The re-calculation steps are also very similar to the initial calculation steps. The first step is to query for all dirty formula cells. This time, however, we don’t query based on which formula cells are affected by modified cells, which we’ll specify as none. Instead, we query based on which formula cells have been modified, which in this case is A11:

// This time, we know that none of the cell values have changed, but the
// formula A11 is updated & needs recalculation.
ixion::abs_range_set_t modified_formula_cells{A11};
dirty_cells = ixion::query_and_sort_dirty_cells(cxt, ixion::abs_range_set_t(), &modified_formula_cells);
std::cout << "number of dirty cells: " << dirty_cells.size() << std::endl;

As is the first calculation, you should only get one dirty cell address from the query_and_sort_dirty_cells() call. Running the above code should produce:

number of dirty cells: 1

The rest should be familiar:

// Perform calculation again.
ixion::calculate_sorted_cells(cxt, dirty_cells, 0);

value = cxt.get_numeric_value(A11);
std::cout << "value of A11: " << value << std::endl;

You should see the following output when finished:

value of A11: 5.5

Overwrite a value cell with a formula cell

Next, let’s overwrite A10, which currently stores a numeric value, with a formula cell. The formula we use here references no other cells, but that makes no difference to how you set it:

// Overwrite A10 with a formula cell with no references.
ixion::abs_address_t A10(0, 9, 0);
tokens = ixion::parse_formula_string(cxt, A10, *resolver, "(100+50)/2");
cxt.set_formula_cell(A10, std::move(tokens));

The model tracks the dependencies of the new formula cell, and drops those of the cell it replaced if that was a formula cell. You don’t need to do anything beyond calling set_formula_cell().

Let’s obtain all formula cells in need of re-calculation:

modified_formula_cells = { A10 };
dirty_cells = ixion::query_and_sort_dirty_cells(cxt, ixion::abs_range_set_t(), &modified_formula_cells);
std::cout << "number of dirty cells: " << dirty_cells.size() << std::endl;

Here, we are only passing one modified formula cell which is A10, and no other cells being modified. Since cell A11 references A1:A10 and A10’s value has changed, this should also trigger A11 for re-calculation. Running this code should produce the following output:

number of dirty cells: 2

Let’s calculate all affected formula cells and check the results of A10 and A11:

ixion::calculate_sorted_cells(cxt, dirty_cells, 0);
value = cxt.get_numeric_value(A10);
std::cout << "value of A10: " << value << std::endl;
value = cxt.get_numeric_value(A11);
std::cout << "value of A11: " << value << std::endl;

Running this code should produce the following output:

value of A10: 75
value of A11: 12

The complete source code of this example is available here.