outline/
outline.rs

1use edit_xlsx::{WorkSheetCol, Format, WorkSheetRow, Workbook, WorkbookResult, Write};
2
3fn main() -> WorkbookResult<()> {
4    // Create a new workbook
5    let mut workbook = Workbook::new();
6    let worksheet1 = workbook.add_worksheet_by_name("Outlined Rows")?;
7    // Add a general format
8    let bold = Format::default().set_bold();
9
10    //
11    // Example 1: A worksheet with outlined rows. It also includes SUBTOTAL()?;    // functions so that it looks like the type of automatic outlines that are
12    // generated when you use the Excel Data->SubTotals menu item.
13    //
14    // For outlines the important parameters are 'level' and 'hidden'. Rows with
15    // the same 'level' are grouped together. The group will be collapsed if
16    // 'hidden' is enabled. The parameters 'height' and 'cell_format' are assigned
17    // default values if they are None.
18    //
19    worksheet1.set_row_level(2, 2)?;
20    worksheet1.set_row_level(3, 2)?;
21    worksheet1.set_row_level(4, 2)?;
22    worksheet1.set_row_level(5, 2)?;
23    worksheet1.set_row_level(6, 1)?;
24    worksheet1.set_row_level(7, 2)?;
25    worksheet1.set_row_level(8, 2)?;
26    worksheet1.set_row_level(9, 2)?;
27    worksheet1.set_row_level(10, 2)?;
28    worksheet1.set_row_level(11, 1)?;
29
30    // Adjust the column width for clarity
31    worksheet1.set_columns_width("A:A", 20.0)?;
32
33    // Add the data, labels and formulas
34    worksheet1.write_with_format("A1", "Region", &bold)?;
35    worksheet1.write("A2", "North")?;
36    worksheet1.write("A3", "North")?;
37    worksheet1.write("A4", "North")?;
38    worksheet1.write("A5", "North")?;
39    worksheet1.write_with_format("A6", "North Total", &bold)?;
40
41    worksheet1.write_with_format("B1", "Sales", &bold)?;
42    worksheet1.write("B2", 1000)?;
43    worksheet1.write("B3", 1200)?;
44    worksheet1.write("B4", 900)?;
45    worksheet1.write("B5", 1200)?;
46    worksheet1.write_formula_with_format("B6", "=SUBTOTAL(9,B2:B5)", &bold)?;
47    worksheet1.write("A7", "South")?;
48    worksheet1.write("A8", "South")?;
49    worksheet1.write("A9", "South")?;
50    worksheet1.write("A10", "South")?;
51    worksheet1.write_with_format("A11", "South Total", &bold)?;
52    worksheet1.write("B7", 400)?;
53    worksheet1.write("B8", 600)?;
54    worksheet1.write("B9", 500)?;
55    worksheet1.write("B10", 600)?;
56    worksheet1.write_formula_with_format("B11", "=SUBTOTAL(9,B7:B10)", &bold)?;
57    worksheet1.write_with_format("A12", "Grand Total", &bold)?;
58    worksheet1.write_formula_with_format("B12", "=SUBTOTAL(9,B2:B10)", &bold)?;
59
60
61    //
62    // Example 2: A worksheet with outlined rows. This is the same as the
63    // previous example except that the rows are collapsed.
64    // Note: We need to indicate the rows that contains the collapsed symbol '+'
65    // with the optional parameter, 'collapsed'. The group will be then be
66    // collapsed if 'hidden' is True.
67    //
68    let worksheet2 = workbook.add_worksheet_by_name("Collapsed Rows")?;
69    worksheet2.set_row_level(2, 2)?;
70    worksheet2.hide_row(2)?;
71    worksheet2.set_row_level(3, 2)?;
72    worksheet2.hide_row(3)?;
73    worksheet2.set_row_level(4, 2)?;
74    worksheet2.hide_row(4)?;
75    worksheet2.set_row_level(5, 2)?;
76    worksheet2.hide_row(5)?;
77    worksheet2.set_row_level(6, 1)?;
78    worksheet2.hide_row(6)?;
79    worksheet2.set_row_level(7, 2)?;
80    worksheet2.hide_row(7)?;
81    worksheet2.set_row_level(8, 2)?;
82    worksheet2.hide_row(8)?;
83    worksheet2.set_row_level(9, 2)?;
84    worksheet2.hide_row(9)?;
85    worksheet2.set_row_level(10, 2)?;
86    worksheet2.hide_row(10)?;
87    worksheet2.set_row_level(11, 1)?;
88    worksheet2.hide_row(11)?;
89    worksheet2.collapse_row(12)?;
90
91    // Adjust the column width for clarity
92    worksheet2.set_columns_width("A:A", 20.0)?;
93
94    // Add the data, labels and formulas
95    worksheet2.write_with_format("A1", "Region", &bold)?;
96    worksheet2.write("A2", "North")?;
97    worksheet2.write("A3", "North")?;
98    worksheet2.write("A4", "North")?;
99    worksheet2.write("A5", "North")?;
100    worksheet2.write_with_format("A6", "North Total", &bold)?;
101
102    worksheet2.write_with_format("B1", "Sales", &bold)?;
103    worksheet2.write("B2", 1000)?;
104    worksheet2.write("B3", 1200)?;
105    worksheet2.write("B4", 900)?;
106    worksheet2.write("B5", 1200)?;
107    worksheet2.write_formula_with_format("B6", "=SUBTOTAL(9,B2:B5)", &bold)?;
108    worksheet2.write("A7", "South")?;
109    worksheet2.write("A8", "South")?;
110    worksheet2.write("A9", "South")?;
111    worksheet2.write("A10", "South")?;
112    worksheet2.write_with_format("A11", "South Total", &bold)?;
113    worksheet2.write("B7", 400)?;
114    worksheet2.write("B8", 600)?;
115    worksheet2.write("B9", 500)?;
116    worksheet2.write("B10", 600)?;
117    worksheet2.write_formula_with_format("B11", "=SUBTOTAL(9,B7:B10)", &bold)?;
118    worksheet2.write_with_format("A12", "Grand Total", &bold)?;
119    worksheet2.write_formula_with_format("B12", "=SUBTOTAL(9,B2:B10)", &bold)?;
120
121    //
122    // Example 3: Create a worksheet with outlined columns.
123    //
124    let worksheet3 = workbook.add_worksheet_by_name("Outline Columns")?;
125    // Add bold format to the first row.
126    worksheet3.set_row_height_with_format(1, 15.0, &bold)?;
127    worksheet3.write_row("A1", &["Month", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Total"])?;
128    worksheet3.set_columns_width_with_format("A:A", 10.0, &bold)?;
129    worksheet3.set_columns_width("B:G", 10.0)?;
130    worksheet3.set_columns_level("B:G", 1)?;
131    worksheet3.set_columns_width("H:H", 10.0)?;
132    worksheet3.write_column("A2", &["North", "South", "East", "East"])?;
133    worksheet3.write_row("B2", &[50, 20, 15, 25, 65, 80])?;
134    worksheet3.write_row("B3", &[10, 20, 30, 50, 50, 50])?;
135    worksheet3.write_row("B4", &[45, 75, 50, 15, 75, 100])?;
136    worksheet3.write_row("B5", &[15, 15, 55, 35, 20, 50])?;
137    worksheet3.write_formula("H2", "=SUM(B2:G2)")?;
138    worksheet3.write_formula("H3", "=SUM(B3:G3)")?;
139    worksheet3.write_formula("H4", "=SUM(B4:G4)")?;
140    worksheet3.write_formula("H5", "=SUM(B5:G5)")?;
141    worksheet3.write_formula_with_format("H6", "=SUM(H2:H5)", &bold)?;
142
143    //
144    // Example 4: Show all possible outline levels.
145    //
146    let levels = [
147        "Level 1",
148        "Level 2",
149        "Level 3",
150        "Level 4",
151        "Level 5",
152        "Level 6",
153        "Level 7",
154        "Level 6",
155        "Level 5",
156        "Level 4",
157        "Level 3",
158        "Level 2",
159        "Level 1",
160    ];
161    let worksheet4 = workbook.add_worksheet_by_name("Outline levels")?;
162    worksheet4.write_column("A1", &levels)?;
163
164    worksheet4.set_row_level(1, 1)?;
165    worksheet4.set_row_level(2, 2)?;
166    worksheet4.set_row_level(3, 3)?;
167    worksheet4.set_row_level(4, 4)?;
168    worksheet4.set_row_level(5, 5)?;
169    worksheet4.set_row_level(6, 6)?;
170    worksheet4.set_row_level(7, 7)?;
171    worksheet4.set_row_level(8, 6)?;
172    worksheet4.set_row_level(9, 5)?;
173    worksheet4.set_row_level(10, 4)?;
174    worksheet4.set_row_level(11, 3)?;
175    worksheet4.set_row_level(12, 2)?;
176    worksheet4.set_row_level(13, 1)?;
177
178    workbook.save_as("examples/outline.xlsx")?;
179    Ok(())
180}
outline/outline.rs

outline/
outline.rs
