From e1a5eae19eaae705ad21c97269d57bcfd23a187b Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Tue, 9 Sep 2008 20:25:16 +0000 Subject: [PATCH] Added support for parsing array constants in formulas. (Helping investigation for bug 45752) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@693591 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/model/FormulaParser.java | 181 ++++++++++++++---- .../hssf/model/OperandClassTransformer.java | 63 ++++-- .../poi/hssf/record/formula/ArrayPtg.java | 90 +++++---- .../poi/hssf/record/formula/ErrPtg.java | 33 +++- .../apache/poi/hssf/record/formula/Ptg.java | 2 +- .../org/apache/poi/hssf/data/testRVA.xls | Bin 32256 -> 37888 bytes .../poi/hssf/model/TestFormulaParser.java | 17 ++ .../org/apache/poi/hssf/model/TestRVA.java | 37 ++-- .../poi/hssf/record/formula/TestArrayPtg.java | 26 +-- 9 files changed, 321 insertions(+), 128 deletions(-) diff --git a/src/java/org/apache/poi/hssf/model/FormulaParser.java b/src/java/org/apache/poi/hssf/model/FormulaParser.java index c2a90e9486..fd46a5792b 100644 --- a/src/java/org/apache/poi/hssf/model/FormulaParser.java +++ b/src/java/org/apache/poi/hssf/model/FormulaParser.java @@ -22,9 +22,12 @@ import java.util.List; import java.util.Stack; //import PTGs .. since we need everything, import * +import org.apache.poi.hssf.record.UnicodeString; +import org.apache.poi.hssf.record.constant.ErrorConstant; import org.apache.poi.hssf.record.formula.*; 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.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFWorkbook; @@ -69,9 +72,9 @@ public final class FormulaParser { public static final int FORMULA_TYPE_ARRAY =2; public static final int FORMULA_TYPE_CONDFORMAT = 3; public static final int FORMULA_TYPE_NAMEDRANGE = 4; - // this constant is currently very specific. The exact differences from general data + // this constant is currently very specific. The exact differences from general data // validation formulas or conditional format formulas is not known yet - public static final int FORMULA_TYPE_DATAVALIDATION_LIST = 5; + public static final int FORMULA_TYPE_DATAVALIDATION_LIST = 5; private final String formulaString; private final int formulaLength; @@ -139,9 +142,9 @@ public final class FormulaParser { /** 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 + msg = "The specified formula '" + formulaString + "' starts with an equals sign which is not allowed."; } else { msg = "Parse error near char " + (pointer-1) + " '" + look + "'" @@ -193,8 +196,8 @@ public final class FormulaParser { /** * Parses a sheet name, named range name, or simple cell reference.
* 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 + * 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 String parseIdentifier() { @@ -250,7 +253,7 @@ public final class FormulaParser { } private Ptg parseNameOrReference(String name) { - + AreaReference areaRef = parseArea(name); if (areaRef != null) { // will happen if dots are used instead of colon @@ -372,29 +375,29 @@ public final class FormulaParser { private ParseNode function(String name) { Ptg nameToken = null; if(!AbstractFunctionPtg.isBuiltInFunctionName(name)) { - // user defined function + // user defined function // in the token tree, the name is more or less the first argument - - - int nameIndex = book.getNameIndex(name); - if (nameIndex >= 0) { - HSSFName hName = book.getNameAt(nameIndex); - if (!hName.isFunctionName()) { - throw new FormulaParseException("Attempt to use name '" + name - + "' as a function, but defined name in workbook does not refer to a function"); - } - - // calls to user-defined functions within the workbook - // get a Name token which points to a defined name record - nameToken = new NamePtg(name, this.book); - } else { - - nameToken = book.getNameXPtg(name); - if (nameToken == null) { - throw new FormulaParseException("Name '" + name - + "' is completely unknown in the current workbook"); - } - } + + + int nameIndex = book.getNameIndex(name); + if (nameIndex >= 0) { + HSSFName hName = book.getNameAt(nameIndex); + if (!hName.isFunctionName()) { + throw new FormulaParseException("Attempt to use name '" + name + + "' as a function, but defined name in workbook does not refer to a function"); + } + + // calls to user-defined functions within the workbook + // get a Name token which points to a defined name record + nameToken = new NamePtg(name, this.book); + } else { + + nameToken = book.getNameXPtg(name); + if (nameToken == null) { + throw new FormulaParseException("Name '" + name + + "' is completely unknown in the current workbook"); + } + } } Match('('); @@ -542,7 +545,7 @@ public final class FormulaParser { SkipWhite(); switch(look) { case '#': - return new ParseNode(parseErrorLiteral()); + return new ParseNode(ErrPtg.valueOf(parseErrorLiteral())); case '-': Match('-'); return new ParseNode(UnaryMinusPtg.instance, powerFactor()); @@ -555,7 +558,12 @@ public final class FormulaParser { Match(')'); return new ParseNode(ParenthesisPtg.instance, inside); case '"': - return new ParseNode(parseStringLiteral()); + return new ParseNode(new StringPtg(parseStringLiteral())); + case '{': + Match('{'); + ParseNode arrayNode = parseArray(); + Match('}'); + return arrayNode; } if (IsAlpha(look) || look == '\''){ return parseFunctionReferenceOrName(); @@ -565,6 +573,95 @@ public final class FormulaParser { } + private ParseNode parseArray() { + List rowsData = new ArrayList(); + while(true) { + Object[] singleRowData = parseArrayRow(); + rowsData.add(singleRowData); + if (look == '}') { + break; + } + if (look != ';') { + throw expected("'}' or ';'"); + } + Match(';'); + } + int nRows = rowsData.size(); + Object[][] values2d = new Object[nRows][]; + rowsData.toArray(values2d); + int nColumns = values2d[0].length; + checkRowLengths(values2d, nColumns); + + return new ParseNode(new ArrayPtg(values2d)); + } + private void checkRowLengths(Object[][] values2d, int nColumns) { + for (int i = 0; i < values2d.length; i++) { + int rowLen = values2d[i].length; + if (rowLen != nColumns) { + throw new FormulaParseException("Array row " + i + " has length " + rowLen + + " but row 0 has length " + nColumns); + } + } + } + + private Object[] parseArrayRow() { + List temp = new ArrayList(); + while (true) { + temp.add(parseArrayItem()); + SkipWhite(); + switch(look) { + case '}': + case ';': + break; + case ',': + Match(','); + continue; + default: + throw expected("'}' or ','"); + + } + break; + } + + Object[] result = new Object[temp.size()]; + temp.toArray(result); + return result; + } + + private Object parseArrayItem() { + SkipWhite(); + switch(look) { + case '"': return new UnicodeString(parseStringLiteral()); + case '#': return ErrorConstant.valueOf(parseErrorLiteral()); + case 'F': case 'f': + case 'T': case 't': + return parseBooleanLiteral(); + } + // else assume number + return convertArrayNumber(parseNumber()); + } + + private Boolean parseBooleanLiteral() { + String iden = parseIdentifier(); + if ("TRUE".equalsIgnoreCase(iden)) { + return Boolean.TRUE; + } + if ("FALSE".equalsIgnoreCase(iden)) { + return Boolean.FALSE; + } + throw expected("'TRUE' or 'FALSE'"); + } + + private static Double convertArrayNumber(Ptg ptg) { + if (ptg instanceof IntPtg) { + return new Double(((IntPtg)ptg).getValue()); + } + if (ptg instanceof NumberPtg) { + return new Double(((NumberPtg)ptg).getValue()); + } + throw new RuntimeException("Unexpected ptg (" + ptg.getClass().getName() + ")"); + } + private Ptg parseNumber() { String number2 = null; String exponent = null; @@ -601,7 +698,7 @@ public final class FormulaParser { } - private ErrPtg parseErrorLiteral() { + private int parseErrorLiteral() { Match('#'); String part1 = parseIdentifier().toUpperCase(); @@ -609,13 +706,13 @@ public final class FormulaParser { case 'V': if(part1.equals("VALUE")) { Match('!'); - return ErrPtg.VALUE_INVALID; + return HSSFErrorConstants.ERROR_VALUE; } throw expected("#VALUE!"); case 'R': if(part1.equals("REF")) { Match('!'); - return ErrPtg.REF_INVALID; + return HSSFErrorConstants.ERROR_REF; } throw expected("#REF!"); case 'D': @@ -623,21 +720,21 @@ public final class FormulaParser { Match('/'); Match('0'); Match('!'); - return ErrPtg.DIV_ZERO; + return HSSFErrorConstants.ERROR_DIV_0; } throw expected("#DIV/0!"); case 'N': if(part1.equals("NAME")) { Match('?'); // only one that ends in '?' - return ErrPtg.NAME_INVALID; + return HSSFErrorConstants.ERROR_NAME; } if(part1.equals("NUM")) { Match('!'); - return ErrPtg.NUM_ERROR; + return HSSFErrorConstants.ERROR_NUM; } if(part1.equals("NULL")) { Match('!'); - return ErrPtg.NULL_INTERSECTION; + return HSSFErrorConstants.ERROR_NULL; } if(part1.equals("N")) { Match('/'); @@ -646,7 +743,7 @@ public final class FormulaParser { } Match(look); // Note - no '!' or '?' suffix - return ErrPtg.N_A; + return HSSFErrorConstants.ERROR_NA; } throw expected("#NAME?, #NUM!, #NULL! or #N/A"); @@ -699,7 +796,7 @@ public final class FormulaParser { } - private StringPtg parseStringLiteral() { + private String parseStringLiteral() { Match('"'); StringBuffer token = new StringBuffer(); @@ -713,7 +810,7 @@ public final class FormulaParser { token.append(look); GetChar(); } - return new StringPtg(token.toString()); + return token.toString(); } /** Parse and Translate a Math Term */ @@ -970,7 +1067,7 @@ end; } return result; } - + private static String[] getOperands(Stack stack, int nOperands) { String[] operands = new String[nOperands]; diff --git a/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java b/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java index 9b5804f0c4..8b7b56638b 100644 --- a/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java +++ b/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java @@ -74,7 +74,7 @@ final class OperandClassTransformer { + _formulaType + ") not supported yet"); } - transformNode(rootNode, rootNodeOperandClass, false, false); + transformNode(rootNode, rootNodeOperandClass, false); } /** @@ -83,22 +83,35 @@ final class OperandClassTransformer { * the function return value). */ private void transformNode(ParseNode node, byte desiredOperandClass, - boolean callerForceArrayFlag, boolean isDirectChildOfValueOperator) { + boolean callerForceArrayFlag) { Ptg token = node.getToken(); ParseNode[] children = node.getChildren(); + boolean isSimpleValueFunc = isSimpleValueFunction(token); + + if (isSimpleValueFunc) { + boolean localForceArray = desiredOperandClass == Ptg.CLASS_ARRAY; + for (int i = 0; i < children.length; i++) { + transformNode(children[i], desiredOperandClass, localForceArray); + } + setSimpleValueFuncClass((AbstractFunctionPtg) token, desiredOperandClass, callerForceArrayFlag); + return; + } + if (token instanceof ValueOperatorPtg || token instanceof ControlPtg) { // Value Operator Ptgs and Control are base tokens, so token will be unchanged - // but any child nodes are processed according to desiredOperandClass and callerForceArrayFlag + + // As per OOO documentation Sec 3.2.4 "Token Class Transformation", "Step 1" + // All direct operands of value operators that are initially 'R' type will + // be converted to 'V' type. + byte localDesiredOperandClass = desiredOperandClass == Ptg.CLASS_REF ? Ptg.CLASS_VALUE : desiredOperandClass; for (int i = 0; i < children.length; i++) { - ParseNode child = children[i]; - transformNode(child, desiredOperandClass, callerForceArrayFlag, true); + transformNode(children[i], localDesiredOperandClass, callerForceArrayFlag); } return; } if (token instanceof AbstractFunctionPtg) { - transformFunctionNode((AbstractFunctionPtg) token, children, desiredOperandClass, - callerForceArrayFlag); + transformFunctionNode((AbstractFunctionPtg) token, children, desiredOperandClass, callerForceArrayFlag); return; } if (children.length > 0) { @@ -109,15 +122,24 @@ final class OperandClassTransformer { // nothing to do return; } - if (isDirectChildOfValueOperator) { - // As per OOO documentation Sec 3.2.4 "Token Class Transformation", "Step 1" - // All direct operands of value operators that are initially 'R' type will - // be converted to 'V' type. - if (token.getPtgClass() == Ptg.CLASS_REF) { - token.setClass(Ptg.CLASS_VALUE); + token.setClass(transformClass(token.getPtgClass(), desiredOperandClass, callerForceArrayFlag)); + } + + private static boolean isSimpleValueFunction(Ptg token) { + if (token instanceof AbstractFunctionPtg) { + AbstractFunctionPtg aptg = (AbstractFunctionPtg) token; + if (aptg.getDefaultOperandClass() != Ptg.CLASS_VALUE) { + return false; + } + int numberOfOperands = aptg.getNumberOfOperands(); + for (int i=numberOfOperands-1; i>=0; i--) { + if (aptg.getParameterClass(i) != Ptg.CLASS_VALUE) { + return false; + } } + return true; } - token.setClass(transformClass(token.getPtgClass(), desiredOperandClass, callerForceArrayFlag)); + return false; } private byte transformClass(byte currentOperandClass, byte desiredOperandClass, @@ -185,6 +207,7 @@ final class OperandClassTransformer { switch (defaultReturnOperandClass) { case Ptg.CLASS_REF: afp.setClass(Ptg.CLASS_REF); +// afp.setClass(Ptg.CLASS_ARRAY); break; case Ptg.CLASS_VALUE: afp.setClass(Ptg.CLASS_ARRAY); @@ -220,7 +243,17 @@ final class OperandClassTransformer { for (int i = 0; i < children.length; i++) { ParseNode child = children[i]; byte paramOperandClass = afp.getParameterClass(i); - transformNode(child, paramOperandClass, localForceArrayFlag, false); + transformNode(child, paramOperandClass, localForceArrayFlag); + } + } + + private void setSimpleValueFuncClass(AbstractFunctionPtg afp, + byte desiredOperandClass, boolean callerForceArrayFlag) { + + if (callerForceArrayFlag || desiredOperandClass == Ptg.CLASS_ARRAY) { + afp.setClass(Ptg.CLASS_ARRAY); + } else { + afp.setClass(Ptg.CLASS_VALUE); } } } diff --git a/src/java/org/apache/poi/hssf/record/formula/ArrayPtg.java b/src/java/org/apache/poi/hssf/record/formula/ArrayPtg.java index 9020c16342..c8d10b15fe 100644 --- a/src/java/org/apache/poi/hssf/record/formula/ArrayPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/ArrayPtg.java @@ -44,8 +44,11 @@ public final class ArrayPtg extends Ptg { * (not including the data which comes after all formula tokens) */ public static final int PLAIN_TOKEN_SIZE = 1+RESERVED_FIELD_LEN; + + private static final byte[] DEFAULT_RESERVED_DATA = new byte[RESERVED_FIELD_LEN]; + // TODO - fix up field visibility and subclasses - private byte[] field_1_reserved; + private final byte[] field_1_reserved; // data from these fields comes after the Ptg data of all tokens in current formula private short token_1_columns; @@ -59,8 +62,42 @@ public final class ArrayPtg extends Ptg { field_1_reserved[i] = in.readByte(); } } - public Object[] getTokenArrayValues() { - return (Object[]) token_3_arrayValues.clone(); + /** + * @param values2d array values arranged in rows + */ + public ArrayPtg(Object[][] values2d) { + int nColumns = values2d[0].length; + int nRows = values2d.length; + // convert 2-d to 1-d array (row by row according to getValueIndex()) + token_1_columns = (short) nColumns; + token_2_rows = (short) nRows; + + Object[] vv = new Object[token_1_columns * token_2_rows]; + for (int r=0; r 0) { + for (int y=0;y 0) { b.append(";"); } - for (int y=0;y 0) { + for (int x=0;x 0) { b.append(","); } Object o = token_3_arrayValues[getValueIndex(x, y)]; @@ -172,11 +202,14 @@ public final class ArrayPtg extends Ptg { b.append("}"); return b.toString(); } + public String toFormulaString(HSSFWorkbook book) { + return formatAsString(); + } private static String getConstantText(Object o) { if (o == null) { - return ""; // TODO - how is 'empty value' represented in formulas? + throw new RuntimeException("Array item cannot be null"); } if (o instanceof UnicodeString) { return "\"" + ((UnicodeString)o).getString() + "\""; @@ -196,11 +229,4 @@ public final class ArrayPtg extends Ptg { public byte getDefaultOperandClass() { return Ptg.CLASS_ARRAY; } - - public Object clone() { - ArrayPtg ptg = (ArrayPtg) super.clone(); - ptg.field_1_reserved = (byte[]) field_1_reserved.clone(); - ptg.token_3_arrayValues = (Object[]) token_3_arrayValues.clone(); - return ptg; - } } diff --git a/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java b/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java index aef580d5e4..d94f2a3807 100644 --- a/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java @@ -24,12 +24,12 @@ import org.apache.poi.hssf.usermodel.HSSFErrorConstants; * @author Daniel Noll (daniel at nuix dot com dot au) */ public final class ErrPtg extends ScalarConstantPtg { - + // convenient access to namespace private static final HSSFErrorConstants EC = null; - + /** #NULL! - Intersection of two cell ranges is empty */ - public static final ErrPtg NULL_INTERSECTION = new ErrPtg(EC.ERROR_NULL); + public static final ErrPtg NULL_INTERSECTION = new ErrPtg(EC.ERROR_NULL); /** #DIV/0! - Division by zero */ public static final ErrPtg DIV_ZERO = new ErrPtg(EC.ERROR_DIV_0); /** #VALUE! - Wrong type of operand */ @@ -37,28 +37,28 @@ public final class ErrPtg extends ScalarConstantPtg { /** #REF! - Illegal or deleted cell reference */ public static final ErrPtg REF_INVALID = new ErrPtg(EC.ERROR_REF); /** #NAME? - Wrong function or range name */ - public static final ErrPtg NAME_INVALID = new ErrPtg(EC.ERROR_NAME); + public static final ErrPtg NAME_INVALID = new ErrPtg(EC.ERROR_NAME); /** #NUM! - Value range overflow */ public static final ErrPtg NUM_ERROR = new ErrPtg(EC.ERROR_NUM); /** #N/A - Argument or function not available */ public static final ErrPtg N_A = new ErrPtg(EC.ERROR_NA); - - + + public static final short sid = 0x1c; private static final int SIZE = 2; private final int field_1_error_code; /** Creates new ErrPtg */ - public ErrPtg(int errorCode) { + private ErrPtg(int errorCode) { if(!HSSFErrorConstants.isValidCode(errorCode)) { throw new IllegalArgumentException("Invalid error code (" + errorCode + ")"); } field_1_error_code = errorCode; } - - public ErrPtg(RecordInputStream in) { - this(in.readByte()); + + public static ErrPtg read(RecordInputStream in) { + return valueOf(in.readByte()); } public void writeBytes(byte [] array, int offset) @@ -78,4 +78,17 @@ public final class ErrPtg extends ScalarConstantPtg { public int getErrorCode() { return field_1_error_code; } + + public static ErrPtg valueOf(int code) { + switch(code) { + case HSSFErrorConstants.ERROR_DIV_0: return DIV_ZERO; + case HSSFErrorConstants.ERROR_NA: return N_A; + case HSSFErrorConstants.ERROR_NAME: return NAME_INVALID; + case HSSFErrorConstants.ERROR_NULL: return NULL_INTERSECTION; + case HSSFErrorConstants.ERROR_NUM: return NUM_ERROR; + case HSSFErrorConstants.ERROR_REF: return REF_INVALID; + case HSSFErrorConstants.ERROR_VALUE: return VALUE_INVALID; + } + throw new RuntimeException("Unexpected error code (" + code + ")"); + } } 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 e16ae178ea..3782751183 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Ptg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Ptg.java @@ -162,7 +162,7 @@ public abstract class Ptg implements Cloneable { case StringPtg.sid: return new StringPtg(in); // 0x17 case AttrPtg.sid: case 0x1a: return new AttrPtg(in); // 0x19 - case ErrPtg.sid: return new ErrPtg(in); // 0x1c + case ErrPtg.sid: return ErrPtg.read(in); // 0x1c case BoolPtg.sid: return new BoolPtg(in); // 0x1d case IntPtg.sid: return new IntPtg(in); // 0x1e case NumberPtg.sid: return new NumberPtg(in); // 0x1f diff --git a/src/testcases/org/apache/poi/hssf/data/testRVA.xls b/src/testcases/org/apache/poi/hssf/data/testRVA.xls index 17aa9fd7108c2f6cf471f36780a549725365e6c4..f24b6926e83fde686ab619f9fde6fcc93b84532c 100644 GIT binary patch delta 7367 zcmb7IYiwI*8Gg^Pb85Ruoi=G|FG(FcPV3X0wsD+Gm$Zo;+ewo&ZPTuUl}>BguG6+| zy>0DS$Q*uD8mf?hqX~h~v@ro2AcTmtfd<0j*SH965@PyRW+w&j=RRsRiDJH7TFzva{Z_8L#ES%LHpbU`NV@a zH!KCG!E#~Edhms&(hjKY$ce-ErE*ipQ`5(aizkYQ^ADtwx%5~vH*7e+x~@YFOSsSA zK8yR@F!X+Z@mOxAn3|og-dRj$2FH?k?lbu5jP;2DBW(=m`e@C*(kIxtJ1v`aeJ8fo za#_J&Lrd?k5UG->0dxs$Lby+gxIeA$CR#XBDAwz%0d5;L9M;tnGY?%=i(IX>QLwg3sNANprk5}|C5g5-8rHuma;!s-O&VF;~ z#KK__DL`C__T#gMQ_F(}(62!M_=g`<(@tlM<0lrFKa7sfrgYulEon#=uYu&* z0e4Yg21@`6frr^bDh>Zk>%EZlaH06=l>J;z8-wFa*#HOW6=P5rsEo(xj1U2BH`D5xp8P z-#UAxs|&GjUq|tC7CCfgj_9!mh?XB(J9h3=Q<&!onTRN$wi21|JleMQ_M-!e5;B#? z+YW@N2l0|JOcH_b$s?uWq{mJ;lSaoSV| z0}EMw1jZS;8uG}1pxS`oCLzdmQ=_=~^NU*tHqtq~m6sxvdqsE(3q9sVLuG^QYk1mh z-n_Uo)+#V;Obv2xu7lZPzH_nb*k*xY$IvK7{97#>oWlw-x2%^=A-qL4Y?TcOV(;M% z-}u$H$+Cz*%$&Tm?HH@A4Ul=M{2F!hiiet80bsB;IH-LL-Y$sQ!8GdUWe=R~)?vPN zX?tv|z_7znU|YutsGE`?y91 zLr4dNA7a6}%sZE3iF*WweO{vr#IRuxYn)P~S>}N&r(&G~hQtT3J;dNl99ct%cbh-H z5-)WL4AKxi2T$vwfGtFpb=-xfCk*ZK{$3cxCbP@gKL#{*2!MYDS~)NLcMC#vAA+#k zK^PE(-Ga~%gojyZGJCFWD>eDlvRZzYp#G-v#;wcTmNof&WuHB#VXn!UjFn^MDfLf6 zX*($82{l95i{aNbFNTaA>_IREF{_rRU_+KECYw?Kco!|@_faA)nE$#Oef$_qxIh`2 zMm(lzKm6qY+Rt&Cr9rf$&(SGbq|=fXd$6~;F3H3j%%M)MgJWAXn~P7iE!JaT3)IiC z66@z!-1<3|rrCV?skT!63froFj_p}Lx3|IEAm)5^a-ux0n;WXkmAu-4CUPAhO4qu% zdmGHbvDU%uZ!pIxwwA$Cs~jI{CtD?{RL`+D_h7T@E*w~JAGiw+8EP5M+~m4TkGKoU zfOvC{=gk^@hS=yXiqsoFx`^j$G>3d{WzvbNI0aV!MNfiecrMLAT?eM{0y0(|9>Hf&QS98;D^ zSb^<4Qh=Zx6*3bz*Bp zysj+sS0i3;op_L^RNIgKB}FiHP3ARO zBUn-dbAlKUw5!%|Nyl)vCb`exOdh81eV4Bf5V26773Ent|0=C|1BB+3(j>(Dgg7T$ zCf?@|wGTP2)v6Tc{;9U@rn^57Ei04HNajLpsZu*+)0Ja z`_AllUjU=~C`XB{Q-b zh8jm7tnJ^5e!yuDO7KJ&22G2ErJb7w!Bd{NpVR0xyMu@PLm&q+Ywi;v_i%g3*Bc-Y zu+vT54|TFU86nB-qDJR&UagR1;lYZfQ(;&<3&u#Jq41ZR;8eBEhjS$Nk$L%=eq=~+ za%-v4Wf8~KQ2;nm4y}}nlUq2z5v|rouY(e+H5n5|bL>_XjV#Ep5ak}1Ga5J+-QAj0 zREus2LQg8*d&T#iw#@zhYr zxh)RUgwcHIXFXhhCd{>`v#|rRB_J>_NS7R@S8hE2*kp1fOEhV2x%PZ)LSW#i!1Jth z&906zfXSLax%R}&q`>g}yRgh3@#*3>N}m3${GR?v%M?~FlcAiY-yz4mg6|rq=n6eU zUj~nx;3}KwA^MJ-p|bq#Ny*7GFVBKJi}IY2=RtYS%5zSh^YT0-&jo(=9RVNysxyj) z^BP!%{PxBEpEHZs_s2LphULqL20n8YnDXf?zL}dZU4L?SPGB};jkTH=m>8ZKVyGZ* zcsN(l9Dk;>RFEC~Swq8VxrpCt)1ib|8s*w3cU&+Q1)9J1F#Z|5<%Np<#?H}?HFlL5 z9BfzyuEqsAD0o#Vjr`dF7?8iSDl#*!ulLO$>Q$VUkx9<@&P#cNiz19^i63X=!9)&< z2lDxwi5zr@aJ`$wUqx-4>TsgCow-Y7yBPzOsp>?*(cyM9vm)GH;AFb9LKh#{h3>3F zm%B!0y+W7G1wO3wUFg=PD~gu*%NwL~sm=-20f#D-0wwNosLnZ5Iio(piCh$nA1Y+I z{UmFV3JEuqT1!k(0L5ADX}bj^|%QoE#d1{db{)U#F+(v-BgnK@;j0dR>cyA_#fcP1yM+ hXGiZ+QBcF=(_%v06es9`>2ZO_kuig?}7tM~n@di!kozTfs4 zjW-x7+l{&X+Ak_MmieFm$xskiHS({6X~|HmcA99?FoEy&>N*3#U7(n)_)V(I*`P#- zsV}k-XLQ#YCDgraSg=HsA59WKbK_0b#!H4KcBgZ>it~h8&sEH0>UNHuf9I?uL!m0Y zypvlT*7qG$7fyZB@MU0#QXVSXr>+HjHuCE9!nnGR|CYSYj6|RRg!}f>n(7;Z3H! z4%Sfz>#T!yJ%Wuzk$MD-qPq^3W=usFU(xjPxy1!tYf`_ymnrpAs{xZOE{PPYn&bz@)*ynELrbK+`Yxi=jX4(o_3!&y6=mDz%Zgu1odm5vLC zb!FfXIHt=vNt`zI$MVozLO85513loF1yUyWm^V%S?GUKhq?>7dO6t@m7OX-8Bh0`w zz5H-?9qRPObZ=5PtV#pt!5NrUuNo}ifx2;VKHV;~KB0|*li=VD^v%ILRmTUJQipJ; z$>gdJ9F|@2{lFZ;?T9pya^&W}Ejf0I(S9*{9-N0;K^pR7KY`191h;kF(ydEWRRpYX z+`)X7uYws?DI5M2HUI^rtVEdXewy^jy-$hJfMdi* z(KX`Ak&}lJ%urSq?IhqBbx`R!)F81nou$mPE#gh6;~%Cs16K~3lSxm*_1h`PIWq;FU_ zeE%6Fd9J6!vCb4gl7ZbO{zlhdJ}@F&{-xxSaGgyO99s@B)3u}#kX4)Oo$ro`1z%$Z z9WyR?ZQ1DH+XjlQD83*bv%=QJWjFmiw6$1@Ywt)W|Yl-r7iLUtRV82Y2K=X8-^I diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index c9fea7f1e4..e1ffc2538d 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -22,10 +22,12 @@ import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.model.FormulaParser.FormulaParseException; +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.AreaI; import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.ArrayPtg; import org.apache.poi.hssf.record.formula.AttrPtg; import org.apache.poi.hssf.record.formula.BoolPtg; import org.apache.poi.hssf.record.formula.ConcatPtg; @@ -48,6 +50,7 @@ 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.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFErrorConstants; import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; @@ -862,4 +865,18 @@ public final class TestFormulaParser extends TestCase { assertEquals(65535, aptg.getLastRow()); } + public void testParseArray() { + Ptg[] ptgs; + ptgs = parseFormula("mode({1,2,2,#REF!;FALSE,3,3,2})"); + assertEquals(2, ptgs.length); + Ptg ptg0 = ptgs[0]; + assertEquals(ArrayPtg.class, ptg0.getClass()); + assertEquals("{1.0,2.0,2.0,#REF!;FALSE,3.0,3.0,2.0}", ptg0.toFormulaString(null)); + + ArrayPtg aptg = (ArrayPtg) ptg0; + Object[][] values = aptg.getTokenArrayValues(); + assertEquals(ErrorConstant.valueOf(HSSFErrorConstants.ERROR_REF), values[0][3]); + assertEquals(Boolean.FALSE, values[1][0]); + + } } \ No newline at end of file diff --git a/src/testcases/org/apache/poi/hssf/model/TestRVA.java b/src/testcases/org/apache/poi/hssf/model/TestRVA.java index ca74c6e2db..ac1832d089 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestRVA.java +++ b/src/testcases/org/apache/poi/hssf/model/TestRVA.java @@ -17,6 +17,10 @@ package org.apache.poi.hssf.model; +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.IOException; + import junit.framework.AssertionFailedError; import junit.framework.TestCase; @@ -42,12 +46,21 @@ public final class TestRVA extends TestCase { public void testFormulas() { HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testRVA.xls"); + try { + wb = new HSSFWorkbook(new FileInputStream("C:/josh/client/poi/svn/trunk-h2/src/testcases/org/apache/poi/hssf/data/testRVA.xls")); + } catch (FileNotFoundException e1) { + throw new RuntimeException(e1); + } catch (IOException e1) { + throw new RuntimeException(e1); + } HSSFSheet sheet = wb.getSheetAt(0); int countFailures = 0; int countErrors = 0; int rowIx = 0; +// rowIx = 34; +// rowIx =32; while (rowIx < 65535) { HSSFRow row = sheet.getRow(rowIx); if (row == null) { @@ -61,8 +74,10 @@ public final class TestRVA extends TestCase { try { confirmCell(cell, formula, wb); } catch (AssertionFailedError e) { + System.out.flush(); System.err.println("Problem with row[" + rowIx + "] formula '" + formula + "'"); System.err.println(e.getMessage()); + System.err.flush(); countFailures++; } catch (RuntimeException e) { System.err.println("Problem with row[" + rowIx + "] formula '" + formula + "'"); @@ -70,6 +85,8 @@ public final class TestRVA extends TestCase { e.printStackTrace(); } rowIx++; +// if (rowIx>30) break; +// break; } if (countErrors + countFailures > 0) { String msg = "One or more RVA tests failed: countFailures=" + countFailures @@ -104,8 +121,8 @@ public final class TestRVA extends TestCase { if (excelPtg.getClass() != poiPtg.getClass()) { hasMismatch = true; sb.append(" mismatch token type[" + i + "] " + getShortClassName(excelPtg) + " " - + getOperandClassName(excelPtg) + " - " + getShortClassName(poiPtg) + " " - + getOperandClassName(poiPtg)); + + excelPtg.getRVAType() + " - " + getShortClassName(poiPtg) + " " + + poiPtg.getRVAType()); sb.append(NEW_LINE); continue; } @@ -113,16 +130,16 @@ public final class TestRVA extends TestCase { continue; } sb.append(" token[" + i + "] " + excelPtg.toString() + " " - + getOperandClassName(excelPtg)); + + excelPtg.getRVAType()); if (excelPtg.getPtgClass() != poiPtg.getPtgClass()) { hasMismatch = true; - sb.append(" - was " + getOperandClassName(poiPtg)); + sb.append(" - was " + poiPtg.getRVAType()); } sb.append(NEW_LINE); } if (false) { // set 'true' to see trace of RVA values - System.out.println(formula); + System.out.println(formulaCell.getRowIndex() + " " + formula); System.out.println(sb.toString()); } if (hasMismatch) { @@ -135,14 +152,4 @@ public final class TestRVA extends TestCase { int pos = cn.lastIndexOf('.'); return cn.substring(pos + 1); } - - private static String getOperandClassName(Ptg ptg) { - byte ptgClass = ptg.getPtgClass(); - switch (ptgClass) { - case Ptg.CLASS_REF: return "R"; - case Ptg.CLASS_VALUE: return "V"; - case Ptg.CLASS_ARRAY: return "A"; - } - throw new RuntimeException("Unknown operand class (" + ptgClass + ")"); - } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestArrayPtg.java b/src/testcases/org/apache/poi/hssf/record/formula/TestArrayPtg.java index 9e5a340045..9c04d7c630 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestArrayPtg.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestArrayPtg.java @@ -60,15 +60,15 @@ public final class TestArrayPtg extends TestCase { ptg.readTokenValues(new TestcaseRecordInputStream(0, ENCODED_CONSTANT_DATA)); assertEquals(3, ptg.getColumnCount()); assertEquals(2, ptg.getRowCount()); - Object[] values = ptg.getTokenArrayValues(); - assertEquals(6, values.length); + Object[][] values = ptg.getTokenArrayValues(); + assertEquals(2, values.length); - assertEquals(Boolean.TRUE, values[0]); - assertEquals(new UnicodeString("ABCD"), values[1]); - assertEquals(new Double(0), values[3]); - assertEquals(Boolean.FALSE, values[4]); - assertEquals(new UnicodeString("FG"), values[5]); + assertEquals(Boolean.TRUE, values[0][0]); + assertEquals(new UnicodeString("ABCD"), values[0][1]); + assertEquals(new Double(0), values[1][0]); + assertEquals(Boolean.FALSE, values[1][1]); + assertEquals(new UnicodeString("FG"), values[1][2]); byte[] outBuf = new byte[ENCODED_CONSTANT_DATA.length]; ptg.writeTokenValueBytes(outBuf, 0); @@ -89,10 +89,10 @@ public final class TestArrayPtg extends TestCase { assertEquals(2, ptg.getRowCount()); assertEquals(0, ptg.getValueIndex(0, 0)); - assertEquals(2, ptg.getValueIndex(1, 0)); - assertEquals(4, ptg.getValueIndex(2, 0)); - assertEquals(1, ptg.getValueIndex(0, 1)); - assertEquals(3, ptg.getValueIndex(1, 1)); + assertEquals(1, ptg.getValueIndex(1, 0)); + assertEquals(2, ptg.getValueIndex(2, 0)); + assertEquals(3, ptg.getValueIndex(0, 1)); + assertEquals(4, ptg.getValueIndex(1, 1)); assertEquals(5, ptg.getValueIndex(2, 1)); } @@ -110,7 +110,7 @@ public final class TestArrayPtg extends TestCase { if (formula.equals("SUM({1.0,6.0,11.0;2.0,7.0,12.0;3.0,8.0,13.0;4.0,9.0,14.0;5.0,10.0,15.0})")) { throw new AssertionFailedError("Identified bug 42564 b"); } - assertEquals("SUM({1.0,2.0,3.0;4.0,5.0,6.0;7.0,8.0,9.0;10.0,11.0,12.0;13.0,14.0,15.0})", formula); + assertEquals("SUM({1.0,2.0,3.0,4.0,5.0;6.0,7.0,8.0,9.0,10.0;11.0,12.0,13.0,14.0,15.0})", formula); } public void testToFormulaString() { @@ -127,7 +127,7 @@ public final class TestArrayPtg extends TestCase { } throw e; } - assertEquals("{TRUE,\"ABCD\";\"E\",0.0;FALSE,\"FG\"}", actualFormula); + assertEquals("{TRUE,\"ABCD\",\"E\";0.0,FALSE,\"FG\"}", actualFormula); } /** -- 2.39.5