Skip to content

Commit 10eb6bf

Browse files
lor6KevinGilmore
lor6
authored and
KevinGilmore
committed
helper classes for excel processing, tests (eugenp#1093)
* helper classes for excel processing, tests * fix imports * list declaration
1 parent c77801e commit 10eb6bf

5 files changed

Lines changed: 353 additions & 0 deletions

File tree

‎apache-poi/pom.xml‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@
99
<maven-compiler-plugin.version>3.6.0</maven-compiler-plugin.version>
1010
<junit.version>4.12</junit.version>
1111
<poi.version>3.15</poi.version>
12+
<jexcel.version>1.0.6</jexcel.version>
1213
</properties>
1314

1415
<build>
@@ -37,5 +38,20 @@
3738
<artifactId>poi-ooxml</artifactId>
3839
<version>${poi.version}</version>
3940
</dependency>
41+
<dependency>
42+
<groupId>org.apache.poi</groupId>
43+
<artifactId>poi</artifactId>
44+
<version>${poi.version}</version>
45+
</dependency>
46+
<dependency>
47+
<groupId>org.apache.poi</groupId>
48+
<artifactId>poi-ooxml-schemas</artifactId>
49+
<version>${poi.version}</version>
50+
</dependency>
51+
<dependency>
52+
<groupId>org.jxls</groupId>
53+
<artifactId>jxls-jexcel</artifactId>
54+
<version>${jexcel.version}</version>
55+
</dependency>
4056
</dependencies>
4157
</project>

‎apache-poi/src/main/java/com/baeldung/jexcel/JExcelHelper.java‎

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
package com.baeldung.jexcel;
2+
3+
import jxl.*;
4+
import java.util.Map;
5+
import java.util.HashMap;
6+
import java.util.ArrayList;
7+
import java.util.List;
8+
import jxl.read.biff.BiffException;
9+
import java.io.File;
10+
import java.io.IOException;
11+
import jxl.write.*;
12+
import jxl.write.Number;
13+
import jxl.format.Colour;
14+
15+
public class JExcelHelper {
16+
17+
public Map<Integer, List<String>> readJExcel(String fileLocation) throws IOException, BiffException {
18+
Map<Integer, List<String>> data = new HashMap<>();
19+
20+
Workbook workbook = Workbook.getWorkbook(new File(fileLocation));
21+
Sheet sheet = workbook.getSheet(0);
22+
int rows = sheet.getRows();
23+
int columns = sheet.getColumns();
24+
25+
for (int i = 0; i < rows; i++) {
26+
data.put(i, new ArrayList<String>());
27+
for (int j = 0; j < columns; j++) {
28+
data.get(i).add(sheet.getCell(j, i).getContents());
29+
}
30+
}
31+
return data;
32+
}
33+
34+
public void writeJExcel() throws IOException, WriteException {
35+
WritableWorkbook workbook = null;
36+
try {
37+
File currDir = new File(".");
38+
String path = currDir.getAbsolutePath();
39+
String fileLocation = path.substring(0, path.length() - 1) + "temp.xls";
40+
41+
workbook = Workbook.createWorkbook(new File(fileLocation));
42+
43+
WritableSheet sheet = workbook.createSheet("Sheet 1", 0);
44+
45+
WritableCellFormat headerFormat = new WritableCellFormat();
46+
WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
47+
headerFormat.setFont(font);
48+
headerFormat.setBackground(Colour.LIGHT_BLUE);
49+
headerFormat.setWrap(true);
50+
Label headerLabel = new Label(0, 0, "Name", headerFormat);
51+
sheet.setColumnView(0, 60);
52+
sheet.addCell(headerLabel);
53+
54+
headerLabel = new Label(1, 0, "Age", headerFormat);
55+
sheet.setColumnView(0, 40);
56+
sheet.addCell(headerLabel);
57+
58+
WritableCellFormat cellFormat = new WritableCellFormat();
59+
cellFormat.setWrap(true);
60+
61+
Label cellLabel = new Label(0, 2, "John Smith", cellFormat);
62+
sheet.addCell(cellLabel);
63+
Number cellNumber = new Number(1, 2, 20, cellFormat);
64+
sheet.addCell(cellNumber);
65+
66+
cellLabel = new Label(0, 3, "Ana Johnson", cellFormat);
67+
sheet.addCell(cellLabel);
68+
cellNumber = new Number(1, 3, 30, cellFormat);
69+
sheet.addCell(cellNumber);
70+
71+
workbook.write();
72+
} finally {
73+
if (workbook != null) {
74+
workbook.close();
75+
}
76+
}
77+
78+
}
79+
}

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

Lines changed: 137 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,137 @@
1+
package com.baeldung.poi.excel;
2+
3+
import org.apache.poi.ss.usermodel.Cell;
4+
import org.apache.poi.ss.usermodel.CellType;
5+
import org.apache.poi.ss.usermodel.CellStyle;
6+
import org.apache.poi.ss.usermodel.IndexedColors;
7+
import org.apache.poi.ss.usermodel.Row;
8+
import org.apache.poi.ss.usermodel.Sheet;
9+
import org.apache.poi.ss.usermodel.Workbook;
10+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
11+
import org.apache.poi.xssf.usermodel.XSSFFont;
12+
import org.apache.poi.ss.usermodel.DateUtil;
13+
import org.apache.poi.ss.usermodel.FillPatternType;
14+
import java.io.File;
15+
import java.io.FileOutputStream;
16+
import java.io.FileInputStream;
17+
import java.io.IOException;
18+
import java.io.InputStream;
19+
import java.util.Map;
20+
import java.util.HashMap;
21+
import java.util.ArrayList;
22+
import java.util.List;
23+
24+
public class ExcelPOIHelper {
25+
26+
public Map<Integer, List<String>> readExcel(String fileLocation) throws IOException {
27+
28+
Map<Integer, List<String>> data = new HashMap<>();
29+
FileInputStream file = new FileInputStream(new File(fileLocation));
30+
Workbook workbook = new XSSFWorkbook(file);
31+
Sheet sheet = workbook.getSheetAt(0);
32+
int i = 0;
33+
for (Row row : sheet) {
34+
data.put(i, new ArrayList<String>());
35+
for (Cell cell : row) {
36+
switch (cell.getCellTypeEnum()) {
37+
case STRING:
38+
data.get(i)
39+
.add(cell.getRichStringCellValue()
40+
.getString());
41+
break;
42+
case NUMERIC:
43+
if (DateUtil.isCellDateFormatted(cell)) {
44+
data.get(i)
45+
.add(cell.getDateCellValue() + "");
46+
} else {
47+
data.get(i)
48+
.add((int)cell.getNumericCellValue() + "");
49+
}
50+
break;
51+
case BOOLEAN:
52+
data.get(i)
53+
.add(cell.getBooleanCellValue() + "");
54+
break;
55+
case FORMULA:
56+
data.get(i)
57+
.add(cell.getCellFormula() + "");
58+
break;
59+
default:
60+
data.get(i)
61+
.add(" ");
62+
}
63+
}
64+
i++;
65+
}
66+
if (workbook != null){
67+
workbook.close();
68+
}
69+
return data;
70+
}
71+
72+
public void writeExcel() throws IOException {
73+
Workbook workbook = new XSSFWorkbook();
74+
75+
try {
76+
Sheet sheet = workbook.createSheet("Persons");
77+
sheet.setColumnWidth(0, 6000);
78+
sheet.setColumnWidth(1, 4000);
79+
80+
Row header = sheet.createRow(0);
81+
82+
CellStyle headerStyle = workbook.createCellStyle();
83+
84+
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
85+
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
86+
87+
XSSFFont font = ((XSSFWorkbook) workbook).createFont();
88+
font.setFontName("Arial");
89+
font.setFontHeightInPoints((short) 16);
90+
font.setBold(true);
91+
headerStyle.setFont(font);
92+
93+
Cell headerCell = header.createCell(0);
94+
headerCell.setCellValue("Name");
95+
headerCell.setCellStyle(headerStyle);
96+
97+
headerCell = header.createCell(1);
98+
headerCell.setCellValue("Age");
99+
headerCell.setCellStyle(headerStyle);
100+
101+
CellStyle style = workbook.createCellStyle();
102+
style.setWrapText(true);
103+
104+
Row row = sheet.createRow(2);
105+
Cell cell = row.createCell(0);
106+
cell.setCellValue("John Smith");
107+
cell.setCellStyle(style);
108+
109+
cell = row.createCell(1);
110+
cell.setCellValue(20);
111+
cell.setCellStyle(style);
112+
113+
row = sheet.createRow(3);
114+
cell = row.createCell(0);
115+
cell.setCellValue("Ana Johnson");
116+
cell.setCellStyle(style);
117+
118+
cell = row.createCell(1);
119+
cell.setCellValue(30);
120+
cell.setCellStyle(style);
121+
122+
File currDir = new File(".");
123+
String path = currDir.getAbsolutePath();
124+
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";
125+
126+
FileOutputStream outputStream = new FileOutputStream(fileLocation);
127+
workbook.write(outputStream);
128+
} finally {
129+
if (workbook != null) {
130+
131+
workbook.close();
132+
133+
}
134+
}
135+
}
136+
137+
}

‎apache-poi/src/test/java/com/baeldung/jexcel/JExcelTest.java‎

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
package com.baeldung.jexcel;
2+
3+
import java.io.File;
4+
import java.io.FileOutputStream;
5+
import java.io.IOException;
6+
import java.io.InputStream;
7+
import jxl.read.biff.BiffException;
8+
import java.util.Map;
9+
import java.util.ArrayList;
10+
import java.util.List;
11+
12+
import com.baeldung.jexcel.JExcelHelper;
13+
14+
import jxl.write.WriteException;
15+
import jxl.read.biff.BiffException;
16+
17+
import static org.junit.Assert.assertEquals;
18+
import static org.junit.Assert.assertTrue;
19+
20+
import org.junit.Test;
21+
import org.junit.Before;
22+
23+
public class JExcelTest {
24+
25+
private JExcelHelper jExcelHelper;
26+
private static String FILE_NAME = "temp.xls";
27+
private String fileLocation;
28+
29+
@Before
30+
public void generateExcelFile() throws IOException, WriteException {
31+
32+
File currDir = new File(".");
33+
String path = currDir.getAbsolutePath();
34+
fileLocation = path.substring(0, path.length() - 1) + FILE_NAME;
35+
36+
jExcelHelper = new JExcelHelper();
37+
jExcelHelper.writeJExcel();
38+
39+
}
40+
41+
@Test
42+
public void whenParsingJExcelFile_thenCorrect() throws IOException, BiffException {
43+
Map<Integer, List<String>> data = jExcelHelper.readJExcel(fileLocation);
44+
45+
assertEquals("Name", data.get(0)
46+
.get(0));
47+
assertEquals("Age", data.get(0)
48+
.get(1));
49+
50+
assertEquals("John Smith", data.get(2)
51+
.get(0));
52+
assertEquals("20", data.get(2)
53+
.get(1));
54+
55+
assertEquals("Ana Johnson", data.get(3)
56+
.get(0));
57+
assertEquals("30", data.get(3)
58+
.get(1));
59+
60+
}
61+
62+
}

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

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
package com.baeldung.poi.excel;
2+
3+
import java.io.File;
4+
import java.io.FileOutputStream;
5+
import java.io.IOException;
6+
import java.io.InputStream;
7+
import jxl.read.biff.BiffException;
8+
import java.util.Map;
9+
import java.util.ArrayList;
10+
import java.util.List;
11+
12+
import com.baeldung.poi.excel.ExcelPOIHelper;
13+
14+
import static org.junit.Assert.assertEquals;
15+
import static org.junit.Assert.assertTrue;
16+
17+
import org.junit.Test;
18+
import org.junit.Before;
19+
20+
public class ExcelTest {
21+
22+
private ExcelPOIHelper excelPOIHelper;
23+
private static String FILE_NAME = "temp.xlsx";
24+
private String fileLocation;
25+
26+
@Before
27+
public void generateExcelFile() throws IOException {
28+
29+
File currDir = new File(".");
30+
String path = currDir.getAbsolutePath();
31+
fileLocation = path.substring(0, path.length() - 1) + FILE_NAME;
32+
33+
excelPOIHelper = new ExcelPOIHelper();
34+
excelPOIHelper.writeExcel();
35+
36+
}
37+
38+
@Test
39+
public void whenParsingPOIExcelFile_thenCorrect() throws IOException {
40+
Map<Integer, List<String>> data = excelPOIHelper.readExcel(fileLocation);
41+
42+
assertEquals("Name", data.get(0)
43+
.get(0));
44+
assertEquals("Age", data.get(0)
45+
.get(1));
46+
47+
assertEquals("John Smith", data.get(1)
48+
.get(0));
49+
assertEquals("20", data.get(1)
50+
.get(1));
51+
52+
assertEquals("Ana Johnson", data.get(2)
53+
.get(0));
54+
assertEquals("30", data.get(2)
55+
.get(1));
56+
57+
}
58+
59+
}

0 commit comments

Comments
 (0)