| /* ==================================================================== |
| 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.hssf.model; |
| |
| import static org.junit.Assert.*; |
| |
| import java.io.IOException; |
| import java.util.Locale; |
| |
| import org.apache.poi.hssf.HSSFTestDataSamples; |
| import org.apache.poi.hssf.record.NameRecord; |
| import org.apache.poi.hssf.record.common.UnicodeString; |
| import org.apache.poi.hssf.usermodel.FormulaExtractor; |
| import org.apache.poi.hssf.usermodel.HSSFCell; |
| import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; |
| import org.apache.poi.hssf.usermodel.HSSFName; |
| import org.apache.poi.hssf.usermodel.HSSFRow; |
| import org.apache.poi.hssf.usermodel.HSSFSheet; |
| import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
| import org.apache.poi.hssf.usermodel.TestHSSFName; |
| import org.apache.poi.ss.formula.FormulaParseException; |
| import org.apache.poi.ss.formula.FormulaParser; |
| import org.apache.poi.ss.formula.FormulaType; |
| import org.apache.poi.ss.formula.constant.ErrorConstant; |
| import org.apache.poi.ss.formula.ptg.AbstractFunctionPtg; |
| import org.apache.poi.ss.formula.ptg.AddPtg; |
| import org.apache.poi.ss.formula.ptg.Area3DPtg; |
| import org.apache.poi.ss.formula.ptg.AreaI; |
| import org.apache.poi.ss.formula.ptg.AreaPtg; |
| import org.apache.poi.ss.formula.ptg.AreaPtgBase; |
| import org.apache.poi.ss.formula.ptg.ArrayPtg; |
| import org.apache.poi.ss.formula.ptg.AttrPtg; |
| import org.apache.poi.ss.formula.ptg.BoolPtg; |
| import org.apache.poi.ss.formula.ptg.ConcatPtg; |
| import org.apache.poi.ss.formula.ptg.DividePtg; |
| import org.apache.poi.ss.formula.ptg.EqualPtg; |
| import org.apache.poi.ss.formula.ptg.ErrPtg; |
| import org.apache.poi.ss.formula.ptg.FuncPtg; |
| import org.apache.poi.ss.formula.ptg.FuncVarPtg; |
| import org.apache.poi.ss.formula.ptg.GreaterThanPtg; |
| import org.apache.poi.ss.formula.ptg.IntPtg; |
| import org.apache.poi.ss.formula.ptg.IntersectionPtg; |
| import org.apache.poi.ss.formula.ptg.MemAreaPtg; |
| import org.apache.poi.ss.formula.ptg.MemFuncPtg; |
| import org.apache.poi.ss.formula.ptg.MissingArgPtg; |
| import org.apache.poi.ss.formula.ptg.MultiplyPtg; |
| import org.apache.poi.ss.formula.ptg.NamePtg; |
| import org.apache.poi.ss.formula.ptg.NameXPtg; |
| import org.apache.poi.ss.formula.ptg.NumberPtg; |
| import org.apache.poi.ss.formula.ptg.ParenthesisPtg; |
| import org.apache.poi.ss.formula.ptg.PercentPtg; |
| import org.apache.poi.ss.formula.ptg.PowerPtg; |
| import org.apache.poi.ss.formula.ptg.Ptg; |
| import org.apache.poi.ss.formula.ptg.RangePtg; |
| import org.apache.poi.ss.formula.ptg.Ref3DPtg; |
| import org.apache.poi.ss.formula.ptg.RefPtg; |
| import org.apache.poi.ss.formula.ptg.StringPtg; |
| import org.apache.poi.ss.formula.ptg.SubtractPtg; |
| import org.apache.poi.ss.formula.ptg.UnaryMinusPtg; |
| import org.apache.poi.ss.formula.ptg.UnaryPlusPtg; |
| import org.apache.poi.ss.formula.ptg.UnionPtg; |
| import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues; |
| import org.apache.poi.ss.usermodel.FormulaError; |
| import org.apache.poi.ss.usermodel.Name; |
| import org.apache.poi.util.HexRead; |
| import org.apache.poi.util.LittleEndianByteArrayInputStream; |
| import org.junit.Test; |
| |
| /** |
| * Test the low level formula parser functionality. High level tests are to |
| * be done via usermodel/HSSFCell.setFormulaValue(). |
| */ |
| public final class TestFormulaParser { |
| |
| /** |
| * @return parsed token array already confirmed not <code>null</code> |
| */ |
| /* package */ static Ptg[] parseFormula(String formula) { |
| Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null); |
| assertNotNull("Ptg array should not be null", result); |
| return result; |
| } |
| private static String toFormulaString(Ptg[] ptgs) { |
| return HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, ptgs); |
| } |
| |
| @Test |
| public void testSimpleFormula() { |
| confirmTokenClasses("2+2",IntPtg.class, IntPtg.class, AddPtg.class); |
| } |
| |
| @Test |
| public void testFormulaWithSpace1() { |
| confirmTokenClasses(" 2 + 2 ",IntPtg.class, IntPtg.class, AddPtg.class); |
| } |
| |
| @Test |
| public void testFormulaWithSpace2() { |
| Ptg[] ptgs = parseFormula("2+ sum( 3 , 4) "); |
| assertEquals(5, ptgs.length); |
| } |
| |
| @Test |
| public void testFormulaWithSpaceNRef() { |
| Ptg[] ptgs = parseFormula("sum( A2:A3 )"); |
| assertEquals(2, ptgs.length); |
| } |
| |
| @Test |
| public void testFormulaWithString() { |
| Ptg[] ptgs = parseFormula("\"hello\" & \"world\" "); |
| assertEquals(3, ptgs.length); |
| } |
| |
| @Test |
| public void testTRUE() { |
| Ptg[] ptgs = parseFormula("TRUE"); |
| assertEquals(1, ptgs.length); |
| BoolPtg flag = (BoolPtg) ptgs[0]; |
| assertEquals(true, flag.getValue()); |
| } |
| |
| @Test |
| public void testSumIf() { |
| Ptg[] ptgs = parseFormula("SUMIF(A1:A5,\">4000\",B1:B5)"); |
| assertEquals(4, ptgs.length); |
| } |
| |
| /** |
| * Bug Reported by xt-jens.riis@nokia.com (Jens Riis) |
| * Refers to Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=17582">#17582</a> |
| * |
| */ |
| @Test |
| public void testNonAlphaFormula() { |
| Ptg[] ptgs = parseFormula("\"TOTAL[\"&F3&\"]\""); |
| confirmTokenClasses(ptgs, StringPtg.class, RefPtg.class, ConcatPtg.class, StringPtg.class, ConcatPtg.class); |
| assertEquals("TOTAL[", ((StringPtg)ptgs[0]).getValue()); |
| } |
| |
| @Test |
| public void testMacroFunction() throws IOException { |
| // testNames.xls contains a VB function called 'myFunc' |
| final String testFile = "testNames.xls"; |
| HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(testFile); |
| try { |
| HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(wb); |
| |
| //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)] |
| Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1); |
| assertEquals(3, ptg.length); |
| |
| // the name gets encoded as the first operand on the stack |
| NamePtg tname = (NamePtg) ptg[0]; |
| assertEquals("myFunc", tname.toFormulaString(book)); |
| |
| // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs |
| StringPtg arg = (StringPtg) ptg[1]; |
| assertEquals("arg", arg.getValue()); |
| |
| // The external FunctionPtg is the last Ptg added to the stack |
| // During formula evaluation, this Ptg pops off the the appropriate number of |
| // arguments (getNumberOfOperands()) and pushes the result on the stack |
| AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2]; //FuncVarPtg |
| assertTrue(tfunc.isExternalFunction()); |
| |
| // confirm formula parsing is case-insensitive |
| FormulaParser.parse("mYfUnC(\"arg\")", book, FormulaType.CELL, -1); |
| |
| // confirm formula parsing doesn't care about argument count or type |
| // this should only throw an error when evaluating the formula. |
| FormulaParser.parse("myFunc()", book, FormulaType.CELL, -1); |
| FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, FormulaType.CELL, -1); |
| |
| // A completely unknown formula name (not saved in workbook) should still be parseable and renderable |
| // but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated. |
| FormulaParser.parse("yourFunc(\"arg\")", book, FormulaType.CELL, -1); |
| |
| // Verify that myFunc and yourFunc were successfully added to Workbook names |
| HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb); |
| try { |
| // HSSFWorkbook/EXCEL97-specific side-effects user-defined function names must be added to Workbook's defined names in order to be saved. |
| assertNotNull(wb2.getName("myFunc")); |
| assertEqualsIgnoreCase("myFunc", wb2.getName("myFunc").getNameName()); |
| assertNotNull(wb2.getName("yourFunc")); |
| assertEqualsIgnoreCase("yourFunc", wb2.getName("yourFunc").getNameName()); |
| |
| // Manually check to make sure file isn't corrupted |
| // TODO: develop a process for occasionally manually reviewing workbooks |
| // to verify workbooks are not corrupted |
| /* |
| final File fileIn = HSSFTestDataSamples.getSampleFile(testFile); |
| final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xls", "-saved.xls")); |
| FileOutputStream fos = new FileOutputStream(reSavedFile); |
| wb2.write(fos); |
| fos.close(); |
| */ |
| } finally { |
| wb2.close(); |
| } |
| } finally { |
| wb.close(); |
| } |
| } |
| |
| private static void assertEqualsIgnoreCase(String expected, String actual) { |
| assertEquals(expected.toLowerCase(Locale.ROOT), actual.toLowerCase(Locale.ROOT)); |
| } |
| |
| @Test |
| public void testEmbeddedSlash() { |
| confirmTokenClasses("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\")", |
| StringPtg.class, StringPtg.class, FuncVarPtg.class); |
| } |
| |
| @Test |
| public void testConcatenate() { |
| confirmTokenClasses("CONCATENATE(\"first\",\"second\")", |
| StringPtg.class, StringPtg.class, FuncVarPtg.class); |
| } |
| |
| @Test |
| public void testWorksheetReferences() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("NoQuotesNeeded"); |
| wb.createSheet("Quotes Needed Here &#$@"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell; |
| |
| cell = row.createCell(0); |
| cell.setCellFormula("NoQuotesNeeded!A1"); |
| |
| cell = row.createCell(1); |
| cell.setCellFormula("'Quotes Needed Here &#$@'!A1"); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testUnaryMinus() { |
| confirmTokenClasses("-A1", RefPtg.class, UnaryMinusPtg.class); |
| } |
| |
| @Test |
| public void testUnaryPlus() { |
| confirmTokenClasses("+A1", RefPtg.class, UnaryPlusPtg.class); |
| } |
| |
| /** |
| * There may be multiple ways to encode an expression involving {@link UnaryPlusPtg} |
| * or {@link UnaryMinusPtg}. These may be perfectly equivalent from a formula |
| * evaluation perspective, or formula rendering. However, differences in the way |
| * POI encodes formulas may cause unnecessary confusion. These non-critical tests |
| * check that POI follows the same encoding rules as Excel. |
| */ |
| @Test |
| public void testExactEncodingOfUnaryPlusAndMinus() { |
| // as tested in Excel: |
| confirmUnary("-3", -3, NumberPtg.class); |
| confirmUnary("--4", -4, NumberPtg.class, UnaryMinusPtg.class); |
| confirmUnary("+++5", 5, IntPtg.class, UnaryPlusPtg.class, UnaryPlusPtg.class); |
| confirmUnary("++-6", -6, NumberPtg.class, UnaryPlusPtg.class, UnaryPlusPtg.class); |
| |
| // Spaces muck things up a bit. It would be clearer why the following cases are |
| // reasonable if POI encoded tAttrSpace in the right places. |
| // Otherwise these differences look capricious. |
| confirmUnary("+ 12", 12, IntPtg.class, UnaryPlusPtg.class); |
| confirmUnary("- 13", 13, IntPtg.class, UnaryMinusPtg.class); |
| } |
| |
| private static void confirmUnary(String formulaText, double val, Class<?>...expectedTokenTypes) { |
| Ptg[] ptgs = parseFormula(formulaText); |
| confirmTokenClasses(ptgs, expectedTokenTypes); |
| Ptg ptg0 = ptgs[0]; |
| if (ptg0 instanceof IntPtg) { |
| IntPtg intPtg = (IntPtg) ptg0; |
| assertEquals((int)val, intPtg.getValue()); |
| } else if (ptg0 instanceof NumberPtg) { |
| NumberPtg numberPtg = (NumberPtg) ptg0; |
| assertEquals(val, numberPtg.getValue(), 0.0); |
| } else { |
| fail("bad ptg0 " + ptg0); |
| } |
| } |
| |
| @Test |
| public void testLeadingSpaceInString() { |
| String value = " hi "; |
| Ptg[] ptgs = parseFormula("\"" + value + "\""); |
| confirmTokenClasses(ptgs, StringPtg.class); |
| assertTrue("ptg0 contains exact value", ((StringPtg)ptgs[0]).getValue().equals(value)); |
| } |
| |
| @Test |
| public void testLookupAndMatchFunctionArgs() { |
| Ptg[] ptgs = parseFormula("lookup(A1, A3:A52, B3:B52)"); |
| confirmTokenClasses(ptgs, RefPtg.class, AreaPtg.class, AreaPtg.class, FuncVarPtg.class); |
| assertTrue("ptg0 has Value class", ptgs[0].getPtgClass() == Ptg.CLASS_VALUE); |
| |
| ptgs = parseFormula("match(A1, A3:A52)"); |
| confirmTokenClasses(ptgs, RefPtg.class, AreaPtg.class, FuncVarPtg.class); |
| assertTrue("ptg0 has Value class", ptgs[0].getPtgClass() == Ptg.CLASS_VALUE); |
| } |
| |
| /** bug 33160*/ |
| @Test |
| public void testLargeInt() { |
| confirmTokenClasses("40", IntPtg.class); |
| confirmTokenClasses("40000", IntPtg.class); |
| } |
| |
| /** bug 33160 */ |
| @Test |
| public void testSimpleLongFormula() { |
| confirmTokenClasses("40000/2", IntPtg.class, IntPtg.class, DividePtg.class); |
| } |
| |
| /** bug 35027, underscore in sheet name */ |
| @Test |
| public void testUnderscore() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("Cash_Flow"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell; |
| |
| cell = row.createCell(0); |
| cell.setCellFormula("Cash_Flow!A1"); |
| |
| wb.close(); |
| } |
| |
| /** bug 49725, defined names with underscore */ |
| @Test |
| public void testNamesWithUnderscore() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); //or new XSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("NamesWithUnderscore"); |
| |
| HSSFName nm; |
| |
| nm = wb.createName(); |
| nm.setNameName("DA6_LEO_WBS_Number"); |
| nm.setRefersToFormula("33"); |
| |
| nm = wb.createName(); |
| nm.setNameName("DA6_LEO_WBS_Name"); |
| nm.setRefersToFormula("33"); |
| |
| nm = wb.createName(); |
| nm.setNameName("A1_"); |
| nm.setRefersToFormula("22"); |
| |
| nm = wb.createName(); |
| nm.setNameName("_A1"); |
| nm.setRefersToFormula("11"); |
| |
| nm = wb.createName(); |
| nm.setNameName("A_1"); |
| nm.setRefersToFormula("44"); |
| |
| nm = wb.createName(); |
| nm.setNameName("A_1_"); |
| nm.setRefersToFormula("44"); |
| |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| |
| cell.setCellFormula("DA6_LEO_WBS_Number*2"); |
| assertEquals("DA6_LEO_WBS_Number*2", cell.getCellFormula()); |
| |
| cell.setCellFormula("(A1_*_A1+A_1)/A_1_"); |
| assertEquals("(A1_*_A1+A_1)/A_1_", cell.getCellFormula()); |
| |
| cell.setCellFormula("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))"); |
| assertEquals("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.getCellFormula()); |
| |
| wb.close(); |
| } |
| |
| // bug 38396 : Formula with exponential numbers not parsed correctly. |
| @Test |
| public void testExponentialParsing() { |
| confirmTokenClasses("1.3E21/2", NumberPtg.class, IntPtg.class, DividePtg.class); |
| confirmTokenClasses("1322E21/2", NumberPtg.class, IntPtg.class, DividePtg.class); |
| confirmTokenClasses("1.3E1/2", NumberPtg.class, IntPtg.class, DividePtg.class); |
| } |
| |
| @Test |
| public void testExponentialInSheet() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("Cash_Flow"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| String formula; |
| |
| cell.setCellFormula("1.3E21/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "1.3E+21/3", formula); |
| |
| cell.setCellFormula("-1.3E21/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-1.3E+21/3", formula); |
| |
| cell.setCellFormula("1322E21/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "1.322E+24/3", formula); |
| |
| cell.setCellFormula("-1322E21/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-1.322E+24/3", formula); |
| |
| cell.setCellFormula("1.3E1/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "13/3", formula); |
| |
| cell.setCellFormula("-1.3E1/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-13/3", formula); |
| |
| cell.setCellFormula("1.3E-4/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "0.00013/3", formula); |
| |
| cell.setCellFormula("-1.3E-4/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-0.00013/3", formula); |
| |
| cell.setCellFormula("13E-15/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "0.000000000000013/3", formula); |
| |
| cell.setCellFormula("-13E-15/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-0.000000000000013/3", formula); |
| |
| cell.setCellFormula("1.3E3/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "1300/3", formula); |
| |
| cell.setCellFormula("-1.3E3/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-1300/3", formula); |
| |
| cell.setCellFormula("1300000000000000/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "1300000000000000/3", formula); |
| |
| cell.setCellFormula("-1300000000000000/3"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-1300000000000000/3", formula); |
| |
| cell.setCellFormula("-10E-1/3.1E2*4E3/3E4"); |
| formula = cell.getCellFormula(); |
| assertEquals("Exponential formula string", "-1/310*4000/30000", formula); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testNumbers() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("Cash_Flow"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| String formula; |
| |
| // starts from decimal point |
| |
| cell.setCellFormula(".1"); |
| formula = cell.getCellFormula(); |
| assertEquals("0.1", formula); |
| |
| cell.setCellFormula("+.1"); |
| formula = cell.getCellFormula(); |
| assertEquals("0.1", formula); |
| |
| cell.setCellFormula("-.1"); |
| formula = cell.getCellFormula(); |
| assertEquals("-0.1", formula); |
| |
| // has exponent |
| |
| cell.setCellFormula("10E1"); |
| formula = cell.getCellFormula(); |
| assertEquals("100", formula); |
| |
| cell.setCellFormula("10E+1"); |
| formula = cell.getCellFormula(); |
| assertEquals("100", formula); |
| |
| cell.setCellFormula("10E-1"); |
| formula = cell.getCellFormula(); |
| assertEquals("1", formula); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testRanges() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("Cash_Flow"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| String formula; |
| |
| cell.setCellFormula("A1.A2"); |
| formula = cell.getCellFormula(); |
| assertEquals("A1:A2", formula); |
| |
| cell.setCellFormula("A1..A2"); |
| formula = cell.getCellFormula(); |
| assertEquals("A1:A2", formula); |
| |
| cell.setCellFormula("A1...A2"); |
| formula = cell.getCellFormula(); |
| assertEquals("A1:A2", formula); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testMultiSheetReference() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| wb.createSheet("Cash_Flow"); |
| wb.createSheet("Test Sheet"); |
| |
| HSSFSheet sheet = wb.createSheet("Test"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| String formula; |
| |
| |
| // References to a single cell: |
| |
| // One sheet |
| cell.setCellFormula("Cash_Flow!A1"); |
| formula = cell.getCellFormula(); |
| assertEquals("Cash_Flow!A1", formula); |
| |
| // Then the other |
| cell.setCellFormula("\'Test Sheet\'!A1"); |
| formula = cell.getCellFormula(); |
| assertEquals("\'Test Sheet\'!A1", formula); |
| |
| // Now both |
| cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1"); |
| formula = cell.getCellFormula(); |
| assertEquals("Cash_Flow:\'Test Sheet\'!A1", formula); |
| |
| |
| // References to a range (area) of cells: |
| |
| // One sheet |
| cell.setCellFormula("Cash_Flow!A1:B2"); |
| formula = cell.getCellFormula(); |
| assertEquals("Cash_Flow!A1:B2", formula); |
| |
| // Then the other |
| cell.setCellFormula("\'Test Sheet\'!A1:B2"); |
| formula = cell.getCellFormula(); |
| assertEquals("\'Test Sheet\'!A1:B2", formula); |
| |
| // Now both |
| cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1:B2"); |
| formula = cell.getCellFormula(); |
| assertEquals("Cash_Flow:\'Test Sheet\'!A1:B2", formula); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Test for bug observable at svn revision 618865 (5-Feb-2008)<br> |
| * a formula consisting of a single no-arg function got rendered without the function braces |
| */ |
| @Test |
| public void testToFormulaStringZeroArgFunction() throws IOException { |
| HSSFWorkbook book = new HSSFWorkbook(); |
| |
| Ptg[] ptgs = { |
| FuncPtg.create(10), |
| }; |
| assertEquals("NA()", HSSFFormulaParser.toFormulaString(book, ptgs)); |
| |
| book.close(); |
| } |
| |
| @Test |
| public void testPercent() { |
| |
| confirmTokenClasses("5%", IntPtg.class, PercentPtg.class); |
| // spaces OK |
| confirmTokenClasses(" 250 % ", IntPtg.class, PercentPtg.class); |
| // double percent OK |
| confirmTokenClasses("12345.678%%", NumberPtg.class, PercentPtg.class, PercentPtg.class); |
| |
| // percent of a bracketed expression |
| confirmTokenClasses("(A1+35)%*B1%", RefPtg.class, IntPtg.class, AddPtg.class, ParenthesisPtg.class, |
| PercentPtg.class, RefPtg.class, PercentPtg.class, MultiplyPtg.class); |
| |
| // percent of a text quantity |
| confirmTokenClasses("\"8.75\"%", StringPtg.class, PercentPtg.class); |
| |
| // percent to the power of |
| confirmTokenClasses("50%^3", IntPtg.class, PercentPtg.class, IntPtg.class, PowerPtg.class); |
| |
| // things that parse OK but would *evaluate* to an error |
| confirmTokenClasses("\"abc\"%", StringPtg.class, PercentPtg.class); |
| confirmTokenClasses("#N/A%", ErrPtg.class, PercentPtg.class); |
| } |
| |
| /** |
| * Tests combinations of various operators in the absence of brackets |
| */ |
| @Test |
| public void testPrecedenceAndAssociativity() { |
| |
| // TRUE=TRUE=2=2 evaluates to FALSE |
| confirmTokenClasses("TRUE=TRUE=2=2", BoolPtg.class, BoolPtg.class, EqualPtg.class, |
| IntPtg.class, EqualPtg.class, IntPtg.class, EqualPtg.class); |
| |
| // 2^3^2 evaluates to 64 not 512 |
| confirmTokenClasses("2^3^2", IntPtg.class, IntPtg.class, PowerPtg.class, |
| IntPtg.class, PowerPtg.class); |
| |
| // "abc" & 2 + 3 & "def" evaluates to "abc5def" |
| confirmTokenClasses("\"abc\"&2+3&\"def\"", StringPtg.class, IntPtg.class, IntPtg.class, |
| AddPtg.class, ConcatPtg.class, StringPtg.class, ConcatPtg.class); |
| |
| // (1 / 2) - (3 * 4) |
| confirmTokenClasses("1/2-3*4", IntPtg.class, IntPtg.class, DividePtg.class, |
| IntPtg.class, IntPtg.class, MultiplyPtg.class, SubtractPtg.class); |
| |
| // 2 * (2^2) |
| // NOT: (2 *2) ^ 2 -> int int multiply int power |
| confirmTokenClasses("2*2^2", IntPtg.class, IntPtg.class, IntPtg.class, PowerPtg.class, MultiplyPtg.class); |
| |
| // 2^200% -> 2 not 1.6E58 |
| confirmTokenClasses("2^200%", IntPtg.class, IntPtg.class, PercentPtg.class, PowerPtg.class); |
| } |
| |
| /* package */ static Ptg[] confirmTokenClasses(String formula, Class<?>...expectedClasses) { |
| Ptg[] ptgs = parseFormula(formula); |
| confirmTokenClasses(ptgs, expectedClasses); |
| return ptgs; |
| } |
| |
| private static void confirmTokenClasses(Ptg[] ptgs, Class<?>...expectedClasses) { |
| assertEquals(expectedClasses.length, ptgs.length); |
| for (int i = 0; i < expectedClasses.length; i++) { |
| if(expectedClasses[i] != ptgs[i].getClass()) { |
| fail("difference at token[" + i + "]: expected (" |
| + expectedClasses[i].getName() + ") but got (" |
| + ptgs[i].getClass().getName() + ")"); |
| } |
| } |
| } |
| |
| @Test |
| public void testPower() { |
| confirmTokenClasses("2^5", IntPtg.class, IntPtg.class, PowerPtg.class); |
| } |
| |
| private static Ptg parseSingleToken(String formula, Class<? extends Ptg> ptgClass) { |
| Ptg[] ptgs = parseFormula(formula); |
| assertEquals(1, ptgs.length); |
| Ptg result = ptgs[0]; |
| assertEquals(ptgClass, result.getClass()); |
| return result; |
| } |
| |
| @Test |
| public void testParseNumber() { |
| IntPtg ip; |
| |
| // bug 33160 |
| ip = (IntPtg) parseSingleToken("40", IntPtg.class); |
| assertEquals(40, ip.getValue()); |
| ip = (IntPtg) parseSingleToken("40000", IntPtg.class); |
| assertEquals(40000, ip.getValue()); |
| |
| // check the upper edge of the IntPtg range: |
| ip = (IntPtg) parseSingleToken("65535", IntPtg.class); |
| assertEquals(65535, ip.getValue()); |
| NumberPtg np = (NumberPtg) parseSingleToken("65536", NumberPtg.class); |
| assertEquals(65536, np.getValue(), 0); |
| |
| np = (NumberPtg) parseSingleToken("65534.6", NumberPtg.class); |
| assertEquals(65534.6, np.getValue(), 0); |
| } |
| |
| @Test |
| public void testMissingArgs() { |
| confirmTokenClasses("if(A1, ,C1)", |
| RefPtg.class, |
| AttrPtg.class, // tAttrIf |
| MissingArgPtg.class, |
| AttrPtg.class, // tAttrSkip |
| RefPtg.class, |
| AttrPtg.class, // tAttrSkip |
| FuncVarPtg.class |
| ); |
| |
| confirmTokenClasses("counta( , A1:B2, )", MissingArgPtg.class, AreaPtg.class, MissingArgPtg.class, |
| FuncVarPtg.class); |
| } |
| |
| @Test |
| public void testParseErrorLiterals() { |
| |
| confirmParseErrorLiteral(ErrPtg.NULL_INTERSECTION, "#NULL!"); |
| confirmParseErrorLiteral(ErrPtg.DIV_ZERO, "#DIV/0!"); |
| confirmParseErrorLiteral(ErrPtg.VALUE_INVALID, "#VALUE!"); |
| confirmParseErrorLiteral(ErrPtg.REF_INVALID, "#REF!"); |
| confirmParseErrorLiteral(ErrPtg.NAME_INVALID, "#NAME?"); |
| confirmParseErrorLiteral(ErrPtg.NUM_ERROR, "#NUM!"); |
| confirmParseErrorLiteral(ErrPtg.N_A, "#N/A"); |
| parseFormula("HLOOKUP(F7,#REF!,G7,#REF!)"); |
| } |
| |
| private static void confirmParseErrorLiteral(ErrPtg expectedToken, String formula) { |
| assertEquals(expectedToken, parseSingleToken(formula, ErrPtg.class)); |
| } |
| |
| /** |
| * To aid readability the parameters have been encoded with single quotes instead of double |
| * quotes. This method converts single quotes to double quotes before performing the parse |
| * and result check. |
| */ |
| private static void confirmStringParse(String singleQuotedValue) { |
| // formula: internal quotes become double double, surround with double quotes |
| String formula = '"' + singleQuotedValue.replaceAll("'", "\"\"") + '"'; |
| String expectedValue = singleQuotedValue.replace('\'', '"'); |
| |
| StringPtg sp = (StringPtg) parseSingleToken(formula, StringPtg.class); |
| assertEquals(expectedValue, sp.getValue()); |
| } |
| |
| @Test |
| public void testParseStringLiterals_bug28754() throws IOException { |
| |
| StringPtg sp; |
| try { |
| sp = (StringPtg) parseSingleToken("\"test\"\"ing\"", StringPtg.class); |
| } catch (RuntimeException e) { |
| if(e.getMessage().startsWith("Cannot Parse")) { |
| fail("Identified bug 28754a"); |
| } |
| throw e; |
| } |
| assertEquals("test\"ing", sp.getValue()); |
| |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| try { |
| HSSFSheet sheet = wb.createSheet(); |
| wb.setSheetName(0, "Sheet1"); |
| |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| cell.setCellFormula("right(\"test\"\"ing\", 3)"); |
| String actualCellFormula = cell.getCellFormula(); |
| if("RIGHT(\"test\"ing\",3)".equals(actualCellFormula)) { |
| fail("Identified bug 28754b"); |
| } |
| assertEquals("RIGHT(\"test\"\"ing\",3)", actualCellFormula); |
| } finally { |
| wb.close(); |
| } |
| } |
| |
| @Test |
| public void testParseStringLiterals() { |
| confirmStringParse("goto considered harmful"); |
| |
| confirmStringParse("goto 'considered' harmful"); |
| |
| confirmStringParse(""); |
| confirmStringParse("'"); |
| confirmStringParse("''"); |
| confirmStringParse("' '"); |
| confirmStringParse(" ' "); |
| } |
| |
| @Test |
| public void testParseSumIfSum() { |
| String formulaString; |
| Ptg[] ptgs; |
| ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))"); |
| formulaString = toFormulaString(ptgs); |
| assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString); |
| |
| ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)"); |
| formulaString = toFormulaString(ptgs); |
| assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString); |
| } |
| |
| @Test |
| public void testParserErrors() { |
| parseExpectedException(" 12 . 345 "); |
| parseExpectedException("1 .23 "); |
| |
| parseExpectedException("sum(#NAME)"); |
| parseExpectedException("1 + #N / A * 2"); |
| parseExpectedException("#value?"); |
| parseExpectedException("#DIV/ 0+2"); |
| |
| |
| parseExpectedException("IF(TRUE)"); |
| parseExpectedException("countif(A1:B5, C1, D1)"); |
| |
| parseExpectedException("("); |
| parseExpectedException(")"); |
| parseExpectedException("+"); |
| parseExpectedException("42+"); |
| |
| parseExpectedException("IF("); |
| } |
| |
| private static void parseExpectedException(String formula) { |
| try { |
| parseFormula(formula); |
| fail("Expected FormulaParseException: " + formula); |
| } catch (FormulaParseException e) { |
| // expected during successful test |
| assertNotNull(e.getMessage()); |
| } |
| } |
| |
| @Test |
| public void testSetFormulaWithRowBeyond32768_Bug44539() throws IOException { |
| |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet(); |
| wb.setSheetName(0, "Sheet1"); |
| |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cell = row.createCell(0); |
| cell.setCellFormula("SUM(A32769:A32770)"); |
| if("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) { |
| fail("Identified bug 44539"); |
| } |
| assertEquals("SUM(A32769:A32770)", cell.getCellFormula()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testSpaceAtStartOfFormula() { |
| // Simulating cell formula of "= 4" (note space) |
| // The same Ptg array can be observed if an excel file is saved with that exact formula |
| |
| AttrPtg spacePtg = AttrPtg.createSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1); |
| Ptg[] ptgs = { spacePtg, new IntPtg(4), }; |
| String formulaString; |
| try { |
| formulaString = toFormulaString(ptgs); |
| } catch (IllegalStateException e) { |
| if(e.getMessage().equalsIgnoreCase("too much stuff left on the stack")) { |
| fail("Identified bug 44609"); |
| } |
| // else some unexpected error |
| throw e; |
| } |
| // FormulaParser strips spaces anyway |
| assertEquals("4", formulaString); |
| |
| ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, }; |
| formulaString = toFormulaString(ptgs); |
| assertEquals("3+4", formulaString); |
| } |
| |
| /** |
| * Checks some internal error detecting logic ('stack underflow error' in toFormulaString) |
| */ |
| @Test |
| public void testTooFewOperandArgs() { |
| // Simulating badly encoded cell formula of "=/1" |
| // Not sure if Excel could ever produce this |
| Ptg[] ptgs = { |
| // Excel would probably have put tMissArg here |
| new IntPtg(1), |
| DividePtg.instance, |
| }; |
| try { |
| toFormulaString(ptgs); |
| fail("Expected exception was not thrown"); |
| } catch (IllegalStateException e) { |
| // expected during successful test |
| assertTrue(e.getMessage().startsWith("Too few arguments supplied to operation")); |
| } |
| } |
| /** |
| * Make sure that POI uses the right Func Ptg when encoding formulas. Functions with variable |
| * number of args should get FuncVarPtg, functions with fixed args should get FuncPtg.<p> |
| * |
| * Prior to the fix for bug 44675 POI would encode FuncVarPtg for all functions. In many cases |
| * Excel tolerates the wrong Ptg and evaluates the formula OK (e.g. SIN), but in some cases |
| * (e.g. COUNTIF) Excel fails to evaluate the formula, giving '#VALUE!' instead. |
| */ |
| @Test |
| public void testFuncPtgSelection() { |
| |
| Ptg[] ptgs = parseFormula("countif(A1:A2, 1)"); |
| assertEquals(3, ptgs.length); |
| if(ptgs[2] instanceof FuncVarPtg) { |
| fail("Identified bug 44675"); |
| } |
| confirmTokenClasses(ptgs, AreaPtg.class, IntPtg.class, FuncPtg.class); |
| |
| confirmTokenClasses("sin(1)", IntPtg.class, FuncPtg.class); |
| } |
| |
| @Test |
| public void testWrongNumberOfFunctionArgs() throws IOException { |
| confirmArgCountMsg("sin()", "Too few arguments to function 'SIN'. Expected 1 but got 0."); |
| confirmArgCountMsg("countif(1, 2, 3, 4)", "Too many arguments to function 'COUNTIF'. Expected 2 but got 4."); |
| confirmArgCountMsg("index(1, 2, 3, 4, 5, 6)", "Too many arguments to function 'INDEX'. At most 4 were expected but got 6."); |
| confirmArgCountMsg("vlookup(1, 2)", "Too few arguments to function 'VLOOKUP'. At least 3 were expected but got 2."); |
| } |
| |
| private static void confirmArgCountMsg(String formula, String expectedMessage) throws IOException { |
| HSSFWorkbook book = new HSSFWorkbook(); |
| try { |
| HSSFFormulaParser.parse(formula, book); |
| fail("Didn't get parse exception as expected"); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, expectedMessage); |
| } |
| book.close(); |
| } |
| |
| @Test |
| public void testParseErrorExpectedMsg() { |
| |
| try { |
| parseFormula("round(3.14;2)"); |
| fail("Didn't get parse exception as expected"); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, |
| "Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'"); |
| } |
| |
| try { |
| parseFormula(" =2+2"); |
| fail("Didn't get parse exception as expected"); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, |
| "The specified formula ' =2+2' starts with an equals sign which is not allowed."); |
| } |
| } |
| |
| /** |
| * this function name has a dot in it. |
| */ |
| @Test |
| public void testParseErrorTypeFunction() { |
| |
| Ptg[] ptgs; |
| try { |
| ptgs = parseFormula("error.type(A1)"); |
| } catch (IllegalArgumentException e) { |
| if (e.getMessage().equals("Invalid Formula cell reference: 'error'")) { |
| fail("Identified bug 45334"); |
| } |
| throw e; |
| } |
| confirmTokenClasses(ptgs, RefPtg.class, FuncPtg.class); |
| assertEquals("ERROR.TYPE", ((FuncPtg) ptgs[1]).getName()); |
| } |
| |
| @Test |
| public void testNamedRangeThatLooksLikeCell() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("Sheet1"); |
| HSSFName name = wb.createName(); |
| name.setRefersToFormula("Sheet1!B1"); |
| name.setNameName("pfy1"); |
| |
| Ptg[] ptgs; |
| try { |
| ptgs = HSSFFormulaParser.parse("count(pfy1)", wb); |
| } catch (IllegalArgumentException e) { |
| if (e.getMessage().equals("Specified colIx (1012) is out of range")) { |
| fail("Identified bug 45354"); |
| } |
| wb.close(); |
| throw e; |
| } |
| confirmTokenClasses(ptgs, NamePtg.class, FuncVarPtg.class); |
| |
| HSSFCell cell = sheet.createRow(0).createCell(0); |
| cell.setCellFormula("count(pfy1)"); |
| assertEquals("COUNT(pfy1)", cell.getCellFormula()); |
| try { |
| cell.setCellFormula("count(pf1)"); |
| fail("Expected formula parse execption"); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, |
| "Specified named range 'pf1' does not exist in the current workbook."); |
| } |
| cell.setCellFormula("count(fp1)"); // plain cell ref, col is in range |
| wb.close(); |
| } |
| |
| @Test |
| public void testParseAreaRefHighRow_bug45358() throws IOException { |
| Ptg[] ptgs; |
| AreaI aptg; |
| |
| HSSFWorkbook book = new HSSFWorkbook(); |
| book.createSheet("Sheet1"); |
| |
| ptgs = HSSFFormulaParser.parse("Sheet1!A10:A40000", book); |
| aptg = (AreaI) ptgs[0]; |
| if (aptg.getLastRow() == -25537) { |
| fail("Identified bug 45358"); |
| } |
| assertEquals(39999, aptg.getLastRow()); |
| |
| ptgs = HSSFFormulaParser.parse("Sheet1!A10:A65536", book); |
| aptg = (AreaI) ptgs[0]; |
| assertEquals(65535, aptg.getLastRow()); |
| |
| // plain area refs should be ok too |
| ptgs = parseFormula("A10:A65536"); |
| aptg = (AreaI) ptgs[0]; |
| assertEquals(65535, aptg.getLastRow()); |
| |
| book.close(); |
| } |
| |
| @Test |
| public void testParseArray() { |
| Ptg[] ptgs; |
| ptgs = parseFormula("mode({1,2,2,#REF!;FALSE,3,3,2})"); |
| confirmTokenClasses(ptgs, ArrayPtg.class, FuncVarPtg.class); |
| assertEquals("{1,2,2,#REF!;FALSE,3,3,2}", ptgs[0].toFormulaString()); |
| |
| ArrayPtg aptg = (ArrayPtg) ptgs[0]; |
| Object[][] values = aptg.getTokenArrayValues(); |
| assertEquals(ErrorConstant.valueOf(FormulaError.REF.getCode()), values[0][3]); |
| assertEquals(Boolean.FALSE, values[1][0]); |
| } |
| |
| @Test |
| public void testParseStringElementInArray() { |
| Ptg[] ptgs; |
| ptgs = parseFormula("MAX({\"5\"},3)"); |
| confirmTokenClasses(ptgs, ArrayPtg.class, IntPtg.class, FuncVarPtg.class); |
| Object element = ((ArrayPtg)ptgs[0]).getTokenArrayValues()[0][0]; |
| if (element instanceof UnicodeString) { |
| // this would cause ClassCastException below |
| fail("Wrong encoding of array element value"); |
| } |
| assertEquals(String.class, element.getClass()); |
| |
| // make sure the formula encodes OK |
| int encSize = Ptg.getEncodedSize(ptgs); |
| byte[] data = new byte[encSize]; |
| Ptg.serializePtgs(ptgs, data, 0); |
| byte[] expData = HexRead.readFromString( |
| "20 00 00 00 00 00 00 00 " // tArray |
| + "1E 03 00 " // tInt(3) |
| + "42 02 07 00 " // tFuncVar(MAX) 2-arg |
| + "00 00 00 " // Array data: 1 col, 1 row |
| + "02 01 00 00 35" // elem (type=string, len=1, "5") |
| ); |
| assertArrayEquals(expData, data); |
| int initSize = Ptg.getEncodedSizeWithoutArrayData(ptgs); |
| Ptg[] ptgs2 = Ptg.readTokens(initSize, new LittleEndianByteArrayInputStream(data)); |
| confirmTokenClasses(ptgs2, ArrayPtg.class, IntPtg.class, FuncVarPtg.class); |
| } |
| |
| @Test |
| public void testParseArrayNegativeElement() { |
| Ptg[] ptgs; |
| try { |
| ptgs = parseFormula("{-42}"); |
| } catch (FormulaParseException e) { |
| if (e.getMessage().equals("Parse error near char 1 '-' in specified formula '{-42}'. Expected Integer")) { |
| fail("Identified bug - failed to parse negative array element."); |
| } |
| throw e; |
| } |
| confirmTokenClasses(ptgs, ArrayPtg.class); |
| Object element = ((ArrayPtg)ptgs[0]).getTokenArrayValues()[0][0]; |
| |
| assertEquals(-42.0, ((Double)element).doubleValue(), 0.0); |
| |
| // Should be able to handle whitespace between unary minus and digits (Excel |
| // accepts this formula after presenting the user with a confirmation dialog). |
| ptgs = parseFormula("{- 5}"); |
| element = ((ArrayPtg)ptgs[0]).getTokenArrayValues()[0][0]; |
| assertEquals(-5.0, ((Double)element).doubleValue(), 0.0); |
| } |
| |
| @Test |
| public void testRangeOperator() throws IOException { |
| |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet(); |
| HSSFCell cell = sheet.createRow(0).createCell(0); |
| |
| wb.setSheetName(0, "Sheet1"); |
| cell.setCellFormula("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator |
| assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula()); |
| |
| cell.setCellFormula("Sheet1!B$4:$C1"); // plain area ref |
| assertEquals("Sheet1!B1:$C$4", cell.getCellFormula()); // note - area ref is normalised |
| |
| cell.setCellFormula("Sheet1!$C1...B$4"); // different syntax for plain area ref |
| assertEquals("Sheet1!B1:$C$4", cell.getCellFormula()); |
| |
| // with funny sheet name |
| wb.setSheetName(0, "A1...A2"); |
| cell.setCellFormula("A1...A2!B1"); |
| assertEquals("A1...A2!B1", cell.getCellFormula()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testBooleanNamedSheet() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("true"); |
| HSSFCell cell = sheet.createRow(0).createCell(0); |
| cell.setCellFormula("'true'!B2"); |
| |
| assertEquals("'true'!B2", cell.getCellFormula()); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testParseExternalWorkbookReference() throws IOException { |
| HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls"); |
| HSSFCell cell = wbA.getSheetAt(0).getRow(0).getCell(0); |
| |
| // make sure formula in sample is as expected |
| assertEquals("[multibookFormulaB.xls]BSheet1!B1", cell.getCellFormula()); |
| Ptg[] expectedPtgs = FormulaExtractor.getPtgs(cell); |
| confirmSingle3DRef(expectedPtgs, 1); |
| |
| // now try (re-)parsing the formula |
| Ptg[] actualPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]BSheet1!B1", wbA); |
| confirmSingle3DRef(actualPtgs, 1); // externalSheetIndex 1 -> BSheet1 |
| |
| // try parsing a formula pointing to a different external sheet |
| Ptg[] otherPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]AnotherSheet!B1", wbA); |
| confirmSingle3DRef(otherPtgs, 0); // externalSheetIndex 0 -> AnotherSheet |
| |
| // try setting the same formula in a cell |
| cell.setCellFormula("[multibookFormulaB.xls]AnotherSheet!B1"); |
| assertEquals("[multibookFormulaB.xls]AnotherSheet!B1", cell.getCellFormula()); |
| |
| wbA.close(); |
| } |
| |
| private static void confirmSingle3DRef(Ptg[] ptgs, int expectedExternSheetIndex) { |
| assertEquals(1, ptgs.length); |
| Ptg ptg0 = ptgs[0]; |
| assertTrue(ptg0 instanceof Ref3DPtg); |
| assertEquals(expectedExternSheetIndex, ((Ref3DPtg)ptg0).getExternSheetIndex()); |
| } |
| |
| @Test |
| public void testUnion() throws IOException { |
| String formula = "Sheet1!$B$2:$C$3,OFFSET(Sheet1!$E$2:$E$4,1,Sheet1!$A$1),Sheet1!$D$6"; |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| Ptg[] ptgs = FormulaParser.parse(formula, HSSFEvaluationWorkbook.create(wb), FormulaType.CELL, -1); |
| |
| confirmTokenClasses(ptgs, |
| // TODO - AttrPtg.class, // Excel prepends this |
| MemFuncPtg.class, |
| Area3DPtg.class, |
| Area3DPtg.class, |
| IntPtg.class, |
| Ref3DPtg.class, |
| FuncVarPtg.class, |
| UnionPtg.class, |
| Ref3DPtg.class, |
| UnionPtg.class |
| ); |
| MemFuncPtg mf = (MemFuncPtg)ptgs[0]; |
| assertEquals(45, mf.getLenRefSubexpression()); |
| |
| // We don't check the type of the operands. |
| confirmTokenClasses("1,2", MemAreaPtg.class, IntPtg.class, IntPtg.class, UnionPtg.class); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testIntersection() throws IOException { |
| String formula = "Sheet1!$B$2:$C$3 OFFSET(Sheet1!$E$2:$E$4, 1,Sheet1!$A$1) Sheet1!$D$6"; |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| Ptg[] ptgs = FormulaParser.parse(formula, HSSFEvaluationWorkbook.create(wb), FormulaType.CELL, -1); |
| |
| confirmTokenClasses(ptgs, |
| // TODO - AttrPtg.class, // Excel prepends this |
| MemFuncPtg.class, |
| Area3DPtg.class, |
| Area3DPtg.class, |
| IntPtg.class, |
| Ref3DPtg.class, |
| FuncVarPtg.class, |
| IntersectionPtg.class, |
| Ref3DPtg.class, |
| IntersectionPtg.class |
| ); |
| MemFuncPtg mf = (MemFuncPtg)ptgs[0]; |
| assertEquals(45, mf.getLenRefSubexpression()); |
| |
| // This used to be an error but now parses. Union has the same behaviour. |
| confirmTokenClasses("1 2", MemAreaPtg.class, IntPtg.class, IntPtg.class, IntersectionPtg.class); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testComparisonInParen() { |
| confirmTokenClasses("(A1 > B2)", |
| RefPtg.class, |
| RefPtg.class, |
| GreaterThanPtg.class, |
| ParenthesisPtg.class |
| ); |
| } |
| |
| @Test |
| public void testUnionInParen() { |
| confirmTokenClasses("(A1:B2,B2:C3)", |
| MemAreaPtg.class, |
| AreaPtg.class, |
| AreaPtg.class, |
| UnionPtg.class, |
| ParenthesisPtg.class |
| ); |
| } |
| |
| @Test |
| public void testIntersectionInParen() { |
| confirmTokenClasses("(A1:B2 B2:C3)", |
| MemAreaPtg.class, |
| AreaPtg.class, |
| AreaPtg.class, |
| IntersectionPtg.class, |
| ParenthesisPtg.class |
| ); |
| } |
| |
| @Test |
| public void testRange_bug46643() throws IOException { |
| String formula = "Sheet1!A1:Sheet1!B3"; |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| Ptg[] ptgs = FormulaParser.parse(formula, HSSFEvaluationWorkbook.create(wb), FormulaType.CELL, -1, -1); |
| |
| if (ptgs.length == 3) { |
| confirmTokenClasses(ptgs, Ref3DPtg.class, Ref3DPtg.class, RangePtg.class); |
| fail("Identified bug 46643"); |
| } |
| |
| confirmTokenClasses(ptgs, |
| MemFuncPtg.class, |
| Ref3DPtg.class, |
| Ref3DPtg.class, |
| RangePtg.class |
| ); |
| MemFuncPtg mf = (MemFuncPtg)ptgs[0]; |
| assertEquals(15, mf.getLenRefSubexpression()); |
| wb.close(); |
| } |
| |
| /** Named ranges with backslashes, e.g. 'POI\\2009' */ |
| @Test |
| public void testBackSlashInNames() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| HSSFName name = wb.createName(); |
| name.setNameName("POI\\2009"); |
| name.setRefersToFormula("Sheet1!$A$1"); |
| |
| HSSFSheet sheet = wb.createSheet(); |
| HSSFRow row = sheet.createRow(0); |
| |
| HSSFCell cell_C1 = row.createCell(2); |
| cell_C1.setCellFormula("POI\\2009"); |
| assertEquals("POI\\2009", cell_C1.getCellFormula()); |
| |
| HSSFCell cell_D1 = row.createCell(2); |
| cell_D1.setCellFormula("NOT(POI\\2009=\"3.5-final\")"); |
| assertEquals("NOT(POI\\2009=\"3.5-final\")", cell_D1.getCellFormula()); |
| |
| wb.close(); |
| } |
| |
| /** |
| * See the related/similar test: {@link BaseTestBugzillaIssues#bug42448()} |
| */ |
| @Test |
| public void testParseAbnormalSheetNamesAndRanges_bug42448() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("A"); |
| try { |
| HSSFFormulaParser.parse("SUM(A!C7:A!C67)", wb); |
| } catch (StringIndexOutOfBoundsException e) { |
| fail("Identified bug 42448"); |
| } |
| // the exact example from the bugzilla description: |
| HSSFFormulaParser.parse("SUMPRODUCT(A!C7:A!C67, B8:B68) / B69", wb); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testRangeFuncOperand_bug46951() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| Ptg[] ptgs; |
| try { |
| ptgs = HSSFFormulaParser.parse("SUM(C1:OFFSET(C1,0,B1))", wb); |
| } catch (RuntimeException e) { |
| if (e.getMessage().equals("Specified named range 'OFFSET' does not exist in the current workbook.")) { |
| fail("Identified bug 46951"); |
| } |
| wb.close(); |
| throw e; |
| } |
| confirmTokenClasses(ptgs, |
| MemFuncPtg.class, // [len=23] |
| RefPtg.class, // [C1] |
| RefPtg.class, // [C1] |
| IntPtg.class, // [0] |
| RefPtg.class, // [B1] |
| FuncVarPtg.class, // [OFFSET nArgs=3] |
| RangePtg.class, // |
| AttrPtg.class // [sum ] |
| ); |
| wb.close(); |
| } |
| |
| @Test |
| public void testUnionOfFullCollFullRowRef() throws IOException { |
| parseFormula("3:4"); |
| Ptg[] ptgs = parseFormula("$Z:$AC"); |
| confirmTokenClasses(ptgs, AreaPtg.class); |
| parseFormula("B:B"); |
| |
| ptgs = parseFormula("$11:$13"); |
| confirmTokenClasses(ptgs, AreaPtg.class); |
| |
| ptgs = parseFormula("$A:$A,$1:$4"); |
| confirmTokenClasses(ptgs, MemAreaPtg.class, |
| AreaPtg.class, |
| AreaPtg.class, |
| UnionPtg.class |
| ); |
| |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| ptgs = HSSFFormulaParser.parse("Sheet1!$A:$A,Sheet1!$1:$4", wb); |
| confirmTokenClasses(ptgs, MemFuncPtg.class, |
| Area3DPtg.class, |
| Area3DPtg.class, |
| UnionPtg.class |
| ); |
| |
| ptgs = HSSFFormulaParser.parse("'Sheet1'!$A:$A,'Sheet1'!$1:$4", wb); |
| confirmTokenClasses(ptgs, |
| MemFuncPtg.class, |
| Area3DPtg.class, |
| Area3DPtg.class, |
| UnionPtg.class |
| ); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testExplicitRangeWithTwoSheetNames() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| Ptg[] ptgs = HSSFFormulaParser.parse("Sheet1!F1:Sheet1!G2", wb); |
| confirmTokenClasses(ptgs, |
| MemFuncPtg.class, |
| Ref3DPtg.class, |
| Ref3DPtg.class, |
| RangePtg.class |
| ); |
| MemFuncPtg mf; |
| mf = (MemFuncPtg)ptgs[0]; |
| assertEquals(15, mf.getLenRefSubexpression()); |
| wb.close(); |
| } |
| |
| /** |
| * Checks that the area-ref and explicit range operators get the right associativity |
| * and that the {@link MemFuncPtg} / {@link MemAreaPtg} is added correctly |
| */ |
| @Test |
| public void testComplexExplicitRangeEncodings() { |
| |
| Ptg[] ptgs; |
| ptgs = parseFormula("SUM(OFFSET(A1,0,0):B2:C3:D4:E5:OFFSET(F6,1,1):G7)"); |
| confirmTokenClasses(ptgs, |
| // AttrPtg.class, // [volatile ] // POI doesn't do this yet (Apr 2009) |
| MemFuncPtg.class, // len 57 |
| RefPtg.class, // [A1] |
| IntPtg.class, // [0] |
| IntPtg.class, // [0] |
| FuncVarPtg.class, // [OFFSET nArgs=3] |
| AreaPtg.class, // [B2:C3] |
| RangePtg.class, |
| AreaPtg.class, // [D4:E5] |
| RangePtg.class, |
| RefPtg.class, // [F6] |
| IntPtg.class, // [1] |
| IntPtg.class, // [1] |
| FuncVarPtg.class, // [OFFSET nArgs=3] |
| RangePtg.class, |
| RefPtg.class, // [G7] |
| RangePtg.class, |
| AttrPtg.class // [sum ] |
| ); |
| |
| MemFuncPtg mf = (MemFuncPtg)ptgs[0]; |
| assertEquals(57, mf.getLenRefSubexpression()); |
| assertEquals("D4:E5", ((AreaPtgBase)ptgs[7]).toFormulaString()); |
| assertTrue(((AttrPtg)ptgs[16]).isSum()); |
| |
| ptgs = parseFormula("SUM(A1:B2:C3:D4)"); |
| confirmTokenClasses(ptgs, |
| // AttrPtg.class, // [volatile ] // POI doesn't do this yet (Apr 2009) |
| MemAreaPtg.class, // len 19 |
| AreaPtg.class, // [A1:B2] |
| AreaPtg.class, // [C3:D4] |
| RangePtg.class, |
| AttrPtg.class // [sum ] |
| ); |
| MemAreaPtg ma = (MemAreaPtg)ptgs[0]; |
| assertEquals(19, ma.getLenRefSubexpression()); |
| } |
| |
| |
| /** |
| * Mostly confirming that erroneous conditions are detected. Actual error message wording is not critical. |
| * |
| */ |
| @Test |
| public void testEdgeCaseParserErrors() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| |
| confirmParseError(wb, "A1:ROUND(B1,1)", "The RHS of the range operator ':' at position 3 is not a proper reference."); |
| |
| confirmParseError(wb, "Sheet1!!!", "Parse error near char 7 '!' in specified formula 'Sheet1!!!'. Expected number, string, defined name, or data table"); |
| confirmParseError(wb, "Sheet1!.Name", "Parse error near char 7 '.' in specified formula 'Sheet1!.Name'. Expected number, string, defined name, or data table"); |
| confirmParseError(wb, "Sheet1!Sheet1", "Specified name 'Sheet1' for sheet Sheet1 not found"); |
| confirmParseError(wb, "Sheet1!F:Sheet1!G", "'Sheet1!F' is not a proper reference."); |
| confirmParseError(wb, "Sheet1!F..foobar", "Complete area reference expected after sheet name at index 11."); |
| confirmParseError(wb, "Sheet1!A .. B", "Dotted range (full row or column) expression 'A .. B' must not contain whitespace."); |
| confirmParseError(wb, "Sheet1!A...B", "Dotted range (full row or column) expression 'A...B' must have exactly 2 dots."); |
| confirmParseError(wb, "Sheet1!A foobar", "Second part of cell reference expected after sheet name at index 10."); |
| |
| confirmParseError(wb, "foobar", "Specified named range 'foobar' does not exist in the current workbook."); |
| confirmParseError(wb, "A1:1", "The RHS of the range operator ':' at position 3 is not a proper reference."); |
| wb.close(); |
| } |
| |
| private static void confirmParseError(HSSFWorkbook wb, String formula, String expectedMessage) { |
| |
| try { |
| HSSFFormulaParser.parse(formula, wb); |
| fail("Expected formula parse execption"); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, expectedMessage); |
| } |
| } |
| |
| /** |
| * In bug 47078, POI had trouble evaluating a defined name flagged as 'complex'. |
| * POI should also be able to parse such defined names. |
| */ |
| @Test |
| public void testParseComplexName() throws IOException { |
| |
| // Mock up a spreadsheet to match the critical details of the sample |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| wb.createSheet("Sheet1"); |
| HSSFName definedName = wb.createName(); |
| definedName.setNameName("foo"); |
| definedName.setRefersToFormula("Sheet1!B2"); |
| |
| // Set the complex flag - POI doesn't usually manipulate this flag |
| NameRecord nameRec = TestHSSFName.getNameRecord(definedName); |
| nameRec.setOptionFlag((short)0x10); // 0x10 -> complex |
| |
| Ptg[] result; |
| try { |
| result = HSSFFormulaParser.parse("1+foo", wb); |
| } catch (FormulaParseException e) { |
| if (e.getMessage().equals("Specified name 'foo' is not a range as expected.")) { |
| fail("Identified bug 47078c"); |
| } |
| wb.close(); |
| throw e; |
| } |
| confirmTokenClasses(result, IntPtg.class, NamePtg.class, AddPtg.class); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Zero is not a valid row number so cell references like 'A0' are not valid. |
| * Actually, they should be treated like defined names. |
| * <br> |
| * In addition, leading zeros (on the row component) should be removed from cell |
| * references during parsing. |
| */ |
| @Test |
| public void testZeroRowRefs() throws IOException { |
| String badCellRef = "B0"; // bad because zero is not a valid row number |
| String leadingZeroCellRef = "B000001"; // this should get parsed as "B1" |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| |
| try { |
| HSSFFormulaParser.parse(badCellRef, wb); |
| fail("Identified bug 47312b - Shouldn't be able to parse cell ref '" |
| + badCellRef + "'."); |
| } catch (FormulaParseException e) { |
| // expected during successful test |
| confirmParseException(e, "Specified named range '" |
| + badCellRef + "' does not exist in the current workbook."); |
| } |
| |
| Ptg[] ptgs; |
| try { |
| ptgs = HSSFFormulaParser.parse(leadingZeroCellRef, wb); |
| assertEquals("B1", ((RefPtg) ptgs[0]).toFormulaString()); |
| } catch (FormulaParseException e) { |
| confirmParseException(e, "Specified named range '" |
| + leadingZeroCellRef + "' does not exist in the current workbook."); |
| // close but no cigar |
| fail("Identified bug 47312c - '" + leadingZeroCellRef + "' should parse as 'B1'."); |
| } |
| |
| // create a defined name called 'B0' and try again |
| Name n = wb.createName(); |
| n.setNameName("B0"); |
| n.setRefersToFormula("1+1"); |
| ptgs = HSSFFormulaParser.parse("B0", wb); |
| confirmTokenClasses(ptgs, NamePtg.class); |
| |
| wb.close(); |
| } |
| |
| private static void confirmParseException(FormulaParseException e, String expMsg) { |
| assertEquals(expMsg, e.getMessage()); |
| } |
| |
| @Test |
| public void test57196_Formula() throws IOException { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| Ptg[] ptgs = HSSFFormulaParser.parse("DEC2HEX(HEX2DEC(O8)-O2+D2)", wb, FormulaType.CELL, -1); |
| assertNotNull("Ptg array should not be null", ptgs); |
| |
| confirmTokenClasses(ptgs, |
| NameXPtg.class, // ?? |
| NameXPtg.class, // ?? |
| RefPtg.class, // O8 |
| FuncVarPtg.class, // HEX2DEC |
| RefPtg.class, // O2 |
| SubtractPtg.class, |
| RefPtg.class, // D2 |
| AddPtg.class, |
| FuncVarPtg.class // DEC2HEX |
| ); |
| |
| RefPtg o8 = (RefPtg) ptgs[2]; |
| FuncVarPtg hex2Dec = (FuncVarPtg) ptgs[3]; |
| RefPtg o2 = (RefPtg) ptgs[4]; |
| RefPtg d2 = (RefPtg) ptgs[6]; |
| FuncVarPtg dec2Hex = (FuncVarPtg) ptgs[8]; |
| |
| assertEquals("O8", o8.toFormulaString()); |
| assertEquals(255, hex2Dec.getFunctionIndex()); |
| //assertEquals("", hex2Dec.toString()); |
| assertEquals("O2", o2.toFormulaString()); |
| assertEquals("D2", d2.toFormulaString()); |
| assertEquals(255, dec2Hex.getFunctionIndex()); |
| |
| wb.close(); |
| } |
| } |