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