| /* ==================================================================== |
| 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.hssf.usermodel; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.fail; |
| |
| import java.io.IOException; |
| |
| import org.apache.poi.hssf.HSSFITestDataProvider; |
| import org.apache.poi.hssf.HSSFTestDataSamples; |
| import org.apache.poi.hssf.record.NameRecord; |
| import org.apache.poi.ss.formula.EvaluationCell; |
| import org.apache.poi.ss.formula.EvaluationListener; |
| import org.apache.poi.ss.formula.WorkbookEvaluator; |
| import org.apache.poi.ss.formula.WorkbookEvaluatorTestHelper; |
| import org.apache.poi.ss.formula.eval.NumberEval; |
| import org.apache.poi.ss.formula.eval.ValueEval; |
| 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.junit.Test; |
| |
| public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator { |
| |
| public TestHSSFFormulaEvaluator() { |
| super(HSSFITestDataProvider.instance); |
| } |
| |
| /** |
| * Test that the HSSFFormulaEvaluator can evaluate simple named ranges |
| * (single cells and rectangular areas) |
| */ |
| @Test |
| public void testEvaluateSimple() throws IOException { |
| HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls"); |
| HSSFSheet sheet = wb.getSheetAt(0); |
| HSSFCell cell = sheet.getRow(8).getCell(0); |
| HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); |
| CellValue cv = fe.evaluate(cell); |
| assertEquals(CellType.NUMERIC, cv.getCellTypeEnum()); |
| assertEquals(3.72, cv.getNumberValue(), 0.0); |
| wb.close(); |
| } |
| |
| /** |
| * Test for bug due to attempt to convert a cached formula error result to a boolean |
| */ |
| @Test |
| @Override |
| public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException { |
| |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("Sheet1"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cellA1 = row.createCell(0); |
| HSSFCell cellB1 = row.createCell(1); |
| cellB1.setCellFormula("A1+1"); |
| HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); |
| |
| cellA1.setCellErrorValue(FormulaError.NAME.getCode()); |
| fe.evaluateFormulaCellEnum(cellB1); |
| |
| cellA1.setCellValue(2.5); |
| fe.notifyUpdateCell(cellA1); |
| try { |
| fe.evaluateInCell(cellB1); |
| } catch (IllegalStateException e) { |
| if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) { |
| fail("Identified bug 46479a"); |
| } |
| } |
| assertEquals(3.5, cellB1.getNumericCellValue(), 0.0); |
| |
| wb.close(); |
| } |
| |
| /** |
| * When evaluating defined names, POI has to decide whether it is capable. Currently |
| * (May2009) POI only supports simple cell and area refs.<br/> |
| * The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex' |
| * which contained a simple area ref. It is not clear what the 'complex' flag is used |
| * for but POI should look elsewhere to decide whether it can evaluate the name. |
| */ |
| @Test |
| public void testDefinedNameWithComplexFlag_bug47048() throws IOException { |
| // Mock up a spreadsheet to match the critical details of the sample |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("Input"); |
| HSSFName definedName = wb.createName(); |
| definedName.setNameName("Is_Multicar_Vehicle"); |
| definedName.setRefersToFormula("Input!$B$17:$G$17"); |
| |
| // Set up some data and the formula |
| HSSFRow row17 = sheet.createRow(16); |
| row17.createCell(0).setCellValue(25.0); |
| row17.createCell(1).setCellValue(1.33); |
| row17.createCell(2).setCellValue(4.0); |
| |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cellA1 = row.createCell(0); |
| cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)"); |
| |
| // Set the complex flag - POI doesn't usually manipulate this flag |
| NameRecord nameRec = TestHSSFName.getNameRecord(definedName); |
| nameRec.setOptionFlag((short)0x10); // 0x10 -> complex |
| |
| HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb); |
| CellValue value; |
| try { |
| value = hsf.evaluate(cellA1); |
| |
| assertEquals(CellType.NUMERIC, value.getCellTypeEnum()); |
| assertEquals(5.33, value.getNumberValue(), 0.0); |
| |
| } catch (RuntimeException e) { |
| if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) { |
| fail("Identified bug 47048a"); |
| } |
| throw e; |
| } finally { |
| wb.close(); |
| } |
| } |
| |
| private static final class EvalCountListener extends EvaluationListener { |
| private int _evalCount; |
| public EvalCountListener() { |
| _evalCount = 0; |
| } |
| @Override |
| public void onStartEvaluate(EvaluationCell cell, ICacheEntry entry) { |
| _evalCount++; |
| } |
| public int getEvalCount() { |
| return _evalCount; |
| } |
| } |
| |
| /** |
| * The HSSFFormula evaluator performance benefits greatly from caching of intermediate cell values |
| */ |
| @Test |
| public void testShortCircuitIfEvaluation() throws IOException { |
| |
| // Set up a simple IF() formula that has measurable evaluation cost for its operands. |
| HSSFWorkbook wb = new HSSFWorkbook(); |
| HSSFSheet sheet = wb.createSheet("Sheet1"); |
| HSSFRow row = sheet.createRow(0); |
| HSSFCell cellA1 = row.createCell(0); |
| cellA1.setCellFormula("if(B1,C1,D1+E1+F1)"); |
| // populate cells B1..F1 with simple formulas instead of plain values so we can use |
| // EvaluationListener to check which parts of the first formula get evaluated |
| for (int i=1; i<6; i++) { |
| // formulas are just literal constants "1".."5" |
| row.createCell(i).setCellFormula(String.valueOf(i)); |
| } |
| |
| EvalCountListener evalListener = new EvalCountListener(); |
| WorkbookEvaluator evaluator = WorkbookEvaluatorTestHelper.createEvaluator(wb, evalListener); |
| ValueEval ve = evaluator.evaluate(HSSFEvaluationTestHelper.wrapCell(cellA1)); |
| int evalCount = evalListener.getEvalCount(); |
| if (evalCount == 6) { |
| // Without short-circuit-if evaluation, evaluating cell 'A1' takes 3 extra evaluations (for D1,E1,F1) |
| fail("Identifed bug 48195 - Formula evaluator should short-circuit IF() calculations."); |
| } |
| assertEquals(3, evalCount); |
| assertEquals(2.0, ((NumberEval)ve).getNumberValue(), 0D); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Ensures that we can handle NameXPtgs in the formulas |
| * we parse. |
| */ |
| @Test |
| public void testXRefs() throws IOException { |
| HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls"); |
| HSSFWorkbook wb2 = HSSFTestDataSamples.openSampleWorkbook("XRefCalcData.xls"); |
| Cell cell; |
| |
| // VLookup on a name in another file |
| cell = wb1.getSheetAt(0).getRow(1).getCell(2); |
| assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); |
| assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultTypeEnum()); |
| assertEquals(12.30, cell.getNumericCellValue(), 0.0001); |
| // WARNING - this is wrong! |
| // The file name should be showing, but bug #45970 is fixed |
| // we seem to loose it |
| assertEquals("VLOOKUP(PART,COSTS,2,FALSE)", cell.getCellFormula()); |
| |
| |
| // Simple reference to a name in another file |
| cell = wb1.getSheetAt(0).getRow(1).getCell(4); |
| assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); |
| assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultTypeEnum()); |
| assertEquals(36.90, cell.getNumericCellValue(), 0.0001); |
| // TODO Correct this! |
| // The file name should be shown too, see bug #56742 |
| assertEquals("Cost*Markup_Cost", cell.getCellFormula()); |
| |
| |
| // Evaluate the cells |
| HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1); |
| HSSFFormulaEvaluator.setupEnvironment( |
| new String[] { "XRefCalc.xls", "XRefCalcData.xls" }, |
| new HSSFFormulaEvaluator[] { |
| eval, |
| new HSSFFormulaEvaluator(wb2) |
| } |
| ); |
| eval.evaluateFormulaCellEnum( |
| wb1.getSheetAt(0).getRow(1).getCell(2) |
| ); |
| eval.evaluateFormulaCellEnum( |
| wb1.getSheetAt(0).getRow(1).getCell(4) |
| ); |
| |
| |
| // Re-check VLOOKUP one |
| cell = wb1.getSheetAt(0).getRow(1).getCell(2); |
| assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); |
| assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultTypeEnum()); |
| assertEquals(12.30, cell.getNumericCellValue(), 0.0001); |
| |
| // Re-check ref one |
| cell = wb1.getSheetAt(0).getRow(1).getCell(4); |
| assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); |
| assertEquals(CellType.NUMERIC, cell.getCachedFormulaResultTypeEnum()); |
| assertEquals(36.90, cell.getNumericCellValue(), 0.0001); |
| |
| |
| // Add a formula that refers to one of the existing external workbooks |
| cell = wb1.getSheetAt(0).getRow(1).createCell(40); |
| cell.setCellFormula("Cost*[XRefCalcData.xls]MarkupSheet!$B$1"); |
| |
| // Check is was stored correctly |
| assertEquals("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.getCellFormula()); |
| |
| // Check it evaluates correctly |
| eval.evaluateFormulaCellEnum(cell); |
| assertEquals(24.60*1.8, cell.getNumericCellValue(), 0); |
| |
| |
| // Try to add a formula for a new external workbook, won't be allowed to start |
| try { |
| cell = wb1.getSheetAt(0).getRow(1).createCell(42); |
| cell.setCellFormula("[alt.xls]Sheet0!$A$1"); |
| fail("New workbook not linked, shouldn't be able to add"); |
| } catch(Exception e) {} |
| |
| // Link our new workbook |
| HSSFWorkbook wb3 = new HSSFWorkbook(); |
| wb3.createSheet().createRow(0).createCell(0).setCellValue("In another workbook"); |
| wb1.linkExternalWorkbook("alt.xls", wb3); |
| |
| // Now add a formula that refers to our new workbook |
| cell.setCellFormula("[alt.xls]Sheet0!$A$1"); |
| assertEquals("[alt.xls]Sheet0!$A$1", cell.getCellFormula()); |
| |
| // Evaluate it, without a link to that workbook |
| try { |
| eval.evaluate(cell); |
| fail("No cached value and no link to workbook, shouldn't evaluate"); |
| } catch(Exception e) {} |
| |
| // Add a link, check it does |
| HSSFFormulaEvaluator.setupEnvironment( |
| new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, |
| new HSSFFormulaEvaluator[] { |
| eval, |
| new HSSFFormulaEvaluator(wb2), |
| new HSSFFormulaEvaluator(wb3) |
| } |
| ); |
| eval.evaluateFormulaCellEnum(cell); |
| assertEquals("In another workbook", cell.getStringCellValue()); |
| |
| |
| // Save and re-load |
| HSSFWorkbook wb4 = HSSFTestDataSamples.writeOutAndReadBack(wb1); |
| eval = new HSSFFormulaEvaluator(wb4); |
| HSSFFormulaEvaluator.setupEnvironment( |
| new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, |
| new HSSFFormulaEvaluator[] { |
| eval, |
| new HSSFFormulaEvaluator(wb2), |
| new HSSFFormulaEvaluator(wb3) |
| } |
| ); |
| |
| // Check the one referring to the previously existing workbook behaves |
| cell = wb4.getSheetAt(0).getRow(1).getCell(40); |
| assertEquals("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.getCellFormula()); |
| eval.evaluateFormulaCellEnum(cell); |
| assertEquals(24.60*1.8, cell.getNumericCellValue(), 0); |
| |
| // Now check the newly added reference |
| cell = wb4.getSheetAt(0).getRow(1).getCell(42); |
| assertEquals("[alt.xls]Sheet0!$A$1", cell.getCellFormula()); |
| eval.evaluateFormulaCellEnum(cell); |
| assertEquals("In another workbook", cell.getStringCellValue()); |
| |
| wb4.close(); |
| wb3.close(); |
| wb2.close(); |
| wb1.close(); |
| } |
| |
| @Test |
| public void testSharedFormulas() throws IOException { |
| baseTestSharedFormulas("shared_formulas.xls"); |
| } |
| |
| } |