Loading a model in bulk

Sometimes you build a whole model in one go from content that already exists, rather than editing cells one at a time: an importer feeding a document into ixion, say, or a program rebuilding a model it took apart earlier. The volume is large, so the work each model_context setter does on every call adds up: it checks what it replaces, validates a formula and records its references right away.

model_context_loader is a stand-in for model_context optimized for bulk loading. It wraps a model you own for the duration of the load, its setters only write, and the validation and the recording of references happen once, in finalize(). After that the loader has done its job and you can discard it. Validating everything at the end also loosens the order the content has to come in: you can load the cells first and the rest, such as named expressions and tables, afterwards, or the other way around.

Create a loader

You create the model and a name resolver on it as usual, then hand the model to the loader. From here on you talk to the loader, starting with the sheets:

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

ixion::model_context_loader loader(cxt);
loader.append_sheet("Sheet1");

The loader offers the setters you know from model_context, minus the ones that overwrite or empty cells, since a load only ever fills empty cells. Let’s put some values in:

loader.set_cell_values(0, {  // Sheet1!A1:B3
    { 1.0, 10.0 },
    { 2.0, 20.0 },
    { 3.0, 30.0 },
});

Formula cells and a name

Now the formula cells. Content loaded in bulk usually brings its results along, so each formula gets one as a formula_result. The first formula uses a name, MyData, which we define right after:

ixion::abs_address_t C1{0, 0, 2};
ixion::abs_address_t C2{0, 1, 2};

ixion::formula_tokens_t tokens = ixion::parse_formula_string(cxt, C1, *resolver, "SUM(MyData)");
loader.set_formula_cell(
    C1, ixion::formula_tokens_store::create(std::move(tokens)), ixion::formula_result(66.0));

tokens = ixion::parse_formula_string(cxt, C2, *resolver, "A1+B1");
loader.set_formula_cell(
    C2, ixion::formula_tokens_store::create(std::move(tokens)), ixion::formula_result(11.0));

Here is the name, set through the same set_named_expression() you would call on the model:

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

Finalize

Once everything is in, call finalize(). It validates every formula cell set through the loader and records their references with the dirty cell tracker:

loader.finalize();

This ends the load: any further call on the loader throws, and you can let it go. The model is yours to use as usual. The results you loaded are readable right away, and the references are in place, so a change to a cell propagates the usual way:

std::cout << "C1 = " << cxt.get_numeric_value(C1) << std::endl;
std::cout << "C2 = " << cxt.get_numeric_value(C2) << std::endl;

cxt.set_numeric_cell(A1, 100.0);
std::vector<ixion::abs_range_t> sorted = ixion::query_and_sort_dirty_cells(cxt, {A1});
ixion::calculate_sorted_cells(cxt, sorted, 0);

std::cout << "C1 = " << cxt.get_numeric_value(C1) << std::endl;
std::cout << "C2 = " << cxt.get_numeric_value(C2) << std::endl;
C1 = 66
C2 = 11
C1 = 165
C2 = 110

What the loader leaves to you

The loader’s setters only write, without the extra validations the regular model_context performs, so you as the user have a few extra responsibilities:

  • You write each cell at most once. A setter aimed at a cell that isn’t empty throws an exception.

  • You append every sheet a formula references before you set the formula, because the parser resolves sheet names against the sheets already present in the model.

  • You define every named expression and table a formula references before you call finalize(), since that is where the references get resolved and dependencies get recorded.

  • You leave the cells alone while the loader works on the model: don’t modify or calculate them through model_context until finalize() has run.

The complete source code of this example is available here.