aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorJames Ahlborn <jtahlborn@yahoo.com>2018-11-08 21:23:48 +0000
committerJames Ahlborn <jtahlborn@yahoo.com>2018-11-08 21:23:48 +0000
commita2a57f1c3ca0efa319270ad3bcac00d269f611dc (patch)
tree57fcf0bf9fadcfe2cd9f495fda5833a05dc8e379
parent47de466737598cfdf8d8ec7bc11f624df3fed283 (diff)
downloadjackcess-a2a57f1c3ca0efa319270ad3bcac00d269f611dc.tar.gz
jackcess-a2a57f1c3ca0efa319270ad3bcac00d269f611dc.zip
add more numeric config
git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1218 f203690c-595d-4dc9-a70b-905162fa7fd2
-rw-r--r--src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java29
-rw-r--r--src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java25
-rw-r--r--src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java857
3 files changed, 453 insertions, 458 deletions
diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java b/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java
index 9ae09b3..f8fd51b 100644
--- a/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java
+++ b/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java
@@ -31,14 +31,39 @@ import java.util.Locale;
public class NumericConfig
{
public static final NumericConfig US_NUMERIC_CONFIG = new NumericConfig(
- Locale.US);
+ 2, true, false, 3, Locale.US);
+ private final int _numDecDigits;
+ private final boolean _incLeadingDigit;
+ private final boolean _useNegParens;
+ private final int _numGroupDigits;
private final DecimalFormatSymbols _symbols;
- public NumericConfig(Locale locale) {
+ public NumericConfig(int numDecDigits, boolean incLeadingDigit,
+ boolean useNegParens, int numGroupDigits, Locale locale) {
+ _numDecDigits = numDecDigits;
+ _incLeadingDigit = incLeadingDigit;
+ _useNegParens = useNegParens;
+ _numGroupDigits = numGroupDigits;
_symbols = DecimalFormatSymbols.getInstance(locale);
}
+ public int getNumDecimalDigits() {
+ return _numDecDigits;
+ }
+
+ public boolean includeLeadingDigit() {
+ return _incLeadingDigit;
+ }
+
+ public boolean useParensForNegatives() {
+ return _useNegParens;
+ }
+
+ public int getNumGroupingDigits() {
+ return _numGroupDigits;
+ }
+
public DecimalFormatSymbols getDecimalFormatSymbols() {
return _symbols;
}
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
index 687edf9..2f71718 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
@@ -27,6 +27,7 @@ import com.healthmarketscience.jackcess.expr.EvalContext;
import com.healthmarketscience.jackcess.expr.EvalException;
import com.healthmarketscience.jackcess.expr.Function;
import com.healthmarketscience.jackcess.expr.FunctionLookup;
+import com.healthmarketscience.jackcess.expr.NumericConfig;
import com.healthmarketscience.jackcess.expr.Value;
import com.healthmarketscience.jackcess.impl.DatabaseImpl;
import com.healthmarketscience.jackcess.impl.NumberFormatter;
@@ -287,10 +288,16 @@ public class DefaultFunctions
return ValueSupport.NULL_VAL;
}
- int numDecDigits = getOptionalIntParam(ctx, params, 1, 2, -1);
- boolean incLeadDigit = getOptionalTriStateBoolean(ctx, params, 2, true);
- boolean negParens = getOptionalTriStateBoolean(ctx, params, 3, false);
- boolean groupDigits = getOptionalTriStateBoolean(ctx, params, 4, true);
+ NumericConfig cfg = ctx.getNumericConfig();
+ int numDecDigits = getOptionalIntParam(
+ ctx, params, 1, cfg.getNumDecimalDigits(), -1);
+ boolean incLeadDigit = getOptionalTriStateBoolean(
+ ctx, params, 2, cfg.includeLeadingDigit());
+ boolean negParens = getOptionalTriStateBoolean(
+ ctx, params, 3, cfg.useParensForNegatives());
+ int numGroupDigits = cfg.getNumGroupingDigits();
+ boolean groupDigits = getOptionalTriStateBoolean(
+ ctx, params, 4, (numGroupDigits > 0));
StringBuilder fmt = new StringBuilder();
@@ -308,8 +315,14 @@ public class DefaultFunctions
// Note, DecimalFormat rounding mode uses HALF_EVEN by default
DecimalFormat df = new DecimalFormat(
- fmt.toString(), ctx.getNumericConfig().getDecimalFormatSymbols());
- df.setGroupingUsed(groupDigits);
+ fmt.toString(), cfg.getDecimalFormatSymbols());
+ if(groupDigits) {
+ df.setGroupingUsed(true);
+ df.setGroupingSize(numGroupDigits);
+ } else {
+ df.setGroupingUsed(false);
+ df.setGroupingSize(numGroupDigits);
+ }
return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx)));
}
diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
index b9b2657..d02aa15 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -38,23 +38,23 @@ public class DefaultFunctionsTest extends TestCase
public void testFuncs() throws Exception
{
- assertEquals("foo", eval("=IIf(10 > 1, \"foo\", \"bar\")"));
- assertEquals("bar", eval("=IIf(10 < 1, \"foo\", \"bar\")"));
- assertEquals(102, eval("=Asc(\"foo\")"));
- assertEquals(9786, eval("=AscW(\"\u263A\")"));
- assertEquals("f", eval("=Chr(102)"));
- assertEquals("\u263A", eval("=ChrW(9786)"));
- assertEquals("263A", eval("=Hex(9786)"));
-
- assertEquals("blah", eval("=Nz(\"blah\")"));
- assertEquals("", eval("=Nz(Null)"));
- assertEquals("blah", eval("=Nz(\"blah\",\"FOO\")"));
- assertEquals("FOO", eval("=Nz(Null,\"FOO\")"));
-
- assertEquals("23072", eval("=Oct(9786)"));
- assertEquals(" 9786", eval("=Str(9786)"));
- assertEquals("-42", eval("=Str(-42)"));
- assertEquals("-42", eval("=Str$(-42)"));
+ assertEval("foo", "=IIf(10 > 1, \"foo\", \"bar\")");
+ assertEval("bar", "=IIf(10 < 1, \"foo\", \"bar\")");
+ assertEval(102, "=Asc(\"foo\")");
+ assertEval(9786, "=AscW(\"\u263A\")");
+ assertEval("f", "=Chr(102)");
+ assertEval("\u263A", "=ChrW(9786)");
+ assertEval("263A", "=Hex(9786)");
+
+ assertEval("blah", "=Nz(\"blah\")");
+ assertEval("", "=Nz(Null)");
+ assertEval("blah", "=Nz(\"blah\",\"FOO\")");
+ assertEval("FOO", "=Nz(Null,\"FOO\")");
+
+ assertEval("23072", "=Oct(9786)");
+ assertEval(" 9786", "=Str(9786)");
+ assertEval("-42", "=Str(-42)");
+ assertEval("-42", "=Str$(-42)");
assertNull(eval("=Str(Null)"));
try {
@@ -64,123 +64,123 @@ public class DefaultFunctionsTest extends TestCase
// success
}
- assertEquals(-1, eval("=CBool(\"1\")"));
- assertEquals(13, eval("=CByte(\"13\")"));
- assertEquals(14, eval("=CByte(\"13.7\")"));
- assertEquals(new BigDecimal("57.1235"), eval("=CCur(\"57.12346\")"));
- assertEquals(new Double("57.12345"), eval("=CDbl(\"57.12345\")"));
- assertEquals(new BigDecimal("57.123456789"), eval("=CDec(\"57.123456789\")"));
- assertEquals(513, eval("=CInt(\"513\")"));
- assertEquals(514, eval("=CInt(\"513.7\")"));
- assertEquals(345513, eval("=CLng(\"345513\")"));
- assertEquals(345514, eval("=CLng(\"345513.7\")"));
+ assertEval(-1, "=CBool(\"1\")");
+ assertEval(13, "=CByte(\"13\")");
+ assertEval(14, "=CByte(\"13.7\")");
+ assertEval(new BigDecimal("57.1235"), "=CCur(\"57.12346\")");
+ assertEval(new Double("57.12345"), "=CDbl(\"57.12345\")");
+ assertEval(new BigDecimal("57.123456789"), "=CDec(\"57.123456789\")");
+ assertEval(513, "=CInt(\"513\")");
+ assertEval(514, "=CInt(\"513.7\")");
+ assertEval(345513, "=CLng(\"345513\")");
+ assertEval(345514, "=CLng(\"345513.7\")");
assertEquals(new Float("57.12345").doubleValue(),
eval("=CSng(\"57.12345\")"));
- assertEquals("9786", eval("=CStr(9786)"));
- assertEquals("-42", eval("=CStr(-42)"));
- assertEquals(new Date(1041483600000L), eval("=CDate('01/02/2003')"));
- assertEquals(new Date(1041508800000L), eval("=CDate('01/02/2003 7:00:00 AM')"));
- assertEquals(new Date(-1948781520000L), eval("=CDate(3013.45)"));
-
-
- assertEquals(-1, eval("=IsNull(Null)"));
- assertEquals(0, eval("=IsNull(13)"));
- assertEquals(-1, eval("=IsDate(#01/02/2003#)"));
- assertEquals(0, eval("=IsDate('foo')"));
- assertEquals(0, eval("=IsDate('200')"));
-
- assertEquals(0, eval("=IsNumeric(Null)"));
- assertEquals(0, eval("=IsNumeric('foo')"));
- assertEquals(0, eval("=IsNumeric(#01/02/2003#)"));
- assertEquals(0, eval("=IsNumeric('01/02/2003')"));
- assertEquals(-1, eval("=IsNumeric(37)"));
- assertEquals(-1, eval("=IsNumeric(' 37 ')"));
- assertEquals(-1, eval("=IsNumeric(' -37.5e2 ')"));
- assertEquals(-1, eval("=IsNumeric(' &H37 ')"));
- assertEquals(0, eval("=IsNumeric(' &H37foo ')"));
- assertEquals(0, eval("=IsNumeric(' &o39 ')"));
- assertEquals(-1, eval("=IsNumeric(' &o36 ')"));
- assertEquals(0, eval("=IsNumeric(' &o36.1 ')"));
-
- assertEquals(1, eval("=VarType(Null)"));
- assertEquals(8, eval("=VarType('blah')"));
- assertEquals(7, eval("=VarType(#01/02/2003#)"));
- assertEquals(3, eval("=VarType(42)"));
- assertEquals(5, eval("=VarType(CDbl(42))"));
- assertEquals(14, eval("=VarType(42.3)"));
-
- assertEquals("Null", eval("=TypeName(Null)"));
- assertEquals("String", eval("=TypeName('blah')"));
- assertEquals("Date", eval("=TypeName(#01/02/2003#)"));
- assertEquals("Long", eval("=TypeName(42)"));
- assertEquals("Double", eval("=TypeName(CDbl(42))"));
- assertEquals("Decimal", eval("=TypeName(42.3)"));
-
- assertEquals(2, eval("=InStr('AFOOBAR', 'FOO')"));
- assertEquals(2, eval("=InStr('AFOOBAR', 'foo')"));
- assertEquals(2, eval("=InStr(1, 'AFOOBAR', 'foo')"));
- assertEquals(0, eval("=InStr(1, 'AFOOBAR', 'foo', 0)"));
- assertEquals(2, eval("=InStr(1, 'AFOOBAR', 'foo', 1)"));
- assertEquals(2, eval("=InStr(1, 'AFOOBAR', 'FOO', 0)"));
- assertEquals(2, eval("=InStr(2, 'AFOOBAR', 'FOO')"));
- assertEquals(0, eval("=InStr(3, 'AFOOBAR', 'FOO')"));
- assertEquals(0, eval("=InStr(17, 'AFOOBAR', 'FOO')"));
- assertEquals(2, eval("=InStr(1, 'AFOOBARFOOBAR', 'FOO')"));
- assertEquals(8, eval("=InStr(3, 'AFOOBARFOOBAR', 'FOO')"));
+ assertEval("9786", "=CStr(9786)");
+ assertEval("-42", "=CStr(-42)");
+ assertEval(new Date(1041483600000L), "=CDate('01/02/2003')");
+ assertEval(new Date(1041508800000L), "=CDate('01/02/2003 7:00:00 AM')");
+ assertEval(new Date(-1948781520000L), "=CDate(3013.45)");
+
+
+ assertEval(-1, "=IsNull(Null)");
+ assertEval(0, "=IsNull(13)");
+ assertEval(-1, "=IsDate(#01/02/2003#)");
+ assertEval(0, "=IsDate('foo')");
+ assertEval(0, "=IsDate('200')");
+
+ assertEval(0, "=IsNumeric(Null)");
+ assertEval(0, "=IsNumeric('foo')");
+ assertEval(0, "=IsNumeric(#01/02/2003#)");
+ assertEval(0, "=IsNumeric('01/02/2003')");
+ assertEval(-1, "=IsNumeric(37)");
+ assertEval(-1, "=IsNumeric(' 37 ')");
+ assertEval(-1, "=IsNumeric(' -37.5e2 ')");
+ assertEval(-1, "=IsNumeric(' &H37 ')");
+ assertEval(0, "=IsNumeric(' &H37foo ')");
+ assertEval(0, "=IsNumeric(' &o39 ')");
+ assertEval(-1, "=IsNumeric(' &o36 ')");
+ assertEval(0, "=IsNumeric(' &o36.1 ')");
+
+ assertEval(1, "=VarType(Null)");
+ assertEval(8, "=VarType('blah')");
+ assertEval(7, "=VarType(#01/02/2003#)");
+ assertEval(3, "=VarType(42)");
+ assertEval(5, "=VarType(CDbl(42))");
+ assertEval(14, "=VarType(42.3)");
+
+ assertEval("Null", "=TypeName(Null)");
+ assertEval("String", "=TypeName('blah')");
+ assertEval("Date", "=TypeName(#01/02/2003#)");
+ assertEval("Long", "=TypeName(42)");
+ assertEval("Double", "=TypeName(CDbl(42))");
+ assertEval("Decimal", "=TypeName(42.3)");
+
+ assertEval(2, "=InStr('AFOOBAR', 'FOO')");
+ assertEval(2, "=InStr('AFOOBAR', 'foo')");
+ assertEval(2, "=InStr(1, 'AFOOBAR', 'foo')");
+ assertEval(0, "=InStr(1, 'AFOOBAR', 'foo', 0)");
+ assertEval(2, "=InStr(1, 'AFOOBAR', 'foo', 1)");
+ assertEval(2, "=InStr(1, 'AFOOBAR', 'FOO', 0)");
+ assertEval(2, "=InStr(2, 'AFOOBAR', 'FOO')");
+ assertEval(0, "=InStr(3, 'AFOOBAR', 'FOO')");
+ assertEval(0, "=InStr(17, 'AFOOBAR', 'FOO')");
+ assertEval(2, "=InStr(1, 'AFOOBARFOOBAR', 'FOO')");
+ assertEval(8, "=InStr(3, 'AFOOBARFOOBAR', 'FOO')");
assertNull(eval("=InStr(3, Null, 'FOO')"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'FOO')"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'foo')"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'foo', -1)"));
- assertEquals(0, eval("=InStrRev('AFOOBAR', 'foo', -1, 0)"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'foo', -1, 1)"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'FOO', -1, 0)"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'FOO', 4)"));
- assertEquals(0, eval("=InStrRev('AFOOBAR', 'FOO', 3)"));
- assertEquals(2, eval("=InStrRev('AFOOBAR', 'FOO', 17)"));
- assertEquals(2, eval("=InStrRev('AFOOBARFOOBAR', 'FOO', 9)"));
- assertEquals(8, eval("=InStrRev('AFOOBARFOOBAR', 'FOO', 10)"));
+ assertEval(2, "=InStrRev('AFOOBAR', 'FOO')");
+ assertEval(2, "=InStrRev('AFOOBAR', 'foo')");
+ assertEval(2, "=InStrRev('AFOOBAR', 'foo', -1)");
+ assertEval(0, "=InStrRev('AFOOBAR', 'foo', -1, 0)");
+ assertEval(2, "=InStrRev('AFOOBAR', 'foo', -1, 1)");
+ assertEval(2, "=InStrRev('AFOOBAR', 'FOO', -1, 0)");
+ assertEval(2, "=InStrRev('AFOOBAR', 'FOO', 4)");
+ assertEval(0, "=InStrRev('AFOOBAR', 'FOO', 3)");
+ assertEval(2, "=InStrRev('AFOOBAR', 'FOO', 17)");
+ assertEval(2, "=InStrRev('AFOOBARFOOBAR', 'FOO', 9)");
+ assertEval(8, "=InStrRev('AFOOBARFOOBAR', 'FOO', 10)");
assertNull(eval("=InStrRev(Null, 'FOO', 3)"));
- assertEquals("FOOO", eval("=UCase(\"fOoO\")"));
- assertEquals("fooo", eval("=LCase(\"fOoO\")"));
+ assertEval("FOOO", "=UCase(\"fOoO\")");
+ assertEval("fooo", "=LCase(\"fOoO\")");
- assertEquals("bl", eval("=Left(\"blah\", 2)"));
- assertEquals("", eval("=Left(\"blah\", 0)"));
- assertEquals("blah", eval("=Left(\"blah\", 17)"));
- assertEquals("la", eval("=Mid(\"blah\", 2, 2)"));
+ assertEval("bl", "=Left(\"blah\", 2)");
+ assertEval("", "=Left(\"blah\", 0)");
+ assertEval("blah", "=Left(\"blah\", 17)");
+ assertEval("la", "=Mid(\"blah\", 2, 2)");
- assertEquals("ah", eval("=Right(\"blah\", 2)"));
- assertEquals("", eval("=Right(\"blah\", 0)"));
- assertEquals("blah", eval("=Right(\"blah\", 17)"));
+ assertEval("ah", "=Right(\"blah\", 2)");
+ assertEval("", "=Right(\"blah\", 0)");
+ assertEval("blah", "=Right(\"blah\", 17)");
- assertEquals("blah ", eval("=LTrim(\" blah \")"));
- assertEquals(" blah", eval("=RTrim(\" blah \")"));
- assertEquals("blah", eval("=Trim(\" blah \")"));
- assertEquals(" ", eval("=Space(3)"));
- assertEquals("ddd", eval("=String(3,'d')"));
+ assertEval("blah ", "=LTrim(\" blah \")");
+ assertEval(" blah", "=RTrim(\" blah \")");
+ assertEval("blah", "=Trim(\" blah \")");
+ assertEval(" ", "=Space(3)");
+ assertEval("ddd", "=String(3,'d')");
- assertEquals(1, eval("=StrComp('FOO', 'bar')"));
- assertEquals(-1, eval("=StrComp('bar', 'FOO')"));
- assertEquals(0, eval("=StrComp('FOO', 'foo')"));
- assertEquals(-1, eval("=StrComp('FOO', 'bar', 0)"));
- assertEquals(1, eval("=StrComp('bar', 'FOO', 0)"));
- assertEquals(-1, eval("=StrComp('FOO', 'foo', 0)"));
+ assertEval(1, "=StrComp('FOO', 'bar')");
+ assertEval(-1, "=StrComp('bar', 'FOO')");
+ assertEval(0, "=StrComp('FOO', 'foo')");
+ assertEval(-1, "=StrComp('FOO', 'bar', 0)");
+ assertEval(1, "=StrComp('bar', 'FOO', 0)");
+ assertEval(-1, "=StrComp('FOO', 'foo', 0)");
- assertEquals("FOO", eval("=StrConv('foo', 1)"));
- assertEquals("foo", eval("=StrConv('foo', 2)"));
- assertEquals("foo", eval("=StrConv('FOO', 2)"));
- assertEquals("Foo Bar", eval("=StrConv('FOO bar', 3)"));
+ assertEval("FOO", "=StrConv('foo', 1)");
+ assertEval("foo", "=StrConv('foo', 2)");
+ assertEval("foo", "=StrConv('FOO', 2)");
+ assertEval("Foo Bar", "=StrConv('FOO bar', 3)");
- assertEquals("halb", eval("=StrReverse('blah')"));
+ assertEval("halb", "=StrReverse('blah')");
- assertEquals("foo", eval("=Choose(1,'foo','bar','blah')"));
- assertEquals(null, eval("=Choose(-1,'foo','bar','blah')"));
- assertEquals("blah", eval("=Choose(3,'foo','bar','blah')"));
+ assertEval("foo", "=Choose(1,'foo','bar','blah')");
+ assertEval(null, "=Choose(-1,'foo','bar','blah')");
+ assertEval("blah", "=Choose(3,'foo','bar','blah')");
- assertEquals(null, eval("=Switch(False,'foo', False, 'bar', False, 'blah')"));
- assertEquals("bar", eval("=Switch(False,'foo', True, 'bar', True, 'blah')"));
- assertEquals("blah", eval("=Switch(False,'foo', False, 'bar', True, 'blah')"));
+ assertEval(null, "=Switch(False,'foo', False, 'bar', False, 'blah')");
+ assertEval("bar", "=Switch(False,'foo', True, 'bar', True, 'blah')");
+ assertEval("blah", "=Switch(False,'foo', False, 'bar', True, 'blah')");
try {
eval("=StrReverse('blah', 1)");
@@ -196,363 +196,320 @@ public class DefaultFunctionsTest extends TestCase
assertTrue(e.getMessage().contains("Invalid function call"));
}
- assertEquals(1615198d, eval("=Val(' 1615 198th Street N.E.')"));
- assertEquals(-1d, eval("=Val(' &HFFFFwhatever')"));
- assertEquals(131071d, eval("=Val(' &H1FFFFwhatever')"));
- assertEquals(-1d, eval("=Val(' &HFFFFFFFFwhatever')"));
- assertEquals(291d, eval("=Val(' &H123whatever')"));
- assertEquals(83d, eval("=Val(' &O123whatever')"));
- assertEquals(1.23d, eval("=Val(' 1 2 3 e -2 whatever')"));
- assertEquals(0d, eval("=Val(' whatever123 ')"));
- assertEquals(0d, eval("=Val('')"));
-
- assertEquals("faa", eval("=Replace('foo','o','a')"));
- assertEquals("faa", eval("=Replace('fOo','o','a')"));
- assertEquals("aa", eval("=Replace('foo','o','a',2)"));
- assertEquals("oo", eval("=Replace('foo','o','a',2,0)"));
- assertEquals("", eval("=Replace('foo','o','a',4)"));
- assertEquals("foo", eval("=Replace('foo','','a')"));
- assertEquals("o", eval("=Replace('foo','','a',3)"));
- assertEquals("fahhabahhaahha", eval("=Replace('fooboooo','OO','ahha')"));
- assertEquals("fahhaboooo", eval("=Replace('fooboooo','OO','ahha',1,1)"));
- assertEquals("fooboooo", eval("=Replace('fooboooo','OO','ahha',1,1,0)"));
- assertEquals("ahhabahhaahha", eval("=Replace('fooboooo','OO','ahha',2)"));
- assertEquals("obahhaahha", eval("=Replace('fooboooo','OO','ahha',3)"));
- assertEquals("fb", eval("=Replace('fooboooo','OO','')"));
- assertEquals("", eval("=Replace('','o','a')"));
- assertEquals("foo", eval("=Replace('foo','foobar','a')"));
+ assertEval(1615198d, "=Val(' 1615 198th Street N.E.')");
+ assertEval(-1d, "=Val(' &HFFFFwhatever')");
+ assertEval(131071d, "=Val(' &H1FFFFwhatever')");
+ assertEval(-1d, "=Val(' &HFFFFFFFFwhatever')");
+ assertEval(291d, "=Val(' &H123whatever')");
+ assertEval(83d, "=Val(' &O123whatever')");
+ assertEval(1.23d, "=Val(' 1 2 3 e -2 whatever')");
+ assertEval(0d, "=Val(' whatever123 ')");
+ assertEval(0d, "=Val('')");
+
+ assertEval("faa", "=Replace('foo','o','a')");
+ assertEval("faa", "=Replace('fOo','o','a')");
+ assertEval("aa", "=Replace('foo','o','a',2)");
+ assertEval("oo", "=Replace('foo','o','a',2,0)");
+ assertEval("", "=Replace('foo','o','a',4)");
+ assertEval("foo", "=Replace('foo','','a')");
+ assertEval("o", "=Replace('foo','','a',3)");
+ assertEval("fahhabahhaahha", "=Replace('fooboooo','OO','ahha')");
+ assertEval("fahhaboooo", "=Replace('fooboooo','OO','ahha',1,1)");
+ assertEval("fooboooo", "=Replace('fooboooo','OO','ahha',1,1,0)");
+ assertEval("ahhabahhaahha", "=Replace('fooboooo','OO','ahha',2)");
+ assertEval("obahhaahha", "=Replace('fooboooo','OO','ahha',3)");
+ assertEval("fb", "=Replace('fooboooo','OO','')");
+ assertEval("", "=Replace('','o','a')");
+ assertEval("foo", "=Replace('foo','foobar','a')");
+
+ assertEval("12,345.00", "=FormatNumber(12345)");
+ assertEval("0.12", "=FormatNumber(0.12345)");
+ assertEval("12.34", "=FormatNumber(12.345)");
+ assertEval("12,345.000", "=FormatNumber(12345,3)");
+ assertEval("0.123", "=FormatNumber(0.12345,3)");
+ assertEval("12.345", "=FormatNumber(12.345,3)");
+ assertEval("12,345", "=FormatNumber(12345,0)");
+ assertEval("0", "=FormatNumber(0.12345,0)");
+ assertEval("12", "=FormatNumber(12.345,0)");
}
public void testNumberFuncs() throws Exception
{
- assertEquals(1, eval("=Abs(1)"));
- assertEquals(1, eval("=Abs(-1)"));
- assertEquals(toBD(1.1), eval("=Abs(-1.1)"));
-
- assertEquals(Math.atan(0.2), eval("=Atan(0.2)"));
- assertEquals(Math.sin(0.2), eval("=Sin(0.2)"));
- assertEquals(Math.tan(0.2), eval("=Tan(0.2)"));
- assertEquals(Math.cos(0.2), eval("=Cos(0.2)"));
-
- assertEquals(Math.exp(0.2), eval("=Exp(0.2)"));
- assertEquals(Math.log(0.2), eval("=Log(0.2)"));
- assertEquals(Math.sqrt(4.3), eval("=Sqr(4.3)"));
-
- assertEquals(3, eval("=Fix(3.5)"));
- assertEquals(4, eval("=Fix(4)"));
- assertEquals(-3, eval("=Fix(-3.5)"));
- assertEquals(-4, eval("=Fix(-4)"));
-
- assertEquals(1, eval("=Sgn(3.5)"));
- assertEquals(1, eval("=Sgn(4)"));
- assertEquals(-1, eval("=Sgn(-3.5)"));
- assertEquals(-1, eval("=Sgn(-4)"));
-
- assertEquals(3, eval("=Int(3.5)"));
- assertEquals(4, eval("=Int(4)"));
- assertEquals(-4, eval("=Int(-3.5)"));
- assertEquals(-4, eval("=Int(-4)"));
-
- assertEquals(toBD(4), eval("=Round(3.7)"));
- assertEquals(4, eval("=Round(4)"));
- assertEquals(toBD(-4), eval("=Round(-3.7)"));
- assertEquals(-4, eval("=Round(-4)"));
-
- assertEquals(toBD(3.73), eval("=Round(3.7345, 2)"));
- assertEquals(4, eval("=Round(4, 2)"));
- assertEquals(toBD(-3.73), eval("=Round(-3.7345, 2)"));
- assertEquals(-4, eval("=Round(-4, 2)"));
+ assertEval(1, "=Abs(1)");
+ assertEval(1, "=Abs(-1)");
+ assertEval(toBD(1.1), "=Abs(-1.1)");
+
+ assertEval(Math.atan(0.2), "=Atan(0.2)");
+ assertEval(Math.sin(0.2), "=Sin(0.2)");
+ assertEval(Math.tan(0.2), "=Tan(0.2)");
+ assertEval(Math.cos(0.2), "=Cos(0.2)");
+
+ assertEval(Math.exp(0.2), "=Exp(0.2)");
+ assertEval(Math.log(0.2), "=Log(0.2)");
+ assertEval(Math.sqrt(4.3), "=Sqr(4.3)");
+
+ assertEval(3, "=Fix(3.5)");
+ assertEval(4, "=Fix(4)");
+ assertEval(-3, "=Fix(-3.5)");
+ assertEval(-4, "=Fix(-4)");
+
+ assertEval(1, "=Sgn(3.5)");
+ assertEval(1, "=Sgn(4)");
+ assertEval(-1, "=Sgn(-3.5)");
+ assertEval(-1, "=Sgn(-4)");
+
+ assertEval(3, "=Int(3.5)");
+ assertEval(4, "=Int(4)");
+ assertEval(-4, "=Int(-3.5)");
+ assertEval(-4, "=Int(-4)");
+
+ assertEval(toBD(4), "=Round(3.7)");
+ assertEval(4, "=Round(4)");
+ assertEval(toBD(-4), "=Round(-3.7)");
+ assertEval(-4, "=Round(-4)");
+
+ assertEval(toBD(3.73), "=Round(3.7345, 2)");
+ assertEval(4, "=Round(4, 2)");
+ assertEval(toBD(-3.73), "=Round(-3.7345, 2)");
+ assertEval(-4, "=Round(-4, 2)");
}
public void testDateFuncs() throws Exception
{
- assertEquals("1/2/2003", eval("=CStr(DateValue(#01/02/2003 7:00:00 AM#))"));
- assertEquals("7:00:00 AM", eval("=CStr(TimeValue(#01/02/2003 7:00:00 AM#))"));
+ assertEval("1/2/2003", "=CStr(DateValue(#01/02/2003 7:00:00 AM#))");
+ assertEval("7:00:00 AM", "=CStr(TimeValue(#01/02/2003 7:00:00 AM#))");
- assertEquals("1:10:00 PM", eval("=CStr(#13:10:00#)"));
+ assertEval("1:10:00 PM", "=CStr(#13:10:00#)");
- assertEquals(2003, eval("=Year(#01/02/2003 7:00:00 AM#)"));
- assertEquals(1, eval("=Month(#01/02/2003 7:00:00 AM#)"));
- assertEquals(2, eval("=Day(#01/02/2003 7:00:00 AM#)"));
+ assertEval(2003, "=Year(#01/02/2003 7:00:00 AM#)");
+ assertEval(1, "=Month(#01/02/2003 7:00:00 AM#)");
+ assertEval(2, "=Day(#01/02/2003 7:00:00 AM#)");
- assertEquals(2003, eval("=Year('01/02/2003 7:00:00 AM')"));
- assertEquals(1899, eval("=Year(#7:00:00 AM#)"));
+ assertEval(2003, "=Year('01/02/2003 7:00:00 AM')");
+ assertEval(1899, "=Year(#7:00:00 AM#)");
assertEquals(Calendar.getInstance().get(Calendar.YEAR),
eval("=Year('01/02 7:00:00 AM')"));
- assertEquals("January", eval("=MonthName(1)"));
- assertEquals("Feb", eval("=MonthName(2,True)"));
- assertEquals("March", eval("=MonthName(3,False)"));
+ assertEval("January", "=MonthName(1)");
+ assertEval("Feb", "=MonthName(2,True)");
+ assertEval("March", "=MonthName(3,False)");
- assertEquals(7, eval("=Hour(#01/02/2003 7:10:27 AM#)"));
- assertEquals(19, eval("=Hour(#01/02/2003 7:10:27 PM#)"));
- assertEquals(10, eval("=Minute(#01/02/2003 7:10:27 AM#)"));
- assertEquals(27, eval("=Second(#01/02/2003 7:10:27 AM#)"));
+ assertEval(7, "=Hour(#01/02/2003 7:10:27 AM#)");
+ assertEval(19, "=Hour(#01/02/2003 7:10:27 PM#)");
+ assertEval(10, "=Minute(#01/02/2003 7:10:27 AM#)");
+ assertEval(27, "=Second(#01/02/2003 7:10:27 AM#)");
- assertEquals(7, eval("=Weekday(#11/22/2003#)"));
- assertEquals(3, eval("=Weekday(#11/22/2003#, 5)"));
- assertEquals(1, eval("=Weekday(#11/22/2003#, 7)"));
+ assertEval(7, "=Weekday(#11/22/2003#)");
+ assertEval(3, "=Weekday(#11/22/2003#, 5)");
+ assertEval(1, "=Weekday(#11/22/2003#, 7)");
- assertEquals("Sunday", eval("=WeekdayName(1)"));
- assertEquals("Sun", eval("=WeekdayName(1,True)"));
- assertEquals("Tuesday", eval("=WeekdayName(1,False,3)"));
- assertEquals("Thu", eval("=WeekdayName(3,True,3)"));
+ assertEval("Sunday", "=WeekdayName(1)");
+ assertEval("Sun", "=WeekdayName(1,True)");
+ assertEval("Tuesday", "=WeekdayName(1,False,3)");
+ assertEval("Thu", "=WeekdayName(3,True,3)");
assertTrue(((String)eval("=CStr(Date())"))
.matches("\\d{1,2}/\\d{1,2}/\\d{4}"));
assertTrue(((String)eval("=CStr(Time())"))
.matches("\\d{1,2}:\\d{2}:\\d{2} (AM|PM)"));
- assertEquals("3:57:34 AM", eval("=CStr(TimeSerial(3,57,34))"));
- assertEquals("3:57:34 PM", eval("=CStr(TimeSerial(15,57,34))"));
- assertEquals("5:45:00 AM", eval("=CStr(TimeSerial(6,-15,0))"));
- assertEquals("12:00:00 AM", eval("=CStr(TimeSerial(0,0,0))"));
- assertEquals("2:00:00 PM", eval("=CStr(TimeSerial(-10,0,0))"));
- assertEquals("6:00:00 AM", eval("=CStr(TimeSerial(30,0,0))"));
-
- assertEquals("2/12/1969", eval("=CStr(DateSerial(69,2,12))"));
- assertEquals("2/12/2010", eval("=CStr(DateSerial(10,2,12))"));
- assertEquals("7/12/2013", eval("=CStr(DateSerial(2014,-5,12))"));
- assertEquals("8/7/2013", eval("=CStr(DateSerial(2014,-5,38))"));
-
- assertEquals(1, eval("=DatePart('ww',#01/03/2018#)"));
- assertEquals(2, eval("=DatePart('ww',#01/03/2018#,4)"));
- assertEquals(1, eval("=DatePart('ww',#01/03/2018#,5)"));
- assertEquals(1, eval("=DatePart('ww',#01/03/2018#,4,3)"));
- assertEquals(52, eval("=DatePart('ww',#01/03/2018#,5,3)"));
- assertEquals(1, eval("=DatePart('ww',#01/03/2018#,4,2)"));
- assertEquals(53, eval("=DatePart('ww',#01/03/2018#,5,2)"));
- assertEquals(2003, eval("=DatePart('yyyy',#11/22/2003 5:45:13 AM#)"));
- assertEquals(4, eval("=DatePart('q',#11/22/2003 5:45:13 AM#)"));
- assertEquals(11, eval("=DatePart('m',#11/22/2003 5:45:13 AM#)"));
- assertEquals(326, eval("=DatePart('y',#11/22/2003 5:45:13 AM#)"));
- assertEquals(22, eval("=DatePart('d',#11/22/2003 5:45:13 AM#)"));
- assertEquals(7, eval("=DatePart('w',#11/22/2003 5:45:13 AM#)"));
- assertEquals(3, eval("=DatePart('w',#11/22/2003 5:45:13 AM#, 5)"));
- assertEquals(5, eval("=DatePart('h',#11/22/2003 5:45:13 AM#)"));
- assertEquals(45, eval("=DatePart('n',#11/22/2003 5:45:13 AM#)"));
- assertEquals(13, eval("=DatePart('s',#11/22/2003 5:45:13 AM#)"));
-
- assertEquals("11/22/2005 5:45:13 AM", eval("CStr(DateAdd('yyyy',2,#11/22/2003 5:45:13 AM#))"));
- assertEquals("2/22/2004 5:45:13 AM", eval("CStr(DateAdd('q',1,#11/22/2003 5:45:13 AM#))"));
- assertEquals("1/22/2004 5:45:13 AM", eval("CStr(DateAdd('m',2,#11/22/2003 5:45:13 AM#))"));
- assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('d',20,#11/22/2003 5:45:13 AM#))"));
- assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('w',20,#11/22/2003 5:45:13 AM#))"));
- assertEquals("12/12/2003 5:45:13 AM", eval("CStr(DateAdd('y',20,#11/22/2003 5:45:13 AM#))"));
- assertEquals("12/27/2003 5:45:13 AM", eval("CStr(DateAdd('ww',5,#11/22/2003 5:45:13 AM#))"));
- assertEquals("11/22/2003 3:45:13 PM", eval("CStr(DateAdd('h',10,#11/22/2003 5:45:13 AM#))"));
- assertEquals("11/22/2003 6:19:13 AM", eval("CStr(DateAdd('n',34,#11/22/2003 5:45:13 AM#))"));
- assertEquals("11/22/2003 5:46:27 AM", eval("CStr(DateAdd('s',74,#11/22/2003 5:45:13 AM#))"));
-
- assertEquals("12/12/2003", eval("CStr(DateAdd('d',20,#11/22/2003#))"));
- assertEquals("11/22/2003 10:00:00 AM", eval("CStr(DateAdd('h',10,#11/22/2003#))"));
- assertEquals("11/23/2003", eval("CStr(DateAdd('h',24,#11/22/2003#))"));
- assertEquals("3:45:13 PM", eval("CStr(DateAdd('h',10,#5:45:13 AM#))"));
- assertEquals("12/31/1899 11:45:13 AM", eval("CStr(DateAdd('h',30,#5:45:13 AM#))"));
-
- assertEquals(0, eval("=DateDiff('yyyy',#10/22/2003#,#11/22/2003#)"));
- assertEquals(4, eval("=DateDiff('yyyy',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-4, eval("=DateDiff('yyyy',#11/22/2007#,#10/22/2003#)"));
-
- assertEquals(0, eval("=DateDiff('q',#10/22/2003#,#11/22/2003#)"));
- assertEquals(3, eval("=DateDiff('q',#03/01/2003#,#11/22/2003#)"));
- assertEquals(16, eval("=DateDiff('q',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-13, eval("=DateDiff('q',#03/22/2007#,#10/22/2003#)"));
-
- assertEquals(1, eval("=DateDiff('m',#10/22/2003#,#11/01/2003#)"));
- assertEquals(8, eval("=DateDiff('m',#03/22/2003#,#11/01/2003#)"));
- assertEquals(49, eval("=DateDiff('m',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-41, eval("=DateDiff('m',#03/22/2007#,#10/01/2003#)"));
-
- assertEquals(10, eval("=DateDiff('d','10/22','11/01')"));
- assertEquals(0, eval("=DateDiff('y',#1:37:00 AM#,#2:15:00 AM#)"));
- assertEquals(10, eval("=DateDiff('d',#10/22/2003#,#11/01/2003#)"));
- assertEquals(1, eval("=DateDiff('d',#10/22/2003 11:00:00 PM#,#10/23/2003 1:00:00 AM#)"));
- assertEquals(224, eval("=DateDiff('d',#03/22/2003#,#11/01/2003#)"));
- assertEquals(1492, eval("=DateDiff('y',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-1268, eval("=DateDiff('d',#03/22/2007#,#10/01/2003#)"));
- assertEquals(366, eval("=DateDiff('d',#1/1/2000#,#1/1/2001#)"));
- assertEquals(365, eval("=DateDiff('d',#1/1/2001#,#1/1/2002#)"));
-
- assertEquals(0, eval("=DateDiff('w',#11/3/2018#,#11/04/2018#)"));
- assertEquals(1, eval("=DateDiff('w',#11/3/2018#,#11/10/2018#)"));
- assertEquals(0, eval("=DateDiff('w',#12/31/2017#,#1/1/2018#)"));
- assertEquals(32, eval("=DateDiff('w',#03/22/2003#,#11/01/2003#)"));
- assertEquals(213, eval("=DateDiff('w',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-181, eval("=DateDiff('w',#03/22/2007#,#10/01/2003#)"));
-
- assertEquals(1, eval("=DateDiff('ww',#11/3/2018#,#11/04/2018#)"));
- assertEquals(1, eval("=DateDiff('ww',#11/3/2018#,#11/10/2018#)"));
- assertEquals(0, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#)"));
- assertEquals(1, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#,2)"));
- assertEquals(0, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#,1,3)"));
- assertEquals(53, eval("=DateDiff('ww',#1/1/2000#,#1/1/2001#)"));
- assertEquals(32, eval("=DateDiff('ww',#03/22/2003#,#11/01/2003#)"));
- assertEquals(213, eval("=DateDiff('ww',#10/22/2003#,#11/22/2007#)"));
- assertEquals(-181, eval("=DateDiff('ww',#03/22/2007#,#10/01/2003#)"));
-
- assertEquals(1, eval("=DateDiff('h',#1:37:00 AM#,#2:15:00 AM#)"));
- assertEquals(13, eval("=DateDiff('h',#1:37:00 AM#,#2:15:00 PM#)"));
- assertEquals(1, eval("=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)"));
- assertEquals(13, eval("=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 PM#)"));
- assertEquals(24, eval("=DateDiff('h',#11/3/2018#,#11/4/2018#)"));
- assertEquals(5641, eval("=DateDiff('h',#3/13/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)"));
- assertEquals(23161, eval("=DateDiff('h',#3/13/2016 1:37:00 AM#,#11/3/2018 2:15:00 AM#)"));
- assertEquals(-23173, eval("=DateDiff('h',#11/3/2018 2:15:00 PM#,#3/13/2016 1:37:00 AM#)"));
-
- assertEquals(1, eval("=DateDiff('n',#1:37:59 AM#,#1:38:00 AM#)"));
- assertEquals(758, eval("=DateDiff('n',#1:37:30 AM#,#2:15:13 PM#)"));
- assertEquals(1, eval("=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)"));
- assertEquals(758, eval("=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 2:15:00 PM#)"));
- assertEquals(1440, eval("=DateDiff('n',#11/3/2018#,#11/4/2018#)"));
- assertEquals(338438, eval("=DateDiff('n',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)"));
- assertEquals(1389638, eval("=DateDiff('n',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)"));
- assertEquals(-1390358, eval("=DateDiff('n',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)"));
-
- assertEquals(1, eval("=DateDiff('s',#1:37:59 AM#,#1:38:00 AM#)"));
- assertEquals(35, eval("=DateDiff('s',#1:37:10 AM#,#1:37:45 AM#)"));
- assertEquals(45463, eval("=DateDiff('s',#1:37:30 AM#,#2:15:13 PM#)"));
- assertEquals(1, eval("=DateDiff('s',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)"));
- assertEquals(45463, eval("=DateDiff('s',#11/3/2018 1:37:30 AM#,#11/3/2018 2:15:13 PM#)"));
- assertEquals(86400, eval("=DateDiff('s',#11/3/2018#,#11/4/2018#)"));
- assertEquals(20306221, eval("=DateDiff('s',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)"));
- assertEquals(83378263, eval("=DateDiff('s',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)"));
- assertEquals(-83421497, eval("=DateDiff('s',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)"));
+ assertEval("3:57:34 AM", "=CStr(TimeSerial(3,57,34))");
+ assertEval("3:57:34 PM", "=CStr(TimeSerial(15,57,34))");
+ assertEval("5:45:00 AM", "=CStr(TimeSerial(6,-15,0))");
+ assertEval("12:00:00 AM", "=CStr(TimeSerial(0,0,0))");
+ assertEval("2:00:00 PM", "=CStr(TimeSerial(-10,0,0))");
+ assertEval("6:00:00 AM", "=CStr(TimeSerial(30,0,0))");
+
+ assertEval("2/12/1969", "=CStr(DateSerial(69,2,12))");
+ assertEval("2/12/2010", "=CStr(DateSerial(10,2,12))");
+ assertEval("7/12/2013", "=CStr(DateSerial(2014,-5,12))");
+ assertEval("8/7/2013", "=CStr(DateSerial(2014,-5,38))");
+
+ assertEval(1, "=DatePart('ww',#01/03/2018#)");
+ assertEval(2, "=DatePart('ww',#01/03/2018#,4)");
+ assertEval(1, "=DatePart('ww',#01/03/2018#,5)");
+ assertEval(1, "=DatePart('ww',#01/03/2018#,4,3)");
+ assertEval(52, "=DatePart('ww',#01/03/2018#,5,3)");
+ assertEval(1, "=DatePart('ww',#01/03/2018#,4,2)");
+ assertEval(53, "=DatePart('ww',#01/03/2018#,5,2)");
+ assertEval(2003, "=DatePart('yyyy',#11/22/2003 5:45:13 AM#)");
+ assertEval(4, "=DatePart('q',#11/22/2003 5:45:13 AM#)");
+ assertEval(11, "=DatePart('m',#11/22/2003 5:45:13 AM#)");
+ assertEval(326, "=DatePart('y',#11/22/2003 5:45:13 AM#)");
+ assertEval(22, "=DatePart('d',#11/22/2003 5:45:13 AM#)");
+ assertEval(7, "=DatePart('w',#11/22/2003 5:45:13 AM#)");
+ assertEval(3, "=DatePart('w',#11/22/2003 5:45:13 AM#, 5)");
+ assertEval(5, "=DatePart('h',#11/22/2003 5:45:13 AM#)");
+ assertEval(45, "=DatePart('n',#11/22/2003 5:45:13 AM#)");
+ assertEval(13, "=DatePart('s',#11/22/2003 5:45:13 AM#)");
+
+ assertEval("11/22/2005 5:45:13 AM", "CStr(DateAdd('yyyy',2,#11/22/2003 5:45:13 AM#))");
+ assertEval("2/22/2004 5:45:13 AM", "CStr(DateAdd('q',1,#11/22/2003 5:45:13 AM#))");
+ assertEval("1/22/2004 5:45:13 AM", "CStr(DateAdd('m',2,#11/22/2003 5:45:13 AM#))");
+ assertEval("12/12/2003 5:45:13 AM", "CStr(DateAdd('d',20,#11/22/2003 5:45:13 AM#))");
+ assertEval("12/12/2003 5:45:13 AM", "CStr(DateAdd('w',20,#11/22/2003 5:45:13 AM#))");
+ assertEval("12/12/2003 5:45:13 AM", "CStr(DateAdd('y',20,#11/22/2003 5:45:13 AM#))");
+ assertEval("12/27/2003 5:45:13 AM", "CStr(DateAdd('ww',5,#11/22/2003 5:45:13 AM#))");
+ assertEval("11/22/2003 3:45:13 PM", "CStr(DateAdd('h',10,#11/22/2003 5:45:13 AM#))");
+ assertEval("11/22/2003 6:19:13 AM", "CStr(DateAdd('n',34,#11/22/2003 5:45:13 AM#))");
+ assertEval("11/22/2003 5:46:27 AM", "CStr(DateAdd('s',74,#11/22/2003 5:45:13 AM#))");
+
+ assertEval("12/12/2003", "CStr(DateAdd('d',20,#11/22/2003#))");
+ assertEval("11/22/2003 10:00:00 AM", "CStr(DateAdd('h',10,#11/22/2003#))");
+ assertEval("11/23/2003", "CStr(DateAdd('h',24,#11/22/2003#))");
+ assertEval("3:45:13 PM", "CStr(DateAdd('h',10,#5:45:13 AM#))");
+ assertEval("12/31/1899 11:45:13 AM", "CStr(DateAdd('h',30,#5:45:13 AM#))");
+
+ assertEval(0, "=DateDiff('yyyy',#10/22/2003#,#11/22/2003#)");
+ assertEval(4, "=DateDiff('yyyy',#10/22/2003#,#11/22/2007#)");
+ assertEval(-4, "=DateDiff('yyyy',#11/22/2007#,#10/22/2003#)");
+
+ assertEval(0, "=DateDiff('q',#10/22/2003#,#11/22/2003#)");
+ assertEval(3, "=DateDiff('q',#03/01/2003#,#11/22/2003#)");
+ assertEval(16, "=DateDiff('q',#10/22/2003#,#11/22/2007#)");
+ assertEval(-13, "=DateDiff('q',#03/22/2007#,#10/22/2003#)");
+
+ assertEval(1, "=DateDiff('m',#10/22/2003#,#11/01/2003#)");
+ assertEval(8, "=DateDiff('m',#03/22/2003#,#11/01/2003#)");
+ assertEval(49, "=DateDiff('m',#10/22/2003#,#11/22/2007#)");
+ assertEval(-41, "=DateDiff('m',#03/22/2007#,#10/01/2003#)");
+
+ assertEval(10, "=DateDiff('d','10/22','11/01')");
+ assertEval(0, "=DateDiff('y',#1:37:00 AM#,#2:15:00 AM#)");
+ assertEval(10, "=DateDiff('d',#10/22/2003#,#11/01/2003#)");
+ assertEval(1, "=DateDiff('d',#10/22/2003 11:00:00 PM#,#10/23/2003 1:00:00 AM#)");
+ assertEval(224, "=DateDiff('d',#03/22/2003#,#11/01/2003#)");
+ assertEval(1492, "=DateDiff('y',#10/22/2003#,#11/22/2007#)");
+ assertEval(-1268, "=DateDiff('d',#03/22/2007#,#10/01/2003#)");
+ assertEval(366, "=DateDiff('d',#1/1/2000#,#1/1/2001#)");
+ assertEval(365, "=DateDiff('d',#1/1/2001#,#1/1/2002#)");
+
+ assertEval(0, "=DateDiff('w',#11/3/2018#,#11/04/2018#)");
+ assertEval(1, "=DateDiff('w',#11/3/2018#,#11/10/2018#)");
+ assertEval(0, "=DateDiff('w',#12/31/2017#,#1/1/2018#)");
+ assertEval(32, "=DateDiff('w',#03/22/2003#,#11/01/2003#)");
+ assertEval(213, "=DateDiff('w',#10/22/2003#,#11/22/2007#)");
+ assertEval(-181, "=DateDiff('w',#03/22/2007#,#10/01/2003#)");
+
+ assertEval(1, "=DateDiff('ww',#11/3/2018#,#11/04/2018#)");
+ assertEval(1, "=DateDiff('ww',#11/3/2018#,#11/10/2018#)");
+ assertEval(0, "=DateDiff('ww',#12/31/2017#,#1/1/2018#)");
+ assertEval(1, "=DateDiff('ww',#12/31/2017#,#1/1/2018#,2)");
+ assertEval(0, "=DateDiff('ww',#12/31/2017#,#1/1/2018#,1,3)");
+ assertEval(53, "=DateDiff('ww',#1/1/2000#,#1/1/2001#)");
+ assertEval(32, "=DateDiff('ww',#03/22/2003#,#11/01/2003#)");
+ assertEval(213, "=DateDiff('ww',#10/22/2003#,#11/22/2007#)");
+ assertEval(-181, "=DateDiff('ww',#03/22/2007#,#10/01/2003#)");
+
+ assertEval(1, "=DateDiff('h',#1:37:00 AM#,#2:15:00 AM#)");
+ assertEval(13, "=DateDiff('h',#1:37:00 AM#,#2:15:00 PM#)");
+ assertEval(1, "=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)");
+ assertEval(13, "=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 PM#)");
+ assertEval(24, "=DateDiff('h',#11/3/2018#,#11/4/2018#)");
+ assertEval(5641, "=DateDiff('h',#3/13/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)");
+ assertEval(23161, "=DateDiff('h',#3/13/2016 1:37:00 AM#,#11/3/2018 2:15:00 AM#)");
+ assertEval(-23173, "=DateDiff('h',#11/3/2018 2:15:00 PM#,#3/13/2016 1:37:00 AM#)");
+
+ assertEval(1, "=DateDiff('n',#1:37:59 AM#,#1:38:00 AM#)");
+ assertEval(758, "=DateDiff('n',#1:37:30 AM#,#2:15:13 PM#)");
+ assertEval(1, "=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)");
+ assertEval(758, "=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 2:15:00 PM#)");
+ assertEval(1440, "=DateDiff('n',#11/3/2018#,#11/4/2018#)");
+ assertEval(338438, "=DateDiff('n',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)");
+ assertEval(1389638, "=DateDiff('n',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)");
+ assertEval(-1390358, "=DateDiff('n',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)");
+
+ assertEval(1, "=DateDiff('s',#1:37:59 AM#,#1:38:00 AM#)");
+ assertEval(35, "=DateDiff('s',#1:37:10 AM#,#1:37:45 AM#)");
+ assertEval(45463, "=DateDiff('s',#1:37:30 AM#,#2:15:13 PM#)");
+ assertEval(1, "=DateDiff('s',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)");
+ assertEval(45463, "=DateDiff('s',#11/3/2018 1:37:30 AM#,#11/3/2018 2:15:13 PM#)");
+ assertEval(86400, "=DateDiff('s',#11/3/2018#,#11/4/2018#)");
+ assertEval(20306221, "=DateDiff('s',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)");
+ assertEval(83378263, "=DateDiff('s',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)");
+ assertEval(-83421497, "=DateDiff('s',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)");
}
public void testFinancialFuncs() throws Exception
{
- assertEquals("-9.57859403981306",
- eval("=CStr(NPer(0.12/12,-100,-1000))"));
- assertEquals("-9.48809500550578",
- eval("=CStr(NPer(0.12/12,-100,-1000,0,1))"));
- assertEquals("60.0821228537616",
- eval("=CStr(NPer(0.12/12,-100,-1000,10000))"));
- assertEquals("59.6738656742947",
- eval("=CStr(NPer(0.12/12,-100,-1000,10000,1))"));
- assertEquals("69.6607168935747",
- eval("=CStr(NPer(0.12/12,-100,0,10000))"));
- assertEquals("69.1619606798005",
- eval("=CStr(NPer(0.12/12,-100,0,10000,1))"));
-
- assertEquals("8166.96698564091",
- eval("=CStr(FV(0.12/12,60,-100))"));
- assertEquals("8248.63665549732",
- eval("=CStr(FV(0.12/12,60,-100,0,1))"));
- assertEquals("6350.27028707682",
- eval("=CStr(FV(0.12/12,60,-100,1000))"));
- assertEquals("6431.93995693323",
- eval("=CStr(FV(0.12/12,60,-100,1000,1))"));
-
- assertEquals("4495.5038406224",
- eval("=CStr(PV(0.12/12,60,-100))"));
- assertEquals("4540.45887902863",
- eval("=CStr(PV(0.12/12,60,-100,0,1))"));
- assertEquals("-1008.99231875519",
- eval("=CStr(PV(0.12/12,60,-100,10000))"));
- assertEquals("-964.03728034897",
- eval("=CStr(PV(0.12/12,60,-100,10000,1))"));
-
- assertEquals("22.2444476849018",
- eval("=CStr(Pmt(0.12/12,60,-1000))"));
- assertEquals("22.0242056286156",
- eval("=CStr(Pmt(0.12/12,60,-1000,0,1))"));
- assertEquals("-100.200029164116",
- eval("=CStr(Pmt(0.12/12,60,-1000,10000))"));
- assertEquals("-99.2079496674414",
- eval("=CStr(Pmt(0.12/12,60,-1000,10000,1))"));
- assertEquals("-122.444476849018",
- eval("=CStr(Pmt(0.12/12,60,0,10000))"));
- assertEquals("-121.232155296057",
- eval("=CStr(Pmt(0.12/12,60,0,10000,1))"));
- assertEquals("22.2444476849018",
- eval("=CStr(Pmt(0.12/12,60,-1000))"));
-
- assertEquals("10",
- eval("=CStr(IPmt(0.12/12,1,60,-1000))"));
- assertEquals("5.90418478297567",
- eval("=CStr(IPmt(0.12/12,30,60,-1000))"));
- assertEquals("0",
- eval("=CStr(IPmt(0.12/12,1,60,-1000,0,1))"));
- assertEquals("5.8457275078967",
- eval("=CStr(IPmt(0.12/12,30,60,-1000,0,1))"));
- assertEquals("0",
- eval("=CStr(IPmt(0.12/12,1,60,0,10000))"));
- assertEquals("40.9581521702433",
- eval("=CStr(IPmt(0.12/12,30,60,0,10000))"));
- assertEquals("0",
- eval("=CStr(IPmt(0.12/12,1,60,0,10000,1))"));
- assertEquals("40.552625911132",
- eval("=CStr(IPmt(0.12/12,30,60,0,10000,1))"));
- assertEquals("10",
- eval("=CStr(IPmt(0.12/12,1,60,-1000,10000))"));
- assertEquals("46.862336953219",
- eval("=CStr(IPmt(0.12/12,30,60,-1000,10000))"));
- assertEquals("0",
- eval("=CStr(IPmt(0.12/12,1,60,-1000,10000,1))"));
- assertEquals("46.3983534190287",
- eval("=CStr(IPmt(0.12/12,30,60,-1000,10000,1))"));
-
- assertEquals("12.2444476849018",
- eval("=CStr(PPmt(0.12/12,1,60,-1000))"));
- assertEquals("16.3402629019261",
- eval("=CStr(PPmt(0.12/12,30,60,-1000))"));
- assertEquals("22.0242056286156",
- eval("=CStr(PPmt(0.12/12,1,60,-1000,0,1))"));
- assertEquals("16.1784781207189",
- eval("=CStr(PPmt(0.12/12,30,60,-1000,0,1))"));
- assertEquals("-122.444476849018",
- eval("=CStr(PPmt(0.12/12,1,60,0,10000))"));
- assertEquals("-163.402629019261",
- eval("=CStr(PPmt(0.12/12,30,60,0,10000))"));
- assertEquals("-121.232155296057",
- eval("=CStr(PPmt(0.12/12,1,60,0,10000,1))"));
- assertEquals("-161.784781207189",
- eval("=CStr(PPmt(0.12/12,30,60,0,10000,1))"));
- assertEquals("-110.200029164116",
- eval("=CStr(PPmt(0.12/12,1,60,-1000,10000))"));
- assertEquals("-147.062366117335",
- eval("=CStr(PPmt(0.12/12,30,60,-1000,10000))"));
- assertEquals("-99.2079496674414",
- eval("=CStr(PPmt(0.12/12,1,60,-1000,10000,1))"));
- assertEquals("-145.60630308647",
- eval("=CStr(PPmt(0.12/12,30,60,-1000,10000,1))"));
-
- assertEquals("1.31506849315068",
- eval("=CStr(DDB(2400,300,10*365,1))"));
- assertEquals("40",
- eval("=CStr(DDB(2400,300,10*12,1))"));
- assertEquals("480",
- eval("=CStr(DDB(2400,300,10,1))"));
- assertEquals("22.1225472000002",
- eval("=CStr(DDB(2400,300,10,10))"));
- assertEquals("245.76",
- eval("=CStr(DDB(2400,300,10,4))"));
- assertEquals("307.2",
- eval("=CStr(DDB(2400,300,10,3))"));
- assertEquals("480",
- eval("=CStr(DDB(2400,300,10,0.1))"));
- assertEquals("274.768033075174",
- eval("=CStr(DDB(2400,300,10,3.5))"));
-
- assertEquals("2250",
- eval("=CStr(SLN(30000,7500,10))"));
- assertEquals("1000",
- eval("=CStr(SLN(10000,5000,5))"));
- assertEquals("1142.85714285714",
- eval("=CStr(SLN(8000,0,7))"));
-
- assertEquals("4090.90909090909",
- eval("=CStr(SYD(30000,7500,10,1))"));
- assertEquals("409.090909090909",
- eval("=CStr(SYD(30000,7500,10,10))"));
-
- assertEquals("-1.63048347266756E-02",
- eval("=CStr(Rate(3,200,-610,0,-20,0.1))"));
- assertEquals("7.70147248820175E-03",
- eval("=CStr(Rate(4*12,-200,8000))"));
- assertEquals("-1.09802980531205",
- eval("=CStr(Rate(60,93.22,5000,0.1))"));
+ assertEval("-9.57859403981306", "=CStr(NPer(0.12/12,-100,-1000))");
+ assertEval("-9.48809500550578", "=CStr(NPer(0.12/12,-100,-1000,0,1))");
+ assertEval("60.0821228537616", "=CStr(NPer(0.12/12,-100,-1000,10000))");
+ assertEval("59.6738656742947", "=CStr(NPer(0.12/12,-100,-1000,10000,1))");
+ assertEval("69.6607168935747", "=CStr(NPer(0.12/12,-100,0,10000))");
+ assertEval("69.1619606798005", "=CStr(NPer(0.12/12,-100,0,10000,1))");
+
+ assertEval("8166.96698564091", "=CStr(FV(0.12/12,60,-100))");
+ assertEval("8248.63665549732", "=CStr(FV(0.12/12,60,-100,0,1))");
+ assertEval("6350.27028707682", "=CStr(FV(0.12/12,60,-100,1000))");
+ assertEval("6431.93995693323", "=CStr(FV(0.12/12,60,-100,1000,1))");
+
+ assertEval("4495.5038406224", "=CStr(PV(0.12/12,60,-100))");
+ assertEval("4540.45887902863", "=CStr(PV(0.12/12,60,-100,0,1))");
+ assertEval("-1008.99231875519", "=CStr(PV(0.12/12,60,-100,10000))");
+ assertEval("-964.03728034897", "=CStr(PV(0.12/12,60,-100,10000,1))");
+
+ assertEval("22.2444476849018", "=CStr(Pmt(0.12/12,60,-1000))");
+ assertEval("22.0242056286156", "=CStr(Pmt(0.12/12,60,-1000,0,1))");
+ assertEval("-100.200029164116", "=CStr(Pmt(0.12/12,60,-1000,10000))");
+ assertEval("-99.2079496674414", "=CStr(Pmt(0.12/12,60,-1000,10000,1))");
+ assertEval("-122.444476849018", "=CStr(Pmt(0.12/12,60,0,10000))");
+ assertEval("-121.232155296057", "=CStr(Pmt(0.12/12,60,0,10000,1))");
+ assertEval("22.2444476849018", "=CStr(Pmt(0.12/12,60,-1000))");
+
+ assertEval("10", "=CStr(IPmt(0.12/12,1,60,-1000))");
+ assertEval("5.90418478297567", "=CStr(IPmt(0.12/12,30,60,-1000))");
+ assertEval("0", "=CStr(IPmt(0.12/12,1,60,-1000,0,1))");
+ assertEval("5.8457275078967", "=CStr(IPmt(0.12/12,30,60,-1000,0,1))");
+ assertEval("0", "=CStr(IPmt(0.12/12,1,60,0,10000))");
+ assertEval("40.9581521702433", "=CStr(IPmt(0.12/12,30,60,0,10000))");
+ assertEval("0", "=CStr(IPmt(0.12/12,1,60,0,10000,1))");
+ assertEval("40.552625911132", "=CStr(IPmt(0.12/12,30,60,0,10000,1))");
+ assertEval("10", "=CStr(IPmt(0.12/12,1,60,-1000,10000))");
+ assertEval("46.862336953219", "=CStr(IPmt(0.12/12,30,60,-1000,10000))");
+ assertEval("0", "=CStr(IPmt(0.12/12,1,60,-1000,10000,1))");
+ assertEval("46.3983534190287", "=CStr(IPmt(0.12/12,30,60,-1000,10000,1))");
+
+ assertEval("12.2444476849018", "=CStr(PPmt(0.12/12,1,60,-1000))");
+ assertEval("16.3402629019261", "=CStr(PPmt(0.12/12,30,60,-1000))");
+ assertEval("22.0242056286156", "=CStr(PPmt(0.12/12,1,60,-1000,0,1))");
+ assertEval("16.1784781207189", "=CStr(PPmt(0.12/12,30,60,-1000,0,1))");
+ assertEval("-122.444476849018", "=CStr(PPmt(0.12/12,1,60,0,10000))");
+ assertEval("-163.402629019261", "=CStr(PPmt(0.12/12,30,60,0,10000))");
+ assertEval("-121.232155296057", "=CStr(PPmt(0.12/12,1,60,0,10000,1))");
+ assertEval("-161.784781207189", "=CStr(PPmt(0.12/12,30,60,0,10000,1))");
+ assertEval("-110.200029164116", "=CStr(PPmt(0.12/12,1,60,-1000,10000))");
+ assertEval("-147.062366117335", "=CStr(PPmt(0.12/12,30,60,-1000,10000))");
+ assertEval("-99.2079496674414", "=CStr(PPmt(0.12/12,1,60,-1000,10000,1))");
+ assertEval("-145.60630308647", "=CStr(PPmt(0.12/12,30,60,-1000,10000,1))");
+
+ assertEval("1.31506849315068", "=CStr(DDB(2400,300,10*365,1))");
+ assertEval("40", "=CStr(DDB(2400,300,10*12,1))");
+ assertEval("480", "=CStr(DDB(2400,300,10,1))");
+ assertEval("22.1225472000002", "=CStr(DDB(2400,300,10,10))");
+ assertEval("245.76", "=CStr(DDB(2400,300,10,4))");
+ assertEval("307.2", "=CStr(DDB(2400,300,10,3))");
+ assertEval("480", "=CStr(DDB(2400,300,10,0.1))");
+ assertEval("274.768033075174", "=CStr(DDB(2400,300,10,3.5))");
+
+ assertEval("2250", "=CStr(SLN(30000,7500,10))");
+ assertEval("1000", "=CStr(SLN(10000,5000,5))");
+ assertEval("1142.85714285714", "=CStr(SLN(8000,0,7))");
+
+ assertEval("4090.90909090909", "=CStr(SYD(30000,7500,10,1))");
+ assertEval("409.090909090909", "=CStr(SYD(30000,7500,10,10))");
+
+ assertEval("-1.63048347266756E-02", "=CStr(Rate(3,200,-610,0,-20,0.1))");
+ assertEval("7.70147248820175E-03", "=CStr(Rate(4*12,-200,8000))");
+ assertEval("-1.09802980531205", "=CStr(Rate(60,93.22,5000,0.1))");
}
+ static void assertEval(Object expected, String exprStr) {
+ try {
+ assertEquals(expected, eval(exprStr));
+ } catch(Error e) {
+ // System.err.println("[ERROR] " + e);
+ throw e;
+ }
+ }
}