plugins/fn-string/functions.c

   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  *  Almer S. Tigelaar (almer@gnome.org)
   9  *
  10  * This program is free software; you can redistribute it and/or modify
  11  * it under the terms of the GNU General Public License as published by
  12  * the Free Software Foundation; either version 2 of the License, or
  13  * (at your option) any later version.
  14  *
  15  * This program is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU General Public License for more details.
  19  *
  20  * You should have received a copy of the GNU General Public License
  21  * along with this program; if not, see <https://www.gnu.org/licenses/>.
  22  */
  23 #include <gnumeric-config.h>
  24 #include <gnumeric.h>
  25 #include <func.h>
  26 #include <parse-util.h>
  27 #include <cell.h>
  28 #include <gnm-format.h>
  29 #include <gutils.h>
  30 #include <sheet.h>
  31 #include <workbook.h>
  32 #include <value.h>
  33 #include <expr.h>
  34 #include <number-match.h>
  35 #include <mathfunc.h>
  36 #include <rangefunc-strings.h>
  37 #include <collect.h>
  38 #include <goffice/goffice.h>
  39 #include <gsf/gsf-utils.h>
  40 #include <gsf/gsf-msole-utils.h>
  41 #include <gnm-i18n.h>
  42 #include <gnm-plugin.h>
  43
  44 #include <limits.h>
  45 #include <string.h>
  46
  47 GNM_PLUGIN_MODULE_HEADER;
  48
  49 /***************************************************************************/
  50
  51 static GIConv CHAR_iconv;
  52
  53 static GnmFuncHelp const help_char[] = {
  54         { GNM_FUNC_HELP_NAME, F_("CHAR:the CP1252 (Windows-1252) character for the code point @{x}")},
  55         { GNM_FUNC_HELP_ARG, F_("x:code point")},
  56         { GNM_FUNC_HELP_DESCRIPTION, F_("CHAR(@{x}) returns the CP1252 (Windows-1252) character with code @{x}.")},
  57         { GNM_FUNC_HELP_DESCRIPTION, F_("@{x} must be in the range 1 to 255.")},
  58         { GNM_FUNC_HELP_DESCRIPTION, F_("CP1252 (Windows-1252) is also known as the \"ANSI code page\", "
  59                                         "but it is not an ANSI standard.")},
  60         { GNM_FUNC_HELP_DESCRIPTION, F_("CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, "
  61                                         "and contains all of its printable characters. It also contains all "
  62                                         "of ISO-8859-15's printable characters (but partially at different "
  63                                         "positions.)")},
  64         { GNM_FUNC_HELP_NOTE, F_("In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters.") },
  65         { GNM_FUNC_HELP_NOTE, F_("For @{x} from 1 to 255 except 129, 141, 143, 144, and 157 we have CODE(CHAR(@{x}))=@{x}.") },
  66         { GNM_FUNC_HELP_DESCRIPTION, F_("This function is Excel compatible.") },
  67         { GNM_FUNC_HELP_EXAMPLES, "=CHAR(65)" },
  68         { GNM_FUNC_HELP_SEEALSO, "CODE"},
  69         { GNM_FUNC_HELP_END }
  70 };
  71
  72 static GnmValue *
  73 gnumeric_char (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
  74 {
  75         gnm_float c = value_get_as_float (argv[0]);
  76
  77         if (c >= 1 && c < 128) {
  78                 char result[2];
  79                 result[0] = (char)c;
  80                 result[1] = 0;
  81                 return value_new_string (result);
  82         } else if (c >= 128 && c < 256) {
  83                 char c2 = (char)c;
  84                 char *str = g_convert_with_iconv (&c2, 1, CHAR_iconv,
  85                                                   NULL, NULL, NULL);
  86                 if (str) {
  87                         int len = g_utf8_strlen (str, -1);
  88                         if (len == 1)
  89                                 return value_new_string_nocopy (str);
  90                         g_warning ("iconv for CHAR(%d) produced a string of length %d",
  91                                    c2, len);
  92                         g_free (str);
  93                 } else
  94                         g_warning ("iconv failed for CHAR(%d)", c2);
  95         }
  96
  97         return value_new_error_VALUE (ei->pos);
  98 }
  99
 100 /***************************************************************************/
 101
 102 static GnmFuncHelp const help_unichar[] = {
 103         { GNM_FUNC_HELP_NAME, F_("UNICHAR:the Unicode character represented by the Unicode code point @{x}")},
 104         { GNM_FUNC_HELP_ARG, F_("x:Unicode code point")},
 105         { GNM_FUNC_HELP_EXAMPLES, "=UNICHAR(65)"},
 106         { GNM_FUNC_HELP_EXAMPLES, "=UNICHAR(960)"},
 107         { GNM_FUNC_HELP_EXAMPLES, "=UNICHAR(20000)"},
 108         { GNM_FUNC_HELP_SEEALSO, "CHAR,UNICODE,CODE"},
 109         { GNM_FUNC_HELP_END}
 110 };
 111
 112 static GnmValue *
 113 gnumeric_unichar (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 114 {
 115         gnm_float c = value_get_as_float (argv[0]);
 116
 117         if (c >= 0 && c <= INT_MAX && g_unichar_validate ((gunichar)c)) {
 118                 char utf8[8];
 119                 int len = g_unichar_to_utf8 ((gunichar)c, utf8);
 120                 utf8[len] = 0;
 121                 return value_new_string (utf8);
 122         } else
 123                 return value_new_error_VALUE (ei->pos);
 124 }
 125
 126 /***************************************************************************/
 127
 128 static GIConv CODE_iconv;
 129
 130 static GnmFuncHelp const help_code[] = {
 131         { GNM_FUNC_HELP_NAME, F_("CODE:the CP1252 (Windows-1252) code point for the character @{c}")},
 132         { GNM_FUNC_HELP_ARG, F_("c:character")},
 133 { GNM_FUNC_HELP_DESCRIPTION, F_("@{c} must be a valid CP1252 (Windows-1252) character.")},
 134 { GNM_FUNC_HELP_DESCRIPTION, F_("CP1252 (Windows-1252) is also known as the \"ANSI code page\", but it is not an ANSI standard.")},
 135 { GNM_FUNC_HELP_DESCRIPTION, F_("CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, and contains all of its printable characters (but partially at different positions.)")},
 136         { GNM_FUNC_HELP_NOTE, F_("In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters.") },
 137         { GNM_FUNC_HELP_NOTE, F_("For @{x} from 1 to 255 except 129, 141, 143, 144, and 157 we have CODE(CHAR(@{x}))=@{x}.") },
 138         { GNM_FUNC_HELP_DESCRIPTION, F_("This function is Excel compatible.") },
 139         { GNM_FUNC_HELP_EXAMPLES, "=CODE(\"A\")" },
 140         { GNM_FUNC_HELP_SEEALSO, "CHAR"},
 141         { GNM_FUNC_HELP_END }
 142 };
 143
 144 static GnmValue *
 145 gnumeric_code (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 146 {
 147         char const *s = value_peek_string (argv[0]);
 148         const unsigned char *us = (const unsigned char *)s;
 149         gsize written, clen;
 150         char *str;
 151         GnmValue *res;
 152
 153         if (*us == 0)
 154                 return value_new_error_VALUE (ei->pos);
 155
 156         if (*us <= 127)
 157                 return value_new_int (*us);
 158
 159         clen = g_utf8_next_char (s) - s;
 160         str = g_convert_with_iconv (s, clen, CODE_iconv,
 161                                     NULL, &written, NULL);
 162         if (written)
 163                 res = value_new_int ((unsigned char)*str);
 164         else {
 165                 g_warning ("iconv failed for CODE(U%x)", g_utf8_get_char (s));
 166                 res = value_new_error_VALUE (ei->pos);
 167         }
 168         g_free (str);
 169
 170         return res;
 171 }
 172
 173 /***************************************************************************/
 174
 175 static GnmFuncHelp const help_unicode[] = {
 176         { GNM_FUNC_HELP_NAME, F_("UNICODE:the Unicode code point for the character @{c}")},
 177         { GNM_FUNC_HELP_ARG, F_("c:character")},
 178         { GNM_FUNC_HELP_EXAMPLES, "=UNICODE(\"A\")" },
 179         { GNM_FUNC_HELP_SEEALSO, "UNICHAR,CODE,CHAR"},
 180         { GNM_FUNC_HELP_END}
 181 };
 182
 183 static GnmValue *
 184 gnumeric_unicode (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 185 {
 186         char const *s = value_peek_string (argv[0]);
 187
 188         if (*s == 0)
 189                 return value_new_error_VALUE (ei->pos);
 190         else
 191                 return value_new_int (g_utf8_get_char (s));
 192 }
 193
 194 /***************************************************************************/
 195
 196 static gboolean
 197 gnm_compare_strings (const char *cstr1, const char *cstr2)
 198 {
 199         const char *a = cstr1, *b = cstr2;
 200         char *str1, *str2;
 201         gboolean eq;
 202
 203         /* Skip leading ASCII prefixes that match.  */
 204         while (*a == *b && *a != 0 && *b != 0)
 205                 a++, b++;
 206         /*
 207          * If we've hit the end of one string, we ought to have hit the
 208          * end of the other.  Otherwise the strings are different.
 209          */
 210         if (*a == 0 || *b == 0)
 211                 return *a == *b;
 212
 213         /*
 214          * If they differ in two ASCII characters (including terminating
 215          * NULs), the strings must be distinct.
 216          */
 217         if ((guchar)*a < 128 && (guchar)*b < 128)
 218                 return FALSE;
 219
 220         /*
 221          * We are using NFD normalization, ie. Characters are decomposed by
 222          * canonical equivalence, and multiple combining characters are
 223          * arranged in a specific order. Note that ligatures remain ligatures,
 224          * formatting such as subscript-3 versus 3 are retained.
 225          *
 226          * Note that for example, the distinct Unicode strings "U+212B"
 227          * (the angstrom sign "Å") and "U+00C5" (the Swedish letter "Å")
 228          * are both expanded by NFD (or NFKD) into the sequence
 229          * "U+0041 U+030A" (Latin letter "A" and combining ring above "°")
 230          * Of course "U+0041 U+030A" is retained in form, so we need to work
 231          * with at least the last ASCII character. Performance should nearly
 232          * be identical to using all
 233          */
 234         str1 = g_utf8_normalize (cstr1, -1, G_NORMALIZE_DEFAULT);
 235         str2 = g_utf8_normalize (cstr2, -1, G_NORMALIZE_DEFAULT);
 236
 237         eq = (g_strcmp0 (str1, str2) == 0);
 238
 239         g_free (str1);
 240         g_free (str2);
 241
 242         return eq;
 243 }
 244
 245 static GnmFuncHelp const help_exact[] = {
 246         { GNM_FUNC_HELP_NAME, F_("EXACT:TRUE if @{string1} is exactly equal to @{string2}")},
 247         { GNM_FUNC_HELP_ARG, F_("string1:first string")},
 248         { GNM_FUNC_HELP_ARG, F_("string2:second string")},
 249         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 250         { GNM_FUNC_HELP_EXAMPLES, "=EXACT(\"Gnumeric\",\"Gnumeric\")" },
 251         { GNM_FUNC_HELP_EXAMPLES, "=EXACT(\"gnumeric\",\"Gnumeric\")" },
 252         { GNM_FUNC_HELP_SEEALSO, "LEN,SEARCH,DELTA"},
 253         { GNM_FUNC_HELP_END}
 254 };
 255
 256 static GnmValue *
 257 gnumeric_exact (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 258 {
 259         return value_new_bool (gnm_compare_strings (value_peek_string (argv[0]),
 260                                                     value_peek_string (argv[1])));
 261 }
 262
 263 /***************************************************************************/
 264
 265 static GnmFuncHelp const help_len[] = {
 266         { GNM_FUNC_HELP_NAME, F_("LEN:the number of characters of the string @{s}")},
 267         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 268         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 269         { GNM_FUNC_HELP_EXAMPLES, "=LEN(\"Helsinki\")" },
 270         { GNM_FUNC_HELP_EXAMPLES, "=LEN(\"L\xc3\xa9vy\")" },
 271         { GNM_FUNC_HELP_SEEALSO, "CHAR,CODE,LENB"},
 272         { GNM_FUNC_HELP_END}
 273 };
 274
 275 static GnmValue *
 276 gnumeric_len (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 277 {
 278         return value_new_int (g_utf8_strlen (value_peek_string (argv[0]), -1));
 279 }
 280
 281 /***************************************************************************/
 282 static GnmFuncHelp const help_lenb[] = {
 283         { GNM_FUNC_HELP_NAME, F_("LENB:the number of bytes in the string @{s}")},
 284         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 285         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 286         { GNM_FUNC_HELP_EXAMPLES, "=LENB(\"Helsinki\")" },
 287         { GNM_FUNC_HELP_EXAMPLES, "=LENB(\"L\xc3\xa9vy\")" },
 288         { GNM_FUNC_HELP_SEEALSO, "CHAR, CODE, LEN"},
 289         { GNM_FUNC_HELP_END}
 290 };
 291
 292 static GnmValue *
 293 gnumeric_lenb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 294 {
 295         return value_new_int (strlen (value_peek_string (argv[0])));
 296 }
 297
 298 /***************************************************************************/
 299
 300 static GnmFuncHelp const help_left[] = {
 301         { GNM_FUNC_HELP_NAME, F_("LEFT:the first @{num_chars} characters of the string @{s}")},
 302         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 303         { GNM_FUNC_HELP_ARG, F_("num_chars:the number of characters to return (defaults to 1)")},
 304         { GNM_FUNC_HELP_NOTE, F_("If the string @{s} is in a right-to-left script, the returned first characters are from the right of the string.")},
 305         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
 306         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.")},
 307         { GNM_FUNC_HELP_EXAMPLES, "=LEFT(\"L\xc3\xa9vy\",3)" },
 308         { GNM_FUNC_HELP_EXAMPLES, "=LEFT(\"L\xc3\xa9vy\",2)" },
 309         { GNM_FUNC_HELP_SEEALSO, "MID,RIGHT,LEN,MIDB,RIGHTB,LENB"},
 310         { GNM_FUNC_HELP_END }
 311 };
 312
 313 static GnmValue *
 314 gnumeric_left (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 315 {
 316         const guchar *peek = (const guchar *)value_peek_string (argv[0]);
 317         gnm_float count = argv[1] ? value_get_as_float (argv[1]) : 1.0;
 318         int icount, newlen;
 319
 320         if (count < 0)
 321                 return value_new_error_VALUE (ei->pos);
 322         icount = (int)MIN ((gnm_float)INT_MAX, count);
 323
 324         for (newlen = 0; peek[newlen] != 0 && icount > 0; icount--)
 325                 newlen += g_utf8_skip[peek[newlen]];
 326
 327         return value_new_string_nocopy (g_strndup (peek, newlen));
 328 }
 329
 330 /***************************************************************************/
 331
 332 static GnmFuncHelp const help_leftb[] = {
 333         { GNM_FUNC_HELP_NAME, F_("LEFTB:the first characters of the string @{s} comprising at most @{num_bytes} bytes")},
 334         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 335         { GNM_FUNC_HELP_ARG, F_("num_bytes:the maximum number of bytes to return (defaults to 1)")},
 336         { GNM_FUNC_HELP_NOTE, F_("The semantics of this function is subject to change as various applications implement it.")},
 337         { GNM_FUNC_HELP_NOTE, F_("If the string is in a right-to-left script, the returned first characters are from the right of the string.")},
 338         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.")},
 339         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
 340         { GNM_FUNC_HELP_EXAMPLES, "=LEFTB(\"L\xc3\xa9vy\",3)" },
 341         { GNM_FUNC_HELP_EXAMPLES, "=LEFTB(\"L\xc3\xa9vy\",2)" },
 342         { GNM_FUNC_HELP_SEEALSO, "MIDB,RIGHTB,LENB,LEFT,MID,RIGHT,LEN" },
 343         { GNM_FUNC_HELP_END }
 344 };
 345
 346 static GnmValue *
 347 gnumeric_leftb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 348 {
 349         const guchar *peek = (const guchar *)value_peek_string (argv[0]);
 350         gnm_float count = argv[1] ? value_get_as_float (argv[1]) : 1.0;
 351         int len = strlen (peek);
 352         int icount, newlen;
 353
 354         if (count < 0)
 355                 return value_new_error_VALUE (ei->pos);
 356         icount = (int)MIN ((gnm_float)INT_MAX, count);
 357         if (icount >= len)
 358                 return value_new_string (peek);
 359
 360         newlen = ((const guchar *)g_utf8_find_prev_char (peek, peek + icount + 1)) - peek;
 361
 362         return value_new_string_nocopy (g_strndup (peek, newlen));
 363 }
 364 /***************************************************************************/
 365
 366 static GnmFuncHelp const help_lower[] = {
 367         { GNM_FUNC_HELP_NAME, F_("LOWER:a lower-case version of the string @{text}")},
 368         { GNM_FUNC_HELP_ARG, F_("text:string")},
 369         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
 370         { GNM_FUNC_HELP_EXAMPLES, "=LOWER(\"J. F. Kennedy\")" },
 371         { GNM_FUNC_HELP_EXAMPLES, "=LOWER(\"L\xc3\x89VY\")" },
 372         { GNM_FUNC_HELP_SEEALSO, "UPPER"},
 373         { GNM_FUNC_HELP_END}
 374 };
 375
 376 static GnmValue *
 377 gnumeric_lower (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 378 {
 379         return value_new_string_nocopy (g_utf8_strdown (value_peek_string (argv[0]), -1));
 380 }
 381
 382 /***************************************************************************/
 383
 384 static GnmFuncHelp const help_mid[] = {
 385         { GNM_FUNC_HELP_NAME, F_("MID:the substring of the string @{s} starting at position @{position} consisting of @{length} characters")},
 386         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 387         { GNM_FUNC_HELP_ARG, F_("position:the starting position")},
 388         { GNM_FUNC_HELP_ARG, F_("length:the number of characters to return")},
 389         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
 390         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.")},
 391         { GNM_FUNC_HELP_EXAMPLES, "=MID(\"L\xc3\xa9vy\",2,1)" },
 392         { GNM_FUNC_HELP_EXAMPLES, "=MID(\"L\xc3\xa9vy\",3,2)" },
 393         { GNM_FUNC_HELP_SEEALSO, "LEFT,RIGHT,LEN,LEFTB,MIDB,RIGHTB,LENB"},
 394         { GNM_FUNC_HELP_END }
 395 };
 396
 397 static GnmValue *
 398 gnumeric_mid (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 399 {
 400         char const *source = value_peek_string (argv[0]);
 401         gnm_float pos = value_get_as_float (argv[1]);
 402         gnm_float len = value_get_as_float (argv[2]);
 403         size_t slen = g_utf8_strlen (source, -1);
 404         char const *upos;
 405         size_t ilen, ipos, ulen;
 406
 407         if (len < 0 || pos < 1)
 408                 return value_new_error_VALUE (ei->pos);
 409         if (pos >= slen + 1)
 410                 return value_new_string ("");
 411
 412         /* Make ipos zero-based.  */
 413         ipos = (size_t)(pos - 1);
 414         ilen  = (size_t)MIN (len, (gnm_float)(slen - ipos));
 415
 416         upos = g_utf8_offset_to_pointer (source, ipos);
 417         ulen = g_utf8_offset_to_pointer (upos, ilen) - upos;
 418
 419         return value_new_string_nocopy (g_strndup (upos, ulen));
 420 }
 421
 422 /***************************************************************************/
 423
 424 static GnmFuncHelp const help_midb[] = {
 425         { GNM_FUNC_HELP_NAME, F_("MIDB:the characters following the first @{start_pos} bytes comprising at most @{num_bytes} bytes")},
 426         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 427         { GNM_FUNC_HELP_ARG, F_("start_pos:the number of the byte with which to start (defaults to 1)")},
 428         { GNM_FUNC_HELP_ARG, F_("num_bytes:the maximum number of bytes to return (defaults to 1)")},
 429         { GNM_FUNC_HELP_NOTE, F_("The semantics of this function is subject to change as various applications implement it.")},
 430         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, "
 431                                   "the differences in the underlying text encoding will usually yield different results.")},
 432         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
 433         { GNM_FUNC_HELP_EXAMPLES, "=MIDB(\"L\xc3\xa9vy\",2,1)" },
 434         { GNM_FUNC_HELP_EXAMPLES, "=MIDB(\"L\xc3\xa9vy\",2,2)" },
 435         { GNM_FUNC_HELP_EXAMPLES, "=MIDB(\"L\xc3\xa9vy\",3,2)" },
 436         { GNM_FUNC_HELP_SEEALSO, "LEFTB,RIGHTB,LENB,LEFT,MID,RIGHT,LEN"},
 437         { GNM_FUNC_HELP_END }
 438 };
 439
 440 static GnmValue *
 441 gnumeric_midb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 442 {
 443         const guchar *peek = (const guchar *)value_peek_string (argv[0]);
 444         gnm_float pos = value_get_as_float (argv[1]);
 445         gnm_float len = value_get_as_float (argv[2]);
 446         int slen = strlen (peek);
 447         int ipos, ilen, newlen;
 448
 449         if ((len < 0) || (pos < 1))
 450                 return value_new_error_VALUE (ei->pos);
 451         ipos = (int)MIN ((gnm_float)INT_MAX / 2, pos) - 1;
 452         ilen = (int)MIN ((gnm_float)INT_MAX / 2, len);
 453         if ((ipos >= slen) ||
 454             ((gunichar)-1 == g_utf8_get_char_validated (peek + ipos, -1)))
 455                 return value_new_error_VALUE (ei->pos);
 456
 457         if ((ipos + ilen) > slen)
 458                 return value_new_string (peek + ipos);
 459
 460         newlen = ((const guchar *)g_utf8_find_prev_char (peek + ipos, peek + ipos + ilen + 1))
 461                 - (peek + ipos);
 462
 463         return value_new_string_nocopy (g_strndup (peek + ipos, newlen));
 464 }
 465
 466 /***************************************************************************/
 467
 468 static GnmFuncHelp const help_findb[] = {
 469         { GNM_FUNC_HELP_NAME, F_("FINDB:first byte position of @{string1} in @{string2} following byte position @{start}")},
 470         { GNM_FUNC_HELP_ARG, F_("string1:search string")},
 471         { GNM_FUNC_HELP_ARG, F_("string2:search field")},
 472         { GNM_FUNC_HELP_ARG, F_("start:starting byte position, defaults to 1")},
 473         { GNM_FUNC_HELP_NOTE, F_("This search is case-sensitive.")},
 474         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, "
 475                                   "the differences in the underlying text encoding will usually yield different results.")},
 476         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
 477         { GNM_FUNC_HELP_EXAMPLES, "=FINDB(\"v\",\"L\xc3\xa9vy\")" },
 478         { GNM_FUNC_HELP_EXAMPLES, "=FINDB(\"v\",\"L\xc3\xa9vy\",3)" },
 479         { GNM_FUNC_HELP_EXAMPLES, "=FINDB(\"v\",\"L\xc3\xa9vy\",5)" },
 480         { GNM_FUNC_HELP_SEEALSO, "FIND,LEFTB,RIGHTB,LENB,LEFT,MID,RIGHT,LEN"},
 481         { GNM_FUNC_HELP_END}
 482 };
 483
 484 static GnmValue *
 485 gnumeric_findb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 486 {
 487         char const *needle   = value_peek_string (argv[0]);
 488         char const *haystack = value_peek_string (argv[1]);
 489         gnm_float count      = argv[2] ? value_get_as_float (argv[2]) : 1.0;
 490         size_t haystacksize = strlen (haystack);
 491         size_t icount;
 492         char const *p;
 493
 494
 495         if (count < 1 || count >= haystacksize + 1)
 496                 return value_new_error_VALUE (ei->pos);
 497
 498         icount = (size_t) count;
 499         p = (icount == 1) ? haystack : g_utf8_find_next_char (haystack + (icount - 2) , NULL);
 500
 501         p = g_strstr_len (p, strlen (p), needle);
 502         if (p)
 503                 return value_new_int
 504                         ((p - haystack) + 1);
 505         else
 506                 return value_new_error_VALUE (ei->pos);
 507 }
 508
 509 /***************************************************************************/
 510
 511 static GnmFuncHelp const help_right[] = {
 512         { GNM_FUNC_HELP_NAME, F_("RIGHT:the last @{num_chars} characters of the string @{s}")},
 513         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 514         { GNM_FUNC_HELP_ARG, F_("num_chars:the number of characters to return (defaults to 1)")},
 515         { GNM_FUNC_HELP_NOTE, F_("If the string @{s} is in a right-to-left script, the returned last characters are from the left of the string.")},
 516         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
 517         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.")},
 518         { GNM_FUNC_HELP_EXAMPLES, "=RIGHT(\"L\xc3\xa9vy\",2)" },
 519         { GNM_FUNC_HELP_EXAMPLES, "=RIGHT(\"L\xc3\xa9vy\",3)" },
 520         { GNM_FUNC_HELP_SEEALSO, "LEFT,MID,LEN,LEFTB,MIDB,RIGHTB,LENB"},
 521         { GNM_FUNC_HELP_END }
 522 };
 523
 524 static GnmValue *
 525 gnumeric_right (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 526 {
 527         char const *os = value_peek_string (argv[0]);
 528         gnm_float count = argv[1] ? value_get_as_float (argv[1]) : 1.0;
 529         int icount, slen;
 530
 531         if (count < 0)
 532                 return value_new_error_VALUE (ei->pos);
 533         icount = (int)MIN ((gnm_float)INT_MAX, count);
 534
 535         slen = g_utf8_strlen (os, -1);
 536
 537         if (icount < slen)
 538                 return value_new_string (g_utf8_offset_to_pointer (os, slen - icount));
 539         else
 540                 /* We could just duplicate the arg, but that would not ensure
 541                    that the result was a string.  */
 542                 return value_new_string (os);
 543 }
 544
 545 /***************************************************************************/
 546
 547 static GnmFuncHelp const help_rightb[] = {
 548         { GNM_FUNC_HELP_NAME, F_("RIGHTB:the last characters of the string @{s} comprising at most @{num_bytes} bytes")},
 549         { GNM_FUNC_HELP_ARG, F_("s:the string")},
 550         { GNM_FUNC_HELP_ARG, F_("num_bytes:the maximum number of bytes to return (defaults to 1)")},
 551         { GNM_FUNC_HELP_NOTE, F_("The semantics of this function is subject to change as various applications implement it.")},
 552         { GNM_FUNC_HELP_NOTE, F_("If the string @{s} is in a right-to-left script, the returned last characters are from the left of the string.")},
 553         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.")},
 554         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
 555         { GNM_FUNC_HELP_EXAMPLES, "=RIGHTB(\"L\xc3\xa9vy\",2)" },
 556         { GNM_FUNC_HELP_EXAMPLES, "=RIGHTB(\"L\xc3\xa9vy\",3)" },
 557         { GNM_FUNC_HELP_SEEALSO, "LEFTB,MIDB,LENB,LEFT,MID,RIGHT,LEN"},
 558         { GNM_FUNC_HELP_END }
 559 };
 560
 561 static GnmValue *
 562 gnumeric_rightb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 563 {
 564         const guchar *peek = (const guchar *)value_peek_string (argv[0]);
 565         gnm_float count = argv[1] ? value_get_as_float (argv[1]) : 1.0;
 566         int len = strlen (peek);
 567         int icount;
 568         gchar *res;
 569
 570         if (count < 0)
 571                 return value_new_error_VALUE (ei->pos);
 572         icount = (int)MIN ((gnm_float)INT_MAX, count);
 573         if (icount >= len)
 574                 return value_new_string (peek);
 575
 576         res = g_utf8_find_next_char (peek + len - icount - 1, peek + len);
 577         return value_new_string ((res == NULL) ? "" : res);
 578 }
 579
 580 /***************************************************************************/
 581
 582 static GnmFuncHelp const help_upper[] = {
 583         { GNM_FUNC_HELP_NAME, F_("UPPER:an upper-case version of the string @{text}")},
 584         { GNM_FUNC_HELP_ARG, F_("text:string")},
 585         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 586         { GNM_FUNC_HELP_EXAMPLES, "=UPPER(\"Gnumeric\")" },
 587         { GNM_FUNC_HELP_EXAMPLES, "=UPPER(\"L\xc3\xa9vy\")" },
 588         { GNM_FUNC_HELP_SEEALSO, "LOWER"},
 589         { GNM_FUNC_HELP_END}
 590 };
 591
 592
 593 static GnmValue *
 594 gnumeric_upper (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 595 {
 596         return value_new_string_nocopy (g_utf8_strup (value_peek_string (argv[0]), -1));
 597 }
 598
 599 /***************************************************************************/
 600
 601 static GnmFuncHelp const help_concatenate[] = {
 602         { GNM_FUNC_HELP_NAME, F_("CONCATENATE:the concatenation of the strings @{s1}, @{s2},\xe2\x80\xa6")},
 603         { GNM_FUNC_HELP_ARG, F_("s1:first string")},
 604         { GNM_FUNC_HELP_ARG, F_("s2:second string")},
 605         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 606         { GNM_FUNC_HELP_EXAMPLES, "=CONCATENATE(\"aa\",\"bb\")" },
 607         { GNM_FUNC_HELP_SEEALSO, "LEFT,MID,RIGHT"},
 608         { GNM_FUNC_HELP_END}
 609 };
 610
 611
 612 static GnmValue *
 613 gnumeric_concatenate (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 614 {
 615         return string_range_function (argc, argv, ei,
 616                                       range_concatenate,
 617                                       NULL,
 618                                       COLLECT_IGNORE_BLANKS,
 619                                       GNM_ERROR_VALUE);
 620 }
 621
 622 static GnmFuncHelp const help_concat[] = {
 623         { GNM_FUNC_HELP_NAME, F_("CONCAT:the concatenation of the strings @{s1}, @{s2},\xe2\x80\xa6")},
 624         { GNM_FUNC_HELP_ARG, F_("s1:first string")},
 625         { GNM_FUNC_HELP_ARG, F_("s2:second string")},
 626         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 627         { GNM_FUNC_HELP_EXAMPLES, "=CONCAT(\"aa\",\"bb\")" },
 628         { GNM_FUNC_HELP_NOTE, F_("This function is identical to CONCATENATE") },
 629         { GNM_FUNC_HELP_SEEALSO, "LEFT,MID,RIGHT"},
 630         { GNM_FUNC_HELP_END}
 631 };
 632
 633 static GnmValue *
 634 gnumeric_concat (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 635 {
 636         return gnumeric_concatenate (ei, argc, argv);
 637 }
 638
 639 /***************************************************************************/
 640
 641 static GnmFuncHelp const help_textjoin[] = {
 642         { GNM_FUNC_HELP_NAME, F_("TEXTJOIN:the concatenation of the strings @{s1}, @{s2},\xe2\x80\xa6 delimited by @{del}")},
 643         { GNM_FUNC_HELP_ARG, F_("del:delimiter")},
 644         { GNM_FUNC_HELP_ARG, F_("blank:ignore blanks")},
 645         { GNM_FUNC_HELP_ARG, F_("s1:first string")},
 646         { GNM_FUNC_HELP_ARG, F_("s2:second string")},
 647         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 648         { GNM_FUNC_HELP_EXAMPLES, "=TEXTJOIN(\"::\",FALSE,\"aa\",\"bb\")" },
 649         { GNM_FUNC_HELP_SEEALSO, "CONCATENATE"},
 650         { GNM_FUNC_HELP_END}
 651 };
 652
 653 struct cb_textjoin {
 654         char *delim;
 655         gboolean ignore_blanks;
 656 };
 657
 658 static int
 659 range_textjoin (GPtrArray *data, char **pres, gpointer user_)
 660 {
 661         struct cb_textjoin *user = user_;
 662         GString *res = g_string_new (NULL);
 663         gboolean first = TRUE;
 664         unsigned ui;
 665
 666         for (ui = 0; ui < data->len; ui++) {
 667                 const char *s = g_ptr_array_index (data, ui);
 668
 669                 if (s[0] == 0 && user->ignore_blanks)
 670                         continue;
 671
 672                 if (first)
 673                         first = FALSE;
 674                 else
 675                         g_string_append (res, user->delim);
 676
 677                 g_string_append (res, s);
 678         }
 679
 680         *pres = g_string_free (res, FALSE);
 681         return 0;
 682 }
 683
 684 static GnmValue *
 685 gnumeric_textjoin (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 686 {
 687         GnmValue *v;
 688         gboolean err;
 689         struct cb_textjoin data;
 690
 691         data.delim = NULL;
 692
 693         if (argc < 3)
 694                 return value_new_error_VALUE (ei->pos);
 695
 696         v = gnm_expr_eval (argv[0], ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
 697         if (VALUE_IS_ERROR (v))
 698                 goto done;
 699         data.delim = value_get_as_string (v);
 700         value_release (v);
 701
 702         v = gnm_expr_eval (argv[1], ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
 703         if (VALUE_IS_ERROR (v))
 704                 goto done;
 705         data.ignore_blanks = value_get_as_bool (v, &err); // What about err?
 706         value_release (v);
 707
 708         v = string_range_function (argc - 2, argv + 2, ei,
 709                                    range_textjoin,
 710                                    &data,
 711                                    data.ignore_blanks ? COLLECT_IGNORE_BLANKS : 0,
 712                                    GNM_ERROR_VALUE);
 713
 714 done:
 715         g_free (data.delim);
 716
 717         return v;
 718 }
 719
 720 /***************************************************************************/
 721
 722 static GnmFuncHelp const help_rept[] = {
 723         { GNM_FUNC_HELP_NAME, F_("REPT:@{num} repetitions of string @{text}")},
 724         { GNM_FUNC_HELP_ARG, F_("text:string")},
 725         { GNM_FUNC_HELP_ARG, F_("num:non-negative integer")},
 726         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 727         { GNM_FUNC_HELP_EXAMPLES, "=REPT(\"x\",3)" },
 728         { GNM_FUNC_HELP_SEEALSO, "CONCATENATE"},
 729         { GNM_FUNC_HELP_END}
 730 };
 731
 732 static GnmValue *
 733 gnumeric_rept (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 734 {
 735         char const *source = value_peek_string (argv[0]);
 736         gnm_float num = value_get_as_float (argv[1]);
 737         size_t len = strlen (source);
 738         char *res;
 739         size_t i, inum;
 740
 741         if (num < 0)
 742                 return value_new_error_VALUE (ei->pos);
 743
 744         /* Fast special case.  =REPT ("",2^30) should not take long.  */
 745         if (len == 0 || num < 1)
 746                 return value_new_string ("");
 747
 748         /* Check if the length would overflow.  */
 749         if (num >= INT_MAX / len)
 750                 return value_new_error_VALUE (ei->pos);
 751
 752         inum = (size_t)num;
 753         res = g_try_malloc (len * inum + 1);
 754         if (!res)
 755                 return value_new_error_VALUE (ei->pos);
 756
 757         for (i = 0; inum-- > 0; i += len)
 758                 memcpy (res + i, source, len);
 759         res[i] = 0;
 760
 761         return value_new_string_nocopy (res);
 762 }
 763
 764 /***************************************************************************/
 765
 766 static GnmFuncHelp const help_clean[] = {
 767         { GNM_FUNC_HELP_NAME, F_("CLEAN:@{text} with any non-printable characters removed")},
 768         { GNM_FUNC_HELP_ARG, F_("text:string")},
 769         { GNM_FUNC_HELP_DESCRIPTION, F_("CLEAN removes non-printable characters from its argument leaving only regular characters and white-space.") },
 770         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 771         { GNM_FUNC_HELP_EXAMPLES, "=CLEAN(\"Gnumeric\"&char(7))" },
 772         { GNM_FUNC_HELP_END}
 773 };
 774
 775 static GnmValue *
 776 gnumeric_clean (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 777 {
 778         char const *s = value_peek_string (argv[0]);
 779         GString *res = g_string_sized_new (strlen (s));
 780
 781         while (*s) {
 782                 gunichar uc = g_utf8_get_char (s);
 783
 784                 if (g_unichar_isprint (uc))
 785                         g_string_append_unichar (res, uc);
 786
 787                 s = g_utf8_next_char (s);
 788         }
 789
 790         return value_new_string_nocopy (g_string_free (res, FALSE));
 791 }
 792
 793 /***************************************************************************/
 794
 795 static GnmFuncHelp const help_find[] = {
 796         { GNM_FUNC_HELP_NAME, F_("FIND:first position of @{string1} in @{string2} following position @{start}")},
 797         { GNM_FUNC_HELP_ARG, F_("string1:search string")},
 798         { GNM_FUNC_HELP_ARG, F_("string2:search field")},
 799         { GNM_FUNC_HELP_ARG, F_("start:starting position, defaults to 1")},
 800         { GNM_FUNC_HELP_NOTE, F_("This search is case-sensitive.")},
 801         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 802         { GNM_FUNC_HELP_EXAMPLES, "=FIND(\"num\",\"Gnumeric\")" },
 803         { GNM_FUNC_HELP_SEEALSO, "EXACT,LEN,MID,SEARCH"},
 804         { GNM_FUNC_HELP_END}
 805 };
 806
 807 static GnmValue *
 808 gnumeric_find (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 809 {
 810         char const *needle   = value_peek_string (argv[0]);
 811         char const *haystack = value_peek_string (argv[1]);
 812         gnm_float count      = argv[2] ? value_get_as_float (argv[2]) : 1.0;
 813         size_t haystacksize = g_utf8_strlen (haystack, -1);
 814         size_t icount;
 815         char const *p;
 816
 817         if (count < 1 || count >= haystacksize + 1)
 818                 return value_new_error_VALUE (ei->pos);
 819         icount = (size_t)count;
 820
 821         haystack = g_utf8_offset_to_pointer (haystack, icount - 1);
 822
 823         p = g_strstr_len (haystack, strlen (haystack), needle);
 824         if (p)
 825                 return value_new_int
 826                         (g_utf8_pointer_to_offset (haystack, p) + icount);
 827         else
 828                 return value_new_error_VALUE (ei->pos);
 829 }
 830
 831 /***************************************************************************/
 832
 833 static GnmFuncHelp const help_fixed[] = {
 834         { GNM_FUNC_HELP_NAME, F_("FIXED:formatted string representation of @{num}")},
 835         { GNM_FUNC_HELP_ARG, F_("num:number")},
 836         { GNM_FUNC_HELP_ARG, F_("decimals:number of decimals")},
 837         { GNM_FUNC_HELP_ARG, F_("no_commas:TRUE if no thousand separators should be used, "
 838                                 "defaults to FALSE")},
 839         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 840         { GNM_FUNC_HELP_EXAMPLES, "=FIXED(1234.567,2)" },
 841         { GNM_FUNC_HELP_EXAMPLES, "=FIXED(1234.567,2,TRUE)" },
 842         { GNM_FUNC_HELP_SEEALSO, "TEXT,VALUE,DOLLAR"},
 843         { GNM_FUNC_HELP_END}
 844 };
 845
 846 static GnmValue *
 847 gnumeric_fixed (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 848 {
 849         gnm_float num = value_get_as_float (argv[0]);
 850         gnm_float decimals = argv[1] ? value_get_as_float (argv[1]) : 2.0;
 851         gboolean no_commas = argv[2] ? value_get_as_checked_bool (argv[2]) : FALSE;
 852         GString *format;
 853         GOFormat *fmt;
 854         GnmValue *v;
 855         char *res;
 856         GOFormatDetails *details;
 857
 858         decimals = gnm_fake_trunc (decimals);
 859         if (decimals >= 128)
 860                 return value_new_error_VALUE (ei->pos);
 861         if (decimals < 0) {
 862                 /* no decimal point : just round and pad 0's */
 863                 gnm_float mult = gnm_pow10 (decimals);
 864                 if (mult == 0)
 865                         num = 0;  /* Underflow */
 866                 else
 867                         num = gnm_fake_round (num * mult) / mult;
 868                 decimals = 0;
 869         }
 870         v = value_new_float (num);
 871
 872         details = go_format_details_new (GO_FORMAT_NUMBER);
 873         details->num_decimals = decimals;
 874         details->thousands_sep = !no_commas;
 875         format = g_string_new (NULL);
 876         go_format_generate_str (format, details);
 877         go_format_details_free (details);
 878
 879         fmt = go_format_new_from_XL (format->str);
 880         g_string_free (format, TRUE);
 881
 882         res = format_value (fmt, v, -1,
 883                             sheet_date_conv (ei->pos->sheet));
 884
 885         go_format_unref (fmt);
 886         value_release (v);
 887
 888         return value_new_string_nocopy (res);
 889 }
 890
 891 /***************************************************************************/
 892
 893 static GnmFuncHelp const help_proper[] = {
 894         { GNM_FUNC_HELP_NAME, F_("PROPER:@{text} with initial of each word capitalised")},
 895         { GNM_FUNC_HELP_ARG, F_("text:string")},
 896         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 897         { GNM_FUNC_HELP_EXAMPLES, "=PROPER(\"j. f. kennedy\")" },
 898         { GNM_FUNC_HELP_SEEALSO, "LOWER,UPPER"},
 899         { GNM_FUNC_HELP_END}
 900 };
 901
 902 static GnmValue *
 903 gnumeric_proper (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 904 {
 905         char const *p;
 906         GString    *res    = g_string_new (NULL);
 907         gboolean   inword = FALSE;
 908
 909         p = value_peek_string (argv[0]);
 910         while (*p) {
 911                 gunichar uc = g_utf8_get_char (p);
 912
 913                 if (g_unichar_isalpha (uc)) {
 914                         if (inword) {
 915                                 g_string_append_unichar
 916                                         (res, g_unichar_tolower (uc));
 917                         } else {
 918                                 g_string_append_unichar
 919                                         (res, g_unichar_toupper (uc));
 920                                 inword = TRUE;
 921                         }
 922                 } else {
 923                         g_string_append_unichar (res, uc);
 924                         inword = FALSE;
 925                 }
 926
 927                 p = g_utf8_next_char (p);
 928         }
 929
 930         return value_new_string_nocopy (g_string_free (res, FALSE));
 931 }
 932
 933 /***************************************************************************/
 934
 935 static GnmFuncHelp const help_replace[] = {
 936         { GNM_FUNC_HELP_NAME, F_("REPLACE:string @{old} with @{num} characters "
 937                                  "starting at @{start} replaced by @{new}")},
 938         { GNM_FUNC_HELP_ARG, F_("old:original text")},
 939         { GNM_FUNC_HELP_ARG, F_("start:starting position")},
 940         { GNM_FUNC_HELP_ARG, F_("num:number of characters to be replaced")},
 941         { GNM_FUNC_HELP_ARG, F_("new:replacement string")},
 942         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
 943         { GNM_FUNC_HELP_EXAMPLES, "=REPLACE(\"Gnumeric\",2,6,\"*6*\")" },
 944         { GNM_FUNC_HELP_SEEALSO, "MID,SEARCH,SUBSTITUTE,TRIM"},
 945         { GNM_FUNC_HELP_END}
 946 };
 947
 948 static GnmValue *
 949 gnumeric_replace (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 950 {
 951         char const *old = value_peek_string (argv[0]);
 952         gnm_float start = value_get_as_float (argv[1]);
 953         gnm_float num = value_get_as_float (argv[2]);
 954         char const *new = value_peek_string (argv[3]);
 955         size_t istart, inum, oldlen, precutlen, postcutlen, newlen;
 956         char const *p, *q;
 957         char *res;
 958
 959         if (start < 1 || num < 0)
 960                 return value_new_error_VALUE (ei->pos);
 961
 962         oldlen = g_utf8_strlen (old, -1);
 963         /* Make istart zero-based.  */
 964         istart = (int)MIN ((gnm_float)oldlen, start - 1);
 965         inum = (int)MIN((gnm_float)(oldlen - istart), num);
 966
 967         /* |<----precut----><cut><---postcut--->| */
 968         /*  ^old            ^p   ^q               */
 969
 970         p = g_utf8_offset_to_pointer (old, istart);
 971         q = g_utf8_offset_to_pointer (p, inum);
 972
 973         precutlen = p - old;
 974         postcutlen = strlen (q);
 975         newlen = strlen (new);
 976
 977         res = g_malloc (precutlen + newlen + postcutlen + 1);
 978         memcpy (res, old, precutlen);
 979         memcpy (res + precutlen, new, newlen);
 980         memcpy (res + precutlen + newlen, q, postcutlen + 1);
 981         return value_new_string_nocopy (res);
 982 }
 983
 984 /***************************************************************************/
 985
 986 static GnmFuncHelp const help_replaceb[] = {
 987         { GNM_FUNC_HELP_NAME, F_("REPLACEB:string @{old} with up to @{num} bytes "
 988                                  "starting at @{start} replaced by @{new}")},
 989         { GNM_FUNC_HELP_ARG, F_("old:original text")},
 990         { GNM_FUNC_HELP_ARG, F_("start:starting byte position")},
 991         { GNM_FUNC_HELP_ARG, F_("num:number of bytes to be replaced")},
 992         { GNM_FUNC_HELP_ARG, F_("new:replacement string")},
 993         { GNM_FUNC_HELP_DESCRIPTION, F_("REPLACEB replaces the string of valid unicode characters starting "
 994                                         "at the byte @{start} and ending at @{start}+@{num}-1 with the string @{new}.")},
 995         { GNM_FUNC_HELP_NOTE, F_("The semantics of this function is subject to change as various applications implement it.")},
 996         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, "
 997                                   "the differences in the underlying text encoding will usually yield different results.")},
 998         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
 999         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",2,1,\"*\")" },
1000         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",2,2,\"*\")" },
1001         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",2,3,\"*\")" },
1002         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",2,4,\"*\")" },
1003         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",3,2,\"*\")" },
1004         { GNM_FUNC_HELP_EXAMPLES, "=REPLACEB(\"L\xc3\xa9vy\",3,3,\"*\")" },
1005         { GNM_FUNC_HELP_SEEALSO, "MID,SEARCH,SUBSTITUTE,TRIM"},
1006         { GNM_FUNC_HELP_END}
1007 };
1008
1009 static GnmValue *
1010 gnumeric_replaceb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1011 {
1012         char const *old = value_peek_string (argv[0]);
1013         gnm_float pos = value_get_as_float (argv[1]);
1014         gnm_float len = value_get_as_float (argv[2]);
1015         char const *new = value_peek_string (argv[3]);
1016         int slen = strlen (old);
1017         int ipos, ilen, newlen;
1018         char *res;
1019
1020         if ((len < 0) || (pos < 1))
1021                 return value_new_error_VALUE (ei->pos);
1022         ipos = (int)MIN ((gnm_float)INT_MAX / 2, pos) - 1;
1023         ilen = (int)MIN ((gnm_float)INT_MAX / 2, len);
1024         if ((ipos > slen) ||
1025             (ipos + ilen > slen) ||
1026             ((gunichar)-1 == g_utf8_get_char_validated (old + ipos, -1)) ||
1027             ((gunichar)-1 == g_utf8_get_char_validated (old + ipos + ilen, -1)) ||
1028             !g_utf8_validate (old + ipos, ilen, NULL))
1029                 return value_new_error_VALUE (ei->pos);
1030         newlen = strlen (new);
1031         res = g_malloc (slen - ilen + newlen + 1);
1032         memcpy (res, old, ipos);
1033         memcpy (res + ipos, new, newlen);
1034         memcpy (res + ipos + newlen, old + ipos + ilen, slen - ipos - ilen + 1);
1035         return value_new_string_nocopy (res);
1036 }
1037
1038 /***************************************************************************/
1039 /* Note: help_t is a reserved symbol.  */
1040
1041 static GnmFuncHelp const help_t_[] = {
1042         { GNM_FUNC_HELP_NAME, F_("T:@{value} if and only if @{value} is text, otherwise empty")},
1043         { GNM_FUNC_HELP_ARG, F_("value:original value")},
1044         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1045         { GNM_FUNC_HELP_EXAMPLES, "=T(\"Gnumeric\")" },
1046         { GNM_FUNC_HELP_EXAMPLES, "=T(64)"},
1047         { GNM_FUNC_HELP_SEEALSO, "CELL,N,VALUE"},
1048         { GNM_FUNC_HELP_END}
1049 };
1050
1051 /* Note: gnumeric_t is a reserved symbol.  */
1052
1053 static GnmValue *
1054 gnumeric_t_ (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1055 {
1056         if (VALUE_IS_STRING (argv[0]))
1057                 return value_dup (argv[0]);
1058         else
1059                 return value_new_empty ();
1060 }
1061
1062 /***************************************************************************/
1063
1064 static GnmFuncHelp const help_text[] = {
1065         { GNM_FUNC_HELP_NAME, F_("TEXT:@{value} as a string formatted as @{format}")},
1066         { GNM_FUNC_HELP_ARG, F_("value:value to be formatted")},
1067         { GNM_FUNC_HELP_ARG, F_("format:desired format")},
1068         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1069         { GNM_FUNC_HELP_EXAMPLES, "=TEXT(3.223,\"$0.00\")" },
1070         { GNM_FUNC_HELP_EXAMPLES, "=TEXT(date(1999,4,15),\"mmmm, dd, yy\")" },
1071         { GNM_FUNC_HELP_SEEALSO, "DOLLAR,FIXED,VALUE"},
1072         { GNM_FUNC_HELP_END}
1073 };
1074
1075 static GnmValue *
1076 gnumeric_text (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1077 {
1078         GnmValue *res, *match = NULL;
1079         GnmValue const *v  = argv[0];
1080         GODateConventions const *conv =
1081                 sheet_date_conv (ei->pos->sheet);
1082         char *lfmt;
1083
1084         /* Why do we have to do these here?  */
1085         if (VALUE_IS_STRING (v)) {
1086                 match = format_match (value_peek_string (v), NULL, conv);
1087                 if (match != NULL)
1088                         v = match;
1089         } else if (VALUE_IS_EMPTY (v))
1090                 v = value_zero;
1091
1092         lfmt = go_format_str_delocalize (value_peek_string (argv[1]));
1093         if (lfmt) {
1094                 GOFormat *fmt = go_format_new_from_XL (lfmt);
1095                 GString *str = g_string_sized_new (80);
1096                 GOFormatNumberError err;
1097
1098                 g_free (lfmt);
1099                 err = format_value_gstring (str, fmt, v, -1, conv);
1100                 if (err) {
1101                         g_string_free (str, TRUE);
1102                         res = value_new_error_VALUE (ei->pos);
1103                 } else {
1104                         res = value_new_string_nocopy (g_string_free (str, FALSE));
1105                 }
1106                 go_format_unref (fmt);
1107         } else {
1108                 res = value_new_error_VALUE (ei->pos);
1109         }
1110
1111         value_release (match);
1112
1113         return res;
1114 }
1115
1116 /***************************************************************************/
1117
1118 static GnmFuncHelp const help_trim[] = {
1119         { GNM_FUNC_HELP_NAME, F_("TRIM:@{text} with only single spaces between words")},
1120         { GNM_FUNC_HELP_ARG, F_("text:string")},
1121         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1122         { GNM_FUNC_HELP_EXAMPLES, "=TRIM(\"  a bbb  cc \")" },
1123         { GNM_FUNC_HELP_SEEALSO, "CLEAN,MID,REPLACE,SUBSTITUTE"},
1124         { GNM_FUNC_HELP_END}
1125 };
1126
1127 static GnmValue *
1128 gnumeric_trim (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1129 {
1130         char const *s;
1131         GString  *res   = g_string_new (NULL);
1132         gboolean  space = TRUE;
1133         size_t    last_len = 0;
1134
1135         s = value_peek_string (argv[0]);
1136         while (*s) {
1137                 gunichar uc = g_utf8_get_char (s);
1138
1139                 /*
1140                  * FIXME: This takes care of tabs and the likes too
1141                  * is that the desired behaviour?
1142                  */
1143                 if (g_unichar_isspace (uc)) {
1144                         if (!space) {
1145                                 last_len = res->len;
1146                                 g_string_append_unichar (res, uc);
1147                                 space = TRUE;
1148                         }
1149                 } else {
1150                         g_string_append_unichar (res, uc);
1151                         space = FALSE;
1152                 }
1153
1154                 s = g_utf8_next_char (s);
1155         }
1156
1157         if (space)
1158                 g_string_truncate (res, last_len);
1159
1160         return value_new_string_nocopy (g_string_free (res, FALSE));
1161 }
1162
1163 /***************************************************************************/
1164
1165 static GnmFuncHelp const help_value[] = {
1166         { GNM_FUNC_HELP_NAME, F_("VALUE:numeric value of @{text}")},
1167         { GNM_FUNC_HELP_ARG, F_("text:string")},
1168         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1169         { GNM_FUNC_HELP_EXAMPLES, "=VALUE(\"$1,000\")" },
1170         { GNM_FUNC_HELP_SEEALSO, "DOLLAR,FIXED,TEXT"},
1171         { GNM_FUNC_HELP_END}
1172 };
1173
1174 static GnmValue *
1175 gnumeric_value (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1176 {
1177         if (VALUE_IS_EMPTY (argv[0]) || VALUE_IS_NUMBER (argv[0]))
1178                 return value_dup (argv[0]);
1179         else {
1180                 GnmValue *v;
1181                 char const *p = value_peek_string (argv[0]);
1182
1183                 /* Skip leading spaces */
1184                 while (*p && g_unichar_isspace (g_utf8_get_char (p)))
1185                        p = g_utf8_next_char (p);
1186
1187                 v = format_match_number (p, NULL,
1188                         sheet_date_conv (ei->pos->sheet));
1189
1190                 if (v != NULL)
1191                         return v;
1192                 return value_new_error_VALUE (ei->pos);
1193         }
1194 }
1195
1196 /***************************************************************************/
1197
1198 static GnmFuncHelp const help_numbervalue[] = {
1199         { GNM_FUNC_HELP_NAME, F_("NUMBERVALUE:numeric value of @{text}")},
1200         { GNM_FUNC_HELP_ARG, F_("text:string")},
1201         { GNM_FUNC_HELP_ARG, F_("separator:decimal separator")},
1202         { GNM_FUNC_HELP_NOTE, F_("If @{text} does not look like a decimal number, "
1203                                  "NUMBERVALUE returns the value VALUE would "
1204                                  "return (ignoring the given @{separator}).")},
1205         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
1206         { GNM_FUNC_HELP_EXAMPLES, "=NUMBERVALUE(\"$1,000\",\",\")" },
1207         { GNM_FUNC_HELP_SEEALSO, "VALUE"},
1208         { GNM_FUNC_HELP_END}
1209 };
1210
1211 static GnmValue *
1212 gnumeric_numbervalue (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1213 {
1214         char const *sep = value_peek_string (argv[1]);
1215         if (strlen(sep) != 1 || (*sep != '.' && *sep != ',')) {
1216                 return value_new_error_VALUE (ei->pos);
1217         }
1218
1219         if (VALUE_IS_EMPTY (argv[0]) || VALUE_IS_NUMBER (argv[0]))
1220                 return value_dup (argv[0]);
1221         else {
1222                 GnmValue *v;
1223                 char const *p = value_peek_string (argv[0]);
1224                 GString *curr;
1225                 GString *thousand;
1226                 GString *decimal;
1227                 GOFormatFamily family = GO_FORMAT_GENERAL;
1228
1229                 decimal = g_string_new (sep);
1230                 thousand = g_string_new ((*sep == '.') ? ",":".");
1231                 curr = g_string_new ("$");
1232
1233                 /* Skip leading spaces */
1234                 while (*p && g_unichar_isspace (g_utf8_get_char (p)))
1235                        p = g_utf8_next_char (p);
1236
1237                 v = format_match_decimal_number_with_locale
1238                         (p, &family, curr, thousand, decimal);
1239
1240                 g_string_free (decimal, TRUE);
1241                 g_string_free (thousand, TRUE);
1242                 g_string_free (curr, TRUE);
1243
1244                 if (v == NULL)
1245                         v = format_match_number
1246                                 (p, NULL,
1247                                  sheet_date_conv (ei->pos->sheet));
1248
1249                 if (v != NULL)
1250                         return v;
1251                 return value_new_error_VALUE (ei->pos);
1252         }
1253 }
1254
1255 /***************************************************************************/
1256
1257 static GnmFuncHelp const help_substitute[] = {
1258         { GNM_FUNC_HELP_NAME, F_("SUBSTITUTE:@{text} with all occurrences of @{old} replaced by @{new}")},
1259         { GNM_FUNC_HELP_ARG, F_("text:original text")},
1260         { GNM_FUNC_HELP_ARG, F_("old:string to be replaced")},
1261         { GNM_FUNC_HELP_ARG, F_("new:replacement string")},
1262         { GNM_FUNC_HELP_ARG, F_("num:if @{num} is specified and a number "
1263                                 "only the @{num}th occurrence of @{old} is replaced")},
1264         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1265         { GNM_FUNC_HELP_EXAMPLES, "=SUBSTITUTE(\"United Nations Educational, Scientific and Cultural Organization\",\"ation\",\"-5-\")" },
1266         { GNM_FUNC_HELP_EXAMPLES, "=SUBSTITUTE(\"United Nations Educational, Scientific and Cultural Organization\",\"ation\",\"-5-\",2)" },
1267         { GNM_FUNC_HELP_SEEALSO, "REPLACE,TRIM"},
1268         { GNM_FUNC_HELP_END}
1269 };
1270
1271 static GnmValue *
1272 gnumeric_substitute (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1273 {
1274         /*
1275          * Careful: value_peek_string handle only two live
1276          * pointers at a time.
1277          */
1278         char *textcopy = VALUE_IS_STRING (argv[0]) ? NULL : value_get_as_string (argv[0]);
1279         char const *text = textcopy ? textcopy : value_peek_string (argv[0]);
1280         char const *old  = value_peek_string (argv[1]);
1281         char const *new  = value_peek_string (argv[2]);
1282         char const *p;
1283         int oldlen, newlen, len, inst;
1284         GString *s;
1285         int num = 0;
1286
1287         if (argv[3]) {
1288                 gnm_float fnum = value_get_as_float (argv[3]);
1289                 if (fnum <= 0) {
1290                         g_free (textcopy);
1291                         return value_new_error_VALUE (ei->pos);
1292                 }
1293                 num = (int)MIN((gnm_float)INT_MAX, fnum);
1294         }
1295
1296         oldlen = strlen (old);
1297         if (oldlen == 0)
1298                 return textcopy
1299                         ? value_new_string_nocopy (textcopy)
1300                         : value_dup (argv[0]);
1301
1302         newlen = strlen (new);
1303         len = strlen (text);
1304         s = g_string_sized_new (len);
1305
1306         p = text;
1307         inst = 0;
1308         while (p - text < len) {
1309                 char const *f = strstr (p, old);
1310                 if (!f)
1311                         break;
1312
1313                 g_string_append_len (s, p, f - p);
1314                 p = f + oldlen;
1315
1316                 inst++;
1317                 if (num == 0 || num == inst) {
1318                         g_string_append_len (s, new, newlen);
1319                         if (num == inst)
1320                                 break;
1321                 } else
1322                         g_string_append_len (s, old, oldlen);
1323         }
1324         g_string_append (s, p);
1325
1326         return value_new_string_nocopy (g_string_free (s, FALSE));
1327 }
1328
1329 /***************************************************************************/
1330
1331 static GnmFuncHelp const help_dollar[] = {
1332         { GNM_FUNC_HELP_NAME, F_("DOLLAR:@{num} formatted as currency")},
1333         { GNM_FUNC_HELP_ARG, F_("num:number")},
1334         { GNM_FUNC_HELP_ARG, F_("decimals:decimals")},
1335         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1336         { GNM_FUNC_HELP_EXAMPLES, "=DOLLAR(12345)" },
1337         { GNM_FUNC_HELP_SEEALSO, "FIXED,TEXT,VALUE"},
1338         { GNM_FUNC_HELP_END}
1339 };
1340
1341 static GnmValue *
1342 gnumeric_dollar (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1343 {
1344         GOFormat *sf;
1345         gnm_float p10;
1346         GnmValue *v;
1347         char *s;
1348         gnm_float number = value_get_as_float (argv[0]);
1349         gnm_float decimals = argv[1] ? value_get_as_float (argv[1]) : 2.0;
1350         gboolean precedes, space_sep;
1351         const GString *curr = go_locale_get_currency (&precedes, &space_sep);
1352         GString *fmt_str;
1353
1354         /* This is what Excel appears to do.  */
1355         if (decimals >= 128)
1356                 return value_new_error_VALUE (ei->pos);
1357         decimals = gnm_fake_trunc (decimals);
1358
1359         /* Since decimals can be negative, round the number.  */
1360         p10 = gnm_pow10 (decimals);
1361         if (p10 == 0)
1362                 number = 0; /* Underflow.  */
1363         else
1364                 number = gnm_fake_round (number * p10) / p10;
1365
1366         fmt_str = g_string_sized_new (150);
1367         if (precedes) {
1368                 g_string_append_c (fmt_str, '"');
1369                 go_string_append_gstring (fmt_str, curr);
1370                 g_string_append (fmt_str, space_sep ? "\" " : "\"");
1371         }
1372         g_string_append (fmt_str, "#,##0");
1373         if (decimals > 0) {
1374                 g_string_append_c (fmt_str, '.');
1375                 go_string_append_c_n (fmt_str, '0', (int)decimals);
1376         }
1377         if (!precedes) {
1378                 g_string_append (fmt_str, space_sep ? " \"" : "\"");
1379                 go_string_append_gstring (fmt_str, curr);
1380                 g_string_append_c (fmt_str, '"');
1381         }
1382
1383         /* No color and no space-for-parenthesis.  */
1384         g_string_append (fmt_str, ";(");
1385         g_string_append_len (fmt_str, fmt_str->str, fmt_str->len - 2);
1386         g_string_append_c (fmt_str, ')');
1387
1388         sf = go_format_new_from_XL (fmt_str->str);
1389
1390         v = value_new_float (number);
1391         s = format_value (sf, v, -1,
1392                           sheet_date_conv (ei->pos->sheet));
1393         value_release (v);
1394         go_format_unref (sf);
1395
1396         g_string_free (fmt_str, TRUE);
1397
1398         return value_new_string_nocopy (s);
1399 }
1400
1401 /***************************************************************************/
1402
1403 static GnmFuncHelp const help_search[] = {
1404         { GNM_FUNC_HELP_NAME, F_("SEARCH:the location of the @{search} string within "
1405                                  "@{text} after position @{start}")},
1406         { GNM_FUNC_HELP_ARG, F_("search:search string")},
1407         { GNM_FUNC_HELP_ARG, F_("text:search field")},
1408         { GNM_FUNC_HELP_ARG, F_("start:starting position, defaults to 1")},
1409         { GNM_FUNC_HELP_DESCRIPTION, F_("@{search} may contain wildcard characters (*) and "
1410                                         "question marks (?). A question mark matches any "
1411                                         "single character, and a wildcard matches any "
1412                                         "string including the empty string. To search for "
1413                                         "* or ?, precede the symbol with ~.")},
1414         { GNM_FUNC_HELP_NOTE, F_("This search is not case sensitive.") },
1415         { GNM_FUNC_HELP_NOTE, F_("If @{search} is not found, SEARCH returns #VALUE!") },
1416         { GNM_FUNC_HELP_NOTE, F_("If @{start} is less than one or it is greater than "
1417                                  "the length of @{text}, SEARCH returns #VALUE!") },
1418         { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1419         { GNM_FUNC_HELP_EXAMPLES, "=SEARCH(\"c\",\"Canc\xc3\xban\")" },
1420         { GNM_FUNC_HELP_EXAMPLES, "=SEARCH(\"c\",\"Canc\xc3\xban\",2)" },
1421         { GNM_FUNC_HELP_EXAMPLES, "=SEARCH(\"c*c\",\"Canc\xc3\xban\")" },
1422         { GNM_FUNC_HELP_EXAMPLES, "=SEARCH(\"c*c\",\"Canc\xc3\xban\",2)" },
1423         { GNM_FUNC_HELP_SEEALSO, "FIND,SEARCHB"},
1424         { GNM_FUNC_HELP_END}
1425 };
1426
1427 static GnmValue *
1428 gnumeric_search (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1429 {
1430         char const *needle = value_peek_string (argv[0]);
1431         char const *haystack = value_peek_string (argv[1]);
1432         gnm_float start = argv[2] ? value_get_as_float (argv[2]) : 1.0;
1433         int res;
1434
1435         if (start < 1 || start >= INT_MAX)
1436                 return value_new_error_VALUE (ei->pos);
1437
1438         res = gnm_excel_search_impl (needle, haystack, (int)start - 1);
1439         return res == -1
1440                 ? value_new_error_VALUE (ei->pos)
1441                 : value_new_int (1 + res);
1442 }
1443
1444 /***************************************************************************/
1445
1446 static GnmFuncHelp const help_searchb[] = {
1447         { GNM_FUNC_HELP_NAME, F_("SEARCHB:the location of the @{search} string within "
1448                                  "@{text} after byte position @{start}")},
1449         { GNM_FUNC_HELP_ARG, F_("search:search string")},
1450         { GNM_FUNC_HELP_ARG, F_("text:search field")},
1451         { GNM_FUNC_HELP_ARG, F_("start:starting byte position, defaults to 1")},
1452         { GNM_FUNC_HELP_DESCRIPTION, F_("@{search} may contain wildcard characters (*) and "
1453                                         "question marks (?). A question mark matches any "
1454                                         "single character, and a wildcard matches any "
1455                                         "string including the empty string. To search for "
1456                                         "* or ?, precede the symbol with ~.")},
1457         { GNM_FUNC_HELP_NOTE, F_("This search is not case sensitive.") },
1458         { GNM_FUNC_HELP_NOTE, F_("If @{search} is not found, SEARCHB returns #VALUE!") },
1459         { GNM_FUNC_HELP_NOTE, F_("If @{start} is less than one or it is greater than "
1460                                  "the byte length of @{text}, SEARCHB returns #VALUE!") },
1461         { GNM_FUNC_HELP_NOTE, F_("The semantics of this function is subject to change as various applications implement it.")},
1462         { GNM_FUNC_HELP_EXCEL, F_("While this function is syntactically Excel compatible, "
1463                                   "the differences in the underlying text encoding will usually yield different results.")},
1464         { GNM_FUNC_HELP_ODF, F_("While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.")},
1465         { GNM_FUNC_HELP_EXAMPLES, "=SEARCHB(\"n\",\"Canc\xc3\xban\")" },
1466         { GNM_FUNC_HELP_EXAMPLES, "=SEARCHB(\"n\",\"Canc\xc3\xban\",4)" },
1467         { GNM_FUNC_HELP_EXAMPLES, "=SEARCHB(\"n\",\"Canc\xc3\xban\",6)" },
1468         { GNM_FUNC_HELP_EXAMPLES, "=SEARCHB(\"n*n\",\"Canc\xc3\xban\")" },
1469         { GNM_FUNC_HELP_EXAMPLES, "=SEARCHB(\"n*n\",\"Canc\xc3\xban\",4)" },
1470         { GNM_FUNC_HELP_SEEALSO, "FINDB,SEARCH"},
1471         { GNM_FUNC_HELP_END}
1472 };
1473
1474 static GnmValue *
1475 gnumeric_searchb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1476 {
1477         char const *needle = value_peek_string (argv[0]);
1478         char const *haystack = value_peek_string (argv[1]);
1479         gnm_float start = argv[2] ? value_get_as_float (argv[2]) : 1.0;
1480         size_t istart;
1481         GORegexp r;
1482
1483         if (start < 1 || start >= INT_MAX || start > strlen (haystack))
1484                 return value_new_error_VALUE (ei->pos);
1485         /* Make istart zero-based.  */
1486         istart = (int)(start - 1);
1487
1488         if (istart > 0)
1489                 istart = g_utf8_next_char(haystack + istart - 1) - haystack;
1490
1491         if (gnm_regcomp_XL (&r, needle, GO_REG_ICASE, FALSE, FALSE) == GO_REG_OK) {
1492                 GORegmatch rm;
1493
1494                 switch (go_regexec (&r, haystack + istart, 1, &rm, 0)) {
1495                 case GO_REG_NOMATCH:
1496                         break;
1497                 case GO_REG_OK:
1498                         go_regfree (&r);
1499                         return value_new_int
1500                                 (1 + istart + rm.rm_so);
1501                 default:
1502                         g_warning ("Unexpected go_regexec result");
1503                 }
1504                 go_regfree (&r);
1505         } else {
1506                 g_warning ("Unexpected regcomp result");
1507         }
1508
1509         return value_new_error_VALUE (ei->pos);
1510 }
1511
1512 /***************************************************************************/
1513
1514 static GnmFuncHelp const help_asc[] = {
1515         { GNM_FUNC_HELP_NAME, F_("ASC:text with full-width katakana and ASCII characters converted to half-width")},
1516         { GNM_FUNC_HELP_ARG, F_("text:string")},
1517         { GNM_FUNC_HELP_DESCRIPTION, F_("ASC converts full-width katakana and ASCII characters to half-width equivalent characters, copying all others.")},
1518         { GNM_FUNC_HELP_DESCRIPTION, F_("The distinction between half-width and full-width characters is described in http://www.unicode.org/reports/tr11/.")},
1519         { GNM_FUNC_HELP_EXCEL, F_("For most strings, this function has the same effect as in Excel.")},
1520         { GNM_FUNC_HELP_NOTE, F_("While in obsolete encodings ASC used to translate between 2-byte and 1-byte characters, this is not the case in UTF-8.")},
1521         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.")},
1522         { GNM_FUNC_HELP_EXAMPLES, "=ASC(\"\xef\xbc\xa1\xef\xbc\xa2\xef\xbc\xa3\")"},
1523         { GNM_FUNC_HELP_SEEALSO, "JIS"},
1524         { GNM_FUNC_HELP_END }
1525 };
1526
1527 static gunichar
1528 gnm_asc_half (gunichar c, GString *str)
1529 {
1530         if (c < 0x2015)
1531                 return c;
1532         if (c == 0x2015)
1533                 return (0xff70);
1534         if (c == 0x2018)
1535                 return (0x0060);
1536         if (c == 0x2019)
1537                 return (0x0027);
1538         if (c == 0x201d)
1539                 return (0x0022);
1540         if (c < 0x3001)
1541                 return c;
1542         if (c == 0x3001)
1543                 return (0xff64);
1544         if (c == 0x3002)
1545                 return (0xff61);
1546         if (c == 0x300c)
1547                 return (0xff62);
1548         if (c == 0x300d)
1549                 return (0xff63);
1550         if (c == 0x309b)
1551                 return (0xff9e);
1552         if (c == 0x309c)
1553                 return (0xff9f);
1554         if (c < 0x30a1)
1555                 return c;
1556         if (0x30a1 <= c && c <= 0x30aa)
1557                 return (c%2 == 0) ? ((c - 0x30a2)/2 + 0xff71)
1558                         : ((c - 0x30a1)/2 + 0xff67);
1559         if (c <= 0x30c2) {
1560                 if (c%2 == 1)
1561                         return ((c - 0x30ab)/2 + 0xff76);
1562                 else {
1563                         g_string_append_unichar
1564                                 (str, (c - 0x30ac)/2 + 0xff76);
1565                         return 0xff9e;
1566                 }
1567         }
1568         if (c == 0x30c3)
1569                 return (0xff6f);
1570         if (c <= 0x30c9) {
1571                 if (c%2 == 0)
1572                         return ((c - 0x30c4)/2 + 0xff82);
1573                 else {
1574                         g_string_append_unichar
1575                                 (str, (c - 0x30c5)/2 + 0xff82);
1576                         return 0xff9e;
1577                 }
1578         }
1579         if (c <= 0x30ce)
1580                 return (c - 0x30ca + 0xff85);
1581         if (c <= 0x30dd)
1582                 switch (c%2) {
1583                 case 0:
1584                         return ((c - 0x30cf)/3 + 0xff8a);
1585                 case 1:
1586                         g_string_append_unichar
1587                                 (str, (c - 0x30d0)/3 + 0xff8a);
1588                         return 0xff9e;
1589                 case 2:
1590                 default:
1591                         g_string_append_unichar
1592                                 (str, (c - 0x30d1)/3 + 0xff8a);
1593                         return 0xff9f;
1594                 }
1595         if (c <= 30e2)
1596                 return (c - 0x30de + 0xff8f);
1597         if (c <= 0x30e8) {
1598                 if (c%2 == 0)
1599                         return ((c - 0x30e4)/2 + 0xff94);
1600                 else
1601                         return ((c - 0x30e3)/2 + 0xff6c);
1602         }
1603         if (c <= 0x30ed)
1604                 return (c - 0x30e9 + 0xff97);
1605         if (c == 0x30ef)
1606                 return (0xff9c);
1607         if (c == 0x30f2)
1608                 return (0xff66);
1609         if (c == 0x30f3)
1610                 return (0xff9d);
1611         if (c == 0x30fb)
1612                 return (0xff65);
1613         if (c == 0x30fc)
1614                 return (0xff70);
1615         if (c < 0xff01)
1616                 return c;
1617         if (c <= 0xff5e)
1618                 return (c - 0xff01 + 0x0021);
1619         if (c == 0xffe5)
1620                 return (0x005c);
1621         return c;
1622 }
1623
1624 static GnmValue *
1625 gnumeric_asc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1626 {
1627         char const *peek = value_peek_string (argv[0]);
1628         GString *str = g_string_new (NULL);
1629
1630         while ((*peek) != '\0') {
1631                 gunichar wc = gnm_asc_half (g_utf8_get_char (peek), str);
1632                 g_string_append_unichar (str, wc);
1633                 peek = g_utf8_next_char(peek);
1634         }
1635
1636         return value_new_string_nocopy (g_string_free (str, FALSE));
1637 }
1638
1639 /***************************************************************************/
1640
1641 static GnmFuncHelp const help_jis[] = {
1642         { GNM_FUNC_HELP_NAME, F_("JIS:text with half-width katakana and ASCII characters converted to full-width")},
1643         { GNM_FUNC_HELP_ARG, F_("text:original text")},
1644         { GNM_FUNC_HELP_DESCRIPTION, F_("JIS converts half-width katakana and ASCII characters "
1645                                         "to full-width equivalent characters, copying all others.")},
1646         { GNM_FUNC_HELP_DESCRIPTION, F_("The distinction between half-width and full-width characters "
1647                                         "is described in http://www.unicode.org/reports/tr11/.")},
1648         { GNM_FUNC_HELP_EXCEL, F_("For most strings, this function has the same effect as in Excel.")},
1649         { GNM_FUNC_HELP_NOTE, F_("While in obsolete encodings JIS used to translate between 1-byte "
1650                                  "and 2-byte characters, this is not the case in UTF-8.")},
1651         { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.")},
1652         { GNM_FUNC_HELP_EXAMPLES, "=JIS(\"ABC\")"},
1653         { GNM_FUNC_HELP_SEEALSO, "ASC"},
1654         { GNM_FUNC_HELP_END }
1655 };
1656
1657 static gunichar
1658 gnm_asc_full (gunichar c, gunichar fc)
1659 {
1660         if (c < 0x0021)
1661                 return c;
1662         if (c == 0x0022)
1663                 return (0x201d);
1664         if (c == 0x0027)
1665                 return (0x2019);
1666         if (c == 0x005c)
1667                 return (0xffe5);
1668         if (c == 0x0060)
1669                 return (0x2018);
1670         if (c <= 0x007e)
1671                 return (c - 0x0021 + 0xff01);
1672         if (c < 0xff61)
1673                 return c;
1674         if (c == 0xff61)
1675                 return (0x3002);
1676         if (c == 0xff62)
1677                 return (0x300c);
1678         if (c == 0xff63)
1679                 return (0x300d);
1680         if (c == 0xff64)
1681                 return (0x3001);
1682         if (c == 0xff65)
1683                 return (0x30fb);
1684         if (c == 0xff66)
1685                 return (0x30f2);
1686         if (c <= 0xff6b)
1687                 return ((c - 0xff67)*2 + 0x30a1);
1688         if (c <= 0xff6e)
1689                 return ((c - 0xff6c)*2 + 0x30e3);
1690         if (c == 0xff6f)
1691                 return (0x30c3);
1692         if (c == 0xff70)
1693                 return (0x30fc);
1694         if (c <= 0xff75)
1695                 return ((c - 0xff71)*2 + 0x30a2);
1696         if (c <= 0xff81) {
1697                 if (fc == 0xff9e)
1698                         return ((c - 0xff76)*2 + 0x30ac);
1699                 else
1700                         return ((c - 0xff76)*2 + 0x30ab);
1701         }
1702         if (c <= 0xff84) {
1703                 if (fc == 0xff9e)
1704                         return ((c - 0xff82)*2 + 0x30c5);
1705                 else
1706                         return ((c - 0xff82)*2 + 0x30c4);
1707         }
1708         if (c <= 0xff89)
1709                 return ((c - 0xff85)*2 + 0x30ca);
1710         if (c <= 0xff8e) {
1711                 if (fc == 0xff9e)
1712                         return ((c - 0xff8a)*3 + 0x30d0);
1713                 else if (fc == 0xff9f)
1714                         return ((c - 0xff8a)*3 + 0x30d1);
1715                 else
1716                         return ((c - 0xff8a)*3 + 0x30cf);
1717         }
1718         if (c <= 0xff93)
1719                 return (c - 0xff8f + 0x30de);
1720         if (c <= 0xff96)
1721                 return ((c - 0xff94)*2 + 0x30e4);
1722         if (c <= 0xff9b)
1723                 return (c - 0xff97 + 0x30e9);
1724         if (c == 0xff9c)
1725                 return (0x30ef);
1726         if (c == 0xff9d)
1727                 return (0x30f3);
1728         if (c == 0xff9e)
1729                 return (0x309b);
1730         if (c == 0xff9f)
1731                 return (0x309c);
1732         return c;
1733 }
1734
1735 static GnmValue *
1736 gnumeric_jis (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1737 {
1738         char const *peek = value_peek_string (argv[0]);
1739         GString *str = g_string_new (NULL);
1740         gunichar tc = g_utf8_get_char (peek);
1741
1742         while ((*peek) != '\0') {
1743                 gunichar fc;
1744                 char const *next = g_utf8_next_char(peek);
1745                 fc = g_utf8_get_char (next);
1746                 g_string_append_unichar (str, gnm_asc_full (tc, fc));
1747                 peek = next;
1748                 tc = fc;
1749         }
1750
1751         return value_new_string_nocopy (g_string_free (str, FALSE));
1752 }
1753 /***************************************************************************/
1754 GnmFuncDescriptor const string_functions[] = {
1755         { "asc",       "s",                       help_asc,
1756           gnumeric_asc, NULL,
1757           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1758         { "char",       "f",                       help_char,
1759           gnumeric_char, NULL,
1760           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1761         { "unichar",    "f",                       help_unichar,
1762           gnumeric_unichar, NULL,
1763           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_BASIC },
1764         { "clean",      "S",                         help_clean,
1765           gnumeric_clean, NULL,
1766           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1767         { "code",       "S",                         help_code,
1768           gnumeric_code, NULL,
1769           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1770         { "unicode",    "S",                         help_unicode,
1771           gnumeric_unicode, NULL,
1772           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_BASIC },
1773         { "concat", NULL,               help_concat,
1774           NULL, gnumeric_concat,
1775           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1776         { "concatenate", NULL,               help_concatenate,
1777           NULL, gnumeric_concatenate,
1778           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1779         { "dollar",     "f|f",               help_dollar,
1780           gnumeric_dollar, NULL,
1781           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1782         { "exact",      "SS",                 help_exact,
1783           gnumeric_exact, NULL,
1784           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1785         { "find",       "SS|f",           help_find,
1786           gnumeric_find, NULL,
1787           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1788         { "findb",       "SS|f",           help_findb,
1789           gnumeric_findb, NULL,
1790           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1791         { "fixed",      "f|fb",        help_fixed,
1792           gnumeric_fixed, NULL,
1793           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1794         { "jis",       "s",                       help_jis,
1795           gnumeric_jis, NULL,
1796           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1797         { "left",       "S|f",             help_left,
1798           gnumeric_left, NULL,
1799           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1800         { "leftb",       "S|f",             help_leftb,
1801           gnumeric_leftb, NULL,
1802           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1803         { "len",        "S",                         help_len,
1804           gnumeric_len, NULL,
1805           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1806         { "lenb",       "S",                         help_lenb,
1807           gnumeric_lenb, NULL,
1808           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1809         { "lower",      "S",                         help_lower,
1810           gnumeric_lower, NULL,
1811           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1812         { "mid",        "Sff",               help_mid,
1813           gnumeric_mid, NULL,
1814           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1815         { "midb",        "Sff",               help_midb,
1816           gnumeric_midb, NULL,
1817           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1818         { "numbervalue",      "SS",          help_numbervalue,
1819           gnumeric_numbervalue, NULL,
1820           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_BASIC },
1821         { "proper",     "S",                         help_proper,
1822           gnumeric_proper, NULL,
1823           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1824         { "replace",    "SffS",         help_replace,
1825           gnumeric_replace, NULL,
1826           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1827         { "replaceb",    "SffS",         help_replaceb,
1828           gnumeric_replaceb, NULL,
1829           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1830         { "rept",       "Sf",                    help_rept,
1831           gnumeric_rept, NULL,
1832           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1833         { "right",      "S|f",             help_right,
1834           gnumeric_right, NULL,
1835           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1836         { "rightb",       "S|f",             help_rightb,
1837           gnumeric_rightb, NULL,
1838           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1839         { "search",     "SS|f",     help_search,
1840           gnumeric_search, NULL,
1841           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1842         { "searchb",     "SS|f",     help_searchb,
1843           gnumeric_searchb, NULL,
1844           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1845         { "substitute", "SSS|f",        help_substitute,
1846           gnumeric_substitute, NULL,
1847           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1848         { "t",          "S",                        help_t_,
1849           gnumeric_t_, NULL,
1850           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1851         { "text",       "Ss",           help_text,
1852           gnumeric_text, NULL,
1853           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1854         { "textjoin", NULL, help_textjoin,
1855           NULL, gnumeric_textjoin,
1856           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
1857         { "trim",       "S",                         help_trim,
1858           gnumeric_trim, NULL,
1859           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1860         { "upper",      "S",                         help_upper,
1861           gnumeric_upper, NULL,
1862           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1863         { "value",      "S",                         help_value,
1864           gnumeric_value, NULL,
1865           GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
1866
1867         {NULL}
1868 };
1869
1870
1871 G_MODULE_EXPORT void
1872 go_plugin_init (GOPlugin *plugin, GOCmdContext *cc)
1873 {
1874         int codepage = gsf_msole_iconv_win_codepage ();
1875         CHAR_iconv = gsf_msole_iconv_open_for_import (codepage);
1876         CODE_iconv = gsf_msole_iconv_open_for_export ();
1877 }
1878
1879 G_MODULE_EXPORT void
1880 go_plugin_shutdown (GOPlugin *plugin, GOCmdContext *cc)
1881 {
1882         gsf_iconv_close (CHAR_iconv);
1883         gsf_iconv_close (CODE_iconv);
1884 }