Skip to content

Commit 12c5053

Browse files
olsiseferiOlsi Seferi
olsiseferi
and
Olsi Seferi
authored
ExcelUtility Jira issue BAEL-5198 (eugenp#11559)
* CODE REFACTOR AND ADDED UNIT TEST * SMALL CHANGE * FIXED TESTS TIMEZONE ISSUES * UPDATED FORMATTING Co-authored-by: Olsi Seferi <[email protected]>
1 parent c9b503d commit 12c5053

4 files changed

Lines changed: 351 additions & 84 deletions

File tree

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

Lines changed: 56 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -13,54 +13,63 @@
1313
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
1414

1515
public class ExcelUtility {
16-
private static final String ENDLINE = System.getProperty("line.separator");
16+
private static final String ENDLINE = System.getProperty("line.separator");
1717

18-
public static String readExcel(String filePath) throws IOException {
19-
File file = new File(filePath);
20-
FileInputStream inputStream = null;
21-
StringBuilder toReturn = new StringBuilder();
22-
try {
23-
inputStream = new FileInputStream(file);
24-
Workbook baeuldungWorkBook = new XSSFWorkbook(inputStream);
25-
for (Sheet sheet : baeuldungWorkBook) {
26-
toReturn.append("--------------------------------------------------------------------").append(ENDLINE);
27-
toReturn.append("Worksheet :").append(sheet.getSheetName()).append(ENDLINE);
28-
toReturn.append("--------------------------------------------------------------------").append(ENDLINE);
29-
int firstRow = sheet.getFirstRowNum();
30-
int lastRow = sheet.getLastRowNum();
31-
for (int index = firstRow + 1; index <= lastRow; index++) {
32-
Row row = sheet.getRow(index);
33-
toReturn.append("|| ");
34-
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
35-
Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
36-
printCellValue(cell, toReturn);
37-
}
38-
toReturn.append(" ||").append(ENDLINE);
39-
}
40-
}
41-
inputStream.close();
18+
public static String readExcel(String filePath) throws IOException {
19+
File file = new File(filePath);
20+
FileInputStream inputStream = null;
21+
StringBuilder toReturn = new StringBuilder();
22+
try {
23+
inputStream = new FileInputStream(file);
24+
Workbook baeuldungWorkBook = new XSSFWorkbook(inputStream);
25+
for (Sheet sheet : baeuldungWorkBook) {
26+
toReturn.append("--------------------------------------------------------------------")
27+
.append(ENDLINE);
28+
toReturn.append("Worksheet :")
29+
.append(sheet.getSheetName())
30+
.append(ENDLINE);
31+
toReturn.append("--------------------------------------------------------------------")
32+
.append(ENDLINE);
33+
int firstRow = sheet.getFirstRowNum();
34+
int lastRow = sheet.getLastRowNum();
35+
for (int index = firstRow + 1; index <= lastRow; index++) {
36+
Row row = sheet.getRow(index);
37+
toReturn.append("|| ");
38+
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
39+
Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
40+
printCellValue(cell, toReturn);
41+
}
42+
toReturn.append(" ||")
43+
.append(ENDLINE);
44+
}
45+
}
46+
inputStream.close();
4247

43-
} catch (IOException e) {
44-
throw e;
45-
}
46-
return toReturn.toString();
47-
}
48+
} catch (IOException e) {
49+
throw e;
50+
}
51+
return toReturn.toString();
52+
}
4853

49-
public static void printCellValue(Cell cell, StringBuilder toReturn) {
50-
CellType cellType = cell.getCellType().equals(CellType.FORMULA) ? cell.getCachedFormulaResultType()
51-
: cell.getCellType();
52-
if (cellType.equals(CellType.STRING)) {
53-
toReturn.append(cell.getStringCellValue()).append(" | ");
54-
}
55-
if (cellType.equals(CellType.NUMERIC)) {
56-
if (DateUtil.isCellDateFormatted(cell)) {
57-
toReturn.append(cell.getDateCellValue()).append(" | ");
58-
} else {
59-
toReturn.append(cell.getNumericCellValue()).append(" | ");
60-
}
61-
}
62-
if (cellType.equals(CellType.BOOLEAN)) {
63-
toReturn.append(cell.getBooleanCellValue()).append(" | ");
64-
}
65-
}
54+
public static void printCellValue(Cell cell, StringBuilder toReturn) {
55+
CellType cellType = cell.getCellType()
56+
.equals(CellType.FORMULA) ? cell.getCachedFormulaResultType() : cell.getCellType();
57+
if (cellType.equals(CellType.STRING)) {
58+
toReturn.append(cell.getStringCellValue())
59+
.append(" | ");
60+
}
61+
if (cellType.equals(CellType.NUMERIC)) {
62+
if (DateUtil.isCellDateFormatted(cell)) {
63+
toReturn.append(cell.getDateCellValue())
64+
.append(" | ");
65+
} else {
66+
toReturn.append(cell.getNumericCellValue())
67+
.append(" | ");
68+
}
69+
}
70+
if (cellType.equals(CellType.BOOLEAN)) {
71+
toReturn.append(cell.getBooleanCellValue())
72+
.append(" | ");
73+
}
74+
}
6675
}

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

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 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.DateUtil;
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.xssf.usermodel.XSSFWorkbook;
14+
15+
public class ExcelUtility {
16+
<<<<<<< HEAD
17+
private static final String ENDLINE = System.getProperty("line.separator");
18+
19+
public static String readExcel(String filePath) throws IOException {
20+
File file = new File(filePath);
21+
FileInputStream inputStream = null;
22+
StringBuilder toReturn = new StringBuilder();
23+
try {
24+
inputStream = new FileInputStream(file);
25+
Workbook baeuldungWorkBook = new XSSFWorkbook(inputStream);
26+
for (Sheet sheet : baeuldungWorkBook) {
27+
toReturn.append("--------------------------------------------------------------------")
28+
.append(ENDLINE);
29+
toReturn.append("Worksheet :")
30+
.append(sheet.getSheetName())
31+
.append(ENDLINE);
32+
toReturn.append("--------------------------------------------------------------------")
33+
.append(ENDLINE);
34+
int firstRow = sheet.getFirstRowNum();
35+
int lastRow = sheet.getLastRowNum();
36+
for (int index = firstRow + 1; index <= lastRow; index++) {
37+
Row row = sheet.getRow(index);
38+
toReturn.append("|| ");
39+
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
40+
Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
41+
printCellValue(cell, toReturn);
42+
}
43+
toReturn.append(" ||")
44+
.append(ENDLINE);
45+
}
46+
}
47+
inputStream.close();
48+
49+
} catch (IOException e) {
50+
throw e;
51+
}
52+
return toReturn.toString();
53+
}
54+
55+
public static void printCellValue(Cell cell, StringBuilder toReturn) {
56+
CellType cellType = cell.getCellType()
57+
.equals(CellType.FORMULA) ? cell.getCachedFormulaResultType() : cell.getCellType();
58+
if (cellType.equals(CellType.STRING)) {
59+
toReturn.append(cell.getStringCellValue())
60+
.append(" | ");
61+
}
62+
if (cellType.equals(CellType.NUMERIC)) {
63+
if (DateUtil.isCellDateFormatted(cell)) {
64+
toReturn.append(cell.getDateCellValue())
65+
.append(" | ");
66+
} else {
67+
toReturn.append(cell.getNumericCellValue())
68+
.append(" | ");
69+
}
70+
}
71+
if (cellType.equals(CellType.BOOLEAN)) {
72+
toReturn.append(cell.getBooleanCellValue())
73+
.append(" | ");
74+
}
75+
}
76+
=======
77+
private static final String ENDLINE = System.getProperty("line.separator");
78+
79+
public static String readExcel(String filePath) throws IOException {
80+
File file = new File(filePath);
81+
FileInputStream inputStream = null;
82+
StringBuilder toReturn = new StringBuilder();
83+
try {
84+
inputStream = new FileInputStream(file);
85+
Workbook baeuldungWorkBook = new XSSFWorkbook(inputStream);
86+
for (Sheet sheet : baeuldungWorkBook) {
87+
toReturn.append("--------------------------------------------------------------------").append(ENDLINE);
88+
toReturn.append("Worksheet :").append(sheet.getSheetName()).append(ENDLINE);
89+
toReturn.append("--------------------------------------------------------------------").append(ENDLINE);
90+
int firstRow = sheet.getFirstRowNum();
91+
int lastRow = sheet.getLastRowNum();
92+
for (int index = firstRow + 1; index <= lastRow; index++) {
93+
Row row = sheet.getRow(index);
94+
toReturn.append("|| ");
95+
for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
96+
Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
97+
printCellValue(cell, toReturn);
98+
}
99+
toReturn.append(" ||").append(ENDLINE);
100+
}
101+
}
102+
inputStream.close();
103+
104+
} catch (IOException e) {
105+
throw e;
106+
}
107+
return toReturn.toString();
108+
}
109+
110+
public static void printCellValue(Cell cell, StringBuilder toReturn) {
111+
CellType cellType = cell.getCellType().equals(CellType.FORMULA) ? cell.getCachedFormulaResultType()
112+
: cell.getCellType();
113+
if (cellType.equals(CellType.STRING)) {
114+
toReturn.append(cell.getStringCellValue()).append(" | ");
115+
}
116+
if (cellType.equals(CellType.NUMERIC)) {
117+
if (DateUtil.isCellDateFormatted(cell)) {
118+
toReturn.append(cell.getDateCellValue()).append(" | ");
119+
} else {
120+
toReturn.append(cell.getNumericCellValue()).append(" | ");
121+
}
122+
}
123+
if (cellType.equals(CellType.BOOLEAN)) {
124+
toReturn.append(cell.getBooleanCellValue()).append(" | ");
125+
}
126+
}
127+
>>>>>>> master
128+
}

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

Lines changed: 55 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -13,42 +13,60 @@
1313
import org.junit.Test;
1414

1515
public class ExcelUtilityUnitTest {
16-
private static final String FILE_NAME = "baeldung.xlsx";
17-
private String fileLocation;
18-
private static final String ENDLINE = System.getProperty("line.separator");
19-
private StringBuilder output;
20-
21-
@Before
22-
public void setupUnitTest() throws IOException, URISyntaxException, ParseException {
23-
output = new StringBuilder();
24-
output.append("--------------------------------------------------------------------").append(ENDLINE);
25-
output.append("Worksheet :Sheet1").append(ENDLINE);
26-
output.append("--------------------------------------------------------------------").append(ENDLINE);
27-
output.append("|| Name1 | Surname1 | 3.55696564113E11 | ").append(new SimpleDateFormat("dd/MM/yyyy").parse("4/11/2021").toString()).append(" | ‡ | ||")
28-
.append(ENDLINE);
29-
output.append("|| Name2 | Surname2 | 5.646513512E9 | ").append(new SimpleDateFormat("dd/MM/yyyy").parse("4/12/2021").toString()).append(" | false | ||")
30-
.append(ENDLINE);
31-
output.append("|| Name3 | Surname3 | 3.55696564113E11 | ").append(new SimpleDateFormat("dd/MM/yyyy").parse("4/11/2021").toString()).append(" | 7.17039641738E11 | ||")
32-
.append(ENDLINE);
33-
output.append("--------------------------------------------------------------------").append(ENDLINE);
34-
output.append("Worksheet :Sheet2").append(ENDLINE);
35-
output.append("--------------------------------------------------------------------").append(ENDLINE);
36-
output.append("|| Name4 | Surname4 | 3.55675623232E11 | 13/04/2021 | ||").append(ENDLINE);
37-
38-
fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME).toURI()).toString();
39-
}
40-
41-
@Test
42-
public void givenStringPath_whenReadExcel_thenReturnStringValue() throws IOException {
43-
assertEquals(output.toString(), ExcelUtility.readExcel(fileLocation));
44-
45-
}
46-
47-
@Test
48-
public void givenStringPath_whenReadExcel_thenThrowException() {
49-
assertThrows(IOException.class, () -> {
50-
ExcelUtility.readExcel("baeldung");
51-
});
52-
}
16+
private static final String FILE_NAME = "baeldung.xlsx";
17+
private String fileLocation;
18+
private static final String ENDLINE = System.getProperty("line.separator");
19+
private StringBuilder output;
20+
21+
@Before
22+
public void setupUnitTest() throws IOException, URISyntaxException, ParseException {
23+
output = new StringBuilder();
24+
output.append("--------------------------------------------------------------------")
25+
.append(ENDLINE);
26+
output.append("Worksheet :Sheet1")
27+
.append(ENDLINE);
28+
output.append("--------------------------------------------------------------------")
29+
.append(ENDLINE);
30+
output.append("|| Name1 | Surname1 | 3.55696564113E11 | ")
31+
.append(new SimpleDateFormat("dd/MM/yyyy").parse("4/11/2021")
32+
.toString())
33+
.append(" | ‡ | ||")
34+
.append(ENDLINE);
35+
output.append("|| Name2 | Surname2 | 5.646513512E9 | ")
36+
.append(new SimpleDateFormat("dd/MM/yyyy").parse("4/12/2021")
37+
.toString())
38+
.append(" | false | ||")
39+
.append(ENDLINE);
40+
output.append("|| Name3 | Surname3 | 3.55696564113E11 | ")
41+
.append(new SimpleDateFormat("dd/MM/yyyy").parse("4/11/2021")
42+
.toString())
43+
.append(" | 7.17039641738E11 | ||")
44+
.append(ENDLINE);
45+
output.append("--------------------------------------------------------------------")
46+
.append(ENDLINE);
47+
output.append("Worksheet :Sheet2")
48+
.append(ENDLINE);
49+
output.append("--------------------------------------------------------------------")
50+
.append(ENDLINE);
51+
output.append("|| Name4 | Surname4 | 3.55675623232E11 | 13/04/2021 | ||")
52+
.append(ENDLINE);
53+
54+
fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME)
55+
.toURI())
56+
.toString();
57+
}
58+
59+
@Test
60+
public void givenStringPath_whenReadExcel_thenReturnStringValue() throws IOException {
61+
assertEquals(output.toString(), ExcelUtility.readExcel(fileLocation));
62+
63+
}
64+
65+
@Test
66+
public void givenStringPath_whenReadExcel_thenThrowException() {
67+
assertThrows(IOException.class, () -> {
68+
ExcelUtility.readExcel("baeldung");
69+
});
70+
}
5371

5472
}

0 commit comments

Comments
 (0)