From ddf6cf7fec44416b8fb1dd8ac6b5913c32edfe40 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Wed, 19 Jan 2011 12:30:18 +0000 Subject: added implementations for CLEAN(), CHAR() and ADDRESS(), see Bugzilla 50607 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1060788 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/eval/FunctionEval.java | 5 +- .../apache/poi/ss/formula/functions/Address.java | 103 +++++++++++++++++++++ .../poi/ss/formula/functions/TextFunction.java | 56 ++++++++++- 3 files changed, 161 insertions(+), 3 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/functions/Address.java (limited to 'src/java/org') diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java index a7bc15cee6..7c84a5860b 100644 --- a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -130,6 +130,7 @@ public final class FunctionEval { retval[109] = NumericFunction.LOG; + retval[111] = TextFunction.CHAR; retval[112] = TextFunction.LOWER; retval[113] = TextFunction.UPPER; @@ -148,7 +149,7 @@ public final class FunctionEval { retval[130] = new T(); retval[ID.INDIRECT] = null; // Indirect.evaluate has different signature - + retval[162] = TextFunction.CLEAN; //Aniket Banerjee retval[169] = new Counta(); retval[183] = AggregateFunction.PRODUCT; @@ -161,7 +162,7 @@ public final class FunctionEval { retval[212] = NumericFunction.ROUNDUP; retval[213] = NumericFunction.ROUNDDOWN; - + retval[219] = new Address(); //Aniket Banerjee retval[220] = new Days360(); retval[221] = new Today(); diff --git a/src/java/org/apache/poi/ss/formula/functions/Address.java b/src/java/org/apache/poi/ss/formula/functions/Address.java new file mode 100644 index 0000000000..288a88edc9 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Address.java @@ -0,0 +1,103 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ +package org.apache.poi.ss.formula.functions; + +import org.apache.poi.ss.formula.SheetNameFormatter; +import org.apache.poi.ss.formula.eval.*; +import org.apache.poi.ss.util.CellReference; + +/** + * Creates a text reference as text, given specified row and column numbers. + * + * @author Aniket Banerjee (banerjee@google.com) + */ +public class Address implements Function { + public static final int REF_ABSOLUTE = 1; + public static final int REF_ROW_ABSOLUTE_COLUMN_RELATIVE = 2; + public static final int REF_ROW_RELATIVE_RELATIVE_ABSOLUTE = 3; + public static final int REF_RELATIVE = 4; + + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, + int srcColumnIndex) { + if(args.length < 2 || args.length > 5) { + return ErrorEval.VALUE_INVALID; + } + try { + boolean pAbsRow, pAbsCol; + + int row = (int)NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex); + int col = (int)NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex); + + int refType; + if(args.length > 2){ + refType = (int)NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex); + } else { + refType = REF_ABSOLUTE; + } + switch (refType){ + case REF_ABSOLUTE: + pAbsRow = true; + pAbsCol = true; + break; + case REF_ROW_ABSOLUTE_COLUMN_RELATIVE: + pAbsRow = true; + pAbsCol = false; + break; + case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE: + pAbsRow = false; + pAbsCol = true; + break; + case REF_RELATIVE: + pAbsRow = false; + pAbsCol = false; + break; + default: + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + boolean a1; + if(args.length > 3){ + ValueEval ve = OperandResolver.getSingleValue(args[3], srcRowIndex, srcColumnIndex); + // TODO R1C1 style is not yet supported + a1 = ve == MissingArgEval.instance ? true : OperandResolver.coerceValueToBoolean(ve, false); + } else { + a1 = true; + } + + String sheetName; + if(args.length == 5){ + ValueEval ve = OperandResolver.getSingleValue(args[4], srcRowIndex, srcColumnIndex); + sheetName = ve == MissingArgEval.instance ? null : OperandResolver.coerceValueToString(ve); + } else { + sheetName = null; + } + + CellReference ref = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol); + StringBuffer sb = new StringBuffer(32); + if(sheetName != null) { + SheetNameFormatter.appendFormat(sb, sheetName); + sb.append('!'); + } + sb.append(ref.formatAsString()); + + return new StringEval(sb.toString()); + + } catch (EvaluationException e){ + return e.getErrorEval(); + } + } +} diff --git a/src/java/org/apache/poi/ss/formula/functions/TextFunction.java b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java index dc57b4277b..86e1e32687 100644 --- a/src/java/org/apache/poi/ss/formula/functions/TextFunction.java +++ b/src/java/org/apache/poi/ss/formula/functions/TextFunction.java @@ -83,6 +83,25 @@ public abstract class TextFunction implements Function { protected abstract ValueEval evaluate(String arg); } + /** + * Returns the character specified by a number. + */ + public static final Function CHAR = new Fixed1ArgFunction() { + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + int arg; + try { + arg = evaluateIntArg(arg0, srcRowIndex, srcColumnIndex); + if (arg < 0 || arg >= 256) { + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + + } catch (EvaluationException e) { + return e.getErrorEval(); + } + return new StringEval(String.valueOf((char)arg)); + } + }; + public static final Function LEN = new SingleArgTextFunc() { protected ValueEval evaluate(String arg) { return new NumberEval(arg.length()); @@ -109,8 +128,43 @@ public abstract class TextFunction implements Function { return new StringEval(arg.trim()); } }; - + /** + * An implementation of the CLEAN function: + * In Excel, the Clean function removes all non-printable characters from a string. + * + * Author: Aniket Banerjee(banerjee@google.com) + */ + public static final Function CLEAN = new SingleArgTextFunc() { + protected ValueEval evaluate(String arg) { + StringBuilder result = new StringBuilder(); + for (int i = 0; i < arg.length(); i++) { + char c = arg.charAt(i); + if (isPrintable(c)) { + result.append(c); + } + } + return new StringEval(result.toString()); + } + + /** + * From Excel docs: The CLEAN function was designed to remove the first 32 nonprinting characters + * in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, + * there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, + * the CLEAN function does not remove these additional nonprinting characters. To do this task, + * use the SUBSTITUTE function to replace the higher value Unicode characters with the 7-bit ASCII + * characters for which the TRIM and CLEAN functions were designed. + * + * @param c the character to test + * @return whether the character is printable + */ + private boolean isPrintable(char c){ + int charCode = (int)c ; + return charCode >= 32; + } + }; + + /** * An implementation of the MID function
* MID returns a specific number of * characters from a text string, starting at the specified position.

-- cgit v1.2.3