poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestFormulaParser.java

/*
 *  ====================================================================
 *    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.
 */
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");
    }
}