Skip to content

Commit 4e97fb0

Browse files
GangGang
Gang
authored and
Gang
committed
BAEL-3656 Read Numeric Strings in Excel Cells as a String with Apache POI
1 parent e152b6f commit 4e97fb0

3 files changed

Lines changed: 149 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/ExcelCellFormatter.java‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
package com.baeldung.poi.excel;
2+
3+
import org.apache.poi.ss.usermodel.Cell;
4+
import org.apache.poi.ss.usermodel.DataFormatter;
5+
import org.apache.poi.ss.usermodel.FormulaEvaluator;
6+
import org.apache.poi.ss.usermodel.Workbook;
7+
8+
public class ExcelCellFormatter {
9+
10+
public String getCellStringValue(Cell cell) {
11+
DataFormatter formatter = new DataFormatter();
12+
return formatter.formatCellValue(cell);
13+
}
14+
15+
public String getCellStringValueWithFormula(Cell cell, Workbook workbook) {
16+
DataFormatter formatter = new DataFormatter();
17+
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
18+
return formatter.formatCellValue(cell, evaluator);
19+
}
20+
}

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

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,128 @@
1+
package com.baeldung.poi.excel;
2+
3+
import static org.junit.Assert.assertEquals;
4+
5+
import java.io.File;
6+
import java.io.FileOutputStream;
7+
import java.io.IOException;
8+
9+
import org.apache.poi.ss.usermodel.Cell;
10+
import org.apache.poi.ss.usermodel.CellStyle;
11+
import org.apache.poi.ss.usermodel.DataFormat;
12+
import org.apache.poi.ss.usermodel.Row;
13+
import org.apache.poi.ss.usermodel.Sheet;
14+
import org.apache.poi.ss.usermodel.Workbook;
15+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
16+
import org.junit.After;
17+
import org.junit.Before;
18+
import org.junit.Test;
19+
20+
public class ExcelCellFormatterIntegrationTest {
21+
private static int STRING_CELL_INDEX = 0;
22+
private static int BOOLEAN_CELL_INDEX = 1;
23+
private static int RAW_NUMERIC_CELL_INDEX = 2;
24+
private static int FORMATTED_NUMERIC_CELL_INDEX = 3;
25+
private static int FORMULA_CELL_INDEX = 4;
26+
27+
private String fileLocation;
28+
29+
@Before
30+
public void generateExcelFile() throws IOException {
31+
32+
Workbook workbook = new XSSFWorkbook();
33+
34+
Sheet sheet = workbook.createSheet("Test");
35+
Row row = sheet.createRow(0);
36+
Cell cell = row.createCell(STRING_CELL_INDEX);
37+
cell.setCellValue("String Test"); // STRING cell
38+
39+
cell = row.createCell(BOOLEAN_CELL_INDEX);
40+
cell.setCellValue(true); // BOOLEAN cell
41+
42+
cell = row.createCell(RAW_NUMERIC_CELL_INDEX);
43+
cell.setCellValue(1234.5678); // NUMERIC cell
44+
45+
cell = row.createCell(FORMATTED_NUMERIC_CELL_INDEX);
46+
cell.setCellValue(1234.5678);
47+
CellStyle curStyle = workbook.createCellStyle();
48+
DataFormat df = workbook.createDataFormat();
49+
curStyle.setDataFormat(df.getFormat("$#,##0.00"));
50+
cell.setCellStyle(curStyle); // NUMERIC cell with format rule
51+
52+
cell = row.createCell(FORMULA_CELL_INDEX);
53+
cell.setCellFormula("SUM(C1:D1)"); // FORMULA cell
54+
55+
File tempFile = File.createTempFile("ExcelCellFormatterIntegrationTest", ".xlsx");
56+
57+
fileLocation = tempFile.getAbsolutePath();
58+
59+
FileOutputStream outputStream = new FileOutputStream(fileLocation);
60+
workbook.write(outputStream);
61+
workbook.close();
62+
outputStream.close();
63+
}
64+
65+
@Test
66+
public void gvieStringCell_whenGetCellStringValue_thenReturnStringValue() throws IOException {
67+
Workbook workbook = new XSSFWorkbook(fileLocation);
68+
Sheet sheet = workbook.getSheetAt(0);
69+
Row row = sheet.getRow(0);
70+
71+
ExcelCellFormatter formatter = new ExcelCellFormatter();
72+
assertEquals("String Test", formatter.getCellStringValue(row.getCell(STRING_CELL_INDEX)));
73+
workbook.close();
74+
}
75+
76+
@Test
77+
public void gvieBooleanCell_whenGetCellStringValue_thenReturnBooleanStringValue() throws IOException {
78+
Workbook workbook = new XSSFWorkbook(fileLocation);
79+
Sheet sheet = workbook.getSheetAt(0);
80+
Row row = sheet.getRow(0);
81+
82+
ExcelCellFormatter formatter = new ExcelCellFormatter();
83+
assertEquals("TRUE", formatter.getCellStringValue(row.getCell(BOOLEAN_CELL_INDEX)));
84+
workbook.close();
85+
}
86+
87+
@Test
88+
public void gvieNumericCell_whenGetCellStringValue_thenReturnNumericStringValue() throws IOException {
89+
Workbook workbook = new XSSFWorkbook(fileLocation);
90+
Sheet sheet = workbook.getSheetAt(0);
91+
Row row = sheet.getRow(0);
92+
93+
ExcelCellFormatter formatter = new ExcelCellFormatter();
94+
assertEquals("1234.5678", formatter.getCellStringValue(row.getCell(RAW_NUMERIC_CELL_INDEX)));
95+
assertEquals("$1,234.57", formatter.getCellStringValue(row.getCell(FORMATTED_NUMERIC_CELL_INDEX)));
96+
workbook.close();
97+
}
98+
99+
@Test
100+
public void gvieFormualCell_whenGetCellStringValue_thenReturnOriginalFormulaString() throws IOException {
101+
Workbook workbook = new XSSFWorkbook(fileLocation);
102+
Sheet sheet = workbook.getSheetAt(0);
103+
Row row = sheet.getRow(0);
104+
105+
ExcelCellFormatter formatter = new ExcelCellFormatter();
106+
assertEquals("SUM(C1:D1)", formatter.getCellStringValue(row.getCell(FORMULA_CELL_INDEX)));
107+
workbook.close();
108+
}
109+
110+
@Test
111+
public void gvieFormualCell_whenGetCellStringValueForFormula_thenReturnOriginalFormulatring() throws IOException {
112+
Workbook workbook = new XSSFWorkbook(fileLocation);
113+
Sheet sheet = workbook.getSheetAt(0);
114+
Row row = sheet.getRow(0);
115+
116+
ExcelCellFormatter formatter = new ExcelCellFormatter();
117+
assertEquals("2469.1356", formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook));
118+
workbook.close();
119+
}
120+
121+
@After
122+
public void cleanup() {
123+
File testFile = new File(fileLocation);
124+
if (testFile.exists()) {
125+
testFile.deleteOnExit();
126+
}
127+
}
128+
}

0 commit comments

Comments
 (0)