From d209a57058e44ab587cc3b1c7c3f77dd64ec49ec Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Tue, 20 Nov 2018 02:01:46 +0000 Subject: [PATCH] implement Format with predefined formats git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1226 f203690c-595d-4dc9-a70b-905162fa7fd2 --- .../jackcess/expr/NumericConfig.java | 43 +++++ .../jackcess/expr/Value.java | 4 + .../jackcess/impl/DBEvalContext.java | 4 +- .../jackcess/impl/expr/BaseValue.java | 1 - .../jackcess/impl/expr/BigDecimalValue.java | 1 - .../jackcess/impl/expr/BuiltinOperators.java | 7 +- .../jackcess/impl/expr/DefaultFunctions.java | 81 +++------ .../impl/expr/DefaultNumberFunctions.java | 1 - .../jackcess/impl/expr/DoubleValue.java | 1 - .../jackcess/impl/expr/FormatUtil.java | 167 +++++++++++++++++- .../impl/{ => expr}/NumberFormatter.java | 78 ++++++-- .../impl/expr/DefaultFunctionsTest.java | 59 +++++++ .../impl/expr/ExpressionatorTest.java | 4 +- .../impl/{ => expr}/NumberFormatterTest.java | 2 +- 14 files changed, 371 insertions(+), 82 deletions(-) rename src/main/java/com/healthmarketscience/jackcess/impl/{ => expr}/NumberFormatter.java (70%) rename src/test/java/com/healthmarketscience/jackcess/impl/{ => expr}/NumberFormatterTest.java (99%) diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java b/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java index cf9bacb..74dd06e 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/NumericConfig.java @@ -18,6 +18,7 @@ package com.healthmarketscience.jackcess.expr; import java.text.DecimalFormatSymbols; import java.util.Locale; +import com.healthmarketscience.jackcess.impl.expr.FormatUtil; /** * A NumericConfig encapsulates number formatting options for expression @@ -33,12 +34,21 @@ public class NumericConfig public static final NumericConfig US_NUMERIC_CONFIG = new NumericConfig( 2, true, false, true, 3, Locale.US); + public enum Type { + CURRENCY, FIXED, STANDARD, PERCENT, SCIENTIFIC; + } + private final int _numDecDigits; private final boolean _incLeadingDigit; private final boolean _useNegParens; private final boolean _useNegCurrencyParens; private final int _numGroupDigits; private final DecimalFormatSymbols _symbols; + private final String _currencyFormat; + private final String _fixedFormat; + private final String _standardFormat; + private final String _percentFormat; + private final String _scientificFormat; public NumericConfig(int numDecDigits, boolean incLeadingDigit, boolean useNegParens, boolean useNegCurrencyParens, @@ -49,6 +59,22 @@ public class NumericConfig _useNegCurrencyParens = useNegCurrencyParens; _numGroupDigits = numGroupDigits; _symbols = DecimalFormatSymbols.getInstance(locale); + + _currencyFormat = FormatUtil.createNumberFormatPattern( + FormatUtil.NumPatternType.CURRENCY, _numDecDigits, _incLeadingDigit, + _useNegCurrencyParens, _numGroupDigits); + _fixedFormat = FormatUtil.createNumberFormatPattern( + FormatUtil.NumPatternType.GENERAL, _numDecDigits, true, + _useNegParens, 0); + _standardFormat = FormatUtil.createNumberFormatPattern( + FormatUtil.NumPatternType.GENERAL, _numDecDigits, _incLeadingDigit, + _useNegParens, _numGroupDigits); + _percentFormat = FormatUtil.createNumberFormatPattern( + FormatUtil.NumPatternType.PERCENT, _numDecDigits, _incLeadingDigit, + _useNegParens, 0); + _scientificFormat = FormatUtil.createNumberFormatPattern( + FormatUtil.NumPatternType.SCIENTIFIC, _numDecDigits, true, + false, 0); } public int getNumDecimalDigits() { @@ -71,6 +97,23 @@ public class NumericConfig return _numGroupDigits; } + public String getNumberFormat(Type type) { + switch(type) { + case CURRENCY: + return _currencyFormat; + case FIXED: + return _fixedFormat; + case STANDARD: + return _standardFormat; + case PERCENT: + return _percentFormat; + case SCIENTIFIC: + return _scientificFormat; + default: + throw new IllegalArgumentException("unknown number type " + type); + } + } + public DecimalFormatSymbols getDecimalFormatSymbols() { return _symbols; } diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java index e9508fa..118215e 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java @@ -35,6 +35,10 @@ public interface Value { NULL, STRING, DATE, TIME, DATE_TIME, LONG, DOUBLE, BIG_DEC; + public boolean isString() { + return (this == STRING); + } + public boolean isNumeric() { return inRange(LONG, BIG_DEC); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/DBEvalContext.java b/src/main/java/com/healthmarketscience/jackcess/impl/DBEvalContext.java index b1e9995..398d9fa 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/DBEvalContext.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/DBEvalContext.java @@ -29,6 +29,7 @@ import com.healthmarketscience.jackcess.expr.NumericConfig; import com.healthmarketscience.jackcess.expr.TemporalConfig; import com.healthmarketscience.jackcess.impl.expr.DefaultFunctions; import com.healthmarketscience.jackcess.impl.expr.Expressionator; +import com.healthmarketscience.jackcess.impl.expr.NumberFormatter; import com.healthmarketscience.jackcess.impl.expr.RandomContext; /** @@ -112,11 +113,12 @@ public class DBEvalContext implements Expressionator.ParseContext, EvalConfig DecimalFormat df = _dfs.get(formatStr); if(df == null) { df = new DecimalFormat(formatStr, _numeric.getDecimalFormatSymbols()); + df.setRoundingMode(NumberFormatter.ROUND_MODE); _dfs.put(formatStr, df); } return df; } - + public float getRandom(Integer seed) { return _rndCtx.getRandom(seed); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java index b444218..2b172d3 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java @@ -22,7 +22,6 @@ import java.util.Date; import com.healthmarketscience.jackcess.expr.Value; import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.LocaleContext; -import com.healthmarketscience.jackcess.impl.NumberFormatter; /** * diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BigDecimalValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BigDecimalValue.java index b847b59..0e78f90 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BigDecimalValue.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BigDecimalValue.java @@ -19,7 +19,6 @@ package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; import com.healthmarketscience.jackcess.expr.LocaleContext; -import com.healthmarketscience.jackcess.impl.NumberFormatter; /** * diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java index be893c3..5131a93 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java @@ -22,7 +22,6 @@ import java.util.regex.Pattern; import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.LocaleContext; import com.healthmarketscience.jackcess.expr.Value; -import com.healthmarketscience.jackcess.impl.NumberFormatter; import static com.healthmarketscience.jackcess.impl.expr.ValueSupport.*; @@ -222,7 +221,7 @@ public class BuiltinOperators Value.Type mathType = getMathTypePrecedence(ctx, param1, param2, CoercionType.GENERAL); - if(mathType == Value.Type.STRING) { + if(mathType.isString()) { throw new EvalException("Unexpected type " + mathType); } return toValue(param1.getAsLongInt(ctx) / param2.getAsLongInt(ctx)); @@ -270,7 +269,7 @@ public class BuiltinOperators Value.Type mathType = getMathTypePrecedence(ctx, param1, param2, CoercionType.GENERAL); - if(mathType == Value.Type.STRING) { + if(mathType.isString()) { throw new EvalException("Unexpected type " + mathType); } return toValue(param1.getAsLongInt(ctx) % param2.getAsLongInt(ctx)); @@ -579,7 +578,7 @@ public class BuiltinOperators return t1; } - if((t1 == Value.Type.STRING) || (t2 == Value.Type.STRING)) { + if(t1.isString() || t2.isString()) { if(cType._allowCoerceStringToNum) { // see if this is mixed string/numeric and the string can be coerced 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 d663309..29c0f71 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java @@ -34,7 +34,6 @@ import com.healthmarketscience.jackcess.expr.NumericConfig; import com.healthmarketscience.jackcess.expr.TemporalConfig; import com.healthmarketscience.jackcess.expr.Value; import com.healthmarketscience.jackcess.impl.DatabaseImpl; -import com.healthmarketscience.jackcess.impl.NumberFormatter; import static com.healthmarketscience.jackcess.impl.expr.FunctionSupport.*; /** @@ -75,7 +74,7 @@ public class DefaultFunctions public static final Function HEX = registerStringFunc(new Func1NullIsNull("Hex") { @Override protected Value eval1(EvalContext ctx, Value param1) { - if((param1.getType() == Value.Type.STRING) && + if(param1.getType().isString() && (param1.getAsString(ctx).length() == 0)) { return ValueSupport.ZERO_VAL; } @@ -95,8 +94,7 @@ public class DefaultFunctions return params[1]; } Value.Type resultType = ctx.getResultType(); - return (((resultType == null) || - (resultType == Value.Type.STRING)) ? + return (((resultType == null) || resultType.isString()) ? ValueSupport.EMPTY_STR_VAL : ValueSupport.ZERO_VAL); } }); @@ -131,7 +129,7 @@ public class DefaultFunctions public static final Function OCT = registerStringFunc(new Func1NullIsNull("Oct") { @Override protected Value eval1(EvalContext ctx, Value param1) { - if((param1.getType() == Value.Type.STRING) && + if(param1.getType().isString() && (param1.getAsString(ctx).length() == 0)) { return ValueSupport.ZERO_VAL; } @@ -256,7 +254,7 @@ public class DefaultFunctions // return true if it is explicitly a date/time, not if it is just a // number (even though casting a number string to a date/time works in // general) - if((param1.getType() == Value.Type.STRING) && + if(param1.getType().isString() && !stringIsNumeric(ctx, param1) && stringIsTemporal(ctx, param1)) { return ValueSupport.TRUE_VAL; @@ -275,8 +273,7 @@ public class DefaultFunctions // note, only a string can be considered numberic for this function, // even though a date/time can be cast to a number in general - if((param1.getType() == Value.Type.STRING) && - stringIsNumeric(ctx, param1)) { + if(param1.getType().isString() && stringIsNumeric(ctx, param1)) { return ValueSupport.TRUE_VAL; } @@ -287,21 +284,21 @@ public class DefaultFunctions public static final Function FORMATNUMBER = registerFunc(new FuncVar("FormatNumber", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - return formatNumber(ctx, params, false, false); + return formatNumber(ctx, params, FormatUtil.NumPatternType.GENERAL); } }); public static final Function FORMATPERCENT = registerFunc(new FuncVar("FormatPercent", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - return formatNumber(ctx, params, true, false); + return formatNumber(ctx, params, FormatUtil.NumPatternType.PERCENT); } }); public static final Function FORMATCURRENCY = registerFunc(new FuncVar("FormatCurrency", 1, 6) { @Override protected Value evalVar(EvalContext ctx, Value[] params) { - return formatNumber(ctx, params, false, true); + return formatNumber(ctx, params, FormatUtil.NumPatternType.CURRENCY); } }); @@ -479,24 +476,30 @@ public class DefaultFunctions }); private static boolean stringIsNumeric(EvalContext ctx, Value param) { + return (maybeGetAsBigDecimal(ctx, param) != null); + } + + static BigDecimal maybeGetAsBigDecimal(EvalContext ctx, Value param) { try { - param.getAsBigDecimal(ctx); - return true; + return param.getAsBigDecimal(ctx); } catch(EvalException ignored) { - // fall through to false + // not a number } - return false; + return null; } private static boolean stringIsTemporal(EvalContext ctx, Value param) { + return (maybeGetAsDateTimeValue(ctx, param) != null); + } + + static Value maybeGetAsDateTimeValue(EvalContext ctx, Value param) { try { // see if we can coerce to date/time - param.getAsDateTimeValue(ctx); - return true; + return param.getAsDateTimeValue(ctx); } catch(EvalException ignored) { // not a date/time } - return false; + return null; } private static boolean getOptionalTriStateBoolean( @@ -525,7 +528,7 @@ public class DefaultFunctions } private static Value formatNumber( - EvalContext ctx, Value[] params, boolean isPercent, boolean isCurrency) { + EvalContext ctx, Value[] params, FormatUtil.NumPatternType numPatType) { Value param1 = params[0]; if(param1.isNull()) { @@ -537,44 +540,18 @@ public class DefaultFunctions ctx, params, 1, cfg.getNumDecimalDigits(), -1); boolean incLeadDigit = getOptionalTriStateBoolean( ctx, params, 2, cfg.includeLeadingDigit()); - boolean defNegParens = (isCurrency ? cfg.useParensForCurrencyNegatives() : - cfg.useParensForNegatives()); + boolean defNegParens = numPatType.useParensForNegatives(cfg); boolean negParens = getOptionalTriStateBoolean( ctx, params, 3, defNegParens); - int numGroupDigits = cfg.getNumGroupingDigits(); + int defNumGroupDigits = cfg.getNumGroupingDigits(); boolean groupDigits = getOptionalTriStateBoolean( - ctx, params, 4, (numGroupDigits > 0)); - - StringBuilder fmt = new StringBuilder(); - - if(isCurrency) { - fmt.append("\u00A4"); - } + ctx, params, 4, (defNumGroupDigits > 0)); + int numGroupDigits = (groupDigits ? defNumGroupDigits : 0); - if(groupDigits) { - fmt.append("#,"); - DefaultTextFunctions.nchars(fmt, numGroupDigits - 1, '#'); - } - - fmt.append(incLeadDigit ? "0" : "#"); - if(numDecDigits > 0) { - fmt.append("."); - DefaultTextFunctions.nchars(fmt, numDecDigits, '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(")"); - } + String fmtStr = FormatUtil.createNumberFormatPattern( + numPatType, numDecDigits, incLeadDigit, negParens, numGroupDigits); - // Note, DecimalFormat rounding mode uses HALF_EVEN by default - DecimalFormat df = ctx.createDecimalFormat(fmt.toString()); + DecimalFormat df = ctx.createDecimalFormat(fmtStr); return ValueSupport.toValue(df.format(param1.getAsBigDecimal(ctx))); } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java index 43bf5f4..1ec08db 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java @@ -22,7 +22,6 @@ import com.healthmarketscience.jackcess.expr.EvalContext; import com.healthmarketscience.jackcess.expr.EvalException; import com.healthmarketscience.jackcess.expr.Function; import com.healthmarketscience.jackcess.expr.Value; -import com.healthmarketscience.jackcess.impl.NumberFormatter; import static com.healthmarketscience.jackcess.impl.expr.DefaultFunctions.*; import static com.healthmarketscience.jackcess.impl.expr.FunctionSupport.*; diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DoubleValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DoubleValue.java index fbbdd96..d37c90b 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DoubleValue.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DoubleValue.java @@ -19,7 +19,6 @@ package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; import com.healthmarketscience.jackcess.expr.LocaleContext; -import com.healthmarketscience.jackcess.impl.NumberFormatter; /** * diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java index 1d9e21e..2c475eb 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java @@ -16,11 +16,15 @@ limitations under the License. package com.healthmarketscience.jackcess.impl.expr; +import java.math.BigDecimal; import java.text.DateFormat; +import java.text.DecimalFormat; +import java.text.NumberFormat; import java.util.HashMap; import java.util.Map; import com.healthmarketscience.jackcess.expr.EvalContext; +import com.healthmarketscience.jackcess.expr.NumericConfig; import com.healthmarketscience.jackcess.expr.TemporalConfig; import com.healthmarketscience.jackcess.expr.Value; @@ -30,11 +34,43 @@ import com.healthmarketscience.jackcess.expr.Value; */ public class FormatUtil { + public enum NumPatternType { + GENERAL, CURRENCY { + @Override + protected void appendPrefix(StringBuilder fmt) { + fmt.append("\u00A4"); + } + @Override + protected boolean useParensForNegatives(NumericConfig cfg) { + return cfg.useParensForCurrencyNegatives(); + } + }, + PERCENT { + @Override + protected void appendSuffix(StringBuilder fmt) { + fmt.append("%"); + } + }, + SCIENTIFIC { + @Override + protected void appendSuffix(StringBuilder fmt) { + fmt.append("E0"); + } + }; + + protected void appendPrefix(StringBuilder fmt) {} + + protected void appendSuffix(StringBuilder fmt) {} + + protected boolean useParensForNegatives(NumericConfig cfg) { + return cfg.useParensForNegatives(); + } + } + private static final Map PREDEF_FMTS = new HashMap(); static { - PREDEF_FMTS.put("General Date", - new PredefDateFmt(TemporalConfig.Type.GENERAL_DATE)); + PREDEF_FMTS.put("General Date", new GenPredefDateFmt()); PREDEF_FMTS.put("Long Date", new PredefDateFmt(TemporalConfig.Type.LONG_DATE)); PREDEF_FMTS.put("Medium Date", @@ -48,6 +84,20 @@ public class FormatUtil PREDEF_FMTS.put("Short Time", new PredefDateFmt(TemporalConfig.Type.SHORT_TIME)); + PREDEF_FMTS.put("General Number", new GenPredefNumberFmt()); + PREDEF_FMTS.put("Currency", + new PredefNumberFmt(NumericConfig.Type.CURRENCY)); + // FIXME ? + // PREDEF_FMTS.put("Euro", + // new PredefNumberFmt(???)); + PREDEF_FMTS.put("Fixed", + new PredefNumberFmt(NumericConfig.Type.FIXED)); + PREDEF_FMTS.put("Standard", + new PredefNumberFmt(NumericConfig.Type.STANDARD)); + PREDEF_FMTS.put("Percent", + new PredefNumberFmt(NumericConfig.Type.PERCENT)); + PREDEF_FMTS.put("Scientific", new ScientificPredefNumberFmt()); + PREDEF_FMTS.put("True/False", new PredefBoolFmt("True", "False")); PREDEF_FMTS.put("Yes/No", new PredefBoolFmt("Yes", "No")); PREDEF_FMTS.put("On/Off", new PredefBoolFmt("On", "Off")); @@ -59,14 +109,55 @@ public class FormatUtil public static Value format(EvalContext ctx, Value expr, String fmtStr, int firstDay, int firstWeekType) { + Fmt predefFmt = PREDEF_FMTS.get(fmtStr); + if(predefFmt != null) { + if(expr.isNull()) { + // predefined formats return null for null + return ValueSupport.NULL_VAL; + } + return predefFmt.format(ctx, expr, null, firstDay, firstWeekType); + } // FIXME, throw new UnsupportedOperationException(); } + public static String createNumberFormatPattern( + NumPatternType numPatType, int numDecDigits, boolean incLeadDigit, + boolean negParens, int numGroupDigits) { + + StringBuilder fmt = new StringBuilder(); + + numPatType.appendPrefix(fmt); + + if(numGroupDigits > 0) { + fmt.append("#,"); + DefaultTextFunctions.nchars(fmt, numGroupDigits - 1, '#'); + } + + fmt.append(incLeadDigit ? "0" : "#"); + if(numDecDigits > 0) { + fmt.append("."); + DefaultTextFunctions.nchars(fmt, numDecDigits, '0'); + } + + numPatType.appendSuffix(fmt); + + 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(")"); + } + + return fmt.toString(); + } + + private static abstract class Fmt { // FIXME, no null + // FIXME, need fmtStr? public abstract Value format(EvalContext ctx, Value expr, String fmtStr, int firstDay, int firstWeekType); } @@ -88,6 +179,22 @@ public class FormatUtil } } + private static class GenPredefDateFmt extends Fmt + { + @Override + public Value format(EvalContext ctx, Value expr, String fmtStr, + int firstDay, int firstWeekType) { + Value tempExpr = expr; + if(!expr.getType().isTemporal()) { + Value maybe = DefaultFunctions.maybeGetAsDateTimeValue(ctx, expr); + if(maybe != null) { + tempExpr = maybe; + } + } + return ValueSupport.toValue(tempExpr.getAsString(ctx)); + } + } + private static class PredefBoolFmt extends Fmt { private final Value _trueVal; @@ -101,10 +208,64 @@ public class FormatUtil @Override public Value format(EvalContext ctx, Value expr, String fmtStr, int firstDay, int firstWeekType) { - // FIXME, handle null? return(expr.getAsBoolean(ctx) ? _trueVal : _falseVal); } } + private static class PredefNumberFmt extends Fmt + { + private final NumericConfig.Type _type; + private PredefNumberFmt(NumericConfig.Type type) { + _type = type; + } + + @Override + public Value format(EvalContext ctx, Value expr, String fmtStr, + int firstDay, int firstWeekType) { + DecimalFormat df = ctx.createDecimalFormat( + ctx.getNumericConfig().getNumberFormat(_type)); + return ValueSupport.toValue(df.format(expr.getAsBigDecimal(ctx))); + } + } + + private static class GenPredefNumberFmt extends Fmt + { + @Override + public Value format(EvalContext ctx, Value expr, String fmtStr, + int firstDay, int firstWeekType) { + Value numExpr = expr; + if(!expr.getType().isNumeric()) { + if(expr.getType().isString()) { + BigDecimal bd = DefaultFunctions.maybeGetAsBigDecimal(ctx, expr); + if(bd != null) { + numExpr = ValueSupport.toValue(bd); + } else { + // convert to date to number + Value maybe = DefaultFunctions.maybeGetAsDateTimeValue(ctx, expr); + if(maybe != null) { + numExpr = ValueSupport.toValue(maybe.getAsDouble(ctx)); + } + } + } else { + // convert date to number + numExpr = ValueSupport.toValue(expr.getAsDouble(ctx)); + } + } + return ValueSupport.toValue(numExpr.getAsString(ctx)); + } + } + + private static class ScientificPredefNumberFmt extends Fmt + { + @Override + public Value format(EvalContext ctx, Value expr, String fmtStr, + int firstDay, int firstWeekType) { + NumberFormat df = ctx.createDecimalFormat( + ctx.getNumericConfig().getNumberFormat( + NumericConfig.Type.SCIENTIFIC)); + df = new NumberFormatter.ScientificFormat(df); + return ValueSupport.toValue(df.format(expr.getAsBigDecimal(ctx))); + } + } } diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/NumberFormatter.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatter.java similarity index 70% rename from src/main/java/com/healthmarketscience/jackcess/impl/NumberFormatter.java rename to src/main/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatter.java index 358a0a6..ce251c2 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/NumberFormatter.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatter.java @@ -14,7 +14,7 @@ See the License for the specific language governing permissions and limitations under the License. */ -package com.healthmarketscience.jackcess.impl; +package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; import java.math.MathContext; @@ -32,6 +32,41 @@ public class NumberFormatter { public static final RoundingMode ROUND_MODE = RoundingMode.HALF_EVEN; + /** designates the format of exponent notation used by ScientificFormat */ + public enum NotationType { + /** Scientific notation "E", "E-" (default java behavior) */ + EXP_E_MINUS { + @Override + protected void format(StringBuffer sb, int eIdx) { + // nothing to do + } + }, + /** Scientific notation "E+", "E-" */ + EXP_E_PLUS { + @Override + protected void format(StringBuffer sb, int eIdx) { + maybeInsertExpPlus(sb, eIdx); + } + }, + /** Scientific notation "e", "e-" */ + EXP_e_MINUS { + @Override + protected void format(StringBuffer sb, int eIdx) { + sb.setCharAt(eIdx, 'e'); + } + }, + /** Scientific notation "e+", "e-" */ + EXP_e_PLUS { + @Override + protected void format(StringBuffer sb, int eIdx) { + sb.setCharAt(eIdx, 'e'); + maybeInsertExpPlus(sb, eIdx); + } + }; + + protected abstract void format(StringBuffer sb, int idx); + } + private static final int FLT_SIG_DIGITS = 7; private static final int DBL_SIG_DIGITS = 15; private static final int DEC_SIG_DIGITS = 28; @@ -102,10 +137,18 @@ public class NumberFormatter return _decFmt.format(bd.round(DEC_MATH_CONTEXT)); } + private static ScientificFormat createScientificFormat(int prec) { + DecimalFormat df = new DecimalFormat("0.#E00"); + df.setMaximumIntegerDigits(1); + df.setMaximumFractionDigits(prec); + df.setRoundingMode(ROUND_MODE); + return new ScientificFormat(df); + } + private static final class TypeFormatter { private final DecimalFormat _df = new DecimalFormat("0.#"); - private final BetterDecimalFormat _dfS; + private final ScientificFormat _dfS; private final int _prec; private TypeFormatter(int prec) { @@ -113,7 +156,7 @@ public class NumberFormatter _df.setMaximumIntegerDigits(prec); _df.setMaximumFractionDigits(prec); _df.setRoundingMode(ROUND_MODE); - _dfS = new BetterDecimalFormat("0.#E00", prec); + _dfS = createScientificFormat(prec); } public String format(BigDecimal bd) { @@ -132,18 +175,26 @@ public class NumberFormatter } } - private static final class BetterDecimalFormat extends NumberFormat + private static void maybeInsertExpPlus(StringBuffer sb, int eIdx) { + if(sb.charAt(eIdx + 1) != '-') { + sb.insert(eIdx + 1, '+'); + } + } + + public static class ScientificFormat extends NumberFormat { private static final long serialVersionUID = 0L; - private final DecimalFormat _df; + private final NumberFormat _df; + private final NotationType _type; - private BetterDecimalFormat(String pat, int prec) { - super(); - _df = new DecimalFormat(pat); - _df.setMaximumIntegerDigits(1); - _df.setMaximumFractionDigits(prec); - _df.setRoundingMode(ROUND_MODE); + public ScientificFormat(NumberFormat df) { + this(df, NotationType.EXP_E_PLUS); + } + + public ScientificFormat(NumberFormat df, NotationType type) { + _df = df; + _type = type; } @Override @@ -151,10 +202,7 @@ public class NumberFormatter FieldPosition pos) { StringBuffer sb = _df.format(number, toAppendTo, pos); - int idx = sb.lastIndexOf("E"); - if(sb.charAt(idx + 1) != '-') { - sb.insert(idx + 1, '+'); - } + _type.format(sb, sb.lastIndexOf("E")); return sb; } 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 8c015d8..132b788 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -263,6 +263,65 @@ public class DefaultFunctionsTest extends TestCase assertEval("13:37", "=FormatDateTime(#1/1/1973 1:37:25 PM#,4)"); } + public void testFormat() throws Exception + { + assertEval("12345.6789", "=Format(12345.6789, 'General Number')"); + assertEval("0.12345", "=Format(0.12345, 'General Number')"); + assertEval("-12345.6789", "=Format(-12345.6789, 'General Number')"); + assertEval("-0.12345", "=Format(-0.12345, 'General Number')"); + assertEval("12345.6789", "=Format('12345.6789', 'General Number')"); + assertEval("1678.9", "=Format('1.6789E+3', 'General Number')"); + assertEval("37623.2916666667", "=Format(#01/02/2003 7:00:00 AM#, 'General Number')"); + assertEval("foo", "=Format('foo', 'General Number')"); + + assertEval("12,345.68", "=Format(12345.6789, 'Standard')"); + assertEval("0.12", "=Format(0.12345, 'Standard')"); + assertEval("-12,345.68", "=Format(-12345.6789, 'Standard')"); + assertEval("-0.12", "=Format(-0.12345, 'Standard')"); + + assertEval("12345.68", "=Format(12345.6789, 'Fixed')"); + assertEval("0.12", "=Format(0.12345, 'Fixed')"); + assertEval("-12345.68", "=Format(-12345.6789, 'Fixed')"); + assertEval("-0.12", "=Format(-0.12345, 'Fixed')"); + + assertEval("$12,345.68", "=Format(12345.6789, 'Currency')"); + assertEval("$0.12", "=Format(0.12345, 'Currency')"); + assertEval("($12,345.68)", "=Format(-12345.6789, 'Currency')"); + assertEval("($0.12)", "=Format(-0.12345, 'Currency')"); + + assertEval("1234567.89%", "=Format(12345.6789, 'Percent')"); + assertEval("12.34%", "=Format(0.12345, 'Percent')"); + assertEval("-1234567.89%", "=Format(-12345.6789, 'Percent')"); + assertEval("-12.34%", "=Format(-0.12345, 'Percent')"); + + assertEval("1.23E+4", "=Format(12345.6789, 'Scientific')"); + assertEval("1.23E-1", "=Format(0.12345, 'Scientific')"); + assertEval("-1.23E+4", "=Format(-12345.6789, 'Scientific')"); + assertEval("-1.23E-1", "=Format(-0.12345, 'Scientific')"); + + assertEval("Yes", "=Format(True, 'Yes/No')"); + assertEval("No", "=Format(False, 'Yes/No')"); + assertEval("True", "=Format(True, 'True/False')"); + assertEval("False", "=Format(False, 'True/False')"); + assertEval("On", "=Format(True, 'On/Off')"); + assertEval("Off", "=Format(False, 'On/Off')"); + + assertEval("1/2/2003 7:00:00 AM", "=Format(#01/02/2003 7:00:00 AM#, 'General Date')"); + assertEval("1/2/2003", "=Format(#01/02/2003#, 'General Date')"); + assertEval("7:00:00 AM", "=Format(#7:00:00 AM#, 'General Date')"); + assertEval("1/2/2003 7:00:00 AM", "=Format('37623.2916666667', 'General Date')"); + assertEval("foo", "=Format('foo', 'General Date')"); + + assertEval("Thursday, January 02, 2003", "=Format(#01/02/2003 7:00:00 AM#, 'Long Date')"); + assertEval("02-Jan-03", "=Format(#01/02/2003 7:00:00 AM#, 'Medium Date')"); + assertEval("1/2/2003", "=Format(#01/02/2003 7:00:00 AM#, 'Short Date')"); + assertEval("7:00:00 AM", "=Format(#01/02/2003 7:00:00 AM#, 'Long Time')"); + assertEval("07:00 AM", "=Format(#01/02/2003 7:00:00 AM#, 'Medium Time')"); + assertEval("07:00", "=Format(#01/02/2003 7:00:00 AM#, 'Short Time')"); + assertEval("19:00", "=Format(#01/02/2003 7:00:00 PM#, 'Short Time')"); + + } + public void testNumberFuncs() throws Exception { assertEval(1, "=Abs(1)"); diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java index 17ad3d7..c67dfe7 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/ExpressionatorTest.java @@ -38,7 +38,7 @@ import com.healthmarketscience.jackcess.expr.ParseException; import com.healthmarketscience.jackcess.expr.TemporalConfig; import com.healthmarketscience.jackcess.expr.Value; import com.healthmarketscience.jackcess.impl.BaseEvalContext; -import com.healthmarketscience.jackcess.impl.NumberFormatter; +import com.healthmarketscience.jackcess.impl.expr.NumberFormatter; import junit.framework.TestCase; /** @@ -609,7 +609,7 @@ public class ExpressionatorTest extends TestCase return new DecimalFormat( formatStr, NumericConfig.US_NUMERIC_CONFIG.getDecimalFormatSymbols()); } - + public FunctionLookup getFunctionLookup() { return DefaultFunctions.LOOKUP; } diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/NumberFormatterTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatterTest.java similarity index 99% rename from src/test/java/com/healthmarketscience/jackcess/impl/NumberFormatterTest.java rename to src/test/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatterTest.java index f69dca1..4d26c1b 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/NumberFormatterTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/NumberFormatterTest.java @@ -14,7 +14,7 @@ See the License for the specific language governing permissions and limitations under the License. */ -package com.healthmarketscience.jackcess.impl; +package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; -- 2.39.5