Skip to content

Commit ba29be1

Browse files
olsiseferiOlsi Seferi
olsiseferi
and
Olsi Seferi
authored
ExcelUtility Jira issue BAEL-5198 (eugenp#11503)
* CODE REFACTOR AND ADDED UNIT TEST * SMALL CHANGE * FIXED TESTS TIMEZONE ISSUES Co-authored-by: Olsi Seferi <olsi.seferi@sisal.al>
1 parent 13c456f commit ba29be1

3 files changed

Lines changed: 120 additions & 0 deletions

File tree

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

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
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+
private static final String ENDLINE = System.getProperty("line.separator");
17+
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();
42+
43+
} catch (IOException e) {
44+
throw e;
45+
}
46+
return toReturn.toString();
47+
}
48+
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+
}
66+
}

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

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
package com.baeldung.poi.excel;
2+
3+
import static org.junit.Assert.assertEquals;
4+
import static org.junit.jupiter.api.Assertions.assertThrows;
5+
6+
import java.io.IOException;
7+
import java.net.URISyntaxException;
8+
import java.nio.file.Paths;
9+
import java.text.ParseException;
10+
import java.text.SimpleDateFormat;
11+
12+
import org.junit.Before;
13+
import org.junit.Test;
14+
15+
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+
}
53+
54+
}

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

16.4 KB
Binary file not shown.

0 commit comments

Comments
 (0)