Skip to content

Commit fd431e4

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

4 files changed

Lines changed: 124 additions & 1 deletion

File tree

‎apache-poi/pom.xml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@
3232
</dependencies>
3333

3434
<properties>
35-
<poi.version>3.15</poi.version>
35+
<poi.version>4.1.1</poi.version>
3636
<jexcel.version>1.0.6</jexcel.version>
3737
</properties>
3838

‎apache-poi/src/main/java/com/baeldung/poi/excel/ReadCellValueNotFormulaHelper.java‎

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
package com.baeldung.poi.excel;
2+
3+
import java.io.File;
4+
import java.io.FileInputStream;
5+
import java.io.IOException;
6+
7+
import org.apache.poi.ss.usermodel.Cell;
8+
import org.apache.poi.ss.usermodel.CellType;
9+
import org.apache.poi.ss.usermodel.FormulaEvaluator;
10+
import org.apache.poi.ss.usermodel.Row;
11+
import org.apache.poi.ss.usermodel.Sheet;
12+
import org.apache.poi.ss.usermodel.Workbook;
13+
import org.apache.poi.ss.util.CellAddress;
14+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
15+
16+
public class ReadCellValueNotFormulaHelper {
17+
18+
public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
19+
Object cellValue = new Object();
20+
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
21+
22+
Workbook workbook = new XSSFWorkbook(inputStream);
23+
24+
Sheet sheet = workbook.getSheetAt(0);
25+
26+
CellAddress cellReference = new CellAddress(cellLocation);
27+
28+
Row row = sheet.getRow(cellReference.getRow());
29+
Cell cell = row.getCell(cellReference.getColumn());
30+
31+
if (cell.getCellType() == CellType.FORMULA) {
32+
switch (cell.getCachedFormulaResultType()) {
33+
case BOOLEAN:
34+
cellValue = cell.getBooleanCellValue();
35+
break;
36+
case NUMERIC:
37+
cellValue = cell.getNumericCellValue();
38+
break;
39+
case STRING:
40+
cellValue = cell.getRichStringCellValue()
41+
.getString();
42+
break;
43+
default:
44+
cellValue = null;
45+
}
46+
}
47+
48+
workbook.close();
49+
return cellValue;
50+
}
51+
52+
public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
53+
Object cellValue;
54+
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
55+
56+
Workbook workbook = new XSSFWorkbook(inputStream);
57+
58+
Sheet sheet = workbook.getSheetAt(0);
59+
FormulaEvaluator evaluator = workbook.getCreationHelper()
60+
.createFormulaEvaluator();
61+
62+
CellAddress cellAddress = new CellAddress(cellLocation);
63+
64+
Row row = sheet.getRow(cellAddress.getRow());
65+
Cell cell = row.getCell(cellAddress.getColumn());
66+
67+
switch (evaluator.evaluateFormulaCell(cell)) {
68+
case BOOLEAN:
69+
cellValue = cell.getBooleanCellValue();
70+
break;
71+
case NUMERIC:
72+
cellValue = cell.getNumericCellValue();
73+
break;
74+
case STRING:
75+
cellValue = cell.getStringCellValue();
76+
break;
77+
default:
78+
cellValue = null;
79+
}
80+
81+
workbook.close();
82+
return cellValue;
83+
}
84+
}

‎apache-poi/src/main/resources/test.xlsx‎

4.74 KB
Binary file not shown.

‎apache-poi/src/test/java/com/baeldung/poi/excel/ReadCellValueNotFormulaUnitTest.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;
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 ReadCellValueNotFormulaUnitTest {
13+
14+
private ReadCellValueNotFormulaHelper readCellValueNotFormulaHelper;
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+
readCellValueNotFormulaHelper = new ReadCellValueNotFormulaHelper();
22+
}
23+
24+
@Test
25+
public void testCachedValueMethod() throws IOException {
26+
final double expectedResult = 7.0;
27+
final Object cellValue = readCellValueNotFormulaHelper.getCellValueByFetchingLastCachedValue(fileLocation, "C2");
28+
29+
assertEquals(expectedResult, cellValue);
30+
}
31+
32+
@Test
33+
public void testFormulaEvaluationMethod() throws IOException {
34+
final double expectedResult = 7.0;
35+
final Object cellValue = readCellValueNotFormulaHelper.getCellValueByEvaluatingFormula(fileLocation, "C2");
36+
37+
assertEquals(expectedResult, cellValue);
38+
}
39+
}

0 commit comments

Comments
 (0)