functions.c 26.1 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
10
 */
#include <config.h>
#include <gnome.h>
11
#include <ctype.h>
12
#include <math.h>
13
14
15
16
17
#include "gnumeric.h"
#include "gnumeric-sheet.h"
#include "utils.h"
#include "func.h"

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
31
gnumeric_char (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
32
33
34
35
36
37
{
	char result [2];

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

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

41
42
43
44
45
46
47
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
48

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

52
static Value *
53
54
gnumeric_code (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
55
{
Morten Welinder's avatar
Morten Welinder committed
56
57
	unsigned char c;

58
	if (argv [0]->type != VALUE_STRING){
59
		*error_string = _("Type mismatch");
60
61
62
		return NULL;
	}

Morten Welinder's avatar
Morten Welinder committed
63
64
	c = argv [0]->v.str->str [0];
	return value_new_int (c);
65
66
}

67
68
69
70
71
static char *help_exact = {
	N_("@FUNCTION=EXACT\n"
	   "@SYNTAX=EXACT(string1, string2)\n"

	   "@DESCRIPTION="
72
73
	   "Returns true if string1 is exactly equal to string2 "
	   "(this routine is case sensitive)."
74
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
75

76
	   "@SEEALSO=LEN")  /* FIXME: DELTA, LEN, SEARCH */
77
78
};

79
static Value *
80
81
gnumeric_exact (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
82
83
{
	if (argv [0]->type != VALUE_STRING || argv [1]->type != VALUE_STRING){
84
		*error_string = _("Type mismatch");
85
86
87
		return NULL;
	}

88
89
	return value_new_int (strcmp (argv [0]->v.str->str,
				      argv [1]->v.str->str) == 0);
90
91
}

92
93
94
95
96
97
98
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
99

100
101
102
	   "@SEEALSO=CHAR, CODE")
};

103
static Value *
104
105
gnumeric_len (struct FunctionDefinition *i,
	      Value *argv [], char **error_string)
106
107
{
	if (argv [0]->type != VALUE_STRING){
108
		*error_string = _("Type mismatch");
109
110
111
		return NULL;
	}

Michael Meeks's avatar
Michael Meeks committed
112
	return value_new_int (strlen (argv [0]->v.str->str));
113
114
}

115
116
117
118
119
120
121
122
123
124
125
126
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 *
127
128
gnumeric_left (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
129
{
Arturo Espinosa's avatar
Arturo Espinosa committed
130
	Value *v;
131
132
133
	int count;
	char *s;

134
	if (argv[1])
Arturo Espinosa's avatar
Arturo Espinosa committed
135
		count = value_get_as_int(argv[1]);
136
137
	else
		count = 1;
Morten Welinder's avatar
Morten Welinder committed
138

139
	s = g_malloc (count + 1);
140
	strncpy (s, argv[0]->v.str->str, count);
141
142
	s [count] = 0;

Michael Meeks's avatar
Michael Meeks committed
143
	v = value_new_string (s);
144
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
145

146
147
148
	return v;
}

149
150
151
152
153
154
155
156
157
158
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")
};

159
static Value *
160
161
gnumeric_lower (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
162
163
164
{
	Value *v;
	char *s, *p;
Morten Welinder's avatar
Morten Welinder committed
165

166
	if (argv [0]->type != VALUE_STRING){
167
		*error_string = _("Type mismatch");
168
169
170
171
		return NULL;
	}

	v = g_new (Value, 1);
172
	v->type = VALUE_STRING;
173
	p = s = strdup (argv [0]->v.str->str);
174
175
176
177
178
	for (; *p; p++){
		*p = tolower (*p);
	}
	v->v.str = string_get (s);
	g_free (s);
179
180
181
182

	return v;
}

183
184
185
186
187
static char *help_mid = {
	N_("@FUNCTION=MID\n"
	   "@SYNTAX=MID(string, position, length)\n"

	   "@DESCRIPTION="
188
189
	   "Returns a substring from @string starting at @position for "
	   "@length characters."
190
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
191

192
193
194
195
196
197
198
199
200
201
202
203
204
	   "@SEEALSO=LEFT, RIGHT")
};

static Value *
gnumeric_mid (struct FunctionDefinition *i, Value *argv [], char **error)
{
	Value *v;
	int pos, len;
	char *s, *source;

	if (argv [0]->type != VALUE_STRING ||
	    argv [1]->type != VALUE_INTEGER ||
	    argv [2]->type != VALUE_INTEGER){
205
		*error = _("Type mismatch");
206
207
208
209
210
211
		return NULL;
	}

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

212
213
	if (len < 0 || pos <= 0){
		*error = _("Invalid arguments");
214
215
216
217
218
		return NULL;
	}

	source = argv [0]->v.str->str;
	if (pos > strlen (source))
Michael Meeks's avatar
Michael Meeks committed
219
		return value_new_string ("");
Arturo Espinosa's avatar
Arturo Espinosa committed
220
221
	pos--;
	s = g_new (gchar, len+1);
222
	strncpy (s, &source[pos], len);
Arturo Espinosa's avatar
Arturo Espinosa committed
223
	s[len] = '\0';
Michael Meeks's avatar
Michael Meeks committed
224
	v = value_new_string (s);
225
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
226

227
228
229
	return v;
}

230
231
232
233
234
static char *help_right = {
	N_("@FUNCTION=RIGHT\n"
	   "@SYNTAX=RIGHT(text[,num_chars])\n"

	   "@DESCRIPTION="
235
236
	   "Returns the rightmost num_chars characters or the right "
	   "character if num_chars is not specified"
237
238
239
240
241
	   "\n"
	   "@SEEALSO=MID, LEFT")
};

static Value *
242
243
gnumeric_right (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
244
{
Arturo Espinosa's avatar
Arturo Espinosa committed
245
	Value *v;
246
247
248
	int count, len;
	char *s;

249
	if (argv[1])
Arturo Espinosa's avatar
Arturo Espinosa committed
250
		count = value_get_as_int(argv[1]);
251
252
	else
		count = 1;
253

254
	len = strlen (argv[0]->v.str->str);
255
256
	if (count > len)
		count = len;
Morten Welinder's avatar
Morten Welinder committed
257

258
	s = g_malloc (count + 1);
259
	strncpy (s, argv[0]->v.str->str+len-count, count);
260
261
	s [count] = 0;

Michael Meeks's avatar
Michael Meeks committed
262
	v = value_new_string (s);
263
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
264

265
266
267
	return v;
}

268
269
270
271
272
273
274
275
276
277
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")
};

278
static Value *
279
280
gnumeric_upper (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
281
282
283
{
	Value *v;
	char *s, *p;
Morten Welinder's avatar
Morten Welinder committed
284

285
	if (argv [0]->type != VALUE_STRING){
286
		*error_string = _("Type mismatch");
287
288
289
290
		return NULL;
	}

	v = g_new (Value, 1);
291
	v->type = VALUE_STRING;
292
	p = s = strdup (argv [0]->v.str->str);
293
294
295
296
297
298

	for (;*p; p++){
		*p = toupper (*p);
	}
	v->v.str = string_get (s);
	g_free (s);
299
300
301

	return v;
}
302

303
304
305
static char *help_concatenate = {
	N_("@FUNCTION=CONCATENATE\n"
	   "@SYNTAX=CONCATENATE(string1[,string2...])\n"
306
307
308
	   "@DESCRIPTION="
	   "Returns up appended strings."
	   "\n"
309
310
311
312
	   "@SEEALSO=LEFT, MID, RIGHT")
};

static Value *
313
314
gnumeric_concatenate (Sheet *sheet, GList *l,
		      int eval_col, int eval_row, char **error_string)
315
316
317
{
	Value *v;
	char *s, *p, *tmp;
Morten Welinder's avatar
Morten Welinder committed
318

319
	if (l==NULL) {
Arturo Espinosa's avatar
Arturo Espinosa committed
320
321
		*error_string = _("Invalid number of arguments");
		return NULL;
322
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
323
324
	s = g_new(gchar, 1);
	*s = '\0';
Morten Welinder's avatar
Morten Welinder committed
325
	while ( l != NULL &&
326
327
328
		(v=eval_expr(sheet, l->data, eval_col, eval_row, error_string)) != NULL) {
/*
		if (v->type != VALUE_STRING) {
Arturo Espinosa's avatar
Arturo Espinosa committed
329
330
331
			*error_string = _("Invalid argument");
			value_release (v);
			return NULL;
332
333
		}
*/
Michael Meeks's avatar
Michael Meeks committed
334
		tmp = value_get_as_string (v);
335
		/* FIXME: this could be massively sped-up with strlen's etc... */
Arturo Espinosa's avatar
Arturo Espinosa committed
336
337
338
339
340
341
		p = g_strconcat (s, tmp, NULL);
		g_free (tmp);
		value_release (v);
		g_free (s);
		s = p;
		l = g_list_next (l);
342
	}
Morten Welinder's avatar
Morten Welinder committed
343

344
345
346
347
348
349
350
351
352
353
354
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (s);
	g_free (s);

	return v;
}

static char *help_rept = {
	N_("@FUNCTION=REPT\n"
	   "@SYNTAX=REPT(string,num)\n"
355
356
357
	   "@DESCRIPTION="
	   "Returns @num repetitions of @string."
	   "\n"
358
359
360
361
	   "@SEEALSO=CONCATENATE")
};

static Value *
362
363
gnumeric_rept (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
364
365
366
367
368
{
	Value *v;
	gchar *s, *p;
	gint num;
	guint len;
Morten Welinder's avatar
Morten Welinder committed
369

370
371
372
373
374
375
376
377
378
379
380
381
382
	if (argv [0]->type != VALUE_STRING) {
		*error_string = _("Type mismatch");
		return NULL;
	} else if ( (num=value_get_as_int(argv[1])) < 0) {
		*error_string = _("Invalid argument");
		return NULL;
	}

	len = strlen (argv[0]->v.str->str);
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	p = s = g_new (gchar, 1 + len * num);
	while (num--) {
Arturo Espinosa's avatar
Arturo Espinosa committed
383
		strncpy (p, argv[0]->v.str->str, len);
384
385
		p += len;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
386
	*p = '\0';
387
388
389
390
391
	v->v.str = string_get (s);
	g_free (s);

	return v;
}
392

393
394
395
396
static char *help_clean = {
	N_("@FUNCTION=CLEAN\n"
	   "@SYNTAX=CLEAN(string)\n"

397
398
399
	   "@DESCRIPTION="
	   "Cleans the string from any non-printable characters."
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
400

401
	   "@SEEALSO=")
402
403
404
};

static Value *
405
gnumeric_clean (FunctionDefinition *fn, Value *argv [], char **error_string)
406
{
407
408
	Value *res;
	char *copy, *p, *q;
Morten Welinder's avatar
Morten Welinder committed
409

410
	if (argv [0]->type != VALUE_STRING){
411
412
413
		*error_string = _("Type mismatch");
		return NULL;
	}
414
415
	p = argv [0]->v.str->str;
	copy = q = g_malloc (strlen (p) + 1);
Morten Welinder's avatar
Morten Welinder committed
416

417
418
419
	while (*p){
		if (isprint (*p))
			*q++ = *p;
420
421
		p++;
	}
422
	*q = 0;
423

424
425
426
427
428
429
	res = g_new (Value, 1);
	res->type = VALUE_STRING;
	res->v.str = string_get (copy);
	g_free (copy);

	return res;
430
}
431

432
433
434
static char *help_find = {
	N_("@FUNCTION=FIND\n"
	   "@SYNTAX=FIND(string1,string2[,start])\n"
435
436
437
438
439
	   "@DESCRIPTION="
	   "Returns position of @string1 in @string2 (case-sesitive), "
	   "searching only from character @start onwards (assumed 1 if "
	   "omitted)."
	   "\n"
440
441
442
443
	   "@SEEALSO=EXACT, LEN, MID, SEARCH")
};

static Value *
444
445
gnumeric_find (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
446
{
Arturo Espinosa's avatar
Arturo Espinosa committed
447
	Value *ret;
448
449
450
	int count;
	char *s, *p;

451
	if (argv[2])
Arturo Espinosa's avatar
Arturo Espinosa committed
452
		count = value_get_as_int(argv[2]);
453
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
454
		count = 1;
455
456

	if ( count > strlen(argv[1]->v.str->str) ||
457
	     count == 0) { /* start position too high or low */
Arturo Espinosa's avatar
Arturo Espinosa committed
458
459
		*error_string = _("Invalid argument");
		return NULL;
460
461
	}

Arturo Espinosa's avatar
Arturo Espinosa committed
462
	g_assert (count >= 1);
463
464
	s = argv[1]->v.str->str + count - 1;
	if ( (p = strstr(s, argv[0]->v.str->str)) == NULL ) {
Arturo Espinosa's avatar
Arturo Espinosa committed
465
466
		*error_string = _("Invalid argument");
		return NULL;
467
468
469
470
	}

	ret = g_new(Value, 1);
	ret->type = VALUE_INTEGER;
Arturo Espinosa's avatar
Arturo Espinosa committed
471
472
	ret->v.v_int = count + p - s;
	return ret;
473
474
475
476
}

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

479
480
481
482
483
	   "@DESCRIPTION="
	   "Returns @num as a formatted string with @decimals numbers "
	   "after the decimal point, omitting commas if requested by "
	   "@no_commas."
	   "\n"
484
485
486
487
	   "@SEEALSO=")
};

static Value *
488
489
gnumeric_fixed (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
490
491
492
{
	Value *v;
	gchar *s, *p, *f;
493
	gint dec, commas, tmp;
494
495
	float_t num;

Michael Meeks's avatar
Michael Meeks committed
496
	num = value_get_as_float (argv[0]);
497
498
499
	if (argv[1])
		dec = value_get_as_int (argv[1]);
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
500
		dec = 2;
501
502

	if (argv[2])
Michael Meeks's avatar
Michael Meeks committed
503
		commas = !value_get_as_bool (argv[2], &tmp);
504
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
505
		commas = TRUE;
506

507
	if (dec >= 1000) { /* else buffer under-run */
Arturo Espinosa's avatar
Arturo Espinosa committed
508
509
		*error_string = _("Invalid argument");
		return NULL;
510
511
		/*
	} else if (lc->thousands_sep[1] != '\0') {
Arturo Espinosa's avatar
Arturo Espinosa committed
512
		fprintf (stderr, "thousands_sep:\"%s\"\n", lc->thousands_sep);
513
		*error_string = _("Invalid thousands separator");
Arturo Espinosa's avatar
Arturo Espinosa committed
514
		return NULL;
515
516
517
518
519
		*/
	} else if (dec <= 0) { /* no decimal point : just round and pad 0's */
		dec *= -1;
		num /= pow(10, dec);
		if (num < 1 && num > -1) {
Arturo Espinosa's avatar
Arturo Espinosa committed
520
521
			s = g_strdup("0");
			commas = 0;
522
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
523
524
525
			f = g_strdup("%00?s%.0f%.00?u"); /* commas, no point, 0's */
			tmp = dec;
			dec += log10(fabs(num));
526
			if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
527
528
				commas = dec / 3;
			p = &f[13]; /* last 0 in trailing 0's count */
529
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
530
531
532
533
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			tmp = commas;
			p = &f[3]; /* last 0 in leading blank spaces for commas */
534
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
535
536
537
538
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			s = g_strdup_printf (f, "", num, 0);
			g_free (f);
539
540
		}
	} else { /* decimal point format */
Arturo Espinosa's avatar
Arturo Espinosa committed
541
542
543
		f = g_strdup("%00?s%.00?f");
		tmp = dec;
		dec = log10(fabs(num));
544
		if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
545
546
			commas = dec / 3;
		p = &f[9];
547
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
548
549
550
551
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		tmp = commas;
		p = &f[3];
552
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
553
554
555
556
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		s = g_strdup_printf (f, "", num);
		g_free (f);
557
558
	}
	if (commas) {
Arturo Espinosa's avatar
Arturo Espinosa committed
559
560
		p = s;
		f = &s[commas];
561
		if (*f == '-')
Arturo Espinosa's avatar
Arturo Espinosa committed
562
563
			*p++ = *f++;
		dec -= 2;
564
		while (dec-- > 0) {
Arturo Espinosa's avatar
Arturo Espinosa committed
565
			*p++ = *f++;
566
567
			if (dec%3 == 0)
				/* FIXME: should use lc->thousands_sep[0] */
Arturo Espinosa's avatar
Arturo Espinosa committed
568
				*p++ = ',';
569
570
571
572
573
574
575
576
577
578
		}
	}

	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (s);
	g_free (s);
	return v;
}

Arturo Espinosa's avatar
Arturo Espinosa committed
579
580
/*
 * proper could be a LOT nicer
581
 * (e.g. "US Of A" -> "US of A", "Cent'S Worth" -> "Cent's Worth")
Arturo Espinosa's avatar
Arturo Espinosa committed
582
583
 * but this is how Excel does it
 */
584
585
586
587
static char *help_proper = {
	N_("@FUNCTION=PROPER\n"
	   "@SYNTAX=PROPER(string)\n"

588
589
590
	   "@DESCRIPTION="
	   "Returns @string with initial of each word capitalised."
	   "\n"
591
592
593
594
	   "@SEEALSO=LOWER, UPPER")
};

static Value *
595
596
gnumeric_proper (struct FunctionDefinition *i,
		 Value *argv [], char **error_string)
597
598
599
600
601
602
603
604
605
606
607
608
{
	Value *v;
	gchar *s, *p;
	gboolean inword = FALSE;

	if (argv [0]->type != VALUE_STRING) {
		*error_string = _("Type mismatch");
		return NULL;
	}

	s = p = argv[0]->v.str->str;
	while (*s) {
Morten Welinder's avatar
Morten Welinder committed
609
		if (isalpha((unsigned char)*s)) {
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
			if (inword) {
				*s = tolower(*s);
			} else {
				*s = toupper(*s);
				inword = TRUE;
			}
		} else
			inword = FALSE;
		s++;
	}

	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (p);
	return v;
}
Morten Welinder's avatar
Morten Welinder committed
626

627
628
629
static char *help_replace = {
	N_("@FUNCTION=REPLACE\n"
	   "@SYNTAX=REPLACE(old,start,num,new)\n"
630
631
632
	   "@DESCRIPTION="
	   "Returns @old with @new replacing @num characters from @start."
	   "\n"
633
634
635
636
	   "@SEEALSO=MID, SEARCH, SUBSTITUTE, TRIM")
};

static Value *
637
638
gnumeric_replace (struct FunctionDefinition *i,
		  Value *argv [], char **error_string)
639
640
{
	Value *v;
641
	gchar *s;
642
643
644
645
646
647
648
649
650
	gint start, num, oldlen, newlen;

	if (argv[0]->type != VALUE_STRING ||
	    argv[1]->type != VALUE_INTEGER ||
	    argv[2]->type != VALUE_INTEGER ||
	    argv[3]->type != VALUE_STRING ) {
		*error_string = _("Type mismatch");
		return NULL;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
651
652
653

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

656
657
658
659
	if (start <= 0 || num <= 0 || --start + num > oldlen ) {
		*error_string = _("Invalid arguments");
		return NULL;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
660
661
662
663

	newlen = strlen (argv [3]->v.str->str);

	s = g_new (gchar, 1 + newlen + oldlen - num);
664
665
	strncpy (s, argv[0]->v.str->str, start);
	strncpy (&s[start], argv[3]->v.str->str, newlen);
666
667
	strncpy (&s[start+newlen], &argv[0]->v.str->str[start+num],
		 oldlen - num - start );
Arturo Espinosa's avatar
Arturo Espinosa committed
668
669
670

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

671
672
673
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (s);
Arturo Espinosa's avatar
Arturo Espinosa committed
674

675
	g_free(s);
Arturo Espinosa's avatar
Arturo Espinosa committed
676

677
678
679
680
681
682
	return v;
}

static char *help_t = {
	N_("@FUNCTION=T\n"
	   "@SYNTAX=T(value)\n"
683
684
685
	   "@DESCRIPTION="
	   "Returns @value if and only if it is text, otherwise a blank "
	   "string.\n"
686
687
688
689
	   "@SEEALSO=CELL, N, VALUE")
};

static Value *
690
691
gnumeric_t (struct FunctionDefinition *i,
	    Value *argv [], char **error_string)
692
693
{
	Value *v;
Arturo Espinosa's avatar
Arturo Espinosa committed
694

695
696
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
Arturo Espinosa's avatar
Arturo Espinosa committed
697
698
699
700
701
702

	if (argv [0]->type == VALUE_STRING)
		v->v.str = string_get (argv[0]->v.str->str);
	else
		v->v.str = string_get ("");

703
704
705
706
707
708
	return v;
}

static char *help_trim = {
	N_("@FUNCTION=TRIM\n"
	   "@SYNTAX=TRIM(text)\n"
709
710
711
	   "@DESCRIPTION="
	   "Returns @text with only single spaces between words."
	   "\n"
712
713
714
715
	   "@SEEALSO=CLEAN, MID, REPLACE, SUBSTITUTE")
};

static Value *
716
717
gnumeric_trim (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
718
719
720
721
722
723
724
725
726
{
	Value *v;
	gchar *new, *dest, *src;
	gboolean space = TRUE;

	if (argv[0]->type != VALUE_STRING) {
		*error_string = _("Type mismatch");
		return NULL;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
727
728
729
730
731
732

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

	while (*src){
		if (*src == ' '){
733
734
			if (!space) {
				*dest++ = *src;
Arturo Espinosa's avatar
Arturo Espinosa committed
735
				space = TRUE;
736
737
			}
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
738
739
			space = FALSE;
			*dest++ = *src;
740
		}
Arturo Espinosa's avatar
Arturo Espinosa committed
741
		src++;
742
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
743
744
745
	if (space && dest > new)
		dest--;

746
	*dest = '\0';
Arturo Espinosa's avatar
Arturo Espinosa committed
747

748
749
750
751
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (new);
	g_free(new);
Arturo Espinosa's avatar
Arturo Espinosa committed
752

753
754
755
756
757
758
	return v;
}

static char *help_value = {
	N_("@FUNCTION=VALUE\n"
	   "@SYNTAX=VALUE(text)\n"
759
760
761
	   "@DESCRIPTION="
	   "Returns numeric value of @text."
	   "\n"
762
763
764
765
	   "@SEEALSO=DOLLAR, FIXED, TEXT")
};

static Value *
766
767
gnumeric_value (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
768
769
770
771
772
773
774
775
776
/* FIXME: in Excel, VALUE("$1, 000") = 1000, and dates etc. supported */
{
	Value *v;
	if (argv[0]->type != VALUE_STRING) {
		*error_string = _("Type mismatch");
		return NULL;
	}
	v = g_new (Value, 1);
	v->type = VALUE_FLOAT;
Michael Meeks's avatar
Michael Meeks committed
777
	v->v.v_float = value_get_as_float (argv[0]);
778
779
780
781
	return v;
}

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

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

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

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

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

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

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

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

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

static Value *
832
833
gnumeric_substitute (struct FunctionDefinition *i,
		     Value *argv [], char **error_string)
834
{
Arturo Espinosa's avatar
Arturo Espinosa committed
835
836
837
838
839
	Value *v;
	gchar *text, *old, *new, *p ,*f;
	gint num;
	guint oldlen, newlen, len, inst;
	struct subs_string *s;
840

Michael Meeks's avatar
Michael Meeks committed
841
842
843
	text = value_get_as_string (argv[0]);
	old  = value_get_as_string (argv[1]);
	new  = value_get_as_string (argv[2]);
844

845
	if (argv[3])
Arturo Espinosa's avatar
Arturo Espinosa committed
846
		num = value_get_as_int (argv[3]);
847
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
848
		num = 0;
849

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

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

Arturo Espinosa's avatar
Arturo Espinosa committed
881
882
883
	v = g_new (Value, 1);
	v->type = VALUE_STRING;
	v->v.str = string_get (p);
884

Arturo Espinosa's avatar
Arturo Espinosa committed
885
886
887
	g_free (new);
	g_free (old);
	g_free (text);
888
	if (s != NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
889
		subs_string_free (s);
890

Arturo Espinosa's avatar
Arturo Espinosa committed
891
	return v;
892
893
894
895
}

static char *help_dollar = {
	N_("@FUNCTION=DOLLAR\n"
896
	   "@SYNTAX=DOLLAR(num,[decimals])\n"
897
898
899
	   "@DESCRIPTION="
	   "Returns @num formatted as currency."
	   "\n"
900
901
902
	   "@SEEALSO=FIXED, TEXT, VALUE")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
903
/* FIXME: should use lc->[pn]_sign_posn, mon_thousands_sep, negative_sign */
904
static Value *
905
906
gnumeric_dollar (struct FunctionDefinition *i,
		 Value *argv [], char **error_string)
907
{
Arturo Espinosa's avatar
Arturo Espinosa committed
908
909
910
911
	Value *v, *ag [3];
	guint len, neg;
	gchar *s;

912
913
	g_warning ("GNUMERIC_DOLLAR is broken, it should use the "
		   "format_value routine");
Arturo Espinosa's avatar
Arturo Espinosa committed
914
915
916
	ag[0] = argv [0];
	ag[1] = argv [1];
	ag[2] = NULL;
Morten Welinder's avatar
Morten Welinder committed
917

Arturo Espinosa's avatar
Arturo Espinosa committed
918
	v = gnumeric_fixed (i, ag, error_string);
919
	if (v == NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
920
		return NULL;
Morten Welinder's avatar
Morten Welinder committed
921

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

Arturo Espinosa's avatar
Arturo Espinosa committed
924
925
926
927
928
929
930
931
932
933
	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] = ')';
934
935
	}
	/* FIXME: should use *lc->currency_symbol */
Arturo Espinosa's avatar
Arturo Espinosa committed
936
937
938
939
940
	s[neg] = '$';
	s[len + 1 + neg] = '\0';
	v->v.str = string_get (s);
	g_free (s);
	return v;
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
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
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
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
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))
	        return 0;

	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)
	        return 0;

	/* Check if match in a wrong place and no wildcard */
	if (! cond->wildcard_prefix && p > str+cond->min_skip)
	        return 0;

	/* 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 *
gnumeric_search (struct FunctionDefinition *i,
		 Value *argv [], char **error_string)
{
        GSList          *conditions, *current;
	string_search_t *current_cond;
        int             ret, start_num, within_len;
	gchar           *text, *within, *match_str, *match_str_next;
	gchar           *p_start, *p_end;

	if (argv[2] == NULL)
	        start_num = 0;
	else
	        start_num = value_get_as_int(argv[2]) - 1;

	text = g_new(gchar, strlen(argv[0]->v.str->str) + 1);
	within = g_new(gchar, strlen(argv[1]->v.str->str) + 1);
	strcpy(text, argv[0]->v.str->str);
	strcpy(within, argv[1]->v.str->str);
	g_strdown(text);
	g_strdown(within);
	
	within_len = strlen(within);

	if (within_len <= start_num) {
	        g_free(text);
		g_free(within);
		*error_string = _("#VALUE!");
		return NULL;
	}

	conditions = parse_search_string(text);
	if (conditions == NULL) {
	        g_free(text);
		g_free(within);
		*error_string = _("#VALUE!");
		return NULL;
	}

	match_str = within;

match_again:
	current = conditions;
	current_cond = current->data;
	ret = match_string(match_str, current_cond, &p_start, &p_end);
	if (ret) {
	        current = current->next;
		if (current == NULL) {
			free_all_after_search(conditions, text, within);
			return value_new_int (p_start - within + 1);
		}
		current_cond = current->data;
		match_str = p_start;
		match_str_next = p_end;
		while (match_string(p_end, current_cond, &p_start, &p_end)) {
		        current = current->next;
			if (current == NULL) {
				free_all_after_search(conditions,
						      text, within);
				return value_new_int (match_str - within + 1);
			}
			current_cond = current->data;
		}
		match_str = match_str_next;
		goto match_again;
	}

	free_all_after_search(conditions, text, within);
	*error_string = _("#VALUE!");
	return NULL;
}


1170
FunctionDefinition string_functions [] = {
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
	{ "char",       "f",    "number",
	  &help_char,       NULL, gnumeric_char },
	{ "clean",      "s",    "text",
	  &help_clean,      NULL, gnumeric_clean },
	{ "code",       "s",    "text",
	  &help_code,       NULL, gnumeric_code },
	{ "concatenate",0,      "text1,text2", 
	  &help_concatenate,gnumeric_concatenate, NULL },
	{ "dollar",     "f|f",  "num,decimals",
	  &help_dollar,     NULL, gnumeric_dollar },
	{ "exact",      "ss",   "text1,text2",
	  &help_exact,      NULL, gnumeric_exact },
	{ "find",       "ss|f", "text1,text2,num",
	  &help_find,       NULL, gnumeric_find },
	{ "fixed",      "f|fb", "num,decs,no_commas",
	  &help_fixed,      NULL, gnumeric_fixed },
	{ "left",       "s|f",  "text,num_chars",
	  &help_left,       NULL, gnumeric_left },
	{ "len",        "s",    "text",
	  &help_len,        NULL, gnumeric_len },
	{ "lower",      "s",    "text",
	  &help_lower,      NULL, gnumeric_lower },
	{ "proper",     "s",    "text",
	  &help_proper,     NULL, gnumeric_proper },
        { "mid",        "sff",  "text,pos,num",
	  &help_mid,        NULL, gnumeric_mid },
	{ "replace",    "sffs", "old,start,num,new",
	  &help_replace,    NULL, gnumeric_replace },
	{ "rept",       "sf",   "text,num",
          &help_rept,       NULL, gnumeric_rept },
	{ "right",      "s|f",  "text,num_chars",
	  &help_right,      NULL, gnumeric_right },
	{ "search",     "ss|f",  "find,within[,start_num]",
	  &help_search,     NULL, gnumeric_search },
	{ "substitute", "sss|f","text,old,new,num",
	  &help_substitute, NULL, gnumeric_substitute },
	{ "t",          "?",    "value",
	  &help_t,          NULL, gnumeric_t },
	{ "trim",       "s",    "text",
	  &help_trim,       NULL, gnumeric_trim },
	{ "upper",      "s",    "text",
	  &help_upper,      NULL, gnumeric_upper },
	{ "value",      "s",    "text",
	  &help_value,      NULL, gnumeric_value },
1215
1216
	{ NULL, NULL },
};
1217

1218
1219
/* Missing:
 * TEXT(number,format) formats number
1220
 */