fn-string.c 25.5 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 15
#include "gnumeric.h"
#include "utils.h"
#include "func.h"

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

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

24 25 26
	   "@SEEALSO=CODE")
};

27
static Value *
28 29
gnumeric_char (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
30 31 32 33 34 35
{
	char result [2];

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

36
	return value_new_string (result);
37 38
}

39 40 41 42 43 44 45
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
46

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

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

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

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

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

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

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

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

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

90 91 92 93 94 95 96
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
97

98 99 100
	   "@SEEALSO=CHAR, CODE")
};

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

110
	return value_new_int (strlen (argv [0]->v.str->str));
111 112
}

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

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

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

141
	v = value_new_string (s);
142
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
143

144 145 146
	return v;
}

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

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

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

169
	p = s = g_strdup (argv [0]->v.str->str);
170 171 172
	for (; *p; p++){
		*p = tolower (*p);
	}
173
	v = value_new_string (s);
174
	g_free (s);
175 176 177 178

	return v;
}

179 180 181 182 183
static char *help_mid = {
	N_("@FUNCTION=MID\n"
	   "@SYNTAX=MID(string, position, length)\n"

	   "@DESCRIPTION="
184 185
	   "Returns a substring from @string starting at @position for "
	   "@length characters."
186
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
187

188 189 190 191 192 193 194 195 196 197 198 199 200
	   "@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){
201
		*error = _("Type mismatch");
202 203 204 205 206 207
		return NULL;
	}

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

208 209
	if (len < 0 || pos <= 0){
		*error = _("Invalid arguments");
210 211 212 213 214
		return NULL;
	}

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

223 224 225
	return v;
}

226 227 228 229 230
static char *help_right = {
	N_("@FUNCTION=RIGHT\n"
	   "@SYNTAX=RIGHT(text[,num_chars])\n"

	   "@DESCRIPTION="
231 232
	   "Returns the rightmost num_chars characters or the right "
	   "character if num_chars is not specified"
233 234 235 236 237
	   "\n"
	   "@SEEALSO=MID, LEFT")
};

static Value *
238 239
gnumeric_right (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
240
{
Arturo Espinosa's avatar
Arturo Espinosa committed
241
	Value *v;
242 243 244
	int count, len;
	char *s;

245
	if (argv[1])
Arturo Espinosa's avatar
Arturo Espinosa committed
246
		count = value_get_as_int(argv[1]);
247 248
	else
		count = 1;
249

250
	len = strlen (argv[0]->v.str->str);
251 252
	if (count > len)
		count = len;
Morten Welinder's avatar
Morten Welinder committed
253

254
	s = g_malloc (count + 1);
255
	strncpy (s, argv[0]->v.str->str+len-count, count);
256 257
	s [count] = 0;

258
	v = value_new_string (s);
259
	g_free (s);
Morten Welinder's avatar
Morten Welinder committed
260

261 262 263
	return v;
}

264 265 266 267 268 269 270 271 272 273
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")
};

274
static Value *
275 276
gnumeric_upper (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
277 278
{
	Value *v;
279
	unsigned char *s, *p;
Morten Welinder's avatar
Morten Welinder committed
280

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

286
	p = s = g_strdup (argv [0]->v.str->str);
287 288 289 290

	for (;*p; p++){
		*p = toupper (*p);
	}
291
	v = value_new_string (s);
292
	g_free (s);
293 294 295

	return v;
}
296

297 298 299
static char *help_concatenate = {
	N_("@FUNCTION=CONCATENATE\n"
	   "@SYNTAX=CONCATENATE(string1[,string2...])\n"
300 301 302
	   "@DESCRIPTION="
	   "Returns up appended strings."
	   "\n"
303 304 305 306
	   "@SEEALSO=LEFT, MID, RIGHT")
};

static Value *
307 308
gnumeric_concatenate (Sheet *sheet, GList *l,
		      int eval_col, int eval_row, char **error_string)
309 310 311
{
	Value *v;
	char *s, *p, *tmp;
Morten Welinder's avatar
Morten Welinder committed
312

313
	if (l==NULL) {
Arturo Espinosa's avatar
Arturo Espinosa committed
314 315
		*error_string = _("Invalid number of arguments");
		return NULL;
316
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
317 318
	s = g_new(gchar, 1);
	*s = '\0';
Morten Welinder's avatar
Morten Welinder committed
319
	while ( l != NULL &&
320 321 322
		(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
323 324 325
			*error_string = _("Invalid argument");
			value_release (v);
			return NULL;
326 327
		}
*/
328
		tmp = value_get_as_string (v);
329
		/* FIXME: this could be massively sped-up with strlen's etc... */
Arturo Espinosa's avatar
Arturo Espinosa committed
330 331 332 333 334 335
		p = g_strconcat (s, tmp, NULL);
		g_free (tmp);
		value_release (v);
		g_free (s);
		s = p;
		l = g_list_next (l);
336
	}
Morten Welinder's avatar
Morten Welinder committed
337

338
	v = value_new_string (s);
339 340 341 342 343 344 345 346
	g_free (s);

	return v;
}

static char *help_rept = {
	N_("@FUNCTION=REPT\n"
	   "@SYNTAX=REPT(string,num)\n"
347 348 349
	   "@DESCRIPTION="
	   "Returns @num repetitions of @string."
	   "\n"
350 351 352 353
	   "@SEEALSO=CONCATENATE")
};

static Value *
354 355
gnumeric_rept (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
356 357 358 359 360
{
	Value *v;
	gchar *s, *p;
	gint num;
	guint len;
Morten Welinder's avatar
Morten Welinder committed
361

362 363 364 365 366 367 368 369 370 371 372
	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);
	p = s = g_new (gchar, 1 + len * num);
	while (num--) {
Arturo Espinosa's avatar
Arturo Espinosa committed
373
		strncpy (p, argv[0]->v.str->str, len);
374 375
		p += len;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
376
	*p = '\0';
377
	v = value_new_string (s);
378 379 380 381
	g_free (s);

	return v;
}
382

383 384 385 386
static char *help_clean = {
	N_("@FUNCTION=CLEAN\n"
	   "@SYNTAX=CLEAN(string)\n"

387 388 389
	   "@DESCRIPTION="
	   "Cleans the string from any non-printable characters."
	   "\n"
Morten Welinder's avatar
Morten Welinder committed
390

391
	   "@SEEALSO=")
392 393 394
};

static Value *
395
gnumeric_clean (FunctionDefinition *fn, Value *argv [], char **error_string)
396
{
397
	Value *res;
398
	unsigned char *copy, *p, *q;
Morten Welinder's avatar
Morten Welinder committed
399

400
	if (argv [0]->type != VALUE_STRING){
401 402 403
		*error_string = _("Type mismatch");
		return NULL;
	}
404 405
	p = argv [0]->v.str->str;
	copy = q = g_malloc (strlen (p) + 1);
Morten Welinder's avatar
Morten Welinder committed
406

407 408 409
	while (*p){
		if (isprint (*p))
			*q++ = *p;
410 411
		p++;
	}
412
	*q = 0;
413

414
	res = value_new_string (copy);
415 416 417
	g_free (copy);

	return res;
418
}
419

420 421 422
static char *help_find = {
	N_("@FUNCTION=FIND\n"
	   "@SYNTAX=FIND(string1,string2[,start])\n"
423 424 425 426 427
	   "@DESCRIPTION="
	   "Returns position of @string1 in @string2 (case-sesitive), "
	   "searching only from character @start onwards (assumed 1 if "
	   "omitted)."
	   "\n"
428 429 430 431
	   "@SEEALSO=EXACT, LEN, MID, SEARCH")
};

static Value *
432 433
gnumeric_find (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
434 435 436 437
{
	int count;
	char *s, *p;

438
	if (argv[2])
Arturo Espinosa's avatar
Arturo Espinosa committed
439
		count = value_get_as_int(argv[2]);
440
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
441
		count = 1;
442 443

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

Arturo Espinosa's avatar
Arturo Espinosa committed
449
	g_assert (count >= 1);
450 451
	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
452 453
		*error_string = _("Invalid argument");
		return NULL;
454 455
	}

456
	return value_new_int (count + p - s);
457 458 459 460
}

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

463 464 465 466 467
	   "@DESCRIPTION="
	   "Returns @num as a formatted string with @decimals numbers "
	   "after the decimal point, omitting commas if requested by "
	   "@no_commas."
	   "\n"
468 469 470 471
	   "@SEEALSO=")
};

static Value *
472 473
gnumeric_fixed (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
474 475 476
{
	Value *v;
	gchar *s, *p, *f;
477
	gint dec, commas, tmp;
478 479
	float_t num;

480
	num = value_get_as_float (argv[0]);
481 482 483
	if (argv[1])
		dec = value_get_as_int (argv[1]);
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
484
		dec = 2;
485 486

	if (argv[2])
487
		commas = !value_get_as_bool (argv[2], &tmp);
488
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
489
		commas = TRUE;
490

491
	if (dec >= 1000) { /* else buffer under-run */
Arturo Espinosa's avatar
Arturo Espinosa committed
492 493
		*error_string = _("Invalid argument");
		return NULL;
494 495
		/*
	} else if (lc->thousands_sep[1] != '\0') {
Arturo Espinosa's avatar
Arturo Espinosa committed
496
		fprintf (stderr, "thousands_sep:\"%s\"\n", lc->thousands_sep);
497
		*error_string = _("Invalid thousands separator");
Arturo Espinosa's avatar
Arturo Espinosa committed
498
		return NULL;
499
		*/
500
	} else if (dec <= 0) { /* no decimal point: just round and pad 0's */
501 502 503
		dec *= -1;
		num /= pow(10, dec);
		if (num < 1 && num > -1) {
Arturo Espinosa's avatar
Arturo Espinosa committed
504 505
			s = g_strdup("0");
			commas = 0;
506
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
507 508 509
			f = g_strdup("%00?s%.0f%.00?u"); /* commas, no point, 0's */
			tmp = dec;
			dec += log10(fabs(num));
510
			if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
511 512
				commas = dec / 3;
			p = &f[13]; /* last 0 in trailing 0's count */
513
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
514 515 516 517
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			tmp = commas;
			p = &f[3]; /* last 0 in leading blank spaces for commas */
518
			do
Arturo Espinosa's avatar
Arturo Espinosa committed
519 520 521 522
				*p-- = '0' + (tmp % 10);
			while (tmp /= 10);
			s = g_strdup_printf (f, "", num, 0);
			g_free (f);
523 524
		}
	} else { /* decimal point format */
Arturo Espinosa's avatar
Arturo Espinosa committed
525 526 527
		f = g_strdup("%00?s%.00?f");
		tmp = dec;
		dec = log10(fabs(num));
528
		if (commas)
Arturo Espinosa's avatar
Arturo Espinosa committed
529 530
			commas = dec / 3;
		p = &f[9];
531
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
532 533 534 535
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		tmp = commas;
		p = &f[3];
536
		do
Arturo Espinosa's avatar
Arturo Espinosa committed
537 538 539 540
			*p-- = '0' + (tmp % 10);
		while (tmp /= 10);
		s = g_strdup_printf (f, "", num);
		g_free (f);
541 542
	}
	if (commas) {
Arturo Espinosa's avatar
Arturo Espinosa committed
543 544
		p = s;
		f = &s[commas];
545
		if (*f == '-')
Arturo Espinosa's avatar
Arturo Espinosa committed
546 547
			*p++ = *f++;
		dec -= 2;
548
		while (dec-- > 0) {
Arturo Espinosa's avatar
Arturo Espinosa committed
549
			*p++ = *f++;
550 551
			if (dec%3 == 0)
				/* FIXME: should use lc->thousands_sep[0] */
Arturo Espinosa's avatar
Arturo Espinosa committed
552
				*p++ = ',';
553 554 555
		}
	}

556
	v = value_new_string (s);
557 558 559 560
	g_free (s);
	return v;
}

Arturo Espinosa's avatar
Arturo Espinosa committed
561 562
/*
 * proper could be a LOT nicer
563
 * (e.g. "US Of A" -> "US of A", "Cent'S Worth" -> "Cent's Worth")
Arturo Espinosa's avatar
Arturo Espinosa committed
564 565
 * but this is how Excel does it
 */
566 567 568 569
static char *help_proper = {
	N_("@FUNCTION=PROPER\n"
	   "@SYNTAX=PROPER(string)\n"

570 571 572
	   "@DESCRIPTION="
	   "Returns @string with initial of each word capitalised."
	   "\n"
573 574 575 576
	   "@SEEALSO=LOWER, UPPER")
};

static Value *
577 578
gnumeric_proper (struct FunctionDefinition *i,
		 Value *argv [], char **error_string)
579 580
{
	Value *v;
581
	unsigned char *s, *p;
582 583 584 585 586 587 588
	gboolean inword = FALSE;

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

589
	s = p = g_strdup (argv[0]->v.str->str);
590
	while (*s) {
591
		if (isalpha(*s)) {
592 593 594 595 596 597 598 599 600 601 602
			if (inword) {
				*s = tolower(*s);
			} else {
				*s = toupper(*s);
				inword = TRUE;
			}
		} else
			inword = FALSE;
		s++;
	}

603
	v = value_new_string (p);
604
	g_free (p);
605 606
	return v;
}
Morten Welinder's avatar
Morten Welinder committed
607

608 609 610
static char *help_replace = {
	N_("@FUNCTION=REPLACE\n"
	   "@SYNTAX=REPLACE(old,start,num,new)\n"
611 612 613
	   "@DESCRIPTION="
	   "Returns @old with @new replacing @num characters from @start."
	   "\n"
614 615 616 617
	   "@SEEALSO=MID, SEARCH, SUBSTITUTE, TRIM")
};

static Value *
618 619
gnumeric_replace (struct FunctionDefinition *i,
		  Value *argv [], char **error_string)
620 621
{
	Value *v;
622
	gchar *s;
623 624 625 626 627 628 629 630 631
	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
632 633 634

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

637 638 639 640
	if (start <= 0 || num <= 0 || --start + num > oldlen ) {
		*error_string = _("Invalid arguments");
		return NULL;
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
641 642 643 644

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

	s = g_new (gchar, 1 + newlen + oldlen - num);
645 646
	strncpy (s, argv[0]->v.str->str, start);
	strncpy (&s[start], argv[3]->v.str->str, newlen);
647 648
	strncpy (&s[start+newlen], &argv[0]->v.str->str[start+num],
		 oldlen - num - start );
Arturo Espinosa's avatar
Arturo Espinosa committed
649 650 651

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

652
	v = value_new_string (s);
Arturo Espinosa's avatar
Arturo Espinosa committed
653

654
	g_free(s);
Arturo Espinosa's avatar
Arturo Espinosa committed
655

656 657 658 659 660 661
	return v;
}

static char *help_t = {
	N_("@FUNCTION=T\n"
	   "@SYNTAX=T(value)\n"
662 663 664
	   "@DESCRIPTION="
	   "Returns @value if and only if it is text, otherwise a blank "
	   "string.\n"
665 666 667 668
	   "@SEEALSO=CELL, N, VALUE")
};

static Value *
669 670
gnumeric_t (struct FunctionDefinition *i,
	    Value *argv [], char **error_string)
671
{
Morten Welinder's avatar
Morten Welinder committed
672 673 674
	if (argv[0]->type == VALUE_STRING)
		return value_duplicate (argv[0]);
	else
675
		return value_new_string ("");
676 677 678 679 680
}

static char *help_trim = {
	N_("@FUNCTION=TRIM\n"
	   "@SYNTAX=TRIM(text)\n"
681 682 683
	   "@DESCRIPTION="
	   "Returns @text with only single spaces between words."
	   "\n"
684 685 686 687
	   "@SEEALSO=CLEAN, MID, REPLACE, SUBSTITUTE")
};

static Value *
688 689
gnumeric_trim (struct FunctionDefinition *i,
	       Value *argv [], char **error_string)
690 691 692 693 694 695 696 697 698
{
	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
699 700 701 702 703 704

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

	while (*src){
		if (*src == ' '){
705 706
			if (!space) {
				*dest++ = *src;
Arturo Espinosa's avatar
Arturo Espinosa committed
707
				space = TRUE;
708 709
			}
		} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
710 711
			space = FALSE;
			*dest++ = *src;
712
		}
Arturo Espinosa's avatar
Arturo Espinosa committed
713
		src++;
714
	}
Arturo Espinosa's avatar
Arturo Espinosa committed
715 716 717
	if (space && dest > new)
		dest--;

718
	*dest = '\0';
Arturo Espinosa's avatar
Arturo Espinosa committed
719

720
	v = value_new_string (new);
721
	g_free(new);
Arturo Espinosa's avatar
Arturo Espinosa committed
722

723 724 725 726 727 728
	return v;
}

static char *help_value = {
	N_("@FUNCTION=VALUE\n"
	   "@SYNTAX=VALUE(text)\n"
729 730 731
	   "@DESCRIPTION="
	   "Returns numeric value of @text."
	   "\n"
732 733 734 735
	   "@SEEALSO=DOLLAR, FIXED, TEXT")
};

static Value *
736 737
gnumeric_value (struct FunctionDefinition *i,
		Value *argv [], char **error_string)
738 739 740 741 742 743
/* FIXME: in Excel, VALUE("$1, 000") = 1000, and dates etc. supported */
{
	if (argv[0]->type != VALUE_STRING) {
		*error_string = _("Type mismatch");
		return NULL;
	}
744
	return value_new_float (value_get_as_float (argv[0]));
745 746 747
}

struct subs_string {
Arturo Espinosa's avatar
Arturo Espinosa committed
748 749 750 751
	gchar *str;
	guint len;
	guint mem;
};
752 753 754 755

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

Arturo Espinosa's avatar
Arturo Espinosa committed
758 759 760 761 762
	s->len = 0;
	s->mem = len;
	s->str = g_new (gchar, len);
	*s->str = '\0';
	return s;
763 764 765 766 767
}

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

770
	while (s->len + n >= s->mem)
Arturo Espinosa's avatar
Arturo Espinosa committed
771
		s->str = g_realloc (s->str, s->mem += chunk);
Morten Welinder's avatar
Morten Welinder committed
772

Arturo Espinosa's avatar
Arturo Espinosa committed
773 774 775 776
	strncpy (&s->str [s->len], src, n);

	s->len += n;
	s->str [s->len] = '\0';
777 778 779 780 781
}

static void
subs_string_free (struct subs_string *s)
{
Arturo Espinosa's avatar
Arturo Espinosa committed
782 783
	g_free (s->str);
	g_free (s);
784 785 786 787
}

static char *help_substitute = {
	N_("@FUNCTION=SUBSTITUTE\n"
788
	   "@SYNTAX=SUBSTITUTE(text, old, new [,num])\n"
789 790 791 792 793
	   "@DESCRIPTION="
	   "Replaces @old with @new in @text.  Substitutions are only "
	   "applied to instance @num of @old in @text, otherwise every "
	   "one is changed."
	   "\n"
794 795 796 797
	   "@SEEALSO=REPLACE, TRIM")
};

static Value *
798 799
gnumeric_substitute (struct FunctionDefinition *i,
		     Value *argv [], char **error_string)
800
{
Arturo Espinosa's avatar
Arturo Espinosa committed
801 802 803 804 805
	Value *v;
	gchar *text, *old, *new, *p ,*f;
	gint num;
	guint oldlen, newlen, len, inst;
	struct subs_string *s;
806

807 808 809
	text = value_get_as_string (argv[0]);
	old  = value_get_as_string (argv[1]);
	new  = value_get_as_string (argv[2]);
810

811
	if (argv[3])
Arturo Espinosa's avatar
Arturo Espinosa committed
812
		num = value_get_as_int (argv[3]);
813
	else
Arturo Espinosa's avatar
Arturo Espinosa committed
814
		num = 0;
815

Arturo Espinosa's avatar
Arturo Espinosa committed
816 817 818
	oldlen = strlen (old);
	newlen = strlen (new);
	len = strlen (text);
819
	if (newlen != oldlen) {
Arturo Espinosa's avatar
Arturo Espinosa committed
820
		s = subs_string_new (len);
Morten Welinder's avatar
Morten Welinder committed
821
	} else
Arturo Espinosa's avatar
Arturo Espinosa committed
822
		s = NULL;
823

Arturo Espinosa's avatar
Arturo Espinosa committed
824 825
	p = text;
	inst = 0;
826 827
	while (p-text < len) {
		if ( (f=strstr(p, old)) == NULL )
Arturo Espinosa's avatar
Arturo Espinosa committed
828
			break;
829 830
		if (num == 0 || num == ++inst) {
			if (s == NULL) {
Arturo Espinosa's avatar
Arturo Espinosa committed
831
				strncpy (f, new, newlen);
832
			} else {
Arturo Espinosa's avatar
Arturo Espinosa committed
833 834
				subs_string_append_n (s, p, f-p);
				subs_string_append_n (s, new, newlen);
835 836
			}
			if (num != 0 && num == inst)
Arturo Espinosa's avatar
Arturo Espinosa committed
837
				break;
838
		}
Arturo Espinosa's avatar
Arturo Espinosa committed
839
		p = f + oldlen;
840 841
	}
	if (newlen != oldlen) { /* FIXME: (p-text) might be bad ? */
Arturo Espinosa's avatar
Arturo Espinosa committed
842 843
		subs_string_append_n (s, p, len - (p-text) );
		p = s->str;
844
	} else
Arturo Espinosa's avatar
Arturo Espinosa committed
845
		p = text;
846

847
	v = value_new_string (p);
848

Arturo Espinosa's avatar
Arturo Espinosa committed
849 850 851
	g_free (new);
	g_free (old);
	g_free (text);
852
	if (s != NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
853
		subs_string_free (s);
854

Arturo Espinosa's avatar
Arturo Espinosa committed
855
	return v;
856 857 858 859
}

static char *help_dollar = {
	N_("@FUNCTION=DOLLAR\n"
860
	   "@SYNTAX=DOLLAR(num,[decimals])\n"
861 862 863
	   "@DESCRIPTION="
	   "Returns @num formatted as currency."
	   "\n"
864 865 866
	   "@SEEALSO=FIXED, TEXT, VALUE")
};

Arturo Espinosa's avatar
Arturo Espinosa committed
867
/* FIXME: should use lc->[pn]_sign_posn, mon_thousands_sep, negative_sign */
868
static Value *
869 870
gnumeric_dollar (struct FunctionDefinition *i,
		 Value *argv [], char **error_string)
871
{
Arturo Espinosa's avatar
Arturo Espinosa committed
872 873 874 875
	Value *v, *ag [3];
	guint len, neg;
	gchar *s;

876 877
	g_warning ("GNUMERIC_DOLLAR is broken, it should use the "
		   "format_value routine");
Arturo Espinosa's avatar
Arturo Espinosa committed
878 879 880
	ag[0] = argv [0];
	ag[1] = argv [1];
	ag[2] = NULL;
Morten Welinder's avatar
Morten Welinder committed
881

Arturo Espinosa's avatar
Arturo Espinosa committed
882
	v = gnumeric_fixed (i, ag, error_string);
883
	if (v == NULL)
Arturo Espinosa's avatar
Arturo Espinosa committed
884
		return NULL;
Morten Welinder's avatar
Morten Welinder committed
885

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

Arturo Espinosa's avatar
Arturo Espinosa committed
888 889 890 891 892 893 894 895 896 897
	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] = ')';
898 899
	}
	/* FIXME: should use *lc->currency_symbol */
Arturo Espinosa's avatar
Arturo Espinosa committed
900 901 902 903 904
	s[neg] = '$';
	s[len + 1 + neg] = '\0';
	v->v.str = string_get (s);
	g_free (s);
	return v;
905 906
}

907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 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 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
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);
Morten Welinder's avatar
Morten Welinder committed
1088
		*error_string = gnumeric_err_VALUE;
1089 1090 1091 1092 1093 1094 1095
		return NULL;
	}

	conditions = parse_search_string(text);
	if (conditions == NULL) {
	        g_free(text);
		g_free(within);
Morten Welinder's avatar
Morten Welinder committed
1096
		*error_string = gnumeric_err_VALUE;
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
		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);
Morten Welinder's avatar
Morten Welinder committed
1129
	*error_string = gnumeric_err_VALUE;
1130 1131 1132 1133
	return NULL;
}


1134
FunctionDefinition string_functions [] = {
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 1170 1171 1172 1173 1174 1175 1176 1177 1178
	{ "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 },
1179 1180
	{ NULL, NULL },
};
1181

1182 1183
/* Missing:
 * TEXT(number,format) formats number
1184
 */