xlsx_tables/
xlsx_tables.rs

1//! Excel structured tables: a plain table, a table with a calculated
2//! column and a totals row, and a table with a custom named style and a
3//! remembered sort order. One tab per feature.
4//!
5//! Run with: `cargo run -p office-toolkit --example xlsx_tables`
6
7use std::path::{Path, PathBuf};
8
9use office_toolkit::SaveToFile;
10use office_toolkit::excel::{
11    Cell, CellFormat, ExcelTable, Sheet, SortCondition, SortState, TableColumn, TableStyle,
12    TableStyleElement, TableStyleElementType, TotalsRowFunction,
13};
14use office_toolkit::prelude::*;
15
16fn main() -> office_toolkit::Result<()> {
17    let path = output_path("xlsx_tables.xlsx");
18
19    let plain_sheet = Sheet::new("Plain table")
20        .with_row(Row::new().with_text("Name").with_text("Score"))
21        .with_row(Row::new().with_text("Alice").with_number(92.0))
22        .with_row(Row::new().with_text("Bob").with_number(87.0))
23        .with_table(ExcelTable::new(
24            "ScoresTable",
25            "A1:B3",
26            vec!["Name", "Score"],
27        ));
28
29    let calculated_sheet = Sheet::new("Calculated and totals")
30        .with_row(
31            Row::new()
32                .with_text("Item")
33                .with_text("Qty")
34                .with_text("Unit price")
35                .with_text("Line total"),
36        )
37        .with_row(
38            Row::new()
39                .with_text("Widget")
40                .with_number(3.0)
41                .with_number(9.99)
42                .with_cell(Cell::formula("B2*C2").with_cached_value(29.97)),
43        )
44        .with_row(
45            Row::new()
46                .with_text("Gadget")
47                .with_number(2.0)
48                .with_number(19.99)
49                .with_cell(Cell::formula("B3*C3").with_cached_value(39.98)),
50        )
51        .with_row(Row::new().with_cell(Cell::text("Total")))
52        .with_table(
53            ExcelTable::with_columns(
54                "OrdersTable",
55                "A1:D4",
56                vec![
57                    TableColumn::new("Item"),
58                    TableColumn::new("Qty").with_totals_row_function(TotalsRowFunction::Sum),
59                    TableColumn::new("Unit price"),
60                    TableColumn::new("Line total")
61                        .with_calculated_formula(
62                            "OrdersTable[[#This Row],[Qty]]*OrdersTable[[#This Row],[Unit price]]",
63                        )
64                        .with_totals_row_function(TotalsRowFunction::Sum),
65                ],
66            )
67            .with_totals_row(true),
68        );
69
70    let styled_sheet = Sheet::new("Custom style and sort")
71        .with_row(Row::new().with_text("City").with_text("Population"))
72        .with_row(Row::new().with_text("Springfield").with_number(120_000.0))
73        .with_row(Row::new().with_text("Shelbyville").with_number(95_000.0))
74        .with_table(
75            ExcelTable::new("CitiesTable", "A1:B3", vec!["City", "Population"])
76                .with_table_style_name("CustomTableStyle")
77                .with_sort_state(
78                    SortState::new("A2:B3")
79                        .with_condition(SortCondition::new("B2:B3").with_descending(true)),
80                ),
81        );
82
83    let workbook = Workbook::new()
84        .with_table_style(
85            TableStyle::new("CustomTableStyle")
86                .with_element(TableStyleElement::new(
87                    TableStyleElementType::HeaderRow,
88                    CellFormat::new()
89                        .with_bold(true)
90                        .with_fill_color("FF2E74B5")
91                        .with_font_color("FFFFFFFF"),
92                ))
93                .with_element(TableStyleElement::new(
94                    TableStyleElementType::FirstRowStripe,
95                    CellFormat::new().with_fill_color("FFF2F2F2"),
96                )),
97        )
98        .with_sheet(plain_sheet)
99        .with_sheet(calculated_sheet)
100        .with_sheet(styled_sheet);
101
102    workbook.save_to_file(&path)?;
103    println!("Wrote {}", path.display());
104    Ok(())
105}
106
107/// Every example in this directory writes its output under
108/// `tests-data/output/`, resolved relative to this crate's own manifest so
109/// it works no matter what directory `cargo run` was invoked from.
110fn output_path(filename: &str) -> PathBuf {
111    Path::new(env!("CARGO_MANIFEST_DIR"))
112        .join("../../tests-data/output")
113        .join(filename)
114}
xlsx_tables/xlsx_tables.rs

xlsx_tables/
xlsx_tables.rs
