?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Solver: adapt to glpk file format changes.
[gnumeric.git] / plugins / fn-lookup / functions.c
blob673daee6bb20ad02f1c2015ae75016b3a96aebb8
1 /* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
2 /*
3 * Range lookup functions
4 *
5 * Authors:
6 * Michael Meeks <michael@ximian.com>
7 * Jukka-Pekka Iivonen <iivonen@iki.fi>
8 * JP Rosevear <jpr@arcavia.com>
9 * Morten Welinder (terra@gnome.org)
10 *
11 * This program is free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * This program is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 * GNU General Public License for more details.
20 *
21 * You should have received a copy of the GNU General Public License
22 * along with this program; if not, write to the Free Software
23 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
24 */
25
26 #include <gnumeric-config.h>
27 #include <gnumeric.h>
28 #include <func.h>
29
30 #include <parse-util.h>
31 #include <dependent.h>
32 #include <cell.h>
33 #include <collect.h>
34 #include <sheet.h>
35 #include <value.h>
36 #include <ranges.h>
37 #include <expr.h>
38 #include <expr-impl.h>
39 #include <application.h>
40 #include <expr-name.h>
41 #include <mathfunc.h>
42 #include <gutils.h>
43 #include <workbook.h>
44 #include <sheet.h>
45 #include <parse-util.h>
46 #include <gnm-i18n.h>
47
48 #include <goffice/goffice.h>
49 #include <gnm-plugin.h>
50
51 #include <string.h>
52 #include <stdlib.h>
53
54 GNM_PLUGIN_MODULE_HEADER;
55
56 /* -------------------------------------------------------------------------- */
57
58 typedef struct {
59 int index;
60 union {
61 const char *str;
62 gnm_float f;
63 } u;
64 } LookupBisectionCacheItemElem;
65
66 typedef struct {
67 int n;
68 LookupBisectionCacheItemElem *data;
69 } LookupBisectionCacheItem;
70
71 static void
72 lookup_bisection_cache_item_free (LookupBisectionCacheItem *item)
73 {
74 g_free (item->data);
75 g_free (item);
76 }
77
78 static int
79 bisection_compare_string (const void *a_, const void *b_)
80 {
81 const LookupBisectionCacheItemElem *a = a_;
82 const LookupBisectionCacheItemElem *b = b_;
83 return g_utf8_collate (a->u.str, b->u.str);
84 }
85
86 static int
87 bisection_compare_float (const void *a_, const void *b_)
88 {
89 const LookupBisectionCacheItemElem *a = a_;
90 const LookupBisectionCacheItemElem *b = b_;
91 int res;
92
93 if (a->u.f < b->u.f)
94 res = -1;
95 else if (a->u.f > b->u.f)
96 res = +1;
97 else
98 res = 0;
99 return res;
100 }
101
102 /* -------------------------------------------------------------------------- */
103
104 static gboolean debug_lookup_caches;
105 static GStringChunk *lookup_string_pool;
106 static GOMemChunk *lookup_float_pool;
107 static GHashTable *linear_hlookup_string_cache;
108 static GHashTable *linear_hlookup_float_cache;
109 static GHashTable *linear_hlookup_bool_cache;
110 static GHashTable *linear_vlookup_string_cache;
111 static GHashTable *linear_vlookup_float_cache;
112 static GHashTable *linear_vlookup_bool_cache;
113 static GHashTable *bisection_hlookup_string_cache;
114 static GHashTable *bisection_hlookup_float_cache;
115 static GHashTable *bisection_hlookup_bool_cache;
116 static GHashTable *bisection_vlookup_string_cache;
117 static GHashTable *bisection_vlookup_float_cache;
118 static GHashTable *bisection_vlookup_bool_cache;
119 static size_t total_cache_size;
120 static size_t protect_string_pool;
121 static size_t protect_float_pool;
122
123 static void
124 clear_caches (void)
125 {
126 if (!linear_hlookup_string_cache)
127 return;
128
129 if (debug_lookup_caches)
130 g_printerr ("Clearing lookup caches [%ld]\n", (long)total_cache_size);
131
132 total_cache_size = 0;
133
134 /* ---------- */
135
136 g_hash_table_destroy (linear_hlookup_string_cache);
137 linear_hlookup_string_cache = NULL;
138
139 g_hash_table_destroy (linear_hlookup_float_cache);
140 linear_hlookup_float_cache = NULL;
141
142 g_hash_table_destroy (linear_hlookup_bool_cache);
143 linear_hlookup_bool_cache = NULL;
144
145 /* ---------- */
146
147 g_hash_table_destroy (linear_vlookup_string_cache);
148 linear_vlookup_string_cache = NULL;
149
150 g_hash_table_destroy (linear_vlookup_float_cache);
151 linear_vlookup_float_cache = NULL;
152
153 g_hash_table_destroy (linear_vlookup_bool_cache);
154 linear_vlookup_bool_cache = NULL;
155
156 /* ---------- */
157
158 g_hash_table_destroy (bisection_hlookup_string_cache);
159 bisection_hlookup_string_cache = NULL;
160
161 g_hash_table_destroy (bisection_hlookup_float_cache);
162 bisection_hlookup_float_cache = NULL;
163
164 g_hash_table_destroy (bisection_hlookup_bool_cache);
165 bisection_hlookup_bool_cache = NULL;
166
167 /* ---------- */
168
169 g_hash_table_destroy (bisection_vlookup_string_cache);
170 bisection_vlookup_string_cache = NULL;
171
172 g_hash_table_destroy (bisection_vlookup_float_cache);
173 bisection_vlookup_float_cache = NULL;
174
175 g_hash_table_destroy (bisection_vlookup_bool_cache);
176 bisection_vlookup_bool_cache = NULL;
177
178 /* ---------- */
179
180 if (!protect_string_pool) {
181 g_string_chunk_free (lookup_string_pool);
182 lookup_string_pool = NULL;
183 }
184
185 if (!protect_float_pool) {
186 go_mem_chunk_destroy (lookup_float_pool, TRUE);
187 lookup_float_pool = NULL;
188 }
189 }
190
191 static void
192 create_caches (void)
193 {
194 if (linear_hlookup_string_cache)
195 return;
196
197 total_cache_size = 0;
198
199 if (!lookup_string_pool)
200 lookup_string_pool = g_string_chunk_new (100 * 1024);
201
202 if (!lookup_float_pool)
203 lookup_float_pool =
204 go_mem_chunk_new ("lookup float pool",
205 sizeof (gnm_float),
206 sizeof (gnm_float) * 1000);
207
208 linear_hlookup_string_cache = g_hash_table_new_full
209 ((GHashFunc)value_hash,
210 (GEqualFunc)value_equal,
211 (GDestroyNotify)value_release,
212 (GDestroyNotify)g_hash_table_destroy);
213 linear_hlookup_float_cache = g_hash_table_new_full
214 ((GHashFunc)value_hash,
215 (GEqualFunc)value_equal,
216 (GDestroyNotify)value_release,
217 (GDestroyNotify)g_hash_table_destroy);
218 linear_hlookup_bool_cache = g_hash_table_new_full
219 ((GHashFunc)value_hash,
220 (GEqualFunc)value_equal,
221 (GDestroyNotify)value_release,
222 (GDestroyNotify)g_hash_table_destroy);
223
224 linear_vlookup_string_cache = g_hash_table_new_full
225 ((GHashFunc)value_hash,
226 (GEqualFunc)value_equal,
227 (GDestroyNotify)value_release,
228 (GDestroyNotify)g_hash_table_destroy);
229 linear_vlookup_float_cache = g_hash_table_new_full
230 ((GHashFunc)value_hash,
231 (GEqualFunc)value_equal,
232 (GDestroyNotify)value_release,
233 (GDestroyNotify)g_hash_table_destroy);
234 linear_vlookup_bool_cache = g_hash_table_new_full
235 ((GHashFunc)value_hash,
236 (GEqualFunc)value_equal,
237 (GDestroyNotify)value_release,
238 (GDestroyNotify)g_hash_table_destroy);
239
240 bisection_hlookup_string_cache = g_hash_table_new_full
241 ((GHashFunc)value_hash,
242 (GEqualFunc)value_equal,
243 (GDestroyNotify)value_release,
244 (GDestroyNotify)lookup_bisection_cache_item_free);
245 bisection_hlookup_float_cache = g_hash_table_new_full
246 ((GHashFunc)value_hash,
247 (GEqualFunc)value_equal,
248 (GDestroyNotify)value_release,
249 (GDestroyNotify)lookup_bisection_cache_item_free);
250 bisection_hlookup_bool_cache = g_hash_table_new_full
251 ((GHashFunc)value_hash,
252 (GEqualFunc)value_equal,
253 (GDestroyNotify)value_release,
254 (GDestroyNotify)lookup_bisection_cache_item_free);
255
256 bisection_vlookup_string_cache = g_hash_table_new_full
257 ((GHashFunc)value_hash,
258 (GEqualFunc)value_equal,
259 (GDestroyNotify)value_release,
260 (GDestroyNotify)lookup_bisection_cache_item_free);
261 bisection_vlookup_float_cache = g_hash_table_new_full
262 ((GHashFunc)value_hash,
263 (GEqualFunc)value_equal,
264 (GDestroyNotify)value_release,
265 (GDestroyNotify)lookup_bisection_cache_item_free);
266 bisection_vlookup_bool_cache = g_hash_table_new_full
267 ((GHashFunc)value_hash,
268 (GEqualFunc)value_equal,
269 (GDestroyNotify)value_release,
270 (GDestroyNotify)lookup_bisection_cache_item_free);
271 }
272
273 static void
274 prune_caches (void)
275 {
276 if (total_cache_size > 10 * GNM_DEFAULT_ROWS) {
277 clear_caches ();
278 create_caches ();
279 }
280 }
281
282 /* -------------------------------------------------------------------------- */
283
284 /*
285 * We use an extra level of pointers for "cache" here to avoid problems
286 * in the case where we later prune the caches. The pointer to the
287 * GHashTable* will stay valid.
288 */
289 typedef struct {
290 gboolean is_new;
291 GnmValue *key_copy;
292 GHashTable *h, **cache;
293 } LinearLookupInfo;
294
295 static GHashTable *
296 get_linear_lookup_cache (GnmFuncEvalInfo *ei,
297 GnmValue const *data, GnmValueType datatype,
298 gboolean vertical, LinearLookupInfo *pinfo)
299 {
300 GnmValue const *key;
301
302 pinfo->is_new = FALSE;
303 pinfo->key_copy = NULL;
304
305 create_caches ();
306
307 switch (datatype) {
308 case VALUE_STRING:
309 pinfo->cache = vertical
310 ? &linear_vlookup_string_cache
311 : &linear_hlookup_string_cache;
312 break;
313 case VALUE_FLOAT:
314 pinfo->cache = vertical
315 ? &linear_vlookup_float_cache
316 : &linear_hlookup_float_cache;
317 break;
318 case VALUE_BOOLEAN:
319 pinfo->cache = vertical
320 ? &linear_vlookup_bool_cache
321 : &linear_hlookup_bool_cache;
322 break;
323 default:
324 g_assert_not_reached ();
325 return NULL;
326 }
327
328 switch (data->v_any.type) {
329 case VALUE_CELLRANGE: {
330 GnmSheetRange sr;
331 GnmRangeRef const *rr = value_get_rangeref (data);
332 Sheet *end_sheet;
333 gnm_rangeref_normalize (rr, ei->pos, &sr.sheet, &end_sheet,
334 &sr.range);
335 if (sr.sheet != end_sheet)
336 return NULL; /* 3D */
337
338 key = pinfo->key_copy =
339 value_new_cellrange_r (sr.sheet, &sr.range);
340 break;
341 }
342 case VALUE_ARRAY:
343 key = data;
344 break;
345 default:
346 return NULL;
347 }
348
349 pinfo->h = g_hash_table_lookup (*pinfo->cache, key);
350 if (pinfo->h == NULL) {
351 prune_caches ();
352 pinfo->is_new = TRUE;
353 if (datatype == VALUE_STRING)
354 pinfo->h = g_hash_table_new (g_str_hash, g_str_equal);
355 else
356 pinfo->h = g_hash_table_new
357 ((GHashFunc)gnm_float_hash,
358 (GEqualFunc)gnm_float_equal);
359 if (!pinfo->key_copy)
360 pinfo->key_copy = value_dup (key);
361 } else {
362 value_release (pinfo->key_copy);
363 pinfo->key_copy = NULL;
364 }
365
366 return pinfo->h;
367 }
368
369 static void
370 linear_lookup_cache_commit (LinearLookupInfo *pinfo)
371 {
372 /*
373 * It is possible to have an entry for the key already in the
374 * cache. In that case we fail to deduct from total_cache_size
375 * but it really doesn't matter.
376 */
377 total_cache_size += g_hash_table_size (pinfo->h);
378
379 g_hash_table_replace (*pinfo->cache, pinfo->key_copy, pinfo->h);
380 }
381
382 /*
383 * We use an extra level of pointers for "cache" here to avoid problems
384 * in the case where we later prune the caches. The pointer to the
385 * GHashTable* will stay valid.
386 */
387 typedef struct {
388 gboolean is_new;
389 GnmValue *key_copy;
390 GHashTable **cache;
391 LookupBisectionCacheItem *item;
392 } BisectionLookupInfo;
393
394 static LookupBisectionCacheItem *
395 get_bisection_lookup_cache (GnmFuncEvalInfo *ei,
396 GnmValue const *data, GnmValueType datatype,
397 gboolean vertical, BisectionLookupInfo *pinfo)
398 {
399 GnmValue const *key;
400
401 pinfo->is_new = FALSE;
402 pinfo->key_copy = NULL;
403
404 create_caches ();
405
406 /* The "&" here is for the pruning case. */
407 switch (datatype) {
408 case VALUE_STRING:
409 pinfo->cache = vertical
410 ? &bisection_vlookup_string_cache
411 : &bisection_hlookup_string_cache;
412 break;
413 case VALUE_FLOAT:
414 pinfo->cache = vertical
415 ? &bisection_vlookup_float_cache
416 : &bisection_hlookup_float_cache;
417 break;
418 case VALUE_BOOLEAN:
419 pinfo->cache = vertical
420 ? &bisection_vlookup_bool_cache
421 : &bisection_hlookup_bool_cache;
422 break;
423 default:
424 g_assert_not_reached ();
425 return NULL;
426 }
427
428 switch (data->v_any.type) {
429 case VALUE_CELLRANGE: {
430 GnmSheetRange sr;
431 GnmRangeRef const *rr = value_get_rangeref (data);
432 Sheet *end_sheet;
433 gnm_rangeref_normalize (rr, ei->pos, &sr.sheet, &end_sheet,
434 &sr.range);
435 if (sr.sheet != end_sheet)
436 return NULL; /* 3D */
437
438 key = pinfo->key_copy = value_new_cellrange_r (sr.sheet, &sr.range);
439 break;
440 }
441 case VALUE_ARRAY:
442 key = data;
443 break;
444 default:
445 return NULL;
446 }
447
448 pinfo->item = g_hash_table_lookup (*pinfo->cache, key);
449 if (pinfo->item == NULL) {
450 prune_caches ();
451 pinfo->is_new = TRUE;
452 pinfo->item = g_new0 (LookupBisectionCacheItem, 1);
453 if (!pinfo->key_copy)
454 pinfo->key_copy = value_dup (key);
455 } else {
456 value_release (pinfo->key_copy);
457 pinfo->key_copy = NULL;
458 }
459
460 return pinfo->item;
461 }
462
463 static void
464 bisection_lookup_cache_commit (BisectionLookupInfo *pinfo)
465 {
466 /*
467 * It is possible to have an entry for the key already in the
468 * cache. In that case we fail to deduct from total_cache_size
469 * but it really doesn't matter.
470 */
471 total_cache_size += pinfo->item->n;
472
473 g_hash_table_replace (*pinfo->cache, pinfo->key_copy, pinfo->item);
474 }
475
476
477 /* -------------------------------------------------------------------------- */
478
479 static gboolean
480 find_type_valid (GnmValue const *find)
481 {
482 /* Excel does not lookup errors or blanks */
483 if (VALUE_IS_EMPTY (find))
484 return FALSE;
485 return VALUE_IS_NUMBER (find) || VALUE_IS_STRING (find);
486 }
487
488 static gboolean
489 find_compare_type_valid (GnmValue const *find, GnmValue const *val)
490 {
491 if (!val)
492 return FALSE;
493
494 if (find->v_any.type == val->v_any.type)
495 return TRUE;
496
497 /* Note: floats do not match bools. */
498
499 return FALSE;
500 }
501
502 /* -------------------------------------------------------------------------- */
503
504 static gboolean
505 is_pattern_match (const char *s)
506 {
507 while (*s) {
508 if (*s == '*' || *s == '?' || *s == '~')
509 return TRUE;
510 s++;
511 }
512 return FALSE;
513 }
514
515 static int
516 calc_length (GnmValue const *data, GnmEvalPos const *ep, gboolean vertical)
517 {
518 if (vertical)
519 return value_area_get_height (data, ep);
520 else
521 return value_area_get_width (data, ep);
522 }
523
524 static const GnmValue *
525 get_elem (GnmValue const *data, guint ui,
526 GnmEvalPos const *ep, gboolean vertical)
527 {
528 if (vertical)
529 return value_area_get_x_y (data, 0, ui, ep);
530 else
531 return value_area_get_x_y (data, ui, 0, ep);
532 }
533
534 enum { LOOKUP_NOT_THERE = -1, LOOKUP_DATA_ERROR = -2 };
535
536
537 static int
538 find_index_linear_equal_string (GnmFuncEvalInfo *ei,
539 GnmValue const *find, GnmValue const *data,
540 gboolean vertical)
541 {
542 GHashTable *h;
543 gpointer pres;
544 char *sc;
545 gboolean found;
546 LinearLookupInfo info;
547
548 h = get_linear_lookup_cache (ei, data, VALUE_STRING, vertical,
549 &info);
550 if (!h)
551 return LOOKUP_DATA_ERROR;
552
553 if (info.is_new) {
554 int lp, length = calc_length (data, ei->pos, vertical);
555
556 protect_string_pool++;
557
558 for (lp = 0; lp < length; lp++) {
559 GnmValue const *v = get_elem (data, lp, ei->pos, vertical);
560 char *vc;
561
562 if (!find_compare_type_valid (find, v))
563 continue;
564
565 vc = g_utf8_casefold (value_peek_string (v), -1);
566 if (!g_hash_table_lookup_extended (h, vc, NULL, NULL)) {
567 char *sc = g_string_chunk_insert (lookup_string_pool, vc);
568 g_hash_table_insert (h, sc, GINT_TO_POINTER (lp));
569 }
570
571 g_free (vc);
572 }
573
574 linear_lookup_cache_commit (&info);
575
576 protect_string_pool--;
577 }
578
579 sc = g_utf8_casefold (value_peek_string (find), -1);
580 found = g_hash_table_lookup_extended (h, sc, NULL, &pres);
581 g_free (sc);
582
583 return found ? GPOINTER_TO_INT (pres) : LOOKUP_NOT_THERE;
584 }
585
586 static int
587 find_index_linear_equal_float (GnmFuncEvalInfo *ei,
588 GnmValue const *find, GnmValue const *data,
589 gboolean vertical)
590 {
591 GHashTable *h;
592 gpointer pres;
593 gnm_float f;
594 gboolean found;
595 LinearLookupInfo info;
596
597 /* This handles floats and bools, but with separate caches. */
598 h = get_linear_lookup_cache (ei, data, find->v_any.type, vertical,
599 &info);
600 if (!h)
601 return LOOKUP_DATA_ERROR;
602
603 if (info.is_new) {
604 int lp, length = calc_length (data, ei->pos, vertical);
605
606 protect_float_pool++;
607
608 for (lp = 0; lp < length; lp++) {
609 GnmValue const *v = get_elem (data, lp, ei->pos, vertical);
610 gnm_float f2;
611
612 if (!find_compare_type_valid (find, v))
613 continue;
614
615 f2 = value_get_as_float (v);
616
617 if (!g_hash_table_lookup_extended (h, &f2, NULL, NULL)) {
618 gnm_float *fp = go_mem_chunk_alloc (lookup_float_pool);
619 *fp = f2;
620 g_hash_table_insert (h, fp, GINT_TO_POINTER (lp));
621 }
622 }
623
624 linear_lookup_cache_commit (&info);
625
626 protect_float_pool--;
627 }
628
629 f = value_get_as_float (find);
630 found = g_hash_table_lookup_extended (h, &f, NULL, &pres);
631
632 return found ? GPOINTER_TO_INT (pres) : LOOKUP_NOT_THERE;
633 }
634
635 static int
636 find_index_linear (GnmFuncEvalInfo *ei,
637 GnmValue const *find, GnmValue const *data,
638 gboolean vertical)
639 {
640 if (VALUE_IS_STRING (find))
641 return find_index_linear_equal_string
642 (ei, find, data, vertical);
643
644 if (VALUE_IS_NUMBER (find))
645 return find_index_linear_equal_float
646 (ei, find, data, vertical);
647
648 /* I don't think we can get here. */
649 return LOOKUP_DATA_ERROR;
650 }
651
652
653 static int
654 wildcard_string_match (const char *key, LookupBisectionCacheItem *bc)
655 {
656 GORegexp rx;
657 GORegmatch rm;
658 int i, res = LOOKUP_NOT_THERE;
659
660 /* FIXME: Do we want to anchor at the end here? */
661 if (gnm_regcomp_XL (&rx, key, GO_REG_ICASE, TRUE, TRUE) != GO_REG_OK) {
662 g_warning ("Unexpected regcomp result");
663 return LOOKUP_DATA_ERROR;
664 }
665
666 for (i = 0; i < bc->n; i++) {
667 if (go_regexec (&rx, bc->data[i].u.str, 1, &rm, 0) == GO_REG_OK) {
668 res = bc->data[i].index;
669 break;
670 }
671 }
672
673 go_regfree (&rx);
674 return res;
675 }
676
677 #undef DEBUG_BISECTION
678
679 static int
680 find_index_bisection (GnmFuncEvalInfo *ei,
681 GnmValue const *find, GnmValue const *data,
682 gint type, gboolean vertical)
683 {
684 int high, low, lastlow, res;
685 LookupBisectionCacheItem *bc;
686 gboolean stringp;
687 int (*comparer) (const void *,const void *);
688 LookupBisectionCacheItemElem key;
689 BisectionLookupInfo info;
690
691 bc = get_bisection_lookup_cache (ei, data, find->v_any.type, vertical,
692 &info);
693 if (!bc)
694 return LOOKUP_DATA_ERROR;
695
696 stringp = VALUE_IS_STRING (find);
697 comparer = stringp ? bisection_compare_string : bisection_compare_float;
698
699 if (info.is_new) {
700 int lp, length = calc_length (data, ei->pos, vertical);
701
702 bc->data = g_new (LookupBisectionCacheItemElem, length + 1);
703
704 if (stringp)
705 protect_string_pool++;
706
707 for (lp = 0; lp < length; lp++) {
708 GnmValue const *v = get_elem (data, lp, ei->pos, vertical);
709 if (!find_compare_type_valid (find, v))
710 continue;
711
712 if (stringp) {
713 char *vc = g_utf8_casefold (value_peek_string (v), -1);
714 bc->data[bc->n].u.str = g_string_chunk_insert (lookup_string_pool, vc);
715 g_free (vc);
716 } else
717 bc->data[bc->n].u.f = value_get_as_float (v);
718
719 bc->data[bc->n].index = lp;
720 bc->n++;
721 }
722
723 bc->data = g_renew (LookupBisectionCacheItemElem,
724 bc->data,
725 bc->n);
726 bisection_lookup_cache_commit (&info);
727
728 if (stringp)
729 protect_string_pool--;
730 }
731
732 #ifdef DEBUG_BISECTION
733 g_printerr ("find=%s\n", value_peek_string (find));
734 #endif
735
736 if (type == 0)
737 return wildcard_string_match (value_peek_string (find), bc);
738
739 if (stringp) {
740 char *vc = g_utf8_casefold (value_peek_string (find), -1);
741 key.u.str = g_string_chunk_insert (lookup_string_pool, vc);
742 g_free (vc);
743 } else {
744 #ifdef DEBUG_BISECTION
745 int lp;
746 for (lp = 0; lp < bc->n; lp++) {
747 g_printerr ("Data %d: %g\n", lp, bc->data[lp].u.f);
748 }
749 #endif
750 key.u.f = value_get_as_float (find);
751 }
752
753 lastlow = LOOKUP_NOT_THERE;
754 low = 0;
755 high = bc->n - 1;
756 while (low <= high) {
757 int mid = (low + high) / 2;
758 int c = comparer (&key, bc->data + mid);
759 #ifdef DEBUG_BISECTION
760 if (!stringp) {
761 g_printerr ("Comparing to #%d %g: %d\n",
762 mid, bc->data[mid].u.f, c);
763 }
764 #endif
765 if (c == 0) {
766 /*
767 * For some sick reason, XLS wants to take the first
768 * or last match, depending on type. We could put
769 * this into the cache if we wanted to.
770 */
771 int dir = (type > 0 ? +1 : -1);
772 while (mid + dir > 0 &&
773 mid + dir < bc->n &&
774 comparer (&key, bc->data + (mid + dir)) == 0)
775 mid += dir;
776 return bc->data[mid].index;
777 }
778 if (type < 0)
779 c = -c; /* Reverse sorted data. */
780 if (c > 0) {
781 lastlow = mid;
782 low = mid + 1;
783 } else {
784 high = mid - 1;
785 }
786 }
787
788 if (type == 0)
789 res = LOOKUP_NOT_THERE;
790 else
791 res = lastlow;
792
793 #ifdef DEBUG_BISECTION
794 g_printerr ("res=%d\n", res);
795 #endif
796 if (res >= 0)
797 res = bc->data[res].index;
798 #ifdef DEBUG_BISECTION
799 g_printerr (" index=%d\n", res);
800 #endif
801
802 return res;
803 }
804
805 /***************************************************************************/
806
807 static GnmFuncHelp const help_address[] = {
808 { GNM_FUNC_HELP_NAME, F_("ADDRESS:cell address as text")},
809 { GNM_FUNC_HELP_ARG, F_("row_num:row number")},
810 { GNM_FUNC_HELP_ARG, F_("col_num:column number")},
811 { GNM_FUNC_HELP_ARG, F_("abs_num:1 for an absolute, 2 for a row absolute and column "
812 "relative, 3 for a row relative and column absolute, "
813 "and 4 for a relative reference; defaults to 1")},
814 { GNM_FUNC_HELP_ARG, F_("a1:if TRUE, an A1-style reference is provided, "
815 "otherwise an R1C1-style reference; defaults to TRUE")},
816 { GNM_FUNC_HELP_ARG, F_("text:name of the worksheet, defaults to no sheet")},
817 { GNM_FUNC_HELP_NOTE, F_("If @{row_num} or @{col_num} is less than one, ADDRESS returns "
818 "#VALUE!")},
819 { GNM_FUNC_HELP_NOTE, F_("If @{abs_num} is greater than 4 ADDRESS returns #VALUE!")},
820 { GNM_FUNC_HELP_EXAMPLES, "=ADDRESS(5,4)" },
821 { GNM_FUNC_HELP_EXAMPLES, "=ADDRESS(5,4,4)" },
822 { GNM_FUNC_HELP_EXAMPLES, "=ADDRESS(5,4,4,FALSE)" },
823 { GNM_FUNC_HELP_EXAMPLES, "=ADDRESS(5,4,4,FALSE,\"Sheet99\")" },
824 { GNM_FUNC_HELP_SEEALSO, "COLUMNNUMBER"},
825 { GNM_FUNC_HELP_END}
826 };
827
828 static GnmValue *
829 gnumeric_address (GnmFuncEvalInfo *ei, GnmValue const * const *args)
830 {
831 GnmConventionsOut out;
832 GnmCellRef ref;
833 GnmParsePos pp;
834 gboolean err;
835 int col, row;
836 Sheet *sheet = NULL;
837 const char *sheet_name = args[4] ? value_peek_string (args[4]) : NULL;
838
839 switch (args[2] ? value_get_as_int (args[2]) : 1) {
840 case 1: case 5: ref.col_relative = ref.row_relative = FALSE; break;
841 case 2: case 6:
842 ref.col_relative = TRUE;
843 ref.row_relative = FALSE;
844 break;
845 case 3: case 7:
846 ref.col_relative = FALSE;
847 ref.row_relative = TRUE;
848 break;
849 case 4: case 8: ref.col_relative = ref.row_relative = TRUE; break;
850
851 default :
852 return value_new_error_VALUE (ei->pos);
853 }
854
855 if (sheet_name)
856 sheet = workbook_sheet_by_name (ei->pos->sheet->workbook,
857 sheet_name);
858 /* For unknown or missing sheet, use current sheet. */
859 if (!sheet)
860 sheet = ei->pos->sheet;
861
862 ref.sheet = NULL;
863 row = ref.row = value_get_as_int (args[0]) - 1;
864 col = ref.col = value_get_as_int (args[1]) - 1;
865 out.pp = parse_pos_init_evalpos (&pp, ei->pos);
866 out.convs = gnm_conventions_default;
867
868 if (NULL != args[3]) {
869 /* MS Excel is ridiculous. This is a special case */
870 if (!value_get_as_bool (args[3], &err)) {
871 out.convs = gnm_conventions_xls_r1c1;
872 if (ref.col_relative)
873 col = ei->pos->eval.col + (++ref.col);
874 if (ref.row_relative)
875 row = ei->pos->eval.row + (++ref.row);
876 }
877 if (err)
878 return value_new_error_VALUE (ei->pos);
879 }
880
881 if (col < 0 || col >= gnm_sheet_get_max_cols (sheet))
882 return value_new_error_VALUE (ei->pos);
883 if (row < 0 || row >= gnm_sheet_get_max_rows (sheet))
884 return value_new_error_VALUE (ei->pos);
885
886 if (!out.convs->r1c1_addresses)
887 pp.eval.col = pp.eval.row = 0;
888
889 if (sheet_name && sheet_name[0]) {
890 out.accum = gnm_expr_conv_quote (out.convs, sheet_name);
891 g_string_append_c (out.accum, out.convs->sheet_name_sep);
892 } else if (sheet_name) {
893 /* A crazy case. Invalid name, but ends up unquoted. */
894 out.accum = g_string_new (NULL);
895 g_string_append_c (out.accum, out.convs->sheet_name_sep);
896 } else
897 out.accum = g_string_new (NULL);
898 cellref_as_string (&out, &ref, TRUE);
899
900 return value_new_string_nocopy (g_string_free (out.accum, FALSE));
901 }
902
903 /***************************************************************************/
904
905 static GnmFuncHelp const help_areas[] = {
906 { GNM_FUNC_HELP_NAME, F_("AREAS:number of areas in @{reference}")},
907 { GNM_FUNC_HELP_ARG, F_("reference:range")},
908 { GNM_FUNC_HELP_EXAMPLES, "=AREAS(A1,B2,C3)" },
909 { GNM_FUNC_HELP_SEEALSO, "ADDRESS,INDEX,INDIRECT,OFFSET"},
910 { GNM_FUNC_HELP_END}
911 };
912
913 /* TODO : we need to rethink EXPR_SET as an operator vs a value type */
914 static GnmValue *
915 gnumeric_areas (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
916 {
917 GnmExpr const *expr;
918 int res = -1;
919
920 if (argc != 1 || argv[0] == NULL)
921 return value_new_error_VALUE (ei->pos);
922 expr = argv[0];
923
924 restart:
925 switch (GNM_EXPR_GET_OPER (expr)) {
926 case GNM_EXPR_OP_CONSTANT:
927 if (VALUE_IS_ERROR (expr->constant.value))
928 return value_dup (expr->constant.value);
929 if (!VALUE_IS_CELLRANGE (expr->constant.value))
930 break;
931
932 case GNM_EXPR_OP_CELLREF:
933 case GNM_EXPR_OP_RANGE_CTOR:
934 case GNM_EXPR_OP_INTERSECT:
935 res = 1;
936 break;
937
938 case GNM_EXPR_OP_FUNCALL: {
939 GnmValue *v = gnm_expr_eval (expr, ei->pos,
940 GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
941 if (VALUE_IS_CELLRANGE (v))
942 res = 1;
943 value_release (v);
944 break;
945 }
946
947 case GNM_EXPR_OP_NAME:
948 if (expr_name_is_active (expr->name.name)) {
949 expr = expr->name.name->texpr->expr;
950 goto restart;
951 }
952 break;
953
954 case GNM_EXPR_OP_SET:
955 res = expr->set.argc;
956 break;
957
958 case GNM_EXPR_OP_PAREN:
959 expr = expr->unary.value;
960 goto restart;
961
962 default:
963 break;
964 }
965
966 if (res > 0)
967 return value_new_int (res);
968 return value_new_error_VALUE (ei->pos);
969 }
970
971 /***************************************************************************/
972
973 static GnmFuncHelp const help_choose[] = {
974 { GNM_FUNC_HELP_NAME, F_("CHOOSE:the (@{index}+1)th argument")},
975 { GNM_FUNC_HELP_ARG, F_("index:positive number")},
976 { GNM_FUNC_HELP_ARG, F_("value1:first value")},
977 { GNM_FUNC_HELP_ARG, F_("value2:second value")},
978 { GNM_FUNC_HELP_DESCRIPTION, F_("CHOOSE returns its (@{index}+1)th argument.")},
979 { GNM_FUNC_HELP_NOTE, F_("@{index} is truncated to an integer. If @{index} < 1 "
980 "or the truncated @{index} > number of values, CHOOSE "
981 "returns #VALUE!")},
982 { GNM_FUNC_HELP_EXAMPLES, "=CHOOSE(3,\"Apple\",\"Orange\",\"Grape\",\"Perry\")" },
983 { GNM_FUNC_HELP_SEEALSO, "IF"},
984 { GNM_FUNC_HELP_END}
985 };
986
987 static GnmValue *
988 gnumeric_choose (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
989 {
990 int index;
991 GnmValue *v;
992 int i;
993
994 if (argc < 1)
995 return value_new_error_VALUE (ei->pos);
996
997 #warning TODO add array eval
998 v = gnm_expr_eval (argv[0], ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
999 if (!v)
1000 return NULL;
1001
1002 if (!VALUE_IS_FLOAT (v)) {
1003 value_release (v);
1004 return value_new_error_VALUE (ei->pos);
1005 }
1006
1007 index = value_get_as_int (v);
1008 value_release (v);
1009 for (i = 1; i < argc; i++) {
1010 index--;
1011 if (!index)
1012 return gnm_expr_eval (argv[i], ei->pos,
1013 GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
1014 }
1015 return value_new_error_VALUE (ei->pos);
1016 }
1017
1018 /***************************************************************************/
1019
1020 static GnmFuncHelp const help_vlookup[] = {
1021 { GNM_FUNC_HELP_NAME, F_("VLOOKUP:search the first column of @{range} for @{value}")},
1022 { GNM_FUNC_HELP_ARG, F_("value:search value")},
1023 { GNM_FUNC_HELP_ARG, F_("range:range to search")},
1024 { GNM_FUNC_HELP_ARG, F_("column:1-based column offset indicating the return values")},
1025 { GNM_FUNC_HELP_ARG, F_("approximate:if false, an exact match of @{value} "
1026 "must be found; defaults to TRUE")},
1027 { GNM_FUNC_HELP_ARG, F_("as_index:if true, the 0-based row offset is "
1028 "returned; defaults to FALSE")},
1029 { GNM_FUNC_HELP_DESCRIPTION, F_("VLOOKUP function finds the row in @{range} that has a first "
1030 "cell similar to @{value}. If @{approximate} is not true it "
1031 "finds the row with an exact equality. If @{approximate} is "
1032 "true, it finds the last row with first value less than "
1033 "or equal to "
1034 "@{value}. If @{as_index} is true the 0-based row offset "
1035 "is returned.")},
1036 { GNM_FUNC_HELP_NOTE, F_("If @{approximate} is true, "
1037 "then the values must be sorted in order of ascending value.")},
1038 { GNM_FUNC_HELP_NOTE, F_("VLOOKUP returns #REF! if @{column} falls outside @{range}.")},
1039 { GNM_FUNC_HELP_SEEALSO, "HLOOKUP"},
1040 { GNM_FUNC_HELP_END}
1041 };
1042
1043 static GnmValue *
1044 gnumeric_vlookup (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1045 {
1046 GnmValue const *find = args[0];
1047 int col_idx = value_get_as_int (args[2]);
1048 gboolean approx = args[3] ? value_get_as_checked_bool (args[3]) : TRUE;
1049 gboolean as_index = args[4] && value_get_as_checked_bool (args[4]);
1050 int index;
1051 gboolean is_string_match;
1052
1053 if (!find_type_valid (find))
1054 return value_new_error_NA (ei->pos);
1055 if (col_idx <= 0)
1056 return value_new_error_VALUE (ei->pos);
1057 if (col_idx > value_area_get_width (args[1], ei->pos))
1058 return value_new_error_REF (ei->pos);
1059
1060 is_string_match = (!approx &&
1061 VALUE_IS_STRING (find) &&
1062 is_pattern_match (value_peek_string (find)));
1063
1064 index = is_string_match
1065 ? find_index_bisection (ei, find, args[1], 0, TRUE)
1066 : (approx
1067 ? find_index_bisection (ei, find, args[1], 1, TRUE)
1068 : find_index_linear (ei, find, args[1], TRUE));
1069 if (index == LOOKUP_DATA_ERROR)
1070 return value_new_error_VALUE (ei->pos); /* 3D */
1071
1072 if (as_index)
1073 return value_new_int (index);
1074
1075 if (index >= 0) {
1076 GnmValue const *v;
1077
1078 v = value_area_fetch_x_y (args[1], col_idx-1, index, ei->pos);
1079 g_return_val_if_fail (v != NULL, NULL);
1080 return value_dup (v);
1081 }
1082
1083 return value_new_error_NA (ei->pos);
1084 }
1085
1086 /***************************************************************************/
1087
1088 static GnmFuncHelp const help_hlookup[] = {
1089 { GNM_FUNC_HELP_NAME, F_("HLOOKUP:search the first row of @{range} for @{value}")},
1090 { GNM_FUNC_HELP_ARG, F_("value:search value")},
1091 { GNM_FUNC_HELP_ARG, F_("range:range to search")},
1092 { GNM_FUNC_HELP_ARG, F_("row:1-based row offset indicating the return values ")},
1093 { GNM_FUNC_HELP_ARG, F_("approximate:if false, an exact match of @{value} "
1094 "must be found; defaults to TRUE")},
1095 { GNM_FUNC_HELP_ARG, F_("as_index:if true, the 0-based column offset is "
1096 "returned; defaults to FALSE")},
1097 { GNM_FUNC_HELP_DESCRIPTION, F_("HLOOKUP function finds the row in @{range} that has a first "
1098 "cell similar to @{value}. If @{approximate} is not true it "
1099 "finds the column with an exact equality. If @{approximate} is "
1100 "true, it finds the last column with first value less than or "
1101 "equal to "
1102 "@{value}. If @{as_index} is true the 0-based column offset "
1103 "is returned.")},
1104 { GNM_FUNC_HELP_NOTE, F_("If @{approximate} is true, "
1105 "then the values must be sorted in order of ascending value.")},
1106 { GNM_FUNC_HELP_NOTE, F_("HLOOKUP returns #REF! if @{row} falls outside @{range}.")},
1107 { GNM_FUNC_HELP_SEEALSO, "VLOOKUP"},
1108 { GNM_FUNC_HELP_END}
1109 };
1110
1111 static GnmValue *
1112 gnumeric_hlookup (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1113 {
1114 GnmValue const *find = args[0];
1115 int row_idx = value_get_as_int (args[2]);
1116 gboolean approx = args[3] ? value_get_as_checked_bool (args[3]) : TRUE;
1117 gboolean as_index = args[4] && value_get_as_checked_bool (args[4]);
1118 int index;
1119 gboolean is_string_match;
1120
1121 if (!find_type_valid (find))
1122 return value_new_error_NA (ei->pos);
1123 if (row_idx <= 0)
1124 return value_new_error_VALUE (ei->pos);
1125 if (row_idx > value_area_get_height (args[1], ei->pos))
1126 return value_new_error_REF (ei->pos);
1127
1128 is_string_match = (!approx &&
1129 VALUE_IS_STRING (find) &&
1130 is_pattern_match (value_peek_string (find)));
1131
1132 index = is_string_match
1133 ? find_index_bisection (ei, find, args[1], 0, FALSE)
1134 : (approx
1135 ? find_index_bisection (ei, find, args[1], 1, FALSE)
1136 : find_index_linear (ei, find, args[1], FALSE));
1137 if (index == LOOKUP_DATA_ERROR)
1138 return value_new_error_VALUE (ei->pos); /* 3D */
1139
1140 if (as_index)
1141 return value_new_int (index);
1142
1143 if (index >= 0) {
1144 GnmValue const *v;
1145
1146 v = value_area_fetch_x_y (args[1], index, row_idx-1, ei->pos);
1147 g_return_val_if_fail (v != NULL, NULL);
1148 return value_dup (v);
1149 }
1150
1151 return value_new_error_NA (ei->pos);
1152 }
1153
1154 /***************************************************************************/
1155
1156 static GnmFuncHelp const help_lookup[] = {
1157 { GNM_FUNC_HELP_NAME, F_("LOOKUP:contents of @{vector2} at the corresponding location to "
1158 "@{value} in @{vector1}")},
1159 { GNM_FUNC_HELP_ARG, F_("value:value to look up")},
1160 { GNM_FUNC_HELP_ARG, F_("vector1:range to search:")},
1161 { GNM_FUNC_HELP_ARG, F_("vector2:range of return values")},
1162 { GNM_FUNC_HELP_DESCRIPTION, F_("If @{vector1} has more rows than columns, LOOKUP searches "
1163 "the first row of @{vector1}, otherwise the first column. "
1164 "If @{vector2} is omitted the return value is taken from "
1165 "the last row or column of @{vector1}.")},
1166 { GNM_FUNC_HELP_NOTE, F_("If LOOKUP can't find @{value} it uses the largest value less "
1167 "than @{value}.")},
1168 { GNM_FUNC_HELP_NOTE, F_("The data must be sorted.")},
1169 { GNM_FUNC_HELP_NOTE, F_("If @{value} is smaller than the first value it returns #N/A.")},
1170 { GNM_FUNC_HELP_NOTE, F_("If the corresponding location does not exist in @{vector2}, "
1171 "it returns #N/A.")},
1172 { GNM_FUNC_HELP_SEEALSO, "VLOOKUP,HLOOKUP"},
1173 { GNM_FUNC_HELP_END}
1174 };
1175
1176 static GnmValue *
1177 gnumeric_lookup (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1178 {
1179 int index = -1;
1180 GnmValue const *v = args[0];
1181 GnmValue const *area = args[1];
1182 GnmValue const *lookup = args[2];
1183 GnmValue *result, *xlookup = NULL;
1184 gboolean vertical_search = (value_area_get_width (area, ei->pos) <
1185 value_area_get_height (area, ei->pos));
1186 gboolean vertical_lookup;
1187 gboolean is_cellrange;
1188
1189 if (!find_type_valid (v))
1190 return value_new_error_NA (ei->pos);
1191
1192 if (lookup) {
1193 int width = value_area_get_width (lookup, ei->pos);
1194 int height = value_area_get_height (lookup, ei->pos);
1195
1196 if (width > 1 && height > 1) {
1197 return value_new_error_NA (ei->pos);
1198 }
1199
1200 vertical_lookup = (width < height);
1201 is_cellrange = VALUE_IS_CELLRANGE (lookup);
1202 #if 0
1203 if (is_cellrange) {
1204 GnmRange r;
1205 /*
1206 * Extend the lookup range all the way. This is utterly
1207 * insane, but Excel really does reach beyond the stated
1208 * range.
1209 */
1210 range_init_value (&r, lookup, &ei->pos->eval);
1211 range_normalize (&r);
1212 if (vertical_lookup)
1213 r.end.row = gnm_sheet_get_last_row (ei->pos->sheet);
1214 else
1215 r.end.col = gnm_sheet_get_last_col (ei->pos->sheet);
1216
1217 lookup = xlookup = value_new_cellrange_r (ei->pos->sheet, &r);
1218 }
1219 #endif
1220 } else {
1221 lookup = area;
1222 vertical_lookup = vertical_search;
1223 is_cellrange = FALSE; /* Doesn't matter. */
1224 }
1225
1226 index = find_index_bisection (ei, v, area, 1, vertical_search);
1227
1228 if (index >= 0) {
1229 int width = value_area_get_width (lookup, ei->pos);
1230 int height = value_area_get_height (lookup, ei->pos);
1231 int x, y;
1232
1233 if (vertical_lookup)
1234 x = width - 1, y = index;
1235 else
1236 x = index, y = height - 1;
1237
1238 if (x < width && y < height)
1239 result = value_dup (value_area_fetch_x_y (lookup, x, y, ei->pos));
1240 else if (is_cellrange)
1241 /* We went off the sheet. */
1242 result = value_new_int (0);
1243 else
1244 /* We went outside an array. */
1245 result = value_new_error_NA (ei->pos);
1246 } else
1247 result = value_new_error_NA (ei->pos);
1248
1249 value_release (xlookup);
1250
1251 return result;
1252 }
1253
1254 /***************************************************************************/
1255
1256 static GnmFuncHelp const help_match[] = {
1257 { GNM_FUNC_HELP_NAME, F_("MATCH:the index of @{seek} in @{vector}")},
1258 { GNM_FUNC_HELP_ARG, F_("seek:value to find")},
1259 { GNM_FUNC_HELP_ARG, F_("vector:n by 1 or 1 by n range to be searched")},
1260 { GNM_FUNC_HELP_ARG, F_("type:+1 (the default) to find the largest value \xe2\x89\xa4 @{seek}, "
1261 "0 to find the first value = @{seek}, or"
1262 "-1 to find the smallest value \xe2\x89\xa5 @{seek}")},
1263 { GNM_FUNC_HELP_DESCRIPTION, F_("MATCH searches @{vector} for @{seek} and returns the 1-based index.")},
1264 { GNM_FUNC_HELP_NOTE, F_(" For @{type} = -1 the data must be sorted in descending order; "
1265 "for @{type} = +1 the data must be sorted in ascending order.")},
1266 { GNM_FUNC_HELP_NOTE, F_("If @{seek} could not be found, #N/A is returned.")},
1267 { GNM_FUNC_HELP_NOTE, F_("If @{vector} is neither n by 1 nor 1 by n, #N/A is returned.")},
1268 { GNM_FUNC_HELP_SEEALSO, "LOOKUP"},
1269 { GNM_FUNC_HELP_END}
1270 };
1271
1272 static GnmValue *
1273 gnumeric_match (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1274 {
1275 int type, index = -1;
1276 int width = value_area_get_width (args[1], ei->pos);
1277 int height = value_area_get_height (args[1], ei->pos);
1278 gboolean vertical;
1279 GnmValue const *find = args[0];
1280 gboolean is_string_match;
1281
1282 if (!find_type_valid (find))
1283 return value_new_error_NA (ei->pos);
1284
1285 if (width > 1 && height > 1)
1286 return value_new_error_NA (ei->pos);
1287 vertical = (width > 1 ? FALSE : TRUE);
1288
1289 type = VALUE_IS_EMPTY (args[2]) ? 1 : value_get_as_int (args[2]);
1290
1291 is_string_match = (type == 0 &&
1292 VALUE_IS_STRING (find) &&
1293 is_pattern_match (value_peek_string (find)));
1294
1295 if (type == 0 && !is_string_match)
1296 index = find_index_linear (ei, find, args[1], vertical);
1297 else
1298 index = find_index_bisection (ei, find, args[1], type,
1299 vertical);
1300
1301 switch (index) {
1302 case LOOKUP_DATA_ERROR: return value_new_error_VALUE (ei->pos);
1303 case LOOKUP_NOT_THERE: return value_new_error_NA (ei->pos);
1304 default: return value_new_int (index + 1);
1305 }
1306 }
1307
1308 /***************************************************************************/
1309
1310 static GnmFuncHelp const help_indirect[] = {
1311 { GNM_FUNC_HELP_NAME, F_("INDIRECT:contents of the cell pointed to by the @{ref_text} string")},
1312 { GNM_FUNC_HELP_ARG, F_("ref_text:textual reference")},
1313 { GNM_FUNC_HELP_ARG, F_("format:if true, @{ref_text} is given in A1-style, "
1314 "otherwise it is given in R1C1 style; defaults to true")},
1315 { GNM_FUNC_HELP_NOTE, F_("If @{ref_text} is not a valid reference in the style determined "
1316 "by @{format}, INDIRECT returns #REF!")},
1317 { GNM_FUNC_HELP_EXAMPLES, "If A1 contains 3.14 and A2 contains \"A1\", then\n"
1318 "INDIRECT(A2) equals 3.14." },
1319 { GNM_FUNC_HELP_SEEALSO, "AREAS,INDEX,CELL"},
1320 { GNM_FUNC_HELP_END}
1321 };
1322
1323 static GnmValue *
1324 gnumeric_indirect (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1325 {
1326 GnmParsePos pp;
1327 GnmValue *res = NULL;
1328 GnmExprTop const *texpr;
1329 char const *text = value_peek_string (args[0]);
1330 GnmConventions const *convs = gnm_conventions_default;
1331
1332 if (args[1] && !value_get_as_checked_bool (args[1]))
1333 convs = gnm_conventions_xls_r1c1;
1334
1335 texpr = gnm_expr_parse_str (text,
1336 parse_pos_init_evalpos (&pp, ei->pos),
1337 GNM_EXPR_PARSE_DEFAULT, convs, NULL);
1338
1339 if (texpr != NULL) {
1340 res = gnm_expr_top_get_range (texpr);
1341 gnm_expr_top_unref (texpr);
1342 }
1343 return (res != NULL) ? res : value_new_error_REF (ei->pos);
1344 }
1345
1346 /*****************************************************************************/
1347
1348 static GnmFuncHelp const help_index[] = {
1349 { GNM_FUNC_HELP_NAME, F_("INDEX:reference to a cell in the given @{array}")},
1350 { GNM_FUNC_HELP_ARG, F_("array:cell or inline array")},
1351 { GNM_FUNC_HELP_ARG, F_("row:desired row, defaults to 1")},
1352 { GNM_FUNC_HELP_ARG, F_("col:desired column, defaults to 1")},
1353 { GNM_FUNC_HELP_ARG, F_("area:from which area to select a cell, defaults to 1")},
1354 { GNM_FUNC_HELP_DESCRIPTION, F_("INDEX gives a reference to a cell "
1355 "in the given @{array}. "
1356 "The cell is selected by @{row} and @{col}, "
1357 "which count the rows and "
1358 "columns in the array.")},
1359 { GNM_FUNC_HELP_NOTE, F_("If the reference falls outside the range of @{array},"
1360 " INDEX returns #REF!")},
1361 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5"
1362 " contain numbers 11.4, "
1363 "17.3, 21.3, 25.9, and 40.1. Then INDEX(A1:A5,4,1,1) equals 25.9") },
1364 { GNM_FUNC_HELP_END}
1365 };
1366
1367 static GnmValue *
1368 gnumeric_index (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
1369 {
1370 GnmExpr const *source;
1371 int elem[3] = { 0, 0, 0 };
1372 int i = 0;
1373 gboolean valid;
1374 GnmValue *v, *res;
1375
1376 if (argc == 0 || argc > 4)
1377 return value_new_error_VALUE (ei->pos);
1378 source = argv[0];
1379
1380 /* This is crazy. */
1381 while (GNM_EXPR_GET_OPER (source) == GNM_EXPR_OP_PAREN)
1382 source = source->unary.value;
1383
1384 v = gnm_expr_eval (source, ei->pos, GNM_EXPR_EVAL_PERMIT_NON_SCALAR);
1385 if (VALUE_IS_ERROR (v))
1386 return v;
1387
1388 for (i = 0; i + 1 < argc && i < (int)G_N_ELEMENTS (elem); i++) {
1389 GnmValue *vi = value_coerce_to_number (
1390 gnm_expr_eval (argv[i + 1], ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY),
1391 &valid, ei->pos);
1392 if (!valid) {
1393 value_release (v);
1394 return vi;
1395 }
1396 elem[i] = value_get_as_int (vi) - 1;
1397 value_release (vi);
1398 }
1399
1400 if (GNM_EXPR_GET_OPER (source) == GNM_EXPR_OP_SET) {
1401 int w = value_area_get_height (v, ei->pos);
1402 int i = elem[2];
1403 GnmValue *vi;
1404 if (i < 0 || i >= w) {
1405 value_release (v);
1406 return value_new_error_REF (ei->pos);
1407 }
1408 vi = value_dup (value_area_fetch_x_y (v, 0, i, ei->pos));
1409 value_release (v);
1410 v = vi;
1411 } else if (elem[2] != 0) {
1412 value_release (v);
1413 return value_new_error_REF (ei->pos);
1414 }
1415
1416 if (elem[1] < 0 ||
1417 elem[1] >= value_area_get_width (v, ei->pos) ||
1418 elem[0] < 0 ||
1419 elem[0] >= value_area_get_height (v, ei->pos)) {
1420 value_release (v);
1421 return value_new_error_REF (ei->pos);
1422 }
1423
1424 #warning Work out a way to fall back to returning value when a reference is unneeded
1425 if (VALUE_IS_CELLRANGE (v)) {
1426 GnmRangeRef const *src = &v->v_range.cell;
1427 GnmCellRef a = src->a, b = src->b;
1428 Sheet *start_sheet, *end_sheet;
1429 GnmRange r;
1430
1431 gnm_rangeref_normalize (src, ei->pos, &start_sheet, &end_sheet, &r);
1432 r.start.row += elem[0];
1433 r.start.col += elem[1];
1434 a.row = r.start.row; if (a.row_relative) a.row -= ei->pos->eval.row;
1435 b.row = r.start.row; if (b.row_relative) b.row -= ei->pos->eval.row;
1436 a.col = r.start.col; if (a.col_relative) a.col -= ei->pos->eval.col;
1437 b.col = r.start.col; if (b.col_relative) b.col -= ei->pos->eval.col;
1438 res = value_new_cellrange_unsafe (&a, &b);
1439 } else if (VALUE_IS_ARRAY (v))
1440 res = value_dup (value_area_fetch_x_y (v, elem[1], elem[0], ei->pos));
1441 else
1442 res = value_new_error_REF (ei->pos);
1443 value_release (v);
1444 return res;
1445 }
1446
1447 /***************************************************************************/
1448
1449 static GnmFuncHelp const help_column[] = {
1450 { GNM_FUNC_HELP_NAME, F_("COLUMN:vector of column numbers") },
1451 { GNM_FUNC_HELP_ARG, F_("x:reference, defaults to the position of the current expression") },
1452 { GNM_FUNC_HELP_DESCRIPTION, F_("COLUMN function returns a Nx1 array containing the sequence "
1453 "of integers "
1454 "from the first column to the last column of @{x}.") },
1455 { GNM_FUNC_HELP_NOTE, F_("If @{x} is neither an array nor a reference nor a range, "
1456 "returns #VALUE!") },
1457 { GNM_FUNC_HELP_EXAMPLES, "=COLUMN(A1:C4)" },
1458 { GNM_FUNC_HELP_EXAMPLES, "=COLUMN(A:C)" },
1459 { GNM_FUNC_HELP_EXAMPLES, F_("column() in G13 equals 7.") },
1460 { GNM_FUNC_HELP_SEEALSO, "COLUMNS,ROW,ROWS" },
1461 { GNM_FUNC_HELP_END }
1462 };
1463
1464 static GnmValue *
1465 gnumeric_column (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1466 {
1467 int col, width, i;
1468 GnmValue *res;
1469 GnmValue const *ref = args[0];
1470
1471 if (ref == NULL) {
1472 col = ei->pos->eval.col + 1; /* user visible counts from 0 */
1473 if (eval_pos_is_array_context (ei->pos))
1474 width = ei->pos->array->cols;
1475 else
1476 return value_new_int (col);
1477 } else if (VALUE_IS_CELLRANGE (ref)) {
1478 Sheet *tmp;
1479 GnmRange r;
1480
1481 gnm_rangeref_normalize (&ref->v_range.cell, ei->pos, &tmp, &tmp, &r);
1482 col = r.start.col + 1;
1483 width = range_width (&r);
1484 } else
1485 return value_new_error_VALUE (ei->pos);
1486
1487 if (width == 1)
1488 return value_new_int (col);
1489
1490 res = value_new_array (width, 1);
1491 for (i = width; i-- > 0 ; )
1492 value_array_set (res, i, 0, value_new_int (col + i));
1493 return res;
1494 }
1495
1496 /***************************************************************************/
1497
1498 static GnmFuncHelp const help_columnnumber[] = {
1499 { GNM_FUNC_HELP_NAME, F_("COLUMNNUMBER:column number for the given column called @{name}")},
1500 { GNM_FUNC_HELP_ARG, F_("name:column name such as \"IV\"")},
1501 { GNM_FUNC_HELP_NOTE, F_("If @{name} is invalid, COLUMNNUMBER returns #VALUE!")},
1502 { GNM_FUNC_HELP_EXAMPLES, "=COLUMNNUMBER(\"E\")" },
1503 { GNM_FUNC_HELP_SEEALSO, "ADDRESS"},
1504 { GNM_FUNC_HELP_END}
1505 };
1506
1507 static GnmValue *
1508 gnumeric_columnnumber (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1509 {
1510 char const *name = value_peek_string (args[0]);
1511 int colno;
1512 unsigned char relative;
1513 char const *after = col_parse (name,
1514 gnm_sheet_get_size (ei->pos->sheet),
1515 &colno, &relative);
1516
1517 if (after == NULL || *after)
1518 return value_new_error_VALUE (ei->pos);
1519
1520 return value_new_int (colno + 1);
1521 }
1522
1523 /***************************************************************************/
1524
1525 static GnmFuncHelp const help_columns[] = {
1526 { GNM_FUNC_HELP_NAME, F_("COLUMNS:number of columns in @{reference}")},
1527 { GNM_FUNC_HELP_ARG, F_("reference:array or area")},
1528 { GNM_FUNC_HELP_NOTE, F_("If @{reference} is neither an array nor a reference nor a range, "
1529 "COLUMNS returns #VALUE!")},
1530 { GNM_FUNC_HELP_EXAMPLES, "=COLUMNS(H2:J3)" },
1531 { GNM_FUNC_HELP_SEEALSO, "COLUMN,ROW,ROWS"},
1532 { GNM_FUNC_HELP_END}
1533 };
1534
1535 static GnmValue *
1536 gnumeric_columns (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1537 {
1538 return value_new_int (value_area_get_width (args[0], ei->pos));
1539 }
1540
1541 /***************************************************************************/
1542
1543 static GnmFuncHelp const help_offset[] = {
1544 { GNM_FUNC_HELP_NAME, F_("OFFSET:an offset cell range")},
1545 { GNM_FUNC_HELP_ARG, F_("range:reference or range")},
1546 { GNM_FUNC_HELP_ARG, F_("row:number of rows to offset @{range}")},
1547 { GNM_FUNC_HELP_ARG, F_("col:number of columns to offset @{range}")},
1548 { GNM_FUNC_HELP_ARG, F_("height:height of the offset range, defaults to height of @{range}")},
1549 { GNM_FUNC_HELP_ARG, F_("width:width of the offset range, defaults to width of @{range}")},
1550 { GNM_FUNC_HELP_DESCRIPTION, F_("OFFSET returns the cell range starting at offset "
1551 "(@{row},@{col}) from @{range} of height @{height} and "
1552 "width @{width}.")},
1553 { GNM_FUNC_HELP_NOTE, F_("If @{range} is neither a reference nor a range, OFFSET "
1554 "returns #VALUE!")},
1555 { GNM_FUNC_HELP_SEEALSO, "COLUMN,COLUMNS,ROWS,INDEX,INDIRECT,ADDRESS"},
1556 { GNM_FUNC_HELP_END}
1557 };
1558
1559 static GnmValue *
1560 gnumeric_offset (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1561 {
1562 int tmp;
1563 int row_offset, col_offset;
1564
1565 /* Copy the references so we can change them */
1566 GnmCellRef a = args[0]->v_range.cell.a;
1567 GnmCellRef b = args[0]->v_range.cell.b;
1568
1569 row_offset = value_get_as_int (args[1]);
1570 col_offset = value_get_as_int (args[2]);
1571 a.row += row_offset;
1572 a.col += col_offset;
1573 if (a.row < 0 || a.col < 0 ||
1574 a.row >= gnm_sheet_get_max_rows (ei->pos->sheet) || a.col >= gnm_sheet_get_max_cols (ei->pos->sheet))
1575 return value_new_error_REF (ei->pos);
1576
1577 if (args[3] != NULL) {
1578 tmp = value_get_as_int (args[3]);
1579 if (tmp < 1)
1580 return value_new_error_VALUE (ei->pos);
1581 b.row = a.row + tmp - 1;
1582 } else
1583 b.row += row_offset;
1584 if (b.col < 0 || b.row >= gnm_sheet_get_max_rows (ei->pos->sheet))
1585 return value_new_error_REF (ei->pos);
1586 if (args[4] != NULL) {
1587 tmp = value_get_as_int (args[4]);
1588 if (tmp < 1)
1589 return value_new_error_VALUE (ei->pos);
1590 b.col = a.col + tmp - 1;
1591 } else
1592 b.col += col_offset;
1593 if (b.col < 0 || b.col >= gnm_sheet_get_max_cols (ei->pos->sheet))
1594 return value_new_error_REF (ei->pos);
1595
1596 return value_new_cellrange_unsafe (&a, &b);
1597 }
1598
1599 /***************************************************************************/
1600
1601 static GnmFuncHelp const help_row[] = {
1602 { GNM_FUNC_HELP_NAME, F_("ROW:vector of row numbers") },
1603 { GNM_FUNC_HELP_ARG, F_("x:reference, defaults to the position of the current expression") },
1604 { GNM_FUNC_HELP_DESCRIPTION, F_("ROW function returns a 1xN array containing the "
1605 "sequence of integers "
1606 "from the first row to the last row of @{x}.") },
1607 { GNM_FUNC_HELP_NOTE, F_("If @{x} is neither an array nor a reference nor a range, "
1608 "returns #VALUE!") },
1609 { GNM_FUNC_HELP_EXAMPLES, "=ROW(A1:D3)" },
1610 { GNM_FUNC_HELP_EXAMPLES, "=ROW(1:3)" },
1611 { GNM_FUNC_HELP_SEEALSO, "COLUMN,COLUMNS,ROWS" },
1612 { GNM_FUNC_HELP_END }
1613 };
1614
1615 static GnmValue *
1616 gnumeric_row (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1617 {
1618 int row, n, i;
1619 GnmValue *res;
1620 GnmValue const *ref = args[0];
1621
1622 if (ref == NULL) {
1623 row = ei->pos->eval.row + 1; /* user visible counts from 0 */
1624 if (ei->pos->array != NULL)
1625 n = ei->pos->array->rows;
1626 else
1627 return value_new_int (row);
1628 } else if (VALUE_IS_CELLRANGE (ref)) {
1629 Sheet *tmp;
1630 GnmRange r;
1631
1632 gnm_rangeref_normalize (&ref->v_range.cell, ei->pos, &tmp, &tmp, &r);
1633 row = r.start.row + 1;
1634 n = range_height (&r);
1635 } else
1636 return value_new_error_VALUE (ei->pos);
1637
1638 if (n == 1)
1639 return value_new_int (row);
1640
1641 res = value_new_array (1, n);
1642 for (i = n ; i-- > 0 ; )
1643 value_array_set (res, 0, i, value_new_int (row + i));
1644 return res;
1645 }
1646
1647 /***************************************************************************/
1648
1649 static GnmFuncHelp const help_rows[] = {
1650 { GNM_FUNC_HELP_NAME, F_("ROWS:number of rows in @{reference}")},
1651 { GNM_FUNC_HELP_ARG, F_("reference:array, reference, or range")},
1652 { GNM_FUNC_HELP_NOTE, F_("If @{reference} is neither an array nor a reference nor a range, "
1653 "ROWS returns #VALUE!")},
1654 { GNM_FUNC_HELP_EXAMPLES, "=ROWS(H7:I13)" },
1655 { GNM_FUNC_HELP_SEEALSO, "COLUMN,COLUMNS,ROW"},
1656 { GNM_FUNC_HELP_END}
1657 };
1658
1659 static GnmValue *
1660 gnumeric_rows (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1661 {
1662 return value_new_int (value_area_get_height (args[0], ei->pos));
1663 }
1664
1665 /***************************************************************************/
1666
1667 static GnmFuncHelp const help_sheets[] = {
1668 { GNM_FUNC_HELP_NAME, F_("SHEETS:number of sheets in @{reference}")},
1669 { GNM_FUNC_HELP_ARG, F_("reference:array, reference, or range, defaults to the maximum range")},
1670 { GNM_FUNC_HELP_NOTE, F_("If @{reference} is neither an array nor a reference nor a range, "
1671 "SHEETS returns #VALUE!")},
1672 { GNM_FUNC_HELP_EXAMPLES, "=SHEETS()" },
1673 { GNM_FUNC_HELP_SEEALSO, "COLUMNS,ROWS"},
1674 { GNM_FUNC_HELP_END}
1675 };
1676
1677 static GnmValue *
1678 gnumeric_sheets (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1679 {
1680 Workbook const *wb = ei->pos->sheet->workbook;
1681 GnmValue const *v = args[0];
1682
1683 if(v) {
1684 if (VALUE_IS_CELLRANGE (v)) {
1685 GnmRangeRef const *r = &v->v_range.cell;
1686 int ans_min, ans_max, a, b;
1687
1688 a = r->a.sheet ? r->a.sheet->index_in_wb : -1;
1689 b = r->b.sheet ? r->b.sheet->index_in_wb : -1;
1690
1691 ans_min = MIN (a,b);
1692 ans_max = MAX (a,b);
1693
1694 if (ans_min == -1)
1695 return value_new_int (1);
1696
1697 return value_new_int (ans_max - ans_min + 1);
1698 } else
1699 return value_new_int (1);
1700 } else
1701 return value_new_int (workbook_sheet_count (wb));
1702 }
1703
1704 /***************************************************************************/
1705 static GnmFuncHelp const help_sheet[] = {
1706 { GNM_FUNC_HELP_NAME, F_("SHEET:sheet number of @{reference}")},
1707 { GNM_FUNC_HELP_ARG, F_("reference:reference or literal sheet name, defaults to the current sheet")},
1708 { GNM_FUNC_HELP_NOTE, F_("If @{reference} is neither a reference "
1709 "nor a literal sheet name, "
1710 "SHEET returns #VALUE!")},
1711 { GNM_FUNC_HELP_EXAMPLES, "=SHEET()" },
1712 { GNM_FUNC_HELP_EXAMPLES, "=SHEET(\"Sheet1\")" },
1713 { GNM_FUNC_HELP_SEEALSO, "SHEETS,ROW,COLUMNNUMBER"},
1714 { GNM_FUNC_HELP_END}
1715 };
1716
1717 static GnmValue *
1718 gnumeric_sheet (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1719 {
1720 Workbook const *wb = ei->pos->sheet->workbook;
1721 GnmValue const *v = args[0];
1722 int n;
1723
1724 if(v) {
1725 if (VALUE_IS_CELLRANGE (v)) {
1726 GnmRangeRef const *r = &v->v_range.cell;
1727 int a, b;
1728
1729 a = r->a.sheet ? r->a.sheet->index_in_wb : -1;
1730 b = r->b.sheet ? r->b.sheet->index_in_wb : -1;
1731
1732 if (a == -1 && b == -1)
1733 n = ei->pos->sheet->index_in_wb;
1734 else if (a == b || a * b < 0)
1735 n = MAX (a,b);
1736 else
1737 return value_new_error_NUM (ei->pos);
1738 } else if (VALUE_IS_STRING (v)) {
1739 Sheet *sheet = workbook_sheet_by_name
1740 (wb, value_peek_string (v));
1741 if (!sheet)
1742 return value_new_error_NUM (ei->pos);
1743 n = sheet->index_in_wb;
1744 } else
1745 return value_new_error_VALUE (ei->pos);
1746 } else
1747 n = ei->pos->sheet->index_in_wb;
1748
1749 return value_new_int (1 + n);
1750 }
1751 /***************************************************************************/
1752
1753 static GnmFuncHelp const help_hyperlink[] = {
1754 { GNM_FUNC_HELP_NAME, F_("HYPERLINK:second or first arguments")},
1755 { GNM_FUNC_HELP_ARG, F_("link_location:string")},
1756 { GNM_FUNC_HELP_ARG, F_("label:string, optional")},
1757 { GNM_FUNC_HELP_DESCRIPTION, F_("HYPERLINK function currently returns its 2nd argument, "
1758 "or if that is omitted the 1st argument.")},
1759 { GNM_FUNC_HELP_EXAMPLES, "=HYPERLINK(\"www.gnome.org\",\"GNOME\")" },
1760 { GNM_FUNC_HELP_EXAMPLES, "=HYPERLINK(\"www.gnome.org\")" },
1761 { GNM_FUNC_HELP_END}
1762 };
1763
1764 static GnmValue *
1765 gnumeric_hyperlink (GnmFuncEvalInfo *ei, GnmValue const * const *args)
1766 {
1767 GnmValue const * v = args[1];
1768 if (v == NULL)
1769 v = args[0];
1770 return value_dup (v);
1771 }
1772
1773 /***************************************************************************/
1774
1775 static GnmFuncHelp const help_transpose[] = {
1776 { GNM_FUNC_HELP_NAME, F_("TRANSPOSE:the transpose of @{matrix}")},
1777 { GNM_FUNC_HELP_ARG, F_("matrix:range")},
1778 { GNM_FUNC_HELP_SEEALSO, "FLIP,MMULT"},
1779 { GNM_FUNC_HELP_END}
1780 };
1781
1782
1783 static GnmValue *
1784 gnumeric_transpose (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1785 {
1786 GnmEvalPos const * const ep = ei->pos;
1787 GnmValue const * const matrix = argv[0];
1788 int r, c;
1789 GnmValue *res;
1790
1791 int const cols = value_area_get_width (matrix, ep);
1792 int const rows = value_area_get_height (matrix, ep);
1793
1794 /* Return the value directly for a singleton */
1795 if (rows == 1 && cols == 1)
1796 return value_dup (value_area_get_x_y (matrix, 0, 0, ep));
1797
1798 /* REMEMBER this is a transpose */
1799 res = value_new_array_non_init (rows, cols);
1800
1801 for (r = 0; r < rows; ++r) {
1802 res->v_array.vals [r] = g_new (GnmValue *, cols);
1803 for (c = 0; c < cols; ++c)
1804 res->v_array.vals[r][c] = value_dup(
1805 value_area_get_x_y (matrix, c, r, ep));
1806 }
1807
1808 return res;
1809 }
1810
1811 /***************************************************************************/
1812
1813 static GnmFuncHelp const help_flip[] = {
1814 { GNM_FUNC_HELP_NAME, F_("FLIP:@{matrix} flipped")},
1815 { GNM_FUNC_HELP_ARG, F_("matrix:range")},
1816 { GNM_FUNC_HELP_ARG, F_("vertical:if true, @{matrix} is flipped vertically, "
1817 "otherwise horizontally; defaults to TRUE")},
1818 { GNM_FUNC_HELP_SEEALSO, "TRANSPOSE"},
1819 { GNM_FUNC_HELP_END}
1820 };
1821
1822
1823 static GnmValue *
1824 gnumeric_flip (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1825 {
1826 GnmEvalPos const * const ep = ei->pos;
1827 GnmValue const * const matrix = argv[0];
1828 int r, c;
1829 GnmValue *res;
1830 gboolean vertical = argv[1] ? value_get_as_checked_bool (argv[1]) : TRUE;
1831
1832 int const cols = value_area_get_width (matrix, ep);
1833 int const rows = value_area_get_height (matrix, ep);
1834
1835 /* Return the value directly for a singleton */
1836 if (rows == 1 && cols == 1)
1837 return value_dup (value_area_get_x_y (matrix, 0, 0, ep));
1838
1839 res = value_new_array_non_init (cols, rows);
1840
1841 if (vertical)
1842 for (c = 0; c < cols; ++c) {
1843 res->v_array.vals [c] = g_new (GnmValue *, rows);
1844 for (r = 0; r < rows; ++r)
1845 res->v_array.vals[c][rows - r - 1] = value_dup
1846 (value_area_get_x_y (matrix, c, r, ep));
1847 }
1848 else
1849 for (c = 0; c < cols; ++c) {
1850 res->v_array.vals [c] = g_new (GnmValue *, rows);
1851 for (r = 0; r < rows; ++r)
1852 res->v_array.vals[c][r] = value_dup
1853 (value_area_get_x_y (matrix, cols - c - 1, r, ep));
1854 }
1855
1856 return res;
1857 }
1858
1859 /***************************************************************************/
1860 static GnmFuncHelp const help_array[] = {
1861 { GNM_FUNC_HELP_NAME, F_("ARRAY:vertical array of the arguments")},
1862 { GNM_FUNC_HELP_ARG, F_("v:value")},
1863 { GNM_FUNC_HELP_SEEALSO, "TRANSPOSE"},
1864 { GNM_FUNC_HELP_END}
1865 };
1866
1867
1868 static GnmValue *
1869 callback_function_array (GnmEvalPos const *ep, GnmValue const *value, void *closure)
1870 {
1871 GSList **list = closure;
1872
1873 *list = g_slist_prepend
1874 (*list, value ? value_dup (value) : value_new_empty ());
1875 return NULL;
1876 }
1877
1878 static GnmValue *
1879 gnumeric_array (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
1880 {
1881 GSList *list = NULL, *l;
1882 int len, i;
1883 GnmValue *val = function_iterate_argument_values
1884 (ei->pos, callback_function_array, &list,
1885 argc, argv, FALSE, CELL_ITER_ALL);
1886
1887 if (val != NULL) {
1888 g_slist_free_full (list, (GDestroyNotify)value_release);
1889 return val;
1890 }
1891 list = g_slist_reverse (list);
1892 len = g_slist_length (list);
1893
1894 if (len == 0) {
1895 g_slist_free_full (list, (GDestroyNotify)value_release);
1896 return value_new_error_VALUE (ei->pos);
1897 }
1898
1899 if (len == 1) {
1900 val = list->data;
1901 g_slist_free (list);
1902 return val;
1903 }
1904
1905 val = value_new_array_empty (1, len);
1906
1907 for (l = list, i = 0; l != NULL; l = l->next, i++)
1908 val->v_array.vals[0][i] = l->data;
1909
1910 g_slist_free (list);
1911 return val;
1912 }
1913
1914
1915 /***************************************************************************/
1916
1917 static GnmFuncHelp const help_sort[] = {
1918 { GNM_FUNC_HELP_NAME, F_("SORT:sorted list of numbers as vertical array")},
1919 { GNM_FUNC_HELP_ARG, F_("ref:list of numbers")},
1920 { GNM_FUNC_HELP_ARG, F_("order:0 (descending order) or 1 (ascending order); defaults to 0")},
1921 { GNM_FUNC_HELP_NOTE, F_("Strings, booleans, and empty cells are ignored.")},
1922 { GNM_FUNC_HELP_EXAMPLES, F_("SORT({4,3,5}) evaluates to {5,4,3}")},
1923 { GNM_FUNC_HELP_SEEALSO, ("ARRAY")},
1924 { GNM_FUNC_HELP_END }
1925 };
1926
1927 static GnmValue *
1928 gnumeric_sort (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1929 {
1930 gnm_float *xs;
1931 int i, j, n;
1932 GnmValue *result = NULL;
1933
1934 xs = collect_floats_value (argv[0], ei->pos,
1935 COLLECT_IGNORE_STRINGS |
1936 COLLECT_IGNORE_BOOLS |
1937 COLLECT_IGNORE_BLANKS |
1938 COLLECT_SORT,
1939 &n, &result);
1940 if (result)
1941 goto out;
1942
1943 switch (argv[1] ? value_get_as_int (argv[1]) : 0) {
1944 case 0:
1945 result = value_new_array_empty (1, n);
1946
1947 for (i = 0, j = n - 1; i < n; i++, j--)
1948 result->v_array.vals[0][i] = value_new_float (xs[j]);
1949 break;
1950 case 1:
1951 result = value_new_array_empty (1, n);
1952
1953 for (i = 0; i < n; i++)
1954 result->v_array.vals[0][i] = value_new_float (xs[i]);
1955 break;
1956 default:
1957 result = value_new_error_VALUE (ei->pos);
1958 break;
1959 }
1960
1961 out:
1962 g_free (xs);
1963
1964 return result;
1965 }
1966 /***************************************************************************/
1967
1968 GnmFuncDescriptor const lookup_functions[] = {
1969 { "address", "ff|fbs",
1970 help_address, gnumeric_address, NULL, NULL, NULL,
1971 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_EXHAUSTIVE },
1972 { "areas", NULL,
1973 help_areas, NULL, gnumeric_areas, NULL, NULL,
1974 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1975 { "choose", NULL,
1976 help_choose, NULL, gnumeric_choose, NULL, NULL,
1977 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1978 { "column", "|A",
1979 help_column, gnumeric_column, NULL, NULL, NULL,
1980 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1981 { "columnnumber", "s",
1982 help_columnnumber, gnumeric_columnnumber, NULL, NULL, NULL,
1983 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1984 { "columns", "A",
1985 help_columns, gnumeric_columns, NULL, NULL, NULL,
1986 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1987 { "hlookup", "EAf|bb",
1988 help_hlookup, gnumeric_hlookup, NULL, NULL, NULL,
1989 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1990 { "hyperlink", "S|S",
1991 help_hyperlink, gnumeric_hyperlink, NULL, NULL, NULL,
1992 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_SUBSET, GNM_FUNC_TEST_STATUS_BASIC },
1993 { "indirect", "s|b",
1994 help_indirect, gnumeric_indirect, NULL, NULL, NULL,
1995 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1996 { "index", "A|fff",
1997 help_index, NULL, gnumeric_index, NULL, NULL,
1998 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1999 { "lookup", "EA|r",
2000 help_lookup, gnumeric_lookup, NULL, NULL, NULL,
2001 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2002 { "match", "EA|f",
2003 help_match, gnumeric_match, NULL, NULL, NULL,
2004 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2005 { "offset", "rff|ff",
2006 help_offset, gnumeric_offset, NULL, NULL, NULL,
2007 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2008 { "row", "|A",
2009 help_row, gnumeric_row, NULL, NULL, NULL,
2010 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2011 { "rows", "A",
2012 help_rows, gnumeric_rows, NULL, NULL, NULL,
2013 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2014 { "sheets", "|A",
2015 help_sheets, gnumeric_sheets, NULL, NULL, NULL,
2016 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
2017 { "sheet", "|?",
2018 help_sheet, gnumeric_sheet, NULL, NULL, NULL,
2019 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
2020 { "sort", "r|f",
2021 help_sort, gnumeric_sort, NULL, NULL, NULL,
2022 GNM_FUNC_RETURNS_NON_SCALAR, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
2023 { "transpose", "A",
2024 help_transpose, gnumeric_transpose, NULL, NULL, NULL,
2025 GNM_FUNC_RETURNS_NON_SCALAR, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2026 { "vlookup", "EAf|bb",
2027 help_vlookup, gnumeric_vlookup, NULL, NULL, NULL,
2028 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
2029 { "array", NULL,
2030 help_array, NULL, gnumeric_array, NULL, NULL,
2031 GNM_FUNC_RETURNS_NON_SCALAR, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
2032 { "flip", "A|b",
2033 help_flip, gnumeric_flip, NULL, NULL, NULL,
2034 GNM_FUNC_RETURNS_NON_SCALAR, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
2035
2036 {NULL}
2037 };
2038
2039 G_MODULE_EXPORT void
2040 go_plugin_init (GOPlugin *plugin, GOCmdContext *cc)
2041 {
2042 debug_lookup_caches = gnm_debug_flag ("lookup-caches");
2043 g_signal_connect (gnm_app_get_app (), "recalc-clear-caches",
2044 G_CALLBACK (clear_caches), NULL);
2045 }
2046
2047 G_MODULE_EXPORT void
2048 go_plugin_shutdown (GOPlugin *plugin, GOCmdContext *cc)
2049 {
2050 g_signal_handlers_disconnect_by_func (gnm_app_get_app (),
2051 G_CALLBACK (clear_caches), NULL);
2052
2053 if (protect_string_pool) {
2054 g_printerr ("Imbalance in string pool: %d\n", (int)protect_string_pool);
2055 protect_string_pool = 0;
2056 }
2057 if (protect_float_pool) {
2058 g_printerr ("Imbalance in float pool: %d\n", (int)protect_float_pool);
2059 protect_float_pool = 0;
2060 }
2061
2062 clear_caches ();
2063 }
gnumeric mirror