/* ====================================================================
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.Assert.*;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator;
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.FormulaError;
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.XSSFITestDataProvider;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.junit.Ignore;
import org.junit.Test;
public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator {
public TestXSSFFormulaEvaluation() {
super(XSSFITestDataProvider.instance);
}
@Test
public void testSharedFormulas() throws IOException {
baseTestSharedFormulas("shared_formulas.xlsx");
}
@Test
public void testSharedFormulas_evaluateInCell() throws IOException {
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.openSampleWorkbook("49872.xlsx");
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
XSSFSheet sheet = wb.getSheetAt(0);
double result = 3.0;
// B3 is a master shared formula, C3 and D3 don't have the formula written in their f element.
// Instead, the attribute si for a particular cell is used to figure what the formula expression
// should be based on the cell's relative location to the master formula, e.g.
// B3: <f t="shared" ref="B3:D3" si="0">B1+B2</f>
// C3 and D3: <f t="shared" si="0"/>
// get B3 and evaluate it in the cell
XSSFCell b3 = sheet.getRow(2).getCell(1);
assertEquals(result, evaluator.evaluateInCell(b3).getNumericCellValue(), 0);
//at this point the master formula is gone, but we are still able to evaluate dependent cells
XSSFCell c3 = sheet.getRow(2).getCell(2);
assertEquals(result, evaluator.evaluateInCell(c3).getNumericCellValue(), 0);
XSSFCell d3 = sheet.getRow(2).getCell(3);
assertEquals(result, evaluator.evaluateInCell(d3).getNumericCellValue(), 0);
wb.close();
}
/**
* Evaluation of cell references with column indexes greater than 255. See bugzilla 50096
*/
@Test
public void testEvaluateColumnGreaterThan255() throws IOException {
XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("50096.xlsx");
XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
/**
* The first row simply contains the numbers 1 - 300.
* The second row simply refers to the cell value above in the first row by a simple formula.
*/
for (int i = 245; i < 265; i++) {
XSSFCell cell_noformula = wb.getSheetAt(0).getRow(0).getCell(i);
XSSFCell cell_formula = wb.getSheetAt(0).getRow(1).getCell(i);
CellReference ref_noformula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
CellReference ref_formula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
String fmla = cell_formula.getCellFormula();
// assure that the formula refers to the cell above.
// the check below is 'deep' and involves conversion of the shared formula:
// in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
assertEquals(ref_noformula.formatAsString(), fmla);
CellValue cv_noformula = evaluator.evaluate(cell_noformula);
CellValue cv_formula = evaluator.evaluate(cell_formula);
assertEquals("Wrong evaluation result in " + ref_formula.formatAsString(),
cv_noformula.getNumberValue(), cv_formula.getNumberValue(), 0);
}
wb.close();
}
/**
* Related to bugs #56737 and #56752 - XSSF workbooks which have
* formulas that refer to cells and named ranges in multiple other
* workbooks, both HSSF and XSSF ones
*/
@Test
public void testReferencesToOtherWorkbooks() throws Exception {
XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("ref2-56737.xlsx");
XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
XSSFSheet s = wb.getSheetAt(0);
// References to a .xlsx file
Row rXSLX = s.getRow(2);
Cell cXSLX_cell = rXSLX.getCell(4);
Cell cXSLX_sNR = rXSLX.getCell(6);
Cell cXSLX_gNR = rXSLX.getCell(8);
assertEquals("[1]Uses!$A$1", cXSLX_cell.getCellFormula());
assertEquals("[1]Defines!NR_To_A1", cXSLX_sNR.getCellFormula());
assertEquals("[1]!NR_Global_B2", cXSLX_gNR.getCellFormula());
assertEquals("Hello!", cXSLX_cell.getStringCellValue());
assertEquals("Test A1", cXSLX_sNR.getStringCellValue());
assertEquals(142.0, cXSLX_gNR.getNumericCellValue(), 0);
// References to a .xls file
Row rXSL = s.getRow(4);
Cell cXSL_cell = rXSL.getCell(4);
Cell cXSL_sNR = rXSL.getCell(6);
Cell cXSL_gNR = rXSL.getCell(8);
assertEquals("[2]Uses!$C$1", cXSL_cell.getCellFormula());
assertEquals("[2]Defines!NR_To_A1", cXSL_sNR.getCellFormula());
assertEquals("[2]!NR_Global_B2", cXSL_gNR.getCellFormula());
assertEquals("Hello!", cXSL_cell.getStringCellValue());
assertEquals("Test A1", cXSL_sNR.getStringCellValue());
assertEquals(142.0, cXSL_gNR.getNumericCellValue(), 0);
// Try to evaluate without references, won't work
// (At least, not unit we fix bug #56752 that is)
try {
evaluator.evaluate(cXSL_cell);
fail("Without a fix for #56752, shouldn't be able to evaluate a " +
"reference to a non-provided linked workbook");
} catch(Exception e) {
// expected here
}
// Setup the environment
Map<String,FormulaEvaluator> evaluators = new HashMap<String, FormulaEvaluator>();
evaluators.put("ref2-56737.xlsx", evaluator);
evaluators.put("56737.xlsx",
_testDataProvider.openSampleWorkbook("56737.xlsx").getCreationHelper().createFormulaEvaluator());
evaluators.put("56737.xls",
HSSFTestDataSamples.openSampleWorkbook("56737.xls").getCreationHelper().createFormulaEvaluator());
evaluator.setupReferencedWorkbooks(evaluators);
// Try evaluating all of them, ensure we don't blow up
for(Row r : s) {
for (Cell c : r) {
evaluator.evaluate(c);
}
}
// And evaluate the other way too
evaluator.evaluateAll();
// Static evaluator won't work, as no references passed in
try {
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
fail("Static method lacks references, shouldn't work");
} catch(Exception e) {
// expected here
}
// Evaluate specific cells and check results
assertEquals("\"Hello!\"", evaluator.evaluate(cXSLX_cell).formatAsString());
assertEquals("\"Test A1\"", evaluator.evaluate(cXSLX_sNR).formatAsString());
assertEquals("142.0", evaluator.evaluate(cXSLX_gNR).formatAsString());
assertEquals("\"Hello!\"", evaluator.evaluate(cXSL_cell).formatAsString());
assertEquals("\"Test A1\"", evaluator.evaluate(cXSL_sNR).formatAsString());
assertEquals("142.0", evaluator.evaluate(cXSL_gNR).formatAsString());
// Add another formula referencing these workbooks
Cell cXSL_cell2 = rXSL.createCell(40);
cXSL_cell2.setCellFormula("[56737.xls]Uses!$C$1");
// TODO Shouldn't it become [2] like the others?
assertEquals("[56737.xls]Uses!$C$1", cXSL_cell2.getCellFormula());
assertEquals("\"Hello!\"", evaluator.evaluate(cXSL_cell2).formatAsString());
// Now add a formula that refers to yet another (different) workbook
// Won't work without the workbook being linked
Cell cXSLX_nw_cell = rXSLX.createCell(42);
try {
cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
fail("New workbook not linked, shouldn't be able to add");
} catch (Exception e) {
// expected here
}
// Link and re-try
Workbook alt = new XSSFWorkbook();
try {
alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook");
// TODO Implement the rest of this, see bug #57184
/*
wb.linkExternalWorkbook("alt.xlsx", alt);
cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
// Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
assertEquals("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula());
// Evaluate it, without a link to that workbook
try {
evaluator.evaluate(cXSLX_nw_cell);
fail("No cached value and no link to workbook, shouldn't evaluate");
} catch(Exception e) {}
// Add a link, check it does
evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator());
evaluator.setupReferencedWorkbooks(evaluators);
evaluator.evaluate(cXSLX_nw_cell);
assertEquals("In another workbook", cXSLX_nw_cell.getStringCellValue());
*/
} finally {
alt.close();
}
wb.close();
}
/**
* If a formula references cells or named ranges in another workbook,
* but that isn't available at evaluation time, the cached values
* should be used instead
* TODO Add the support then add a unit test
* See bug #56752
*/
@Test
@Ignore
public void testCachedReferencesToOtherWorkbooks() throws Exception {
// TODO
}
/**
* 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).
* This test, based on common test files for HSSF and XSSF, checks
* that we can correctly evaluate these
*/
@Test
public void testMultiSheetReferencesHSSFandXSSF() throws Exception {
Workbook wb1 = HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls");
Workbook wb2 = XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx");
for (Workbook wb : new Workbook[] {wb1,wb2}) {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
Sheet s1 = wb.getSheetAt(0);
// Simple SUM over numbers
Cell sumF = s1.getRow(2).getCell(0);
assertNotNull(sumF);
assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
assertEquals("Failed for " + wb.getClass(), "66.0", evaluator.evaluate(sumF).formatAsString());
// Various Stats formulas on numbers
Cell avgF = s1.getRow(2).getCell(1);
assertNotNull(avgF);
assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
assertEquals("22.0", evaluator.evaluate(avgF).formatAsString());
Cell minF = s1.getRow(3).getCell(1);
assertNotNull(minF);
assertEquals("MIN(Sheet1:Sheet3!A$1)", minF.getCellFormula());
assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
Cell maxF = s1.getRow(4).getCell(1);
assertNotNull(maxF);
assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
assertEquals("33.0", evaluator.evaluate(maxF).formatAsString());
Cell countF = s1.getRow(5).getCell(1);
assertNotNull(countF);
assertEquals("COUNT(Sheet1:Sheet3!A$1)", countF.getCellFormula());
assertEquals("3.0", evaluator.evaluate(countF).formatAsString());
// Various CountAs on Strings
Cell countA_1F = s1.getRow(2).getCell(2);
assertNotNull(countA_1F);
assertEquals("COUNTA(Sheet1:Sheet3!C1)", countA_1F.getCellFormula());
assertEquals("3.0", evaluator.evaluate(countA_1F).formatAsString());
Cell countA_2F = s1.getRow(2).getCell(3);
assertNotNull(countA_2F);
assertEquals("COUNTA(Sheet1:Sheet3!D1)", countA_2F.getCellFormula());
assertEquals("0.0", evaluator.evaluate(countA_2F).formatAsString());
Cell countA_3F = s1.getRow(2).getCell(4);
assertNotNull(countA_3F);
assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
}
wb2.close();
wb1.close();
}
/**
* A handful of functions (such as SUM, COUNTA, MIN) support
* multi-sheet areas (eg Sheet1:Sheet3!A1:B2 = Cell A1 to Cell B2,
* from Sheets 1 through Sheet 3).
* This test, based on common test files for HSSF and XSSF, checks
* that we can correctly evaluate these
*/
@Test
public void testMultiSheetAreasHSSFandXSSF() throws IOException {
Workbook wb1 = HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls");
Workbook wb2 = XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx");
for (Workbook wb : new Workbook[]{wb1,wb2}) {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
Sheet s1 = wb.getSheetAt(0);
// SUM over a range
Cell sumFA = s1.getRow(2).getCell(7);
assertNotNull(sumFA);
assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
assertEquals("Failed for " + wb.getClass(), "110.0", evaluator.evaluate(sumFA).formatAsString());
// Various Stats formulas on ranges of numbers
Cell avgFA = s1.getRow(2).getCell(8);
assertNotNull(avgFA);
assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
assertEquals("27.5", evaluator.evaluate(avgFA).formatAsString());
Cell minFA = s1.getRow(3).getCell(8);
assertNotNull(minFA);
assertEquals("MIN(Sheet1:Sheet3!A$1:B$2)", minFA.getCellFormula());
assertEquals("11.0", evaluator.evaluate(minFA).formatAsString());
Cell maxFA = s1.getRow(4).getCell(8);
assertNotNull(maxFA);
assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
assertEquals("44.0", evaluator.evaluate(maxFA).formatAsString());
Cell countFA = s1.getRow(5).getCell(8);
assertNotNull(countFA);
assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
assertEquals("4.0", evaluator.evaluate(countFA).formatAsString());
}
wb2.close();
wb1.close();
}
@Test
public void testMultisheetFormulaEval() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet1 = wb.createSheet("Sheet1");
XSSFSheet sheet2 = wb.createSheet("Sheet2");
XSSFSheet sheet3 = wb.createSheet("Sheet3");
// sheet1 A1
XSSFCell cell = sheet1.createRow(0).createCell(0);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(1.0);
// sheet2 A1
cell = sheet2.createRow(0).createCell(0);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(1.0);
// sheet2 B1
cell = sheet2.getRow(0).createCell(1);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(1.0);
// sheet3 A1
cell = sheet3.createRow(0).createCell(0);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.createRow(1).createCell(0);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.createRow(2).createCell(0);
cell.setCellType(CellType.FORMULA);
cell.setCellFormula("SUM(Sheet1:Sheet3!A1:B1)");
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
cell = sheet1.getRow(1).getCell(0);
assertEquals(3.0, cell.getNumericCellValue(), 0);
cell = sheet1.getRow(2).getCell(0);
assertEquals(4.0, cell.getNumericCellValue(), 0);
} finally {
wb.close();
}
}
@Test
public void testBug55843() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(B1=0,\"\",((ROW()-ROW(A$1))*12))");
CellValue evaluate = formulaEvaluator.evaluate(cellA2);
assertEquals("12.0", evaluate.formatAsString());
cellA2.setCellFormula("IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")");
CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
assertEquals(evaluate.toString(), evaluateN.toString());
assertEquals("12.0", evaluateN.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843a() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(B1=0,\"\",((ROW(A$1))))");
CellValue evaluate = formulaEvaluator.evaluate(cellA2);
assertEquals("1.0", evaluate.formatAsString());
cellA2.setCellFormula("IF(NOT(B1=0),((ROW(A$1))),\"\")");
CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
assertEquals(evaluate.toString(), evaluateN.toString());
assertEquals("1.0", evaluateN.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843b() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue evaluate = formulaEvaluator.evaluate(cellA2);
assertEquals("2.0", evaluate.formatAsString());
cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")");
CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
assertEquals(evaluate.toString(), evaluateN.toString());
assertEquals("2.0", evaluateN.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843c() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(NOT(B1=0),((ROW())))");
CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
assertEquals("2.0", evaluateN.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843d() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")");
CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
assertEquals("2.0", evaluateN.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843e() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue evaluate = formulaEvaluator.evaluate(cellA2);
assertEquals("2.0", evaluate.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug55843f() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("test");
XSSFRow row = sheet.createRow(0);
XSSFRow row2 = sheet.createRow(1);
XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
cellB1.setCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
cellA2.setCellFormula("IF(B1=0,\"\",IF(B1=10,3,4))");
CellValue evaluate = formulaEvaluator.evaluate(cellA2);
assertEquals("3.0", evaluate.formatAsString());
} finally {
wb.close();
}
}
@Test
public void testBug56655() throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
setCellFormula(sheet, 0, 0, "#VALUE!");
setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
assertEquals(CellType.ERROR, getCell(sheet, 0,0).getCachedFormulaResultTypeEnum());
assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,0).getErrorCellValue());
assertEquals(CellType.ERROR, getCell(sheet, 0,1).getCachedFormulaResultTypeEnum());
assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,1).getErrorCellValue());
wb.close();
}
@Test
public void testBug56655a() throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
setCellFormula(sheet, 0, 0, "B1*C1");
sheet.getRow(0).createCell(1).setCellValue("A");
setCellFormula(sheet, 1, 0, "B1*C1");
sheet.getRow(1).createCell(1).setCellValue("A");
setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
assertEquals(CellType.ERROR, getCell(sheet, 0, 0).getCachedFormulaResultTypeEnum());
assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
assertEquals(CellType.ERROR, getCell(sheet, 1, 0).getCachedFormulaResultTypeEnum());
assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 1, 0).getErrorCellValue());
assertEquals(CellType.ERROR, getCell(sheet, 0, 3).getCachedFormulaResultTypeEnum());
assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 3).getErrorCellValue());
wb.close();
}
// bug 57721
@Test
public void structuredReferences() throws IOException {
verifyAllFormulasInWorkbookCanBeEvaluated("evaluate_formula_with_structured_table_references.xlsx");
}
// bug 57840
@Ignore("Takes over a minute to evaluate all formulas in this large workbook. Run this test when profiling for formula evaluation speed.")
@Test
public void testLotsOfFormulasWithStructuredReferencesToCalculatedTableColumns() throws IOException {
verifyAllFormulasInWorkbookCanBeEvaluated("StructuredRefs-lots-with-lookups.xlsx");
}
// FIXME: use junit4 parameterization
private static void verifyAllFormulasInWorkbookCanBeEvaluated(String sampleWorkbook) throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(sampleWorkbook);
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
wb.close();
}
/**
* @param row 0-based
* @param column 0-based
*/
private void setCellFormula(Sheet sheet, int row, int column, String formula) {
Row r = sheet.getRow(row);
if (r == null) {
r = sheet.createRow(row);
}
Cell cell = r.getCell(column);
if (cell == null) {
cell = r.createCell(column);
}
cell.setCellType(CellType.FORMULA);
cell.setCellFormula(formula);
}
/**
* @param rowNo 0-based
* @param column 0-based
*/
private Cell getCell(Sheet sheet, int rowNo, int column) {
return sheet.getRow(rowNo).getCell(column);
}
@Test
public void test59736() {
Workbook wb = XSSFTestDataSamples.openSampleWorkbook("59736.xlsx");
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
assertEquals(1, cell.getNumericCellValue(), 0.001);
cell = wb.getSheetAt(0).getRow(1).getCell(0);
CellValue value = evaluator.evaluate(cell);
assertEquals(1, value.getNumberValue(), 0.001);
cell = wb.getSheetAt(0).getRow(2).getCell(0);
value = evaluator.evaluate(cell);
assertEquals(1, value.getNumberValue(), 0.001);
}
@Test
public void evaluateInCellReturnsSameDataType() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
wb.createSheet().createRow(0).createCell(0);
XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
XSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0);
XSSFCell same = evaluator.evaluateInCell(cell);
assertSame(cell, same);
wb.close();
}
}