formulaparser.java

来自「EXCEL read and write」· Java 代码 · 共 1,059 行 · 第 1/3 页

/* ====================================================================
   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;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.hssf.record.constant.ErrorConstant;
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.ArrayPtg;
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.ErrPtg;
import org.apache.poi.hssf.record.formula.FuncPtg;
import org.apache.poi.hssf.record.formula.FuncVarPtg;
import org.apache.poi.hssf.record.formula.GreaterEqualPtg;
import org.apache.poi.hssf.record.formula.GreaterThanPtg;
import org.apache.poi.hssf.record.formula.IntPtg;
import org.apache.poi.hssf.record.formula.LessEqualPtg;
import org.apache.poi.hssf.record.formula.LessThanPtg;
import org.apache.poi.hssf.record.formula.MissingArgPtg;
import org.apache.poi.hssf.record.formula.MultiplyPtg;
import org.apache.poi.hssf.record.formula.NamePtg;
import org.apache.poi.hssf.record.formula.NameXPtg;
import org.apache.poi.hssf.record.formula.NotEqualPtg;
import org.apache.poi.hssf.record.formula.NumberPtg;
import org.apache.poi.hssf.record.formula.ParenthesisPtg;
import org.apache.poi.hssf.record.formula.PercentPtg;
import org.apache.poi.hssf.record.formula.PowerPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.RangePtg;
import org.apache.poi.hssf.record.formula.Ref3DPtg;
import org.apache.poi.hssf.record.formula.RefPtg;
import org.apache.poi.hssf.record.formula.StringPtg;
import org.apache.poi.hssf.record.formula.SubtractPtg;
import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
import org.apache.poi.hssf.record.formula.function.FunctionMetadata;
import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
import org.apache.poi.hssf.util.AreaReference;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.CellReference.NameType;

/**
 * This class parses a formula string into a List of tokens in RPN order.
 * Inspired by
 *           Lets Build a Compiler, by Jack Crenshaw
 * BNF for the formula expression is :
 * <expression> ::= <term> [<addop> <term>]*
 * <term> ::= <factor>  [ <mulop> <factor> ]*
 * <factor> ::= <number> | (<expression>) | <cellRef> | <function>
 * <function> ::= <functionName> ([expression [, expression]*])
 *
 *  @author Avik Sengupta <avik at apache dot org>
 *  @author Andrew C. oliver (acoliver at apache dot org)
 *  @author Eric Ladner (eladner at goldinc dot com)
 *  @author Cameron Riley (criley at ekmail.com)
 *  @author Peter M. Murray (pete at quantrix dot com)
 *  @author Pavel Krupets (pkrupets at palmtreebusiness dot com)
 *  @author Josh Micich
 */
public final class FormulaParser {
    private static final class Identifier {
        private final String _name;
        private final boolean _isQuoted;

        public Identifier(String name, boolean isQuoted) {
            _name = name;
            _isQuoted = isQuoted;
        }
        public String getName() {
            return _name;
        }
        public boolean isQuoted() {
            return _isQuoted;
        }
        public String toString() {
            StringBuffer sb = new StringBuffer(64);
            sb.append(getClass().getName());
            sb.append(" [");
            if (_isQuoted) {
                sb.append("'").append(_name).append("'");
            } else {
                sb.append(_name);
            }
            sb.append("]");
            return sb.toString();
        }
    }

    /**
     * Specific exception thrown when a supplied formula does not parse properly.<br/>
     * Primarily used by test cases when testing for specific parsing exceptions.</p>
     *
     */
    static final class FormulaParseException extends RuntimeException {
        // This class was given package scope until it would become clear that it is useful to
        // general client code.
        public FormulaParseException(String msg) {
            super(msg);
        }
    }


    private final String formulaString;
    private final int formulaLength;
    private int pointer;

    private ParseNode _rootNode;

    private static char TAB = '\t';

    /**
     * Lookahead Character.
     * gets value '\0' when the input string is exhausted
     */
    private char look;

    private FormulaParsingWorkbook book;



    /**
     * Create the formula parser, with the string that is to be
     *  parsed against the supplied workbook.
     * A later call the parse() method to return ptg list in
     *  rpn order, then call the getRPNPtg() to retrive the
     *  parse results.
     * This class is recommended only for single threaded use.
     *
     * If you only have a usermodel.HSSFWorkbook, and not a
     *  model.Workbook, then use the convenience method on
     *  usermodel.HSSFFormulaEvaluator
     */
    private FormulaParser(String formula, FormulaParsingWorkbook book){
        formulaString = formula;
        pointer=0;
        this.book = book;
        formulaLength = formulaString.length();
    }

    public static Ptg[] parse(String formula, FormulaParsingWorkbook book) {
        return parse(formula, book, FormulaType.CELL);
    }

    public static Ptg[] parse(String formula, FormulaParsingWorkbook workbook, int formulaType) {
        FormulaParser fp = new FormulaParser(formula, workbook);
        fp.parse();
        return fp.getRPNPtg(formulaType);
    }

    /** Read New Character From Input Stream */
    private void GetChar() {
        // Check to see if we've walked off the end of the string.
        if (pointer > formulaLength) {
            throw new RuntimeException("too far");
        }
        if (pointer < formulaLength) {
            look=formulaString.charAt(pointer);
        } else {
            // Just return if so and reset 'look' to something to keep
            // SkipWhitespace from spinning
            look = (char)0;
        }
        pointer++;
        //System.out.println("Got char: "+ look);
    }

    /** Report What Was Expected */
    private RuntimeException expected(String s) {
        String msg;

        if (look == '=' && formulaString.substring(0, pointer-1).trim().length() < 1) {
            msg = "The specified formula '" + formulaString
                + "' starts with an equals sign which is not allowed.";
        } else {
            msg = "Parse error near char " + (pointer-1) + " '" + look + "'"
                + " in specified formula '" + formulaString + "'. Expected "
                + s;
        }
        return new FormulaParseException(msg);
    }

    /** Recognize an Alpha Character */
    private static boolean IsAlpha(char c) {
        return Character.isLetter(c) || c == '$' || c=='_';
    }

    /** Recognize a Decimal Digit */
    private static boolean IsDigit(char c) {
        return Character.isDigit(c);
    }

    /** Recognize an Alphanumeric */
    private static boolean IsAlNum(char c) {
        return IsAlpha(c) || IsDigit(c);
    }

    /** Recognize White Space */
    private static boolean IsWhite( char c) {
        return  c ==' ' || c== TAB;
    }

    /** Skip Over Leading White Space */
    private void SkipWhite() {
        while (IsWhite(look)) {
            GetChar();
        }
    }

    /**
     *  Consumes the next input character if it is equal to the one specified otherwise throws an
     *  unchecked exception. This method does <b>not</b> consume whitespace (before or after the
     *  matched character).
     */
    private void Match(char x) {
        if (look != x) {
            throw expected("'" + x + "'");
        }
        GetChar();
    }
    private String parseUnquotedIdentifier() {
        Identifier iden = parseIdentifier();
        if (iden.isQuoted()) {
            throw expected("unquoted identifier");
        }
        return iden.getName();
    }
    /**
     * Parses a sheet name, named range name, or simple cell reference.<br/>
     * Note - identifiers in Excel can contain dots, so this method may return a String
     * which may need to be converted to an area reference.  For example, this method
     * may return a value like "A1..B2", in which case the caller must convert it to
     * an area reference like "A1:B2"
     */
    private Identifier parseIdentifier() {
        StringBuffer sb = new StringBuffer();
        if (!IsAlpha(look) && look != '\'' && look != '[') {
            throw expected("Name");
        }
        boolean isQuoted = look == '\''; 
        if(isQuoted) {
            Match('\'');
            boolean done = look == '\'';
            while(!done) {
                sb.append(look);
                GetChar();
                if(look == '\'')
                {
                    Match('\'');
                    done = look != '\'';
                }
            }
        } else {
            // allow for any sequence of dots and identifier chars
            // special case of two consecutive dots is best treated in the calling code
            while (IsAlNum(look) || look == '.' || look == '[' || look == ']') {
                sb.append(look);
                GetChar();
            }
        }
        return new Identifier(sb.toString(), isQuoted);
    }

    /** Get a Number */
    private String GetNum() {
        StringBuffer value = new StringBuffer();

        while (IsDigit(this.look)){
            value.append(this.look);
            GetChar();
        }
        return value.length() == 0 ? null : value.toString();
    }

    private ParseNode parseFunctionReferenceOrName() {
        Identifier iden = parseIdentifier();
        if (look == '('){
            //This is a function
            return function(iden.getName());
        }
        if (!iden.isQuoted()) {
            String name = iden.getName();
            if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
                return  new ParseNode(new BoolPtg(name.toUpperCase()));
            }
        }
        return parseRangeExpression(iden);
    }

    private ParseNode parseRangeExpression(Identifier iden) {
        Ptg ptgA = parseNameOrCellRef(iden);
        if (look == ':') {
            GetChar();
            Identifier iden2 = parseIdentifier();
            Ptg ptgB = parseNameOrCellRef(iden2);
            Ptg simplified = reduceRangeExpression(ptgA, ptgB);
            
            if (simplified == null) {
                ParseNode[] children = {
                    new ParseNode(ptgA),    
                    new ParseNode(ptgB),
                };
                return new ParseNode(RangePtg.instance, children);
            }
            return new ParseNode(simplified);
        }
        return new ParseNode(ptgA);
    } 
    
    /**
     * 
     * "A1", "B3" -> "A1:B3"   
     * "sheet1!A1", "B3" -> "sheet1!A1:B3"
     * 
     * @return <code>null</code> if the range expression cannot / shouldn't be reduced.
     */
    private static Ptg reduceRangeExpression(Ptg ptgA, Ptg ptgB) {
        if (!(ptgB instanceof RefPtg)) {
            // only when second ref is simple 2-D ref can the range 
            // expression be converted to an area ref
            return null;
        }
        RefPtg refB = (RefPtg) ptgB;

        if (ptgA instanceof RefPtg) {
            RefPtg refA = (RefPtg) ptgA;
            return new AreaPtg(refA.getRow(), refB.getRow(), refA.getColumn(), refB.getColumn(),
                    refA.isRowRelative(), refB.isRowRelative(), refA.isColRelative(), refB.isColRelative());
        }
        if (ptgA instanceof Ref3DPtg) {