functions.c 26.8 KB
Newer Older
1
/*
2
 * fn-string.c:  Built in string functions.
3
 *
4
 * Authors:
5
 *  Miguel de Icaza (miguel@gnu.org)
6
 *  Sean Atkinson (sca20@cam.ac.uk)
7
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
8
9
 */
#include <config.h>
10
#include <ctype.h>
11
#include <math.h>
12
13
14
#include "gnumeric.h"
#include "utils.h"
#include "func.h"
15
#include "format.h"
16
#include "number-match.h"
17

18
19
20
21
22
static char *help_char = {
	N_("@FUNCTION=CHAR\n"
	   "@SYNTAX=CHAR(x)\n"

	   "@DESCRIPTION="
23
	   "Returns the ascii character represented by the number x."
24
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
25

26
27
28
	   "@SEEALSO=CODE")
};

29
static Value *
30
gnumeric_char (FunctionEvalInfo *ei, Value **argv)
31
32
33
34
35
36
{
	char result [2];

	result [0] = value_get_as_int (argv [0]);
	result [1] = 0;

Michael Meeks's avatar
Michael Meeks committed
37
	return value_new_string (result);
38
39
}

40
41
42
43
44
45
46
static char *help_code = {
	N_("@FUNCTION=CODE\n"
	   "@SYNTAX=CODE(char)\n"

	   "@DESCRIPTION="
	   "Returns the ASCII number for the character char."
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
47

48
49
50
	   "@SEEALSO=CHAR")
};

51
static Value *
52
gnumeric_code (FunctionEvalInfo *ei, Value **argv)
53
{
Morten Welinder's avatar
Morten Welinder committed
54
55
	unsigned char c;

56
57
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
58

Morten Welinder's avatar
Morten Welinder committed
59
60
	c = argv [0]->v.str->str [0];
	return value_new_int (c);
61
62
}

63
64
65
66
67
static char *help_exact = {
	N_("@FUNCTION=EXACT\n"
	   "@SYNTAX=EXACT(string1, string2)\n"

	   "@DESCRIPTION="
68
69
	   "Returns true if string1 is exactly equal to string2 "
	   "(this routine is case sensitive)."
70
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
71

72
	   "@SEEALSO=LEN")  /* FIXME: DELTA, LEN, SEARCH */
73
74
};

75
static Value *
76
gnumeric_exact (FunctionEvalInfo *ei, Value **argv)
77
{
78
79
	if (argv [0]->type != VALUE_STRING || argv [1]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
80

81
82
	return value_new_int (strcmp (argv [0]->v.str->str,
				      argv [1]->v.str->str) == 0);
83
84
}

85
86
87
88
89
90
91
static char *help_len = {
	N_("@FUNCTION=LEN\n"
	   "@SYNTAX=LEN(string)\n"

	   "@DESCRIPTION="
	   "Returns the length in characters of the string @string."
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
92

93
94
95
	   "@SEEALSO=CHAR, CODE")
};

96
static Value *
97
gnumeric_len (FunctionEvalInfo *ei, Value **argv)
98
{
99
100
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
101

Michael Meeks's avatar
Michael Meeks committed
102
	return value_new_int (strlen (argv [0]->v.str->str));
103
104
}

105
106
107
108
109
110
111
112
113
114
115
116
static char *help_left = {
	N_("@FUNCTION=LEFT\n"
	   "@SYNTAX=LEFT(text[,num_chars])\n"

	   "@DESCRIPTION="
	   "Returns the leftmost num_chars characters or the left"
	   " character if num_chars is not specified"
	   "\n"
	   "@SEEALSO=MID, RIGHT")
};

static Value *
117
gnumeric_left (FunctionEvalInfo *ei, Value **argv)
118
{
Arturo Espinosa's avatar
Arturo Espinosa committed
119
	Value *v;
120
121
122
	int count;
	char *s;

123
	if (argv[1])
Arturo Espinosa's avatar
Arturo Espinosa committed
124
		count = value_get_as_int(argv[1]);
125
126
	else
		count = 1;
Morten Welinder's avatar
Morten Welinder committed
127

128
	s = g_malloc (count + 1);
129
	strncpy (s, argv[0]->v.str->str, count);
130
131
	s [count] = 0;

Michael Meeks's avatar
Michael Meeks committed
132
	v = value_new_string (s);
133
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
134

135
136
137
	return v;
}

138
139
140
141
142
143
144
145
146
147
static char *help_lower = {
	N_("@FUNCTION=LOWER\n"
	   "@SYNTAX=LOWER(text)\n"

	   "@DESCRIPTION="
	   "Returns a lower-case version of the string in @text"
	   "\n"
	   "@SEEALSO=UPPER")
};

148
static Value *
149
gnumeric_lower (FunctionEvalInfo *ei, Value **argv)
150
151
{
	Value *v;
152
	unsigned char *s, *p;
Morten Welinder's avatar
Morten Welinder committed
153

154
155
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
156

157
	p = s = g_strdup (argv [0]->v.str->str);
158
159
160
	for (; *p; p++){
		*p = tolower (*p);
	}
161
	v = value_new_string (s);
162
	g_free (s);
163
164
165
166

	return v;
}

167
168
169
170
171
static char *help_mid = {
	N_("@FUNCTION=MID\n"
	   "@SYNTAX=MID(string, position, length)\n"

	   "@DESCRIPTION="
172
173
	   "Returns a substring from @string starting at @position for "
	   "@length characters."
174
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
175

176
177
178
179
	   "@SEEALSO=LEFT, RIGHT")
};

static Value *
180
gnumeric_mid  (FunctionEvalInfo *ei, Value **argv)
181
182
183
184
185
186
187
{
	Value *v;
	int pos, len;
	char *s, *source;

	if (argv [0]->type != VALUE_STRING ||
	    argv [1]->type != VALUE_INTEGER ||
188
189
	    argv [2]->type != VALUE_INTEGER)
		return function_error (ei, _("Type mismatch"));
190
191
192
193

	len = value_get_as_int (argv [2]);
	pos = value_get_as_int (argv [1]);

194
195
	if (len < 0 || pos <= 0)
		return function_error (ei, _("Invalid arguments"));
196
197
198

	source = argv [0]->v.str->str;
	if (pos > strlen (source))
Michael Meeks's avatar
Michael Meeks committed
199
		return value_new_string ("");
Arturo Espinosa's avatar
Arturo Espinosa committed
200
201
	pos--;
	s = g_new (gchar, len+1);
202
	strncpy (s, &source[pos], len);
Arturo Espinosa's avatar
Arturo Espinosa committed
203
	s[len] = '\0';
Michael Meeks's avatar
Michael Meeks committed
204
	v = value_new_string (s);
205
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
206

207
208
209
	return v;
}

210
211
212
213
214
static char *help_right = {
	N_("@FUNCTION=RIGHT\n"
	   "@SYNTAX=RIGHT(text[,num_chars])\n"

	   "@DESCRIPTION="
215
216
	   "Returns the rightmost num_chars characters or the right "
	   "character if num_chars is not specified"
217
218
219
220
221
	   "\n"
	   "@SEEALSO=MID, LEFT")
};

static Value *
222
gnumeric_right (FunctionEvalInfo *ei, Value **argv)
223
{
Arturo Espinosa's avatar
Arturo Espinosa committed
224
	Value *v;
225
226
227
	int count, len;
	char *s;

228
	if (argv[1])
Arturo Espinosa's avatar
Arturo Espinosa committed
229
		count = value_get_as_int(argv[1]);
230
231
	else
		count = 1;
232

233
	len = strlen (argv[0]->v.str->str);
234
235
	if (count > len)
		count = len;
Morten Welinder's avatar
Morten Welinder committed
236

237
	s = g_malloc (count + 1);
238
	strncpy (s, argv[0]->v.str->str+len-count, count);
239
240
	s [count] = 0;

Michael Meeks's avatar
Michael Meeks committed
241
	v = value_new_string (s);
242
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
243

244
245
246
	return v;
}

247
248
249
250
251
252
253
254
255
256
static char *help_upper = {
	N_("@FUNCTION=UPPER\n"
	   "@SYNTAX=UPPER(text)\n"

	   "@DESCRIPTION="
	   "Returns a upper-case version of the string in @text"
	   "\n"
	   "@SEEALSO=LOWER")
};

257
static Value *
258
gnumeric_upper (FunctionEvalInfo *ei, Value **argv)
259
260
{
	Value *v;
261
	unsigned char *s, *p;
Morten Welinder's avatar
Morten Welinder committed
262

263
264
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
265

266
	p = s = g_strdup (argv [0]->v.str->str);
267
268
269
270

	for (;*p; p++){
		*p = toupper (*p);
	}
271
	v = value_new_string (s);
272
	g_free (s);
273
274
275

	return v;
}
276

277
278
279
static char *help_concatenate = {
	N_("@FUNCTION=CONCATENATE\n"
	   "@SYNTAX=CONCATENATE(string1[,string2...])\n"
280
281
282
	   "@DESCRIPTION="
	   "Returns up appended strings."
	   "\n"
283
284
285
286
	   "@SEEALSO=LEFT, MID, RIGHT")
};

static Value *
287
gnumeric_concatenate (FunctionEvalInfo *ei, GList *l)
288
289
290
{
	Value *v;
	char *s, *p, *tmp;
Morten Welinder's avatar
Morten Welinder committed
291

292
293
294
	if (l==NULL)
		return function_error (ei, _("Invalid number of arguments"));

Arturo Espinosa's avatar
Arturo Espinosa committed
295
296
	s = g_new(gchar, 1);
	*s = '\0';
Morten Welinder's avatar
Morten Welinder committed
297
	while ( l != NULL &&
Morten Welinder's avatar
Morten Welinder committed
298
		(v=eval_expr(ei, l->data)) != NULL) {
299
300
/*
		if (v->type != VALUE_STRING) {
301
			return function_error (ei, _("Invalid argument"));
Arturo Espinosa's avatar
Arturo Espinosa committed
302
303
			value_release (v);
			return NULL;
304
305
		}
*/
Michael Meeks's avatar
Michael Meeks committed
306
		tmp = value_get_as_string (v);
307
		/* FIXME: this could be massively sped-up with strlen's etc... */
Arturo Espinosa's avatar
Arturo Espinosa committed
308
309
310
311
312
313
		p = g_strconcat (s, tmp, NULL);
		g_free (tmp);
		value_release (v);
		g_free (s);
		s = p;
		l = g_list_next (l);
314
	}
Morten Welinder's avatar
Morten Welinder committed
315

316
	v = value_new_string (s);
317
318
319
320
321
322
323
324
	g_free (s);

	return v;
}

static char *help_rept = {
	N_("@FUNCTION=REPT\n"
	   "@SYNTAX=REPT(string,num)\n"
325
326
327
	   "@DESCRIPTION="
	   "Returns @num repetitions of @string."
	   "\n"
328
329
330
331
	   "@SEEALSO=CONCATENATE")
};

static Value *
332
gnumeric_rept (FunctionEvalInfo *ei, Value **argv)
333
334
335
336
337
{
	Value *v;
	gchar *s, *p;
	gint num;
	guint len;
Morten Welinder's avatar
Morten Welinder committed
338

339
340
341
342
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
	else if ( (num=value_get_as_int(argv[1])) < 0)
		return function_error (ei, _("Invalid argument"));
343
344
345
346

	len = strlen (argv[0]->v.str->str);
	p = s = g_new (gchar, 1 + len * num);
	while (num--) {
Arturo Espinosa's avatar
Arturo Espinosa committed
347
		strncpy (p, argv[0]->v.str->str, len);
348
349
		p += len;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
350
	*p = '\0';
351
	v = value_new_string (s);
352
353
354
355
	g_free (s);

	return v;
}
356

357
358
359
360
static char *help_clean = {
	N_("@FUNCTION=CLEAN\n"
	   "@SYNTAX=CLEAN(string)\n"

361
362
363
	   "@DESCRIPTION="
	   "Cleans the string from any non-printable characters."
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
364

365
	   "@SEEALSO=")
366
367
368
};

static Value *
369
gnumeric_clean  (FunctionEvalInfo *ei, Value **argv)
370
{
371
	Value *res;
372
	unsigned char *copy, *p, *q;
Morten Welinder's avatar
Morten Welinder committed
373

374
375
376
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));

377
378
	p = argv [0]->v.str->str;
	copy = q = g_malloc (strlen (p) + 1);
Morten Welinder's avatar
Morten Welinder committed
379

380
381
382
	while (*p){
		if (isprint (*p))
			*q++ = *p;
383
384
		p++;
	}
385
	*q = 0;
386

387
	res = value_new_string (copy);
388
389
390
	g_free (copy);

	return res;
391
}
392

393
394
395
static char *help_find = {
	N_("@FUNCTION=FIND\n"
	   "@SYNTAX=FIND(string1,string2[,start])\n"
396
397
398
399
400
	   "@DESCRIPTION="
	   "Returns position of @string1 in @string2 (case-sesitive), "
	   "searching only from character @start onwards (assumed 1 if "
	   "omitted)."
	   "\n"
401
402
403
404
	   "@SEEALSO=EXACT, LEN, MID, SEARCH")
};

static Value *
405
gnumeric_find (FunctionEvalInfo *ei, Value **argv)
406
407
408
409
{
	int count;
	char *s, *p;

410
	if (argv[2])
Arturo Espinosa's avatar
Arturo Espinosa committed
411
		count = value_get_as_int(argv[2]);
412
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
413
		count = 1;
414
415

	if ( count > strlen(argv[1]->v.str->str) ||
416
417
	     count == 0) /* start position too high or low */
		return function_error (ei, _("Invalid argument"));
418

Arturo Espinosa's avatar
Arturo Espinosa committed
419
	g_assert (count >= 1);
420
	s = argv[1]->v.str->str + count - 1;
421
422
	if ( (p = strstr(s, argv[0]->v.str->str)) == NULL )
		return function_error (ei, _("Invalid argument"));
423

424
	return value_new_int (count + p - s);
425
426
427
428
}

static char *help_fixed = {
	N_("@FUNCTION=FIXED\n"
429
	   "@SYNTAX=FIXED(num, [decimals, no_commas])\n"
430

431
432
433
434
435
	   "@DESCRIPTION="
	   "Returns @num as a formatted string with @decimals numbers "
	   "after the decimal point, omitting commas if requested by "
	   "@no_commas."
	   "\n"
436
437
438
439
	   "@SEEALSO=")
};

static Value *
440
gnumeric_fixed (FunctionEvalInfo *ei, Value **argv)
441
442
443
{
	Value *v;
	gchar *s, *p, *f;
444
	gint dec, commas, tmp;
445
446
	float_t num;

Michael Meeks's avatar
Michael Meeks committed
447
	num = value_get_as_float (argv[0]);
448
449
450
	if (argv[1])
		dec = value_get_as_int (argv[1]);
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
451
		dec = 2;
452
453

	if (argv[2])
Michael Meeks's avatar
Michael Meeks committed
454
		commas = !value_get_as_bool (argv[2], &tmp);
455
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
456
		commas = TRUE;
457

458
	if (dec >= 1000) { /* else buffer under-run */
459
		return function_error (ei, _("Invalid argument"));
460
461
		/*
	} else if (lc->thousands_sep[1] != '\0') {
Arturo Espinosa's avatar
Arturo Espinosa committed
462
		fprintf (stderr, "thousands_sep:\"%s\"\n", lc->thousands_sep);
463
		return function_error (ei, _("Invalid thousands separator"));
464
		*/
465
	} else if (dec <= 0) { /* no decimal point : just round and pad 0's */
466
467
468
		dec *= -1;
		num /= pow(10, dec);
		if (num < 1 && num > -1) {
Arturo Espinosa's avatar
Arturo Espinosa committed
469
470
			s = g_strdup("0");
			commas = 0;
471
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
472
473
474
			f = g_strdup("%00?s%.0f%.00?u"); /* commas, no point, 0's */
			tmp = dec;
			dec += log10(fabs(num));
475
			if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
476
477
				commas = dec / 3;
			p = &f[13]; /* last 0 in trailing 0's count */
478
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
479
480
481
482
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			tmp = commas;
			p = &f[3]; /* last 0 in leading blank spaces for commas */
483
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
484
485
486
487
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			s = g_strdup_printf (f, "", num, 0);
			g_free (f);
488
489
		}
	} else { /* decimal point format */
Arturo Espinosa's avatar
Arturo Espinosa committed
490
491
492
		f = g_strdup("%00?s%.00?f");
		tmp = dec;
		dec = log10(fabs(num));
493
		if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
494
495
			commas = dec / 3;
		p = &f[9];
496
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
497
498
499
500
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		tmp = commas;
		p = &f[3];
501
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
502
503
504
505
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		s = g_strdup_printf (f, "", num);
		g_free (f);
506
507
	}
	if (commas) {
Arturo Espinosa's avatar
Arturo Espinosa committed
508
509
		p = s;
		f = &s[commas];
510
		if (*f == '-')
Arturo Espinosa's avatar
Arturo Espinosa committed
511
512
			*p++ = *f++;
		dec -= 2;
513
		while (dec-- > 0) {
Arturo Espinosa's avatar
Arturo Espinosa committed
514
			*p++ = *f++;
515
516
			if (dec%3 == 0)
				/* FIXME: should use lc->thousands_sep[0] */
Arturo Espinosa's avatar
Arturo Espinosa committed
517
				*p++ = ',';
518
519
520
		}
	}

521
	v = value_new_string (s);
522
523
524
525
	g_free (s);
	return v;
}

Arturo Espinosa's avatar
Arturo Espinosa committed
526
527
/*
 * proper could be a LOT nicer
528
 * (e.g. "US Of A" -> "US of A", "Cent'S Worth" -> "Cent's Worth")
Arturo Espinosa's avatar
Arturo Espinosa committed
529
530
 * but this is how Excel does it
 */
531
532
533
534
static char *help_proper = {
	N_("@FUNCTION=PROPER\n"
	   "@SYNTAX=PROPER(string)\n"

535
536
537
	   "@DESCRIPTION="
	   "Returns @string with initial of each word capitalised."
	   "\n"
538
539
540
541
	   "@SEEALSO=LOWER, UPPER")
};

static Value *
542
gnumeric_proper (FunctionEvalInfo *ei, Value **argv)
543
544
{
	Value *v;
545
	unsigned char *s, *p;
546
547
	gboolean inword = FALSE;

548
549
	if (argv [0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
550

551
	s = p = g_strdup (argv[0]->v.str->str);
552
	while (*s) {
553
		if (isalpha(*s)) {
554
555
556
557
558
559
560
561
562
563
564
			if (inword) {
				*s = tolower(*s);
			} else {
				*s = toupper(*s);
				inword = TRUE;
			}
		} else
			inword = FALSE;
		s++;
	}

565
	v = value_new_string (p);
566
	g_free (p);
567
568
	return v;
}
Morten Welinder's avatar
Morten Welinder committed
569

570
571
572
static char *help_replace = {
	N_("@FUNCTION=REPLACE\n"
	   "@SYNTAX=REPLACE(old,start,num,new)\n"
573
574
575
	   "@DESCRIPTION="
	   "Returns @old with @new replacing @num characters from @start."
	   "\n"
576
577
578
579
	   "@SEEALSO=MID, SEARCH, SUBSTITUTE, TRIM")
};

static Value *
580
gnumeric_replace (FunctionEvalInfo *ei, Value **argv)
581
582
{
	Value *v;
583
	gchar *s;
584
585
	gint start, num, oldlen, newlen;

586
	/* Why do we need this ? */
587
588
589
	if (argv[0]->type != VALUE_STRING ||
	    argv[1]->type != VALUE_INTEGER ||
	    argv[2]->type != VALUE_INTEGER ||
590
591
	    argv[3]->type != VALUE_STRING )
		return function_error (ei, _("Type mismatch"));
Arturo Espinosa's avatar
Arturo Espinosa committed
592
593
594

	start = value_get_as_int (argv[1]);
	num = value_get_as_int (argv[2]);
Morten Welinder's avatar
Morten Welinder committed
595
	oldlen = strlen(argv[0]->v.str->str);
Arturo Espinosa's avatar
Arturo Espinosa committed
596

597
	if (start <= 0 || num <= 0)
598
		return function_error (ei, _("Invalid arguments"));
Arturo Espinosa's avatar
Arturo Espinosa committed
599

600
601
	if (--start + num > oldlen)
		num = oldlen - start;
Arturo Espinosa's avatar
Arturo Espinosa committed
602
603
604
	newlen = strlen (argv [3]->v.str->str);

	s = g_new (gchar, 1 + newlen + oldlen - num);
605
606
	strncpy (s, argv[0]->v.str->str, start);
	strncpy (&s[start], argv[3]->v.str->str, newlen);
607
608
	strncpy (&s[start+newlen], &argv[0]->v.str->str[start+num],
		 oldlen - num - start );
Arturo Espinosa's avatar
Arturo Espinosa committed
609
610
611

	s [newlen+oldlen-num] = '\0';

612
	v = value_new_string (s);
Arturo Espinosa's avatar
Arturo Espinosa committed
613

614
	g_free(s);
Arturo Espinosa's avatar
Arturo Espinosa committed
615

616
617
618
	return v;
}

619
620
/*****************************************************************/

621
622
623
static char *help_t = {
	N_("@FUNCTION=T\n"
	   "@SYNTAX=T(value)\n"
624
625
626
	   "@DESCRIPTION="
	   "Returns @value if and only if it is text, otherwise a blank "
	   "string.\n"
627
628
629
630
	   "@SEEALSO=CELL, N, VALUE")
};

static Value *
631
gnumeric_t (FunctionEvalInfo *ei, Value **argv)
632
{
Morten Welinder's avatar
Morten Welinder committed
633
634
635
	if (argv[0]->type == VALUE_STRING)
		return value_duplicate (argv[0]);
	else
636
		return value_new_string ("");
637
638
}

639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
/*****************************************************************/

static char *help_text = {
	N_("@FUNCTION=TEXT\n"
	   "@SYNTAX=TEXT(value,format_text)\n"
	   "@DESCRIPTION="
	   "Returns @value as a string with the specified format."
	   "\n"
	   "@SEEALSO=DOLLAR")
};

static Value *
gnumeric_text (FunctionEvalInfo *ei, Value **args)
{
	StyleFormat *format  = style_format_new (args[1]->v.str->str);
	Value *res, *tmp = NULL;
	Value const *arg  = args[0];
	gboolean ok = FALSE;

	/* FIXME FIXME FIXME : All this should really be moved
	 *                     into value_get_as_float
	 */
	if (arg->type == VALUE_CELLRANGE || arg->type == VALUE_ARRAY) {
		if (value_area_get_height (&ei->pos, arg) == 1 &&
		    value_area_get_width (&ei->pos, arg) == 1)
			arg = value_area_fetch_x_y (&ei->pos, arg, 0, 0);
	}

	if (arg->type == VALUE_STRING) {
		char *format = NULL;
		float_t num = 0.;
		if ((ok = format_match (arg->v.str->str, &num, &format)))
			tmp = value_new_float (num);
	} else
		ok = VALUE_IS_NUMBER(arg);

	if (ok)
	{
		char const *str = format_value (format,
						(tmp != NULL) ? tmp : arg,
						NULL);
		res = value_new_string (str);
	} else
		res = function_error (ei, _("Type mismatch"));

	if (tmp != NULL)
		value_release (tmp);
	style_format_unref (format);
	return res;
}


/*****************************************************************/

693
694
695
static char *help_trim = {
	N_("@FUNCTION=TRIM\n"
	   "@SYNTAX=TRIM(text)\n"
696
697
698
	   "@DESCRIPTION="
	   "Returns @text with only single spaces between words."
	   "\n"
699
700
701
702
	   "@SEEALSO=CLEAN, MID, REPLACE, SUBSTITUTE")
};

static Value *
703
gnumeric_trim (FunctionEvalInfo *ei, Value **argv)
704
705
706
707
708
{
	Value *v;
	gchar *new, *dest, *src;
	gboolean space = TRUE;

709
710
	if (argv[0]->type != VALUE_STRING)
		return function_error (ei, _("Type mismatch"));
Arturo Espinosa's avatar
Arturo Espinosa committed
711
712
713
714
715
716

	dest = new = g_new (gchar, strlen(argv[0]->v.str->str) + 1);
	src = argv [0]->v.str->str;

	while (*src){
		if (*src == ' '){
717
718
			if (!space) {
				*dest++ = *src;
Arturo Espinosa's avatar
Arturo Espinosa committed
719
				space = TRUE;
720
721
			}
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
722
723
			space = FALSE;
			*dest++ = *src;
724
		}
Arturo Espinosa's avatar
Arturo Espinosa committed
725
		src++;
726
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
727
728
729
	if (space && dest > new)
		dest--;

730
	*dest = '\0';
Arturo Espinosa's avatar
Arturo Espinosa committed
731

732
	v = value_new_string (new);
733
	g_free(new);
Arturo Espinosa's avatar
Arturo Espinosa committed
734

735
736
737
738
739
740
	return v;
}

static char *help_value = {
	N_("@FUNCTION=VALUE\n"
	   "@SYNTAX=VALUE(text)\n"
741
742
743
	   "@DESCRIPTION="
	   "Returns numeric value of @text."
	   "\n"
744
745
746
747
	   "@SEEALSO=DOLLAR, FIXED, TEXT")
};

static Value *
748
gnumeric_value (FunctionEvalInfo *ei, Value **argv)
749
{
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
	char *arg, *p, *q;
	double v;
	char *format;
	gboolean ok;

	switch (argv[0]->type) {
	case VALUE_INTEGER:
	case VALUE_FLOAT:
		/* This specifically should not be used for VALUE_BOOL.  */
		return value_duplicate (argv[0]);
	default:
		q = p = arg = value_get_as_string (argv[0]);
		while (*p) {
			if (!isspace ((unsigned char)*p))
				*q++ = *p;
			p++;
		}
		*q = 0;

		ok = format_match (arg, &v, &format);
		free (arg);
771

772
773
774
775
776
		if (ok)
			return value_new_float (v);
		else
			return function_error (ei, gnumeric_err_VALUE);
	}
777
778
779
}

struct subs_string {
Arturo Espinosa's avatar
Arturo Espinosa committed
780
781
782
783
	gchar *str;
	guint len;
	guint mem;
};
784
785
786
787

static struct subs_string *
subs_string_new (guint len)
{
Arturo Espinosa's avatar
Arturo Espinosa committed
788
	struct subs_string *s = g_new (struct subs_string, 1);
Morten Welinder's avatar
Morten Welinder committed
789

Arturo Espinosa's avatar
Arturo Espinosa committed
790
791
792
793
794
	s->len = 0;
	s->mem = len;
	s->str = g_new (gchar, len);
	*s->str = '\0';
	return s;
795
796
797
798
799
}

static void
subs_string_append_n (struct subs_string *s, gchar *src, guint n)
{
Arturo Espinosa's avatar
Arturo Espinosa committed
800
801
	const guint chunk = 1024;

802
	while (s->len + n >= s->mem)
Arturo Espinosa's avatar
Arturo Espinosa committed
803
		s->str = g_realloc (s->str, s->mem += chunk);
Morten Welinder's avatar
Morten Welinder committed
804

Arturo Espinosa's avatar
Arturo Espinosa committed
805
806
807
808
	strncpy (&s->str [s->len], src, n);

	s->len += n;
	s->str [s->len] = '\0';
809
810
811
812
813
}

static void
subs_string_free (struct subs_string *s)
{
Arturo Espinosa's avatar
Arturo Espinosa committed
814
815
	g_free (s->str);
	g_free (s);
816
817
818
819
}

static char *help_substitute = {
	N_("@FUNCTION=SUBSTITUTE\n"
820
	   "@SYNTAX=SUBSTITUTE(text, old, new [,num])\n"
821
822
823
824
825
	   "@DESCRIPTION="
	   "Replaces @old with @new in @text.  Substitutions are only "
	   "applied to instance @num of @old in @text, otherwise every "
	   "one is changed."
	   "\n"
826
827
828
829
	   "@SEEALSO=REPLACE, TRIM")
};

static Value *
830
gnumeric_substitute (FunctionEvalInfo *ei, Value **argv)
831
{
Arturo Espinosa's avatar
Arturo Espinosa committed
832
833
834
835
836
	Value *v;
	gchar *text, *old, *new, *p ,*f;
	gint num;
	guint oldlen, newlen, len, inst;
	struct subs_string *s;
837

Michael Meeks's avatar
Michael Meeks committed
838
839
840
	text = value_get_as_string (argv[0]);
	old  = value_get_as_string (argv[1]);
	new  = value_get_as_string (argv[2]);
841

842
	if (argv[3])
Arturo Espinosa's avatar
Arturo Espinosa committed
843
		num = value_get_as_int (argv[3]);
844
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
845
		num = 0;
846

Arturo Espinosa's avatar
Arturo Espinosa committed
847
848
849
	oldlen = strlen (old);
	newlen = strlen (new);
	len = strlen (text);
850
	if (newlen != oldlen) {
Arturo Espinosa's avatar
Arturo Espinosa committed
851
		s = subs_string_new (len);
Morten Welinder's avatar
Morten Welinder committed
852
	} else
Arturo Espinosa's avatar
Arturo Espinosa committed
853
		s = NULL;
854

Arturo Espinosa's avatar
Arturo Espinosa committed
855
856
	p = text;
	inst = 0;
857
858
	while (p-text < len) {
		if ( (f=strstr(p, old)) == NULL )
Arturo Espinosa's avatar
Arturo Espinosa committed
859
			break;
860
861
		if (num == 0 || num == ++inst) {
			if (s == NULL) {
Arturo Espinosa's avatar
Arturo Espinosa committed
862
				strncpy (f, new, newlen);
863
			} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
864
865
				subs_string_append_n (s, p, f-p);
				subs_string_append_n (s, new, newlen);
866
867
			}
			if (num != 0 && num == inst)
Arturo Espinosa's avatar
Arturo Espinosa committed
868
				break;
869
		}
Arturo Espinosa's avatar
Arturo Espinosa committed
870
		p = f + oldlen;
871
872
	}
	if (newlen != oldlen) { /* FIXME: (p-text) might be bad ? */
Arturo Espinosa's avatar
Arturo Espinosa committed
873
874
		subs_string_append_n (s, p, len - (p-text) );
		p = s->str;
875
	} else
Arturo Espinosa's avatar
Arturo Espinosa committed
876
		p = text;
877

878
	v = value_new_string (p);
879

Arturo Espinosa's avatar
Arturo Espinosa committed
880
881
882
	g_free (new);
	g_free (old);
	g_free (text);
883
	if (s != NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
884
		subs_string_free (s);
885

Arturo Espinosa's avatar
Arturo Espinosa committed
886
	return v;
887
888
889
890
}

static char *help_dollar = {
	N_("@FUNCTION=DOLLAR\n"
891
	   "@SYNTAX=DOLLAR(num,[decimals])\n"
892
893
894
	   "@DESCRIPTION="
	   "Returns @num formatted as currency."
	   "\n"
895
896
897
	   "@SEEALSO=FIXED, TEXT, VALUE")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
898
/* FIXME: should use lc->[pn]_sign_posn, mon_thousands_sep, negative_sign */
899
static Value *
900
gnumeric_dollar (FunctionEvalInfo *ei, Value **argv)
901
{
Arturo Espinosa's avatar
Arturo Espinosa committed
902
903
904
	Value *v, *ag [3];
	guint len, neg;
	gchar *s;
Morten Welinder's avatar
Morten Welinder committed
905
	static int barfed = 0;
Arturo Espinosa's avatar
Arturo Espinosa committed
906

Morten Welinder's avatar
Morten Welinder committed
907
908
909
910
911
	if (!barfed) {
		g_warning ("GNUMERIC_DOLLAR is broken, it should use the "
			   "format_value routine");
		barfed = 1;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
912
913
914
	ag[0] = argv [0];
	ag[1] = argv [1];
	ag[2] = NULL;
Morten Welinder's avatar
Morten Welinder committed
915

Morten Welinder's avatar
Morten Welinder committed
916
	v = gnumeric_fixed (ei, ag);
917
	if (v == NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
918
		return NULL;
Morten Welinder's avatar
Morten Welinder committed
919

Arturo Espinosa's avatar
Arturo Espinosa committed
920
	g_assert (v->type == VALUE_STRING);
Morten Welinder's avatar
Morten Welinder committed
921

Arturo Espinosa's avatar
Arturo Espinosa committed
922
923
924
925
926
927
928
929
930
931
	len = strlen (v->v.str->str);
	neg = (v->v.str->str [0] == '-') ? 1 : 0;

	s = g_new (gchar, len + 2 + neg);
	strncpy (&s [1], v->v.str->str, len);

	string_unref (v->v.str);
	if (neg){
		s [0] = '(';
		s [len+1] = ')';
932
933
	}
	/* FIXME: should use *lc->currency_symbol */
Arturo Espinosa's avatar
Arturo Espinosa committed
934
935
936
937
938
	s[neg] = '$';
	s[len + 1 + neg] = '\0';
	v->v.str = string_get (s);
	g_free (s);
	return v;
939
940
}

941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
static char *help_search = {
	N_("@FUNCTION=SEARCH\n"
	   "@SYNTAX=SEARCH(text,within[,start_num])\n"
	   "@DESCRIPTION="
	   "Returns the location of a character or text string within "
	   "another string.  @text is the string or character to be searched. "
	   "@within is the string in which you want to search.  @start_num "
	   "is the start position of the search in @within.  If @start_num "
	   "is omitted, it is assumed to be one.  The search is not case "
	   "sensitive. "
	   "\n"
	   "@text can contain wildcard characters (*) and question marks (?) "
	   "to control the search.  A question mark matches with any "
	   "character and wildcard matches with any string including empty "
	   "string.  If you want the actual wildcard or question mark to "
	   "be searched, use tilde (~) before the character. "
	   "\n"
	   "If @text is not found, SEARCH returns #VALUE! error. "
	   "If @start_num is less than one or it is greater than the length "
	   "of @within, SEARCH returns #VALUE! error. "
	   "\n"
	   "@SEEALSO=FIND")
};

typedef struct {
        gchar    *str;
        int      min_skip;
        gboolean wildcard_prefix;
} string_search_t;


static int
wildcards_and_question_marks(gchar *find_str, int *qmarks, int *wildcard)
{
        int pos, skip=0;

	*wildcard = 0;
	for (pos=0; find_str[pos]; pos++)
	        if (find_str[pos] == '?')
		        ++skip;
	        else if (find_str[pos] == '*')
		        *wildcard = 1;
	        else
		        break;
	*qmarks = skip;

	return pos;
}


/* Breaks the regular expression into a list of string and skip pairs.
 */
static GSList *
parse_search_string(gchar *find_str)
{
        string_search_t *search_cond;
        GSList          *conditions = NULL;
	int             i, pos, qmarks;
	gboolean        wildcard;
        gchar           *buf, *p;

	buf = g_new(gchar, strlen(find_str) + 1);
	p = buf;
	i = 0;
	
	pos = wildcards_and_question_marks(find_str, &qmarks, &wildcard);
	wildcard = 1;
	find_str += pos;
	
	while (*find_str) {
	        if (*find_str == '~') {
		        buf[i++] = *(++find_str);
			find_str++;
		} else if (*find_str == '?' || *find_str == '*') {
		        buf[i] = '\0';
			search_cond = g_new(string_search_t, 1);
			search_cond->str = g_new(gchar, strlen(buf)+1);
			strcpy(search_cond->str, buf);
			search_cond->min_skip = qmarks;
			search_cond->wildcard_prefix = wildcard;
			conditions = g_slist_append(conditions, search_cond);
			i = 0;
			pos = wildcards_and_question_marks(find_str, &qmarks,
							   &wildcard);
			find_str += pos;
		} else
		        buf[i++] = *find_str++;
	}
	buf[i] = '\0';
	search_cond = g_new(string_search_t, 1);
	search_cond->str = g_new(gchar, strlen(buf)+1);
	strcpy(search_cond->str, buf);
	search_cond->min_skip = qmarks;
	search_cond->wildcard_prefix = wildcard;
	conditions = g_slist_append(conditions, search_cond);
	
	g_free(buf);

	return conditions;
}

/* Returns 1 if a given condition ('cond') matches with 'str'.  'match_start'
 * points to the beginning of the token found and 'match_end' points to the
 * end of the token.
 */
static int
match_string(gchar *str, string_search_t *cond, gchar **match_start, 
	     gchar **match_end)
{
        gchar *p;

        if (cond->min_skip > strlen(str))
1053
		return 0;
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063

	if (*cond->str == '\0') {
	         *match_start = str;
		 *match_end = str+1;
		 return 1;
	}
        p = strstr(str+cond->min_skip, cond->str);

	/* Check no match case */
	if (p == NULL)
1064
		return 0;
1065
1066
1067

	/* Check if match in a wrong place and no wildcard */
	if (! cond->wildcard_prefix && p > str+cond->min_skip)
1068
		return 0;
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095

	/* Matches correctly */
	*match_start = p-cond->min_skip;
	*match_end = p+strlen(cond->str);

	return 1;
}

static void
free_all_after_search(GSList *conditions, gchar *text, gchar *within)
{
        GSList          *current;
	string_search_t *current_cond;

	current = conditions;
	while (current != NULL) {
	        current_cond = current->data;
	        g_free(current_cond->str);
		g_free(current_cond);
		current = current->next;
	}
	g_slist_free(conditions);
	g_free(text);
	g_free(within);
}

static Value *
1096
gnumeric_search (FunctionEvalInfo *ei, Value **argv)
Jukka-Pekka Iivonen's avatar