Merge pull request eugenp#8414 from SunilJain95/master

eric-martin · web-flow · commit dcca5d8aa315 · 2020-01-04T09:32:32.000-06:00
BAEL-3658: Read actual cell value rather than the formula that is evaluating it
diff --git a/apache-poi/pom.xml b/apache-poi/pom.xml
@@ -32,7 +32,7 @@
     </dependencies>
 
     <properties>
-        <poi.version>3.15</poi.version>
+        <poi.version>4.1.1</poi.version>
         <jexcel.version>1.0.6</jexcel.version>
     </properties>
 
diff --git a/apache-poi/src/main/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaHelper.java b/apache-poi/src/main/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaHelper.java
@@ -0,0 +1,83 @@
+package com.baeldung.poi.excel.read.cellvalueandnotformula;
+
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.IOException;
+
+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.CellAddress;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+public class CellValueAndNotFormulaHelper {
+
+    public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
+        Object cellValue = new Object();
+
+        FileInputStream inputStream = new FileInputStream(new File(fileLocation));
+        Workbook workbook = new XSSFWorkbook(inputStream);
+
+        Sheet sheet = workbook.getSheetAt(0);
+
+        CellAddress cellAddress = new CellAddress(cellLocation);
+        Row row = sheet.getRow(cellAddress.getRow());
+        Cell cell = row.getCell(cellAddress.getColumn());
+
+        if (cell.getCellType() == CellType.FORMULA) {
+            switch (cell.getCachedFormulaResultType()) {
+                case BOOLEAN:
+                    cellValue = cell.getBooleanCellValue();
+                    break;
+                case NUMERIC:
+                    cellValue = cell.getNumericCellValue();
+                    break;
+                case STRING:
+                    cellValue = cell.getStringCellValue();
+                    break;
+                default:
+                    cellValue = null;
+            }
+        }
+
+        workbook.close();
+        return cellValue;
+    }
+
+    public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
+        Object cellValue = new Object();
+
+        FileInputStream inputStream = new FileInputStream(new File(fileLocation));
+        Workbook workbook = new XSSFWorkbook(inputStream);
+
+        Sheet sheet = workbook.getSheetAt(0);
+        FormulaEvaluator evaluator = workbook.getCreationHelper()
+            .createFormulaEvaluator();
+
+        CellAddress cellAddress = new CellAddress(cellLocation);
+        Row row = sheet.getRow(cellAddress.getRow());
+        Cell cell = row.getCell(cellAddress.getColumn());
+
+        if (cell.getCellType() == CellType.FORMULA) {
+            switch (evaluator.evaluateFormulaCell(cell)) {
+                case BOOLEAN:
+                    cellValue = cell.getBooleanCellValue();
+                    break;
+                case NUMERIC:
+                    cellValue = cell.getNumericCellValue();
+                    break;
+                case STRING:
+                    cellValue = cell.getStringCellValue();
+                    break;
+                default:
+                    cellValue = null;
+            }
+        }
+
+        workbook.close();
+        return cellValue;
+    }
+}
diff --git a/apache-poi/src/main/resources/test.xlsx b/apache-poi/src/main/resources/test.xlsx
diff --git a/apache-poi/src/test/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaUnitTest.java b/apache-poi/src/test/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaUnitTest.java
@@ -0,0 +1,39 @@
+package com.baeldung.poi.excel.read.cellvalueandnotformula;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.io.IOException;
+import java.net.URISyntaxException;
+import java.nio.file.Paths;
+
+import org.junit.Before;
+import org.junit.Test;
+
+public class CellValueAndNotFormulaUnitTest {
+
+    private CellValueAndNotFormulaHelper readCellValueAndNotFormulaHelper;
+    private String fileLocation;
+    private static final String FILE_NAME = "test.xlsx";
+
+    @Before
+    public void setup() throws URISyntaxException {
+        fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME).toURI()).toString();
+        readCellValueAndNotFormulaHelper = new CellValueAndNotFormulaHelper();
+    }
+
+    @Test
+    public void givenExcelCell_whenReadCellValueByLastCachedValue_thenProduceCorrectResult() throws IOException {
+        final double expectedResult = 7.0;
+        final Object cellValue = readCellValueAndNotFormulaHelper.getCellValueByFetchingLastCachedValue(fileLocation, "C2");
+
+        assertEquals(expectedResult, cellValue);
+    }
+
+    @Test
+    public void givenExcelCell_whenReadCellValueByEvaluatingFormula_thenProduceCorrectResult() throws IOException {
+        final double expectedResult = 7.0;
+        final Object cellValue = readCellValueAndNotFormulaHelper.getCellValueByEvaluatingFormula(fileLocation, "C2");
+
+        assertEquals(expectedResult, cellValue);
+    }
+}
