outline_collapsed/
outline_collapsed.rs

1use edit_xlsx::{WorkSheetCol, Format, FormatColor, WorkSheetRow, Workbook, WorkbookResult, WorkSheet, Write};
2
3fn main() -> WorkbookResult<()> {
4    // Create a new workbook
5    let mut workbook = Workbook::new();
6    // Add a general format
7    let bold = Format::default().set_bold();
8
9    //
10    // Example 1: A worksheet with outlined rows. It also includes SUBTOTAL()
11    // 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    let worksheet1 = workbook.add_worksheet_by_name("Outlined Rows")?;
15    worksheet1.set_row_level(2, 2)?;
16    worksheet1.set_row_level(3, 2)?;
17    worksheet1.set_row_level(4, 2)?;
18    worksheet1.set_row_level(5, 2)?;
19    worksheet1.set_row_level(6, 1)?;
20    worksheet1.set_row_level(7, 2)?;
21    worksheet1.set_row_level(8, 2)?;
22    worksheet1.set_row_level(9, 2)?;
23    worksheet1.set_row_level(10, 2)?;
24    worksheet1.set_row_level(11, 1)?;
25    create_sub_totals(worksheet1)?;
26
27    //
28    // Example 2: Create a worksheet with collapsed outlined rows.
29    // This is the same as the example 1  except that the all rows are collapsed.
30    // Note: We need to indicate the rows that contains the collapsed symbol '+'
31    // with the optional parameter, 'collapsed'.
32    //
33    let worksheet2 = workbook.add_worksheet_by_name("Collapsed Rows 1")?;
34    worksheet2.set_row_level(2, 2)?;
35    worksheet2.hide_row(2)?;
36    worksheet2.set_row_level(3, 2)?;
37    worksheet2.hide_row(3)?;
38    worksheet2.set_row_level(4, 2)?;
39    worksheet2.hide_row(4)?;
40    worksheet2.set_row_level(5, 2)?;
41    worksheet2.hide_row(5)?;
42    worksheet2.set_row_level(6, 1)?;
43    worksheet2.hide_row(6)?;
44    worksheet2.set_row_level(7, 2)?;
45    worksheet2.hide_row(7)?;
46    worksheet2.set_row_level(8, 2)?;
47    worksheet2.hide_row(8)?;
48    worksheet2.set_row_level(9, 2)?;
49    worksheet2.hide_row(9)?;
50    worksheet2.set_row_level(10, 2)?;
51    worksheet2.hide_row(10)?;
52    worksheet2.set_row_level(11, 1)?;
53    worksheet2.hide_row(11)?;
54
55    worksheet2.collapse_row(12)?;
56
57    // Write the sub-total data that is common to the row examples.
58    create_sub_totals(worksheet2)?;
59
60
61    //
62    // Example 3: Create a worksheet with collapsed outlined rows.
63    // Same as the example 1  except that the two sub-totals are collapsed.
64    //
65    let worksheet3 = workbook.add_worksheet_by_name("Collapsed Rows 2")?;
66    worksheet3.set_row_level(2, 2)?;
67    worksheet3.hide_row(2)?;
68    worksheet3.set_row_level(3, 2)?;
69    worksheet3.hide_row(3)?;
70    worksheet3.set_row_level(4, 2)?;
71    worksheet3.hide_row(4)?;
72    worksheet3.set_row_level(5, 2)?;
73    worksheet3.hide_row(5)?;
74    worksheet3.set_row_level(6, 1)?;
75    worksheet3.collapse_row(6)?;
76    worksheet3.set_row_level(7, 2)?;
77    worksheet3.hide_row(7)?;
78    worksheet3.set_row_level(8, 2)?;
79    worksheet3.hide_row(8)?;
80    worksheet3.set_row_level(9, 2)?;
81    worksheet3.hide_row(9)?;
82    worksheet3.set_row_level(10, 2)?;
83    worksheet3.hide_row(10)?;
84    worksheet3.set_row_level(11, 1)?;
85    worksheet3.collapse_row(11)?;
86    // Write the sub-total data that is common to the row examples.
87    create_sub_totals(worksheet3)?;
88
89
90    //
91    // Example 4: Create a worksheet with outlined rows.
92    // Same as the example 1  except that the two sub-totals are collapsed.
93    //
94    let worksheet4 = workbook.add_worksheet_by_name("Collapsed Rows 3")?;
95    worksheet4.set_row_level(2, 2)?;
96    worksheet4.hide_row(2)?;
97    worksheet4.set_row_level(3, 2)?;
98    worksheet4.hide_row(3)?;
99    worksheet4.set_row_level(4, 2)?;
100    worksheet4.hide_row(4)?;
101    worksheet4.set_row_level(5, 2)?;
102    worksheet4.hide_row(5)?;
103    worksheet4.set_row_level(6, 1)?;
104    worksheet4.hide_row(6)?;
105    worksheet4.collapse_row(6)?;
106    worksheet4.set_row_level(7, 2)?;
107    worksheet4.hide_row(7)?;
108    worksheet4.set_row_level(8, 2)?;
109    worksheet4.hide_row(8)?;
110    worksheet4.set_row_level(9, 2)?;
111    worksheet4.hide_row(9)?;
112    worksheet4.set_row_level(10, 2)?;
113    worksheet4.hide_row(10)?;
114    worksheet4.set_row_level(11, 1)?;
115    worksheet4.hide_row(11)?;
116    worksheet4.collapse_row(11)?;
117    worksheet4.collapse_row(12)?;
118    // Write the sub-total data that is common to the row examples.
119    create_sub_totals(worksheet4)?;
120
121
122
123    //
124    // Example 5: Create a worksheet with outlined columns.
125    //
126    let worksheet5 = workbook.add_worksheet_by_name("Outline Columns")?;
127    worksheet5.set_row_height_with_format(1, 15.0, &bold)?;
128    worksheet5.write_row("A1", &["Month", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Total"])?;
129    worksheet5.set_columns_width_with_format("A:A", 10.0, &bold)?;
130    worksheet5.set_columns_width("B:G", 5.0)?;
131    worksheet5.set_columns_level("B:G", 1)?;
132    worksheet5.set_columns_width("H:H", 10.0)?;
133    worksheet5.write_column("A2", &["North", "South", "East", "East"])?;
134    worksheet5.write_row("B2", &[50, 20, 15, 25, 65, 80])?;
135    worksheet5.write_row("B3", &[10, 20, 30, 50, 50, 50])?;
136    worksheet5.write_row("B4", &[45, 75, 50, 15, 75, 100])?;
137    worksheet5.write_row("B5", &[15, 15, 55, 35, 20, 50])?;
138    worksheet5.write_formula("H2", "=SUM(B2:G2)")?;
139    worksheet5.write_formula("H3", "=SUM(B3:G3)")?;
140    worksheet5.write_formula("H4", "=SUM(B4:G4)")?;
141    worksheet5.write_formula("H5", "=SUM(B5:G5)")?;
142    worksheet5.write_formula_with_format("H6", "=SUM(H2:H5)", &bold)?;
143
144
145
146    //
147    // Example 6: Create a worksheet with collapsed outlined columns.
148    // This is the same as the previous example except with collapsed columns.
149    //
150    let worksheet6 = workbook.add_worksheet_by_name("Collapsed Columns")?;
151    worksheet6.set_row_height_with_format(1, 15.0, &bold)?;
152    worksheet6.write_row("A1", &["Month", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Total"])?;
153    worksheet6.set_columns_width_with_format("A:H", 10.0, &bold)?;
154    worksheet6.set_columns_level("B:G", 1)?;
155    worksheet6.set_columns_level("C:F", 2)?;
156    worksheet6.set_columns_level("D:E", 3)?;
157    worksheet6.hide_columns("D:E")?;
158    worksheet6.collapse_columns("D:E")?;
159    worksheet6.write_column("A2", &["North", "South", "East", "East"])?;
160    worksheet6.write_row("B2", &[50, 20, 15, 25, 65, 80])?;
161    worksheet6.write_row("B3", &[10, 20, 30, 50, 50, 50])?;
162    worksheet6.write_row("B4", &[45, 75, 50, 15, 75, 100])?;
163    worksheet6.write_row("B5", &[15, 15, 55, 35, 20, 50])?;
164    worksheet6.write_formula("H2", "=SUM(B2:G2)")?;
165    worksheet6.write_formula("H3", "=SUM(B3:G3)")?;
166    worksheet6.write_formula("H4", "=SUM(B4:G4)")?;
167    worksheet6.write_formula("H5", "=SUM(B5:G5)")?;
168    worksheet6.write_formula_with_format("H6", "=SUM(H2:H5)", &bold)?;
169
170
171    workbook.save_as("examples/outline_collapsed.xlsx")?;
172    Ok(())
173}
174
175fn create_sub_totals(worksheet: &mut WorkSheet) -> WorkbookResult<()> {
176    // Add a general format
177    let bold = Format::default().set_bold();
178
179    // Add the data, labels and formulas
180    worksheet.write_with_format("A1", "Region", &bold)?;
181    worksheet.write("A2", "North")?;
182    worksheet.write("A3", "North")?;
183    worksheet.write("A4", "North")?;
184    worksheet.write("A5", "North")?;
185    worksheet.write_with_format("A6", "North Total", &bold)?;
186
187    worksheet.write_with_format("B1", "Sales", &bold)?;
188    worksheet.write("B2", 1000)?;
189    worksheet.write("B3", 1200)?;
190    worksheet.write("B4", 900)?;
191    worksheet.write("B5", 1200)?;
192    worksheet.write_formula_with_format("B6", "=SUBTOTAL(9,B2:B5)", &bold)?;
193    worksheet.write("A7", "South")?;
194    worksheet.write("A8", "South")?;
195    worksheet.write("A9", "South")?;
196    worksheet.write("A10", "South")?;
197    worksheet.write_with_format("A11", "South Total", &bold)?;
198    worksheet.write("B7", 400)?;
199    worksheet.write("B8", 600)?;
200    worksheet.write("B9", 500)?;
201    worksheet.write("B10", 600)?;
202    worksheet.write_formula_with_format("B11", "=SUBTOTAL(9,B7:B10)", &bold)?;
203    worksheet.write_with_format("A12", "Grand Total", &bold)?;
204    worksheet.write_formula_with_format("B12", "=SUBTOTAL(9,B2:B10)", &bold)?;
205    Ok(())
206}
outline_collapsed/outline_collapsed.rs

outline_collapsed/
outline_collapsed.rs
