| /* |
| * ==================================================================== |
| * 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.tests.formula; |
| |
| import static org.junit.jupiter.api.Assertions.assertEquals; |
| import static org.junit.jupiter.api.Assertions.assertNotNull; |
| import static org.junit.jupiter.api.Assertions.assertThrows; |
| import static org.junit.jupiter.api.Assertions.assertTrue; |
| |
| import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; |
| import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
| import org.apache.poi.ss.formula.FormulaParseException; |
| import org.apache.poi.ss.formula.FormulaParser; |
| import org.apache.poi.ss.formula.FormulaParsingWorkbook; |
| import org.apache.poi.ss.formula.FormulaType; |
| import org.apache.poi.ss.formula.ptg.AbstractFunctionPtg; |
| import org.apache.poi.ss.formula.ptg.NameXPxg; |
| import org.apache.poi.ss.formula.ptg.Ptg; |
| import org.apache.poi.ss.formula.ptg.Ref3DPxg; |
| import org.apache.poi.ss.formula.ptg.StringPtg; |
| import org.apache.poi.util.IOUtils; |
| import org.apache.poi.xssf.XSSFTestDataSamples; |
| import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook; |
| import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
| import org.junit.jupiter.api.Test; |
| |
| /** |
| * Test {@link FormulaParser}'s handling of row numbers at the edge of the |
| * HSSF/XSSF ranges. |
| * |
| * @author David North |
| */ |
| class TestFormulaParser { |
| |
| @Test |
| void testHSSFFailsForOver65536() { |
| FormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.create(new HSSFWorkbook()); |
| assertThrows(FormulaParseException.class, () -> |
| FormulaParser.parse("Sheet1!1:65537", workbook, FormulaType.CELL, 0)); |
| } |
| |
| private static void checkHSSFFormula(String formula) { |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| FormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.create(wb); |
| FormulaParser.parse(formula, workbook, FormulaType.CELL, 0); |
| IOUtils.closeQuietly(wb); |
| } |
| private static void checkXSSFFormula(String formula) { |
| XSSFWorkbook wb = new XSSFWorkbook(); |
| FormulaParsingWorkbook workbook = XSSFEvaluationWorkbook.create(wb); |
| FormulaParser.parse(formula, workbook, FormulaType.CELL, 0); |
| IOUtils.closeQuietly(wb); |
| } |
| private static void checkFormula(String formula) { |
| checkHSSFFormula(formula); |
| checkXSSFFormula(formula); |
| } |
| |
| @Test |
| void testHSSFPassCase() { |
| checkHSSFFormula("Sheet1!1:65536"); |
| } |
| |
| @Test |
| void testXSSFWorksForOver65536() { |
| checkXSSFFormula("Sheet1!1:65537"); |
| } |
| |
| @Test |
| void testXSSFFailCase() { |
| FormulaParsingWorkbook workbook = XSSFEvaluationWorkbook.create(new XSSFWorkbook()); |
| assertThrows(FormulaParseException.class, () -> |
| FormulaParser.parse("Sheet1!1:1048577", workbook, FormulaType.CELL, 0), "one more than max rows"); |
| } |
| |
| // copied from org.apache.poi.hssf.model.TestFormulaParser |
| @Test |
| void testMacroFunction() throws Exception { |
| // testNames.xlsm contains a VB function called 'myFunc' |
| final String testFile = "testNames.xlsm"; |
| try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(testFile)) { |
| XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb); |
| |
| //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)] |
| Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", workbook, FormulaType.CELL, -1); |
| assertEquals(3, ptg.length); |
| |
| // the name gets encoded as the first operand on the stack |
| NameXPxg tname = (NameXPxg) ptg[0]; |
| assertEquals("myFunc", tname.toFormulaString()); |
| |
| // 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]; |
| assertTrue(tfunc.isExternalFunction()); |
| |
| // confirm formula parsing is case-insensitive |
| FormulaParser.parse("mYfUnC(\"arg\")", workbook, 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()", workbook, FormulaType.CELL, -1); |
| FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", workbook, 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\")", workbook, FormulaType.CELL, -1); |
| |
| // Make sure workbook can be written and read |
| XSSFTestDataSamples.writeOutAndReadBack(wb).close(); |
| |
| // 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 = XSSFTestDataSamples.getSampleFile(testFile); |
| final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xlsm", "-saved.xlsm")); |
| final FileOutputStream fos = new FileOutputStream(reSavedFile); |
| wb.write(fos); |
| fos.close(); |
| */ |
| } |
| } |
| |
| @Test |
| void testParserErrors() throws Exception { |
| try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("testNames.xlsm")) { |
| XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb); |
| |
| parseExpectedException("("); |
| parseExpectedException(")"); |
| parseExpectedException("+"); |
| parseExpectedException("42+"); |
| parseExpectedException("IF()"); |
| parseExpectedException("IF("); //no closing paren |
| parseExpectedException("myFunc(", workbook); //no closing paren |
| } |
| } |
| |
| private static void parseExpectedException(String formula) { |
| parseExpectedException(formula, null); |
| } |
| |
| /** confirm formula has invalid syntax and parsing the formula results in FormulaParseException |
| */ |
| private static void parseExpectedException(String formula, FormulaParsingWorkbook wb) { |
| FormulaParseException e = assertThrows(FormulaParseException.class, () -> |
| FormulaParser.parse(formula, wb, FormulaType.CELL, -1)); |
| assertNotNull(e.getMessage()); |
| } |
| |
| // trivial case for bug 60219: FormulaParser can't parse external references when sheet name is quoted |
| @Test |
| void testParseExternalReferencesWithUnquotedSheetName() throws Exception { |
| XSSFWorkbook wb = new XSSFWorkbook(); |
| XSSFEvaluationWorkbook fpwb = XSSFEvaluationWorkbook.create(wb); |
| Ptg[] ptgs = FormulaParser.parse("[1]Sheet1!A1", fpwb, FormulaType.CELL, -1); |
| // org.apache.poi.ss.formula.ptg.Ref3DPxg [ [workbook=1] sheet=Sheet 1 ! A1] |
| assertEquals(1, ptgs.length, "Ptgs length"); |
| assertTrue(ptgs[0] instanceof Ref3DPxg, "Ptg class"); |
| Ref3DPxg pxg = (Ref3DPxg) ptgs[0]; |
| assertEquals(1, pxg.getExternalWorkbookNumber(), "External workbook number"); |
| assertEquals("Sheet1", pxg.getSheetName(), "Sheet name"); |
| assertEquals(0, pxg.getRow(), "Row"); |
| assertEquals(0, pxg.getColumn(), "Column"); |
| wb.close(); |
| } |
| |
| // bug 60219: FormulaParser can't parse external references when sheet name is quoted |
| @Test |
| void testParseExternalReferencesWithQuotedSheetName() throws Exception { |
| XSSFWorkbook wb = new XSSFWorkbook(); |
| XSSFEvaluationWorkbook fpwb = XSSFEvaluationWorkbook.create(wb); |
| Ptg[] ptgs = FormulaParser.parse("'[1]Sheet 1'!A1", fpwb, FormulaType.CELL, -1); |
| // org.apache.poi.ss.formula.ptg.Ref3DPxg [ [workbook=1] sheet=Sheet 1 ! A1] |
| assertEquals(1, ptgs.length, "Ptgs length"); |
| assertTrue(ptgs[0] instanceof Ref3DPxg, "Ptg class"); |
| Ref3DPxg pxg = (Ref3DPxg) ptgs[0]; |
| assertEquals(1, pxg.getExternalWorkbookNumber(), "External workbook number"); |
| assertEquals("Sheet 1", pxg.getSheetName(), "Sheet name"); |
| assertEquals(0, pxg.getRow(), "Row"); |
| assertEquals(0, pxg.getColumn(), "Column"); |
| wb.close(); |
| } |
| |
| // bug 60260 |
| @Test |
| void testUnicodeSheetName() { |
| checkFormula("'Sheet\u30FB1'!A1:A6"); |
| } |
| } |