001/* 002// This software is subject to the terms of the Eclipse Public License v1.0 003// Agreement, available at the following URL: 004// http://www.eclipse.org/legal/epl-v10.html. 005// You must accept the terms of that agreement to use this software. 006// 007// Copyright (C) 2007-2011 Pentaho 008// All Rights Reserved. 009*/ 010package mondrian.olap.fun.vba; 011 012import mondrian.olap.InvalidArgumentException; 013import mondrian.olap.Util; 014 015import java.text.*; 016import java.util.*; 017import java.util.regex.Matcher; 018import java.util.regex.Pattern; 019 020import static mondrian.olap.fun.JavaFunDef.*; 021 022/** 023 * Implementations of functions in the Visual Basic for Applications (VBA) 024 * specification. 025 * 026 * <p>The functions are defined in 027 * <a href="http://msdn.microsoft.com/en-us/library/32s6akha(VS.80).aspx">MSDN 028 * </a>. 029 * 030 * @author jhyde 031 * @since Dec 31, 2007 032 */ 033public class Vba { 034 private static final long MILLIS_IN_A_DAY = 24 * 60 * 60 * 1000; 035 036 private static final DateFormatSymbols DATE_FORMAT_SYMBOLS = 037 new DateFormatSymbols(Locale.getDefault()); 038 039 // Conversion 040 041 @FunctionName("CBool") 042 @Signature("CBool(expression)") 043 @Description( 044 "Returns an expression that has been converted to a Variant of subtype " 045 + "Boolean.") 046 public static boolean cBool(Object expression) { 047 if (expression instanceof Boolean) { 048 return (Boolean) expression; 049 } else { 050 int i = cInt(expression); 051 return i != 0; 052 } 053 } 054 055 // Conversion functions 056 057 @FunctionName("CByte") 058 @Signature("CByte(expression)") 059 @Description( 060 "Returns an expression that has been converted to a Variant of subtype " 061 + "Byte.") 062 public static byte cByte(Object expression) { 063 if (expression instanceof Byte) { 064 return (Byte) expression; 065 } else { 066 int i = cInt(expression); 067 return (byte) i; 068 } 069 } 070 071 // public Currency cCur(Object expression) 072 073 @FunctionName("CDate") 074 @Signature("CDate(date)") 075 @Description( 076 "Returns an expression that has been converted to a Variant of subtype " 077 + "Date.") 078 public static Date cDate(Object expression) { 079 String str = String.valueOf(expression); 080 if (expression instanceof Date) { 081 return (Date) expression; 082 } else if (expression == null) { 083 return null; 084 } else { 085 // note that this currently only supports a limited set of dates and 086 // times 087 // "October 19, 1962" 088 // "4:35:47 PM" 089 try { 090 return DateFormat.getTimeInstance().parse(str); 091 } catch (ParseException ex0) { 092 try { 093 return DateFormat.getDateTimeInstance().parse(str); 094 } catch (ParseException ex1) { 095 try { 096 return DateFormat.getDateInstance().parse(str); 097 } catch (ParseException ex2) { 098 throw new InvalidArgumentException( 099 "Invalid parameter. " 100 + "expression parameter of CDate function must be " 101 + "formatted correctly (" 102 + String.valueOf(expression) + ")"); 103 } 104 } 105 } 106 } 107 } 108 109 @FunctionName("CDbl") 110 @Signature("CDbl(expression)") 111 @Description( 112 "Returns an expression that has been converted to a Variant of subtype " 113 + "Double.") 114 public static double cDbl(Object expression) { 115 if (expression instanceof Number) { 116 Number number = (Number) expression; 117 return number.doubleValue(); 118 } else { 119 final String s = String.valueOf(expression); 120 return new Double(s).intValue(); 121 } 122 } 123 124 @FunctionName("CInt") 125 @Signature("CInt(expression)") 126 @Description( 127 "Returns an expression that has been converted to a Variant of subtype " 128 + "Integer.") 129 public static int cInt(Object expression) { 130 if (expression instanceof Number) { 131 Number number = (Number) expression; 132 final int intValue = number.intValue(); 133 if (number instanceof Float || number instanceof Double) { 134 final double doubleValue = number.doubleValue(); 135 if (doubleValue == (double) intValue) { 136 // Number is already an integer 137 return intValue; 138 } 139 final double doubleDouble = doubleValue * 2d; 140 if (doubleDouble == Math.floor(doubleDouble)) { 141 // Number ends in .5 - round towards even required 142 return (int) Math.round(doubleValue / 2d) * 2; 143 } 144 return (int) Math.round(doubleValue); 145 } 146 return intValue; 147 } else { 148 // Try to parse as integer before parsing as double. More 149 // efficient, and avoids loss of precision. 150 final String s = String.valueOf(expression); 151 try { 152 return Integer.parseInt(s); 153 } catch (NumberFormatException e) { 154 return new Double(s).intValue(); 155 } 156 } 157 } 158 159 // public int cLng(Object expression) 160 // public float cSng(Object expression) 161 // public String cStr(Object expression) 162 // public Object cVDate(Object expression) 163 // public Object cVErr(Object expression) 164 // public Object cVar(Object expression) 165 // public String error$(Object errorNumber) 166 // public Object error(Object errorNumber) 167 168 @FunctionName("Fix") 169 @Signature("Fix(number)") 170 @Description( 171 "Returns the integer portion of a number. If negative, returns the " 172 + "negative number greater than or equal to the number.") 173 public static int fix(Object number) { 174 if (number instanceof Number) { 175 int v = ((Number) number).intValue(); 176 double dv = ((Number) number).doubleValue(); 177 if (v < 0 && v < dv) { 178 v++; 179 } 180 return v; 181 } else { 182 throw new InvalidArgumentException( 183 "Invalid parameter. " 184 + "number parameter " + number 185 + " of Int function must be " + "of type number"); 186 } 187 } 188 189 @FunctionName("Hex") 190 @Signature("Hex(number)") 191 @Description( 192 "Returns a String representing the hexadecimal value of a number.") 193 public static String hex(Object number) { 194 if (number instanceof Number) { 195 return Integer.toHexString(((Number) number).intValue()) 196 .toUpperCase(); 197 } else { 198 throw new InvalidArgumentException( 199 "Invalid parameter. " 200 + "number parameter " + number 201 + " of Hex function must be " + "of type number"); 202 } 203 } 204 205 @FunctionName("Int") 206 @Signature("Int(number)") 207 @Description( 208 "Returns the integer portion of a number. If negative, returns the " 209 + "negative number less than or equal to the number.") 210 public static int int_(Object number) { 211 if (number instanceof Number) { 212 int v = ((Number) number).intValue(); 213 double dv = ((Number) number).doubleValue(); 214 if (v < 0 && v > dv) { 215 v--; 216 } 217 return v; 218 } else { 219 throw new InvalidArgumentException( 220 "Invalid parameter. " 221 + "number parameter " + number 222 + " of Int function must be " + "of type number"); 223 } 224 } 225 226 /** 227 * Equivalent of the {@link #int_} function on the native 'double' type. 228 * Not an MDX function. 229 * 230 * @param dv Double value 231 * @return Value rounded towards negative infinity 232 */ 233 static int intNative(double dv) { 234 int v = (int) dv; 235 if (v < 0 && v > dv) { 236 v--; 237 } 238 return v; 239 } 240 241 // public String oct$(Object number) 242 243 @FunctionName("Oct") 244 @Signature("Oct(number)") 245 @Description( 246 "Returns a Variant (String) representing the octal value of a number.") 247 public static String oct(Object number) { 248 if (number instanceof Number) { 249 return Integer.toOctalString(((Number) number).intValue()); 250 } else { 251 throw new InvalidArgumentException( 252 "Invalid parameter. " 253 + "number parameter " + number 254 + " of Oct function must be " + "of type number"); 255 } 256 } 257 258 // public String str$(Object number) 259 260 @FunctionName("Str") 261 @Signature("Str(number)") 262 @Description("Returns a Variant (String) representation of a number.") 263 public static String str(Object number) { 264 // When numbers are converted to strings, a leading space is always 265 // reserved for the sign of number. If number is positive, the returned 266 // string contains a leading space and the plus sign is implied. 267 // 268 // Use the Format function to convert numeric values you want formatted 269 // as dates, times, or currency or in other user-defined formats. 270 // Unlike Str, the Format function doesn't include a leading space for 271 // the sign of number. 272 // 273 // Note The Str function recognizes only the period (.) as a valid 274 // decimal separator. When different decimal separators may be used 275 // (for example, in international applications), use CStr to convert a 276 // number to a string. 277 if (number instanceof Number) { 278 if (((Number) number).doubleValue() >= 0) { 279 return " " + number.toString(); 280 } else { 281 return number.toString(); 282 } 283 } else { 284 throw new InvalidArgumentException( 285 "Invalid parameter. " 286 + "number parameter " + number 287 + " of Str function must be " + "of type number"); 288 } 289 } 290 291 @FunctionName("Val") 292 @Signature("Val(string)") 293 @Description( 294 "Returns the numbers contained in a string as a numeric value of " 295 + "appropriate type.") 296 public static double val(String string) { 297 // The Val function stops reading the string at the first character it 298 // can't recognize as part of a number. Symbols and characters that are 299 // often considered parts of numeric values, such as dollar signs and 300 // commas, are not recognized. However, the function recognizes the 301 // radix prefixes &O (for octal) and &H (for hexadecimal). Blanks, 302 // tabs, and linefeed characters are stripped from the argument. 303 // 304 // The following returns the value 1615198: 305 // 306 // Val(" 1615 198th Street N.E.") 307 // In the code below, Val returns the decimal value -1 for the 308 // hexadecimal value shown: 309 // 310 // Val("&HFFFF") 311 // Note The Val function recognizes only the period (.) as a valid 312 // decimal separator. When different decimal separators are used, as in 313 // international applications, use CDbl instead to convert a string to 314 // a number. 315 316 string = string.replaceAll("\\s", ""); // remove all whitespace 317 if (string.startsWith("&H")) { 318 string = string.substring(2); 319 Pattern p = Pattern.compile("[0-9a-fA-F]*"); 320 Matcher m = p.matcher(string); 321 m.find(); 322 return Integer.parseInt(m.group(), 16); 323 } else if (string.startsWith("&O")) { 324 string = string.substring(2); 325 Pattern p = Pattern.compile("[0-7]*"); 326 Matcher m = p.matcher(string); 327 m.find(); 328 return Integer.parseInt(m.group(), 8); 329 } else { 330 // find the first number 331 Pattern p = Pattern.compile("-?[0-9]*[.]?[0-9]*"); 332 Matcher m = p.matcher(string); 333 m.find(); 334 return Double.parseDouble(m.group()); 335 } 336 } 337 338 // DateTime 339 340 // public Calendar calendar() 341 // public void calendar(Calendar val) 342 // public String date$() 343 // public void date$(String val) 344 345 @FunctionName("DateAdd") 346 @Signature("DateAdd(interval, number, date)") 347 @Description( 348 "Returns a Variant (Date) containing a date to which a specified time " 349 + "interval has been added.") 350 public static Date dateAdd(String intervalName, double number, Date date) { 351 Interval interval = Interval.valueOf(intervalName); 352 final double floor = Math.floor(number); 353 354 // We use the local calendar here. This method will therefore return 355 // different results in different locales: it depends whether the 356 // initial date and the final date are in DST. 357 Calendar calendar = Calendar.getInstance(); 358 calendar.setTime(date); 359 if (floor != number) { 360 final double ceil = Math.ceil(number); 361 interval.add(calendar, (int) ceil); 362 final long ceilMillis = calendar.getTimeInMillis(); 363 364 calendar.setTime(date); 365 interval.add(calendar, (int) floor); 366 final long floorMillis = calendar.getTimeInMillis(); 367 368 final long amount = 369 (long) 370 (((double) (ceilMillis - floorMillis)) * (number - floor)); 371 calendar.add( 372 Calendar.DAY_OF_YEAR, 373 (int) (amount / MILLIS_IN_A_DAY)); 374 calendar.add( 375 Calendar.MILLISECOND, (int) 376 (amount % MILLIS_IN_A_DAY)); 377 } else { 378 interval.add(calendar, (int) floor); 379 } 380 return calendar.getTime(); 381 } 382 383 @FunctionName("DateDiff") 384 @Signature( 385 "DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])") 386 @Description( 387 "Returns a Variant (Long) specifying the number of time intervals " 388 + "between two specified dates.") 389 public static long dateDiff(String interval, Date date1, Date date2) { 390 return _dateDiff( 391 interval, date1, date2, Calendar.SUNDAY, 392 FirstWeekOfYear.vbFirstJan1); 393 } 394 395 @FunctionName("DateDiff") 396 @Signature( 397 "DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])") 398 @Description( 399 "Returns a Variant (Long) specifying the number of time intervals " 400 + "between two specified dates.") 401 public static long dateDiff( 402 String interval, Date date1, Date date2, int firstDayOfWeek) 403 { 404 return _dateDiff( 405 interval, date1, date2, firstDayOfWeek, 406 FirstWeekOfYear.vbFirstJan1); 407 } 408 409 @FunctionName("DateDiff") 410 @Signature( 411 "DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])") 412 @Description( 413 "Returns a Variant (Long) specifying the number of time intervals " 414 + "between two specified dates.") 415 public static long dateDiff( 416 String interval, Date date1, Date date2, 417 int firstDayOfWeek, int firstWeekOfYear) 418 { 419 return _dateDiff( 420 interval, date1, date2, firstDayOfWeek, 421 FirstWeekOfYear.values()[firstWeekOfYear]); 422 } 423 424 private static long _dateDiff( 425 String intervalName, Date date1, Date date2, 426 int firstDayOfWeek, FirstWeekOfYear firstWeekOfYear) 427 { 428 Interval interval = Interval.valueOf(intervalName); 429 Calendar calendar1 = Calendar.getInstance(); 430 firstWeekOfYear.apply(calendar1); 431 calendar1.setTime(date1); 432 Calendar calendar2 = Calendar.getInstance(); 433 firstWeekOfYear.apply(calendar2); 434 calendar2.setTime(date2); 435 return interval.diff(calendar1, calendar2, firstDayOfWeek); 436 } 437 438 @FunctionName("DatePart") 439 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])") 440 @Description( 441 "Returns a Variant (Integer) containing the specified part of a given " 442 + "date.") 443 public static int datePart(String interval, Date date) { 444 return _datePart( 445 interval, date, Calendar.SUNDAY, 446 FirstWeekOfYear.vbFirstJan1); 447 } 448 449 @FunctionName("DatePart") 450 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])") 451 @Description( 452 "Returns a Variant (Integer) containing the specified part of a given " 453 + "date.") 454 public static int datePart(String interval, Date date, int firstDayOfWeek) { 455 return _datePart( 456 interval, date, firstDayOfWeek, 457 FirstWeekOfYear.vbFirstJan1); 458 } 459 460 @FunctionName("DatePart") 461 @Signature("DatePart(interval, date[,firstdayofweek[, firstweekofyear]])") 462 @Description( 463 "Returns a Variant (Integer) containing the specified part of a given " 464 + "date.") 465 public static int datePart( 466 String interval, Date date, int firstDayOfWeek, 467 int firstWeekOfYear) 468 { 469 return _datePart( 470 interval, date, firstDayOfWeek, 471 FirstWeekOfYear.values()[firstWeekOfYear]); 472 } 473 474 private static int _datePart( 475 String intervalName, 476 Date date, 477 int firstDayOfWeek, 478 FirstWeekOfYear firstWeekOfYear) 479 { 480 Interval interval = Interval.valueOf(intervalName); 481 Calendar calendar = Calendar.getInstance(); 482 calendar.setTime(date); 483 switch (interval) { 484 case w: 485 case ww: 486 // firstWeekOfYear and firstDayOfWeek only matter for 'w' and 'ww' 487 firstWeekOfYear.apply(calendar); 488 calendar.setFirstDayOfWeek(firstDayOfWeek); 489 break; 490 } 491 return interval.datePart(calendar); 492 } 493 494 @FunctionName("Date") 495 @Signature("Date") 496 @Description("Returns a Variant (Date) containing the current system date.") 497 public static Date date() { 498 Calendar calendar = Calendar.getInstance(); 499 calendar.clear(); 500 calendar.set(Calendar.HOUR_OF_DAY, 0); 501 calendar.set(Calendar.MINUTE, 0); 502 calendar.set(Calendar.SECOND, 0); 503 calendar.set(Calendar.MILLISECOND, 0); 504 return calendar.getTime(); 505 } 506 507 // public void date(Object val) 508 509 @FunctionName("DateSerial") 510 @Signature("DateSerial(year, month, day)") 511 @Description( 512 "Returns a Variant (Date) for a specified year, month, and day.") 513 public static Date dateSerial(int year, int month, int day) { 514 Calendar calendar = Calendar.getInstance(); 515 calendar.clear(); 516 calendar.set(year, month - 1, day); 517 return calendar.getTime(); 518 } 519 520 @FunctionName("DateValue") 521 @Signature("DateValue(date)") 522 @Description("Returns a Variant (Date).") 523 public static Date dateValue(Date date) { 524 final Calendar calendar = Calendar.getInstance(); 525 calendar.clear(); 526 calendar.setTime(date); 527 calendar.set(Calendar.HOUR_OF_DAY, 0); 528 calendar.set(Calendar.MINUTE, 0); 529 calendar.set(Calendar.SECOND, 0); 530 calendar.set(Calendar.MILLISECOND, 0); 531 return calendar.getTime(); 532 } 533 534 @FunctionName("Day") 535 @Signature("Day(date)") 536 @Description( 537 "Returns a Variant (Integer) specifying a whole number between 1 and " 538 + "31, inclusive, representing the day of the month.") 539 public static int day(Date date) { 540 final Calendar calendar = Calendar.getInstance(); 541 calendar.setTime(date); 542 return calendar.get(Calendar.DAY_OF_MONTH); 543 } 544 545 @FunctionName("Hour") 546 @Signature("Hour(time)") 547 @Description( 548 "Returns a Variant (Integer) specifying a whole number between 0 and " 549 + "23, inclusive, representing the hour of the day.") 550 public static int hour(Date time) { 551 final Calendar calendar = Calendar.getInstance(); 552 calendar.setTime(time); 553 return calendar.get(Calendar.HOUR_OF_DAY); 554 } 555 556 @FunctionName("Minute") 557 @Signature("Minute(time)") 558 @Description( 559 "Returns a Variant (Integer) specifying a whole number between 0 and " 560 + "59, inclusive, representing the minute of the hour.") 561 public static int minute(Date time) { 562 final Calendar calendar = Calendar.getInstance(); 563 calendar.setTime(time); 564 return calendar.get(Calendar.MINUTE); 565 } 566 567 @FunctionName("Month") 568 @Signature("Month(date)") 569 @Description( 570 "Returns a Variant (Integer) specifying a whole number between 1 and " 571 + "12, inclusive, representing the month of the year.") 572 public static int month(Date date) { 573 final Calendar calendar = Calendar.getInstance(); 574 calendar.setTime(date); 575 final int month = calendar.get(Calendar.MONTH); 576 return month + 1; // convert from 0- to 1-based 577 } 578 579 @FunctionName("Now") 580 @Signature("Now()") 581 @Description( 582 "Returns a Variant (Date) specifying the current date and time " 583 + "according your computer's system date and time.") 584 public static Date now() { 585 return new Date(); 586 } 587 588 @FunctionName("Second") 589 @Signature("Second(time)") 590 @Description( 591 "Returns a Variant (Integer) specifying a whole number between 0 and " 592 + "59, inclusive, representing the second of the minute.") 593 public static int second(Date time) { 594 final Calendar calendar = Calendar.getInstance(); 595 calendar.setTime(time); 596 return calendar.get(Calendar.SECOND); 597 } 598 599 // public String time$() 600 // public void time$(String val) 601 602 @FunctionName("Time") 603 @Signature("Time()") 604 @Description("Returns a Variant (Date) indicating the current system time.") 605 public static Date time() { 606 return new Date(); 607 } 608 609 // public void time(Object val) 610 611 @FunctionName("TimeSerial") 612 @Signature("TimeSerial(hour, minute, second)") 613 @Description( 614 "Returns a Variant (Date) containing the time for a specific hour, " 615 + "minute, and second.") 616 public static Date timeSerial(int hour, int minute, int second) { 617 final Calendar calendar = Calendar.getInstance(); 618 calendar.clear(); 619 calendar.set(Calendar.HOUR_OF_DAY, hour); 620 calendar.set(Calendar.MINUTE, minute); 621 calendar.set(Calendar.SECOND, second); 622 return calendar.getTime(); 623 } 624 625 @FunctionName("TimeValue") 626 @Signature("TimeValue(time)") 627 @Description("Returns a Variant (Date) containing the time.") 628 public static Date timeValue(Date time) { 629 final Calendar calendar = Calendar.getInstance(); 630 calendar.clear(); 631 calendar.setTime(time); 632 calendar.set(1970, 0, 1); 633 return calendar.getTime(); 634 } 635 636 @FunctionName("Timer") 637 @Signature("Timer()") 638 @Description( 639 "Returns a Single representing the number of seconds elapsed since " 640 + "midnight.") 641 public static float timer() { 642 final Calendar calendar = Calendar.getInstance(); 643 final long now = calendar.getTimeInMillis(); 644 calendar.set(Calendar.HOUR_OF_DAY, 0); 645 calendar.set(Calendar.MINUTE, 0); 646 calendar.set(Calendar.SECOND, 0); 647 calendar.set(Calendar.MILLISECOND, 0); 648 final long midnight = calendar.getTimeInMillis(); 649 return ((float) (now - midnight)) / 1000f; 650 } 651 652 @FunctionName("Weekday") 653 @Signature("Weekday(date[, firstDayOfWeek])") 654 @Description( 655 "Returns a Variant (Integer) containing a whole number representing " 656 + "the day of the week.") 657 public static int weekday(Date date) { 658 return weekday(date, Calendar.SUNDAY); 659 } 660 661 @FunctionName("Weekday") 662 @Signature("Weekday(date[, firstDayOfWeek])") 663 @Description( 664 "Returns a Variant (Integer) containing a whole number representing " 665 + "the day of the week.") 666 public static int weekday(Date date, int firstDayOfWeek) { 667 final Calendar calendar = Calendar.getInstance(); 668 calendar.setTime(date); 669 int weekday = calendar.get(Calendar.DAY_OF_WEEK); 670 // adjust for start of week 671 weekday -= (firstDayOfWeek - 1); 672 // bring into range 1..7 673 weekday = (weekday + 6) % 7 + 1; 674 return weekday; 675 } 676 677 @FunctionName("Year") 678 @Signature("Year(date)") 679 @Description( 680 "Returns a Variant (Integer) containing a whole number representing " 681 + "the year.") 682 public static int year(Date date) { 683 final Calendar calendar = Calendar.getInstance(); 684 calendar.setTime(date); 685 return calendar.get(Calendar.YEAR); 686 } 687 688 // 689 // /* FileSystem */ 690 // public void chDir(String path) 691 // public void chDrive(String drive) 692 // public String curDir$(Object drive) 693 // public Object curDir(Object drive) 694 // public String dir(Object pathName, FileAttribute attributes /* default 695 // FileAttribute.Normal */) 696 // public boolean EOF(int fileNumber) 697 // public int fileAttr(int fileNumber, int returnType /* default 1 */) 698 // public void fileCopy(String source, String destination) 699 // public Object fileDateTime(String pathName) 700 // public int fileLen(String pathName) 701 // public int freeFile(Object rangeNumber) 702 // public FileAttribute getAttr(String pathName) 703 // public void kill(Object pathName) 704 // public int LOF(int fileNumber) 705 // public int loc(int fileNumber) 706 // public void mkDir(String path) 707 // public void reset() 708 // public void rmDir(String path) 709 // public int seek(int fileNumber) 710 // public void setAttr(String pathName, FileAttribute attributes) 711 // 712 // Financial 713 714 @FunctionName("DDB") 715 @Signature("DDB(cost, salvage, life, period[, factor])") 716 @Description( 717 "Returns a Double specifying the depreciation of an asset for a " 718 + "specific time period using the double-declining balance method or " 719 + "some other method you specify.") 720 public static double dDB( 721 double cost, 722 double salvage, 723 double life, 724 double period) 725 { 726 return dDB(cost, salvage, life, period, 2.0); 727 } 728 729 @FunctionName("DDB") 730 @Signature("DDB(cost, salvage, life, period[, factor])") 731 @Description( 732 "Returns a Double specifying the depreciation of an asset for a " 733 + "specific time period using the double-declining balance method or " 734 + "some other method you specify.") 735 public static double dDB( 736 double cost, 737 double salvage, 738 double life, 739 double period, 740 double factor) 741 { 742 return (((cost - salvage) * factor) / life) * period; 743 } 744 745 @FunctionName("FV") 746 @Signature("FV(rate, nper, pmt[, pv[, type]])") 747 @Description( 748 "Returns a Double specifying the future value of an annuity based on " 749 + "periodic, fixed payments and a fixed interest rate.") 750 public static double fV(double rate, double nPer, double pmt) { 751 return fV(rate, nPer, pmt, 0d, false); 752 } 753 754 @FunctionName("FV") 755 @Signature("FV(rate, nper, pmt[, pv[, type]])") 756 @Description( 757 "Returns a Double specifying the future value of an annuity based on " 758 + "periodic, fixed payments and a fixed interest rate.") 759 public static double fV(double rate, double nPer, double pmt, double pv) { 760 return fV(rate, nPer, pmt, pv, false); 761 } 762 763 @FunctionName("FV") 764 @Signature("FV(rate, nper, pmt[, pv[, type]])") 765 @Description( 766 "Returns a Double specifying the future value of an annuity based on " 767 + "periodic, fixed payments and a fixed interest rate.") 768 public static double fV( 769 double rate, 770 double nPer, 771 double pmt, 772 double pv, 773 boolean type) 774 { 775 if (rate == 0) { 776 return -(pv + (nPer * pmt)); 777 } else { 778 double r1 = rate + 1; 779 return ((1 - Math.pow(r1, nPer)) * (type ? r1 : 1) * pmt) / rate 780 - pv * Math.pow(r1, nPer); 781 } 782 } 783 784 @FunctionName("IPmt") 785 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])") 786 @Description( 787 "Returns a Double specifying the interest payment for a given period " 788 + "of an annuity based on periodic, fixed payments and a fixed " 789 + "interest rate.") 790 public static double iPmt(double rate, double per, double nPer, double PV) { 791 return iPmt(rate, per, nPer, PV, 0); 792 } 793 794 795 @FunctionName("IPmt") 796 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])") 797 @Description( 798 "Returns a Double specifying the interest payment for a given period " 799 + "of an annuity based on periodic, fixed payments and a fixed " 800 + "interest rate.") 801 public static double iPmt( 802 double rate, 803 double per, 804 double nPer, 805 double PV, 806 double fv) 807 { 808 return iPmt(rate, per, nPer, PV, fv, false); 809 } 810 811 812 @FunctionName("IPmt") 813 @Signature("IPmt(rate, per, nper, pv[, fv[, type]])") 814 @Description( 815 "Returns a Double specifying the interest payment for a given period " 816 + "of an annuity based on periodic, fixed payments and a fixed " 817 + "interest rate.") 818 public static double iPmt( 819 double rate, 820 double per, 821 double nPer, 822 double PV, 823 double fv, 824 boolean due) 825 { 826 double pmtVal = pmt(rate, nPer, PV, fv, due); 827 double pValm1 = PV - pV(rate, per - 1, pmtVal, fv, due); 828 return - pValm1 * rate; 829 } 830 831 @FunctionName("IRR") 832 @Signature("IRR(values()[, guess])") 833 @Description( 834 "Returns a Double specifying the internal rate of return for a series " 835 + "of periodic cash flows (payments and receipts).") 836 public static double IRR(double[] valueArray) { 837 return IRR(valueArray, 0.10); 838 } 839 840 841 @FunctionName("IRR") 842 @Signature("IRR(values()[, guess])") 843 @Description( 844 "Returns a Double specifying the internal rate of return for a series " 845 + "of periodic cash flows (payments and receipts).") 846 public static double IRR(double[] valueArray, double guess) { 847 // calc pV of stream (sum of pV's for valueArray) ((1 + guess) ^ index) 848 double minGuess = 0.0; 849 double maxGuess = 1.0; 850 851 // i'm not certain 852 int r = 1; 853 if (valueArray[0] > 0) { 854 r = -1; 855 } 856 857 for (int i = 0; i < 30; i++) { 858 // first calculate overall return based on guess 859 double totalPv = 0; 860 for (int j = 0; j < valueArray.length; j++) { 861 totalPv += valueArray[j] / Math.pow(1.0 + guess, j); 862 } 863 if ((maxGuess - minGuess) < 0.0000001) { 864 return guess; 865 } else if (totalPv * r < 0) { 866 maxGuess = guess; 867 } else { 868 minGuess = guess; 869 } 870 // avg max min to determine next step 871 guess = (maxGuess + minGuess) / 2; 872 } 873 // unable to find a match 874 return -1; 875 } 876 877 @FunctionName("MIRR") 878 @Signature("MIRR(values(), finance_rate, reinvest_rate)") 879 @Description( 880 "Returns a Double specifying the modified internal rate of return for " 881 + "a series of periodic cash flows (payments and receipts).") 882 public static double MIRR( 883 double valueArray[], 884 double financeRate, 885 double reinvestRate) 886 { 887 // based on 888 // http://en.wikipedia.org/wiki/Modified_Internal_Rate_of_Return 889 double reNPV = 0.0; 890 double fiNPV = 0.0; 891 for (int j = 0; j < valueArray.length; j++) { 892 if (valueArray[j] > 0) { 893 reNPV += valueArray[j] / Math.pow(1.0 + reinvestRate, j); 894 } else { 895 fiNPV += valueArray[j] / Math.pow(1.0 + financeRate, j); 896 } 897 } 898 899 double ratio = 900 (- reNPV * Math.pow(1 + reinvestRate, valueArray.length)) 901 / (fiNPV * (1 + financeRate)); 902 903 return Math.pow(ratio, 1.0 / (valueArray.length - 1)) - 1.0; 904 } 905 906 @FunctionName("NPer") 907 @Signature("NPer(rate, pmt, pv[, fv[, type]])") 908 @Description( 909 "Returns a Double specifying the number of periods for an annuity " 910 + "based on periodic, fixed payments and a fixed interest rate.") 911 public static double nPer( 912 double rate, 913 double pmt, 914 double pv, 915 double fv, 916 boolean due) 917 { 918 if (rate == 0) { 919 return -(fv + pv) / pmt; 920 } else { 921 double r1 = rate + 1; 922 double ryr = (due ? r1 : 1) * pmt / rate; 923 double a1 = 924 ((ryr - fv) < 0) 925 ? Math.log(fv - ryr) 926 : Math.log(ryr - fv); 927 double a2 = 928 ((ryr - fv) < 0) 929 ? Math.log(-pv - ryr) 930 : Math.log(pv + ryr); 931 double a3 = Math.log(r1); 932 return (a1 - a2) / a3; 933 } 934 } 935 936 @FunctionName("NPV") 937 @Signature("NPV(rate, values())") 938 @Description( 939 "Returns a Double specifying the net present value of an investment " 940 + "based on a series of periodic cash flows (payments and receipts) " 941 + "and a discount rate.") 942 public static double nPV(double r, double[] cfs) { 943 double npv = 0; 944 double r1 = r + 1; 945 double trate = r1; 946 for (int i = 0, iSize = cfs.length; i < iSize; i++) { 947 npv += cfs[i] / trate; 948 trate *= r1; 949 } 950 return npv; 951 } 952 953 @FunctionName("PPmt") 954 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])") 955 @Description( 956 "Returns a Double specifying the principal payment for a given period " 957 + "of an annuity based on periodic, fixed payments and a fixed " 958 + "interest rate.") 959 public static double pPmt(double rate, double per, double nPer, double PV) { 960 return pPmt(rate, per, nPer, PV, 0); 961 } 962 963 @FunctionName("PPmt") 964 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])") 965 @Description( 966 "Returns a Double specifying the principal payment for a given period " 967 + "of an annuity based on periodic, fixed payments and a fixed " 968 + "interest rate.") 969 public static double pPmt( 970 double rate, 971 double per, 972 double nPer, 973 double PV, 974 double fv) 975 { 976 return pPmt(rate, per, nPer, PV, fv, false); 977 } 978 979 @FunctionName("PPmt") 980 @Signature("PPmt(rate, per, nper, pv[, fv[, type]])") 981 @Description( 982 "Returns a Double specifying the principal payment for a given period " 983 + "of an annuity based on periodic, fixed payments and a fixed " 984 + "interest rate.") 985 public static double pPmt( 986 double rate, 987 double per, 988 double nPer, 989 double PV, 990 double fv, 991 boolean due) 992 { 993 return pmt(rate, nPer, PV, fv, due) 994 - iPmt(rate, per, nPer, PV, fv, due); 995 } 996 997 @FunctionName("Pmt") 998 @Signature("Pmt(rate, nper, pv[, fv[, type]])") 999 @Description( 1000 "Returns a Double specifying the payment for an annuity based on " 1001 + "periodic, fixed payments and a fixed interest rate.") 1002 public static double pmt( 1003 double rate, 1004 double nPer, 1005 double pv, 1006 double fv, 1007 boolean due) 1008 { 1009 if (rate == 0) { 1010 return -(fv + pv) / nPer; 1011 } else { 1012 double r1 = rate + 1; 1013 return 1014 (fv + pv * Math.pow(r1, nPer)) 1015 * rate 1016 / ((due ? r1 : 1) * (1 - Math.pow(r1, nPer))); 1017 } 1018 } 1019 1020 @FunctionName("PV") 1021 @Signature("PV(rate, nper, pmt[, fv[, type]])") 1022 @Description( 1023 "Returns a Double specifying the present value of an annuity based on " 1024 + "periodic, fixed payments to be paid in the future and a fixed " 1025 + "interest rate.") 1026 public static double pV( 1027 double rate, 1028 double nper, 1029 double pmt, 1030 double fv, 1031 boolean due) 1032 { 1033 if (rate == 0) { 1034 return -((nper * pmt) + fv); 1035 } else { 1036 double r1 = rate + 1; 1037 return 1038 (((1 - Math.pow(r1, nper)) / rate) * (due ? r1 : 1) * pmt - fv) 1039 / Math.pow(r1, nper); 1040 } 1041 } 1042 1043 @FunctionName("Rate") 1044 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])") 1045 @Description( 1046 "Returns a Double specifying the interest rate per period for an " 1047 + "annuity.") 1048 public static double rate( 1049 double nPer, 1050 double pmt, 1051 double PV) 1052 { 1053 return rate(nPer, pmt, PV, 0); 1054 } 1055 1056 @FunctionName("Rate") 1057 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])") 1058 @Description( 1059 "Returns a Double specifying the interest rate per period for an " 1060 + "annuity.") 1061 public static double rate( 1062 double nPer, 1063 double pmt, 1064 double PV, 1065 double fv) 1066 { 1067 return rate(nPer, pmt, PV, fv, false); 1068 } 1069 1070 @FunctionName("Rate") 1071 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])") 1072 @Description( 1073 "Returns a Double specifying the interest rate per period for an " 1074 + "annuity.") 1075 public static double rate( 1076 double nPer, 1077 double pmt, 1078 double PV, 1079 double fv, 1080 boolean type) 1081 { 1082 return rate(nPer, pmt, PV, fv, type, 0.1); 1083 } 1084 1085 @FunctionName("Rate") 1086 @Signature("Rate(nper, pmt, pv[, fv[, type[, guess]]])") 1087 @Description( 1088 "Returns a Double specifying the interest rate per period for an " 1089 + "annuity.") 1090 public static double rate( 1091 double nPer, // specifies the number of payment periods 1092 double pmt, // payment per period of annuity 1093 double PV, // the present value of the annuity (0 if a loan) 1094 double fv, // the future value of the annuity ($ if savings) 1095 boolean due, 1096 double guess) 1097 { 1098 if (nPer <= 0) { 1099 throw new InvalidArgumentException( 1100 "number of payment periods must be larger than 0"); 1101 } 1102 double minGuess = 0.0; 1103 double maxGuess = 1.0; 1104 1105 // converge on the correct answer should use Newton's Method 1106 // for now use a binary search 1107 int r = 1; 1108 if (PV < fv) { 1109 r = -1; 1110 } 1111 1112 // the vb method uses 20 iterations, but they also probably use newton's 1113 // method, 1114 // so i've bumped it up to 30 iterations. 1115 for (int n = 0; n < 30; n++) { 1116 double gFV = fV(guess, nPer, pmt, PV, due); 1117 double diff = gFV - fv; 1118 if ((maxGuess - minGuess) < 0.0000001) { 1119 return guess; 1120 } else { 1121 if (diff * r < 0) { 1122 maxGuess = guess; 1123 } else { 1124 minGuess = guess; 1125 } 1126 guess = (maxGuess + minGuess) / 2; 1127 } 1128 } 1129 // fail, not sure how VB fails 1130 return -1; 1131 } 1132 1133 @FunctionName("SLN") 1134 @Signature("SLN(cost, salvage, life)") 1135 @Description( 1136 "Returns a Double specifying the straight-line depreciation of an " 1137 + "asset for a single period.") 1138 public static double sLN(double cost, double salvage, double life) { 1139 return (cost - salvage) / life; 1140 } 1141 1142 @FunctionName("SYD") 1143 @Signature("SYD(cost, salvage, life, period)") 1144 @Description( 1145 "Returns a Double specifying the sum-of-years' digits depreciation of " 1146 + "an asset for a specified period.") 1147 public static double sYD( 1148 double cost, 1149 double salvage, 1150 double life, 1151 double period) 1152 { 1153 return (cost - salvage) * (life / (period * (period + 1) / 2)); 1154 } 1155 1156 // Information 1157 1158 // public Throwable err() 1159 // public Object iMEStatus() 1160 1161 @FunctionName("IsArray") 1162 @Signature("IsArray(varname)") 1163 @Description( 1164 "Returns a Boolean value indicating whether a variable is an array.") 1165 public boolean isArray(Object varName) { 1166 // arrays are not supported at present 1167 return false; 1168 } 1169 1170 @FunctionName("IsDate") 1171 @Signature("IsDate(varname)") 1172 @Description( 1173 "Returns a Boolean value indicating whether an expression can be " 1174 + "converted to a date.") 1175 public static boolean isDate(Object expression) { 1176 // IsDate returns True if Expression represents a valid date, a valid 1177 // time, or a valid date and time. 1178 try { 1179 Date val = cDate(expression); 1180 return (val != null); 1181 } catch (InvalidArgumentException e) { 1182 return false; 1183 } 1184 } 1185 1186 // use mondrian's implementation of IsEmpty 1187 // public boolean isEmpty(Object expression) 1188 1189 @FunctionName("IsError") 1190 @Signature("IsError(varname)") 1191 @Description( 1192 "Returns a Boolean value indicating whether an expression is an error " 1193 + "value.") 1194 public boolean isError(Object expression) { 1195 return expression instanceof Throwable; 1196 } 1197 1198 @FunctionName("IsMissing") 1199 @Signature("IsMissing(varname)") 1200 @Description( 1201 "Returns a Boolean value indicating whether an optional Variant " 1202 + "argument has been passed to a procedure.") 1203 public boolean isMissing(Object argName) { 1204 // We have no way to detect missing arguments. 1205 return false; 1206 } 1207 1208 @FunctionName("IsNull") 1209 @Signature("IsNull(varname)") 1210 @Description( 1211 "Returns a Boolean value that indicates whether an expression " 1212 + "contains no valid data (Null).") 1213 public boolean isNull(Object expression) { 1214 return expression == null; 1215 } 1216 1217 @FunctionName("IsNumeric") 1218 @Signature("IsNumeric(varname)") 1219 @Description( 1220 "Returns a Boolean value indicating whether an expression can be " 1221 + "evaluated as a number.") 1222 public boolean isNumeric(Object expression) { 1223 return expression instanceof Number; 1224 } 1225 1226 @FunctionName("IsObject") 1227 @Signature("IsObject(varname)") 1228 @Description( 1229 "Returns a Boolean value indicating whether an identifier represents " 1230 + "an object variable.") 1231 public boolean isObject(Object expression) { 1232 return false; 1233 } 1234 1235 // public int qBColor(int color) 1236 // public int RGB(int red, int green, int blue) 1237 1238 @FunctionName("TypeName") 1239 @Signature("TypeName(varname)") 1240 @Description("Returns a String that provides information about a variable.") 1241 public static String typeName(Object varName) { 1242 // The string returned by TypeName can be any one of the following: 1243 // 1244 // String returned Variable 1245 // object type An object whose type is objecttype 1246 // Byte Byte value 1247 // Integer Integer 1248 // Long Long integer 1249 // Single Single-precision floating-point number 1250 // Double Double-precision floating-point number 1251 // Currency Currency value 1252 // Decimal Decimal value 1253 // Date Date value 1254 // String String 1255 // Boolean Boolean value 1256 // Error An error value 1257 // Empty Uninitialized 1258 // Null No valid data 1259 // Object An object 1260 // Unknown An object whose type is unknown 1261 // Nothing Object variable that doesn't refer to an object 1262 1263 if (varName == null) { 1264 return "NULL"; 1265 } else { 1266 // strip off the package information 1267 String name = varName.getClass().getName(); 1268 if (name.lastIndexOf(".") >= 0) { 1269 name = name.substring(name.lastIndexOf(".") + 1); 1270 } 1271 return name; 1272 } 1273 } 1274 1275 // public VarType varType(Object varName) 1276 1277 // Interaction 1278 1279 // public void appActivate(Object title, Object wait) 1280 // public void beep() 1281 // public Object callByName(Object object, String procName, CallType 1282 // callType, Object args, int lcid) 1283 // public Object choose(float index, Object choice) 1284 // public String command$() 1285 // public Object command() 1286 // public Object createObject(String Class, String serverName) 1287 // public int doEvents() 1288 // public String environ$(Object expression) 1289 // public Object environ(Object expression) 1290 // public Object getAllSettings(String appName, String section) 1291 // public Object getObject(Object pathName, Object Class) 1292 // public String getSetting(String appName, String section, String key, 1293 // Object Default) 1294 // public Object iIf(Object expression, Object truePart, Object falsePart) 1295 // public String inputBox(Object prompt, Object title, Object Default, 1296 // Object xPos, Object yPos, Object helpFile, Object context) 1297 // public String macScript(String script) 1298 // public MsgBoxResult msgBox(Object prompt, MsgBoxStyle buttons /* default 1299 // MsgBoxStyle.OKOnly */, Object title, Object helpFile, Object context) 1300 // public Object partition(Object number, Object start, Object stop, Object 1301 // interval) 1302 // public void saveSetting(String appName, String section, String key, 1303 // String setting) 1304 // public void sendKeys(String string, Object wait) 1305 // public double shell(Object pathName, AppWinStyle windowStyle /* default 1306 // AppWinStyle.MinimizedFocus */) 1307 // public Object Switch(Object varExpr) 1308 1309 // Mathematical 1310 1311 @FunctionName("Abs") 1312 @Signature("Abs(number)") 1313 @Description( 1314 "Returns a value of the same type that is passed to it specifying the " 1315 + "absolute value of a number.") 1316 public static double abs(double number) { 1317 return Math.abs(number); 1318 } 1319 1320 @FunctionName("Atn") 1321 @Signature("Atn(number)") 1322 @Description("Returns a Double specifying the arctangent of a number.") 1323 public static double atn(double number) { 1324 return Math.atan(number); 1325 } 1326 1327 @FunctionName("Cos") 1328 @Signature("Cos(number)") 1329 @Description("Returns a Double specifying the cosine of an angle.") 1330 public static double cos(double number) { 1331 return Math.cos(number); 1332 } 1333 1334 @FunctionName("Exp") 1335 @Signature("Exp(number)") 1336 @Description( 1337 "Returns a Double specifying e (the base of natural logarithms) " 1338 + "raised to a power.") 1339 public static double exp(double number) { 1340 return Math.exp(number); 1341 } 1342 1343 @FunctionName("Log") 1344 @Signature("Log(number)") 1345 @Description( 1346 "Returns a Double specifying the natural logarithm of a number.") 1347 public static double log(double number) { 1348 return Math.log(number); 1349 } 1350 1351 // Cannot implement randomize and rnd - we require context to hold the 1352 // seed 1353 1354 // public void randomize(Object number) 1355 // public float rnd(Object number) 1356 1357 @FunctionName("Round") 1358 @Signature("Round(number[, numDigitsAfterDecimal])") 1359 @Description( 1360 "Returns a number rounded to a specified number of decimal places.") 1361 public static double round(double number) { 1362 return Math.round(number); 1363 } 1364 1365 @FunctionName("Round") 1366 @Signature("Round(number[, numDigitsAfterDecimal])") 1367 @Description( 1368 "Returns a number rounded to a specified number of decimal places.") 1369 public static double round(double number, int numDigitsAfterDecimal) { 1370 if (numDigitsAfterDecimal == 0) { 1371 return Math.round(number); 1372 } 1373 final double shift = Math.pow(10d, numDigitsAfterDecimal); 1374 double numberScaled = number * shift; 1375 double resultScaled = Math.round(numberScaled); 1376 return resultScaled / shift; 1377 } 1378 1379 @FunctionName("Sgn") 1380 @Signature("Sgn(number)") 1381 @Description("Returns a Variant (Integer) indicating the sign of a number.") 1382 public static int sgn(double number) { 1383 // We could use Math.signum(double) from JDK 1.5 onwards. 1384 return number < 0.0d ? -1 : number > 0.0d ? 1 : 0; 1385 } 1386 1387 @FunctionName("Sin") 1388 @Signature("Sin(number)") 1389 @Description("Returns a Double specifying the sine of an angle.") 1390 public static double sin(double number) { 1391 return Math.sin(number); 1392 } 1393 1394 @FunctionName("Sqr") 1395 @Signature("Sqr(number)") 1396 @Description("Returns a Double specifying the square root of a number.") 1397 public static double sqr(double number) { 1398 return Math.sqrt(number); 1399 } 1400 1401 @FunctionName("Tan") 1402 @Signature("Tan(number)") 1403 @Description("Returns a Double specifying the tangent of an angle.") 1404 public static double tan(double number) { 1405 return Math.tan(number); 1406 } 1407 1408 // Strings 1409 1410 @FunctionName("Asc") 1411 @Signature("Asc(string)") 1412 @Description( 1413 "Returns an Integer representing the character code corresponding to " 1414 + "the first letter in a string.") 1415 public static int asc(String string) { 1416 return string.charAt(0); 1417 } 1418 1419 @FunctionName("AscB") 1420 @Signature("AscB(string)") 1421 @Description("See Asc.") 1422 public static int ascB(String string) { 1423 return (byte) string.charAt(0); 1424 } 1425 1426 @FunctionName("AscW") 1427 @Signature("AscW(string)") 1428 @Description("See Asc.") 1429 public static int ascW(String string) { 1430 return asc(string); 1431 } 1432 1433 // public String chr$(int charCode) 1434 // public String chrB$(int charCode) 1435 1436 @FunctionName("Chr") 1437 @Signature("Chr(charcode)") 1438 @Description( 1439 "Returns a String containing the character associated with the " 1440 + "specified character code.") 1441 public static String chr(int charCode) { 1442 return new String(new char[] { (char) charCode }); 1443 } 1444 1445 @FunctionName("ChrB") 1446 @Signature("ChrB(charcode)") 1447 @Description("See Chr.") 1448 public static String chrB(int charCode) { 1449 return new String(new byte[] { (byte) charCode }); 1450 } 1451 1452 // public String chrW$(int charCode) 1453 1454 @FunctionName("ChrW") 1455 @Signature("ChrW(charcode)") 1456 @Description("See Chr.") 1457 public static String chrW(int charCode) { 1458 return new String(new char[] { (char) charCode }); 1459 } 1460 1461 // public Object filter(Object sourceArray, String match, boolean include /* 1462 // default 1 */, int compare /* default BinaryCompare */) 1463 // public String format$(Object expression, Object format, int 1464 // firstDayOfWeek /* default Sunday */, int firstWeekOfYear /* default 1465 // FirstJan1 */) 1466 1467 @FunctionName("FormatCurrency") 1468 @Signature( 1469 "FormatCurrency(Expression[,NumDigitsAfterDecimal " 1470 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1471 + "[,GroupDigits]]]])") 1472 @Description( 1473 "Returns an expression formatted as a currency value using the " 1474 + "currency symbol defined in the system control panel.") 1475 public static String formatCurrency(Object expression) { 1476 return formatCurrency(expression, -1, -2, -2, -2); 1477 } 1478 1479 @FunctionName("FormatCurrency") 1480 @Signature( 1481 "FormatCurrency(Expression[,NumDigitsAfterDecimal " 1482 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1483 + "[,GroupDigits]]]])") 1484 @Description( 1485 "Returns an expression formatted as a currency value using the " 1486 + "currency symbol defined in the system control panel.") 1487 public static String formatCurrency( 1488 Object expression, 1489 int numDigitsAfterDecimal) 1490 { 1491 return formatCurrency(expression, numDigitsAfterDecimal, -2, -2, -2); 1492 } 1493 1494 @FunctionName("FormatCurrency") 1495 @Signature( 1496 "FormatCurrency(Expression[,NumDigitsAfterDecimal " 1497 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1498 + "[,GroupDigits]]]])") 1499 @Description( 1500 "Returns an expression formatted as a currency value using the " 1501 + "currency symbol defined in the system control panel.") 1502 public static String formatCurrency( 1503 Object expression, 1504 int numDigitsAfterDecimal, 1505 int includeLeadingDigit) 1506 { 1507 return formatCurrency( 1508 expression, numDigitsAfterDecimal, 1509 includeLeadingDigit, -2, -2); 1510 } 1511 1512 @FunctionName("FormatCurrency") 1513 @Signature( 1514 "FormatCurrency(Expression[,NumDigitsAfterDecimal " 1515 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1516 + "[,GroupDigits]]]])") 1517 @Description( 1518 "Returns an expression formatted as a currency value using the " 1519 + "currency symbol defined in the system control panel.") 1520 public static String formatCurrency( 1521 Object expression, 1522 int numDigitsAfterDecimal, 1523 int includeLeadingDigit, 1524 int useParensForNegativeNumbers) 1525 { 1526 return formatCurrency( 1527 expression, 1528 numDigitsAfterDecimal, 1529 includeLeadingDigit, 1530 useParensForNegativeNumbers, 1531 -2); 1532 } 1533 1534 @FunctionName("FormatCurrency") 1535 @Signature( 1536 "FormatCurrency(Expression[,NumDigitsAfterDecimal " 1537 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1538 + "[,GroupDigits]]]])") 1539 @Description( 1540 "Returns an expression formatted as a currency value using the " 1541 + "currency symbol defined in the system control panel.") 1542 public static String formatCurrency( 1543 Object expression, 1544 int numDigitsAfterDecimal, 1545 int includeLeadingDigit, 1546 int useParensForNegativeNumbers, 1547 int groupDigits) 1548 { 1549 DecimalFormat format = 1550 (DecimalFormat) NumberFormat.getCurrencyInstance(); 1551 if (numDigitsAfterDecimal != -1) { 1552 format.setMaximumFractionDigits(numDigitsAfterDecimal); 1553 format.setMinimumFractionDigits(numDigitsAfterDecimal); 1554 } 1555 if (includeLeadingDigit != -2) { 1556 if (includeLeadingDigit != 0) { 1557 format.setMinimumIntegerDigits(1); 1558 } else { 1559 format.setMinimumIntegerDigits(0); 1560 } 1561 } 1562 if (useParensForNegativeNumbers != -2) { 1563 // todo: implement. 1564 // This will require tweaking of the currency expression 1565 } 1566 if (groupDigits != -2) { 1567 if (groupDigits != 0) { 1568 format.setGroupingUsed(false); 1569 } else { 1570 format.setGroupingUsed(true); 1571 } 1572 } 1573 return format.format(expression); 1574 } 1575 1576 @FunctionName("FormatDateTime") 1577 @Signature("FormatDateTime(Date[,NamedFormat])") 1578 @Description("Returns an expression formatted as a date or time.") 1579 public static String formatDateTime(Date date) { 1580 return formatDateTime(date, 0); 1581 } 1582 1583 @FunctionName("FormatDateTime") 1584 @Signature("FormatDateTime(Date[,NamedFormat])") 1585 @Description("Returns an expression formatted as a date or time.") 1586 public static String formatDateTime( 1587 Date date, 1588 int namedFormat /* default 0, GeneralDate */) 1589 { 1590 // todo: test 1591 // todo: how do we support VB Constants? Strings or Ints? 1592 switch (namedFormat) { 1593 // vbLongDate, 1 1594 // Display a date using the long date format specified in your 1595 // computer's regional settings. 1596 1597 case 1: 1598 return DateFormat.getDateInstance(DateFormat.LONG).format(date); 1599 1600 // vbShortDate, 2 1601 // Display a date using the short date format specified in your 1602 // computer's regional settings. 1603 case 2: 1604 return DateFormat.getDateInstance(DateFormat.SHORT).format(date); 1605 1606 // vbLongTime, 3 1607 // Display a time using the time format specified in your computer's 1608 // regional settings. 1609 case 3: 1610 return DateFormat.getTimeInstance(DateFormat.LONG).format(date); 1611 1612 // vbShortTime, 4 1613 // Display a time using the 24-hour format (hh:mm). 1614 case 4: 1615 return DateFormat.getTimeInstance(DateFormat.SHORT).format(date); 1616 1617 // vbGeneralDate, 0 1618 // Display a date and/or time. If there is a date part, 1619 // display it as a short date. If there is a time part, 1620 // display it as a long time. If present, both parts are 1621 // displayed. 1622 // 1623 // todo: how do we determine if there is a "time part" in java? 1624 case 0: 1625 default: 1626 return DateFormat.getDateTimeInstance().format(date); 1627 } 1628 } 1629 1630 // Format is implemented with FormatFunDef, third and fourth params are not 1631 // supported 1632 // @FunctionName("Format") 1633 // @Signature("Format(expression[, format[, firstdayofweek[, 1634 // firstweekofyear]]])") 1635 // @Description("Returns a Variant (String) containing an expression 1636 // formatted according to instructions contained in a format expression.") 1637 1638 1639 @FunctionName("FormatNumber") 1640 @Signature( 1641 "FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit " 1642 + "[,UseParensForNegativeNumbers [,GroupDigits]]]])") 1643 @Description("Returns an expression formatted as a number.") 1644 public static String formatNumber(Object expression) { 1645 return formatNumber(expression, -1); 1646 } 1647 1648 @FunctionName("FormatNumber") 1649 @Signature( 1650 "FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit " 1651 + "[,UseParensForNegativeNumbers [,GroupDigits]]]])") 1652 @Description("Returns an expression formatted as a number.") 1653 public static String formatNumber( 1654 Object expression, 1655 int numDigitsAfterDecimal) 1656 { 1657 return formatNumber(expression, numDigitsAfterDecimal, -1); 1658 } 1659 1660 @FunctionName("FormatNumber") 1661 @Signature( 1662 "FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit " 1663 + "[,UseParensForNegativeNumbers [,GroupDigits]]]])") 1664 @Description("Returns an expression formatted as a number.") 1665 public static String formatNumber( 1666 Object expression, 1667 int numDigitsAfterDecimal, 1668 int includeLeadingDigit) 1669 { 1670 return formatNumber( 1671 expression, 1672 numDigitsAfterDecimal, 1673 includeLeadingDigit, 1674 -1); 1675 } 1676 1677 @FunctionName("FormatNumber") 1678 @Signature( 1679 "FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit " 1680 + "[,UseParensForNegativeNumbers [,GroupDigits]]]])") 1681 @Description("Returns an expression formatted as a number.") 1682 public static String formatNumber( 1683 Object expression, 1684 int numDigitsAfterDecimal, 1685 int includeLeadingDigit, 1686 int useParensForNegativeNumbers) 1687 { 1688 return formatNumber( 1689 expression, 1690 numDigitsAfterDecimal, 1691 includeLeadingDigit, 1692 useParensForNegativeNumbers, -1); 1693 } 1694 1695 @FunctionName("FormatNumber") 1696 @Signature( 1697 "FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit " 1698 + "[,UseParensForNegativeNumbers [,GroupDigits]]]])") 1699 @Description("Returns an expression formatted as a number.") 1700 public static String formatNumber( 1701 Object expression, 1702 int numDigitsAfterDecimal /* default -1 */, 1703 int includeLeadingDigit /* default usedefault */, 1704 int useParensForNegativeNumbers /* default UseDefault */, 1705 int groupDigits /* default UseDefault */) 1706 { 1707 NumberFormat format = NumberFormat.getNumberInstance(); 1708 if (numDigitsAfterDecimal != -1) { 1709 format.setMaximumFractionDigits(numDigitsAfterDecimal); 1710 format.setMinimumFractionDigits(numDigitsAfterDecimal); 1711 } 1712 1713 if (includeLeadingDigit != -1) { 1714 if (includeLeadingDigit != 0) { 1715 // true 1716 format.setMinimumIntegerDigits(1); 1717 } else { 1718 format.setMinimumIntegerDigits(0); 1719 } 1720 } 1721 1722 if (useParensForNegativeNumbers != -1) { 1723 if (useParensForNegativeNumbers != 0) { 1724 DecimalFormat dformat = (DecimalFormat)format; 1725 dformat.setNegativePrefix("("); 1726 dformat.setNegativeSuffix(")"); 1727 } else { 1728 DecimalFormat dformat = (DecimalFormat)format; 1729 dformat.setNegativePrefix( 1730 "" + dformat.getDecimalFormatSymbols().getMinusSign()); 1731 dformat.setNegativeSuffix(""); 1732 } 1733 } 1734 1735 if (groupDigits != -1) { 1736 format.setGroupingUsed(groupDigits != 0); 1737 } 1738 1739 return format.format(expression); 1740 } 1741 1742 @FunctionName("FormatPercent") 1743 @Signature( 1744 "FormatPercent(Expression[,NumDigitsAfterDecimal " 1745 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1746 + "[,GroupDigits]]]])") 1747 @Description( 1748 "Returns an expression formatted as a percentage (multipled by 100) " 1749 + "with a trailing % character.") 1750 public static String formatPercent(Object expression) { 1751 return formatPercent(expression, -1); 1752 } 1753 1754 @FunctionName("FormatPercent") 1755 @Signature( 1756 "FormatPercent(Expression[,NumDigitsAfterDecimal " 1757 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1758 + "[,GroupDigits]]]])") 1759 @Description( 1760 "Returns an expression formatted as a percentage (multipled by 100) " 1761 + "with a trailing % character.") 1762 public static String formatPercent( 1763 // todo: impl & test 1764 Object expression, int numDigitsAfterDecimal /* default -1 */) 1765 { 1766 return formatPercent(expression, numDigitsAfterDecimal, -1); 1767 } 1768 1769 @FunctionName("FormatPercent") 1770 @Signature( 1771 "FormatPercent(Expression[,NumDigitsAfterDecimal " 1772 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1773 + "[,GroupDigits]]]])") 1774 @Description( 1775 "Returns an expression formatted as a percentage (multipled by 100) " 1776 + "with a trailing % character.") 1777 public static String formatPercent( 1778 // todo: impl & test 1779 Object expression, 1780 int numDigitsAfterDecimal /* default -1 */, 1781 int includeLeadingDigit /* default UseDefault */) 1782 { 1783 return formatPercent( 1784 expression, 1785 numDigitsAfterDecimal, 1786 includeLeadingDigit, 1787 -1); 1788 } 1789 1790 @FunctionName("FormatPercent") 1791 @Signature( 1792 "FormatPercent(Expression[,NumDigitsAfterDecimal " 1793 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1794 + "[,GroupDigits]]]])") 1795 @Description( 1796 "Returns an expression formatted as a percentage (multipled by 100) " 1797 + "with a trailing % character.") 1798 public static String formatPercent( 1799 // todo: impl & test 1800 Object expression, 1801 int numDigitsAfterDecimal /* default -1 */, 1802 int includeLeadingDigit /* default UseDefault */, 1803 int useParensForNegativeNumbers /* default UseDefault */) 1804 { 1805 return formatPercent( 1806 expression, numDigitsAfterDecimal, 1807 includeLeadingDigit, useParensForNegativeNumbers, -1); 1808 } 1809 1810 @FunctionName("FormatPercent") 1811 @Signature( 1812 "FormatPercent(Expression[,NumDigitsAfterDecimal " 1813 + "[,IncludeLeadingDigit [,UseParensForNegativeNumbers " 1814 + "[,GroupDigits]]]])") 1815 @Description( 1816 "Returns an expression formatted as a percentage (multipled by 100) " 1817 + "with a trailing % character.") 1818 public static String formatPercent( 1819 Object expression, 1820 int numDigitsAfterDecimal /* default -1 */, 1821 int includeLeadingDigit /* default UseDefault */, 1822 int useParensForNegativeNumbers /* default UseDefault */, 1823 int groupDigits /* default UseDefault */) 1824 { 1825 NumberFormat format = NumberFormat.getPercentInstance(); 1826 if (numDigitsAfterDecimal != -1) { 1827 format.setMaximumFractionDigits(numDigitsAfterDecimal); 1828 format.setMinimumFractionDigits(numDigitsAfterDecimal); 1829 } 1830 1831 if (includeLeadingDigit != -1) { 1832 if (includeLeadingDigit != 0) { 1833 // true 1834 format.setMinimumIntegerDigits(1); 1835 } else { 1836 format.setMinimumIntegerDigits(0); 1837 } 1838 } 1839 1840 if (useParensForNegativeNumbers != -1) { 1841 if (useParensForNegativeNumbers != 0) { 1842 DecimalFormat dformat = (DecimalFormat)format; 1843 dformat.setNegativePrefix("("); 1844 dformat.setNegativeSuffix( 1845 "" + dformat.getDecimalFormatSymbols().getPercent() + ")"); 1846 } else { 1847 DecimalFormat dformat = (DecimalFormat)format; 1848 dformat.setNegativePrefix( 1849 "" + dformat.getDecimalFormatSymbols().getMinusSign()); 1850 dformat.setNegativeSuffix( 1851 "" + dformat.getDecimalFormatSymbols().getPercent()); 1852 } 1853 } 1854 1855 if (groupDigits != -1) { 1856 format.setGroupingUsed(groupDigits != 0); 1857 } 1858 1859 return format.format(expression); 1860 } 1861 1862 // public Object inStrB(Object start, Object string1, Object string2, int 1863 // compare /* default BinaryCompare */) 1864 1865 @FunctionName("InStr") 1866 @Signature("InStr([start, ]stringcheck, stringmatch[, compare])") 1867 @Description( 1868 "Returns a Variant (Long) specifying the position of the first " 1869 + "occurrence of one string within another.") 1870 public static int inStr(String stringCheck, String stringMatch) { 1871 return inStr(1, stringCheck, stringMatch, 0); 1872 } 1873 1874 @FunctionName("InStr") 1875 @Signature("InStr([start, ]stringcheck, stringmatch[, compare])") 1876 @Description( 1877 "Returns the position of an occurrence of one string within " 1878 + "another.") 1879 public static int inStr( 1880 int start /* default 1 */, 1881 String stringCheck, 1882 String stringMatch) 1883 { 1884 return inStr(start, stringCheck, stringMatch, 0); 1885 } 1886 1887 @FunctionName("InStr") 1888 @Signature("InStr([start, ]stringcheck, stringmatch[, compare])") 1889 @Description( 1890 "Returns the position of an occurrence of one string within " 1891 + "another.") 1892 public static int inStr( 1893 int start /* default 1 */, 1894 String stringCheck, 1895 String stringMatch, 1896 int compare /* default BinaryCompare */) 1897 { 1898 // todo: implement binary vs. text compare 1899 if (start == 0 || start < -1) { 1900 throw new InvalidArgumentException( 1901 "start must be -1 or a location in the string to start"); 1902 } 1903 if (start != -1) { 1904 return stringCheck.indexOf(stringMatch, start - 1) + 1; 1905 } else { 1906 return stringCheck.indexOf(stringMatch) + 1; 1907 } 1908 } 1909 1910 @FunctionName("InStrRev") 1911 @Signature("InStrRev(stringcheck, stringmatch[, start[, compare]])") 1912 @Description( 1913 "Returns the position of an occurrence of one string within another, " 1914 + "from the end of string.") 1915 public static int inStrRev(String stringCheck, String stringMatch) { 1916 return inStrRev(stringCheck, stringMatch, -1); 1917 } 1918 1919 @FunctionName("InStrRev") 1920 @Signature("InStrRev(stringcheck, stringmatch[, start[, compare]])") 1921 @Description( 1922 "Returns the position of an occurrence of one string within another, " 1923 + "from the end of string.") 1924 public static int inStrRev( 1925 String stringCheck, 1926 String stringMatch, 1927 int start /* default -1 */) 1928 { 1929 return inStrRev(stringCheck, stringMatch, start, 0); 1930 } 1931 1932 @FunctionName("InStrRev") 1933 @Signature("InStrRev(stringcheck, stringmatch[, start[, compare]])") 1934 @Description( 1935 "Returns the position of an occurrence of one string within another, " 1936 + "from the end of string.") 1937 public static int inStrRev( 1938 String stringCheck, 1939 String stringMatch, 1940 int start /* default -1 */, 1941 int compare /* default BinaryCompare */) 1942 { 1943 // todo: implement binary vs. text compare 1944 if (start == 0 || start < -1) { 1945 throw new InvalidArgumentException( 1946 "start must be -1 or a location in the string to start"); 1947 } 1948 if (start != -1) { 1949 return stringCheck.lastIndexOf(stringMatch, start - 1) + 1; 1950 } else { 1951 return stringCheck.lastIndexOf(stringMatch) + 1; 1952 } 1953 } 1954 1955 // public String join(Object sourceArray, Object delimiter) 1956 1957 @FunctionName("LCase") 1958 @Signature("LCase(string)") 1959 @Description("Returns a String that has been converted to lowercase.") 1960 public static String lCase(String string) { 1961 return string.toLowerCase(); 1962 } 1963 1964 // public Object lCase$(Object string) 1965 // public String lTrim$(String string) 1966 1967 @FunctionName("LTrim") 1968 @Signature("LTrim(string)") 1969 @Description( 1970 "Returns a Variant (String) containing a copy of a specified string " 1971 + "without leading spaces.") 1972 public static String lTrim(String string) { 1973 int i = 0, n = string.length(); 1974 while (i < n) { 1975 if (string.charAt(i) > ' ') { 1976 break; 1977 } 1978 i++; 1979 } 1980 return string.substring(i); 1981 } 1982 1983 // public String left$(String string, int length) 1984 // public String leftB$(String string, int length) 1985 // public Object leftB(Object string, int length) 1986 1987 @FunctionName("Left") 1988 @Signature("Left(string, length)") 1989 @Description( 1990 "Returns a specified number of characters from the left side of a " 1991 + "string.") 1992 public static String left(String string, int length) { 1993 final int stringLength = string.length(); 1994 if (length >= stringLength) { 1995 return string; 1996 } 1997 return string.substring(0, length); 1998 } 1999 2000 // public Object lenB(Object expression) 2001 2002 // len is already implemented in BuiltinFunTable... defer 2003 2004 // @FunctionName("Len") 2005 // @Signature("Len(String)") 2006 // @Description("Returns a Long containing the number of characters in a 2007 // string.") 2008 // public static int len(String expression) { 2009 // return expression.length(); 2010 // } 2011 2012 // public String mid$(String string, int start, Object length) 2013 // public String midB$(String string, int start, Object length) 2014 // public Object midB(Object string, int start, Object length) 2015 2016 @FunctionName("Mid") 2017 @Signature("Mid(value, beginIndex[, length])") 2018 @Description("Returns a specified number of characters from a string.") 2019 public static String mid(String value, int beginIndex) { 2020 // If we used 'value.length() - beginIndex' as the default value for 2021 // length, we'd have problems if beginIndex is huge; 2022 // so 'value.length()' looks like an overestimate - but will always 2023 // return the correct result. 2024 final int length = value.length(); 2025 return mid(value, beginIndex, length); 2026 } 2027 2028 @FunctionName("Mid") 2029 @Signature("Mid(value, beginIndex[, length])") 2030 @Description("Returns a specified number of characters from a string.") 2031 public static String mid(String value, int beginIndex, int length) { 2032 // Arguments are 1-based. Spec says that the function gives an error if 2033 // Start <= 0 or Length < 0. 2034 if (beginIndex <= 0) { 2035 throw new InvalidArgumentException( 2036 "Invalid parameter. " 2037 + "Start parameter of Mid function must be positive"); 2038 } 2039 if (length < 0) { 2040 throw new InvalidArgumentException( 2041 "Invalid parameter. " 2042 + "Length parameter of Mid function must be non-negative"); 2043 } 2044 2045 if (beginIndex > value.length()) { 2046 return ""; 2047 } 2048 2049 // Shift from 1-based to 0-based. 2050 --beginIndex; 2051 int endIndex = beginIndex + length; 2052 return endIndex >= value.length() ? value.substring(beginIndex) : value 2053 .substring(beginIndex, endIndex); 2054 } 2055 2056 @FunctionName("MonthName") 2057 @Signature("MonthName(month, abbreviate)") 2058 @Description("Returns a string indicating the specified month.") 2059 public static String monthName(int month, boolean abbreviate) { 2060 // VB months are 1-based, Java months are 0-based 2061 --month; 2062 return (abbreviate ? getDateFormatSymbols().getShortMonths() 2063 : getDateFormatSymbols().getMonths())[month]; 2064 } 2065 2066 /** 2067 * Returns an instance of {@link DateFormatSymbols} for the current locale. 2068 * 2069 * <p> 2070 * Todo: inherit locale from connection. 2071 * 2072 * @return a DateFormatSymbols object 2073 */ 2074 private static DateFormatSymbols getDateFormatSymbols() { 2075 // We would use DataFormatSymbols.getInstance(), but it is only 2076 // available from JDK 1.6 onwards. 2077 return DATE_FORMAT_SYMBOLS; 2078 } 2079 2080 // public String rTrim$(String string) 2081 2082 @FunctionName("RTrim") 2083 @Signature("RTrim(string)") 2084 @Description( 2085 "Returns a Variant (String) containing a copy of a specified string " 2086 + "without trailing spaces.") 2087 public static String rTrim(String string) { 2088 int i = string.length() - 1; 2089 while (i >= 0) { 2090 if (string.charAt(i) > ' ') { 2091 break; 2092 } 2093 i--; 2094 } 2095 return string.substring(0, i + 1); 2096 } 2097 2098 @FunctionName("Replace") 2099 @Signature( 2100 "Replace(expression, find, replace[, start[, count[, compare]]])") 2101 @Description( 2102 "Returns a string in which a specified substring has been replaced " 2103 + "with another substring a specified number of times.") 2104 public static String replace( 2105 String expression, 2106 String find, 2107 String replace, 2108 int start, 2109 int count, 2110 int compare) 2111 { 2112 // compare is currently ignored 2113 Util.discard(compare); 2114 return _replace(expression, find, replace, start, count); 2115 } 2116 2117 @FunctionName("Replace") 2118 @Signature( 2119 "Replace(expression, find, replace[, start[, count[, compare]]])") 2120 @Description( 2121 "Returns a string in which a specified substring has been replaced " 2122 + "with another substring a specified number of times.") 2123 public static String replace( 2124 String expression, 2125 String find, 2126 String replace, 2127 int start, 2128 int count) 2129 { 2130 return _replace(expression, find, replace, start, count); 2131 } 2132 2133 @FunctionName("Replace") 2134 @Signature( 2135 "Replace(expression, find, replace[, start[, count[, compare]]])") 2136 @Description( 2137 "Returns a string in which a specified substring has been replaced " 2138 + "with another substring a specified number of times.") 2139 public static String replace( 2140 String expression, 2141 String find, 2142 String replace, 2143 int start) 2144 { 2145 return _replace(expression, find, replace, start, -1); 2146 } 2147 2148 @FunctionName("Replace") 2149 @Signature( 2150 "Replace(expression, find, replace[, start[, count[, compare]]])") 2151 @Description("") 2152 public static String replace( 2153 String expression, 2154 String find, 2155 String replace) 2156 { 2157 // compare is currently ignored 2158 return _replace(expression, find, replace, 1, -1); 2159 } 2160 2161 private static String _replace( 2162 String expression, 2163 String find, 2164 String replace, 2165 int start /* default 1 */, 2166 int count /* default -1 */) 2167 { 2168 final StringBuilder buf = new StringBuilder(expression); 2169 int i = 0; 2170 int pos = start - 1; 2171 while (true) { 2172 if (i++ == count) { 2173 break; 2174 } 2175 final int j = buf.indexOf(find, pos); 2176 if (j == -1) { 2177 break; 2178 } 2179 buf.replace(j, j + find.length(), replace); 2180 pos = j + replace.length(); 2181 } 2182 return buf.toString(); 2183 } 2184 2185 // public String right$(String string, int length) 2186 // public String rightB$(String string, int length) 2187 // public Object rightB(Object string, int length) 2188 2189 @FunctionName("Right") 2190 @Signature("Right(string, length)") 2191 @Description( 2192 "Returns a Variant (String) containing a specified number of " 2193 + "characters from the right side of a string.") 2194 public static String right(String string, int length) { 2195 final int stringLength = string.length(); 2196 if (length >= stringLength) { 2197 return string; 2198 } 2199 return string.substring(stringLength - length, stringLength); 2200 } 2201 2202 // public String space$(int number) 2203 2204 @FunctionName("Space") 2205 @Signature("Space(number)") 2206 @Description( 2207 "Returns a Variant (String) consisting of the specified number of " 2208 + "spaces.") 2209 public static String space(int number) { 2210 return string(number, ' '); 2211 } 2212 2213 // public Object split(String expression, Object delimiter, int limit /* 2214 // default -1 */, int compare /* default BinaryCompare */) 2215 2216 @FunctionName("StrComp") 2217 @Signature("StrComp(string1, string2[, compare])") 2218 @Description( 2219 "Returns a Variant (Integer) indicating the result of a string " 2220 + "comparison.") 2221 public static int strComp(String string1, String string2) { 2222 return strComp(string1, string2, 0); 2223 } 2224 2225 @FunctionName("StrComp") 2226 @Signature("StrComp(string1, string2[, compare])") 2227 @Description( 2228 "Returns a Variant (Integer) indicating the result of a string " 2229 + "comparison.") 2230 public static int strComp( 2231 String string1, 2232 String string2, 2233 int compare /* default BinaryCompare */) 2234 { 2235 // Note: compare is currently ignored 2236 // Wrapper already checked whether args are null 2237 assert string1 != null; 2238 assert string2 != null; 2239 return string1.compareTo(string2); 2240 } 2241 2242 // public Object strConv(Object string, StrConv conversion, int localeID) 2243 2244 @FunctionName("StrReverse") 2245 @Signature("StrReverse(string)") 2246 @Description( 2247 "Returns a string in which the character order of a specified string " 2248 + "is reversed.") 2249 public static String strReverse(String expression) { 2250 final char[] chars = expression.toCharArray(); 2251 for (int i = 0, j = chars.length - 1; i < j; i++, j--) { 2252 char c = chars[i]; 2253 chars[i] = chars[j]; 2254 chars[j] = c; 2255 } 2256 return new String(chars); 2257 } 2258 2259 // public String string$(int number, Object character) 2260 2261 @FunctionName("String") 2262 @Signature("String(number, character)") 2263 @Description("") 2264 public static String string(int number, char character) { 2265 if (character == 0) { 2266 return ""; 2267 } 2268 final char[] chars = new char[number]; 2269 Arrays.fill(chars, (char) (character % 256)); 2270 return new String(chars); 2271 } 2272 2273 // public String trim$(String string) 2274 2275 @FunctionName("Trim") 2276 @Signature("Trim(string)") 2277 @Description( 2278 "Returns a Variant (String) containing a copy of a specified string " 2279 + "without leading and trailing spaces.") 2280 public static String trim(String string) { 2281 // JDK has a method for trim, but not ltrim or rtrim 2282 return string.trim(); 2283 } 2284 2285 // ucase is already implemented in BuiltinFunTable... defer 2286 2287 // public String uCase$(String string) 2288 2289// @FunctionName("UCase") 2290// @Signature("UCase(string)") 2291// @Description("Returns a String that has been converted to uppercase.") 2292// public String uCase(String string) { 2293// return string.toUpperCase(); 2294// } 2295 2296 // TODO: should use connection's locale to determine first day of week, 2297 // not the JVM's default 2298 2299 @FunctionName("WeekdayName") 2300 @Signature("WeekdayName(weekday, abbreviate, firstdayofweek)") 2301 @Description("Returns a string indicating the specified day of the week.") 2302 public static String weekdayName( 2303 int weekday, 2304 boolean abbreviate, 2305 int firstDayOfWeek) 2306 { 2307 // Java and VB agree: SUNDAY = 1, ... SATURDAY = 7 2308 final Calendar calendar = Calendar.getInstance(); 2309 if (firstDayOfWeek == 0) { 2310 firstDayOfWeek = calendar.getFirstDayOfWeek(); 2311 } 2312 // compensate for start of week 2313 weekday += (firstDayOfWeek - 1); 2314 // bring into range 1..7 2315 weekday = (weekday - 1) % 7 + 1; 2316 if (weekday <= 0) { 2317 // negative numbers give negative modulo 2318 weekday += 7; 2319 } 2320 return 2321 (abbreviate 2322 ? getDateFormatSymbols().getShortWeekdays() 2323 : getDateFormatSymbols().getWeekdays()) 2324 [weekday]; 2325 } 2326 2327 // Misc 2328 2329 // public Object array(Object argList) 2330 // public String input$(int number, int fileNumber) 2331 // public String inputB$(int number, int fileNumber) 2332 // public Object inputB(int number, int fileNumber) 2333 // public Object input(int number, int fileNumber) 2334 // public void width(int fileNumber, int width) 2335 2336 // ~ Inner classes 2337 2338 private enum Interval { 2339 yyyy("Year", Calendar.YEAR), 2340 q("Quarter", -1), 2341 m("Month", Calendar.MONTH), 2342 y("Day of year", Calendar.DAY_OF_YEAR), 2343 d("Day", Calendar.DAY_OF_MONTH), 2344 w("Weekday", Calendar.DAY_OF_WEEK), 2345 ww("Week", Calendar.WEEK_OF_YEAR), 2346 h("Hour", Calendar.HOUR_OF_DAY), 2347 n("Minute", Calendar.MINUTE), 2348 s("Second", Calendar.SECOND); 2349 2350 private final int dateField; 2351 2352 Interval(String desc, int dateField) { 2353 Util.discard(desc); 2354 this.dateField = dateField; 2355 } 2356 2357 void add(Calendar calendar, int amount) { 2358 switch (this) { 2359 case q: 2360 calendar.add(Calendar.MONTH, amount * 3); 2361 break; 2362 default: 2363 calendar.add(dateField, amount); 2364 break; 2365 } 2366 } 2367 2368 Calendar floor(Calendar calendar) { 2369 Calendar calendar2 = Calendar.getInstance(); 2370 calendar2.setTime(calendar.getTime()); 2371 floorInplace(calendar2); 2372 return calendar2; 2373 } 2374 2375 private void floorInplace(Calendar calendar) { 2376 switch (this) { 2377 case yyyy: 2378 calendar.set(Calendar.DAY_OF_YEAR, 1); 2379 d.floorInplace(calendar); 2380 break; 2381 case q: 2382 int month = calendar.get(Calendar.MONTH); 2383 month -= month % 3; 2384 calendar.set(Calendar.MONTH, month); 2385 calendar.set(Calendar.DAY_OF_MONTH, 1); 2386 d.floorInplace(calendar); 2387 break; 2388 case m: 2389 calendar.set(Calendar.DAY_OF_MONTH, 1); 2390 d.floorInplace(calendar); 2391 break; 2392 case w: 2393 final int dow = calendar.get(Calendar.DAY_OF_WEEK); 2394 final int firstDayOfWeek = calendar.getFirstDayOfWeek(); 2395 if (dow == firstDayOfWeek) { 2396 // nothing to do 2397 } else if (dow > firstDayOfWeek) { 2398 final int roll = firstDayOfWeek - dow; 2399 assert roll < 0; 2400 calendar.roll(Calendar.DAY_OF_WEEK, roll); 2401 } else { 2402 final int roll = firstDayOfWeek - dow - 7; 2403 assert roll < 0; 2404 calendar.roll(Calendar.DAY_OF_WEEK, roll); 2405 } 2406 d.floorInplace(calendar); 2407 break; 2408 case y: 2409 case d: 2410 calendar.set(Calendar.HOUR_OF_DAY, 0); 2411 calendar.set(Calendar.MINUTE, 0); 2412 calendar.set(Calendar.SECOND, 0); 2413 calendar.set(Calendar.MILLISECOND, 0); 2414 break; 2415 case h: 2416 calendar.set(Calendar.MINUTE, 0); 2417 calendar.set(Calendar.SECOND, 0); 2418 calendar.set(Calendar.MILLISECOND, 0); 2419 break; 2420 case n: 2421 calendar.set(Calendar.SECOND, 0); 2422 calendar.set(Calendar.MILLISECOND, 0); 2423 break; 2424 case s: 2425 calendar.set(Calendar.MILLISECOND, 0); 2426 break; 2427 } 2428 } 2429 2430 int diff(Calendar calendar1, Calendar calendar2, int firstDayOfWeek) { 2431 switch (this) { 2432 case q: 2433 return m.diff(calendar1, calendar2, firstDayOfWeek) / 3; 2434 default: 2435 return floor(calendar1).get(dateField) 2436 - floor(calendar2).get(dateField); 2437 } 2438 } 2439 2440 int datePart(Calendar calendar) { 2441 switch (this) { 2442 case q: 2443 return (m.datePart(calendar) + 2) / 3; 2444 case m: 2445 return calendar.get(dateField) + 1; 2446 case w: 2447 int dayOfWeek = calendar.get(dateField); 2448 dayOfWeek -= (calendar.getFirstDayOfWeek() - 1); 2449 dayOfWeek = dayOfWeek % 7; 2450 if (dayOfWeek <= 0) { 2451 dayOfWeek += 7; 2452 } 2453 return dayOfWeek; 2454 default: 2455 return calendar.get(dateField); 2456 } 2457 } 2458 } 2459 2460 private enum FirstWeekOfYear { 2461 vbUseSystem( 2462 0, "Use the NLS API setting."), 2463 2464 vbFirstJan1( 2465 1, 2466 "Start with week in which January 1 occurs (default)."), 2467 2468 vbFirstFourDays( 2469 2, 2470 "Start with the first week that has at least four days in the new year."), 2471 2472 vbFirstFullWeek( 2473 3, 2474 "Start with first full week of the year."); 2475 2476 FirstWeekOfYear(int code, String desc) { 2477 assert code == ordinal(); 2478 assert desc != null; 2479 } 2480 2481 void apply(Calendar calendar) { 2482 switch (this) { 2483 case vbUseSystem: 2484 break; 2485 case vbFirstJan1: 2486 calendar.setMinimalDaysInFirstWeek(1); 2487 break; 2488 case vbFirstFourDays: 2489 calendar.setMinimalDaysInFirstWeek(4); 2490 break; 2491 case vbFirstFullWeek: 2492 calendar.setMinimalDaysInFirstWeek(7); 2493 break; 2494 } 2495 } 2496 } 2497} 2498 2499// End Vba.java