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

3 files changed, 161 insertions, 3 deletions

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<br/>
 	 * MID returns a specific number of
 	 * characters from a text string, starting at the specified position.<p/>