Skip to content

Commit 1f08721

Browse files
amitiw4uamitiw4u
authored
Bael 5200 numeric excel (eugenp#11594)
* Changes for Numeric Format * Update NumberCellValueUnitTest.java * Corrected the workbook format * Unwanted change removed * Corrected the spell issue * Update NumberCellValueUnitTest.java * Fixed review comment * Update NumberCellValueUnitTest.java
1 parent 0711cec commit 1f08721

3 files changed

Lines changed: 124 additions & 1 deletion

File tree

‎apache-poi-2/src/main/java/com/baeldung/poi/excel/newcolumn/numeric/ExcelNumericFormat.java‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
package com.baeldung.poi.excel.newcolumn.numeric;
2+
3+
import org.apache.poi.ss.usermodel.Cell;
4+
import org.apache.poi.ss.usermodel.CellStyle;
5+
import org.apache.poi.ss.usermodel.DataFormat;
6+
import org.apache.poi.ss.usermodel.Row;
7+
import org.apache.poi.ss.usermodel.Workbook;
8+
9+
public class ExcelNumericFormat {
10+
11+
public static void applyNumericFormat(Workbook outWorkbook, Row row, Cell cell, Double value, String styleFormat) {
12+
CellStyle style = outWorkbook.createCellStyle();
13+
DataFormat format = outWorkbook.createDataFormat();
14+
style.setDataFormat(format.getFormat(styleFormat));
15+
cell.setCellValue(value);
16+
cell.setCellStyle(style);
17+
}
18+
19+
}

‎apache-poi-2/src/test/java/com/baeldung/poi/excel/newcolumn/numeric/NumberCellValueUnitTest.java‎

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
package com.baeldung.poi.excel.newcolumn.numeric;
2+
3+
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
4+
import org.apache.poi.ss.usermodel.Cell;
5+
import org.apache.poi.ss.usermodel.Row;
6+
import org.apache.poi.ss.usermodel.Sheet;
7+
import org.apache.poi.ss.usermodel.Workbook;
8+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
9+
import org.junit.Test;
10+
import org.junit.jupiter.api.Assertions;
11+
12+
import java.io.File;
13+
import java.io.FileInputStream;
14+
import java.io.FileOutputStream;
15+
import java.io.IOException;
16+
import java.text.DecimalFormat;
17+
18+
public class NumberCellValueUnitTest {
19+
20+
@Test
21+
public void decimalDisplay_whenAddedDouble_thenNumericCellCreated() throws IOException {
22+
File file = new File("number_test.xlsx");
23+
try (Workbook outWorkbook = new XSSFWorkbook()) {
24+
Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
25+
Row row = sheet.createRow(0);
26+
Cell cell = row.createCell(0);
27+
ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, 10.251, "0.00");
28+
FileOutputStream fileOut = new FileOutputStream(file);
29+
outWorkbook.write(fileOut);
30+
fileOut.close();
31+
}
32+
try (Workbook inWorkbook = new XSSFWorkbook("number_test.xlsx")) {
33+
Sheet sheet = inWorkbook.cloneSheet(0);
34+
Row row = sheet.getRow(0);
35+
Assertions.assertEquals(10.251, row.getCell(0)
36+
.getNumericCellValue());
37+
file.delete();
38+
}
39+
}
40+
41+
@Test
42+
public void decimalRoundedDisplay_whenAddedDouble_thenNumericCellCreated() throws IOException {
43+
File file = new File("number_test.xlsx");
44+
try (Workbook outWorkbook = new XSSFWorkbook()) {
45+
Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
46+
Row row = sheet.createRow(0);
47+
Cell cell = row.createCell(0);
48+
ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, 10.251123, "#,##0.0000");
49+
FileOutputStream fileOut = new FileOutputStream(file);
50+
outWorkbook.write(fileOut);
51+
fileOut.close();
52+
}
53+
try (Workbook inWorkbook = new XSSFWorkbook("number_test.xlsx")) {
54+
Sheet sheet = inWorkbook.cloneSheet(0);
55+
Row row = sheet.getRow(0);
56+
Assertions.assertEquals(10.251123, row.getCell(0)
57+
.getNumericCellValue());
58+
file.delete();
59+
}
60+
}
61+
62+
@Test
63+
public void decimalDisplayInXLS_whenAddedDouble_thenNumericCellCreated() throws IOException {
64+
File file = new File("number_test.xls");
65+
try (Workbook outWorkbook = new HSSFWorkbook()) {
66+
Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
67+
Row row = sheet.createRow(0);
68+
Cell cell = row.createCell(0);
69+
ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, 10.251, "0.00");
70+
FileOutputStream fileOut = new FileOutputStream(file);
71+
outWorkbook.write(fileOut);
72+
fileOut.close();
73+
}
74+
try (Workbook inWorkbook = new HSSFWorkbook(new FileInputStream(file))) {
75+
Sheet sheet = inWorkbook.cloneSheet(0);
76+
Row row = sheet.getRow(0);
77+
Assertions.assertEquals(10.251, row.getCell(0)
78+
.getNumericCellValue());
79+
file.delete();
80+
}
81+
}
82+
83+
@Test
84+
public void decimalValue_whenAddedDouble_thenNumericCellCreated() throws IOException {
85+
File file = new File("number_test.xlsx");
86+
try (Workbook outWorkbook = new XSSFWorkbook()) {
87+
Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
88+
Row row = sheet.createRow(0);
89+
Cell cell = row.createCell(0);
90+
DecimalFormat df = new DecimalFormat("#,###.##");
91+
ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, Double.valueOf(df.format(10.251)), "#,###.##");
92+
93+
FileOutputStream fileOut = new FileOutputStream(file);
94+
outWorkbook.write(fileOut);
95+
fileOut.close();
96+
}
97+
try (Workbook inWorkbook = new XSSFWorkbook("number_test.xlsx")) {
98+
Sheet sheet = inWorkbook.cloneSheet(0);
99+
Row row = sheet.getRow(0);
100+
Assertions.assertEquals(10.25, row.getCell(0)
101+
.getNumericCellValue());
102+
file.delete();
103+
}
104+
}
105+
}

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

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,6 @@
22

33
import org.apache.poi.ss.usermodel.Cell;
44
import org.apache.poi.ss.usermodel.CellStyle;
5-
import org.apache.poi.ss.usermodel.Row;
65

76
public class MultilineText {
87
public void formatMultilineText(Cell cell, int cellNumber) {

0 commit comments

Comments
 (0)