Sorting a sheet through a sheet view

A sheet_view is a named view of a sheet whose rows can get sorted without touching the sheet itself. Think of the sheet views feature of a spreadsheet application, where each user sorts the rows of a shared sheet the way they like while everyone keeps working on the same underlying data.

A view takes a snapshot of the content of its base sheet when created, and reads its cell values from that snapshot. Views never calculate; formula cells in a view carry the cached results they had on the base sheet when the snapshot was taken.

Populate the base sheet

Let’s start with a small sheet holding a header row, three data rows and a totals row, and dump it to see what we have:

ixion::model_context cxt;
cxt.append_sheet("Scores");

// header row
cxt.set_string_cell({0, 0, 0}, "Name");
cxt.set_string_cell({0, 0, 1}, "Score");

// data rows
cxt.set_string_cell({0, 1, 0}, "bob");
cxt.set_numeric_cell({0, 1, 1}, 20.0);
cxt.set_string_cell({0, 2, 0}, "amy");
cxt.set_numeric_cell({0, 2, 1}, 30.0);
cxt.set_string_cell({0, 3, 0}, "cid");
cxt.set_numeric_cell({0, 3, 1}, 10.0);

// totals row
cxt.set_string_cell({0, 4, 0}, "Total");
cxt.set_numeric_cell({0, 4, 1}, 60.0);

cxt.dump_sheet(std::cout, 0, ixion::sheet_dump_mode_t::simple);
std::cout << std::endl;

The dump_sheet() method prints the content of a sheet as a text grid:

+---+-------+-------+
|   | A     | B     |
+---+-------+-------+
| 1 | Name  | Score |
| 2 | bob   | 20    |
| 3 | amy   | 30    |
| 4 | cid   | 10    |
| 5 | Total | 60    |
+---+-------+-------+

Create a view

You create a view via create_sheet_view(), which takes the index of the sheet to view and a name for the view. The name must be unique among the views of the same sheet. The model context owns the view, and the returned reference stays valid until the view gets removed or the model context gets destroyed:

ixion::sheet_view& view = cxt.create_sheet_view(0, "by-score");

ixion::abs_rc_address_t A2{1, 0};
std::cout << "A2 in the view: " << view.get_string_value(A2) << std::endl;

The view offers the same cell value getters as model_context, except that they take an abs_rc_address_t, a cell position without a sheet index, since a view is always of one sheet. Right after creation the view shows exactly what the base sheet shows:

A2 in the view: bob

Sort a range

To sort, call sort() with the range to sort and one or more sort keys. Each sort_key_t names a column within the range and the sort direction as a sort_order_t value; the direction must always be given. Here we sort the three data rows by the score column in descending order:

ixion::abs_rc_range_t A2_B4{1, 0, 3, 2}; // the data rows
view.sort(A2_B4, {{1, ixion::sort_order_t::descending}}); // by column B

view.dump(std::cout, ixion::sheet_dump_mode_t::simple);
std::cout << std::endl;

The rows of the range move as units across all of its columns, while the header and totals rows outside the range stay put. The dump() method prints the view the same way dump_sheet() prints a sheet, with an extra base column showing the base sheet row each row of the view comes from:

+---+------+-------+-------+
|   | base | A     | B     |
+---+------+-------+-------+
| 1 |    1 | Name  | Score |
| 2 |    3 | amy   | 30    |
| 3 |    2 | bob   | 20    |
| 4 |    4 | cid   | 10    |
| 5 |    5 | Total | 60    |
+---+------+-------+-------+

The base sheet stays untouched:

std::cout << "A2 in the base sheet: " << cxt.get_string_value({0, 1, 0}) << std::endl;
A2 in the base sheet: bob

The sort is stable. In ascending order, cells of different types come in the following order:

  1. numeric values

  2. strings

  3. boolean values, false before true

  4. error values

  5. empty cells

A descending sort reverses the order of the first four types, while empty cells always come last regardless of the sort direction.

Note

Empty cells always come last regardless of the sort direction.

Formula cells sort by their cached results. See sort() for the details, including how formula groups behave when a sort moves their cells.

Map rows between the view and the base sheet

The base column of the dump comes from the row mapping the view keeps. You can query it in both directions via to_base_row() and to_view_row():

std::cout << "view row 1 shows base row " << view.to_base_row(1) << std::endl;
std::cout << "base row 1 is shown at view row " << view.to_view_row(1) << std::endl;
view row 1 shows base row 2
base row 1 is shown at view row 2

Note that row positions are 0-based here, whereas the dump labels rows from 1. The mapping reflects the combined effect of all the sorts applied to the view so far in case multiple sorts are applied.

The view-to-base mapping comes in handy when, in a hypothetical UI, a user edits a cell in a sheet view and the new value needs to reach the base sheet. You would query the position of the corresponding cell in the base sheet, and apply the same edit there.

Likewise, the base-to-view mapping comes in handy when a user edits a cell in the base sheet and the change needs to reach every view of that sheet. You would query the position of the corresponding cell in each view, and update what the view displays there.

Note

These scenarios are hypothetical. Ixion itself does not currently propagate edits between a sheet and its views; the mapping is there in case an application using Ixion needs to support such actions.

Refresh the view

Since a view is a snapshot, later edits to the base sheet do not automatically show up in it. Call refresh() to take a fresh snapshot. When the view is refreshed, the view takes the current cell values from the base sheet while preserving the row order even when the new values no longer match the sort order.

For example, we raise cid’s score on the base sheet from 10 to 40 and refresh:

// change cid's score at A4 on the base sheet
cxt.set_numeric_cell({0, 3, 1}, 40.0);

view.refresh();
view.dump(std::cout, ixion::sheet_dump_mode_t::simple);
std::cout << std::endl;
+---+------+-------+-------+
|   | base | A     | B     |
+---+------+-------+-------+
| 1 |    1 | Name  | Score |
| 2 |    3 | amy   | 30    |
| 3 |    2 | bob   | 20    |
| 4 |    4 | cid   | 40    |
| 5 |    5 | Total | 60    |
+---+------+-------+-------+

The view now shows the new score, but cid stays in the last data row. Re-sorting happens only when you ask for it. Sorting the same range again re-orders the rows as the view currently shows them:

view.sort(A2_B4, {{1, ixion::sort_order_t::descending}});
view.dump(std::cout, ixion::sheet_dump_mode_t::simple);
std::cout << std::endl;
+---+------+-------+-------+
|   | base | A     | B     |
+---+------+-------+-------+
| 1 |    1 | Name  | Score |
| 2 |    4 | cid   | 40    |
| 3 |    3 | amy   | 30    |
| 4 |    2 | bob   | 20    |
| 5 |    5 | Total | 60    |
+---+------+-------+-------+

Sort a table

When the sorted rows belong to a table, you can sort by a table column name instead of a range and a column position. First, define the table via set_table(). A table_t stores the name of the table, the sheet it is on, its entire range including the header row and the totals rows, its column names, and the number of totals rows:

ixion::table_t tab;
tab.name = "ScoreTable";
tab.sheet = 0;
tab.range = {0, 0, 5, 2}; // A1:B5
tab.columns = {"Name", "Score"};
tab.totals_row_count = 1;
cxt.set_table(std::move(tab));

Then call sort_table() with the table name, the column name to sort by, and the direction. Only the data rows of the table move; its header row and totals rows stay in place:

view.sort_table("ScoreTable", "Name", ixion::sort_order_t::ascending);
view.dump(std::cout, ixion::sheet_dump_mode_t::simple);
std::cout << std::endl;
+---+------+-------+-------+
|   | base | A     | B     |
+---+------+-------+-------+
| 1 |    1 | Name  | Score |
| 2 |    3 | amy   | 30    |
| 3 |    2 | bob   | 20    |
| 4 |    4 | cid   | 40    |
| 5 |    5 | Total | 60    |
+---+------+-------+-------+

Remove the view

A view stays alive until the model context that owns it gets destroyed, unless you remove it earlier via remove_sheet_view(). Afterwards get_sheet_view() no longer finds it:

cxt.remove_sheet_view(0, "by-score");
bool found = cxt.get_sheet_view(0, "by-score") != nullptr;
std::cout << "view found: " << std::boolalpha << found << std::endl;
view found: false

The complete source code of this example is available here.