Skip to content

Commit af53d2b

Browse files
author
Sunil Jain
committed
BAEL-3658: Read cell value rather than the formula that is evaluating it
1 parent c7342ad commit af53d2b

3 files changed

Lines changed: 47 additions & 50 deletions

File tree

apache-poi/src/main/java/com/baeldung/poi/excel/ReadCellValueNotFormulaHelper.java renamed to apache-poi/src/main/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaHelper.java

Lines changed: 8 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
package com.baeldung.poi.excel;
1+
package com.baeldung.poi.excel.read.cellvalueandnotformula;
22

33
import java.io.File;
44
import java.io.FileInputStream;
@@ -13,20 +13,19 @@
1313
import org.apache.poi.ss.util.CellAddress;
1414
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
1515

16-
public class ReadCellValueNotFormulaHelper {
16+
public class CellValueAndNotFormulaHelper {
1717

1818
public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
1919
Object cellValue = new Object();
20-
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
2120

21+
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
2222
Workbook workbook = new XSSFWorkbook(inputStream);
2323

2424
Sheet sheet = workbook.getSheetAt(0);
2525

26-
CellAddress cellReference = new CellAddress(cellLocation);
27-
28-
Row row = sheet.getRow(cellReference.getRow());
29-
Cell cell = row.getCell(cellReference.getColumn());
26+
CellAddress cellAddress = new CellAddress(cellLocation);
27+
Row row = sheet.getRow(cellAddress.getRow());
28+
Cell cell = row.getCell(cellAddress.getColumn());
3029

3130
if (cell.getCellType() == CellType.FORMULA) {
3231
switch (cell.getCachedFormulaResultType()) {
@@ -37,8 +36,7 @@ public Object getCellValueByFetchingLastCachedValue(String fileLocation, String
3736
cellValue = cell.getNumericCellValue();
3837
break;
3938
case STRING:
40-
cellValue = cell.getRichStringCellValue()
41-
.getString();
39+
cellValue = cell.getStringCellValue();
4240
break;
4341
default:
4442
cellValue = null;
@@ -51,16 +49,15 @@ public Object getCellValueByFetchingLastCachedValue(String fileLocation, String
5149

5250
public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
5351
Object cellValue;
54-
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
5552

53+
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
5654
Workbook workbook = new XSSFWorkbook(inputStream);
5755

5856
Sheet sheet = workbook.getSheetAt(0);
5957
FormulaEvaluator evaluator = workbook.getCreationHelper()
6058
.createFormulaEvaluator();
6159

6260
CellAddress cellAddress = new CellAddress(cellLocation);
63-
6461
Row row = sheet.getRow(cellAddress.getRow());
6562
Cell cell = row.getCell(cellAddress.getColumn());
6663

‎apache-poi/src/test/java/com/baeldung/poi/excel/ReadCellValueNotFormulaUnitTest.java‎

Lines changed: 0 additions & 39 deletions
This file was deleted.

‎apache-poi/src/test/java/com/baeldung/poi/excel/read/cellvalueandnotformula/CellValueAndNotFormulaUnitTest.java‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
package com.baeldung.poi.excel.read.cellvalueandnotformula;
2+
3+
import static org.junit.jupiter.api.Assertions.assertEquals;
4+
5+
import java.io.IOException;
6+
import java.net.URISyntaxException;
7+
import java.nio.file.Paths;
8+
9+
import org.junit.Before;
10+
import org.junit.Test;
11+
12+
public class CellValueAndNotFormulaUnitTest {
13+
14+
private CellValueAndNotFormulaHelper readCellValueAndNotFormulaHelper;
15+
private String fileLocation;
16+
private static final String FILE_NAME = "test.xlsx";
17+
18+
@Before
19+
public void setup() throws URISyntaxException {
20+
fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME).toURI()).toString();
21+
readCellValueAndNotFormulaHelper = new CellValueAndNotFormulaHelper();
22+
}
23+
24+
@Test
25+
public void givenExcelCell_whenReadCellValueByLastCachedValue_thenProduceCorrectResult() throws IOException {
26+
final double expectedResult = 7.0;
27+
final Object cellValue = readCellValueAndNotFormulaHelper.getCellValueByFetchingLastCachedValue(fileLocation, "C2");
28+
29+
assertEquals(expectedResult, cellValue);
30+
}
31+
32+
@Test
33+
public void givenExcelCell_whenReadCellValueByEvaluatingFormula_thenProduceCorrectResult() throws IOException {
34+
final double expectedResult = 7.0;
35+
final Object cellValue = readCellValueAndNotFormulaHelper.getCellValueByEvaluatingFormula(fileLocation, "C2");
36+
37+
assertEquals(expectedResult, cellValue);
38+
}
39+
}

0 commit comments

Comments
 (0)