diff options
Diffstat (limited to 'src')
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; + } + } } |