1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * This Source Code Form is subject to the terms of the Mozilla Public
4 * License, v. 2.0. If a copy of the MPL was not distributed with this
5 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
6 */
7
8 #include "orcus/orcus_xlsx.hpp"
9 #include "orcus/pstring.hpp"
10 #include "orcus/global.hpp"
11 #include "orcus/stream.hpp"
12 #include "orcus/config.hpp"
13 #include "orcus/spreadsheet/factory.hpp"
14 #include "orcus/spreadsheet/document.hpp"
15 #include "orcus/spreadsheet/view.hpp"
16 #include "orcus/spreadsheet/sheet.hpp"
17 #include "orcus/spreadsheet/auto_filter.hpp"
18 #include "orcus/spreadsheet/pivot.hpp"
19 #include "orcus/spreadsheet/styles.hpp"
20
21 #include <cstdlib>
22 #include <cassert>
23 #include <string>
24 #include <sstream>
25 #include <set>
26 #include <cmath>
27 #include <vector>
28 #include <iostream>
29
30 #include <ixion/model_context.hpp>
31 #include <ixion/address.hpp>
32 #include <ixion/formula_name_resolver.hpp>
33
34 using namespace orcus;
35 using namespace orcus::spreadsheet;
36 namespace ss = orcus::spreadsheet;
37 using namespace std;
38
39 namespace {
40
41 config test_config(format_t::xlsx);
42
load_doc(const pstring & path)43 std::unique_ptr<spreadsheet::document> load_doc(const pstring& path)
44 {
45 spreadsheet::range_size_t ss{1048576, 16384};
46 std::unique_ptr<spreadsheet::document> doc = orcus::make_unique<spreadsheet::document>(ss);
47 spreadsheet::import_factory factory(*doc);
48 orcus_xlsx app(&factory);
49 app.read_file(path.str());
50 app.set_config(test_config);
51 doc->recalc_formula_cells();
52
53 return doc;
54 }
55
56 /**
57 * Convenience function to retrieve a pivot cache instance from textural
58 * sheet name and range name.
59 */
get_pivot_cache(const pivot_collection & pc,const pstring & sheet_name,const pstring & range_name)60 const pivot_cache* get_pivot_cache(
61 const pivot_collection& pc, const pstring& sheet_name, const pstring& range_name)
62 {
63 std::unique_ptr<ixion::formula_name_resolver> resolver =
64 ixion::formula_name_resolver::get(
65 ixion::formula_name_resolver_t::excel_a1, nullptr);
66
67 if (!resolver)
68 return nullptr;
69
70 ixion::abs_address_t origin(0,0,0);
71
72 ixion::formula_name_t fn =
73 resolver->resolve(range_name.get(), range_name.size(), origin);
74
75 if (fn.type != ixion::formula_name_t::range_reference)
76 return nullptr;
77
78 ixion::abs_range_t range = ixion::to_range(fn.range).to_abs(origin);
79 return pc.get_cache(sheet_name, range);
80 }
81
82 vector<const char*> dirs = {
83 SRCDIR"/test/xlsx/raw-values-1/",
84 SRCDIR"/test/xlsx/boolean-values/",
85 SRCDIR"/test/xlsx/empty-shared-strings/",
86 SRCDIR"/test/xlsx/formula-array-1/",
87 SRCDIR"/test/xlsx/formula-cells/",
88 SRCDIR"/test/xlsx/formula-shared/",
89 SRCDIR"/test/xlsx/named-expression/",
90 SRCDIR"/test/xlsx/named-expression-sheet-local/",
91 };
92
93 /**
94 * Semi-automated import test that goes through all specified directories,
95 * and in each directory, reads the input.xlsx file, dumps its output and
96 * checks it against the check.txt content.
97 */
test_xlsx_import()98 void test_xlsx_import()
99 {
100 for (const char* dir : dirs)
101 {
102 string path(dir);
103
104 // Read the input.xlsx document.
105 path.append("input.xlsx");
106 auto doc = load_doc(path);
107
108 // Dump the content of the model.
109 ostringstream os;
110 doc->dump_check(os);
111 string check = os.str();
112
113 // Check that against known control.
114 path = dir;
115 path.append("check.txt");
116 file_content control(path.data());
117
118 assert(!check.empty());
119 assert(!control.empty());
120
121 pstring s1(&check[0], check.size());
122 pstring s2 = control.str();
123 assert(s1.trim() == s2.trim());
124 }
125 }
126
test_xlsx_table_autofilter()127 void test_xlsx_table_autofilter()
128 {
129 string path(SRCDIR"/test/xlsx/table/autofilter.xlsx");
130 spreadsheet::range_size_t ss{1048576, 16384};
131 document doc{ss};
132 import_factory factory(doc);
133 orcus_xlsx app(&factory);
134 app.read_file(path.c_str());
135
136 const sheet* sh = doc.get_sheet(0);
137 assert(sh);
138 const auto_filter_t* af = sh->get_auto_filter_data();
139 assert(af);
140
141 // Autofilter is over B2:C11.
142 assert(af->range.first.column == 1);
143 assert(af->range.first.row == 1);
144 assert(af->range.last.column == 2);
145 assert(af->range.last.row == 10);
146
147 // Check the match values of the 1st column filter criterion.
148 auto_filter_t::columns_type::const_iterator it = af->columns.find(0);
149 assert(it != af->columns.end());
150
151 const auto_filter_column_t* afc = &it->second;
152 assert(afc->match_values.count("A") > 0);
153 assert(afc->match_values.count("C") > 0);
154
155 // And the 2nd column.
156 it = af->columns.find(1);
157 assert(it != af->columns.end());
158 afc = &it->second;
159 assert(afc->match_values.count("1") > 0);
160 }
161
test_xlsx_table()162 void test_xlsx_table()
163 {
164 string path(SRCDIR"/test/xlsx/table/table-1.xlsx");
165 document doc{{1048576, 16384}};
166 import_factory factory(doc);
167 orcus_xlsx app(&factory);
168 app.read_file(path.c_str());
169
170 pstring name("Table1");
171 const table_t* p = doc.get_table(name);
172 assert(p);
173 assert(p->identifier == 1);
174 assert(p->name == name);
175 assert(p->display_name == name);
176 assert(p->totals_row_count == 1);
177
178 // Table range is C3:D9.
179 ixion::abs_range_t range;
180 range.first.column = 2;
181 range.first.row = 2;
182 range.first.sheet = 0;
183 range.last.column = 3;
184 range.last.row = 8;
185 range.last.sheet = 0;
186 assert(p->range == range);
187
188 // Table1 has 2 table columns.
189 assert(p->columns.size() == 2);
190
191 const table_column_t* tcol = &p->columns[0];
192 assert(tcol);
193 assert(tcol->identifier == 1);
194 assert(tcol->name == "Category");
195 assert(tcol->totals_row_label == "Total");
196 assert(tcol->totals_row_function == totals_row_function_t::none);
197
198 tcol = &p->columns[1];
199 assert(tcol);
200 assert(tcol->identifier == 2);
201 assert(tcol->name == "Value");
202 assert(tcol->totals_row_label.empty());
203 assert(tcol->totals_row_function == totals_row_function_t::sum);
204
205 const auto_filter_t& filter = p->filter;
206
207 // Auto filter range is C3:D8.
208 range.last.row = 7;
209 assert(filter.range == range);
210
211 assert(filter.columns.size() == 1);
212 const auto_filter_column_t& afc = filter.columns.begin()->second;
213 assert(afc.match_values.size() == 4);
214 assert(afc.match_values.count("A") > 0);
215 assert(afc.match_values.count("C") > 0);
216 assert(afc.match_values.count("D") > 0);
217 assert(afc.match_values.count("E") > 0);
218
219 // Check table style.
220 const table_style_t& style = p->style;
221 assert(style.name == "TableStyleLight9");
222 assert(style.show_first_column == false);
223 assert(style.show_last_column == false);
224 assert(style.show_row_stripes == true);
225 assert(style.show_column_stripes == false);
226 }
227
test_xlsx_merged_cells()228 void test_xlsx_merged_cells()
229 {
230 string path(SRCDIR"/test/xlsx/merged-cells/simple.xlsx");
231
232 spreadsheet::range_size_t ss{1048576, 16384};
233 document doc{ss};
234 import_factory factory(doc);
235 orcus_xlsx app(&factory);
236 app.set_config(test_config);
237
238 app.read_file(path.c_str());
239
240 const sheet* sheet1 = doc.get_sheet("Sheet1");
241 assert(sheet1);
242
243 spreadsheet::range_t merge_range = sheet1->get_merge_cell_range(0, 1);
244 assert(merge_range.first.column == 1);
245 assert(merge_range.last.column == 2);
246 assert(merge_range.first.row == 0);
247 assert(merge_range.last.row == 0);
248
249 merge_range = sheet1->get_merge_cell_range(0, 3);
250 assert(merge_range.first.column == 3);
251 assert(merge_range.last.column == 5);
252 assert(merge_range.first.row == 0);
253 assert(merge_range.last.row == 0);
254
255 merge_range = sheet1->get_merge_cell_range(1, 0);
256 assert(merge_range.first.column == 0);
257 assert(merge_range.last.column == 0);
258 assert(merge_range.first.row == 1);
259 assert(merge_range.last.row == 2);
260
261 merge_range = sheet1->get_merge_cell_range(3, 0);
262 assert(merge_range.first.column == 0);
263 assert(merge_range.last.column == 0);
264 assert(merge_range.first.row == 3);
265 assert(merge_range.last.row == 5);
266
267 merge_range = sheet1->get_merge_cell_range(2, 2);
268 assert(merge_range.first.column == 2);
269 assert(merge_range.last.column == 5);
270 assert(merge_range.first.row == 2);
271 assert(merge_range.last.row == 5);
272 }
273
test_xlsx_date_time()274 void test_xlsx_date_time()
275 {
276 string path(SRCDIR"/test/xlsx/date-time/input.xlsx");
277
278 document doc{{1048576, 16384}};
279 import_factory factory(doc);
280 orcus_xlsx app(&factory);
281 app.set_config(test_config);
282
283 app.read_file(path.c_str());
284
285 const sheet* sheet1 = doc.get_sheet("Sheet1");
286 assert(sheet1);
287
288 // B1 contains date-only value.
289 date_time_t dt = sheet1->get_date_time(0, 1);
290 assert(dt == date_time_t(2016, 12, 14));
291
292 // B2 contains date-time value with no fraction seconds.
293 dt = sheet1->get_date_time(1, 1);
294 assert(dt == date_time_t(2002, 2, 3, 12, 34, 45));
295
296 // B3 contains date-time value with fraction second (1992-03-04 08:34:33.555)
297 dt = sheet1->get_date_time(2, 1);
298 assert(dt.year == 1992);
299 assert(dt.month == 3);
300 assert(dt.day == 4);
301 assert(dt.hour == 8);
302 assert(dt.minute == 34);
303 assert(std::floor(dt.second) == 33.0);
304
305 // Evalutate the fraction second as milliseconds.
306 double ms = dt.second * 1000.0;
307 ms -= std::floor(dt.second) * 1000.0;
308 ms = std::round(ms);
309 assert(ms == 555.0);
310 }
311
test_xlsx_background_fill()312 void test_xlsx_background_fill()
313 {
314 pstring path(SRCDIR"/test/xlsx/background-color/standard.xlsx");
315 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
316
317 spreadsheet::styles& styles = doc->get_styles();
318
319 spreadsheet::sheet* sh = doc->get_sheet(0);
320 assert(sh);
321
322 struct check
323 {
324 spreadsheet::row_t row;
325 spreadsheet::col_t col;
326 spreadsheet::fill_pattern_t pattern_type;
327 spreadsheet::color_t fg_color;
328 };
329
330 std::vector<check> checks =
331 {
332 { 1, 0, spreadsheet::fill_pattern_t::solid, { 255, 192, 0, 0 } }, // A2 - dark red
333 { 2, 0, spreadsheet::fill_pattern_t::solid, { 255, 255, 0, 0 } }, // A3 - red
334 { 3, 0, spreadsheet::fill_pattern_t::solid, { 255, 255, 192, 0 } }, // A4 - orange
335 { 4, 0, spreadsheet::fill_pattern_t::solid, { 255, 255, 255, 0 } }, // A5 - yellow
336 { 5, 0, spreadsheet::fill_pattern_t::solid, { 255, 146, 208, 80 } }, // A6 - light green
337 { 6, 0, spreadsheet::fill_pattern_t::solid, { 255, 0, 176, 80 } }, // A7 - green
338 { 7, 0, spreadsheet::fill_pattern_t::solid, { 255, 0, 176, 240 } }, // A8 - light blue
339 { 8, 0, spreadsheet::fill_pattern_t::solid, { 255, 0, 112, 192 } }, // A9 - blue
340 { 9, 0, spreadsheet::fill_pattern_t::solid, { 255, 0, 32, 96 } }, // A10 - dark blue
341 { 10, 0, spreadsheet::fill_pattern_t::solid, { 255, 112, 48, 160 } }, // A11 - purple
342 };
343
344 for (const check& c : checks)
345 {
346 size_t xf = sh->get_cell_format(c.row, c.col);
347
348 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
349 assert(cf);
350
351 const spreadsheet::fill_t* fill_data = styles.get_fill(cf->fill);
352 assert(fill_data);
353 assert(fill_data->pattern_type == c.pattern_type);
354 assert(fill_data->fg_color == c.fg_color);
355 }
356 }
357
test_xlsx_number_format()358 void test_xlsx_number_format()
359 {
360 pstring path(SRCDIR"/test/xlsx/number-format/date-time.xlsx");
361 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
362
363 spreadsheet::sheet* sh = doc->get_sheet(0);
364 assert(sh);
365
366 struct check
367 {
368 ss::row_t row;
369 ss::col_t col;
370 pstring expected;
371 };
372
373 std::vector<check> checks =
374 {
375 { 1, 1, "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy" },
376 { 2, 1, "[ENG][$-409]mmmm\\ d\\,\\ yyyy;@" },
377 { 3, 1, "m/d/yy;@" },
378 };
379
380 // TODO : We need to build our own internal number format code table for
381 // xlsx. Firstly, xlsx uses numFmtId explicitly to link between the xf and
382 // the format string and the ID's are not sequential. Secondly, there is a
383 // set of built-in format strings for ID < 164, and that information is not
384 // stored in the file.
385 #if 0
386 spreadsheet::styles& styles = doc->get_styles();
387
388 for (const check& c : checks)
389 {
390 size_t xf = sh->get_cell_format(c.row, c.col);
391 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
392 assert(cf);
393
394 const spreadsheet::number_format_t* nf = styles.get_number_format(cf->number_format);
395 assert(nf);
396 assert(nf->format_string == c.expected);
397 }
398 #endif
399 }
400
test_xlsx_text_alignment()401 void test_xlsx_text_alignment()
402 {
403 pstring path(SRCDIR"/test/xlsx/text-alignment/input.xlsx");
404 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
405
406 spreadsheet::styles& styles = doc->get_styles();
407
408 spreadsheet::sheet* sh = doc->get_sheet(0);
409 assert(sh);
410
411 struct check
412 {
413 spreadsheet::row_t row;
414 spreadsheet::col_t col;
415 bool apply_align;
416 spreadsheet::hor_alignment_t hor_align;
417 spreadsheet::ver_alignment_t ver_align;
418 };
419
420 std::vector<check> checks =
421 {
422 { 1, 2, false, spreadsheet::hor_alignment_t::unknown, spreadsheet::ver_alignment_t::unknown }, // C2
423 { 2, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::bottom }, // C3
424 { 3, 2, true, spreadsheet::hor_alignment_t::center, spreadsheet::ver_alignment_t::bottom }, // C4
425 { 4, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::bottom }, // C5
426 { 5, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::bottom }, // C6
427 { 6, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::bottom }, // C7
428 { 7, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::bottom }, // C8
429 { 8, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::bottom }, // C9
430 { 9, 2, true, spreadsheet::hor_alignment_t::unknown, spreadsheet::ver_alignment_t::middle }, // C10
431 { 10, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::middle }, // C11
432 { 11, 2, true, spreadsheet::hor_alignment_t::center, spreadsheet::ver_alignment_t::middle }, // C12
433 { 12, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::middle }, // C13
434 { 13, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::middle }, // C14
435 { 14, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::middle }, // C15
436 { 15, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::middle }, // C16
437 { 16, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::middle }, // C17
438 { 17, 2, true, spreadsheet::hor_alignment_t::unknown, spreadsheet::ver_alignment_t::top }, // C18
439 { 18, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::top }, // C19
440 { 19, 2, true, spreadsheet::hor_alignment_t::center, spreadsheet::ver_alignment_t::top }, // C20
441 { 20, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::top }, // C21
442 { 21, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::top }, // C22
443 { 22, 2, true, spreadsheet::hor_alignment_t::left, spreadsheet::ver_alignment_t::top }, // C23
444 { 23, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::top }, // C24
445 { 24, 2, true, spreadsheet::hor_alignment_t::right, spreadsheet::ver_alignment_t::top }, // C25
446 { 25, 2, true, spreadsheet::hor_alignment_t::unknown, spreadsheet::ver_alignment_t::justified }, // C26
447 { 26, 2, true, spreadsheet::hor_alignment_t::justified, spreadsheet::ver_alignment_t::bottom }, // C27
448 { 27, 2, true, spreadsheet::hor_alignment_t::distributed, spreadsheet::ver_alignment_t::distributed }, // C28
449 };
450
451 for (const check& c : checks)
452 {
453 size_t xf = sh->get_cell_format(c.row, c.col);
454
455 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
456 assert(cf);
457 assert(c.apply_align == cf->apply_alignment);
458
459 if (!cf->apply_alignment)
460 continue;
461
462 assert(c.hor_align == cf->hor_align);
463 assert(c.ver_align == cf->ver_align);
464 }
465 }
466
test_xlsx_cell_borders_single_cells()467 void test_xlsx_cell_borders_single_cells()
468 {
469 pstring path(SRCDIR"/test/xlsx/borders/single-cells.xlsx");
470 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
471
472 spreadsheet::styles& styles = doc->get_styles();
473
474 spreadsheet::sheet* sh = doc->get_sheet(0);
475 assert(sh);
476
477 struct check
478 {
479 spreadsheet::row_t row;
480 spreadsheet::col_t col;
481 spreadsheet::border_style_t style;
482 };
483
484 std::vector<check> checks =
485 {
486 { 3, 1, border_style_t::hair },
487 { 5, 1, border_style_t::dotted },
488 { 7, 1, border_style_t::dash_dot_dot },
489 { 9, 1, border_style_t::dash_dot },
490 { 11, 1, border_style_t::dashed },
491 { 13, 1, border_style_t::thin },
492 { 1, 3, border_style_t::medium_dash_dot_dot },
493 { 3, 3, border_style_t::slant_dash_dot },
494 { 5, 3, border_style_t::medium_dash_dot },
495 { 7, 3, border_style_t::medium_dashed },
496 { 9, 3, border_style_t::medium },
497 { 11, 3, border_style_t::thick },
498 { 13, 3, border_style_t::double_border },
499 };
500
501 for (const check& c : checks)
502 {
503 size_t xf = sh->get_cell_format(c.row, c.col);
504 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
505 assert(cf);
506 assert(cf->apply_border);
507
508 const spreadsheet::border_t* border = styles.get_border(cf->border);
509 assert(border);
510 assert(border->top.style == c.style);
511 assert(border->bottom.style == c.style);
512 assert(border->left.style == c.style);
513 assert(border->right.style == c.style);
514 }
515 }
516
test_xlsx_cell_borders_directions()517 void test_xlsx_cell_borders_directions()
518 {
519 pstring path(SRCDIR"/test/xlsx/borders/directions.xlsx");
520 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
521
522 spreadsheet::styles& styles = doc->get_styles();
523
524 spreadsheet::sheet* sh = doc->get_sheet(0);
525 assert(sh);
526
527 struct check
528 {
529 spreadsheet::row_t row;
530 spreadsheet::col_t col;
531 spreadsheet::border_direction_t dir;
532 };
533
534 std::vector<check> checks =
535 {
536 { 1, 1, spreadsheet::border_direction_t::top },
537 { 3, 1, spreadsheet::border_direction_t::left },
538 { 5, 1, spreadsheet::border_direction_t::right },
539 { 7, 1, spreadsheet::border_direction_t::bottom },
540 { 9, 1, spreadsheet::border_direction_t::diagonal_tl_br },
541 { 11, 1, spreadsheet::border_direction_t::diagonal_bl_tr },
542 { 13, 1, spreadsheet::border_direction_t::diagonal },
543 };
544
545 for (const check& c : checks)
546 {
547 size_t xf = sh->get_cell_format(c.row, c.col);
548 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
549 assert(cf);
550 assert(cf->apply_border);
551
552 const spreadsheet::border_t* border = styles.get_border(cf->border);
553 assert(border);
554
555 switch (c.dir)
556 {
557 case spreadsheet::border_direction_t::top:
558 assert(border->top.style == spreadsheet::border_style_t::thin);
559 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
560 assert(border->left.style == spreadsheet::border_style_t::unknown);
561 assert(border->right.style == spreadsheet::border_style_t::unknown);
562 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
563 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
564 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
565 break;
566 case spreadsheet::border_direction_t::left:
567 assert(border->top.style == spreadsheet::border_style_t::unknown);
568 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
569 assert(border->left.style == spreadsheet::border_style_t::thin);
570 assert(border->right.style == spreadsheet::border_style_t::unknown);
571 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
572 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
573 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
574 break;
575 case spreadsheet::border_direction_t::right:
576 assert(border->top.style == spreadsheet::border_style_t::unknown);
577 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
578 assert(border->left.style == spreadsheet::border_style_t::unknown);
579 assert(border->right.style == spreadsheet::border_style_t::thin);
580 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
581 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
582 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
583 break;
584 case spreadsheet::border_direction_t::bottom:
585 assert(border->top.style == spreadsheet::border_style_t::unknown);
586 assert(border->bottom.style == spreadsheet::border_style_t::thin);
587 assert(border->left.style == spreadsheet::border_style_t::unknown);
588 assert(border->right.style == spreadsheet::border_style_t::unknown);
589 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
590 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
591 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
592 break;
593 case spreadsheet::border_direction_t::diagonal:
594 assert(border->top.style == spreadsheet::border_style_t::unknown);
595 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
596 assert(border->left.style == spreadsheet::border_style_t::unknown);
597 assert(border->right.style == spreadsheet::border_style_t::unknown);
598 assert(border->diagonal.style == spreadsheet::border_style_t::thin);
599 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
600 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
601 break;
602 case spreadsheet::border_direction_t::diagonal_tl_br:
603 assert(border->top.style == spreadsheet::border_style_t::unknown);
604 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
605 assert(border->left.style == spreadsheet::border_style_t::unknown);
606 assert(border->right.style == spreadsheet::border_style_t::unknown);
607 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
608 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::unknown);
609 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::thin);
610 break;
611 case spreadsheet::border_direction_t::diagonal_bl_tr:
612 assert(border->top.style == spreadsheet::border_style_t::unknown);
613 assert(border->bottom.style == spreadsheet::border_style_t::unknown);
614 assert(border->left.style == spreadsheet::border_style_t::unknown);
615 assert(border->right.style == spreadsheet::border_style_t::unknown);
616 assert(border->diagonal.style == spreadsheet::border_style_t::unknown);
617 assert(border->diagonal_bl_tr.style == spreadsheet::border_style_t::thin);
618 assert(border->diagonal_tl_br.style == spreadsheet::border_style_t::unknown);
619 break;
620 default:
621 assert(!"unhandled direction!");
622 }
623 }
624 }
625
test_xlsx_cell_borders_colors()626 void test_xlsx_cell_borders_colors()
627 {
628 pstring path(SRCDIR"/test/xlsx/borders/colors.xlsx");
629 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
630
631 spreadsheet::styles& styles = doc->get_styles();
632
633 spreadsheet::sheet* sh = doc->get_sheet(0);
634 assert(sh);
635
636 struct check
637 {
638 spreadsheet::row_t row;
639 spreadsheet::col_t col;
640 color_t color;
641 };
642
643 std::vector<check> checks =
644 {
645 { 2, 1, color_t(0xFF, 0xFF, 0, 0) }, // B3 - red
646 { 3, 1, color_t(0xFF, 0, 0x70, 0xC0) }, // B4 - blue
647 { 4, 1, color_t(0xFF, 0, 0xB0, 0x50) }, // B5 - green
648 };
649
650 for (const check& c : checks)
651 {
652 size_t xf = sh->get_cell_format(c.row, c.col); // B3
653
654 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
655 assert(cf);
656 assert(cf->apply_border);
657
658 const spreadsheet::border_t* border = styles.get_border(cf->border);
659 assert(border);
660
661 assert(border->left.style == border_style_t::unknown);
662 assert(border->right.style == border_style_t::thick);
663 assert(border->top.style == border_style_t::unknown);
664 assert(border->bottom.style == border_style_t::unknown);
665
666 assert(border->right.border_color == c.color);
667 }
668
669 // B7 contains yellow left border, purple right border, and light blue
670 // diagonal borders.
671
672 size_t xf = sh->get_cell_format(6, 1); // B7
673
674 const spreadsheet::cell_format_t* cf = styles.get_cell_format(xf);
675 assert(cf);
676 assert(cf->apply_border);
677
678 const spreadsheet::border_t* border = styles.get_border(cf->border);
679 assert(border);
680
681 assert(border->left.style == border_style_t::thick);
682 assert(border->left.border_color == color_t(0xFF, 0xFF, 0xFF, 0)); // yellow
683
684 assert(border->right.style == border_style_t::thick);
685 assert(border->right.border_color == color_t(0xFF, 0x70, 0x30, 0xA0)); // purple
686
687 assert(border->diagonal.style == border_style_t::thick);
688 assert(border->diagonal.border_color == color_t(0xFF, 0x00, 0xB0, 0xF0)); // light blue
689
690 // B7 also contains multi-line string. Test that as well.
691 ixion::model_context& model = doc->get_model_context();
692 ixion::string_id_t sid = model.get_string_identifier(ixion::abs_address_t(0,6,1));
693 const std::string* s = model.get_string(sid);
694 assert(s);
695 assert(*s == "<- Yellow\nPurple ->\nLight Blue \\");
696 }
697
test_xlsx_hidden_rows_columns()698 void test_xlsx_hidden_rows_columns()
699 {
700 pstring path(SRCDIR"/test/xlsx/hidden-rows-columns/input.xlsx");
701 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
702
703 spreadsheet::sheet* sh = doc->get_sheet("Hidden Rows");
704 assert(sh);
705
706 spreadsheet::row_t row_start = -1, row_end = -1;
707
708 // Row 1 is visible.
709 assert(!sh->is_row_hidden(0, &row_start, &row_end));
710 assert(row_start == 0);
711 assert(row_end == 1); // the end position is non-inclusive.
712
713 // Rows 2-3 are hidden.
714 assert(sh->is_row_hidden(1, &row_start, &row_end));
715 assert(row_start == 1);
716 assert(row_end == 3); // the end position is non-inclusive.
717
718 // Row 4 is visible.
719 assert(!sh->is_row_hidden(3, &row_start, &row_end));
720 assert(row_start == 3);
721 assert(row_end == 4); // the end position is non-inclusive.
722
723 // Row 5 is hidden.
724 assert(sh->is_row_hidden(4, &row_start, &row_end));
725 assert(row_start == 4);
726 assert(row_end == 5); // the end position is non-inclusive.
727
728 // Rows 6-8 are visible.
729 assert(!sh->is_row_hidden(5, &row_start, &row_end));
730 assert(row_start == 5);
731 assert(row_end == 8); // the end position is non-inclusive.
732
733 // Row 9 is hidden.
734 assert(sh->is_row_hidden(8, &row_start, &row_end));
735 assert(row_start == 8);
736 assert(row_end == 9); // the end position is non-inclusive.
737
738 // The rest of the rows are visible.
739 assert(!sh->is_row_hidden(9, &row_start, &row_end));
740 assert(row_start == 9);
741 assert(row_end == doc->get_sheet_size().rows); // the end position is non-inclusive.
742
743 sh = doc->get_sheet("Hidden Columns");
744 assert(sh);
745
746 spreadsheet::col_t col_start = -1, col_end = -1;
747
748 // Columns A-B are visible.
749 assert(!sh->is_col_hidden(0, &col_start, &col_end));
750 assert(col_start == 0);
751 assert(col_end == 2); // non-inclusive
752
753 // Columns C-E are hidden.
754 assert(sh->is_col_hidden(2, &col_start, &col_end));
755 assert(col_start == 2);
756 assert(col_end == 6); // non-inclusive
757
758 // Columns G-J are visible.
759 assert(!sh->is_col_hidden(6, &col_start, &col_end));
760 assert(col_start == 6);
761 assert(col_end == 10); // non-inclusive
762
763 // Column K is hidden.
764 assert(sh->is_col_hidden(10, &col_start, &col_end));
765 assert(col_start == 10);
766 assert(col_end == 11); // non-inclusive
767
768 // The rest of the columns are all visible.
769 assert(!sh->is_col_hidden(11, &col_start, &col_end));
770 assert(col_start == 11);
771 assert(col_end == doc->get_sheet_size().columns); // non-inclusive
772 }
773
test_xlsx_pivot_two_pivot_caches()774 void test_xlsx_pivot_two_pivot_caches()
775 {
776 string path(SRCDIR"/test/xlsx/pivot-table/two-pivot-caches.xlsx");
777
778 document doc{{1048576, 16384}};
779 import_factory factory(doc);
780 orcus_xlsx app(&factory);
781 app.set_config(test_config);
782
783 app.read_file(path.c_str());
784
785 const pivot_collection& pc = doc.get_pivot_collection();
786 assert(pc.get_cache_count() == 2);
787
788 // B2:C6 on sheet 'Data'.
789 const pivot_cache* cache = get_pivot_cache(pc, "Data", "B2:C6");
790 assert(cache);
791 assert(cache->get_field_count() == 2);
792
793 // Test the content of this cache.
794 const pivot_cache_field_t* fld = cache->get_field(0);
795 assert(fld);
796 assert(fld->name == "F1");
797
798 {
799 // This field should contain 4 string items 'A', 'B', 'C' and 'D'.
800 std::set<pivot_cache_item_t> expected =
801 {
802 pivot_cache_item_t(ORCUS_ASCII("A")),
803 pivot_cache_item_t(ORCUS_ASCII("B")),
804 pivot_cache_item_t(ORCUS_ASCII("C")),
805 pivot_cache_item_t(ORCUS_ASCII("D")),
806 };
807
808 std::set<pivot_cache_item_t> actual(fld->items.begin(), fld->items.end());
809 assert(actual == expected);
810 }
811
812 fld = cache->get_field(1);
813 assert(fld);
814 assert(fld->name == "D1");
815
816 // This is a pure numeric field with min and max values specified.
817 assert(fld->min_value && *fld->min_value == 1.0);
818 assert(fld->max_value && *fld->max_value == 4.0);
819 assert(fld->items.empty());
820
821 {
822 // Check the records.
823 pivot_cache::records_type expected =
824 {
825 { pivot_cache_record_value_t(size_t(0)), pivot_cache_record_value_t(1.0) },
826 { pivot_cache_record_value_t(size_t(1)), pivot_cache_record_value_t(2.0) },
827 { pivot_cache_record_value_t(size_t(2)), pivot_cache_record_value_t(3.0) },
828 { pivot_cache_record_value_t(size_t(3)), pivot_cache_record_value_t(4.0) },
829 };
830
831 assert(expected == cache->get_all_records());
832 }
833
834 // F10:G14 on the same sheet.
835 cache = get_pivot_cache(pc, "Data", "F10:G14");
836 assert(cache);
837 assert(cache->get_field_count() == 2);
838
839 // This field should contain 4 string items 'W', 'X', 'Y' and 'Z' but not
840 // necessarily in this order.
841 fld = cache->get_field(0);
842 assert(fld);
843 assert(fld->name == "F2");
844
845 {
846 std::set<pivot_cache_item_t> expected =
847 {
848 pivot_cache_item_t(ORCUS_ASCII("W")),
849 pivot_cache_item_t(ORCUS_ASCII("X")),
850 pivot_cache_item_t(ORCUS_ASCII("Y")),
851 pivot_cache_item_t(ORCUS_ASCII("Z")),
852 };
853
854 std::set<pivot_cache_item_t> actual;
855 actual.insert(fld->items.begin(), fld->items.end());
856 assert(actual == expected);
857 }
858
859 fld = cache->get_field(1);
860 assert(fld);
861 assert(fld->name == "D2");
862
863 // This is a pure numeric field with min and max values specified.
864 assert(fld->min_value && *fld->min_value == 1.0);
865 assert(fld->max_value && *fld->max_value == 4.0);
866 assert(fld->items.empty());
867
868 {
869 // Check the records.
870 pivot_cache::records_type expected =
871 {
872 { pivot_cache_record_value_t(size_t(0)), pivot_cache_record_value_t(4.0) },
873 { pivot_cache_record_value_t(size_t(1)), pivot_cache_record_value_t(3.0) },
874 { pivot_cache_record_value_t(size_t(2)), pivot_cache_record_value_t(2.0) },
875 { pivot_cache_record_value_t(size_t(3)), pivot_cache_record_value_t(1.0) },
876 };
877
878 assert(expected == cache->get_all_records());
879 }
880 }
881
test_xlsx_pivot_mixed_type_field()882 void test_xlsx_pivot_mixed_type_field()
883 {
884 string path(SRCDIR"/test/xlsx/pivot-table/mixed-type-field.xlsx");
885
886 document doc{{1048576, 16384}};
887 import_factory factory(doc);
888 orcus_xlsx app(&factory);
889 app.set_config(test_config);
890
891 app.read_file(path.c_str());
892
893 const pivot_collection& pc = doc.get_pivot_collection();
894 assert(pc.get_cache_count() == 2);
895
896 // B2:C7 on sheet 'Data'.
897 const pivot_cache* cache = get_pivot_cache(pc, "Data", "B2:C7");
898 assert(cache);
899 assert(cache->get_field_count() == 2);
900
901 // 1st field
902 const pivot_cache_field_t* fld = cache->get_field(0);
903 assert(fld);
904 assert(fld->name == "F1");
905 assert(fld->min_value && fld->min_value == 1.0);
906 assert(fld->max_value && fld->max_value == 2.0);
907
908 {
909 // This field should contain 3 string items 'A', 'B', 'C' and 2 numeric
910 // items 1 and 2.
911 std::set<pivot_cache_item_t> expected =
912 {
913 pivot_cache_item_t(ORCUS_ASCII("A")),
914 pivot_cache_item_t(ORCUS_ASCII("B")),
915 pivot_cache_item_t(ORCUS_ASCII("C")),
916 pivot_cache_item_t(1.0),
917 pivot_cache_item_t(2.0),
918 };
919
920 std::set<pivot_cache_item_t> actual(fld->items.begin(), fld->items.end());
921 assert(actual == expected);
922 }
923
924 // 2nd field should be a nuemric field between 1.1 and 1.5.
925 fld = cache->get_field(1);
926 assert(fld);
927 assert(fld->name == "V1");
928 assert(fld->items.empty());
929
930 assert(fld->min_value);
931 assert(std::round(*fld->min_value * 100.0) == 110.0); // min = 1.1
932 assert(fld->max_value);
933 assert(std::round(*fld->max_value * 100.0) == 150.0); // max = 1.5
934
935 {
936 // Check the records.
937 pivot_cache::records_type expected =
938 {
939 { pivot_cache_record_value_t(size_t(0)), pivot_cache_record_value_t(1.1) },
940 { pivot_cache_record_value_t(size_t(1)), pivot_cache_record_value_t(1.2) },
941 { pivot_cache_record_value_t(size_t(2)), pivot_cache_record_value_t(1.3) },
942 { pivot_cache_record_value_t(size_t(3)), pivot_cache_record_value_t(1.4) },
943 { pivot_cache_record_value_t(size_t(4)), pivot_cache_record_value_t(1.5) },
944 };
945
946 assert(expected == cache->get_all_records());
947 }
948
949 // B10:C17 on sheet 'Data'.
950 cache = get_pivot_cache(pc, "Data", "B10:C17");
951 assert(cache);
952 assert(cache->get_field_count() == 2);
953
954 // 1st field
955 fld = cache->get_field(0);
956 assert(fld);
957 assert(fld->name == "F2");
958 assert(fld->min_value && fld->min_value == 1.0);
959 assert(fld->max_value && fld->max_value == 5.0);
960
961 {
962 // This field should contain 3 string items 'A', 'B', 'C' and 4 numeric
963 // items 1, 2, 3.5 and 5.
964 std::set<pivot_cache_item_t> expected =
965 {
966 pivot_cache_item_t(ORCUS_ASCII("A")),
967 pivot_cache_item_t(ORCUS_ASCII("B")),
968 pivot_cache_item_t(ORCUS_ASCII("C")),
969 pivot_cache_item_t(1.0),
970 pivot_cache_item_t(2.0),
971 pivot_cache_item_t(3.5),
972 pivot_cache_item_t(5.0),
973 };
974
975 std::set<pivot_cache_item_t> actual;
976 actual.insert(fld->items.begin(), fld->items.end());
977 assert(actual == expected);
978 }
979
980 // 2nd field
981 fld = cache->get_field(1);
982 assert(fld);
983 assert(fld->name == "V2");
984 assert(fld->items.empty());
985
986 assert(fld->min_value);
987 assert(std::round(*fld->min_value * 100.0) == 110.0); // min = 1.1
988 assert(fld->max_value);
989 assert(std::round(*fld->max_value * 100.0) == 220.0); // max = 2.2
990
991 {
992 // Check the records.
993 pivot_cache::records_type expected =
994 {
995 { pivot_cache_record_value_t(size_t(0)), pivot_cache_record_value_t(1.1) },
996 { pivot_cache_record_value_t(size_t(1)), pivot_cache_record_value_t(1.2) },
997 { pivot_cache_record_value_t(size_t(2)), pivot_cache_record_value_t(1.3) },
998 { pivot_cache_record_value_t(size_t(3)), pivot_cache_record_value_t(1.4) },
999 { pivot_cache_record_value_t(size_t(4)), pivot_cache_record_value_t(1.5) },
1000 { pivot_cache_record_value_t(size_t(5)), pivot_cache_record_value_t(1.8) },
1001 { pivot_cache_record_value_t(size_t(6)), pivot_cache_record_value_t(2.2) },
1002 };
1003
1004 assert(expected == cache->get_all_records());
1005 }
1006 }
1007
test_xlsx_pivot_group_field()1008 void test_xlsx_pivot_group_field()
1009 {
1010 string path(SRCDIR"/test/xlsx/pivot-table/group-field.xlsx");
1011
1012 document doc{{1048576, 16384}};
1013 import_factory factory(doc);
1014 orcus_xlsx app(&factory);
1015 app.set_config(test_config);
1016
1017 app.read_file(path.c_str());
1018
1019 const pivot_collection& pc = doc.get_pivot_collection();
1020 assert(pc.get_cache_count() == 1);
1021
1022 // B2:C6 on sheet 'Sheet1'.
1023 const pivot_cache* cache = get_pivot_cache(pc, "Sheet1", "B2:C6");
1024 assert(cache);
1025 assert(cache->get_field_count() == 3);
1026
1027 // First field is labeled 'Key'.
1028 const pivot_cache_field_t* fld = cache->get_field(0);
1029 assert(fld);
1030 assert(fld->name == "Key");
1031
1032 {
1033 // This field should contain 4 string items 'A', 'B', 'C' and 'D'.
1034 std::set<pivot_cache_item_t> expected =
1035 {
1036 pivot_cache_item_t(ORCUS_ASCII("A")),
1037 pivot_cache_item_t(ORCUS_ASCII("B")),
1038 pivot_cache_item_t(ORCUS_ASCII("C")),
1039 pivot_cache_item_t(ORCUS_ASCII("D")),
1040 };
1041
1042 std::set<pivot_cache_item_t> actual(fld->items.begin(), fld->items.end());
1043 assert(actual == expected);
1044 }
1045
1046 // 2nd field is 'Value' and is a numeric field.
1047 fld = cache->get_field(1);
1048 assert(fld);
1049 assert(fld->name == "Value");
1050 assert(fld->items.empty());
1051
1052 assert(fld->min_value);
1053 assert(*fld->min_value == 1.0);
1054 assert(fld->max_value);
1055 assert(*fld->max_value == 4.0);
1056
1057 // 3rd field is a group field labeled 'Key2'.
1058 fld = cache->get_field(2);
1059 assert(fld);
1060 assert(fld->name == "Key2");
1061 assert(fld->items.empty());
1062
1063 const pivot_cache_group_data_t* gd = fld->group_data.get();
1064 assert(gd);
1065 assert(gd->base_field == 0);
1066 assert(gd->items.size() == 2);
1067
1068 {
1069 // It should have two items - Group1 and Group2.
1070 std::set<pivot_cache_item_t> expected =
1071 {
1072 pivot_cache_item_t(ORCUS_ASCII("Group1")),
1073 pivot_cache_item_t(ORCUS_ASCII("Group2")),
1074 };
1075
1076 std::set<pivot_cache_item_t> actual;
1077 actual.insert(gd->items.begin(), gd->items.end());
1078 assert(actual == expected);
1079 }
1080
1081 // Group1 should group 'A' and 'B' from the 1st field, and Group2 should
1082 // group 'C' and 'D'.
1083
1084 pivot_cache_indices_t expected_group = { 0, 0, 1, 1 };
1085 assert(gd->base_to_group_indices == expected_group);
1086
1087 {
1088 // Check the records.
1089 pivot_cache::records_type expected =
1090 {
1091 { pivot_cache_record_value_t(size_t(0)), pivot_cache_record_value_t(1.0) },
1092 { pivot_cache_record_value_t(size_t(1)), pivot_cache_record_value_t(2.0) },
1093 { pivot_cache_record_value_t(size_t(2)), pivot_cache_record_value_t(3.0) },
1094 { pivot_cache_record_value_t(size_t(3)), pivot_cache_record_value_t(4.0) },
1095 };
1096
1097 assert(expected == cache->get_all_records());
1098 }
1099 }
1100
test_xlsx_pivot_group_by_numbers()1101 void test_xlsx_pivot_group_by_numbers()
1102 {
1103 string path(SRCDIR"/test/xlsx/pivot-table/group-by-numbers.xlsx");
1104
1105 document doc{{1048576, 16384}};
1106 import_factory factory(doc);
1107 orcus_xlsx app(&factory);
1108 app.set_config(test_config);
1109
1110 app.read_file(path.c_str());
1111
1112 const pivot_collection& pc = doc.get_pivot_collection();
1113 assert(pc.get_cache_count() == 1);
1114
1115 // B2:C13 on sheet 'Sheet1'.
1116 const pivot_cache* cache = get_pivot_cache(pc, "Sheet1", "B2:C13");
1117 assert(cache);
1118 assert(cache->get_field_count() == 2);
1119
1120 // First field is a field with numeric grouping with intervals.
1121 const pivot_cache_field_t* fld = cache->get_field(0);
1122 assert(fld);
1123 assert(fld->name == "V1");
1124
1125 // There should be 11 raw values ranging from 9.78E-2 to 9.82.
1126 assert(fld->items.size() == 11);
1127 assert(fld->min_value);
1128 assert(fld->max_value);
1129 assert(std::round(*fld->min_value*10000.0) == 978.00); // 9.78E-2
1130 assert(std::round(*fld->max_value*100.0) == 982.00); // 9.82
1131
1132 // We'll just make sure that all 11 items are of numeric type.
1133
1134 for (const pivot_cache_item_t& item : fld->items)
1135 {
1136 assert(item.type == pivot_cache_item_t::item_type::numeric);
1137 assert(*fld->min_value <= item.value.numeric);
1138 assert(item.value.numeric <= *fld->max_value);
1139 }
1140
1141 // This field is also gruop field with 7 numeric intervals of width 2.
1142 assert(fld->group_data);
1143 const pivot_cache_group_data_t& grp = *fld->group_data;
1144 assert(grp.items.size() == 7);
1145
1146 pivot_cache_items_t expected =
1147 {
1148 pivot_cache_item_t(ORCUS_ASCII("<0")),
1149 pivot_cache_item_t(ORCUS_ASCII("0-2")),
1150 pivot_cache_item_t(ORCUS_ASCII("2-4")),
1151 pivot_cache_item_t(ORCUS_ASCII("4-6")),
1152 pivot_cache_item_t(ORCUS_ASCII("6-8")),
1153 pivot_cache_item_t(ORCUS_ASCII("8-10")),
1154 pivot_cache_item_t(ORCUS_ASCII(">10")),
1155 };
1156
1157 assert(grp.items == expected);
1158
1159 // Check the numeric range properties.
1160 assert(grp.range_grouping);
1161 assert(grp.range_grouping->group_by == pivot_cache_group_by_t::range);
1162 assert(!grp.range_grouping->auto_start);
1163 assert(!grp.range_grouping->auto_end);
1164 assert(grp.range_grouping->start == 0.0);
1165 assert(grp.range_grouping->end == 10.0);
1166 assert(grp.range_grouping->interval == 2.0);
1167
1168 // Test the 2nd field. This field is purely a numeric field with no
1169 // discrete items.
1170
1171 fld = cache->get_field(1);
1172 assert(fld);
1173 assert(fld->name == "V2");
1174 assert(!fld->group_data);
1175 assert(fld->items.empty());
1176 assert(fld->min_value);
1177 assert(fld->min_value == 1.0);
1178 assert(fld->max_value);
1179 assert(fld->max_value == 11.0);
1180 }
1181
test_xlsx_pivot_group_by_dates()1182 void test_xlsx_pivot_group_by_dates()
1183 {
1184 string path(SRCDIR"/test/xlsx/pivot-table/group-by-dates.xlsx");
1185
1186 document doc{{1048576, 16384}};
1187 import_factory factory(doc);
1188 orcus_xlsx app(&factory);
1189 app.set_config(test_config);
1190
1191 app.read_file(path.c_str());
1192
1193 const pivot_collection& pc = doc.get_pivot_collection();
1194 assert(pc.get_cache_count() == 1);
1195
1196 const pivot_cache* cache = get_pivot_cache(pc, "Sheet1", "B2:C14");
1197 assert(cache);
1198
1199 // First field is a date field.
1200 const pivot_cache_field_t* fld = cache->get_field(0);
1201 assert(fld);
1202 assert(fld->name == "Date");
1203
1204 // Minimum and maximum date values.
1205 assert(fld->min_date);
1206 assert(*fld->min_date == date_time_t(2014, 1, 1));
1207 assert(fld->max_date);
1208 assert(*fld->max_date == date_time_t(2014, 12, 2));
1209
1210 pivot_cache_items_t expected =
1211 {
1212 pivot_cache_item_t(date_time_t(2014, 1, 1)),
1213 pivot_cache_item_t(date_time_t(2014, 2, 1)),
1214 pivot_cache_item_t(date_time_t(2014, 3, 1)),
1215 pivot_cache_item_t(date_time_t(2014, 4, 1)),
1216 pivot_cache_item_t(date_time_t(2014, 5, 1)),
1217 pivot_cache_item_t(date_time_t(2014, 6, 1)),
1218 pivot_cache_item_t(date_time_t(2014, 7, 1)),
1219 pivot_cache_item_t(date_time_t(2014, 8, 1)),
1220 pivot_cache_item_t(date_time_t(2014, 9, 1)),
1221 pivot_cache_item_t(date_time_t(2014, 10, 1)),
1222 pivot_cache_item_t(date_time_t(2014, 11, 1)),
1223 pivot_cache_item_t(date_time_t(2014, 12, 1)),
1224 };
1225
1226 pivot_cache_items_t actual(fld->items.begin(), fld->items.end());
1227 assert(actual == expected);
1228
1229 // This field is grouped by month.
1230
1231 assert(fld->group_data);
1232 const pivot_cache_group_data_t& gd = *fld->group_data;
1233
1234 expected =
1235 {
1236 pivot_cache_item_t(ORCUS_ASCII("<1/1/2014")),
1237 pivot_cache_item_t(ORCUS_ASCII("Jan")),
1238 pivot_cache_item_t(ORCUS_ASCII("Feb")),
1239 pivot_cache_item_t(ORCUS_ASCII("Mar")),
1240 pivot_cache_item_t(ORCUS_ASCII("Apr")),
1241 pivot_cache_item_t(ORCUS_ASCII("May")),
1242 pivot_cache_item_t(ORCUS_ASCII("Jun")),
1243 pivot_cache_item_t(ORCUS_ASCII("Jul")),
1244 pivot_cache_item_t(ORCUS_ASCII("Aug")),
1245 pivot_cache_item_t(ORCUS_ASCII("Sep")),
1246 pivot_cache_item_t(ORCUS_ASCII("Oct")),
1247 pivot_cache_item_t(ORCUS_ASCII("Nov")),
1248 pivot_cache_item_t(ORCUS_ASCII("Dec")),
1249 pivot_cache_item_t(ORCUS_ASCII(">12/2/2014")),
1250 };
1251
1252 assert(gd.items == expected);
1253
1254 assert(gd.range_grouping);
1255 assert(gd.range_grouping->group_by == pivot_cache_group_by_t::months);
1256
1257 assert(gd.range_grouping->start_date == date_time_t(2014,1,1));
1258 assert(gd.range_grouping->end_date == date_time_t(2014,12,2));
1259
1260 // The 2nd field is a simple numeric field.
1261 fld = cache->get_field(1);
1262 assert(fld);
1263 assert(fld->name == "Value");
1264 assert(fld->min_value == 1.0);
1265 assert(fld->max_value == 12.0);
1266
1267 // The 3rd field is an extra group field.
1268 fld = cache->get_field(2);
1269 assert(fld);
1270 assert(fld->name == "Quarters");
1271 assert(fld->group_data);
1272 const pivot_cache_group_data_t& gd_qtrs = *fld->group_data;
1273 assert(gd_qtrs.base_field == 0);
1274
1275 assert(gd_qtrs.range_grouping);
1276 assert(gd_qtrs.range_grouping->group_by == pivot_cache_group_by_t::quarters);
1277 assert(gd_qtrs.range_grouping->start_date == date_time_t(2014,1,1));
1278 assert(gd_qtrs.range_grouping->end_date == date_time_t(2014,12,2));
1279
1280 expected =
1281 {
1282 pivot_cache_item_t(ORCUS_ASCII("<1/1/2014")),
1283 pivot_cache_item_t(ORCUS_ASCII("Qtr1")),
1284 pivot_cache_item_t(ORCUS_ASCII("Qtr2")),
1285 pivot_cache_item_t(ORCUS_ASCII("Qtr3")),
1286 pivot_cache_item_t(ORCUS_ASCII("Qtr4")),
1287 pivot_cache_item_t(ORCUS_ASCII(">12/2/2014")),
1288 };
1289
1290 assert(gd_qtrs.items == expected);
1291 }
1292
test_xlsx_pivot_error_values()1293 void test_xlsx_pivot_error_values()
1294 {
1295 string path(SRCDIR"/test/xlsx/pivot-table/error-values.xlsx");
1296
1297 document doc{{1048576, 16384}};
1298 import_factory factory(doc);
1299 orcus_xlsx app(&factory);
1300 app.set_config(test_config);
1301
1302 app.read_file(path.c_str());
1303
1304 const pivot_collection& pc = doc.get_pivot_collection();
1305 assert(pc.get_cache_count() == 1);
1306
1307 const pivot_cache* cache = get_pivot_cache(pc, "Sheet1", "B2:C6");
1308 assert(cache);
1309
1310 const pivot_cache_field_t* fld = cache->get_field(0);
1311
1312 assert(fld);
1313 assert(fld->name == "F1");
1314
1315 // This field should contain 4 string items 'A', 'B', 'C' and 'D'.
1316 std::set<pivot_cache_item_t> expected =
1317 {
1318 pivot_cache_item_t(ORCUS_ASCII("A")),
1319 pivot_cache_item_t(ORCUS_ASCII("B")),
1320 pivot_cache_item_t(ORCUS_ASCII("C")),
1321 pivot_cache_item_t(ORCUS_ASCII("D")),
1322 };
1323
1324 std::set<pivot_cache_item_t> actual(fld->items.begin(), fld->items.end());
1325 assert(actual == expected);
1326
1327 fld = cache->get_field(1);
1328
1329 assert(fld);
1330 assert(fld->name == "F2");
1331
1332 expected =
1333 {
1334 pivot_cache_item_t(spreadsheet::error_value_t::div0),
1335 pivot_cache_item_t(spreadsheet::error_value_t::name),
1336 };
1337
1338 actual.clear();
1339 actual.insert(fld->items.begin(), fld->items.end());
1340
1341 assert(actual == expected);
1342 }
1343
test_xlsx_view_cursor_per_sheet()1344 void test_xlsx_view_cursor_per_sheet()
1345 {
1346 string path(SRCDIR"/test/xlsx/view/cursor-per-sheet.xlsx");
1347
1348 document doc{{1048576, 16384}};
1349 spreadsheet::view view(doc);
1350 spreadsheet::import_factory factory(doc, view);
1351 orcus_xlsx app(&factory);
1352 app.set_config(test_config);
1353
1354 app.read_file(path.c_str());
1355
1356 // Sheet3 should be active.
1357 assert(view.get_active_sheet() == 2);
1358
1359 const spreadsheet::sheet_view* sv = view.get_sheet_view(0);
1360 assert(sv);
1361
1362 spreadsheet::iface::import_reference_resolver* resolver =
1363 factory.get_reference_resolver(spreadsheet::formula_ref_context_t::global);
1364 assert(resolver);
1365
1366 // On Sheet1, the cursor should be set to C4.
1367 spreadsheet::range_t expected = to_rc_range(resolver->resolve_range(ORCUS_ASCII("C4")));
1368 spreadsheet::range_t actual = sv->get_selection(spreadsheet::sheet_pane_t::top_left);
1369 assert(expected == actual);
1370
1371 sv = view.get_sheet_view(1);
1372 assert(sv);
1373
1374 // On Sheet2, the cursor should be set to D8.
1375 expected = to_rc_range(resolver->resolve_range(ORCUS_ASCII("D8")));
1376 actual = sv->get_selection(spreadsheet::sheet_pane_t::top_left);
1377 assert(expected == actual);
1378
1379 sv = view.get_sheet_view(2);
1380 assert(sv);
1381
1382 // On Sheet3, the cursor should be set to D2.
1383 expected = to_rc_range(resolver->resolve_range(ORCUS_ASCII("D2")));
1384 actual = sv->get_selection(spreadsheet::sheet_pane_t::top_left);
1385 assert(expected == actual);
1386
1387 sv = view.get_sheet_view(3);
1388 assert(sv);
1389
1390 // On Sheet4, the cursor should be set to C5:E8.
1391 expected = to_rc_range(resolver->resolve_range(ORCUS_ASCII("C5:E8")));
1392 actual = sv->get_selection(spreadsheet::sheet_pane_t::top_left);
1393 assert(expected == actual);
1394 }
1395
1396 struct expected_selection
1397 {
1398 spreadsheet::sheet_pane_t pane;
1399 const char* sel;
1400 size_t sel_n;
1401 };
1402
test_xlsx_view_cursor_split_pane()1403 void test_xlsx_view_cursor_split_pane()
1404 {
1405 string path(SRCDIR"/test/xlsx/view/cursor-split-pane.xlsx");
1406
1407 document doc{{1048576, 16384}};
1408 spreadsheet::view view(doc);
1409 spreadsheet::import_factory factory(doc, view);
1410 orcus_xlsx app(&factory);
1411 app.set_config(test_config);
1412
1413 app.read_file(path.c_str());
1414
1415 spreadsheet::iface::import_reference_resolver* resolver =
1416 factory.get_reference_resolver(spreadsheet::formula_ref_context_t::global);
1417 assert(resolver);
1418
1419 // Sheet4 should be active.
1420 assert(view.get_active_sheet() == 3);
1421
1422 const spreadsheet::sheet_view* sv = view.get_sheet_view(0);
1423 assert(sv);
1424
1425 // On Sheet1, the view is split into 4.
1426 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::bottom_left);
1427 assert(sv->get_split_pane().hor_split == 5190.0);
1428 assert(sv->get_split_pane().ver_split == 1800.0);
1429
1430 {
1431 spreadsheet::address_t expected = to_rc_address(resolver->resolve_address(ORCUS_ASCII("F6")));
1432 spreadsheet::address_t actual = sv->get_split_pane().top_left_cell;
1433 assert(expected == actual);
1434 }
1435
1436 std::vector<expected_selection> expected_selections =
1437 {
1438 { spreadsheet::sheet_pane_t::top_left, ORCUS_ASCII("E4") },
1439 { spreadsheet::sheet_pane_t::top_right, ORCUS_ASCII("J2") },
1440 { spreadsheet::sheet_pane_t::bottom_left, ORCUS_ASCII("A8") },
1441 { spreadsheet::sheet_pane_t::bottom_right, ORCUS_ASCII("J17") },
1442 };
1443
1444 for (const expected_selection& es : expected_selections)
1445 {
1446 // cursor in the top-left pane.
1447 spreadsheet::range_t expected = to_rc_range(resolver->resolve_range(es.sel, es.sel_n));
1448 spreadsheet::range_t actual = sv->get_selection(es.pane);
1449 assert(expected == actual);
1450 }
1451
1452 sv = view.get_sheet_view(1);
1453 assert(sv);
1454
1455 // Sheet2 is also split into 4 views.
1456 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::top_right);
1457 assert(sv->get_split_pane().hor_split == 5190.0);
1458 assert(sv->get_split_pane().ver_split == 2400.0);
1459
1460 {
1461 spreadsheet::address_t expected = to_rc_address(resolver->resolve_address(ORCUS_ASCII("F8")));
1462 spreadsheet::address_t actual = sv->get_split_pane().top_left_cell;
1463 assert(expected == actual);
1464 }
1465
1466 expected_selections =
1467 {
1468 { spreadsheet::sheet_pane_t::top_left, ORCUS_ASCII("C2:C6") },
1469 { spreadsheet::sheet_pane_t::top_right, ORCUS_ASCII("H2:L2") },
1470 { spreadsheet::sheet_pane_t::bottom_left, ORCUS_ASCII("B18:C23") },
1471 { spreadsheet::sheet_pane_t::bottom_right, ORCUS_ASCII("H11:J13") },
1472 };
1473
1474 for (const expected_selection& es : expected_selections)
1475 {
1476 // cursor in the top-left pane.
1477 spreadsheet::range_t expected = to_rc_range(resolver->resolve_range(es.sel, es.sel_n));
1478 spreadsheet::range_t actual = sv->get_selection(es.pane);
1479 assert(expected == actual);
1480 }
1481
1482 sv = view.get_sheet_view(2);
1483 assert(sv);
1484
1485 // Sheet3 is horizontally split into top and bottom views (top-left and bottom-left).
1486 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::bottom_left);
1487 assert(sv->get_split_pane().hor_split == 0.0);
1488 assert(sv->get_split_pane().ver_split == 1500.0);
1489
1490 {
1491 spreadsheet::address_t expected = to_rc_address(resolver->resolve_address(ORCUS_ASCII("A5")));
1492 spreadsheet::address_t actual = sv->get_split_pane().top_left_cell;
1493 assert(expected == actual);
1494 }
1495
1496 expected_selections =
1497 {
1498 { spreadsheet::sheet_pane_t::top_left, ORCUS_ASCII("D2") },
1499 { spreadsheet::sheet_pane_t::bottom_left, ORCUS_ASCII("C9") },
1500 };
1501
1502 for (const expected_selection& es : expected_selections)
1503 {
1504 // cursor in the top-left pane.
1505 spreadsheet::range_t expected = to_rc_range(resolver->resolve_range(es.sel, es.sel_n));
1506 spreadsheet::range_t actual = sv->get_selection(es.pane);
1507 assert(expected == actual);
1508 }
1509
1510 sv = view.get_sheet_view(3);
1511 assert(sv);
1512
1513 // Sheet4 is vertically split into left and right views (top-left and top-right).
1514 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::top_left);
1515 assert(sv->get_split_pane().hor_split == 4230.0);
1516 assert(sv->get_split_pane().ver_split == 0.0);
1517
1518 {
1519 spreadsheet::address_t expected = to_rc_address(resolver->resolve_address(ORCUS_ASCII("E1")));
1520 spreadsheet::address_t actual = sv->get_split_pane().top_left_cell;
1521 assert(expected == actual);
1522 }
1523
1524 expected_selections =
1525 {
1526 { spreadsheet::sheet_pane_t::top_left, ORCUS_ASCII("B18") },
1527 { spreadsheet::sheet_pane_t::top_right, ORCUS_ASCII("I11") },
1528 };
1529
1530 for (const expected_selection& es : expected_selections)
1531 {
1532 // cursor in the top-left pane.
1533 spreadsheet::range_t expected = to_rc_range(resolver->resolve_range(es.sel, es.sel_n));
1534 spreadsheet::range_t actual = sv->get_selection(es.pane);
1535 assert(expected == actual);
1536 }
1537 }
1538
test_xlsx_view_frozen_pane()1539 void test_xlsx_view_frozen_pane()
1540 {
1541 string path(SRCDIR"/test/xlsx/view/frozen-pane.xlsx");
1542
1543 document doc{{1048576, 16384}};
1544 spreadsheet::view view(doc);
1545 spreadsheet::import_factory factory(doc, view);
1546 orcus_xlsx app(&factory);
1547 app.set_config(test_config);
1548
1549 app.read_file(path.c_str());
1550
1551 spreadsheet::iface::import_reference_resolver* resolver =
1552 factory.get_reference_resolver(spreadsheet::formula_ref_context_t::global);
1553 assert(resolver);
1554
1555 // Sheet3 should be active.
1556 assert(view.get_active_sheet() == 2);
1557
1558 const spreadsheet::sheet_view* sv = view.get_sheet_view(0);
1559 assert(sv);
1560
1561 {
1562 // Sheet1 is vertically frozen between columns A and B.
1563 const spreadsheet::frozen_pane_t& fp = sv->get_frozen_pane();
1564 assert(fp.top_left_cell == to_rc_address(resolver->resolve_address(ORCUS_ASCII("B1"))));
1565 assert(fp.visible_columns == 1);
1566 assert(fp.visible_rows == 0);
1567 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::top_right);
1568 }
1569
1570 sv = view.get_sheet_view(1);
1571 assert(sv);
1572
1573 {
1574 // Sheet2 is horizontally frozen between rows 1 and 2.
1575 const spreadsheet::frozen_pane_t& fp = sv->get_frozen_pane();
1576 assert(fp.top_left_cell == to_rc_address(resolver->resolve_address(ORCUS_ASCII("A2"))));
1577 assert(fp.visible_columns == 0);
1578 assert(fp.visible_rows == 1);
1579 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::bottom_left);
1580 }
1581
1582 sv = view.get_sheet_view(2);
1583 assert(sv);
1584
1585 {
1586 // Sheet3 is frozen both horizontally and vertically.
1587 const spreadsheet::frozen_pane_t& fp = sv->get_frozen_pane();
1588 assert(fp.top_left_cell == to_rc_address(resolver->resolve_address(ORCUS_ASCII("E9"))));
1589 assert(fp.visible_columns == 4);
1590 assert(fp.visible_rows == 8);
1591 assert(sv->get_active_pane() == spreadsheet::sheet_pane_t::bottom_right);
1592 }
1593 }
1594
test_xlsx_doc_structure_unordered_sheet_positions()1595 void test_xlsx_doc_structure_unordered_sheet_positions()
1596 {
1597 pstring path(SRCDIR"/test/xlsx/doc-structure/unordered-sheet-positions.xlsx");
1598 std::unique_ptr<spreadsheet::document> doc = load_doc(path);
1599
1600 // There should be 9 sheets named S1, S2, ..., S9.
1601 std::vector<pstring> expected_sheet_names = {
1602 "S1", "S2", "S3", "S4", "S5", "S6", "S7", "S8", "S9"
1603 };
1604
1605 assert(doc->get_sheet_count() == expected_sheet_names.size());
1606
1607 sheet_t n = expected_sheet_names.size();
1608 for (sheet_t i = 0; i < n; ++i)
1609 {
1610 pstring sheet_name = doc->get_sheet_name(i);
1611 assert(sheet_name == expected_sheet_names[i]);
1612 }
1613 }
1614
1615 }
1616
main()1617 int main()
1618 {
1619 test_config.debug = true;
1620 test_config.structure_check = true;
1621
1622 test_xlsx_import();
1623 test_xlsx_table_autofilter();
1624 test_xlsx_table();
1625 test_xlsx_merged_cells();
1626 test_xlsx_date_time();
1627 test_xlsx_background_fill();
1628 test_xlsx_number_format();
1629 test_xlsx_text_alignment();
1630 test_xlsx_cell_borders_single_cells();
1631 test_xlsx_cell_borders_directions();
1632 test_xlsx_cell_borders_colors();
1633 test_xlsx_hidden_rows_columns();
1634
1635 // pivot table
1636 test_xlsx_pivot_two_pivot_caches();
1637 test_xlsx_pivot_mixed_type_field();
1638 test_xlsx_pivot_group_field();
1639 test_xlsx_pivot_group_by_numbers();
1640 test_xlsx_pivot_group_by_dates();
1641 test_xlsx_pivot_error_values();
1642
1643 // view import
1644 test_xlsx_view_cursor_per_sheet();
1645 test_xlsx_view_cursor_split_pane();
1646 test_xlsx_view_frozen_pane();
1647
1648 // document structure
1649 test_xlsx_doc_structure_unordered_sheet_positions();
1650
1651 return EXIT_SUCCESS;
1652 }
1653
1654 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
1655