diff options
| -rw-r--r-- | src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java | 193 | ||||
| -rw-r--r-- | src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java | 194 |
2 files changed, 326 insertions, 61 deletions
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 a6c52f0..2258591 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/FormatUtil.java @@ -17,7 +17,10 @@ limitations under the License. package com.healthmarketscience.jackcess.impl.expr; import java.math.BigDecimal; +import java.text.DecimalFormat; +import java.text.FieldPosition; import java.text.NumberFormat; +import java.text.ParsePosition; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.time.format.DateTimeFormatterBuilder; @@ -263,6 +266,7 @@ public class FormatUtil private static final NumberFormatter.NotationType[] NO_EXP_TYPES = new NumberFormatter.NotationType[NUM_NF_FMTS]; + private static final boolean[] NO_FMT_TYPES = new boolean[NUM_NF_FMTS]; private static final class Args @@ -552,7 +556,7 @@ public class FormatUtil private static void addCustomGeneralFormat(String[] fmtStrs, int fmtIdx, StringBuilder sb) { - addCustomNumberFormat(fmtStrs, NO_EXP_TYPES, fmtIdx, sb); + addCustomNumberFormat(fmtStrs, NO_EXP_TYPES, NO_FMT_TYPES, fmtIdx, sb); } private static Fmt parseCustomDateFormat(ExprBuf buf, Args args) { @@ -732,6 +736,7 @@ public class FormatUtil StringBuilder pendingLiteral = new StringBuilder(); NumberFormatter.NotationType[] expTypes = new NumberFormatter.NotationType[NUM_NF_FMTS]; + boolean[] hasFmts = new boolean[NUM_NF_FMTS]; BUF_LOOP: while(buf.hasNext()) { @@ -770,13 +775,14 @@ public class FormatUtil break BUF_LOOP; } flushPendingNumberLiteral(pendingLiteral, sb); - addCustomNumberFormat(fmtStrs, expTypes, fmtIdx++, sb); + addCustomNumberFormat(fmtStrs, expTypes, hasFmts, fmtIdx++, sb); break; default: pendingLiteral.append(c); } break; case FCT_NUMBER: + hasFmts[fmtIdx] = true; switch(c) { case EXP_E_CHAR: int signChar = buf.peekNext(); @@ -818,19 +824,19 @@ public class FormatUtil // fill in remaining formats while(fmtIdx < NUM_NF_FMTS) { flushPendingNumberLiteral(pendingLiteral, sb); - addCustomNumberFormat(fmtStrs, expTypes, fmtIdx++, sb); + addCustomNumberFormat(fmtStrs, expTypes, hasFmts, fmtIdx++, sb); } return new CustomNumberFmt( - createCustomNumberFormat(fmtStrs, expTypes, NF_POS_IDX, args), - createCustomNumberFormat(fmtStrs, expTypes, NF_NEG_IDX, args), - createCustomNumberFormat(fmtStrs, expTypes, NF_ZERO_IDX, args), - createCustomNumberFormat(fmtStrs, expTypes, NF_NULL_IDX, args)); + createCustomNumberFormat(fmtStrs, expTypes, hasFmts, NF_POS_IDX, args), + createCustomNumberFormat(fmtStrs, expTypes, hasFmts, NF_NEG_IDX, args), + createCustomNumberFormat(fmtStrs, expTypes, hasFmts, NF_ZERO_IDX, args), + createCustomNumberFormat(fmtStrs, expTypes, hasFmts, NF_NULL_IDX, args)); } private static void addCustomNumberFormat( String[] fmtStrs, NumberFormatter.NotationType[] expTypes, - int fmtIdx, StringBuilder sb) + boolean[] hasFmts, int fmtIdx, StringBuilder sb) { if(sb.length() == 0) { // do special empty format handling on a per-format-type basis @@ -839,11 +845,13 @@ public class FormatUtil // re-use "pos" format sb.append('-').append(fmtStrs[NF_POS_IDX]); expTypes[NF_NEG_IDX] = expTypes[NF_POS_IDX]; + hasFmts[NF_NEG_IDX] = hasFmts[NF_POS_IDX]; break; case NF_ZERO_IDX: // re-use "pos" format sb.append(fmtStrs[NF_POS_IDX]); expTypes[NF_ZERO_IDX] = expTypes[NF_POS_IDX]; + hasFmts[NF_ZERO_IDX] = hasFmts[NF_POS_IDX]; break; default: // use empty string result @@ -886,22 +894,43 @@ public class FormatUtil private static NumberFormat createCustomNumberFormat( String[] fmtStrs, NumberFormatter.NotationType[] expTypes, - int fmtIdx, Args args) { + boolean[] hasFmts, int fmtIdx, Args args) { String fmtStr = fmtStrs[fmtIdx]; + if(!hasFmts[fmtIdx]) { + // convert the literal string to a dummy number format + if(fmtStr.length() > 0) { + // strip quoting + StringBuilder sb = new StringBuilder(fmtStr) + .deleteCharAt(fmtStr.length() - 1) + .deleteCharAt(0); + if(sb.length() > 0) { + for(int i = 0; i < sb.length(); ++i) { + if(sb.charAt(i) == SINGLE_QUOTE_CHAR) { + // delete next single quote char + sb.deleteCharAt(++i); + } + } + } + fmtStr = sb.toString(); + } + return new LiteralNumberFormat(fmtStr); + } + NumberFormatter.NotationType expType = expTypes[fmtIdx]; + NumberFormat nf = args._ctx.createDecimalFormat(fmtStr); - if(fmtIdx == NF_NEG_IDX) { - // force explicit negative format handling - fmtStr = fmtStr + ";" + fmtStr; + DecimalFormat df = (DecimalFormat)nf; + if(df.getMaximumFractionDigits() > 0) { + // if the decimal is included in the format, access always shows it + df.setDecimalSeparatorAlwaysShown(true); } - NumberFormat df = args._ctx.createDecimalFormat(fmtStr); if(expType != null) { - df = new NumberFormatter.ScientificFormat(df, expType); + nf = new NumberFormatter.ScientificFormat(nf, expType); } - return df; + return nf; } private static Fmt parseCustomTextFormat(ExprBuf buf, Args args) { @@ -1088,7 +1117,7 @@ public class FormatUtil private static String parseColorString(ExprBuf buf) { return ExpressionTokenizer.parseStringUntil( - buf, END_COLOR_CHAR, START_COLOR_CHAR, false); + buf, START_COLOR_CHAR, END_COLOR_CHAR, false); } private static void fillInPartialPrefixes() { @@ -1369,7 +1398,29 @@ public class FormatUtil } } - private static final class CustomNumberFmt implements Fmt + private static abstract class BaseCustomNumberFmt implements Fmt + { + @Override + public Value format(Args args) { + if(args._expr.isNull()) { + return formatNull(args); + } + + BigDecimal bd = args.getAsBigDecimal(); + int cmp = BigDecimal.ZERO.compareTo(bd); + + return ((cmp < 0) ? formatPos(bd, args) : + ((cmp > 0) ? formatNeg(bd, args) : + formatZero(bd, args))); + } + + protected abstract Value formatNull(Args args); + protected abstract Value formatPos(BigDecimal bd, Args args); + protected abstract Value formatNeg(BigDecimal bd, Args args); + protected abstract Value formatZero(BigDecimal bd, Args args); + } + + private static final class CustomNumberFmt extends BaseCustomNumberFmt { private final NumberFormat _posFmt; private final NumberFormat _negFmt; @@ -1384,36 +1435,41 @@ public class FormatUtil _nullFmt = nullFmt; } - @Override - public Value format(Args args) { - if(args._expr.isNull()) { - return ValueSupport.toValue(_nullFmt.format(BigDecimal.ZERO)); + private Value formatMaybeZero(BigDecimal bd, NumberFormat fmt) { + // in theory we want to use the given format. however, if, due to + // rounding, we end up with a number equivalent to zero, then we fall + // back to the zero format + int maxDecDigits = fmt.getMaximumFractionDigits(); + if(maxDecDigits < bd.scale()) { + bd = bd.setScale(maxDecDigits, NumberFormatter.ROUND_MODE); } - BigDecimal bd = args.getAsBigDecimal(); - int cmp = BigDecimal.ZERO.compareTo(bd); - - NumberFormat fmt = null; - if(cmp > 0) { - // in theory we want to use the negative format. however, if, due to - // rounding, we end up with a number equivalent to zero, then we fall - // back to the zero format - fmt = _negFmt; - int maxDecDigits = fmt.getMaximumFractionDigits(); - bd = bd.negate().setScale(maxDecDigits, NumberFormatter.ROUND_MODE); - if(BigDecimal.ZERO.equals(bd)) { - // fall back to zero format - fmt = _zeroFmt; - } - } else { - // positive or zero number - fmt = ((cmp < 0) ? _posFmt : _zeroFmt); + if(BigDecimal.ZERO.compareTo(bd) == 0) { + // fall back to zero format + fmt = _zeroFmt; } return ValueSupport.toValue(fmt.format(bd)); } + + @Override + protected Value formatNull(Args args) { + return ValueSupport.toValue(_nullFmt.format(BigDecimal.ZERO)); + } + @Override + protected Value formatPos(BigDecimal bd, Args args) { + return formatMaybeZero(bd, _posFmt); + } + @Override + protected Value formatNeg(BigDecimal bd, Args args) { + return formatMaybeZero(bd.negate(), _negFmt); + } + @Override + protected Value formatZero(BigDecimal bd, Args args) { + return ValueSupport.toValue(_zeroFmt.format(bd)); + } } - private static final class CustomGeneralFmt implements Fmt + private static final class CustomGeneralFmt extends BaseCustomNumberFmt { private final Value _posVal; private final Value _negVal; @@ -1429,17 +1485,56 @@ public class FormatUtil } @Override - public Value format(Args args) { - if(args._expr.isNull()) { - return _nullVal; - } - BigDecimal bd = args.getAsBigDecimal(); - int cmp = BigDecimal.ZERO.compareTo(bd); - - return ((cmp < 0) ? _posVal : - ((cmp > 0) ? _negVal : _zeroVal)); + protected Value formatNull(Args args) { + return _nullVal; + } + @Override + protected Value formatPos(BigDecimal bd, Args args) { + return _posVal; + } + @Override + protected Value formatNeg(BigDecimal bd, Args args) { + return _negVal; + } + @Override + protected Value formatZero(BigDecimal bd, Args args) { + return _zeroVal; } } + private static final class LiteralNumberFormat extends NumberFormat + { + private static final long serialVersionUID = 0L; + + private final String _str; + + private LiteralNumberFormat(String str) { + _str = str; + } + + @Override + public StringBuffer format(Object number, StringBuffer toAppendTo, + FieldPosition pos) + { + return toAppendTo.append(_str); + } + + @Override + public StringBuffer format(double number, StringBuffer toAppendTo, + FieldPosition pos) { + throw new UnsupportedOperationException(); + } + + @Override + public Number parse(String source, ParsePosition parsePosition) { + throw new UnsupportedOperationException(); + } + + @Override + public StringBuffer format(long number, StringBuffer toAppendTo, + FieldPosition pos) { + throw new UnsupportedOperationException(); + } + } } 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 b9a764d..24f3c5b 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -21,6 +21,7 @@ import java.time.LocalDateTime; import java.util.Calendar; import com.healthmarketscience.jackcess.expr.EvalException; +import junit.framework.AssertionFailedError; import junit.framework.TestCase; import static com.healthmarketscience.jackcess.impl.expr.ExpressionatorTest.eval; import static com.healthmarketscience.jackcess.impl.expr.ExpressionatorTest.toBD; @@ -251,6 +252,7 @@ public class DefaultFunctionsTest extends TestCase assertEval("-.123%", "=FormatPercent(-0.0012345,3,False)"); assertEval("$12,345.00", "=FormatCurrency(12345)"); + assertEval("($12,345.00)", "=FormatCurrency(-12345)"); assertEval("-$12.34", "=FormatCurrency(-12.345,-1,True,False)"); assertEval("$12", "=FormatCurrency(12.345,0,True,True)"); assertEval("($.123)", "=FormatCurrency(-0.12345,3,False)"); @@ -317,6 +319,7 @@ public class DefaultFunctionsTest extends TestCase 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("", "=Format('', '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')"); @@ -340,22 +343,189 @@ public class DefaultFunctionsTest extends TestCase assertEval("4 7:13:00 AM ttt this 'is' \"text\"", "=Format(#7:13:00 AM#, \"q c ttt \"\"this 'is' \"\"\"\"text\"\"\"\"\"\"\")"); assertEval("12/29/1899", "=Format('true', 'c')"); - assertEval("Tuesday, 00 Jan 2, 21:36:00", - "=Format('3.9', 'dddd, yy mmm d, hh:nn:ss')"); - assertEval("Tuesday, 00 Jan 01 2, 09:36:00 PM", - "=Format('3.9', 'dddd, yy mmm mm d, hh:nn:ss AMPM')"); + assertEval("Tuesday, 00 Jan 2, 21:36:00 Y", + "=Format('3.9', '*~dddd, yy mmm d, hh:nn:ss \\Y[Yellow]')"); + assertEval("Tuesday, 00 Jan 01/2, 09:36:00 PM", + "=Format('3.9', 'dddd, yy mmm mm/d, hh:nn:ss AMPM')"); assertEval("foo", "=Format('foo', 'dddd, yy mmm mm d, hh:nn:ss AMPM')"); - assertEval("p13.00blah", - "=Format('13', '\"p\"#.00#\"blah\"')"); - assertEval("-p13.00blah", - "=Format('-13', '\"p\"#.00#\"blah\";(\"p\"#.00#\"blah\")')"); - // assertEval("-p13.00blah", - // "=Format('-13', '\"p\"#.00#\"blah\"')"); - - + assertEvalFormat("';\\y;\\n'", + "foo", "'foo'", + "", "''", + "y", "True", + "n", "'0'", + "", "Null"); + + assertEvalFormat("';\"y\";!\\n;*~\\z[Blue];'", + "foo", "'foo'", + "", "''", + "y", "True", + "n", "'0'", + "z", "Null"); + + assertEvalFormat("'\"p\"#.00#\"blah\"'", + "p13.00blah", "13", + "-p13.00blah", "-13", + "p.00blah", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'\"p\"#.00#\"blah\";(\"p\"#.00#\"blah\")'", + "p13.00blah", "13", + "(p13.00blah)", "-13", + "p.00blah", "0", + "(p1.00blah)", "True", + "p.00blah", "'false'", + "p37623.292blah", "#01/02/2003 7:00:00 AM#", + "p37623.292blah", "'01/02/2003 7:00:00 AM'", + "NotANumber", "'NotANumber'", + "", "''", + "", "Null"); + + assertEvalFormat("'\"p\"#.00#\"blah\";!(\"p\"#.00#\"blah\")[Red];\"zero\"'", + "p13.00blah", "13", + "(p13.00blah)", "-13", + "zero", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'\"p\"#.00#\"blah\";(\"p\"#.00#\"blah\");\"zero\";\"yuck\"'", + "p13.00blah", "13", + "(p13.00blah)", "-13", + "zero", "0", + "", "''", + "yuck", "Null"); + + assertEvalFormat("'0.##;(0.###);\"zero\";\"yuck\"'", + "0.03", "0.03", + "zero", "0.003", + "(0.003)", "-0.003", + "zero", "-0.0003"); + + // FIXME, need to handle rounding w/ negatives + // FIXME, need to handle dangling decimal + // assertEvalFormat("'0.##;(0.###E+0)'", + // "0.03", "0.03", + // "(0.003)", "-0.0003", + + assertEvalFormat("'0.'", + "13.", "13", + "0.", "0.003", + "-45.", "-45", + "0.", "-0.003", + "0.", "0" + ); + + assertEvalFormat("'0.#'", + "13.", "13", + "0.3", "0.3", + "0.", "0.003", + "-45.", "-45", + "0.", "-0.003", + "0.", "0" + ); + + assertEvalFormat("'0'", + "13", "13", + "0", "0.003", + "-45", "-45", + "0", "-0.003", + "0", "0" + ); + + assertEvalFormat("'#'", + "13", "13", + "0", "0.003", + "-45", "-45", + "0", "-0.003" + // FIXME + // "", "0" + ); + + assertEvalFormat("'$0.0#'", + "$213.0", "213"); + + assertEvalFormat("'@'", + "foo", "'foo'", + "-13", "-13", + "0", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'>@'", + "FOO", "'foo'", + "-13", "-13", + "0", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'<@'", + "foo", "'FOO'", + "-13", "-13", + "0", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'!>@'", + "O", "'foo'", + "3", "-13", + "0", "0", + "", "''", + "", "Null"); + + assertEvalFormat("'!>@[Red];\"empty\"'", + "O", "'foo'", + "3", "-13", + "0", "0", + "empty", "''", + "empty", "Null"); + + assertEvalFormat("'><@'", + "fOo", "'fOo'"); + + assertEvalFormat("'\\x@@@&&&\\y'", + "x fy", "'f'", + "x fooy", "'foo'", + "x foobay", "'fooba'", + "xfoobarybaz", "'foobarbaz'" + ); + + assertEvalFormat("'!\\x@@@&&&\\y'", + "xf y", "'f'", + "xfooy", "'foo'", + "xfoobay", "'fooba'", + "xbarbazy", "'foobarbaz'" + ); + + assertEvalFormat("'\\x&&&@@@\\y'", + "x fy", "'f'", + "xfooy", "'foo'", + "xfoobay", "'fooba'", + "xfoobarybaz", "'foobarbaz'" + ); + + assertEvalFormat("'!\\x&&&@@@\\y'", + "xf y", "'f'", + "xfoo y", "'foo'", + "xfooba y", "'fooba'", + "xbarbazy", "'foobarbaz'" + ); + } + private static void assertEvalFormat(String fmtStr, String... testStrs) { + for(int i = 0; i < testStrs.length; i+=2) { + String expected = testStrs[i]; + String val = testStrs[i + 1]; + + try { + assertEval(expected, + "=Format(" + val + ", " + fmtStr + ")"); + } catch(AssertionFailedError afe) { + throw new AssertionFailedError("Input " + val + ": " + + afe.getMessage()); + } + } } public void testNumberFuncs() throws Exception |