| /* ==================================================================== |
| 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.usermodel; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertNull; |
| import static org.junit.Assert.assertSame; |
| import static org.junit.Assert.fail; |
| |
| import java.io.IOException; |
| |
| import org.apache.poi.ss.ITestDataProvider; |
| import org.junit.Test; |
| |
| /** |
| * Common superclass for testing implementation of {@link FormulaEvaluator} |
| */ |
| public abstract class BaseTestFormulaEvaluator { |
| |
| protected final ITestDataProvider _testDataProvider; |
| |
| /** |
| * @param testDataProvider an object that provides test data in / specific way |
| */ |
| protected BaseTestFormulaEvaluator(ITestDataProvider testDataProvider) { |
| _testDataProvider = testDataProvider; |
| } |
| |
| @Test |
| public void testSimpleArithmetic() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| |
| Cell c1 = r.createCell(0); |
| c1.setCellFormula("1+5"); |
| assertEquals(0.0, c1.getNumericCellValue(), 0.0); |
| |
| Cell c2 = r.createCell(1); |
| c2.setCellFormula("10/2"); |
| assertEquals(0.0, c2.getNumericCellValue(), 0.0); |
| |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| fe.evaluateFormulaCellEnum(c1); |
| fe.evaluateFormulaCellEnum(c2); |
| |
| assertEquals(6.0, c1.getNumericCellValue(), 0.0001); |
| assertEquals(5.0, c2.getNumericCellValue(), 0.0001); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testSumCount() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet s = wb.createSheet(); |
| Row r = s.createRow(0); |
| r.createCell(0).setCellValue(2.5); |
| r.createCell(1).setCellValue(1.1); |
| r.createCell(2).setCellValue(3.2); |
| r.createCell(4).setCellValue(10.7); |
| |
| r = s.createRow(1); |
| |
| Cell c1 = r.createCell(0); |
| c1.setCellFormula("SUM(A1:B1)"); |
| assertEquals(0.0, c1.getNumericCellValue(), 0.0); |
| |
| Cell c2 = r.createCell(1); |
| c2.setCellFormula("SUM(A1:E1)"); |
| assertEquals(0.0, c2.getNumericCellValue(), 0.0); |
| |
| Cell c3 = r.createCell(2); |
| c3.setCellFormula("COUNT(A1:A1)"); |
| assertEquals(0.0, c3.getNumericCellValue(), 0.0); |
| |
| Cell c4 = r.createCell(3); |
| c4.setCellFormula("COUNTA(A1:E1)"); |
| assertEquals(0.0, c4.getNumericCellValue(), 0.0); |
| |
| |
| // Evaluate and test |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| fe.evaluateFormulaCellEnum(c1); |
| fe.evaluateFormulaCellEnum(c2); |
| fe.evaluateFormulaCellEnum(c3); |
| fe.evaluateFormulaCellEnum(c4); |
| |
| assertEquals(3.6, c1.getNumericCellValue(), 0.0001); |
| assertEquals(17.5, c2.getNumericCellValue(), 0.0001); |
| assertEquals(1, c3.getNumericCellValue(), 0.0001); |
| assertEquals(4, c4.getNumericCellValue(), 0.0001); |
| |
| wb.close(); |
| } |
| |
| public void baseTestSharedFormulas(String sampleFile) throws IOException { |
| Workbook wb = _testDataProvider.openSampleWorkbook(sampleFile); |
| |
| Sheet sheet = wb.getSheetAt(0); |
| |
| FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); |
| Cell cell; |
| |
| cell = sheet.getRow(1).getCell(0); |
| assertEquals("B2", cell.getCellFormula()); |
| assertEquals("ProductionOrderConfirmation", evaluator.evaluate(cell).getStringValue()); |
| |
| cell = sheet.getRow(2).getCell(0); |
| assertEquals("B3", cell.getCellFormula()); |
| assertEquals("RequiredAcceptanceDate", evaluator.evaluate(cell).getStringValue()); |
| |
| cell = sheet.getRow(3).getCell(0); |
| assertEquals("B4", cell.getCellFormula()); |
| assertEquals("Header", evaluator.evaluate(cell).getStringValue()); |
| |
| cell = sheet.getRow(4).getCell(0); |
| assertEquals("B5", cell.getCellFormula()); |
| assertEquals("UniqueDocumentNumberID", evaluator.evaluate(cell).getStringValue()); |
| |
| wb.close(); |
| } |
| |
| /** |
| * Test creation / evaluation of formulas with sheet-level names |
| */ |
| @Test |
| public void testSheetLevelFormulas() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| |
| Row row; |
| Sheet sh1 = wb.createSheet("Sheet1"); |
| Name nm1 = wb.createName(); |
| nm1.setNameName("sales_1"); |
| nm1.setSheetIndex(0); |
| nm1.setRefersToFormula("Sheet1!$A$1"); |
| row = sh1.createRow(0); |
| row.createCell(0).setCellValue(3); |
| row.createCell(1).setCellFormula("sales_1"); |
| row.createCell(2).setCellFormula("sales_1*2"); |
| |
| Sheet sh2 = wb.createSheet("Sheet2"); |
| Name nm2 = wb.createName(); |
| nm2.setNameName("sales_1"); |
| nm2.setSheetIndex(1); |
| nm2.setRefersToFormula("Sheet2!$A$1"); |
| |
| row = sh2.createRow(0); |
| row.createCell(0).setCellValue(5); |
| row.createCell(1).setCellFormula("sales_1"); |
| row.createCell(2).setCellFormula("sales_1*3"); |
| |
| FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); |
| assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); |
| assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); |
| |
| assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); |
| assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testFullColumnRefs() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sheet = wb.createSheet("Sheet1"); |
| Row row = sheet.createRow(0); |
| Cell cell0 = row.createCell(0); |
| cell0.setCellFormula("sum(D:D)"); |
| Cell cell1 = row.createCell(1); |
| cell1.setCellFormula("sum(D:E)"); |
| |
| // some values in column D |
| setValue(sheet, 1, 3, 5.0); |
| setValue(sheet, 2, 3, 6.0); |
| setValue(sheet, 5, 3, 7.0); |
| setValue(sheet, 50, 3, 8.0); |
| |
| // some values in column E |
| setValue(sheet, 1, 4, 9.0); |
| setValue(sheet, 2, 4, 10.0); |
| setValue(sheet, 30000, 4, 11.0); |
| |
| // some other values |
| setValue(sheet, 1, 2, 100.0); |
| setValue(sheet, 2, 5, 100.0); |
| setValue(sheet, 3, 6, 100.0); |
| |
| |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0); |
| assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void testRepeatedEvaluation() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| Sheet sheet = wb.createSheet("Sheet1"); |
| Row r = sheet.createRow(0); |
| Cell c = r.createCell(0, CellType.FORMULA); |
| |
| // Create a value and check it |
| c.setCellFormula("Date(2011,10,6)"); |
| CellValue cellValue = fe.evaluate(c); |
| assertEquals(40822.0, cellValue.getNumberValue(), 0.0); |
| cellValue = fe.evaluate(c); |
| assertEquals(40822.0, cellValue.getNumberValue(), 0.0); |
| |
| // Change it |
| c.setCellFormula("Date(2011,10,4)"); |
| |
| // Evaluate it, no change as the formula evaluator |
| // won't know to clear the cache |
| cellValue = fe.evaluate(c); |
| assertEquals(40822.0, cellValue.getNumberValue(), 0.0); |
| |
| // Manually flush for this cell, and check |
| fe.notifySetFormula(c); |
| cellValue = fe.evaluate(c); |
| assertEquals(40820.0, cellValue.getNumberValue(), 0.0); |
| |
| // Change again, without notifying |
| c.setCellFormula("Date(2010,10,4)"); |
| cellValue = fe.evaluate(c); |
| assertEquals(40820.0, cellValue.getNumberValue(), 0.0); |
| |
| // Now manually clear all, will see the new value |
| fe.clearAllCachedResultValues(); |
| cellValue = fe.evaluate(c); |
| assertEquals(40455.0, cellValue.getNumberValue(), 0.0); |
| |
| wb.close(); |
| } |
| |
| private static void setValue(Sheet sheet, int rowIndex, int colIndex, double value) { |
| Row row = sheet.getRow(rowIndex); |
| if (row == null) { |
| row = sheet.createRow(rowIndex); |
| } |
| row.createCell(colIndex).setCellValue(value); |
| } |
| |
| /** |
| * {@link FormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same whether the cell |
| * is <code>null</code> or blank. |
| */ |
| @Test |
| public void testEvaluateBlank() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| assertNull(fe.evaluate(null)); |
| Sheet sheet = wb.createSheet("Sheet1"); |
| Cell cell = sheet.createRow(0).createCell(0); |
| assertNull(fe.evaluate(cell)); |
| wb.close(); |
| } |
| |
| /** |
| * Test for bug due to attempt to convert a cached formula error result to a boolean |
| */ |
| @Test |
| public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException { |
| |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sheet = wb.createSheet("Sheet1"); |
| Row row = sheet.createRow(0); |
| Cell cellA1 = row.createCell(0); |
| Cell cellB1 = row.createCell(1); |
| cellB1.setCellFormula("A1+1"); |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| 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(); |
| } |
| |
| @Test |
| public void testRounding_bug51339() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| Sheet sheet = wb.createSheet("Sheet1"); |
| Row row = sheet.createRow(0); |
| Cell cellA1 = row.createCell(0); |
| cellA1.setCellValue(2162.615d); |
| Cell cellB1 = row.createCell(1); |
| cellB1.setCellFormula("round(a1,2)"); |
| Cell cellC1 = row.createCell(2); |
| cellC1.setCellFormula("roundup(a1,2)"); |
| Cell cellD1 = row.createCell(3); |
| cellD1.setCellFormula("rounddown(a1,2)"); |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| assertEquals(2162.62, fe.evaluateInCell(cellB1).getNumericCellValue(), 0.0); |
| assertEquals(2162.62, fe.evaluateInCell(cellC1).getNumericCellValue(), 0.0); |
| assertEquals(2162.61, fe.evaluateInCell(cellD1).getNumericCellValue(), 0.0); |
| |
| wb.close(); |
| } |
| |
| @Test |
| public void evaluateInCellReturnsSameCell() throws IOException { |
| Workbook wb = _testDataProvider.createWorkbook(); |
| wb.createSheet().createRow(0).createCell(0); |
| FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); |
| Cell cell = wb.getSheetAt(0).getRow(0).getCell(0); |
| Cell same = evaluator.evaluateInCell(cell); |
| assertSame(cell, same); |
| wb.close(); |
| } |
| } |