From 11eb7c710ca76bb76d9856a7359176bed50f1300 Mon Sep 17 00:00:00 2001 From: Avik Sengupta Date: Sun, 4 May 2003 18:22:09 +0000 Subject: [PATCH] Addition of GreaterThan and LessThan in formulas (IF Functions) Originally submitted by Cameron Riley (PR 16392). Thanks. Added simple tests at high and low level, more complicated cases to be tested <= and >= probably still wont work! Had to apply diff by hand, one horrible line at a time (yikes!!) git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@353081 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/model/FormulaParser.java | 158 +++++++-------- .../hssf/record/formula/GreaterThanPtg.java | 175 +++++++++++++++++ .../poi/hssf/record/formula/LessThanPtg.java | 185 ++++++++++++++++++ .../apache/poi/hssf/record/formula/Ptg.java | 8 + .../poi/hssf/model/TestFormulaParser.java | 13 +- .../poi/hssf/usermodel/TestFormulas.java | 30 +++ 6 files changed, 490 insertions(+), 79 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/GreaterThanPtg.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/LessThanPtg.java diff --git a/src/java/org/apache/poi/hssf/model/FormulaParser.java b/src/java/org/apache/poi/hssf/model/FormulaParser.java index 27debdc1f4..32e6cd4af2 100644 --- a/src/java/org/apache/poi/hssf/model/FormulaParser.java +++ b/src/java/org/apache/poi/hssf/model/FormulaParser.java @@ -61,27 +61,9 @@ import java.util.Iterator; import java.util.LinkedList; import java.util.List; -import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; -import org.apache.poi.hssf.record.formula.AddPtg; -import org.apache.poi.hssf.record.formula.Area3DPtg; -import org.apache.poi.hssf.record.formula.AreaPtg; -import org.apache.poi.hssf.record.formula.AttrPtg; -import org.apache.poi.hssf.record.formula.BoolPtg; -import org.apache.poi.hssf.record.formula.ConcatPtg; -import org.apache.poi.hssf.record.formula.DividePtg; -import org.apache.poi.hssf.record.formula.EqualPtg; -import org.apache.poi.hssf.record.formula.FuncVarPtg; -import org.apache.poi.hssf.record.formula.IntPtg; -import org.apache.poi.hssf.record.formula.MultiplyPtg; -import org.apache.poi.hssf.record.formula.NumberPtg; -import org.apache.poi.hssf.record.formula.OperationPtg; -import org.apache.poi.hssf.record.formula.ParenthesisPtg; -import org.apache.poi.hssf.record.formula.PowerPtg; -import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.hssf.record.formula.Ref3DPtg; -import org.apache.poi.hssf.record.formula.ReferencePtg; -import org.apache.poi.hssf.record.formula.StringPtg; -import org.apache.poi.hssf.record.formula.SubtractPtg; +//import PTG's .. since we need everything, import * +import org.apache.poi.hssf.record.formula.*; + import org.apache.poi.hssf.util.SheetReferences; @@ -98,6 +80,7 @@ import org.apache.poi.hssf.util.SheetReferences; * @author Avik Sengupta * @author Andrew C. oliver (acoliver at apache dot org) * @author Eric Ladner (eladner at goldinc dot com) + * @author Cameron Riley (criley at ekmail.com) */ public class FormulaParser { @@ -154,7 +137,7 @@ public class FormulaParser { return; } look=formulaString.charAt(pointer++); - //System.out.println("Got char: "+Look); + //System.out.println("Got char: "+ look); } @@ -380,18 +363,17 @@ public class FormulaParser { } private int getPtgSize(int start, int end) { - int count = 0; - int index = start; - Iterator ptgIterator = tokens.listIterator(index); - while (ptgIterator.hasNext() && index <= end) { - Ptg ptg = (Ptg)ptgIterator.next(); - count+=ptg.getSize(); - index++; - } - - return count; + int count = 0; + int index = start; + Iterator ptgIterator = tokens.listIterator(index); + while (ptgIterator.hasNext() && index <= end) { + Ptg ptg = (Ptg)ptgIterator.next(); + count+=ptg.getSize(); + index++; + } + + return count; } - /** * Generates the variable function ptg for the formula. *

@@ -403,19 +385,19 @@ public class FormulaParser { private Ptg getFunction(String name,byte numArgs) { Ptg retval = null; - if (name.equals("IF")) { - retval = new FuncVarPtg(AbstractFunctionPtg.ATTR_NAME, numArgs); - - //simulated pop, no bounds checking because this list better be populated by function() - List argumentPointers = (List)this.functionTokens.get(0); - - + if (name.equals("IF")) { + retval = new FuncVarPtg(AbstractFunctionPtg.ATTR_NAME, numArgs); + + //simulated pop, no bounds checking because this list better be populated by function() + List argumentPointers = (List)this.functionTokens.get(0); + + AttrPtg ifPtg = new AttrPtg(); - ifPtg.setData((short)7); //mirroring excel output - ifPtg.setOptimizedIf(true); - + ifPtg.setData((short)7); //mirroring excel output + ifPtg.setOptimizedIf(true); + if (argumentPointers.size() != 2 && argumentPointers.size() != 3) { - throw new IllegalArgumentException("["+argumentPointers.size()+"] Arguments Found - An IF formula requires 2 or 3 arguments. IF(CONDITION, TRUE_VALUE, FALSE_VALUE [OPTIONAL]"); + throw new IllegalArgumentException("["+argumentPointers.size()+"] Arguments Found - An IF formula requires 2 or 3 arguments. IF(CONDITION, TRUE_VALUE, FALSE_VALUE [OPTIONAL]"); } //Biffview of an IF formula record indicates the attr ptg goes after the condition ptgs and are @@ -423,50 +405,50 @@ public class FormulaParser { //The beginning first argument pointer is the last ptg of the condition int ifIndex = tokens.indexOf(argumentPointers.get(0))+1; tokens.add(ifIndex, ifPtg); - + //we now need a goto ptgAttr to skip to the end of the formula after a true condition //the true condition is should be inserted after the last ptg in the first argument - - int gotoIndex = tokens.indexOf(argumentPointers.get(1))+1; - - AttrPtg goto1Ptg = new AttrPtg(); - goto1Ptg.setGoto(true); - - - tokens.add(gotoIndex, goto1Ptg); + int gotoIndex = tokens.indexOf(argumentPointers.get(1))+1; + + AttrPtg goto1Ptg = new AttrPtg(); + goto1Ptg.setGoto(true); + + + tokens.add(gotoIndex, goto1Ptg); - if (numArgs > 2) { //only add false jump if there is a false condition - //second goto to skip past the function ptg - AttrPtg goto2Ptg = new AttrPtg(); - goto2Ptg.setGoto(true); - goto2Ptg.setData((short)(retval.getSize()-1)); - //Page 472 of the Microsoft Excel Developer's kit states that: - //The b(or w) field specifies the number byes (or words to skip, minus 1 - - tokens.add(goto2Ptg); //this goes after all the arguments are defined + if (numArgs > 2) { //only add false jump if there is a false condition + + //second goto to skip past the function ptg + AttrPtg goto2Ptg = new AttrPtg(); + goto2Ptg.setGoto(true); + goto2Ptg.setData((short)(retval.getSize()-1)); + //Page 472 of the Microsoft Excel Developer's kit states that: + //The b(or w) field specifies the number byes (or words to skip, minus 1 + + tokens.add(goto2Ptg); //this goes after all the arguments are defined } //data portion of the if ptg points to the false subexpression (Page 472 of MS Excel Developer's kit) //count the number of bytes after the ifPtg to the False Subexpression //doesn't specify -1 in the documentation - ifPtg.setData((short)(getPtgSize(ifIndex+1, gotoIndex))); + ifPtg.setData((short)(getPtgSize(ifIndex+1, gotoIndex))); //count all the additional (goto) ptgs but dont count itself - int ptgCount = this.getPtgSize(gotoIndex)-goto1Ptg.getSize()+retval.getSize(); - if (ptgCount > (int)Short.MAX_VALUE) { - throw new RuntimeException("Ptg Size exceeds short when being specified for a goto ptg in an if"); - } - - goto1Ptg.setData((short)(ptgCount-1)); - + int ptgCount = this.getPtgSize(gotoIndex)-goto1Ptg.getSize()+retval.getSize(); + if (ptgCount > (int)Short.MAX_VALUE) { + throw new RuntimeException("Ptg Size exceeds short when being specified for a goto ptg in an if"); + } + + goto1Ptg.setData((short)(ptgCount-1)); + } else { - - retval = new FuncVarPtg(name,numArgs); + + retval = new FuncVarPtg(name,numArgs); } - - return retval; + + return retval; } /** get arguments to a function */ @@ -543,13 +525,16 @@ public class FormulaParser { /** Parse and Translate a Math Term */ private void Term(){ Factor(); - while (look == '*' || look == '/' || look == '^' || look == '&' || look == '=' ) { + while (look == '*' || look == '/' || look == '^' || look == '&' || + look == '=' || look == '>' || look == '<' ) { ///TODO do we need to do anything here?? if (look == '*') Multiply(); if (look == '/') Divide(); if (look == '^') Power(); if (look == '&') Concat(); if (look == '=') Equal(); + if (look == '>') GreaterThan(); + if (look == '<') LessThan(); } } @@ -579,8 +564,8 @@ public class FormulaParser { Match('-'); Term(); tokens.add(new SubtractPtg()); - } - + } + private void Power() { Match('^'); Term(); @@ -600,11 +585,27 @@ public class FormulaParser { if (look == '-') Subtract(); if (look == '*') Multiply(); if (look == '/') Divide(); + if (look == '>') GreaterThan(); + if (look == '<') LessThan(); } addArgumentPointer(); } + /** Recognize and Translate a Greater Than */ + private void GreaterThan() { + Match('>'); + Term(); + tokens.add(new GreaterThanPtg()); + } + + /** Recognize and Translate a Less Than */ + private void LessThan() { + Match('<'); + Term(); + tokens.add(new LessThanPtg()); + } + //{--------------------------------------------------------------} //{ Parse and Translate an Assignment Statement } @@ -826,7 +827,8 @@ end; * Useful for testing */ public String toString() { - SheetReferences refs = book.getSheetReferences(); + SheetReferences refs = null; + if (book!=null) book.getSheetReferences(); StringBuffer buf = new StringBuffer(); for (int i=0;i. + */ + +/* + * GreaterThanPtg.java + * + * Created on January 23, 2003, 9:47 AM + */ +package org.apache.poi.hssf.record.formula; + +import java.util.List; + +import org.apache.poi.hssf.util.SheetReferences; + +/** + * Greater than operator PTG ">" + * @author Cameron Riley (criley at ekmail.com) + */ +public class GreaterThanPtg + extends OperationPtg +{ + public final static int SIZE = 1; + public final static byte sid = 0x0D; + private final static String GREATERTHAN = ">"; + + /** + * Constructor. Creates new GreaterThanPtg + */ + public GreaterThanPtg() + { + //deliberately empty + } + + /** + * Constructor. Create a new GreaterThanPtg. + * @param data the byte array to have the PTG added to + * @param offset the offset to the PTG to. + */ + public GreaterThanPtg(byte [] data, int offset) + { + //deliberately empty + } + + /** + * Write the sid to an array + * @param array the array of bytes to write the sid to + * @param offset the offset to add the sid to + */ + public void writeBytes(byte [] array, int offset) + { + array[ offset + 0 ] = sid; + } + + /** + * Get the size of the sid + * @return int the size of the sid in terms of byte additions to an array + */ + public int getSize() + { + return SIZE; + } + + /** + * Get the type of PTG for Greater Than + * @return int the identifier for the type + */ + public int getType() + { + return TYPE_BINARY; + } + + /** + * Get the number of operands for the Less than operator + * @return int the number of operands + */ + public int getNumberOfOperands() + { + return 2; + } + + /** + * Implementation of method from Ptg + * @param refs the Sheet References + */ + public String toFormulaString(SheetReferences refs) + { + return this.GREATERTHAN; + } + + /** + * Implementation of method from OperationsPtg + * @param operands a String array of operands + * @return String the Formula as a String + */ + public String toFormulaString(String[] operands) + { + StringBuffer buffer = new StringBuffer(); + + buffer.append(operands[ 0 ]); + buffer.append(this.GREATERTHAN); + buffer.append(operands[ 1 ]); + return buffer.toString(); + } + + /** + * Get the default operands class value + * @return byte the Ptg Class Value as a byte from the Ptg Parent object + */ + public byte getDefaultOperandClass() + { + return Ptg.CLASS_VALUE; + } + + /** + * Implementation of clone method from Object + * @return Object a clone of this class as an Object + */ + public Object clone() + { + return new GreaterThanPtg(); + } +} + + diff --git a/src/java/org/apache/poi/hssf/record/formula/LessThanPtg.java b/src/java/org/apache/poi/hssf/record/formula/LessThanPtg.java new file mode 100644 index 0000000000..cab99be9c3 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/LessThanPtg.java @@ -0,0 +1,185 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2003 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +/* + * LessThanPtg.java + * + * Created on January 23, 2003, 9:47 AM + */ +package org.apache.poi.hssf.record.formula; + +//JDK +import java.util.List; + +//POI +import org.apache.poi.hssf.util.SheetReferences; + +/** + * Less than operator PTG "<". The SID is taken from the + * Openoffice.orgs Documentation of the Excel File Format, + * Table 3.5.7 + * @author Cameron Riley (criley at ekmail.com) + */ +public class LessThanPtg + extends OperationPtg +{ + /** the size of the Ptg */ + public final static int SIZE = 1; + + /** the sid for the less than operator as hex */ + public final static byte sid = 0x09; + + /** identifier for LESS THAN char */ + private final static String LESSTHAN = "<"; + + /** + * Constructor. Creates new LessThanPtg + */ + public LessThanPtg() + { + //deliberately empty + } + + /** + * Constructor. Create a new LessThanPtg. + * @param data the byte array to have the PTG added to + * @param offset the offset to the PTG to. + */ + public LessThanPtg(byte [] data, int offset) + { + //deliberately empty + } + + /** + * Write the sid to an array + * @param array the array of bytes to write the sid to + * @param offset the offset to add the sid to + */ + public void writeBytes(byte[] array, int offset) + { + array[ offset + 0 ] = sid; + } + + /** + * Get the size of the sid + * @return int the size of the sid in terms of byte additions to an array + */ + public int getSize() + { + return SIZE; + } + + /** + * Get the type of PTG for Less Than + * @return int the identifier for the type + */ + public int getType() + { + return TYPE_BINARY; + } + + /** + * Get the number of operands for the Less than operator + * @return int the number of operands + */ + public int getNumberOfOperands() + { + return 2; + } + + /** + * Implementation of method from Ptg + * @param refs the Sheet References + */ + public String toFormulaString(SheetReferences refs) + { + return this.LESSTHAN; + } + + /** + * Implementation of method from OperationsPtg + * @param operands a String array of operands + * @return String the Formula as a String + */ + public String toFormulaString(String[] operands) + { + StringBuffer buffer = new StringBuffer(); + buffer.append(operands[ 0 ]); + buffer.append(this.LESSTHAN); + buffer.append(operands[ 1 ]); + return buffer.toString(); + } + + /** + * Get the default operands class value + * @return byte the Ptg Class Value as a byte from the Ptg Parent object + */ + public byte getDefaultOperandClass() + { + return Ptg.CLASS_VALUE; + } + + /** + * Implementation of clone method from Object + * @return Object a clone of this class as an Object + */ + public Object clone() + { + return new LessThanPtg(); + } + +} + + + diff --git a/src/java/org/apache/poi/hssf/record/formula/Ptg.java b/src/java/org/apache/poi/hssf/record/formula/Ptg.java index ec07884ba0..8cd18b0e19 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Ptg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Ptg.java @@ -177,6 +177,14 @@ public abstract class Ptg retval = new EqualPtg(data, offset); break; + case GreaterThanPtg.sid: + retval = new GreaterThanPtg(data, offset); + break; + + case LessThanPtg.sid: + retval = new LessThanPtg(data, offset); + break; + case ConcatPtg.sid : retval = new ConcatPtg(data, offset); break; diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index 45f1b67157..339fdcd864 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -249,7 +249,18 @@ public class TestFormulaParser extends TestCase { //the PTG order isn't 100% correct but it still works - dmui - } + } + + public void testSimpleLogical() { + FormulaParser fp=new FormulaParser("IF(A1