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_contextuntilfinalize()has run.
The complete source code of this example is available here.