From 25069267ebe8d5f9028d51c2fbe7fb0217e5a07f Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Thu, 8 Nov 2018 23:33:15 +0000 Subject: [PATCH] implement FormatCurrency and FormatPercent git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1220 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/impl/expr/DefaultFunctions.java | 116 +++++++++++------- .../impl/expr/DefaultFunctionsTest.java | 10 ++ 2 files changed, 83 insertions(+), 43 deletions(-) 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 132daeb..c75bb14 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java @@ -283,51 +283,23 @@ public class DefaultFunctions public static final Function FORMATNUMBER = registerFunc(new FuncVar("FormatNumber", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - Value param1 = params[0]; - if(param1.isNull()) { - return ValueSupport.NULL_VAL; - } - - 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(); - - fmt.append(incLeadDigit ? "0" : "#"); - if(numDecDigits > 0) { - fmt.append("."); - for(int i = 0; i < numDecDigits; ++i) { - fmt.append("0"); - } - } + return formatNumber(ctx, params, false, false); + } + }); - if(negParens) { - // the javadocs claim the second pattern does not need to be fully - // defined, but it doesn't seem to work that way - String mainPat = fmt.toString(); - fmt.append(";(").append(mainPat).append(")"); - } - - // Note, DecimalFormat rounding mode uses HALF_EVEN by default - DecimalFormat df = new DecimalFormat( - fmt.toString(), cfg.getDecimalFormatSymbols()); - if(groupDigits) { - df.setGroupingUsed(true); - df.setGroupingSize(numGroupDigits); - } else { - df.setGroupingUsed(false); - df.setGroupingSize(numGroupDigits); - } + public static final Function FORMATPERCENT = registerFunc(new FuncVar("FormatPercent", 1, 6) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + // FIXME, are defaults same for percent & currency? + return formatNumber(ctx, params, true, false); + } + }); - return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx))); + public static final Function FORMATCURRENCY = registerFunc(new FuncVar("FormatCurrency", 1, 6) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + // FIXME, are defaults same for percent & currency? + return formatNumber(ctx, params, false, true); } }); @@ -495,6 +467,64 @@ public class DefaultFunctions return bv; } + private static Value formatNumber( + EvalContext ctx, Value[] params, boolean isPercent, boolean isCurrency) { + + Value param1 = params[0]; + if(param1.isNull()) { + return ValueSupport.NULL_VAL; + } + + 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(); + + if(isCurrency) { + fmt.append("\u00A4"); + } + + fmt.append(incLeadDigit ? "0" : "#"); + if(numDecDigits > 0) { + fmt.append("."); + for(int i = 0; i < numDecDigits; ++i) { + fmt.append("0"); + } + } + + if(isPercent) { + fmt.append("%"); + } + + if(negParens) { + // the javadocs claim the second pattern does not need to be fully + // defined, but it doesn't seem to work that way + String mainPat = fmt.toString(); + fmt.append(";(").append(mainPat).append(")"); + } + + // Note, DecimalFormat rounding mode uses HALF_EVEN by default + DecimalFormat df = new DecimalFormat( + 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))); + } + // https://www.techonthenet.com/access/functions/ // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83 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 163f0f2..f40a1de 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -243,6 +243,16 @@ public class DefaultFunctionsTest extends TestCase assertEval("(12)", "=FormatNumber(-12.345,0,True,True)"); assertEval("12,345.00", "=FormatNumber(12345,-1,-2,-2,True)"); assertEval("12345.00", "=FormatNumber(12345,-1,-2,-2,False)"); + + assertEval("1,234,500.00%", "=FormatPercent(12345)"); + assertEval("(1,234.50%)", "=FormatPercent(-12.345,-1,True,True)"); + assertEval("34%", "=FormatPercent(0.345,0,True,True)"); + assertEval("-.123%", "=FormatPercent(-0.0012345,3,False)"); + + assertEval("$12,345.00", "=FormatCurrency(12345)"); + assertEval("($12.34)", "=FormatCurrency(-12.345,-1,True,True)"); + assertEval("$12", "=FormatCurrency(12.345,0,True,True)"); + assertEval("-$.123", "=FormatCurrency(-0.12345,3,False)"); } public void testNumberFuncs() throws Exception -- 2.39.5