log4j/src/testcases/org/apache/poi/ss/formula/functions/TestIndirect.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.formula.functions;

import static org.junit.jupiter.api.Assertions.assertEquals;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.junit.jupiter.api.Test;

/**
 * Tests for the INDIRECT() function.</p>
 */
final class TestIndirect {
    // convenient access to namespace
    // private static final ErrorEval EE = null;

    private static void createDataRow(HSSFSheet sheet, int rowIndex, double... vals) {
        HSSFRow row = sheet.createRow(rowIndex);
        for (int i = 0; i < vals.length; i++) {
            row.createCell(i).setCellValue(vals[i]);
        }
    }

    private static HSSFWorkbook createWBA() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        HSSFSheet sheet2 = wb.createSheet("Sheet2");
        HSSFSheet sheet3 = wb.createSheet("John's sales");

        createDataRow(sheet1, 0, 11, 12, 13, 14);
        createDataRow(sheet1, 1, 21, 22, 23, 24);
        createDataRow(sheet1, 2, 31, 32, 33, 34);

        createDataRow(sheet2, 0, 50, 55, 60, 65);
        createDataRow(sheet2, 1, 51, 56, 61, 66);
        createDataRow(sheet2, 2, 52, 57, 62, 67);

        createDataRow(sheet3, 0, 30, 31, 32);
        createDataRow(sheet3, 1, 33, 34, 35);

        HSSFName name1 = wb.createName();
        name1.setNameName("sales1");
        name1.setRefersToFormula("Sheet1!A1:D1");

        HSSFName name2 = wb.createName();
        name2.setNameName("sales2");
        name2.setRefersToFormula("Sheet2!B1:C3");

        HSSFRow row = sheet1.createRow(3);
        row.createCell(0).setCellValue("sales1");  //A4
        row.createCell(1).setCellValue("sales2");  //B4

        return wb;
    }

    private static HSSFWorkbook createWBB() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        HSSFSheet sheet2 = wb.createSheet("Sheet2");
        HSSFSheet sheet3 = wb.createSheet("## Look here!");

        createDataRow(sheet1, 0, 400, 440, 480, 520);
        createDataRow(sheet1, 1, 420, 460, 500, 540);

        createDataRow(sheet2, 0, 50, 55, 60, 65);
        createDataRow(sheet2, 1, 51, 56, 61, 66);

        createDataRow(sheet3, 0, 42);

        return wb;
    }

    @Test
    void testBasic() throws Exception {

        HSSFWorkbook wbA = createWBA();
        HSSFCell c = wbA.getSheetAt(0).createRow(5).createCell(2);
        HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);

        // non-error cases
        confirm(feA, c, "INDIRECT(\"C2\")", 23);
        confirm(feA, c, "INDIRECT(\"C2\", TRUE)", 23);
        confirm(feA, c, "INDIRECT(\"$C2\")", 23);
        confirm(feA, c, "INDIRECT(\"C$2\")", 23);
        confirm(feA, c, "SUM(INDIRECT(\"Sheet2!B1:C3\"))", 351); // area ref
        confirm(feA, c, "SUM(INDIRECT(\"Sheet2! B1 : C3 \"))", 351); // spaces in area ref
        confirm(feA, c, "SUM(INDIRECT(\"'John''s sales'!A1:C1\"))", 93); // special chars in sheet name
        confirm(feA, c, "INDIRECT(\"'Sheet1'!B3\")", 32); // redundant sheet name quotes
        confirm(feA, c, "INDIRECT(\"sHeet1!B3\")", 32); // case-insensitive sheet name
        confirm(feA, c, "INDIRECT(\" D3 \")", 34); // spaces around cell ref
        confirm(feA, c, "INDIRECT(\"Sheet1! D3 \")", 34); // spaces around cell ref
        confirm(feA, c, "INDIRECT(\"A1\", TRUE)", 11); // explicit arg1. only TRUE supported so far

        confirm(feA, c, "INDIRECT(\"A1:G1\")", 13); // de-reference area ref (note formula is in C4)

        confirm(feA, c, "SUM(INDIRECT(A4))", 50); // indirect defined name
        confirm(feA, c, "SUM(INDIRECT(B4))", 351); // indirect defined name pointinh to other sheet

        // simple error propagation:

        // arg0 is evaluated to text first
        confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
        confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
        confirm(feA, c, "INDIRECT(#NAME?, \"x\")", ErrorEval.NAME_INVALID);
        confirm(feA, c, "INDIRECT(#NUM!, #N/A)", ErrorEval.NUM_ERROR);

        // arg1 is evaluated to boolean before arg0 is decoded
        confirm(feA, c, "INDIRECT(\"garbage\", #N/A)", ErrorEval.NA);
        confirm(feA, c, "INDIRECT(\"garbage\", \"\")", ErrorEval.VALUE_INVALID); // empty string is not valid boolean
        confirm(feA, c, "INDIRECT(\"garbage\", \"flase\")", ErrorEval.VALUE_INVALID); // must be "TRUE" or "FALSE"


        // spaces around sheet name (with or without quotes makes no difference)
        confirm(feA, c, "INDIRECT(\"'Sheet1 '!D3\")", ErrorEval.REF_INVALID);
        confirm(feA, c, "INDIRECT(\" Sheet1!D3\")", ErrorEval.REF_INVALID);
        confirm(feA, c, "INDIRECT(\"'Sheet1' !D3\")", ErrorEval.REF_INVALID);


        confirm(feA, c, "SUM(INDIRECT(\"'John's sales'!A1:C1\"))", ErrorEval.REF_INVALID); // bad quote escaping
        confirm(feA, c, "INDIRECT(\"[Book1]Sheet1!A1\")", ErrorEval.REF_INVALID); // unknown external workbook
        confirm(feA, c, "INDIRECT(\"Sheet3!A1\")", ErrorEval.REF_INVALID); // unknown sheet
//        if (false) { // TODO - support evaluation of defined names
//            confirm(feA, c, "INDIRECT(\"Sheet1!IW1\")", ErrorEval.REF_INVALID); // bad column
//            confirm(feA, c, "INDIRECT(\"Sheet1!A65537\")", ErrorEval.REF_INVALID); // bad row
//        }
        confirm(feA, c, "INDIRECT(\"Sheet1!A 1\")", ErrorEval.REF_INVALID); // space in cell ref

        wbA.close();
    }

    @Test
    void testMultipleWorkbooks() throws Exception {
        HSSFWorkbook wbA = createWBA();
        HSSFCell cellA = wbA.getSheetAt(0).createRow(10).createCell(0);
        HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);

        HSSFWorkbook wbB = createWBB();
        HSSFCell cellB = wbB.getSheetAt(0).createRow(10).createCell(0);
        HSSFFormulaEvaluator feB = new HSSFFormulaEvaluator(wbB);

        String[] workbookNames = { "MyBook", "Figures for January", };
        HSSFFormulaEvaluator[] evaluators = { feA, feB, };
        HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);

        confirm(feB, cellB, "INDIRECT(\"'[Figures for January]## Look here!'!A1\")", 42); // same wb
        confirm(feA, cellA, "INDIRECT(\"'[Figures for January]## Look here!'!A1\")", 42); // across workbooks

        // 2 level recursion
        confirm(feB, cellB, "INDIRECT(\"[MyBook]Sheet2!A1\")", 50); // set up (and check) first level
        confirm(feA, cellA, "INDIRECT(\"'[Figures for January]Sheet1'!A11\")", 50); // points to cellB

        wbB.close();
        wbA.close();
    }

    private static void confirm(FormulaEvaluator fe, Cell cell, String formula, double expectedResult) {
        fe.clearAllCachedResultValues();
        cell.setCellFormula(formula);
        CellValue cv = fe.evaluate(cell);
        assertEquals(CellType.NUMERIC, cv.getCellType(), "expected numeric cell type but got " + cv.formatAsString());
        assertEquals(expectedResult, cv.getNumberValue(), 0.0);
    }

    private static void confirm(FormulaEvaluator fe, Cell cell, String formula, ErrorEval expectedResult) {
        fe.clearAllCachedResultValues();
        cell.setCellFormula(formula);
        CellValue cv = fe.evaluate(cell);
        assertEquals(CellType.ERROR, cv.getCellType(), "expected error cell type but got " + cv.formatAsString());
        int expCode = expectedResult.getErrorCode();
        assertEquals(expCode, cv.getErrorValue(), "Expected error '" + ErrorEval.getText(expCode) + "' but got '" + cv.formatAsString() + "'.");
    }

    @Test
    void testInvalidInput() {
        assertEquals(ErrorEval.VALUE_INVALID, Indirect.instance.evaluate(new ValueEval[] {}, null));
    }
}