TestXSSFFormulaParser.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.xssf.usermodel;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.io.IOException;
import java.util.Arrays;
import org.apache.poi.hssf.HSSFTestDataSamples;
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.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.WorkbookDependentFormula;
import org.apache.poi.ss.formula.ptg.Area3DPtg;
import org.apache.poi.ss.formula.ptg.Area3DPxg;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.AttrPtg;
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.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.NamePtg;
import org.apache.poi.ss.formula.ptg.NameXPxg;
import org.apache.poi.ss.formula.ptg.ParenthesisPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.Ref3DPtg;
import org.apache.poi.ss.formula.ptg.Ref3DPxg;
import org.apache.poi.ss.formula.ptg.RefPtg;
import org.apache.poi.ss.formula.ptg.StringPtg;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.junit.jupiter.api.Test;
public final class TestXSSFFormulaParser {
private static Ptg[] parse(FormulaParsingWorkbook fpb, String fmla) {
return FormulaParser.parse(fmla, fpb, FormulaType.CELL, -1);
}
private static Ptg[] parse(FormulaParsingWorkbook fpb, String fmla, int rowIndex) {
return FormulaParser.parse(fmla, fpb, FormulaType.CELL, -1, rowIndex);
}
@Test
void basicParsing() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
ptgs = parse(fpb, "ABC10");
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg, "Had " + Arrays.toString(ptgs));
ptgs = parse(fpb, "A500000");
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg, "Had " + Arrays.toString(ptgs));
ptgs = parse(fpb, "ABC500000");
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg, "Had " + Arrays.toString(ptgs));
//highest allowed rows and column (XFD and 0x100000)
ptgs = parse(fpb, "XFD1048576");
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg, "Had " + Arrays.toString(ptgs));
//column greater than XFD
FormulaParseException e;
e = assertThrows(FormulaParseException.class, () -> parse(fpb, "XFE10"));
assertEquals("Specified named range 'XFE10' does not exist in the current workbook.", e.getMessage());
//row greater than 0x100000
e = assertThrows(FormulaParseException.class, () -> parse(fpb, "XFD1048577"));
assertEquals("Specified named range 'XFD1048577' does not exist in the current workbook.", e.getMessage());
// Formula referencing one cell
ptgs = parse(fpb, "ISEVEN(A1)");
assertEquals(3, ptgs.length);
assertEquals(NameXPxg.class, ptgs[0].getClass());
assertEquals(RefPtg.class, ptgs[1].getClass());
assertEquals(FuncVarPtg.class, ptgs[2].getClass());
assertEquals("ISEVEN", ptgs[0].toFormulaString());
assertEquals("A1", ptgs[1].toFormulaString());
assertEquals("#external#", ptgs[2].toFormulaString());
// Formula referencing an area
ptgs = parse(fpb, "SUM(A1:B3)");
assertEquals(2, ptgs.length);
assertEquals(AreaPtg.class, ptgs[0].getClass());
assertEquals(AttrPtg.class, ptgs[1].getClass());
assertEquals("A1:B3", ptgs[0].toFormulaString());
assertEquals("SUM", ptgs[1].toFormulaString());
// Formula referencing one cell in a different sheet
ptgs = parse(fpb, "SUM(Sheet1!A1)");
assertEquals(2, ptgs.length);
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
assertEquals(AttrPtg.class, ptgs[1].getClass());
assertEquals("Sheet1!A1", ptgs[0].toFormulaString());
assertEquals("SUM", ptgs[1].toFormulaString());
// Formula referencing an area in a different sheet
ptgs = parse(fpb, "SUM(Sheet1!A1:B3)");
assertEquals(2, ptgs.length);
assertEquals(Area3DPxg.class,ptgs[0].getClass());
assertEquals(AttrPtg.class, ptgs[1].getClass());
assertEquals("Sheet1!A1:B3", ptgs[0].toFormulaString());
assertEquals("SUM", ptgs[1].toFormulaString());
wb.close();
}
@Test
void builtInFormulas() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
ptgs = parse(fpb, "LOG10");
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
ptgs = parse(fpb, "LOG10(100)");
assertEquals(2, ptgs.length);
assertTrue(ptgs[0] instanceof IntPtg);
assertTrue(ptgs[1] instanceof FuncPtg);
wb.close();
}
@Test
void formulaReferencesSameWorkbook() throws IOException {
// Use a test file with "other workbook" style references
// to itself
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// Reference to a named range in our own workbook, as if it
// were defined in a different workbook
ptgs = parse(fpb, "[0]!NR_Global_B2");
assertEquals(1, ptgs.length);
assertEquals(NameXPxg.class, ptgs[0].getClass());
assertEquals(0, ((NameXPxg)ptgs[0]).getExternalWorkbookNumber());
assertNull(((NameXPxg) ptgs[0]).getSheetName());
assertEquals("NR_Global_B2",((NameXPxg)ptgs[0]).getNameName());
assertEquals("[0]!NR_Global_B2", ptgs[0].toFormulaString());
wb.close();
}
@Test
void formulaReferencesOtherSheets() throws IOException {
// Use a test file with the named ranges in place
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// Reference to a single cell in a different sheet
ptgs = parse(fpb, "Uses!A1");
assertEquals(1, ptgs.length);
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
assertEquals(-1, ((Ref3DPxg)ptgs[0]).getExternalWorkbookNumber());
assertEquals("A1", ((Ref3DPxg)ptgs[0]).format2DRefAsString());
assertEquals("Uses!A1", ptgs[0].toFormulaString());
// Reference to a single cell in a different sheet, which needs quoting
ptgs = parse(fpb, "'Testing 47100'!A1");
assertEquals(1, ptgs.length);
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
assertEquals(-1, ((Ref3DPxg)ptgs[0]).getExternalWorkbookNumber());
assertEquals("Testing 47100", ((Ref3DPxg)ptgs[0]).getSheetName());
assertEquals("A1", ((Ref3DPxg)ptgs[0]).format2DRefAsString());
assertEquals("'Testing 47100'!A1", ptgs[0].toFormulaString());
// Reference to a sheet scoped named range from another sheet
ptgs = parse(fpb, "Defines!NR_To_A1");
assertEquals(1, ptgs.length);
assertEquals(NameXPxg.class, ptgs[0].getClass());
assertEquals(-1, ((NameXPxg)ptgs[0]).getExternalWorkbookNumber());
assertEquals("Defines", ((NameXPxg)ptgs[0]).getSheetName());
assertEquals("NR_To_A1",((NameXPxg)ptgs[0]).getNameName());
assertEquals("Defines!NR_To_A1", ptgs[0].toFormulaString());
// Reference to a workbook scoped named range
ptgs = parse(fpb, "NR_Global_B2");
assertEquals(1, ptgs.length);
assertEquals(NamePtg.class, ptgs[0].getClass());
assertEquals("NR_Global_B2",((NamePtg)ptgs[0]).toFormulaString(fpb));
wb.close();
}
@Test
void formulaReferencesOtherWorkbook() throws IOException {
// Use a test file with the external linked table in place
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// Reference to a single cell in a different workbook
ptgs = parse(fpb, "[1]Uses!$A$1");
assertEquals(1, ptgs.length);
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
assertEquals(1, ((Ref3DPxg)ptgs[0]).getExternalWorkbookNumber());
assertEquals("Uses",((Ref3DPxg)ptgs[0]).getSheetName());
assertEquals("$A$1",((Ref3DPxg)ptgs[0]).format2DRefAsString());
assertEquals("[1]Uses!$A$1", ptgs[0].toFormulaString());
// Reference to a sheet-scoped named range in a different workbook
ptgs = parse(fpb, "[1]Defines!NR_To_A1");
assertEquals(1, ptgs.length);
assertEquals(NameXPxg.class, ptgs[0].getClass());
assertEquals(1, ((NameXPxg)ptgs[0]).getExternalWorkbookNumber());
assertEquals("Defines", ((NameXPxg)ptgs[0]).getSheetName());
assertEquals("NR_To_A1",((NameXPxg)ptgs[0]).getNameName());
assertEquals("[1]Defines!NR_To_A1", ptgs[0].toFormulaString());
// Reference to a global named range in a different workbook
ptgs = parse(fpb, "[1]!NR_Global_B2");
assertEquals(1, ptgs.length);
assertEquals(NameXPxg.class, ptgs[0].getClass());
assertEquals(1, ((NameXPxg)ptgs[0]).getExternalWorkbookNumber());
assertNull(((NameXPxg) ptgs[0]).getSheetName());
assertEquals("NR_Global_B2",((NameXPxg)ptgs[0]).getNameName());
assertEquals("[1]!NR_Global_B2", ptgs[0].toFormulaString());
wb.close();
}
/**
* A handful of functions (such as SUM, COUNTA, MIN) support
* multi-sheet references (eg Sheet1:Sheet3!A1 = Cell A1 from
* Sheets 1 through Sheet 3) and multi-sheet area references
* (eg Sheet1:Sheet3!A1:B2 = Cells A1 through B2 from Sheets
* 1 through Sheet 3).
* This test, based on common test files for HSSF and XSSF, checks
* that we can read and parse these kinds of references
* (but not evaluate - that's elsewhere in the test suite)
*/
@Test
void multiSheetReferencesHSSFandXSSF() throws IOException {
Workbook[] wbs = new Workbook[] {
HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls"),
XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx")
};
for (Workbook wb : wbs) {
Sheet s1 = wb.getSheetAt(0);
Ptg[] ptgs;
// Check the contents
Cell sumF = s1.getRow(2).getCell(0);
assertNotNull(sumF);
assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
Cell avgF = s1.getRow(2).getCell(1);
assertNotNull(avgF);
assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
Cell countAF = s1.getRow(2).getCell(2);
assertNotNull(countAF);
assertEquals("COUNTA(Sheet1:Sheet3!C1)", countAF.getCellFormula());
Cell maxF = s1.getRow(4).getCell(1);
assertNotNull(maxF);
assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
Cell sumFA = s1.getRow(2).getCell(7);
assertNotNull(sumFA);
assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
Cell avgFA = s1.getRow(2).getCell(8);
assertNotNull(avgFA);
assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
Cell maxFA = s1.getRow(4).getCell(8);
assertNotNull(maxFA);
assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
Cell countFA = s1.getRow(5).getCell(8);
assertNotNull(countFA);
assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
// Create a formula parser
final FormulaParsingWorkbook fpb;
if (wb instanceof HSSFWorkbook)
fpb = HSSFEvaluationWorkbook.create((HSSFWorkbook)wb);
else
fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook)wb);
// Check things parse as expected:
// SUM to one cell over 3 workbooks, relative reference
ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1)");
assertEquals(2, ptgs.length);
if (wb instanceof HSSFWorkbook) {
assertEquals(Ref3DPtg.class, ptgs[0].getClass());
} else {
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
}
assertEquals("Sheet1:Sheet3!A1", toFormulaString(ptgs[0], fpb));
assertEquals(AttrPtg.class, ptgs[1].getClass());
assertEquals("SUM", toFormulaString(ptgs[1], fpb));
// MAX to one cell over 3 workbooks, absolute row reference
ptgs = parse(fpb, "MAX(Sheet1:Sheet3!A$1)");
assertEquals(2, ptgs.length);
if (wb instanceof HSSFWorkbook) {
assertEquals(Ref3DPtg.class, ptgs[0].getClass());
} else {
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
}
assertEquals("Sheet1:Sheet3!A$1", toFormulaString(ptgs[0], fpb));
assertEquals(FuncVarPtg.class, ptgs[1].getClass());
assertEquals("MAX", toFormulaString(ptgs[1], fpb));
// MIN to one cell over 3 workbooks, absolute reference
ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1)");
assertEquals(2, ptgs.length);
if (wb instanceof HSSFWorkbook) {
assertEquals(Ref3DPtg.class, ptgs[0].getClass());
} else {
assertEquals(Ref3DPxg.class, ptgs[0].getClass());
}
assertEquals("Sheet1:Sheet3!$A$1", toFormulaString(ptgs[0], fpb));
assertEquals(FuncVarPtg.class, ptgs[1].getClass());
assertEquals("MIN", toFormulaString(ptgs[1], fpb));
// SUM to a range of cells over 3 workbooks
ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)");
assertEquals(2, ptgs.length);
if (wb instanceof HSSFWorkbook) {
assertEquals(Area3DPtg.class, ptgs[0].getClass());
} else {
assertEquals(Area3DPxg.class, ptgs[0].getClass());
}
assertEquals("Sheet1:Sheet3!A1:B2", toFormulaString(ptgs[0], fpb));
assertEquals(AttrPtg.class, ptgs[1].getClass());
assertEquals("SUM", toFormulaString(ptgs[1], fpb));
// MIN to a range of cells over 3 workbooks, absolute reference
ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)");
assertEquals(2, ptgs.length);
if (wb instanceof HSSFWorkbook) {
assertEquals(Area3DPtg.class, ptgs[0].getClass());
} else {
assertEquals(Area3DPxg.class, ptgs[0].getClass());
}
assertEquals("Sheet1:Sheet3!$A$1:$B$2", toFormulaString(ptgs[0], fpb));
assertEquals(FuncVarPtg.class, ptgs[1].getClass());
assertEquals("MIN", toFormulaString(ptgs[1], fpb));
// Check we can round-trip - try to set a new one to a new single cell
Cell newF = s1.getRow(0).createCell(10, CellType.FORMULA);
newF.setCellFormula("SUM(Sheet2:Sheet3!A1)");
assertEquals("SUM(Sheet2:Sheet3!A1)", newF.getCellFormula());
// Check we can round-trip - try to set a new one to a cell range
newF = s1.getRow(0).createCell(11, CellType.FORMULA);
newF.setCellFormula("MIN(Sheet1:Sheet2!A1:B2)");
assertEquals("MIN(Sheet1:Sheet2!A1:B2)", newF.getCellFormula());
wb.close();
}
}
@Test
void testQuotedSheetNamesReference() throws IOException {
// quoted sheet names bug fix
// see TestHSSFFormulaEvaluator equivalent which behaves a little differently
try (XSSFWorkbook wb = new XSSFWorkbook()) {
Sheet sheet1 = wb.createSheet("Sheet1");
Sheet sheet2 = wb.createSheet("Sheet2");
Sheet sheet3 = wb.createSheet("Sheet 3");
Sheet sheet4 = wb.createSheet("Sheet4>");
Row tempRow = sheet1.createRow(0);
tempRow.createCell(0).setCellValue(1);
tempRow.createCell(1).setCellValue(2);
tempRow = sheet2.createRow(0);
tempRow.createCell(0).setCellValue(3);
tempRow.createCell(1).setCellValue(4);
tempRow = sheet3.createRow(0);
tempRow.createCell(0).setCellValue(5);
tempRow.createCell(1).setCellValue(6);
tempRow = sheet4.createRow(0);
tempRow.createCell(0).setCellValue(5);
tempRow.createCell(1).setCellValue(6);
Cell cell = tempRow.createCell(2);
// unquoted sheet names
String formula = "SUM(Sheet1:Sheet2!A1:B1)";
cell.setCellFormula(formula);
String cellFormula = cell.getCellFormula();
assertEquals(formula, cellFormula);
// quoted sheet names with no space
cell = tempRow.createCell(3);
formula = "SUM('Sheet1:Sheet2'!A1:B1)";
cell.setCellFormula(formula);
cellFormula = cell.getCellFormula();
assertEquals("SUM('Sheet1:Sheet2'!A1:B1)", cellFormula);
// quoted sheet names with space
cell = tempRow.createCell(4);
formula = "SUM('Sheet1:Sheet 3'!A1:B1)";
cell.setCellFormula(formula);
cellFormula = cell.getCellFormula();
assertEquals(formula, cellFormula);
// quoted sheet names with special character
cell = tempRow.createCell(5);
formula = "SUM('Sheet1:Sheet4>'!A1:B1)";
cell.setCellFormula(formula);
cellFormula = cell.getCellFormula();
assertEquals(formula, cellFormula);
// quoted sheet names with special character #2
// cell = tempRow.createCell(6);
// formula = "SUM('Sheet 3:Sheet4>'!A1:B1)";
// cell.setCellFormula(formula);
// cellFormula = cell.getCellFormula();
// assertEquals(formula, cellFormula);
}
}
private static String toFormulaString(Ptg ptg, FormulaParsingWorkbook wb) {
if (ptg instanceof WorkbookDependentFormula) {
return ((WorkbookDependentFormula)ptg).toFormulaString((FormulaRenderingWorkbook)wb);
}
return ptg.toFormulaString();
}
@Test
void test58648Single() throws IOException {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
ptgs = parse(fpb, "(ABC10 )");
assertEquals(2, ptgs.length, "Had: " + Arrays.toString(ptgs));
assertTrue(ptgs[0] instanceof RefPtg, "Had " + Arrays.toString(ptgs));
assertTrue(ptgs[1] instanceof ParenthesisPtg, "Had " + Arrays.toString(ptgs));
}
}
@Test
void test58648Basic() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// verify whitespaces in different places
ptgs = parse(fpb, "(ABC10)");
assertEquals(2, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
ptgs = parse(fpb, "( ABC10)");
assertEquals(2, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
ptgs = parse(fpb, "(ABC10 )");
assertEquals(2, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
ptgs = parse(fpb, "((ABC10))");
assertEquals(3, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
assertTrue(ptgs[2] instanceof ParenthesisPtg);
ptgs = parse(fpb, "((ABC10) )");
assertEquals(3, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
assertTrue(ptgs[2] instanceof ParenthesisPtg);
ptgs = parse(fpb, "( (ABC10))");
assertEquals(3, ptgs.length);
assertTrue(ptgs[0] instanceof RefPtg);
assertTrue(ptgs[1] instanceof ParenthesisPtg);
assertTrue(ptgs[2] instanceof ParenthesisPtg);
wb.close();
}
@Test
void test58648FormulaParsing() throws IOException {
Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58648.xlsx");
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet xsheet = wb.getSheetAt(i);
for (Row row : xsheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.FORMULA) {
try {
evaluator.evaluateFormulaCell(cell);
} catch (Exception e) {
CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
throw new RuntimeException("error at: " + cellRef, e);
}
}
}
}
}
Sheet sheet = wb.getSheet("my-sheet");
Cell cell = sheet.getRow(1).getCell(4);
assertEquals(CellType.FORMULA, cell.getCellType(),
"Had: " + cell);
assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultType(),
"Had: " + cell + " and " + cell.getCachedFormulaResultType());
assertEquals(5d, cell.getNumericCellValue(), 0d);
wb.close();
}
@Test
void testWhitespaceInFormula() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// verify whitespaces in different places
ptgs = parse(fpb, "INTERCEPT(A2:A5, B2:B5)");
assertEquals(3, ptgs.length);
assertTrue(ptgs[0] instanceof AreaPtg);
assertTrue(ptgs[1] instanceof AreaPtg);
assertTrue(ptgs[2] instanceof FuncPtg);
ptgs = parse(fpb, " INTERCEPT ( \t \r A2 : \nA5 , B2 : B5 ) \t");
assertEquals(3, ptgs.length);
assertTrue(ptgs[0] instanceof AreaPtg);
assertTrue(ptgs[1] instanceof AreaPtg);
assertTrue(ptgs[2] instanceof FuncPtg);
ptgs = parse(fpb, "(VLOOKUP(\"item1\", A2:B3, 2, FALSE) - VLOOKUP(\"item2\", A2:B3, 2, FALSE) )");
assertEquals(12, ptgs.length);
assertTrue(ptgs[0] instanceof StringPtg);
assertTrue(ptgs[1] instanceof AreaPtg);
assertTrue(ptgs[2] instanceof IntPtg);
ptgs = parse(fpb, "A1:B1 B1:B2");
assertEquals(4, ptgs.length);
assertTrue(ptgs[0] instanceof MemAreaPtg);
assertTrue(ptgs[1] instanceof AreaPtg);
assertTrue(ptgs[2] instanceof AreaPtg);
assertTrue(ptgs[3] instanceof IntersectionPtg);
ptgs = parse(fpb, "A1:B1 B1:B2");
assertEquals(4, ptgs.length);
assertTrue(ptgs[0] instanceof MemAreaPtg);
assertTrue(ptgs[1] instanceof AreaPtg);
assertTrue(ptgs[2] instanceof AreaPtg);
assertTrue(ptgs[3] instanceof IntersectionPtg);
wb.close();
}
@Test
void testWhitespaceInComplexFormula() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
// verify whitespaces in different places
ptgs = parse(fpb, "SUM(A1:INDEX(1:1048576,MAX(IFERROR(MATCH(99^99,B:B,1),0),IFERROR(MATCH(\"zzzz\",B:B,1),0)),MAX(IFERROR(MATCH(99^99,1:1,1),0),IFERROR(MATCH(\"zzzz\",1:1,1),0))))");
assertEquals(40, ptgs.length);
assertTrue(ptgs[0] instanceof MemFuncPtg);
assertTrue(ptgs[1] instanceof RefPtg);
assertTrue(ptgs[2] instanceof AreaPtg);
assertTrue(ptgs[3] instanceof NameXPxg);
ptgs = parse(fpb, "SUM ( A1 : INDEX( 1 : 1048576 , MAX( IFERROR ( MATCH ( 99 ^ 99 , B : B , 1 ) , 0 ) , IFERROR ( MATCH ( \"zzzz\" , B:B , 1 ) , 0 ) ) , MAX ( IFERROR ( MATCH ( 99 ^ 99 , 1 : 1 , 1 ) , 0 ) , IFERROR ( MATCH ( \"zzzz\" , 1 : 1 , 1 ) , 0 ) ) ) )");
assertEquals(40, ptgs.length);
assertTrue(ptgs[0] instanceof MemFuncPtg);
assertTrue(ptgs[1] instanceof RefPtg);
assertTrue(ptgs[2] instanceof AreaPtg);
assertTrue(ptgs[3] instanceof NameXPxg);
wb.close();
}
@Test
void parseStructuredReferences() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
Ptg[] ptgs;
/*
The following cases are tested (copied from FormulaParser.parseStructuredReference)
1 Table1[col]
2 Table1[[#Totals],[col]]
3 Table1[#Totals]
4 Table1[#All]
5 Table1[#Data]
6 Table1[#Headers]
7 Table1[#Totals]
8 Table1[#This Row]
9 Table1[[#All],[col]]
10 Table1[[#Headers],[col]]
11 Table1[[#Totals],[col]]
12 Table1[[#All],[col1]:[col2]]
13 Table1[[#Data],[col1]:[col2]]
14 Table1[[#Headers],[col1]:[col2]]
15 Table1[[#Totals],[col1]:[col2]]
16 Table1[[#Headers],[#Data],[col2]]
17 Table1[[#This Row], [col1]]
18 Table1[ [col1]:[col2] ]
19 Table1[]
*/
final String tbl = "\\_Prime.1";
final String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]";
////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs ////////
ptgs = parse(fpb, "SUM("+tbl+"[calc='#*'#])");
assertEquals(2, ptgs.length);
// Area3DPxg [sheet=Table ! A2:A7]
assertTrue(ptgs[0] instanceof Area3DPxg);
Area3DPxg ptg0 = (Area3DPxg) ptgs[0];
assertEquals("Table", ptg0.getSheetName());
assertEquals("A2:A7", ptg0.format2DRefAsString());
// Note: structured references are evaluated and resolved to regular 3D area references.
assertEquals("Table!A2:A7", ptg0.toFormulaString());
// AttrPtg [sum ]
assertTrue(ptgs[1] instanceof AttrPtg);
AttrPtg ptg1 = (AttrPtg) ptgs[1];
assertTrue(ptg1.isSum());
////// Case 1: Evaluate "Table1[col]" ////////
ptgs = parse(fpb, tbl+"[Name]");
assertEquals(1, ptgs.length);
assertEquals("Table!B2:B7", ptgs[0].toFormulaString(), "Table1[col]");
////// Case 2: Evaluate "Table1[[#Totals],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#Totals],[col]]");
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);
////// Case 3: Evaluate "Table1[#Totals]" ////////
ptgs = parse(fpb, tbl+"[#Totals]");
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);
////// Case 4: Evaluate "Table1[#All]" ////////
ptgs = parse(fpb, tbl+"[#All]");
assertEquals(1, ptgs.length);
assertEquals("Table!A1:C7", ptgs[0].toFormulaString(), "Table1[#All]");
////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Total rows) ////////
ptgs = parse(fpb, tbl+"[#Data]");
assertEquals(1, ptgs.length);
assertEquals("Table!A2:C7", ptgs[0].toFormulaString(), "Table1[#Data]");
////// Case 6: Evaluate "Table1[#Headers]" ////////
ptgs = parse(fpb, tbl+"[#Headers]");
assertEquals(1, ptgs.length);
assertEquals("Table!A1:C1", ptgs[0].toFormulaString(), "Table1[#Headers]");
////// Case 7: Evaluate "Table1[#Totals]" ////////
ptgs = parse(fpb, tbl+"[#Totals]");
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);
////// Case 8: Evaluate "Table1[#This Row]" ////////
ptgs = parse(fpb, tbl+"[#This Row]", 2);
assertEquals(1, ptgs.length);
assertEquals("Table!A3:C3", ptgs[0].toFormulaString(), "Table1[#This Row]");
////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") ////////
ptgs = parse(fpb, tbl+"[@]", 2);
assertEquals(1, ptgs.length);
assertEquals("Table!A3:C3", ptgs[0].toFormulaString());
////// Evaluate "Table1[#This Row]" when rowIndex is outside Table ////////
ptgs = parse(fpb, tbl+"[#This Row]", 10);
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[#This Row]");
////// Evaluate "Table1[@]" when rowIndex is outside Table ////////
ptgs = parse(fpb, tbl+"[@]", 10);
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[@]");
////// Evaluate "Table1[[#Data],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#Data], [Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!C2:C7", ptgs[0].toFormulaString(), "Table1[[#Data],[col]]");
////// Case 9: Evaluate "Table1[[#All],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#All], [Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!C1:C7", ptgs[0].toFormulaString(), "Table1[[#All],[col]]");
////// Case 10: Evaluate "Table1[[#Headers],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#Headers], [Number]]");
assertEquals(1, ptgs.length);
// also acceptable: Table1!B1
assertEquals("Table!C1:C1", ptgs[0].toFormulaString(), "Table1[[#Headers],[col]]");
////// Case 11: Evaluate "Table1[[#Totals],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#Totals],[Name]]");
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);
////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" ////////
ptgs = parse(fpb, tbl+"[[#All], [Name]:[Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!B1:C7", ptgs[0].toFormulaString(), "Table1[[#All],[col1]:[col2]]");
////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" ////////
ptgs = parse(fpb, tbl+"[[#Data], [Name]:[Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!B2:C7", ptgs[0].toFormulaString(), "Table1[[#Data],[col]:[col2]]");
////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" ////////
ptgs = parse(fpb, tbl+"[[#Headers], [Name]:[Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!B1:C1", ptgs[0].toFormulaString(), "Table1[[#Headers],[col1]:[col2]]");
////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" ////////
ptgs = parse(fpb, tbl+"[[#Totals], [Name]:[Number]]");
assertEquals(1, ptgs.length);
assertEquals(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason);
////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" ////////
ptgs = parse(fpb, tbl+"[[#Headers],[#Data],[Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!C1:C7", ptgs[0].toFormulaString(), "Table1[[#Headers],[#Data],[col]]");
////// Case 17: Evaluate "Table1[[#This Row], [col1]]" ////////
ptgs = parse(fpb, tbl+"[[#This Row], [Number]]", 2);
assertEquals(1, ptgs.length);
// also acceptable: Table!C3
assertEquals("Table!C3:C3", ptgs[0].toFormulaString(), "Table1[[#This Row], [col1]]");
////// Case 18: Evaluate "Table1[[col]:[col2]]" ////////
ptgs = parse(fpb, tbl+"[[Name]:[Number]]");
assertEquals(1, ptgs.length);
assertEquals("Table!B2:C7", ptgs[0].toFormulaString(), "Table1[[col]:[col2]]");
////// Case 19: Evaluate "Table1[]" ////////
// Excludes Header and Total rows, equivalent to Table1[#Data] (see case 5).
// This is the only case where [] is allowed.
ptgs = parse(fpb, tbl+"[]");
assertEquals(1, ptgs.length);
assertEquals("Table!A2:C7", ptgs[0].toFormulaString(), "Table1[]");
wb.close();
}
}