dynamic_arrays/
dynamic_arrays.rs

1use edit_xlsx::{WorkSheetCol, Format, FormatColor, Workbook, WorkbookResult, WorkSheet, WorkSheetResult, Write};
2
3fn main() -> WorkbookResult<()> {
4    // Create a new workbook called simple.xls and add some worksheets.
5    let mut workbook = Workbook::new();
6    let header1 = Format::default()
7        .set_color(FormatColor::RGB(255, 255, 255))
8        .set_background_color(FormatColor::RGB(74, 196, 12));
9    let header2 = Format::default()
10        .set_color(FormatColor::RGB(255, 255, 255))
11        .set_background_color(FormatColor::RGB(40, 253, 3));
12
13    //
14    // Example of using the FILTER() function.
15    //
16    let worksheet1 = workbook.add_worksheet_by_name("Filter")?;
17    worksheet1.write_formula("F2", "_xlfn.FILTER(A1:D17,C1:C17=K2)")?;
18
19    // Write the data the function will work on.
20    worksheet1.write_with_format("K1", "Product", &header2)?;
21    worksheet1.write("K2", "Apple")?;
22    worksheet1.write_with_format("F1", "Region", &header2)?;
23    worksheet1.write_with_format("G1", "Sales Rep", &header2)?;
24    worksheet1.write_with_format("H1", "Product", &header2)?;
25    worksheet1.write_with_format("I1", "Units", &header2)?;
26
27    write_worksheet_data(worksheet1, &header1)?;
28    worksheet1.set_columns_width_pixels("E:E", 10.0)?;
29    worksheet1.set_columns_width_pixels("J:J", 10.0)?;
30
31    //
32    // Example of using the UNIQUE() function.
33    //
34    let worksheet2 = workbook.add_worksheet_by_name("Unique")?;
35    worksheet2.write_formula("F2", "_xlfn.UNIQUE(B2:B17)")?;
36
37    // A more complex example combining SORT and UNIQUE.
38    worksheet2.write_formula("H2", "_xlfn.SORT(_xlfn.UNIQUE(B2:B17))")?;
39
40    // Write the data the function will work on.
41    worksheet2.write_with_format("F1", "Sales Rep", &header2)?;
42    worksheet2.write_with_format("H1", "Sales Rep", &header2)?;
43
44    write_worksheet_data(worksheet2, &header1)?;
45    worksheet2.set_columns_width_pixels("E:E", 10.0)?;
46    worksheet2.set_columns_width_pixels("G:G", 10.0)?;
47
48    //
49    // Example of using the SORT() function.
50    //
51    let worksheet3 = workbook.add_worksheet_by_name("Sort")?;
52    worksheet3.write_formula("F2", "_xlfn.SORT(B2:B17)")?;
53
54    // A more complex example combining SORT and FILTER.
55    worksheet3.write_formula("H2", "_xlfn.SORT(_xlfn.FILTER(C2: D17, D2: D17 > 5000, \"\"), 2, 1)")?;
56
57    // Write the data the function will work on.
58    worksheet3.write_with_format("F1", "Sales Rep", &header2)?;
59    worksheet3.write_with_format("H1", "Product", &header2)?;
60    worksheet3.write_with_format("I1", "Units", &header2)?;
61
62    write_worksheet_data(worksheet3, &header1)?;
63    worksheet3.set_columns_width_pixels("E:E", 10.0)?;
64    worksheet3.set_columns_width_pixels("G:G", 10.0)?;
65
66
67    //
68    // Example of using the SORTBY() function.
69    //
70    let worksheet4 = workbook.add_worksheet_by_name("Sortby")?;
71    worksheet4.write_formula("D2", "_xlfn.SORTBY(A2:B9,B2:B9)")?;
72
73    // Write the data the function will work on.
74    worksheet4.write_with_format("A1", "Name", &header1)?;
75    worksheet4.write_with_format("B1", "Age", &header1)?;
76
77    worksheet4.write("A2", "Tom")?;
78    worksheet4.write("A3", "Fred")?;
79    worksheet4.write("A4", "Amy")?;
80    worksheet4.write("A5", "Sal")?;
81    worksheet4.write("A6", "Fritz")?;
82    worksheet4.write("A7", "Srivan")?;
83    worksheet4.write("A8", "Xi")?;
84    worksheet4.write("A9", "Hector")?;
85
86    worksheet4.write("B2", 52)?;
87    worksheet4.write("B3", 65)?;
88    worksheet4.write("B4", 22)?;
89    worksheet4.write("B5", 73)?;
90    worksheet4.write("B6", 19)?;
91    worksheet4.write("B7", 39)?;
92    worksheet4.write("B8", 19)?;
93    worksheet4.write("B9", 66)?;
94
95    worksheet4.write_with_format("D1", "Name", &header2)?;
96    worksheet4.write_with_format("E1", "Age", &header2)?;
97
98    worksheet4.set_columns_width_pixels("C:C", 10.0)?;
99
100    //
101    // Example of using the XLOOKUP() function.
102    //
103    let worksheet5 = workbook.add_worksheet_by_name("Xlookup")?;
104    worksheet5.write_formula("F1", "_xlfn.XLOOKUP(E1,A2:A9,C2:C9)")?;
105
106    // Write the data the function will work on.
107    worksheet5.write_with_format("A1", "Country", &header1)?;
108    worksheet5.write_with_format("B1", "Abr", &header1)?;
109    worksheet5.write_with_format("C1", "Prefix", &header1)?;
110
111    worksheet5.write("A2", "China")?;
112    worksheet5.write("A3", "India")?;
113    worksheet5.write("A4", "United States")?;
114    worksheet5.write("A5", "Indonesia")?;
115    worksheet5.write("A6", "Brazil")?;
116    worksheet5.write("A7", "Pakistan")?;
117    worksheet5.write("A8", "Nigeria")?;
118    worksheet5.write("A9", "Bangladesh")?;
119
120    worksheet5.write("B2", "CN")?;
121    worksheet5.write("B3", "IN")?;
122    worksheet5.write("B4", "US")?;
123    worksheet5.write("B5", "ID")?;
124    worksheet5.write("B6", "BR")?;
125    worksheet5.write("B7", "PK")?;
126    worksheet5.write("B8", "NG")?;
127    worksheet5.write("B9", "BD")?;
128
129    worksheet5.write("C2", 86)?;
130    worksheet5.write("C3", 91)?;
131    worksheet5.write("C4", 1)?;
132    worksheet5.write("C5", 62)?;
133    worksheet5.write("C6", 55)?;
134    worksheet5.write("C7", 92)?;
135    worksheet5.write("C8", 234)?;
136    worksheet5.write("C9", 880)?;
137
138    worksheet5.write_with_format("E1", "Brazil", &header2)?;
139
140    worksheet5.set_columns_width_pixels("A:A", 100.0)?;
141    worksheet5.set_columns_width_pixels("D:D", 10.0)?;
142
143    //
144    // Example of using the XMATCH() function.
145    //
146    let worksheet6 = workbook.add_worksheet_by_name("Xmatch")?;
147    worksheet6.write_formula("D2", "_xlfn.XMATCH(C2,A2:A6)")?;
148
149    // Write the data the function will work on.
150    worksheet6.write_with_format("A1", "Product", &header1)?;
151
152    worksheet6.write("A2", "Apple")?;
153    worksheet6.write("A3", "Grape")?;
154    worksheet6.write("A4", "Pear")?;
155    worksheet6.write("A5", "Banana")?;
156    worksheet6.write("A6", "Cherry")?;
157
158    worksheet6.write_with_format("C1", "Product", &header2)?;
159    worksheet6.write_with_format("D1", "Position", &header2)?;
160    worksheet6.write("C2", "Grape")?;
161
162    worksheet6.set_columns_width_pixels("B:B", 10.0)?;
163
164    //
165    // Example of using the RANDARRAY() function.
166    //
167    let worksheet7 = workbook.add_worksheet_by_name("Randarray")?;
168    worksheet7.write_dynamic_array_formula("A1", "_xlfn.RANDARRAY(5,3,1,100, TRUE)")?;
169
170    //
171    // Example of using the SEQUENCE() function.
172    //
173    let worksheet8 = workbook.add_worksheet_by_name("Sequence")?;
174    worksheet8.write_dynamic_array_formula("A1", "_xlfn.SEQUENCE(4,5)")?;
175
176    //
177    // Example of using the Spill range operator.
178    //
179    let worksheet9 = workbook.add_worksheet_by_name("Spill ranges")?;
180    worksheet9.write_dynamic_array_formula("H2", "_xlfn.ANCHORARRAY(F2)")?;
181
182    worksheet9.write_dynamic_array_formula("J2", "_xlfn.COUNTA(_xlfn.ANCHORARRAY(F2))")?;
183
184    // Write the data the to work on.
185    worksheet9.write_dynamic_array_formula("F2", "_xlfn.UNIQUE(B2:B17)")?;
186    worksheet9.write_with_format("F1", "Unique", &header2)?;
187    worksheet9.write_with_format("H1", "Spill", &header2)?;
188    worksheet9.write_with_format("J1", "Spill", &header2)?;
189
190    write_worksheet_data(worksheet9, &header1)?;
191    worksheet9.set_columns_width_pixels("E:E", 10.0)?;
192    worksheet9.set_columns_width_pixels("G:G", 10.0)?;
193    worksheet9.set_columns_width_pixels("I:I", 10.0)?;
194
195    //
196    // Example of using dynamic ranges with older Excel functions.
197    //
198    let worksheet10 = workbook.add_worksheet_by_name("Older functions")?;
199    worksheet10.write_dynamic_array_formula("B1", "=LEN(A1:A3)")?;
200
201    // Write the data the to work on.
202    worksheet10.write("A1", "Foo")?;
203    worksheet10.write("A2", "Food")?;
204    worksheet10.write("A3", "Frood")?;
205
206
207    workbook.save_as("examples/dynamic_arrays.xlsx")?;
208    Ok(())
209}
210
211fn write_worksheet_data(worksheet: &mut WorkSheet, header: &Format) -> WorkSheetResult<()> {
212    worksheet.write_with_format("A1", "Region", &header)?;
213    worksheet.write_with_format("B1", "Sales Rep", &header)?;
214    worksheet.write_with_format("C1", "Product", &header)?;
215    worksheet.write_with_format("D1", "Units", &header)?;
216    let data = [
217        ["East", "Tom", "Apple"],
218        ["West", "Fred", "Grape"],
219        ["North", "Amy", "Pear"],
220        ["South", "Sal", "Banana"],
221        ["East", "Fritz", "Apple"],
222        ["West", "Sravan", "Grape"],
223        ["North", "Xi", "Pear"],
224        ["South", "Hector", "Banana"],
225        ["East", "Tom", "Banana"],
226        ["West", "Fred", "Pear"],
227        ["North", "Amy", "Grape"],
228        ["South", "Sal", "Apple"],
229        ["East", "Fritz", "Banana"],
230        ["West", "Sravan", "Pear"],
231        ["North", "Xi", "Grape"],
232        ["South", "Hector", "Apple"],
233    ];
234    let mut row_num = 2;
235    for data in data {
236        worksheet.write_row((row_num, 1), &data)?;
237        row_num += 1;
238    }
239    let units = [6380, 5619, 4565, 5323, 4394, 7195, 5231, 2427, 4213, 3239, 6520, 1310, 6274, 4894, 7580, 9814];
240    worksheet.write_column("D2", &units)?;
241    Ok(())
242}
dynamic_arrays/dynamic_arrays.rs

dynamic_arrays/
dynamic_arrays.rs
