# Everything on a sheet that is not a cell
`Worksheet` keeps its grid in one map and everything else in public fields
beside it. Read them, push to them, drop them; the writer puts back what it
finds. This page covers the ones with rules of their own. Drawings - charts,
pictures, shapes - are in [the model page](model.md#charts).
## Merges
A merge is a rectangle, and the value belongs to its top left cell. Excel
keeps whatever sat in the covered cells and shows none of it.
```rust
use excelerate::{Range, at};
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut book = Spreadsheet::empty();
# let mut sheet = Worksheet::new("Sheet1")?;
sheet.merges.push(Range::new(
at!("A1"),
at!("C1"),
));
# book.add_sheet(sheet)?;
# Ok::<(), excelerate::Error>(())
```
Nothing stops two merges from overlapping here; Excel refuses to open a file
where they do.
## Comments
A note carries its own author. Excel keeps authors in a table and points each
comment at a row of it; writing rebuilds that table from what the comments say,
so there is no list to keep in step.
```rust
use excelerate::at;
use excelerate::model::{Comment, TextRun};
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut book = Spreadsheet::empty();
# let mut sheet = Worksheet::new("Sheet1")?;
sheet.comments.insert(
at!("B2"),
Comment {
author: "Отдел продаж".to_owned(),
text: vec![TextRun {
text: "Проверить курс на дату отгрузки".to_owned(),
font: None, // `Some(DiffFont { .. })` to change part of the font
}],
..Comment::default()
},
);
# book.add_sheet(sheet)?;
# Ok::<(), excelerate::Error>(())
```
The frame a note is drawn in is a shape in the sheet's VML part, which travels
as bytes. Before writing, `writer::comment::prepare` compares that part with
the model: a deleted note has its shape cut out, a new one gets a shape with
Excel's defaults, and a sheet with no VML at all gets a new part. Two things
about the frame are on the note itself: `visible` (pinned open rather than
shown on hover) and `size` in points (`None` is Excel's 108 by 59.25). A frame
that says otherwise is changed in place: its style, `<x:Visible/>`, and the far
corner of its anchor, which is what Excel places it by and which is worked out
from the widths of the columns and heights of the rows it covers. A note moved
through the model starts over at the default place; a note moved by a grid
edit keeps its frame.
## Tables
A table is a named range with a header row, and formulas refer to it by name:
`Sales[Amount]` follows the table when rows are inserted into it. The part is
modelled rather than carried, which is what makes that work - see
[Formulas](formulas.md) for the reference syntax.
```rust
# use excelerate::model::{Spreadsheet, Worksheet};
# let book = Spreadsheet::empty();
# let sheet = Worksheet::new("Sheet1")?;
for table in &sheet.tables {
println!(
"{} over {} - {} columns, {} header rows",
table.display_name,
table.range,
table.columns.len(),
table.header_row_count.unwrap_or(1),
);
}
# Ok::<(), excelerate::Error>(())
```
`header_row_count` and `totals_row_count` are `Option<u32>` rather than `u32`
for the same reason the protection flags are `Option<bool>`: each attribute has
its own default in the schema, and writing out an attribute the file never had
puts a decision in it that nobody made.
## Protection
`SheetProtection` holds sixteen separate refusals, each an `Option<bool>`.
`Some(false)` is a decision the user made; `None` is the attribute being absent,
and the defaults differ between flags, so collapsing them into `bool` would
write things the file never said.
```rust
use excelerate::model::{PasswordHash, SheetProtection};
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut book = Spreadsheet::empty();
# let mut sheet = Worksheet::new("Sheet1")?;
sheet.protection = SheetProtection {
sheet: Some(true),
format_cells: Some(false),
..SheetProtection::default()
};
// SHA-512, a random 16-byte salt, 100,000 spins - what Excel writes.
sheet.protection.password = PasswordHash::new("проба");
# book.add_sheet(sheet)?;
# Ok::<(), excelerate::Error>(())
```
None of this encrypts anything. It is a hash Excel checks before letting the
user edit, and `PasswordHash::verify(password)` checks it the same way, against
SHA-512, SHA-384, SHA-256 or SHA-1. Excel 2019 accepted a sheet locked this
way and unlocked it with the same password.
`PasswordHash::iso(password, salt, spins)` takes your own salt, and
`PasswordHash::legacy(password)` writes the 16-bit verifier Excel 97 used.
## Autofilter
`AutoFilter` is a range and a list of columns; a column's `col_id` is an offset
from the first column of the range, not a column of the sheet. Reading a file
does not compute what the filter hides: a hidden row is already a property of
the row, Excel writes both, and in someone else's file the two are allowed to
disagree.
Setting a filter does compute it. `edit::filter_table` puts a criterion on a
column of a table and hides the data rows that the table's criteria reject,
showing the rest, as applying a filter in Excel does:
```rust
use excelerate::edit::filter_table;
use excelerate::model::autofilter::{ColumnFilter, CustomFilter, FilterOperator};
# let mut book = excelerate::reader::read("tests/fixtures/table.xlsx")?;
// Column 2 of the table "Sales" (from 0): keep what is over 100.
let over_100 = ColumnFilter::Custom {
and: false,
rules: vec![CustomFilter { operator: FilterOperator::GreaterThan, value: "100".into() }],
};
filter_table(&mut book, "Sales", 2, Some(over_100))?;
filter_table(&mut book, "Sales", 2, None)?; // and off again
# Ok::<(), excelerate::Error>(())
```
`Values` matches what the cell shows, without regard to case; `Custom`
compares numbers with numbers and text with text, `*` and `?` in an equality;
`Top10` keeps the extremes and records the cut-off as Excel does. `Dynamic`,
`Color` and `Icon` are stored but hide nothing: they need the clock, the
painted colours or the conditional formats.
```rust
# use excelerate::model::{Spreadsheet, Worksheet};
# let sheet = Worksheet::new("Sheet1")?;
if let Some(filter) = &sheet.auto_filter {
println!("{} with {} column rules", filter.range, filter.columns.len());
}
# Ok::<(), excelerate::Error>(())
```
A rule is one of four shapes rather than a single tagged list, because
`<filters>` and `<top10>` share no attributes: `Values` (literals, blanks, a
date group), `Custom` (one or two comparisons joined by and/or), `Dynamic`
(`today`, `aboveAverage` - a criterion Excel works out afresh), `Top10`,
`Color` (the fill or font colour, as an index into the differential styles)
and `Icon` (an icon of a conditional format's set).
`SortState` records the last sort Excel applied: the range, whether it ran
across columns, and the keys, each by value, colour or icon, ascending or not,
optionally by a custom list. It sits in three places - `AutoFilter::sort_state`
for a sort from the filter's drop-downs, `Worksheet::sort_state` for one from
Data > Sort, `Table::sort_state` for a table's - and follows its cells through
a grid edit. It is a record, not an instruction: the cells are already in that
order.
## Sparklines
`Worksheet::sparklines` holds the sheet's sparkline groups: the kind (line,
column, win/loss), which points are marked, the date axis, and the sparklines
themselves, each a formula for what it reads and the cell it is drawn in.
Colours and axis settings stay as the file wrote them. They live in the sheet's
`<extLst>`, and the writer replaces just their `<ext>` there, and only when the
groups changed; the extensions beside them travel byte for byte. A grid edit
moves the cell a sparkline sits in and rewrites what it reads on any sheet, and
so does renaming or removing a sheet.
```rust
use excelerate::at;
use excelerate::model::sparkline::{Sparkline, SparklineGroup, SparklineKind};
# use excelerate::model::Worksheet;
# let mut sheet = Worksheet::new("Sheet1")?;
let mut group = SparklineGroup::new(SparklineKind::Column);
group.high = true;
group.sparklines.push(Sparkline {
data: Some("Sheet1!B2:M2".to_owned()),
location: at!("N2"),
});
sheet.sparklines.push(group);
# Ok::<(), excelerate::Error>(())
```
## Data validation
```rust
use excelerate::model::{DataValidation, ValidationType};
use excelerate::{Range, at};
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut book = Spreadsheet::empty();
# let mut sheet = Worksheet::new("Sheet1")?;
sheet.data_validations.push(DataValidation {
sqref: vec![Range::new(at!("A2"), at!("A99"))],
kind: ValidationType::List,
formula1: "\"да,нет\"".to_owned(),
allow_blank: true,
..DataValidation::default()
});
# book.add_sheet(sheet)?;
# Ok::<(), excelerate::Error>(())
```
One field lies about itself and is kept as the file spells it: `show_drop_down`
suppresses the in-cell arrow when it is `true`. The attribute is named
`showDropDown`, Excel writes `1` to hide the arrow, and correcting the sense
here would flip every file that has it.
## Conditional formats
A rule carries a `DifferentialStyle`, whose every part is an `Option`. A `dxf`
says *what to replace*, so a whole `Font` in its place would impose a name and a
size the file never had.
## Hyperlinks
A link covers a range, not a cell, and points either outside the workbook or at
a place inside it:
```rust
use excelerate::model::{Hyperlink, LinkTarget};
use excelerate::{Range, at};
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut book = Spreadsheet::empty();
# let mut sheet = Worksheet::new("Sheet1")?;
let a1 = at!("A1");
sheet.hyperlinks.push(Hyperlink {
range: Range::new(a1, a1),
target: LinkTarget::Outside("https://example.org/report".to_owned()),
display: None,
tooltip: Some("Открыть отчёт".to_owned()),
});
# book.add_sheet(sheet)?;
# Ok::<(), excelerate::Error>(())
```
An outside link goes into the sheet's relationships when written; an inside one
(`LinkTarget::Inside`) is an address or a defined name and stays in the sheet.
## Print setup
`margins`, `page_setup`, `print_options`, `header_footer`, `row_breaks` and
`col_breaks` are read and written whole. The print area and the repeating rows
are not here: they are defined names on the workbook, `_xlnm.Print_Area` and
`_xlnm.Print_Titles`.
## The saved view
`SheetView` holds zoom, the view mode, the top left cell, the selection and the
frozen panes, plus the switches: formulas shown instead of values, outline
symbols, ruler, white space between pages, a locked window and a grid colour
other than the default. `Spreadsheet::active_sheet` holds which tab opens, and
`Spreadsheet::workbook_view` the rest of the tab bar - the first tab shown, its
share of the window, whether tabs and scroll bars show. Without these a
rewritten file opens in a different state from the one it was saved in, which is
what they are read for.
## Sheet visibility
```rust
use excelerate::model::SheetVisibility;
# use excelerate::model::{Spreadsheet, Worksheet};
# let mut sheet = Worksheet::new("Sheet1")?;
sheet.visibility = SheetVisibility::VeryHidden; // not in the unhide dialog
# Ok::<(), excelerate::Error>(())
```
`Spreadsheet::sheets()` lists hidden sheets too. The index every other call
takes is the sheet's position in the workbook, and skipping hidden ones would
shift it.